Discover Sequelize, a fully featured Typescript ORM

What is Sequelize ORM, what are its use cases ?

An ORM allows you to bridge your SQL database (SQLite for instance) and Typescript objects

To fulfill that binding between the code and the database, Sequelize & ORMs provide the following tooling and abstractions:

• Models allow you to define Typescript objects and define how they are stored in the SQL database
• Models instances have handy methods to perform CRUD operations (create, update, delete) on the database
• With model querying, write SQL queries to the database from a Typescript query description

Let’s see some classic usages of the Sequelize ORM with SQLite database

Installing Sequelize

Sequelize can be installed with npm, the node package manager, with npm install --save sequelize. Then, you have to install the DefinitelyTyped package for Sequelize in order to use Sequelize with Typescript: npm install --save-dev @types/sequelize

Binding a SQLite database to Typescript using Sequelize

An advantage of an ORM over using raw SQL query is that it will adapt the Typescript model query to any SQL database, considering each SQL implementation specificity. You can use the following SQL databases with Sequelize:

• SQLite
• MySQL/MariaDB
• PostgreSQL

To get started, make sure you have setup your Typescript project and installed the Typescript driver associated to the database of your choice. In this article, we will setup SQLite and use it with Sequelize

We will start with Typescript code that connects to SQLite using Sequelize in the file database.ts:

import { Sequelize } from "sequelize";

export const sequelize = new Sequelize(
  { dialect: 'sqlite', storage: __dirname + '/db.sqlite' }
);

In order to create a connection to the SQLite backend, we tell SQLite that the implementation of the SQL database we are using is sqlite. Then, we specify that the SQLite DB will be stored in filename db.sqlite, located at project root

Its the only part of the where we will have to tell Sequelize specific information about the SQLite backend. If we choose later in the project to migrate to MariaDB, we will only have to update this file (and to transfer data from SQLite to MariaDB of course)

Defining your first Sequelize model

We will reuse the example project of the integration of SQLite with Typescript and create a model adapted to an existing SQL database schema

In the SQLite example, we created a SQL table containing articles, and inserted a few examples rows in that table. Then, we done some queries on that DB

The goal is to migrate these SQL queries into Typescript calls to Sequelize ORM

So let’s create a Sequelize model that will link the articles SQL table to the Typescript object Article. Let’s store that model definition into models/article.ts

import { DataTypes } from "sequelize";
import { sequelize } from "../database";

export const ArticleModel = sequelize.define('Article', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
  },
  title: {
    type: DataTypes.STRING,
    allowNull: false
  },
  description: {
    type: DataTypes.TEXT,
    allowNull: false
  }
}, { tableName: 'articles', timestamps: false });

The table articles has three columns, id, title and description. The properties Article model are mapped to the database columns

We also configured the model to be mapped to the table articles with the option tableName: 'articles'

Another option is whether or not you want to store row creation/update time in the database (adding createdAt/updatedAt model properties). Here we disabled that using timestamps: false

In development mode, you can also create the schema definition of the Article model using synchronization feature ArticleModel.sync(). In production, Sequelize recommends using Umzug to run Database migrations when upgrading your software

Typing the model with TypeScript

If we inspect ArticleModel typing using our IDE, we get ModelCtor<Model<any, any>>

TypeScript have little information about ArticleModel: the ArticleModel attributes and creation attributes are typed as any. The definition of a Sequelize model using sequelize.define is not enough to get a precise and useful type

Fortunately, Sequelize+TypeScript guide give us the solution to this issue:

Instead of defining a Model constructor using sequelize.define, we will create a class ArticleModel inheriting from Model. Then, we will initialize this class with the init method inherited from Model

import { CreationOptional, DataTypes, InferAttributes, InferCreationAttributes, Model } from "sequelize";
import { sequelize } from "../database";

export class ArticleModel extends Model<InferAttributes<ArticleModel>, InferCreationAttributes<ArticleModel>> {
    declare id: CreationOptional<number>;
    declare title: string;
    declare description: string;
};

ArticleModel.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
  },
  title: {
    type: DataTypes.STRING,
    allowNull: false
  },
  description: {
    type: DataTypes.TEXT,
    allowNull: false
  }
}, { sequelize, tableName: 'articles', timestamps: false });

TypeScript keyword declare is used to tell the TypeScript compiler that the variable will exists in the JavaScript code without the need for TypeScript compiler to define it in compiled JavaScript code. This is useful in this case because the model class variables will be defined on the ArticleModel.init method call

We type the id as optional on creation using CreationOptional because it has a default value in the SQL database: it is generated using AUTOINCREMENT

ArticleModel now has a precise TypeScript definition, which will be useful in the later code when we manipulate model instances

Manipulating data using a Sequelize model

In the previous article on SQLite, we saw how to manipulate data using SQLite driver

We used to write our SQL queries (sometime with parameters), and then ask the driver to execute them, and then fetching the results

We now can perform these operations with Sequelize ORM which will write the queries using the argument operation and query description

Reading data

As in the first tutorial, we want to fetch the two articles in the database with the longest description:

import { ArticleModel } from "./models/article";

async function main () {
  const twoLongestDescriptions = await ArticleModel.findAll({
    order: [[sequelize.fn('length', sequelize.col('description')), 'DESC']],
    limit: 2
 });
}

main();

It is possible to print the query generated and executed by Sequelize using the option logging: console.log. In this case, this SQL code is generated:

SELECT `id`, `title`, `description`
FROM `articles` AS `Article`
ORDER BY length(`description`) DESC
LIMIT 2

The SQL generated by Sequelize corresponds to the previously hand writen query. But that time, we only had to specify the business logic part of the query: the limit to apply and the sorting method

The code is also more adaptable: if we add a column to the articles table, we only have to update the Sequelize model, no matter how many calls it had which is not the case using plain SQL statements

Updating and saving a model instance

With Sequelize, it is possible to update the models instances returned by write operations like findAll. When modified with set method, a model instance is not saved in the database. To do that, you can use save method on modified model instance

import { ArticleModel } from "./models/article";

async function main () {
  const twoLongestDescriptions = await ArticleModel.findAll({
    order: [[sequelize.fn('length', sequelize.col('description')), 'DESC']],
    limit: 2
 });

  const lengthiestArticle = twoLongestDescriptions[0];
  lengthiestArticle.set('title', 'Lengthiest article');
  await lengthiestArticle.save();
}

main();

If the model is already up to date (no modifications since last model saving), no query will be ran on the database

Removing lines from the Database

To remove an Sequelize model instance from the database, you can use destroy method:

  const secondArticle = twoLongestDescriptions[1];
  await secondArticle.destroy();

Conclusion

Sequelize ORM can facilitate the integration of a SQL database into your Typescript application. The project maintenance is also simplified, since database definition is centralised in models definition, and boilerplate SQL code is written automatically

Before using an ORM, good SQL knowledge is a must, so don’t hesitate to check out the SQLite documentation on SQL syntax and familiarize with SQL logic and possibilities