Skip to content

Using sequelize with Typescript

What's Sequelize?

Sequelize is a Javascript ORM for Oracle, Postgres, MySQL, MariaDB, SQLite and SQL Server. An ORM is an object-relational mapping in other words is a piece of software that enables us to translate and interact with relational databases using OOP languages. This way is easier to map the tables in the database and interact with the data, for example creating and finding entries.

Using Sequelize without sequelize-typescript?

When trying to use Sequelize with Typescript according to the official documentation is better to use sequelize-typescript.

We're working hard on making Sequelize a breeze to use in TypeScript. Some parts are still a work in progress. We recommend using sequelize-typescript to bridge the gap until our improvements are ready to be released.

The problem is that if we want to use sequelize-typescript we need to enable multiple options in our tsconfig.json for example setting experimentalDecorators and emitDecoratorMetadata to true, we also need to implement multiple changes in the way we define our models and the way we declare our sequelize instance.

Creating new project

This doesn't mean we can't use sequelize without sequelize-typescript package. We can create a singleton to initialize our Sequelize connection and use this to create our models.

First we need to create a new node project

mkdir sequelize-typescript
cd sequelize-typescript
npm init -y

This is a list of all the packages needed and the reason why we will install them:

"express" (npm install express): Simplifies the creation of RESTful APIs 
using Node
``` mechanism that only allow access to our resources for specified domains
"sequelize" (npm install sequelize): Sequelize is an easy-to-use and promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite, DB2, Microsoft SQL Server, and Snowflake
"dotenv" (npm install dotenv): Loads environment variables from a .env
"pg" (npm install pg): Non-blocking PostgreSQL client for Node.js
"typescript" (npm install -D typescript): TypeScript adds optional types to JavaScript that support tools for large-scale JavaScript applications for any browser
"ts-node" (npm install -D ts-node): Transforms TypeScript into JavaScript
"@types/express" (npm install --save @types/express): Types definition for express
npm i express sequelize dotenv pg
npm i --save-dev typescript ts-node @types/express

Next we will create a tsconfig.json file

npx tsc --init

Change the tsconfig.json file that was created

{
  "compilerOptions": {
    "target": "es6",
    "module": "commonjs",
    "declaration": true,
    "sourceMap": true,
    "outDir": "dist",
    "rootDir": "./",
    "strict": true,
    "esModuleInterop": true
  }
}

Now change the package.json file

{
    "name": "sequelize-typescript",
    "version": "1.0.0",
    "description": "",
    "main": "dist/index.js",
    "types": "dist/index.d.ts",
    "scripts": {
      "start": "ts-node index.ts", 
      "build": "tsc"
    },
    "keywords": [],
    "author": "",
    "license": "ISC",
    "devDependencies": {
      "@types/express": "^4.17.17",
      "ts-node": "^10.9.1",
      "typescript": "^5.1.6"
    }
}

Creating server starting point and routes

Next we are going to create an index.ts file in the base of the project

// ./index.ts

import 'dotenv/config'

import express, { Application } from "express";
import { SequelizeConnection } from "./src/services/sequelize";
import Author from "./src/models/author";
import Book from "./src/models/book";

const port = 3015;
// New express application instance
const app: Application = express();

/**
 * Initialize sequelize connection and set singleton instance
 */
SequelizeConnection.getInstance();

/**
 * Route to get all books with authors, this should be avoided routes and business logic should be in separated
 * specific folder and files.
 */
app.get("/books", async (req, res) => {
    /**
     * Get all books from the database as raw objects (avoid getting sequelize fields),
     * including the author relation and add the author object to the js object returned.
     */
    const allBooks = await Book.findAll({
        // Avoid getting sequelize fields
        raw: true,
        // Name of the relation set in model
        include: ['author'],
        // Add the relation to the returned objects
        nest: true,
    });

    res.status(200).send(allBooks)
})

/**
 * Begin listening on port selected
 */
app.listen(port, () => {
    console.log(`Server is running on port ${port}...`);
});

Creating connection singleton

Now will create a singleton to initialize our sequelize connection, create a new folder called src in the base of the project. Inside this create a new folder called services, inside create a new file called sequelize.ts

// ./src/services/sequelize.ts

import {  Dialect, Sequelize} from "sequelize";

/**
 * The Singleton class defines the `getInstance` method that lets clients access
 * the unique singleton instance.
 */
export class SequelizeConnection {
    // Connection instance
    private static instance: Sequelize;

    /**
     * The Singleton's constructor should always be private to prevent direct
     * construction calls with the `new` operator.
     */
    private constructor() {
        // Information needed to initialize database connection
        const dbName = process.env.DB_NAME as string
        const dbUser = process.env.DB_USER as string
        const dbHost = process.env.DB_HOST
        const dbDriver = process.env.DB_DRIVER as Dialect
        const dbPassword = process.env.DB_PASSWORD

        // Initialize connection
        SequelizeConnection.instance = new Sequelize(dbName, dbUser, dbPassword, {
            host: dbHost,
            dialect: dbDriver
        })

        // Test connection
        SequelizeConnection.instance.authenticate().then(() => {
            console.log('Sequelize connected')
        })
    }

    /**
     * The static method that controls the access to the singleton instance.
     *
     */
    public static getInstance(): Sequelize {
        if (!SequelizeConnection.instance) {
            new SequelizeConnection();
        }

        return SequelizeConnection.instance;
    }
}

Creating env variables

Next we need to create an .env file in the base of the project to the define the connection values, I will use postgres for this tutorial, you are free to use whatever you want. The implementation will be the same

NODE_ENV=development
DB_NAME=
DB_USER=
DB_HOST=
DB_DRIVER=postgres
DB_PASSWORD=
DB_URI=

Creating models

Now we are going to start creating our models we will create two, Books and Authors, we will have a relation between the two to show how relations work. Create a new folder called models in the base of the project, inside create two new files author.ts and book.ts

// ./src/models/author.ts

import {DataTypes, Model} from "sequelize";
import {SequelizeConnection} from "../services/sequelize";

export default class Author extends Model {
    declare id: number;

    declare name: string;

    declare lastName: string;
}

const sequelizeConnection = SequelizeConnection.getInstance();

/**
 * Initialize model, define sequelize connection, the name of the table, 
 * it's attributes and relations
 */
Author.init(
    {
        id: {
            field: "id",
            primaryKey: true,
            type: DataTypes.INTEGER,
            allowNull: false,
            autoIncrement: true,
        },
        name: {
            field: 'name',
            type: DataTypes.STRING,
        },
        lastName: {
            field: 'lastName',
            type: DataTypes.STRING,
        },
    },
    {
        sequelize:  sequelizeConnection,
        tableName: "authors",
        modelName: "Author",
    },
);

// Will create the table automatically if it's not found
Author.sync().then();
// ./src/models/book.ts

import Author from "./author";

import * as crypto from "crypto";

import { DataTypes, ForeignKey, Model, NonAttribute } from "sequelize";
import { SequelizeConnection } from "../services/sequelize";

export default class Book extends Model {
    declare id: number;

    declare name: string;

    // Foreign key making the relation between a book and an author
    declare authorId: ForeignKey<Author["id"]>;

    /**
     * Field that will contain relation, it's not counted as an 
     * attribute of the table
     */
    declare author: NonAttribute<Author>;
}

// Get sequelize connection
const sequelizeConnection = SequelizeConnection.getInstance();

/**
 * Initialize model, define sequelize connection, the name of the table, 
 * it's attributes and relations
 */
Book.init(
    {
        id: {
            field: "id",
            primaryKey: true,
            type: DataTypes.INTEGER,
            allowNull: false,
            autoIncrement: true,
        },
        name: {
            field: 'name',
            type: DataTypes.STRING,
            unique: true,
        },
        authorId: {
            field: "authorId",
            type: DataTypes.INTEGER,
            references: {
                model: "authors",
                key: "id",
            },
        },
    },
    {
        sequelize:  sequelizeConnection,
        tableName: "books",
        modelName: "Book",
    },
);

/**
 * Both sides of the relation must be in the same file to avoid getting errors,
 * make sure both models are available in sequelizeConnection.models if one of the
 * models is not added to this object then your seqquelize connection is being initialized
 * more than one time, use include: ['author'] when fetching for books to include the relation
 */
Book.belongsTo(Author, {
    foreignKey: "authorId",
    as: "author",
});

Author.hasMany(Book, {
    sourceKey: "id",
    foreignKey: "authorId",
    as: "books",
});

// Will create the table automatically if it's not found
Book.sync().then();

Now start the server, this will create the tables for us because we use the sync method when defining our models.

Starting project

npm run start

Adding seeders and migrations

Next we are going to add some entries to our new tables, using seeders. But first we need to create a configuration file to tell sequelize where to save the new seeders, migrations and where to connect to the database. Create a new file called .sequelizerc in the base of the project. Now create two new folders inside of src one called seeders and the other one called migrations make sure you use the same name as the values of seeders-path and migrations-path.

// ./.sequelizerc

// Make env variables available
require('dotenv').config()

const path = require('path');

module.exports = {
    // Current environment for example development or production
    'env': process.env.NODE_ENV,
    // Folder to seeders
    'seeders-path': path.resolve('src', 'seeders'),
    // Folder to migrations
    'migrations-path': path.resolve('src', 'migrations'),
    // Url to connect to DB
    'url': process.env.DB_URI
};

Now execute the following command to create a new seeder, a new file should appear inside of src/seeders

npx sequelize-cli seed:generate --name authors
// ./src/seeders/timestamp-authors.js
'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
    up: (queryInterface, Sequelize) => {
        // Add multiple authors to database
        return queryInterface.bulkInsert('authors', [
            {
                id: 1,
                name: 'George',
                lastName: 'Orwell',
                createdAt: new Date(),
                updatedAt: new Date(),
            },
            {
                id: 2,
                name: 'Ray',
                lastName: 'Bradbury',
                createdAt: new Date(),
                updatedAt: new Date(),
            },
        ]);
    },
    down: (queryInterface, Sequelize) => {
        // Remove all authors from database
        return queryInterface.bulkDelete('authors', null, {});
    }
};

Create another seedeer running the same command, this time we will create the books entries

npx sequelize-cli seed:generate --name books
// ./src/seeders/timestamp-books.js
'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
    up: (queryInterface, Sequelize) => {
        // Add multiple books to database
        return queryInterface.bulkInsert("books", [
            {
                id: 1,
                name: '1984',
                authorId: 1,
                createdAt: new Date(),
                updatedAt: new Date(),
            },
            {
                id: 2,
                name: 'Animal Farm',
                authorId: 1,
                createdAt: new Date(),
                updatedAt: new Date(),
            },
            {
                id: 3,
                name: 'Fahrenheit 451',
                authorId: 2,
                createdAt: new Date(),
                updatedAt: new Date(),
            },
        ]);
    },
    down: (queryInterface, Sequelize) => {
        // Remove all books from database
        return queryInterface.bulkDelete('books', null, {});
    }
};

Now run the following command in the terminal to run the seeder, after this you should see the new entries in the database.

npx sequelize-cli db:seed:all

Start the server and go to http://localhost:3015/books you should see a list with all the books in the database including the author. Now you should be able to use create, update or find methods to interact with your models and their relations.

npm run start

GitHub repository

In this repository https://github.com/obravocedillo/SequelizeTypescript you can find the code.