ORMs in Production PostgreSQL: Friend or Foe?

Object-relational mappings (ORMs) connect object-oriented programming languages to databases. With an ORM, developers can access relational databases like PostgreSQL while leveraging an object-oriented model.

The object-oriented wrapping and familiar language interface make ORMs popular with developers. However, ORMs come with downsides as well. Let’s take a look at ORMs to see where they work and where they struggle.

What Is Object-Relational Mapping (ORM)?

In general, an ORM is an application API layer that connects an object-oriented system to a relational database that uses SQL as its primary query language. The ORM is responsible for translating API calls in the programming language of your choice into SQL code. Put another way, it converts procedural code (Python, Java, etc.) into declarative code (SQL). 

Some common ORMs are SQLAlchemy for Python, Hibernate for Java, and Django’s ORM model. Let’s look at some example code to see exactly how ORMs work and what they do for developers.

ORM’s in Different Languages

Let’s look at the query structure for a simple SQL query in several different ORMs. Assume we’re pulling all the data from a table, i.e., [[SELECT * FROM table]] .

For Python development, Django and SQLAlchemy are two popular ORMs. Django pulls tables into object wrappers, so selecting a whole table looks like this: 

table.objects.all() 

In SQLAlchemy, an engine runs queries against a database, so a select-all query looks like:

engine.select([table])

For JavaScript, Sequelize is a popular option. In Sequelize, selecting a whole table looks like:

table.findAll(); this

For TypeScript, TypeORM is a common choice. TypeORM uses its own query builder syntax, so a select-all query has a particular form

const table = await dataSource
	.getRepository(table)
	.createQueryBuilder(“table”)
	.getMany()

For Go, one popular ORM is GORM. A select-all command is: 

result := db.Find(&table)

All these different ORMs have specific ways of wrapping even simple SQL queries. If you decide to use an ORM, you must learn how that package functions and modify it for your project. 

The Pros and Cons of ORMS

The primary benefit of an ORM is that it allows you to develop your entire project in a single language. The ORM handles the interfacing for you so you can focus on your project. A single language can give you a more straightforward code base and documentation. ORMs can also save valuable training time since developers can work in languages they already know.

Another benefit is the O in ORM: objects. Objects are flexible, so interfacing objects with SQL can allow business logic to be integrated into objects. For example, you could pull customer data like purchased items, amount spent, shopping time, etc., into a “user” object. The user object could then be equipped with functions that allow it to be part of a customer behavior simulation for predictive modeling.

ORMs also usually have simplified syntax for common query structures. Since it handles the specifics of the query behind the scenes, you don’t have to worry about query structures or SQL optimization. ORMs also handle query safety, so you don’t have to worry about unexpected query disasters.

Why We Don’t Recommend Using ORMs With Large Production Postgres 

However, we don’t recommend using ORMs on large-scale production Postgres systems. And here are some reasons why. 

ORM packages may be part of a familiar language but are still complex. This complexity means that engineers must still devote time to learning and understanding how the ORM works before using it. Often, this will mean learning some SQL anyway. At the very least, it means learning how the underlying relational model works.

ORMs interface with SQL behind the scenes, but the ORM generates that SQL automatically. These systems are most effective for small, simple queries as part of a larger coding project, like fetching rows of data to plug into an object. If the query gets more complicated, hand-rolled SQL usually performs better since engineers can optimize it for their use case. The ORM’s high level of abstraction produces inefficient SQL queries on the backend, which is tricky to observe and harder to optimize. 

For similar reasons, utilizing ORMs with complex relational systems is difficult. ORMs are simple to set up with a single-table system since simple queries are effective with a single table. Multiple-table systems, however, require more complex query systems, which quickly become hard to manage in ORM syntax. An object is needed for the result of each table and the result of each query; it’s a lot of mapping.

Abstraction also makes it painful to manage migrations or updates to the database. ORMs may be nice during initial development—but they can become a severe obstacle when scaling down the line. While an SQL query may require an update alongside changes to database architecture, this is what SQL is for. On the other hand, ORMs usually require complete overhauls to deal with a change in the underlying database.

Complicated systems need to take advantage of PostgreSQL-specific features for performance at scale. Indexing, materialization, leveraging partitions, and specific query scheduling allow SQL to deliver responsive and complex queries. Without an ORM, this complexity may leave room for injection attacks. However, ORMS themselves can have vulnerabilities hidden beneath abstraction layers, and SQL’s long history has produced a wealth of resources covering the prepared statement techniques required to keep systems safe. 

ORM queries’ lack of optimization leads to a significant slowdown for involved analytics dashboards. The EdgeDB team benchmarked their SQL queries vs. popular ORMs, achieving a 4x throughput increase and latency reduction. Inefficient ORM queries often bottleneck large data systems and, even worse, take considerable developer time to debug and fix. The ORM that helped you launch your proof of concept will quickly become technical debt.

Conclusion

ORMs are a helpful development tool but come with a severe performance tax. If you need a robust, simple PostgreSQL database at scale, Timescale might be right for you. See for yourself by trying Timescale for free with no credit card required.

Code quick starts

Want to try out TimescaleDB in your favorite programming language? Check our code quick starts or feel free to request one.