Editor’s note: This post was updated 5 October 2022 to include information about using Sequelize, Knex, and other ORMs with Node.js, when to use an ORM, and the “best” ORM to use for Node.js. The article was also updated to remove outdated information and make general edits.
In a nutshell, ORM refers to object-relational mapping. And this means we map relational sequel objects — foreign keys and primary keys to actual objects.
The result of this is that ORM creates a structure for CRUD operations in our database. Consequently, it also provides a layer of abstraction over the SQL statement by allowing developers to operate on the object instead of going through the hassle of writing SQL queries. Thus, ORM improves readability, documentation, and the speed of development.
However, in this tutorial, we’ll consider three reasons why you should avoid using ORM in your project. While the concepts discussed are applicable to every language and platform, the code examples will be written in Node.js-flavored JavaScript, and we will be considering packages obtained from the npm repository.
I don’t intend to diss any of the modules mentioned in this post. A lot of hard work has gone into each and every one of them. They are used by production applications all around the world which merrily respond to plenty of requests every day. I’ve also deployed applications using ORMs and regret nothing.
ORM is the process of mapping between objects and relational database systems. Different database systems access data in myriad ways, and ORM helps you maintain objects even when the sources and apps they access change over time.
ORMs are commonly used to streamline the migration of data between databases.
Before we get to the reasons why you shouldn’t use ORM with Node.js, let’s list some benefits. When used properly, ORM in Node.js enables you to:
ORMs are powerful tools. The ORMs we’ll be examining in this post are able to communicate with SQL backends such as SQLite, PostgreSQL, MySQL, and MSSQL. The examples in this post will make use of PostgreSQL, which is a very powerful open source SQL server.
There are ORMs capable of communicating with NoSQL backends, such as the Mongoose ORM backed by MongoDB, but we won’t be considering those in this post.
First, run the following commands to start an instance of PostgreSQL locally. It will be configured in such a way that requests made to the default PostgreSQL port on localhost:5432
will be forwarded to the container. It’ll also write the files to disk in your home directory so that subsequent instantiations will retain the data we’re already created:
mkdir -p ~/data/pg-node-orms docker run --name pg-node-orms -p 5432:5432 -e POSTGRES_PASSWORD=hunter12 -e POSTGRES_USER=orm-user -e POSTGRES_DB=orm-db -v ~/data/pg-node-orms:/var/lib/postgresql/data -d postgres
Now that you’ve got a database running, we need to add some tables and data to the database. This will allow us to query against the data and get a better understanding of the various layers of abstraction.
Run the next command to start an interactive PostgreSQL prompt:
docker run -it --rm --link pg-node-orms:postgres postgres psql -h postgres -U orm-user orm-db
At the prompt type in the password from the previous code block, hunter12
. Now that you’re connected, copy and paste the following queries into the prompt and press the Enter
key:
CREATE TYPE item_type AS ENUM ( 'meat', 'veg', 'spice', 'dairy', 'oil' ); CREATE TABLE item ( id SERIAL PRIMARY KEY, name VARCHAR(64) NOT NULL, type item_type ); CREATE INDEX ON item (type); INSERT INTO item VALUES (1, 'Chicken', 'meat'), (2, 'Garlic', 'veg'), (3, 'Ginger', 'veg'), (4, 'Garam Masala', 'spice'), (5, 'Turmeric', 'spice'), (6, 'Cumin', 'spice'), (7, 'Ground Chili', 'spice'), (8, 'Onion', 'veg'), (9, 'Coriander', 'spice'), (10, 'Tomato', 'veg'), (11, 'Cream', 'dairy'), (12, 'Paneer', 'dairy'), (13, 'Peas', 'veg'), (14, 'Ghee', 'oil'), (15, 'Cinnamon', 'spice'); CREATE TABLE dish ( id SERIAL PRIMARY KEY, name VARCHAR(64) NOT NULL, veg BOOLEAN NOT NULL ); CREATE INDEX ON dish (veg); INSERT INTO dish VALUES (1, 'Chicken Tikka Masala', false), (2, 'Matar Paneer', true); CREATE TABLE ingredient ( dish_id INTEGER NOT NULL REFERENCES dish (id), item_id INTEGER NOT NULL REFERENCES item (id), quantity FLOAT DEFAULT 1, unit VARCHAR(32) NOT NULL ); INSERT INTO ingredient VALUES (1, 1, 1, 'whole breast'), (1, 2, 1.5, 'tbsp'), (1, 3, 1, 'tbsp'), (1, 4, 2, 'tsp'), (1, 5, 1, 'tsp'), (1, 6, 1, 'tsp'), (1, 7, 1, 'tsp'), (1, 8, 1, 'whole'), (1, 9, 1, 'tsp'), (1, 10, 2, 'whole'), (1, 11, 1.25, 'cup'), (2, 2, 3, 'cloves'), (2, 3, 0.5, 'inch piece'), (2, 13, 1, 'cup'), (2, 6, 0.5, 'tsp'), (2, 5, 0.25, 'tsp'), (2, 7, 0.5, 'tsp'), (2, 4, 0.5, 'tsp'), (2, 11, 1, 'tbsp'), (2, 14, 2, 'tbsp'), (2, 10, 3, 'whole'), (2, 8, 1, 'whole'), (2, 15, 0.5, 'inch stick');
You now have a populated database. You can type quit
to disconnect from the psql
client and get control of your terminal back. If you ever want to run raw SQL commands again, you can run that same docker run
command again.
Finally, you’ll also need to create a file named connection.json
containing the following JSON structure. This will be used by the Node applications later to connect to the database:
{ "host": "localhost", "port": 5432, "database": "orm-db", "user": "orm-user", "password": "hunter12" }
Before diving into too much code, let’s clarify a few different layers of abstraction. Just like everything in computer science, there are tradeoffs as we add layers of abstraction. With each added layer of abstraction, we attempt to trade a decrease in performance with an increase in developer productivity (though this is not always the case).
This is basically as low-level as you can get — short of manually generating TCP packets and delivering them to the database.
A database driver is going to handle connecting to a database (and sometimes connection pooling). At this level, you’re going to be writing raw SQL strings and delivering them to a database, and receiving a response from the database.
In the Node.js ecosystem there are many libraries operating at this layer. Here are three popular libraries:
Each of these libraries essentially works the same way: take the database credentials, instantiate a new database instance, connect to the database, and send it queries in the form of a string and asynchronously handle the result.
Here is a simple example using the pg
module to get a list of ingredients required to cook chicken tikka masala:
#!/usr/bin/env node // $ npm install pg const { Client } = require('pg'); const connection = require('./connection.json'); const client = new Client(connection); client.connect(); const query = `SELECT ingredient.*, item.name AS item_name, item.type AS item_type FROM ingredient LEFT JOIN item ON item.id = ingredient.item_id WHERE ingredient.dish_id = $1`; client .query(query, [1]) .then(res => { console.log('Ingredients:'); for (let row of res.rows) { console.log(`${row.item_name}: ${row.quantity} ${row.unit}`); } client.end(); });
This is the intermediary level between using the simpler database driver module vs. a full-fledged ORM. The most notable module which operates at this layer is Knex.
This module is able to generate queries for a few different SQL dialects. This module depends on one of the aforementioned libraries — you’ll need to install the particular ones you plan on using with Knex.
When creating a Knex instance, you provide the connection details, along with the dialect you plan on using, and are then able to start making queries. The queries you write will closely resemble the underlying SQL queries.
One nicety is that you’re able to programmatically generate dynamic queries in a much more convenient way than if you were to concatenate strings together to form SQL (which often introduces security vulnerabilities).
Here is a simple example using the knex
module to get a list of ingredients required to cook chicken tikka masala:
#!/usr/bin/env node // $ npm install pg knex const knex = require('knex'); const connection = require('./connection.json'); const client = knex({ client: 'pg', connection }); client .select([ '*', client.ref('item.name').as('item_name'), client.ref('item.type').as('item_type'), ]) .from('ingredient') .leftJoin('item', 'item.id', 'ingredient.item_id') .where('dish_id', '=', 1) .debug() .then(rows => { console.log('Ingredients:'); for (let row of rows) { console.log(`${row.item_name}: ${row.quantity} ${row.unit}`); } client.destroy(); });
This is the highest level of abstraction we’re going to consider. When working with ORMs, we typically need to do a lot more configuration ahead of time. The point of ORM, as the name implies, is to map a record in a relational database to an object (typically, but not always, a class instance) in our application.
What this means is that we’re defining the structure of these objects, as well as their relationships, in our application code.
In this example, we’re going to look at the most popular of the ORMs: Sequelize. We’re also going to model the relationships represented in our original PostgreSQL schema using Sequelize.
Here is the same example using the Sequelize module to get a list of ingredients required to cook chicken tikka masala:
#!/usr/bin/env node // $ npm install sequelize pg const Sequelize = require('sequelize'); const connection = require('./connection.json'); const DISABLE_SEQUELIZE_DEFAULTS = { timestamps: false, freezeTableName: true, }; const { DataTypes } = Sequelize; const sequelize = new Sequelize({ database: connection.database, username: connection.user, host: connection.host, port: connection.port, password: connection.password, dialect: 'postgres', operatorsAliases: false }); const Dish = sequelize.define('dish', { id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true }, name: { type: DataTypes.STRING }, veg: { type: DataTypes.BOOLEAN } }, DISABLE_SEQUELIZE_DEFAULTS); const Item = sequelize.define('item', { id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true }, name: { type: DataTypes.STRING }, type: { type: DataTypes.STRING } }, DISABLE_SEQUELIZE_DEFAULTS); const Ingredient = sequelize.define('ingredient', { dish_id: { type: DataTypes.INTEGER, primaryKey: true }, item_id: { type: DataTypes.INTEGER, primaryKey: true }, quantity: { type: DataTypes.FLOAT }, unit: { type: DataTypes.STRING } }, DISABLE_SEQUELIZE_DEFAULTS); Item.belongsToMany(Dish, { through: Ingredient, foreignKey: 'item_id' }); Dish.belongsToMany(Item, { through: Ingredient, foreignKey: 'dish_id' }); Dish.findOne({where: {id: 1}, include: [{model: Item}]}).then(rows => { console.log('Ingredients:'); for (let row of rows.items) { console.log( `${row.dataValues.name}: ${row.ingredient.dataValues.quantity} ` + row.ingredient.dataValues.unit ); } sequelize.close(); });
We have tried to classify some ORMs in previous sections, but in this section, we will be comparing these ORMs.
Let’s start with Sequelize. Sequelize is a full-blown ORM. It is feature-packed and compels you to hide SQL behind object representation.
Knex, on the other hand, is more low-level as it is a plain query builder. Knex is great because it allows you easily find and see what is going on in the database without the abstraction of ORM. However, as the application grows and becomes more complex, we see that working with complex relations with Knex can be tedious and time-consuming.
Objection.js sits in the middle. It combines the good parts of different ORMs without compromising the power of writing raw SQL queries.
Now that you’ve seen an example of how to perform similar queries using the different abstraction layers, let’s dive into three reasons why you should be wary of using ORM.
Many people pick up ORM because they don’t want to take the time to learn the underlying SQL. The belief is that SQL is hard to learn and that by learning ORM, we can simply write our applications using a single language instead of two.
At first glance, this seems to hold up. ORM is going to be written in the same language as the rest of the application, while SQL is a completely different syntax.
There is a problem with this line of thinking, however. The problem is that ORMs represent some of the most complex libraries you can get your hands on. The surface area of ORM is very large and learning it inside and out is no easy task.
Once you have learned a particular ORM, this knowledge likely won’t transfer that well. This is true if you switch from one platform to another, such as JS/Node.js to C#/.NET. But perhaps even less obvious is that this is true if you switch from one ORM to another within the same platform, such as Sequelize to Bookshelf with Node.js.
Consider the following ORM examples which each generate a list of all recipe items which are vegetarian.
With Sequelize:
#!/usr/bin/env node // $ npm install sequelize pg const Sequelize = require('sequelize'); const { Op, DataTypes } = Sequelize; const connection = require('./connection.json'); const DISABLE_SEQUELIZE_DEFAULTS = { timestamps: false, freezeTableName: true, }; const sequelize = new Sequelize({ database: connection.database, username: connection.user, host: connection.host, port: connection.port, password: connection.password, dialect: 'postgres', operatorsAliases: false }); const Item = sequelize.define('item', { id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true }, name: { type: DataTypes.STRING }, type: { type: DataTypes.STRING } }, DISABLE_SEQUELIZE_DEFAULTS); // SELECT "id", "name", "type" FROM "item" AS "item" // WHERE "item"."type" = 'veg'; Item .findAll({where: {type: 'veg'}}) .then(rows => { console.log('Veggies:'); for (let row of rows) { console.log(`${row.dataValues.id}t${row.dataValues.name}`); } sequelize.close(); });
With Bookshelf:
#!/usr/bin/env node // $ npm install bookshelf knex pg const connection = require('./connection.json'); const knex = require('knex')({ client: 'pg', connection, // debug: true }); const bookshelf = require('bookshelf')(knex); const Item = bookshelf.Model.extend({ tableName: 'item' }); // select "item".* from "item" where "type" = ? Item .where('type', 'veg') .fetchAll() .then(result => { console.log('Veggies:'); for (let row of result.models) { console.log(`${row.attributes.id}t${row.attributes.name}`); } knex.destroy(); });
With Waterline:
#!/usr/bin/env node // $ npm install sails-postgresql waterline const pgAdapter = require('sails-postgresql'); const Waterline = require('waterline'); const waterline = new Waterline(); const connection = require('./connection.json'); const itemCollection = Waterline.Collection.extend({ identity: 'item', datastore: 'default', primaryKey: 'id', attributes: { id: { type: 'number', autoMigrations: {autoIncrement: true} }, name: { type: 'string', required: true }, type: { type: 'string', required: true }, } }); waterline.registerModel(itemCollection); const config = { adapters: { 'pg': pgAdapter }, datastores: { default: { adapter: 'pg', host: connection.host, port: connection.port, database: connection.database, user: connection.user, password: connection.password } } }; waterline.initialize(config, (err, ontology) => { const Item = ontology.collections.item; // select "id", "name", "type" from "public"."item" // where "type" = $1 limit 9007199254740991 Item .find({ type: 'veg' }) .then(rows => { console.log('Veggies:'); for (let row of rows) { console.log(`${row.id}t${row.name}`); } Waterline.stop(waterline, () => {}); }); });
With Objection:
#!/usr/bin/env node // $ npm install knex objection pg const connection = require('./connection.json'); const knex = require('knex')({ client: 'pg', connection, // debug: true }); const { Model } = require('objection'); Model.knex(knex); class Item extends Model { static get tableName() { return 'item'; } } // select "item".* from "item" where "type" = ? Item .query() .where('type', '=', 'veg') .then(rows => { for (let row of rows) { console.log(`${row.id}t${row.name}`); } knex.destroy(); });
The syntax for a simple read
operation varies greatly between these examples. As the operation you’re trying to perform increases in complexity, such as operations involving multiple tables, the ORM syntax will vary from between implementations even more.
There are at least dozens of ORMs for Node.js alone, and at least hundreds of ORMs for all platforms. Learning all of those tools would be a nightmare!
Lucky for us, there are really only a few SQL dialects to worry about. By learning how to generate queries using raw SQL you can easily transfer this knowledge between different platforms.
Recall that the purpose of ORM is to take the underlying data stored in a database and map it into an object that we can interact within our application. This often comes with some inefficiencies when we use ORM to fetch certain data.
Consider, for example, the queries we first looked at in the section on layers of abstraction. In that query, we simply wanted a list of ingredients and respective quantities for a particular recipe. First, we made the query by writing SQL by hand. Next, we made the query by using the query builder, Knex. Finally, we made a query by using the ORM Sequelize. Let’s take a look at the queries which have been generated by those three commands.
pg
driver.This first query is exactly the one we wrote by hand. It represents the most succinct method to get exactly the data we want:
SELECT ingredient.*, item.name AS item_name, item.type AS item_type FROM ingredient LEFT JOIN item ON item.id = ingredient.item_id WHERE ingredient.dish_id = ?;
When we prefix this query with EXPLAIN
and send it to the PostgreSQL server, we get a cost operation of 34.12
.
This next query was mostly generated for us, but due to the explicit nature of the Knex query builder, we should have a pretty good expectation of what the output will look like:
select *, "item"."name" as "item_name", "item"."type" as "item_type" from "ingredient" left join "item" on "item"."id" = "ingredient"."item_id" where "dish_id" = ?;
New lines have been added by me for readability. Other than some minor formatting and unnecessary table names in my handwritten example, these queries are identical. In fact, once the EXPLAIN
query is run, we get the same score of 34.12
.
Now let’s take a look at the query generated by ORM:
SELECT "dish"."id", "dish"."name", "dish"."veg", "items"."id" AS "items.id", "items"."name" AS "items.name", "items"."type" AS "items.type", "items->ingredient"."dish_id" AS "items.ingredient.dish_id", "items->ingredient"."item_id" AS "items.ingredient.item_id", "items->ingredient"."quantity" AS "items.ingredient.quantity", "items->ingredient"."unit" AS "items.ingredient.unit" FROM "dish" AS "dish" LEFT OUTER JOIN ( "ingredient" AS "items->ingredient" INNER JOIN "item" AS "items" ON "items"."id" = "items->ingredient"."item_id" ) ON "dish"."id" = "items->ingredient"."dish_id" WHERE "dish"."id" = ?;
New lines have been added by me for readability.
As you can tell this query is a lot different from the previous two queries. Why is it behaving so differently? Well, due to the relationships we’ve defined, Sequelize is trying to get more information than what we’ve asked for. In particular, we’re getting information about the dish
itself when we really only care about the ingredients
belonging to that dish
.
The cost of this query, according to EXPLAIN
, is 42.32
.
Not all queries can be represented as an ORM operation. When we need to generate these queries, we have to fall back to generating the SQL query by hand. This often means a codebase with heavy ORM usage will still have a few handwritten queries strewn about it. The implications here are that, as a developer working on one of these projects, we end up needing to know BOTH the ORM syntax as well as some underlying SQL syntax.
A common situation that doesn’t work too well with ORMs is when a query contains a subquery. Consider the situation where I know that I have already purchased all the ingredients for dish #2 in our database, however, I still need to purchase whatever ingredients are needed for dish #1. In order to get this list I might run the following query:
SELECT * FROM item WHERE id NOT IN (SELECT item_id FROM ingredient WHERE dish_id = 2) AND id IN (SELECT item_id FROM ingredient WHERE dish_id = 1);
To the best of my knowledge, this query cannot be cleanly represented using the aforementioned ORMs. To combat these situations it’s common for ORM to offer the ability to inject raw SQL into the query interface.
Sequelize offers a .query()
method to execute raw SQL as if you were using the underlying database driver. With both the Bookshelf and Objection ORMs, you get access to the raw Knex object which you provide during instantiation and can use that for its query builder powers.
The Knex object also has a .raw()
method to execute raw SQL. With Sequelize, you also get a Sequelize.literal()
method which can be used to intersperse raw SQL in various parts of a Sequelize ORM call.
But in each of these situations, you still need to know some underlying SQL to generate certain queries.
In previous sections, we gave reasons to why ORM may not be required. However, as the most popular Node.js ORM and with around 1.35 million weekly downloads at the time of writing this article, Sequelize puzzles us with its popularity.
Although we made valid points in the previous section, we have to note that 1.35 million people cannot be wrong weekly. So let’s look at some of the reasons one might use Sequelize in the first place:
sequelize.query
method for writing raw queries where this is preferredUsing the low-level database driver modules is rather enticing. There is no overhead when generating a query for the database as we are manually writing the query. The overall dependencies our project relies upon are also minimized. However, generating dynamic queries can be very tedious, and in my opinion, is the biggest drawback of using a simple database driver.
Consider, for example, a web interface where a user can select criteria when they want to retrieve items. If there is only a single option that a user can input, such as color, our query might look like the following:
SELECT * FROM things WHERE color = ?;
This single query works nicely with the simple database driver. However, consider if the color is optional and that there’s a second optional field called is_heavy
. We now need to support a few different permutations of this query:
SELECT * FROM things; -- Neither SELECT * FROM things WHERE color = ?; -- Color only SELECT * FROM things WHERE is_heavy = ?; -- Is Heavy only SELECT * FROM things WHERE color = ? AND is_heavy = ?; -- Both
However, due to the aforementioned reasons, a fully featured ORM isn’t the tool we want to reach for either.
The query builder ends up being a pretty nice tool in these situations. The interface exposed by Knex is so close to the underlying SQL query that we are forced to always know what the SQL query looks like. This relationship is similar to how something like TypeScript translates to JavaScript.
Using a query builder is a fine solution as long as you fully understand the underlying SQL it’s generating. Never use it as a tool to hide from what is happening at a lower layer. Only use it as a matter of convenience and in situations where you know exactly what it’s doing.
If you ever find yourself questioning what a generated query actually looks like you can add a debug field to the Knex()
instantiation call. Doing so looks like this:
const knex = require('knex')({ client: 'pg', connection, debug: true // Enable Query Debugging });
In fact, most of the libraries mentioned in this post include some sort of method for debugging the calls being executed.
From our discussion so far, we have seen the weak points of ORM. However, in this subsection, we will look at certain scenarios where ORM is a good choice.
Generally, since ORMs provide a high-level abstraction over a database, it offers less control than a raw query. And for this reason, it is slower in performance than raw query. But to make up for these downsides, ORM often ships with a lot of great features:
In summary, use ORM to achieve code standardization, security, maintainability, language abstraction, DRY, etc.
So far, we have learned about some Node.js ORM and query builders. However, picking the “best” ORM for Node.js is not easy because they all have pros and cons. And in my opinion, the best Node.js ORM changes depending on your application needs.
Previously, we classified libraries used to query and manipulate data into three categories based on their level of abstraction:
Since there is no ORM that solves all your problems, the best ORM is the ORM that is most suitable for your application need. And with the information above, you should know what you are willing to trade and what your application requires. Consequently, you can choose which ORM is best for your Node.js application.
We’ve looked at three different layers of abstracting database interactions, namely the low-level database drivers, query builders, and the high-level ORMs.
We’ve also examined the tradeoffs of using each layer as well as the SQL queries being generated, including the difficulty of generating dynamic queries with a database driver, the added complexity of ORMs, and finally the sweet spot of using a query generator.
Thank you for reading and be sure to take this into consideration when you build your next project.
Once you’re done following along, you may run the following commands to completely remove the docker container and remove the database files from your computer:
Deploying a Node-based web app or website is the easy part. Making sure your Node instance continues to serve resources to your app is where things get tougher. If you’re interested in ensuring requests to the backend or third-party services are successful, try LogRocket.
LogRocket is like a DVR for web and mobile apps, recording literally everything that happens while a user interacts with your app. Instead of guessing why problems happen, you can aggregate and report on problematic network requests to quickly understand the root cause.
LogRocket instruments your app to record baseline performance timings such as page load time, time to first byte, slow network requests, and also logs Redux, NgRx, and Vuex actions/state. Start monitoring for free.
docker stop pg-node-orms docker rm pg-node-orms sudo rm -rf ~/data/pg-node-orms
Would you be interested in joining LogRocket's developer community?
Join LogRocket’s Content Advisory Board. You’ll help inform the type of content we create and get access to exclusive meetups, social accreditation, and swag.
Sign up nowExplore use cases for using npm vs. npx such as long-term dependency management or temporary tasks and running packages on the fly.
Validating and auditing AI-generated code reduces code errors and ensures that code is compliant.
Build a real-time image background remover in Vue using Transformers.js and WebGPU for client-side processing with privacy and efficiency.
Optimize search parameter handling in React and Next.js with nuqs for SEO-friendly, shareable URLs and a better user experience.
84 Replies to "Node.js ORMs: Why you shouldn’t use them"
Really? Discussing the reasons why you should NOT use ORM? What about the reasons you SHOULD use ORM? The level of abstraction ORM can offer is elementary when dealing with large projects. And it can be beneficial in smaller projects as well. ORM has been proven in use for many years and has simplified the maintenance of many projects. And that you can use ORM incorrectly is a truism, as with every kind of framework. I think you’re just wrong when rejecting ORM as a solution.
Not really convinced you should NOT use ORM either. I think what you advocating for in this blog post is more like an anti-pattern. We probably are in the era of microservices architectures and we want more simplicity of agility in software development but if you don’t at least structure your software with proven design patterns you’re just producing bad quality software difficult to maintain and to scale.
You should find some gum to chew :B
I’m not sure what bug is under the skin of the other people commenting here, I think they hastily read the title and got frustrated, but I thought this was a well-written, succinct, and fair post. Maybe the title is baity, but the content was good and completely fair to ORMs. I think some of the comments got hasty and did not actually read the post. Thanks for succinctly pointing out some of the real dangers and common edges people brush up against when using ORMs.
I was really curious when I landed on this page to understand why I should NOT use sequelize with NodeJS. But very disappointed with explanation.
#1: This is not so true. You are NOT learning wrong. When you get some incorrect results, we always have to debug the sql log printed, so it is not correct.
#2: Complex ORM calls can be difficult. Believe me, you are totally wrong here. See this following code I had to write to get the products, along with the child tables data:
const sequelizeCondition = {
where: {
active: 1
},
attributes: [‘id’, ‘name’, ‘slug’, ‘description’, ‘ogDescription’, ‘metaTitle’, ‘metaDescription’, ‘ogTitle’, ‘order’, ‘type’, ‘aboutProduct’, ‘active’],
include: [
{
model: db.productVariation,
as: ‘productVariations’,
where: {
active: 1
},
attributes: [‘id’, ‘price’, ‘highPrice’, ‘qty’, ‘sku’, ‘size’, ‘color’],
required: false,
include: [{
model: db.image,
as: ‘images’,
required: false,
where: {
active: 1
},
attributes: [‘id’, ‘src’, ‘alt’, ‘title’, ‘order’],
}]
},
{
model: db.review,
as: ‘reviews’,
where: {
active: 1,
},
attributes: [‘id’, ‘review’, ‘rating’],
required: false,
include: [{
model: db.user,
attributes: [‘id’, ‘firstName’, ‘lastName’, ‘profilePic’],
required: false,
where: {
active: 1
}
}]
},
{
model: db.sizeChart,
as: ‘sizeCharts’,
where: {
active: 1,
},
required: false,
// include: [{
// model: db.user,
// attributes: [‘id’, ‘firstName’, ‘lastName’, ‘profilePic’],
// required: true,
// where: {
// active: 1
// }
// }]
},
{
model: db.productCategoryMapping,
where: {
active: 1,
},
required: false,
include:[{
model: db.category,
where: {
active: 1
},
required: true,
attributes: [‘id’, ‘name’, ‘slug’, ‘order’],
}]
},
]
};
It is purely logical and as simple as any other SQL query can be.
#3: Again, we can simply write a procedure for something we are unable to achieve in Sequelize.
Thanks,
V
Being careful with ORM lead our projects using an intermediate SQL helper tool. It turned out to be very stable and low maintenance while ORM approaches are often a maintenance nightmare …
I agree.
The fact that vasty majority of people, including LogRocket and Martin Fowler using ORM the wrong way, doesn’t make all people use it the wrong way.
Talk whatever you like. You got great SEO ranking right there, so I feel like writing some comment here.
ORM has its merit and appropriate use case.
Just don’t design your business logic around ORM.
Read more details here:
https://blog.cleancoder.com/uncle-bob/2013/10/01/Dance-You-Imps.html
He publishes the article for quite some time, but I rediscover the same thing independently.
Nope, I am not gonna avoid using ORM. I use it for mainly save, load, and some transaction capability to simplify things.
Good luck with your “sweet spot” but bashing ORM under the wrong assumption is not cool.
I think that some comments above are written without reading at all the post content.
Besides this, i can’t understand IN ANY WAY how an ORM may prevent from understand any database query. It’s abstract i know, but whatever ORM you try you’ll smash your head again some “Where” or “Select”, and you’ll understand in broad terms what is a Join.
I don’t agree neither with the fact that they are SO different. They have differents building,we can see it all. But in terms of abstraction ARE ALL THE SAME. So, you can’t understand a book if some periods are just shifted? Can’t follow a road if signals are in another language? Road is still a road, and book is still a book. The abstraction of ORMs looks so similar to me, i can’t really understand how someone can say that you may feel “disoriented” passing from one to another.
Last but not least, we’re discussing of delays of seconds. An ORM have helped me save HOURS OF MY TIME for queries. More needs to be said?
I have a hard time figuring out why I’m so resistant to even give ORM a try. It just “feels” wrong; but that’s probably because I’m a minimalist. I just don’t trust someone to generate and manage my SQL, having spent my share of adulthood looking at really complex Query Analyzer graphs to figure out what indexes to add, and how to order the fields so as to make it applicable to a specific query, but also to as many other queries as possible. Designing a truly robust enterprise-grade RDMBS is a momentous undertaking. And ORMs are a pathetic attempt at abstracting out its complexities.
The irony is that Martin Fowler, in the article you link to, doesn’t come to the conclusion you do. To whit:
> So ORMs help us deal with a very real problem for most enterprise applications. It’s true they are often misused, and sometimes the underlying problem can be avoided. They aren’t pretty tools, but then the problem they tackle isn’t exactly cuddly either. I think they deserve a little more respect and a lot more understanding.
Ref: https://martinfowler.com/bliki/OrmHate.html
As a developer with more than 30 years of application development experience using plan old SQL and many ORMs, I agree with your conclusion. Looks like we are still in the minority judging from the comments.
you are right. I have seen in the industry how complicate a sql query can get over time. Maintaining such huge complicated queries can be nightmare in ORM over time as the creators will move on to other jobs. I have spent time to learn loop back and sequeluze, but were not happy when we find we need to write sql first then test it then export it to use it at orm. Truly hassle . For small task these orms seems blessings but when need grows it will become miserable
How did Martin Fowler use ORM the wrong way?
Unfortunately this post is a coming short in why actually now a query builder is “the sweet spot”.
I am not able to find good reasons why the second layer of abstraction is doing a good job?
More concrete examples would be great. As with a query builder, you still have to build a lot of basic functions. Functions that a ORM performs at its basic layer. So you would be using your own functions on top of a query builder to create, read, update and delete items of a table. Boom, you created another ORM, just in your own style.
I find as an analogy to this topic, I see the same with people using HTML forms to perform HTTP calls.
You might be able to set up a HTML form and make it send some data to the server. It is a simple abstraction when you want to do one thing: submitting user data to a server with a page redirect/reload. But for anything else you need good knowledge about HTTP, once you turn to AJAX.
I see it the same with ORMs, everyone is able to do a simple Item.find() query, but once you need more you need to dig deeper. It is the same with every abstraction. Specific tools are designed to do specific tasks. This is what makes them so powerful. You use ORMs not to abstract everything away, but most of the functions you need daily, and make that simple to use. If I’d have complex queries, I’d turn to GraphQL and use join-monster to do the hard work. You can’t have one solution to solve world hunger. There will always be solutions on top of solutions.
Nowadays I’d turn to Strapi to do the bulk work of organizing my Rest or GraphQL API on top of an ORM that I can easily extend when needed.
ORM’s absolutely can become a nightmare especially with more and more and more and more joins. THEY’RE performance nightmares. Just because you guys didn’t take the time to learn and implement what he is saying doesn’t mean he’s wrong. I guarantee you for any large scale db schema that your joins are sucking the life outta your application but hey what does it matter to you.. you don’t have to deal with it only the person retrieving your slow crawling data does.
TLDR he’s right and you’re wrong deal with it.
I personally can’t see any point in learning one new thing to avoid having to learn another new thing. I started to learn sequelize and really disliked it so just went back to writing SQL. Just couldn’t see the point in introducing an unnecessary and complicated layer of abstraction.
Man… I been singing this song for about 10 years now, ever since I spent months undoing the N+1 nightmares created by ActiveRecord.
People said I was crazy then and they say you’re crazy now.
As I see it, I can either spend time mastering the ORM, or I can master SQL itself. I’ve discovered there are some stupendous features in postgresql for example, like CTEs, range types, fuzzy string matching, and array_agg that I would be horrified to give up at this point.
Shine on you crazy diamond!
Thanks for the article man.
I totally agree and think that the negative comments are from people that didn’t read everything or didn’t get the point.
I think that ORM is good for smaller projects but you should know how to use it in your architecture and how to use it with more complex situations.
Almost every client project I work on have the problem that the developers are using the ORM mapped data structures as domain objects. Also using ORM can result in some crazy bugs when the ORM package makes a mistake and you end up debugging the raw queries to find the problem..
I never use any ORM for my own projects anymore, for clients I adjust when the developers are ORM minded or the project itself already uses it.
+1
I agree, it’s a great post.
Yes, it depends, ORMs can be bloated and not scalable, BUT it beats some alternatives like creating SQL using strings
Seems like you completely missed the point of ORM:
– the main purpose of ORM is decoupling persistence layer (database) from your application layer (not “easier use” or other non-sense)
– with any ORM you can still write your own queries (as with any low lever “driver”)
… there just these two points dismiss all “uneducated” arguments in this blog post.
I agree with it.
In a REST project to handle search API, we had about throughput of 18 per second, after removing ORM and optimizing the query, we managed to reach about 500.
SQL Builders are sweet.
But, ORMs are perfect for large projects, as it cause the project to fail and then I have a job to do, wiping away the ORM and optimizing the queries 😉
But one good thing about ORM is database portability, which I do not care as I choose the database for each part of the project and changing it is out of the question.
By the way give ‘sql-bricks’ a try it is a nice sql builder. It has also a special feature for PostgreSQL.
You are correct for sure. Java projects of mine rely on ORMs and are a large nightmare without. JDBC requires so much extra work that just isn’t needed. Sure smaller projects don’t require it, but it is for sure useful. And this author person doesn’t know that SQL is still used a lot with ORMs. And also one of the most important things learned by programmers is that less code is less hassle. And every programmer needs to know that running everything in the database isn’t a “best-practice” tip taught in school.
I Thing too much JavaScript, and smaller or medium size project has made this author think that orm is bad, try work with technology like .net, java, you will start having headache when your application starts to grow large, and many data manipulation is required. ORM is your friend and not your enemy.
It seems you’ve made the crucial mistake of actually reading the article you’re referencing. 🙂
ORMs are high level layers of indirection, modeling and replicating the DB model, due to just one main reason, developers don’t want to lear not just SQL, but DDL, DDM, DB normalizations, etc. I agree with the author, and would add another reason, most ORM frameworks, with the exception of Spring ORM, precludes the power of using DB stored procedures for example, what substracts a big deal of power to the developer toolbox. maintenance of the DB model, not just at DB model, but at the upper lever of the ORM, is … a bit more than a nightmare, and there is not a single mention to the fact that ROM frawks add tables, virews, etc… so, more processes running on the server, somtimes in a totally opaque way.
Regarding the most used reason of the DB portability, In almost 50 years on the industry I have seen a complete DB migration from one RDBMS to another, one single time! Very low frequency to be a good reason !
I agree with the Author, it is best learning SQL, stored procs, views, and learn to deal with the DB, first hand.
If you are writing your own queries for your ORM you are not decoupled anymore.
As a beginner, I found this really interesting. Thanks. I agree with one commenter above that it would be helpful to see a more detailed explanation of how a query builder makes the choosing a color option problem easier to solve than it would be using just SQL. But that’s probably because I’m a beginner. 🙂
Also, it is disappointing to see people react so negatively to a blog post. Oh well.
Very nice Article, specially a lot of efforts done to take this research, Thank you very much for great efforts. I believe you have very good knowledge of topic. but I think ORM is best to use but in NodeJS we have to use something like Knex + Objection which is quit good combination. As in rails we use ActiveRecord and it is very helpful. As for performance in most of solution nobody care about. In raw queries people forget security most of time, so ORM bound them to all security.
ORM’s change, they might be good for getting things out quickly, but 3 years later, they become the biggest pain. Everything works, but the ORM”s outdate so fast. In a proper design, the database tables get abstract away by the SQL and scripts/ stored procs. ORM’s do the opposite. Tight coupling to the tables. The only benefit I can see for ORM’s is that some of them allow one to change the database vendor/ database underneath quickly. Stored procs do the same – unfortunately not all database support stored procs and their language differs. If you stick to the main databases this should not be a problem.
I don’t agree fully with the author. I see his point and understand his reasons to use the lower level adapters to speed up queries. Of course It can depend on a type of a project, but a large enough project that has to deal with 25+ models constantly changing will benefit most likely from using an ORM to avoid writing CRUD operations manually, plus managing schema changes separately from the code is an extra overhead (migrations are very important part of ORM). Talking about speed, there is a ceiling of what typical sql database can handle anyway and companies naturally progress to additional horizontally scalable tools to speed up things like distributed cache, data warehouse, sharding etc. Fast sql queries won’t be enough at certain level.
ORM boys got angry!
ORMs are great for CRUD operations, _but in my personal experience_, then very soon become a burden and I find myself losing time looking on some heavily opinionated codebase to figure out how to generate a query I can easily write myself. Also — and specially for new programmers — hiding SQL leads to lack of understanding of relational databases and application suffer as soon as data grow a few hundred MB.
It means you have the liability of ORM (setup, dependency management, learn dialect) for the easy CRUD operations and, then, rely on SQL for the more advanced features? It seems legit to me, but:
– it’s not easy to play with SQL queries together with ORM models, so you end up with two mappings, one using ORM models and another different using POJO or arrays, with completely different interfaces;
– if the contributors base is not used to write SQL how you think the quality of the SQL written for the non-trivial cases will be?
I started out using ORMs and I can tell you for a fact that I did not need to learn proper SQL to use them. That was limiting (I was also a noob developer, so there was that)
Later on I learned SQL and started writing raw SQL queries. You know what? I liked it a lot more because I could just take queries right out of a tool like PG Admin, paste it into my code, and I knew it would work. I still do that for complex queries.
Knex hits a sweet spot for me, too. It’s so sql-like that I can see what is going on without having to burn any brain cells translating it. It returns a promise, which is convenient, and it has a very concise syntax. I tried using it with Bookshelf for awhile but down the road I just went back to Knex by itself.
No. The author is quite correct. He’s simply warning you about the consequences of boiling the ocean when you just need a cup of tea. These days, people try to abstract everything away to build any size project, I say no if you are doing it in software, but yes if you are creating hardware. I have been writing this stuff for 20 years, (Every day). ORM’s are only good for learning how to build code generators that create that cup of tea that you need. Use or create your own code generators that creates just what you need, when you need it.]
So Really! Not using ORM is a very smart thing not to do.
Really!
I think the only time to use ORM is when your software must support a variety of databases (e.g. distributed open source software). When you are in control of the execution environment, you will be more likely to have to change the ORM than the database.
I still don’t get this post. When dealing with complex queries you can make an ORM execute a raw query string but when dealing with simple queries the ORM works really well. Most of the time an application uses simple queries but if you’ve created an application which is using a complex query all the time then I must say that instead of learning SQL you must first learn database design because most likely your entire schema sucks.
thanks for warnings.. i was confused about it.. by your article with answers i reach to solution for my learning path.. thanks a lot..
I think subqueries are super useful, common, and often used … yet the ORM requires you to write “raw” SQL to achieve it. Are you saying anyone who uses a subquery in their design has a schema that “sucks”? I’m gonna have to strongly disagree.
Great writeup! Yes one should really consider the pros and cons before coming to a decision like using an ORM in a high traffic production system.
Well, having done this for like 32 years, including a bunch of years as a RDBMS administrator and query optimizer on staff, eeeehhhhhhhhhh…. ORMs are great for getting stuff done quickly and more simply, and with actual objects with predictable shapes and functions.
Should every developer fully understand all the SQL goodness underneath? Probably, yeah. But ORMs let you (most of the time) not really care what the data layer is and lets you prototype on one layer locally and deploy to another, a clustered setup, a DOCKER’d setup, a REST setup, or a host of other things, without really a lot of changes or too-configurable-for-stability code building.
On balance, ORMs are good.
Reminds me to that argument on whether we should use high level programming languages or stick to assembly.
Yeah this is old and I came here because it appeared on a google search so I was curious.
On in your simple example, you say you don’t want data from the table dish, so why did you ask for it. Just because you have an association defined in the model does not mean you have to ask for it every time. You never show us your call to retrieve the data. In Sequelize you are obviously calling the dish model to get your results, why not call from the ingredients model and if there is an association with dish just don’t include it.
Simplified foo example
const ingredients = await Ingredients.findAll({ where: { itemid: itemid, include: item });
Your dissing an ORM when it is obviously a developer mistake you made.
Also if you don’t want a Left Outer Join then just include the required: true parameter and you won’t get it.
Yeah, this post illustrates a few things:
1. You hadn’t used a great ORM when you wrote this. The Django ORM, for example, is a great ORM and much better than the shitty javascript/typescript ones. Docs are fantastic, too.
2. You didn’t realize the power in hooking up a great ORM to other integration points (Django and Rails can spit out authenticated CRUD endpoints in REST from a single table definition with about 5 additional lines of code, for instance.)
3. You discount the day-to-day drudgery involved in quickly prototyping or bootstrapping a project which may involve cranking out a lot of code where writing out a bunch of simple operations in SQL is unproductive at best. Most people, at the end of the day, are writing web apps, and so, on balance, most projects would do well to have a good ORM.
4. You believe that learning an ORM is such a big investment that it inhibits you from actually learning SQL. Personally, I find this pretty laughable as neither SQL or ORMs are rocket science or even particularly hard, but I suppose it’s all relative to the time invested.
5. You apparently think that you might switch ORMs a lot? Who cares how many syntaxes there are for various (crappy) JavaScript ORMS – you’re not gonna use more than 1 or 2, most likely. If you switch jobs to a new place where they use the same language with a new ORM let’s face it: learning some new ORM syntax with plenty of examples in the code will be the least of your problems while you’re figuring out a new code base.
Also, the people saying ORMS are ruining your performance: you’re focusing on an almost irrelevant problem, practically speaking.
I’m old enough to have read “SQL for Smarties” in paperback at the turn of the century but 99% of the time I’ve never needed that kind of knowledge (though it’s super fun to bust it out when called for!)
Yes, there’s going to be a few hot spots in your code where you can mess up the joins in ORMS, but they’re probably between 1% to 5% of the SQL you’ll actually create in an ORM. Simple profiling (or even just minding the slow query log in your database) is the bare minimum for being a competent developer. Also, if you know even a TINY bit about SQL you’ll have a hunch when the ORM might generate a bad query plan.
Either way any decent ORM allows you to print the SQL plan, then tweak things or even drop down to raw SQL if you need to. Focusing on the low percentage of optimization problems you could temporarily run into while disregarding the vast majority of cases where it stops you writing tons of boilerplate code is silly.
I don’t agree with the author.
I haven’t tried Node.js ORMs, I actually got here trying to figure out why Node.js ORMs weren’t as popular as I thought.
Please, If you haven’t tried, check out the Django ORM or how FastAPI integrates with SQLAlchemy.
Honestly, don’t you get tired of writing the same database schema that supports the same CRUD operations over and over?
Or tired of updating the SQL schema and migration scripts with the updated table schema, plus the SQL query in your code, plus the serializer / de-serializer… every time the schema changes?
Those are manual tasks that are prone to error that could end in production.
I advocate for automating everything, and ORMs are part of that automation.
You do not use an ORM for its querying capabilities, but rather for its capability to abstract from storage considerations.
Benchmarking an ORM with queries is like testing a cruise ship for its speed.
– Focus on your business logic, implement it within your domain models,
– Implement a service layer, where each controller provides a business use case, by :
1. fetching domain models from your ORM
2. orchestrating invocation of BL methods/operations on your domain models
3. persisting your updated/created domain models
– Yes, it’s “slow”. But it’s clean, production-ready and works damn fine !
One of the most important reasons to use ORM is preventing SQL injections. Personally I don’t like adding an abstraction layer to my codes until I have a good reason for that, but I can not ignore the treats of SQL injection and I don’t like to manually validate all inputs from users to see if there is a treat in it!
Well, I think it depends, … if you need to support multiple DBMS, perform dynamic query building and performing CRUD queries then using an ORM is fine, when you need to deal with low level SQL optimization, complex query building, dynamic structure at runtime etc … then sometimes you’ll have hard times.
You can always opt for a mix of ORM and not-ORM, … but as for all the ther things this is something that should be done balancing pros and cons.