Discussion on ORM and Query Builder - Sequelize vs Knex!!!

Problem

In any project, regardless of the type of database used, selecting a library (client) to support connection and data querying is essential.

There are three popular methods that libraries use to query data: Raw Query, Query Builder, and ORM. Each method has different implementation and pros/cons, and depending on the project requirements, we will choose the one that best leverages their strengths.

In today's article, I will present and analyze the pros and cons as well as recommend which method to use in your next project.

Raw, Builder, and ORM

Raw Query, as the name suggests, involves writing SQL statements directly into the project's code, meaning you write the SQL query as it is in your code.

For example, in MySQL, there is a library called mysql2 that allows you to query data using raw statements. This library is also used as a dependency by many other libraries.

If you use mysql2, the syntax for querying data will be:

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',  
  user: 'root',  
  database: 'test'
});

connection.query('SELECT * FROM `person` WHERE `name` = "Page" AND `age` > 45');

As you can see, raw query directly writes SQL statements in the code, which speeds up querying because you can easily write the most optimized commands without going through any conversion layers. Additionally, it is easy to write long and complex queries. However, if you choose this method, you need to determine which database to use from the beginning because it affects the library choice. Not all libraries can connect to MySQL or Postgres... For example, the mysql2 library in the above example can only be used for MySQL. If you want to use Postgres or SQLServer, you will have to find another library. Furthermore, raw queries may not work when you switch the project to use a different database.

Query Builder has improved syntax and can be a library that supports multiple types of databases such as MySQL, Postgres, SQLServer... This means that you can easily connect to multiple types of databases using one library.

Query builder combines query syntax, where you chain functions to retrieve the desired data. This is why it is called a builder.

For example, a popular query builder library is Knex.

const knex = require('knex')({
  client: 'mysql',  
  connection: {
      host: 'localhost',  
      user: 'root',  
      database: 'test'
  }
});

knex
    .select('*')
    .where("name", "Page")
    .where("age", ">", 45)
    .from("person");

Instead of writing raw commands, the Query Builder provides chaining functions to support queries. This helps to easily switch to SQL statements compatible with different types of databases. You can also easily view the raw command by calling .toString().

knex
    .select('*')
    .where("name", "Page")
    .where("age", ">", 45)
    .from("table")
    .toString();

// SELECT * FROM `person` WHERE `name` = "Page" AND `age` > 45;

Query builder helps to make your code cleaner and more structured. The syntax is consistent and supports multiple types of databases. It is suitable for projects that use complex queries and still want clear syntax.

ORM (Object Relational Mapping) provides a solution to map the database into code. It abstracts tables into objects (Classes) in the code.

Because of abstraction, it is convenient for querying and retrieving data. Direct operations with the database are hidden to reduce the complexity of the code. Sequelize is a popular library that supports ORM. Like Query Builder, Sequelize supports multiple types of databases.

With ORM, you have to write more code initially to declare classes corresponding to tables in the database. For example, here is a declaration for the person table:

const User = sequelize.define('person', {
  name: {
    type: DataTypes.STRING,  
    allowNull: false,  
  },  
  age: {
    type: DataTypes.STRING,  
    allowNull: false,  
  }
}

Then querying becomes very simple:

const users = await User.findAll({
    where: {
        "name": "Page",  
        "age": {
            [Op.gt]: 45
        },  
});

One benefit of ORM is that the users in the query result above is directly mapped to the rows in the database. Therefore, you can manipulate and update the data directly from them. For example, the following code updates the name of the first row in the search result to Page Updated:

users[0].setAttribute("name", "Page Updated");
await users[0].save();

Because ORM has mapped each row to the corresponding object, manipulating objects is like manipulating the database itself without the need for any SQL code.

Additionally, ORM also supports migrating (adding, modifying, deleting) tables and data through Classes. You can learn more on the official Sequelize website.

However, the drawback of ORM is that the performance of complex queries is not always great. This is because Sequelize hides the complexity of query statements and must use "roundabout" commands to support multiple types of databases.

If your project involves many advanced and complex queries, Sequelize may not be the most optimal choice. Instead, consider using Raw query or Query Builder.

Summary

The above are three methods of using Database clients to interact with databases. Depending on the project, we will choose the most suitable method. Raw queries still provide the best performance optimization, but involve writing more code. ORM makes querying easier but is difficult to use in cases of complex queries. Query builder strikes a balance between the two methods, but careful consideration is needed before using it.