Malik Samad Khan

Posted on Dec 14, 2023

Sequelize CLI & Typescript

#sequelize #typescript #express #sql

Introduction

Sequelize is specifically built for javascript projects and has no official documentation on how to use it in typescript as you would normally do in javascript.

In this post, I will explain how I do the configuration for Sequelize in a typescript project which works for production uses specifically for running migrations cli. Sequelize by default uses javascript for its cli commands and we need to make it compatible with a typescript project.

Note:

Most of the online blogs/posts on this topic don't show how you can configure for running migration and instead show you the implementation by using connection.sync() method, which is not an option for your production environment. check the official documentation for the sync method here

Pre-requesites

Before we start the configuration, it is needed to have Node installed on your machine. also for npm package management, I usually prefer yarn but you can use npm for this as well.

I will be using MySQL for the database using the docker container, and for viewing the database I am using MySQL Workbench.

Repository Link

https://github.com/malik-samad/sequelize-with-typscript

Optional - Creating Database using Docker

I prefer to create databases using docker container which I feel is a more clean way of doing it. For this purpose, we will use docker-compose.yml file in the project's root directory.

version: '3'
name: sequelize-with-typescript
services:
  local-sql-db:
    image: mysql:latest
    container_name: local-sql-db
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: sql-ts-local
      MYSQL_USER: user
      MYSQL_PASSWORD: password
    ports:
      - '3306:3306'
    volumes:
      - mysql-data:/var/lib/mysql # Change this path as per your preference

volumes:
  mysql-data:

now you should be able to spin up your DB using docker-compose up

To view the database you can add a connection in your SQL Workbench as per the credentials defined in the docker-compose.yml file.

Instalation

Let's start creating a basic express server with Sequelize and Typescript. First of all, create a folder for the project, and then navigate into it.

mkdir sequlize-with-typescript
cd sequlize-with-typescript

Then, install the necessary packages.

yarn init -y
yarn add express node sequelize sequelize-cli mysql2 dotenv
yarn add typescript ts-node @types/express @types/node @types/sequelize nodemon --dev

Configure Typescript

For a typescript-based project, we need to have a tsconfig.json file in the root directory, which contains configuration related to typescript. For this project, we will use the below config.

{
  "compilerOptions": {
    "allowJs": true,
    "baseUrl": "./src",
    "allowUnreachableCode": false,
    "noFallthroughCasesInSwitch": true,
    "allowSyntheticDefaultImports": true,
    "downlevelIteration": true,
    "esModuleInterop": true,
    "forceConsistentCasingInFileNames": true,
    "module": "Node16",
    "noImplicitAny": true,
    "resolveJsonModule": true,
    "noImplicitReturns": false,
    "noImplicitThis": true,
    "noUnusedLocals": false,
    "sourceMap": true,
    "strictNullChecks": true,
    "strict": true,
    "pretty": true,
    "target": "es6",
    "typeRoots": ["./src/typings", "../../node_modules/@types"],
    "outDir": "build"
  },
  "include": ["./**/*.ts"],
  "exclude": ["node_modules", "build", "dist"]
}

please refer to the documentation for more details on how to tweak tsconfig file as per your need. you can also start with npx tsc --init which will create a basic tsconfig including almost all possible options with commented-out details.

Configure Sequelize

To configure Sequelize we need to first create a config file named .sequelizerc

// .sequelizerc
const path = require('path');

module.exports = {
  config: path.resolve('src/database', 'sequelize.config.js'),
  'models-path': path.resolve('src/database', 'models'),
  'seeders-path': path.resolve('src/database', 'seeders'),
  'migrations-path': path.resolve('src/database', 'migrations'),
};

Now let's run npx sequelize init, this will create a directory under src/database which will contain sub-folders for models, migrations, and seeds. additionally, it will also contain sequelize.config.js.

we will need to replace the content of sequelize.config.js with below:

// src/database/sequelize.config.js
require('ts-node/register');
const configs = require('../configs.ts');

