Why do you need to avoid using ORM (including the Node.js example)

Time:2019-10-9

Why do you need to avoid using ORM (including the Node.js example)

In this article, we will discuss why you shouldn’t use it in your project.ORM(Object Relational Mapping).
Although the concepts discussed in this article apply to all languages and platforms, the code samples useJavascriptPreparedNodejsTo illustrate, and fromNPMGet packages in the library.

First of all, I do not intend to diss any of the modules mentioned in this article. Their authors have put a lot of hard work into it. At the same time, they are used by many applications in production environments and respond to a large number of requests every day. I also use it.ORMI have deployed the application and I don’t regret it.

Keep up!

ORM is a powerful tool. The ORM we will study in this article can communicate with the SQL backend, for exampleSQLite, PostgreSQL, MySQLandMSSQLThis is the case. This example will usePostgreSQLIt is a powerful SQL server. There are others.ORMCan andNoSQLCommunications, e.g. byMongoDBSupportingMongoose ORMTheseORMIt is beyond the scope of this discussion.

First, run the following command to start a localPostgreSQL instance, which will be configured in such a way that requests for local port 5432 (localhost: 5432) will be forwarded to the container. At the same time, the file will be stored in the root directory, and the subsequent instantiation will save the data we have 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 we will have a database where we can create new tables and insert data. This will enable us to query data and better understand the abstraction layers, and run the next command to enter PostgreSQL interaction.

docker run 
  -it --rm 
  --link pg-node-orms:postgres 
  postgres 
  psql 
  -h postgres 
  -U orm-user 
  orm-db


At the prompt, enter the password in the last block of code.hunter12This is the case. When the connection is successful, copy the following query code and execute it.

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');

Now that you have a populated database, you can enter it now.quitDisconnect from PSQL and re-control the terminal. If you need to enter the original SQL statement again, you can run it again.docker runOrder.

Finally, you need to create oneconnection.jsonFile, as shown below. This file will be used later by Node applications to connect to databases.

{
  "host": "localhost",
  "port": 5432,
  "database": "orm-db",
  "user": "orm-user",
  "password": "hunter12"
}

Abstraction layer

Before delving into too much code, let’s figure out some different abstraction layers. Like all other computer science, we have to weigh and balance when we add abstraction layers. Every time we add an abstraction layer, we try to improve developer productivity at the cost of performance degradation (though not always).

Bottom layer: database driver

Basically the lowest level we can reach, and then we will manually generate TCP packets and send them to the database. The database driver handles operations connected to the database (sometimes the connection pool). In this layer, we will write the original SQL statement and send it to the database and receive the response. stayNode.jsIn the ecosystem, there are many libraries running in this layer. Here are three most popular libraries:

  • mysql: MySQL (13k stars / 330k weekly downloads)
  • pg: PostgreSQL (6k stars / 520k weekly downloads)
  • sqlite3: SQLite (3k stars / 120k weekly downloads)

These libraries basically work in the same way:

  • Get database credentials,
  • Instantiate a new database instance.
  • Connect to the database.
  • Then the query is sent to it as a string and the result is processed asynchronously.

Here is a simple example, usingpgThe module obtains the list of raw materials needed to make 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();
});

Middle tier: query constructor

This layer is between using a simple database driver and a mature ORM.
The most famous modules running at this level areKnexThis is the case. This module can generate queries for several different SQL languages. This module relies on several database-driven libraries mentioned above — you need to install specific libraries to use them.Knex

  • Knex:Query Builder (8k stars / 170k weekly downloads)

EstablishKnexWhen the instance is executed, the connection details and the SQL language you plan to use are provided, and then you can start querying. The queries you write will be very similar to the basic SQL queries. One advantage is that you can generate dynamic queries programmatically in a more convenient way than concatenating strings together to form SQL (which usually causes security vulnerabilities).

Here is a useKnexAn example of a module acquiring a list of cooking Chicken Tikka Masala materials:

#!/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();
});

Upper layer: ORM

This is the highest level of abstraction we are going to discuss. When we useORMWhen you use it, you have to configure it a lot before you use it. Seeing the name of a thing one thinks of its function,ORMThe point is to map records in relational databases to objects in the application (typically a class instance, but not all of them). This means that we define the structure and relationships of these objects in the application code.

  • sequelize: (16k stars / 270k weekly downloads)
  • bookshelf: Knex based (5k stars / 23k weekly downloads)
  • waterline: (5k stars / 20k weekly downloads)
  • objection: Knex based (3k stars / 20k weekly downloads)

In the following example, we will look at the most popularORMSequelizeThis is the case. We will also use it.SequelizeOn primitivenessPostgreSQLModeling the relationships represented in the schema, here is a use ofSequelizeAn example of a module acquiring a list of cooking Chicken Tikka Masala materials:

#!/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();
});

Now that you’ve seen examples of how to use different abstraction layers to execute similar queries, let’s take a closer look at why you should be cautious about using ORM.

Reason 1: You’re learning the wrong things.

Many people chooseORMBecause they don’t want to spend time learning basic SQL, people usually think that SQL is difficult to learn, and by learning ORM, we can use one language instead of two to write applications. At first glance, this seems to be a good reason.ORMIt will be written in the same language as the rest of the application, and SQL is a completely different grammar.

However, there are problems in this way of thinking. The problem isORMRepresents some of the most complex libraries you can use.ORMIt’s not easy to learn from inside to outside because of its large size.

