The Best of ORMs: #1 Sequelize

Why ORMs?

ORM stands for Object Relational Mapping. It is a tool that lets you write a code with a language of your choice. It transforms the code as a query to a database and you manipulate the retrieved data as an object. You can define your data model at one place, it’s easy to maintain, update and you don’t have to repeat the code at more places. The only bad thing is that you’re limited with the ORM library you use and when you want to write more complex queries you would need to use raw queries instead.

But which ORM to use and which one is the best?

Well there are many factors that define which ORM suits you well, like types support, usage of querying methods, filtering, operators usage, relations, migrations and seeds support etc.

I decided to try a couple of ORMs and compare one with another. This will be a mini blog series and every blog will be about one ORM, starting with the sequelize. For every ORM I will use node.js runtime with typescript and postgres database.

Sequelize ORM

Sequelize is a Node.js typescript ORM library for Postgres, MySQL, MariaDB, SQLite, SQL Server and more. It supports type safety with typescript and it supports promise based functions

It allows defining entities over database schema and provides numerous methods to perform queries on the database.

Installing sequelize and it's dependencies

npm install sequelize  
npm install --save pg pg-hstore  

Connection

First you need to initialize the connection to a database. With sequelize you need to specify a database driver and some other connection parameters. For postgres it goes like this:

import config from '../../config'  
import { Sequelize } from 'sequelize'  
import logger from '../logger'  
  
const sequelize = new Sequelize(  
    'dbName', 'user', 'password',  
  {  
    host: 'localhost',  
    dialect: 'postgres',  
    logging: msg => logger.debug(msg),  
  }  
)

I added there logging because it might be useful for debugging.

Entity

Entity or a model is an abstraction for a table in a database. On the model you define columns, its data types, default values, constraints, etc.

There are two ways you can define an entity in sequelize.

First is to extend a Model class and then call an init method on it and define columns.

The second method is to call the define method on a sequelize connection and define columns there.

In the end it doesn't matter which one of those you use because the second approach internally calls the init method under the hood. I chose the second approach.

Tables in postgres database:

Drivers

CREATE TABLE drivers (  
  id serial PRIMARY KEY,  
  username varchar(255) NOT NULL,  
  firstname varchar(255) DEFAULT NULL,  
  created_at timestamp DEFAULT NULL,  
  CONSTRAINT drivers_username_unique UNIQUE(username)  
);

Drivers' trips

CREATE TABLE trip_drivers (  
  id serial primary key,  
  driver_id integer not NULL,  
  state varchar(50) DEFAULT NULL,  
  created_at timestamp DEFAULT NULL,  
  CONSTRAINT trip_drivers_driver_id_foreign foreign key(driver_id) REFERENCES drivers(id) ON DELETE CASCADE ON UPDATE CASCADE  
);

Defining table entities in typescript:

I used the table schema that is described above and then I created the same schema as a model with sequelize.

import { DataTypes, ModelDefined, Optional } from 'sequelize'  
import connection from './connect'  
  
export interface DriversAttributes {  
  id: number  
  username: string  
  firstname?: string  
  state?: string  
  created_at?: Date  
}  
  
export interface DriversAttributesCreate  
  extends Optional {}  
  
   ...

   ...  
  
export const Driver: ModelDefined =  
  connection.define(  
    'Drivers',  
    {  
      id: {  
        type: DataTypes.INTEGER,  
        autoIncrement: true,  
        primaryKey: true,  
      },  
      username: {  
        type: DataTypes.STRING(255),  
        allowNull: false,  
        unique: true,  
      },  
     state: {  
        type: DataTypes.STRING(255),  
        allowNull: true,  
        defaultValue: null,  
      },  
      created_at: {  
        type: DataTypes.DATE,  
        allowNull: true,  
        defaultValue: null,  
      },  
    },  
    {  
      tableName: 'drivers',  
      timestamps: false,  
    }  
  )

I created 2 interfaces, one for a base schema and the second one when I call a create query, because I might not want some of the properties when I create data, like id, because it's a serial. 

Then I used these two interfaces and Sequelize's ModelDefined to define a new ORM model called Driver.

On the define method 1st parameter is a name of an entity, 2nd parameter is an object of defined columns and the 3rd parameter are table options.