module.exports = {
  username: configs.DB_USERNAME,
  password: configs.DB_PASSWORD,
  database: configs.DB_DATABASE,
  host: configs.DB_HOST,
  dialect: 'mysql',
  port: 3306
};

Note: It is important to keep require('ts-node/register'); on the first line, this will make sure that Sequelize cli understands typescript when running migrations or seedings.

In sequelize.config.js file we are using runtime envs for the sequelize cli, using the same config file that we will be using inside the express server.

Now we need to update the package.json to have some scripts for running the server and migrations, below are the needed scripts:

{
  ...
  "scripts": {
    "start": "ts-node src/index.ts",
    "db:up": "docker-compose up -d",
    "db:create": "sequelize db:create",
    "db:migrate": "sequelize db:migrate",
    ...
  },
  ...
}

Creating migration files

To create migration files, we will create 2 models named users and todos using sequelize cli.

npx sequelize-cli model:generate --name Todos --attributes title:string,isDone:boolean

npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string,password:string

this will create 2 files **********-create-user.js and **********-create-todos.js, as you can see these files are not typescript but instead javascript files.

Sequelize will not create a ts file by default and that's why our next step will be to rename the file and make it a .ts file. additionally, we will also need to add some typing to remove errors reported by typescript on the file.

after doing these changes these 2 migration files will be as below:
src/database/migrations/**********-create-user.ts

import { QueryInterface, DataTypes } from 'sequelize';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface: QueryInterface, Sequelize:typeof DataTypes) {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstName: {
        type: Sequelize.STRING
      },
      lastName: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      password: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  async down(queryInterface:QueryInterface, Sequelize:any) {
    await queryInterface.dropTable('Users');
  }
};

src/database/migrations/**********-create-todos.ts

import { QueryInterface, DataTypes } from 'sequelize';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface: QueryInterface, Sequelize:typeof DataTypes) { 
    await queryInterface.createTable('Todos', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      title: {
        type: Sequelize.STRING
      },
      isDone: {
        type: Sequelize.BOOLEAN
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
    await queryInterface.addColumn(
      'Todos', // name of Source model
      'user_id', // name of the key we're adding 
      { 
        type: Sequelize.INTEGER ,
        references: {
          model: 'Users', // name of Target model
          key: 'id', // key in Target model that we're referencing
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
      }
    )
  },
  async down(queryInterface:QueryInterface, Sequelize:any) {
    await queryInterface.dropTable('Todos');
  }
};

Sequelize Models

we need to make some changes in the model files to make them work properly in a typescript project.
First of all, we will have to change the file's extensions from .js to .ts.

Now the content for models should be changed as shown below:
for src/databse/models/users.ts

import { Model, DataTypes } from 'sequelize';
import connection from '../connection';
import Todo from './todos';

interface UserAttributes {
  id?: number;
  firstName: string;
  lastName: string;
  email: string;
  password: string;

  updatedAt?: Date;
  deletedAt?: Date;
  createdAt?: Date;
}

class User extends Model<UserAttributes> implements UserAttributes {
  public id!: number;
  public firstName!: string;
  public lastName!: string;
  public email!: string;
  public password!: string;

  public readonly updatedAt!: Date;
  public readonly createdAt!: Date;
}

User.init(
  {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: DataTypes.NUMBER,
    },
    firstName: {
      allowNull: false,
      type: DataTypes.STRING,
    },
    lastName: {
      allowNull: false,
      type: DataTypes.STRING,
    },
    email: {
      allowNull: false,
      unique: true,
      type: DataTypes.STRING,
    },
    password: {
      allowNull: false,
      type: DataTypes.STRING,
    }, 

    createdAt: {
      allowNull: false,
      type: DataTypes.DATE,
    },
    updatedAt: {
      allowNull: false,
      type: DataTypes.DATE,
    },
  },
  {
    sequelize: connection,
    modelName: 'User',
  }
);

export default User;

and for src/databse/models/todos.ts

import { Model, DataTypes } from 'sequelize';
import connection from '../connection';
import User from './user';

interface TodoAttributes{

  id?: number;
  title: string;
  user_id:number;
  isDone: boolean;

  updatedAt?: Date;
  deletedAt?: Date;
  createdAt?: Date;
}

class Todo extends Model<TodoAttributes> implements TodoAttributes {
  public id!: number;
  public title!: string;
  public user_id!:number;
  public isDone!: boolean;

  public readonly updatedAt!: Date;
  public readonly createdAt!: Date;
}

Todo.init(
  {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: DataTypes.NUMBER,
    }, 
    title: {
      allowNull: false,
      type: DataTypes.STRING,
    },
    isDone: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
    user_id:{
      type: DataTypes.NUMBER,
      allowNull:false
    },

    createdAt: {
      allowNull: false,
      type: DataTypes.DATE,
    },
    updatedAt: {
      allowNull: false,
      type: DataTypes.DATE,
    },
  },
  {
    sequelize: connection,
    modelName: 'Todo',
  }
);

// associate
Todo.belongsTo(User, {
  as: 'user',
  foreignKey: {
    name: 'user_id',
    allowNull: false,
  },
  foreignKeyConstraint: true,
});

export default Todo;

To demonstrate an association between the 2 models, I have associated todo with the user by using the belongsTo method.

As you may already noticed, we are using a connection inside the model file. this connection file is defined in src/database directory:
src/database/connection.ts

import { Sequelize } from 'sequelize';
import { DB_DATABASE, DB_HOST, DB_PASSWORD, DB_USERNAME } from '../configs';

let sequelizeConnection: Sequelize = new Sequelize(DB_DATABASE, DB_USERNAME, DB_PASSWORD, {
  host: DB_HOST,
  dialect: 'mysql',
  port: 3306, 
});

export default sequelizeConnection;

Server

Now, to test these models and Sequelize, let's create a simple basic express server with GET Api as shown below:

src/index.ts

import { PORT } from "./configs";
import Express from "express";
import Todo from "./database/models/todos";
import User from "./database/models/user";

const server = Express();

server.get("/todo/all", async (req, res) => {
  try{
    res.send(await Todo.findAll({
      include: [{model: User, as: "user"}]
    }))
  }catch(err){
    console.error(err);
    res.status(500).send("Unexpected error occurred on server!");
  }
})

const port = PORT || 80;
server.listen(port, () => {
  console.log(`🚀 Server is running on port ${port}`)
})

Ok, at this point we have everything ready to test and run, to do this let's open the terminal for your project root directory we will run cli to create MySQL Container, create DB, and run migrations.

yarn db:up
yarn db:create
yarn db:migrate

Now, let's spin up the express server using the below cli

yarn start

and open the GET endpoint http://localhost/todo/all in the browser, you should see a response like this:

To add some test data, open your DB and add dummy data using below MySQL scripts

INSERT INTOsequelize-with-typescript.Users(firstName,lastName,email,password,createdAt,updatedAt) VALUES ('Malik', 'Samad', 'test@email.com', 'secure pass', '2023-12-05 00:00:00', '2023-12-05 00:00:00');

INSERT INTOsequelize-with-typescript.Todos(title,user_id,isDone,createdAt,updatedAt) VALUES ('test todo 1', '1', '0', '2023-12-05 00:00:00', '2023-12-05 00:00:00');

INSERT INTOsequelize-with-typescript.Todos(title,user_id,isDone,createdAt,updatedAt) VALUES ('test todo 2', '1', '0', '2023-12-07 00:00:00', '2023-12-07 00:00:00');

Conclusion

This blog provides a comprehensive guide on configuring and using Sequelize with TypeScript for building robust and maintainable‎ APIs. here we have covered the configuration needed for running migration cli in your typescript project for the production environment.

additionally, we have also covered fundamentals of Sequelize cli, migrations, associations, and models.

Top comments (6)

Waqas Ahmad • Dec 17 '23

Fantastic read! I love how your blog is such easy to grasp and so comprehensive.The clear explanations make it easy to follow. This will definitely be a go-to resource for anyone venturing into this tech stack. Well done!"