Once you have a specific ORM, this knowledge may not be well applied in other languages. Suppose you switch from one platform to another (e.g. JS/Node.js to C#/NET). But perhaps even harder to consider is if you are on the same platform from oneORMSwitch to another, for exampleNodejsMedium obedienceSequelizeSwitch toBookshelfThis is the case. For example:

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();
  });

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();
  });

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, () => {});
    });
});

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();
  });


Between these examples, the syntax of simple read operations varies greatly. As the complexity of the operations you are trying to perform increases, such as those involving multiple tables, the ORM syntax will differ greatly between different implementations.

Node.js alone has at least dozens of ORMs, and all platforms have at least hundreds of ORMs. Learning all these tools will be a nightmare!

Fortunately for us, we only need to learn a limited number of SQL languages. By learning how to use raw SQL to generate queries, you can easily transfer this knowledge between different platforms.

Reason 2: Complex ORM calls are inefficient

Think about it.ORMThe goal is to capture the basic data stored in the database and map it to objects that we can interact with in the application. When we use ORM to get some data, it usually leads to some inefficiencies.

For example, take a look at the queries we made in the abstraction layer chapter. In this query, we only need a list of ingredients and their quantities for a particular formulation. First, we query by writing SQL manually. Next, we use the query constructor Knex to query. Finally, we useSequelizeQueries were made.
Let’s look at the queries generated by these three commands:

Writing SQL manually with “pg” driver

The first query is the one we write manually. It represents the simplest way to get the required data.

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 add the queryEXPLAINPrefix and send it toPostgreSQL server costs 34.12.

Generation with “knex” query constructor

The next query is mainly generated by Knex, but due to the clear features of Knex query constructor, there should be a good performance expectation.

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" = ?;

For ease of reading, I added line breaks. These queries are the same except for some minor formats and unnecessary table names in my handwritten example. In fact, runEXPLAINAfter querying, we got a score of 34.12.

Generation with “Sequelize” ORM

Now let’s look at the queries 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" = ?;

For ease of reading, I added line breaks. As you can see, this query is very different from the first two queries. Why do you behave so differently? Because of the relationship we have defined,SequelizeTry to get more information than we ask for. To put it bluntly, when we only care about the ingredients that belong to the dish, we will get information about the dish itself. according toEXPLAINAs a result, the cost of this query is 42.32

Reason 3: ORM is not omnipotent

Not all queries can be represented asORMOperation. When we need to generate these queries, we have to go back and manually generate the SQL queries. This usually means a lot of use.ORMThe code base will still have some handwritten queries. This means that as developers working on one of these projects, we ultimately need to understand at the same time.ORMGrammar and some basic SQL grammar. A common scenario is when a query contains subqueries.ORMUsually it doesn’t work well. Considering this situation, I would like to query the database for all the ingredients needed for dish 1, but not for dish 2. To meet this requirement, I may run the following queries:

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);

As far as I know, the above ORM cannot be used to express this query clearly. In response to these situations,ORMUsually it provides the ability to inject the original SQL into the query interface.SequelizeProvided a.query()Method to execute the original SQL as if you were using the underlying database driver. adoptBookshelfandObjectionYou can access the original Knex object provided during instantiation and use it for query constructor functions.KnexObjects also have.raw()Method to execute the original SQL. With Sequelize, you can also use itSequelize.literal()Method: Spread the original SQL aroundSequelizeIn each part of the call. But in each case, you still need to know some basic SQL to generate these queries.

Query Constructor: Best Choice

Using the underlying database driver module is attractive. There is no extra overhead when generating database queries, because we write the SQL statements manually. Our project dependency has also been minimized. However, generating dynamic queries can be very cumbersome, which I think is the biggest disadvantage of using database drivers.

For example, in a Web interface, users can select the conditions in which they want to retrieve items. If the user can enter only one option (such as color), our query may be as follows:

SELECT * FROM things WHERE color = ?;

This simple query works very well under the driver. However, if the color is optional, there is another name namedis_heavyOptional fields. Now, we need to support some different permutations of this query:

SELECT * FROM things; -- Neither
SELECT * FROM things WHERE color =?; -- Color only
SELECT * FROM things WHERE is_heavy =?; -- Only Is Heavy
SELECT * FROM things WHERE color = AND_heavy =?; -- Both

However, due to the various reasons mentioned in the previous chapter, the functions are complete.ORMIt’s not the tool we want.

In these cases, the query constructor eventually becomes a very good tool.KnexOpen interfaces are so close to basic SQL queries that we can eventually get a rough idea of what an SQL statement is like.
This relationship is similar toTypeScriptConvert toJavaScriptWays.

As long as you fully understand the underlying generated SQL, using the query constructor is a good solution. Don’t use it as a tool to hide the underlying, but for convenience and when you know exactly what it is doing. If you have questions about the generated SQL statements, you can use theKnex()Add debug fields when instantiating. Like this:

const knex = require('knex')({
  client: 'pg',
  connection,
  debug: true // Enable Query Debugging
});

In fact, most of the libraries mentioned in this article provide methods for debugging calls being executed.


We studied three different abstraction layers for interacting with databases, namely, the underlying database driver, the middle query constructor and the upper layer.ORMThis is the case. We also studied the pros and cons of using each layer and the generated SQL statements. Including the use of database drivers to generate dynamic queries will be difficult, but ORM will increase the complexity, and finally concluded that the use of query constructors is the best choice.

Thank you for your reading, and be sure to take this into account when building the next project.


After you have finished, you can run the following commands to delete them completelydockerContainer and delete database files from the computer:

docker stop pg-node-orms
docker rm pg-node-orms
sudo rm -rf ~/data/pg-node-orms