The great thing is that data types for columns are described in sequelize as an sql standard so then it's clear which type is used for a specific column.

CRUD operations

For any CRUD operation there are particular methods on the sequelize model. All methods are Promise based, and you can use them with async/await syntax.

Find method (read)

For reading a record from a table there are findOne, fineByPk, findAll methods.

Driver.findAll({  
    attributes: [  
      'id',  
      'username',  
      'firstname',  
      'surname',  
      'created_at',  
      'phone',  
    ],  
    order: [['created_at', 'DESC']],  
    limit: 10,  
    offset: 20,  
    where: {  
      created_at: {  
        [Op.gt]: new Date('02-21-2019').toString()  
      },  
      firstname: {  
        [Op.iLike]: '%arel'  
      },  
    },  
  }),

Attributes are for specifying columns which are then selected, then there are other options like limit, offset for pagination and where for filtering records.

Sequelize provides you with operators that are used in standard sql language like <, like. They are defined on Op object and you can use them in where clause, see [operators](https://sequelize.org/docs/v6/core-concepts/model-querying-basics/#operators).

Create

To create records use create or bulkCreate to create more than one record.

Driver.bulkCreate([  
    {  
      username: randomUUID(),  
      firstname: 'Pepa',  
      created_at: new Date(),  
    },  
    {  
      username: randomUUID(),  
      firstname: 'Bartoloměj',  
      created_at: new Date(),  
    }])

Update

Driver.update(  
    { created_at: new Date() },  
    {  
      where: {  
        firstname: 'Karel',  
      },  
    }  
  )

Delete

Driver.destroy({  
    where: {  
      firstname: 'Pepa',  
    },  
  })

Relations

Relations are defined with hasOne, hasMany, belongsTo, belongsToMany methods, based on what relation you need to define.

In this project I have two tables Driver and TripDrivers. Driver have many trips and TripDrivers can have only one Driver and are referenced to Driver by driver_id.

This is how is it defined in sequelize:

Driver.hasMany(TripDrivers)  
TripDrivers.belongsTo(Driver)

To associate driver to a trip:

TripDrivers.belongsTo(Driver, { foreignKey: 'driver_id' })  
TripDrivers.findAll({  
  include: [  
    {  
      model: Driver,  
      required: false,  
    },  
  ],  
})

required: false option tells sequelize not to include null values.

Migrations and seeds

Running migrations and seed requires specifying a config.js file with connection options.

module.exports = {  
    development: {  
      username: process.env.POSTGRES_USER,  
      password: process.env.POSTGRES_PASSWORD,  
      database: process.env.POSTGRES_DB,  
      host: process.env.DB_HOST,  
      port: +process.env.DB_PORT,  
      dialect: 'postgres',  
    },  
}

Sequelize can actually do it for you, just run:

npx sequelize-cli init

This will create a config, migrations, models and seeds folder and templates files inside these folders.

You can generate migrations with: 

npx sequelize-cli migration:generate --name migration-name.

This will create a migration skeleton with up and down methods. Up methods to run migrations and down methods to rollback migrations.

module.exports = {  
  up: (queryInterface, Sequelize) => {  
      
  },  
  down: (queryInterface, Sequelize) => {  
      
  }  
}

To run migrations: npx sequelize db:migrate

To rollback migrations: npx sequelize db:migrate:undo

The same logic applies for seeds.

Use sequelize cli for generating a model and as a migration file:

npx sequelize model:generate --name driver --attributes \  
id:integer,\  
username:string,\  
firstname:string,\  
created_at:Date,\

It is a cool feature but I don't recommend it since you can only provide typescript data types and not sql standard types. Therefore I think it's better to create a migration file and create a model there.

Summary

Sequelize comes with so many cool features, such as standard SQL data types for columns, entity definition, defining a type for SELECT and CREATE queries. Query methods are well typed and it's easy for you to use them. Sequelize also comes with a great documentation where everything is explained well and it's in an active development. Overall configuration is fast and easy. What I also like is generating migration config files, migration, seed and model files from sequelize cli and running migrations and seeds  with it.

However, there are some cons. When calling a join query it doesn't work well with typing which can be defined as a return type but still I would expect to have correct types which are inferred properly. Other than that I didn't find anything that would be a con. Next time we’ll take a look at pros and cons of another ORM library: Mikro-ORM.