Sequelize CLI & Typescript
Tags: sequelize, typescript, express, sql
Published: 2023-12-14 04:51:58

## 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](https://sequelize.org/docs/v6/core-concepts/model-basics/#synchronization-in-production)

## 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.

![SQL-Workbench connection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/un0rzxiufabjt0h7mtfu.png)

## 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](https://www.typescriptlang.org/docs/handbook/tsconfig-json.html) 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:

![API response in browser](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/skl8zes4hu6bps07qc1q.png)


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

```
`INSERT INTO `sequelize-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 INTO `sequelize-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 INTO `sequelize-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.