Using Sequelize with Typescript

Object Relational Mapping (ORM) can be a very useful when we interact with a database, in this post I will go through a brief example project of utilizing the Sequelize ORM and demonstrate some of the advantages of ORMs in general.

The main crux of an ORM is that they encapsulate the details of querying a database and reduce the amount of raw SQL queries we need to write.

A few nice advantages of an ORM are

Object oriented paradigm to perform database queries
They have there own API for building queries which make us less vulnerable to SQL injection attacks
Many ORMs have tools to perform database migrations which helps keep our schemas in sync as requirements change
Helps to keep our codebase DRY, as the schema/models classes can be used through out a project

What we will build

We will build a base Express Rest API with Typescript and utilize the Sequelize ORM to interact with a database.

disclaimer: This tutorial will focus on the Sequelize implementation, and we will be directly interacting with the database model through our REST api. In a real world production environment, we would not be interacting directly with the database from our controllers, but rather through a domain/business layer.

Getting started


# Create a new folder for the project
mkdir api-orm-project
cd api-orm-project

# Create a package.json and initialize git
npm init -y
git init

# Add our dependencies
npm i express sequelize
npm i --save-dev typescript ts-node-dev @types/node @types/express dotenv

# Create tsconfig.json
npx tsc --init

Now we have initialized the project and installed our dependencies, we can now create our index.ts file which is the entry point into our server.

src/index.ts

import express, { Application } from 'express'

const app: Application = express()
const port = 3000

app.use(express.json());
app.use(express.urlencoded({ extended: true }));

app.listen(port, () => {
    console.log(`Server running on http://localhost:${port}`)
})

Setup Sequelize

We will use Sequelize to translate define and translate Typescript objects into SQL queries in order to communicate with a database. There is some good documenation on the Typescript implementation on Sequelize if you want to read more details about it for your particular use case.

The Sequleize library does support multiple databases, but for this example we will use Postgres.

Please note you will need Postgres installed on your local machine

npm install --save sequelize pg
npm install --save-dev @types/pg

Firstly we will need to configure our database credentials, this will create an instance of Sequelize with our specific database configuration.

src/db/config.ts

require("dotenv").config();

import { Dialect, Sequelize } from 'sequelize'

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

const sequelizeConnection = new Sequelize(dbName, dbUser, dbPassword, {
    host: dbHost,
    dialect: dbDriver
})

export default sequelizeConnection

Define our models

In this example we are going to create 2 models, an Author model and a Book model.

author book relationship diagram — Author Book Relationship Diagram

We want to create a many to many relationship between Authors and Books.

Lets create our Author model

We will need to first define the following interfaces:

AuthorAttributes all the possible attributes of the Author model
AuthorInput object type passed into the Sequelize model.create method
AuthorOutput object type returned from Sequelize on methods such as model.findOne

Then we will create the Author class which is the model we wll interact with in our application.

src/db/models/Author.ts

import {
    Optional, Model, DataTypes
} from "sequelize";
import sequelizeConnection from './../config';
import { Book } from "./Book";
export interface AuthorAttributes {
    id: string;
    firstName: string;
    lastName: string;
    createdAt?: Date;
    updatedAt?: Date;
    deletedAt?: Date;
}

export interface AuthorInput extends Optional<AuthorAttributes, "id"> { }
export interface AuthorOutput extends Required<AuthorAttributes> { }

export class Author extends Model<AuthorAttributes, AuthorInput> implements AuthorAttributes {
    declare id: string;
    declare firstName: string;
    declare lastName: string;

    declare readonly createdAt: Date;
    declare readonly updatedAt: Date;
    declare readonly deletedAt: Date;
}

Author.init({
    id: { type: DataTypes.UUID, defaultValue: DataTypes.UUIDV4, primaryKey: true, allowNull: false },
    firstName: { type: DataTypes.STRING, allowNull: false },
    lastName: { type: DataTypes.STRING, allowNull: false }
}, { timestamps: true, sequelize: sequelizeConnection, paranoid: true })

Lets also define the Book model.

src/db/models/Book.ts

import {
    Optional, Model, DataTypes
} from "sequelize";
import sequelizeConnection from './../config';
import { Author } from "./Author";

export interface BookAttributes {
    id: string;
    title: string;
    isbn: string;
    numberOfPages: number;
    createdAt?: Date;
    updatedAt?: Date;
    deletedAt?: Date;
}

export interface BookInput extends Optional<BookAttributes, "id"> { }
export interface BookOutput extends Required<BookAttributes> { }

export class Book extends Model<BookAttributes, BookInput> implements BookAttributes {
    declare id: string;
    declare title: string;
    declare isbn: string;
    declare numberOfPages: number;

    declare readonly createdAt: Date;
    declare readonly updatedAt: Date;
    declare readonly deletedAt: Date;
}

Book.init({
    id: { type: DataTypes.UUID, defaultValue: DataTypes.UUIDV4, primaryKey: true, allowNull: false },
    title: { type: DataTypes.STRING, allowNull: false },
    isbn: { type: DataTypes.STRING, allowNull: false },
    numberOfPages: { type: DataTypes.INTEGER, allowNull: false },
}, { timestamps: true, sequelize: sequelizeConnection, paranoid: true })

Now we have a class for both our models, the next step is to define the relationship between the two of them.

As we have a many-to-many relationship between our models, this will require us to create a join table to link the two models together.

This requires us to create an additional model class that represents the join table and any attributes we need.

src/db/models/BookAuthor.ts

import { Optional, Model, DataTypes } from "sequelize";
import sequelizeConnection from './../config';
import { Author } from "./Author";
import { Book } from "./Book";

interface BookAuthorAttributes {
    id: string;
    BookId: string;
    AuthorId: string;
    createdAt?: Date;
    updatedAt?: Date;
    deletedAt?: Date;
}

export interface BookAuthorInput extends Optional<BookAuthorAttributes, "id"> { }
export interface BookAuthorOutput extends Required<BookAuthorAttributes> { }

export class BookAuthor extends Model<BookAuthorAttributes, BookAuthorInput> implements BookAuthorAttributes {
    declare id: string;
    declare BookId: string;
    declare AuthorId: string;

    declare readonly createdAt: Date;
    declare readonly updatedAt: Date;
    declare readonly deletedAt: Date;
}

BookAuthor.init({
    id: { type: DataTypes.UUID, defaultValue: DataTypes.UUIDV4, primaryKey: true, allowNull: false },
    BookId: { type: DataTypes.UUID, references: { model: Book, key: 'id' } },
    AuthorId: { type: DataTypes.UUID, references: { model: Author, key: 'id' } }
}, { sequelize: sequelizeConnection });


// This is where we tell Sequelize how the two models are related and which attributes to use as foreign keys
Book.belongsToMany(Author, { through: BookAuthor, foreignKey: 'BookId' });
Author.belongsToMany(Book, { through: BookAuthor, foreignKey: 'AuthorId' });

Sequelize now knows how the two models are related, and if we try to make queries on our models we will be able to include attributes from related rows in the database.

However, wouldn't it be nice if we can have methods on our Author and Book Typescript classes that allow us to query the relationship of a particular row in the database?

This is where Sequelize mixins come in handy, we can add method definitions so that Typescript is aware of what our models can do.

Lets update our Author and Book models.

src/db/models/Author.ts

import {
    Optional, Model, DataTypes,
    BelongsToManyAddAssociationMixin,
    BelongsToManyAddAssociationsMixin,
    BelongsToManyCountAssociationsMixin,
    BelongsToManyCreateAssociationMixin,
    BelongsToManyGetAssociationsMixin,
    BelongsToManyHasAssociationMixin,
    BelongsToManyHasAssociationsMixin,
    BelongsToManyRemoveAssociationMixin,
    BelongsToManyRemoveAssociationsMixin,
    BelongsToManySetAssociationsMixin
} from "sequelize";
import sequelizeConnection from './../config';
import { Book } from "./Book";
export interface AuthorAttributes {
    id: string;
    firstName: string;
    lastName: string;
    createdAt?: Date;
    updatedAt?: Date;
    deletedAt?: Date;
}

export interface AuthorInput extends Optional<AuthorAttributes, "id"> { }
export interface AuthorOutput extends Required<AuthorAttributes> { }

export class Author extends Model<AuthorAttributes, AuthorInput> implements AuthorAttributes {
    declare id: string;
    declare firstName: string;
    declare lastName: string;

    declare readonly createdAt: Date;
    declare readonly updatedAt: Date;
    declare readonly deletedAt: Date;

    declare addBook: BelongsToManyAddAssociationMixin<Book, string>
    declare addBooks: BelongsToManyAddAssociationsMixin<Book, string>
    declare countBooks: BelongsToManyCountAssociationsMixin
    declare createBook: BelongsToManyCreateAssociationMixin<Book>
    declare getBooks: BelongsToManyGetAssociationsMixin<Book>
    declare hasBook: BelongsToManyHasAssociationMixin<Book, string>
    declare hasBooks: BelongsToManyHasAssociationsMixin<Book, string>
    declare removeBook: BelongsToManyRemoveAssociationMixin<Book, string>
    declare removeBooks: BelongsToManyRemoveAssociationsMixin<Book, string>
    declare setBooks: BelongsToManySetAssociationsMixin<Book, string>
}

Author.init({
    id: { type: DataTypes.UUID, defaultValue: DataTypes.UUIDV4, primaryKey: true, allowNull: false },
    firstName: { type: DataTypes.STRING, allowNull: false },
    lastName: { type: DataTypes.STRING, allowNull: false }
}, { timestamps: true, sequelize: sequelizeConnection, paranoid: true })

src/db/models/Book.ts

import {
    Optional, Model, DataTypes, BelongsToManyAddAssociationMixin,
    BelongsToManyAddAssociationsMixin,
    BelongsToManyCountAssociationsMixin,
    BelongsToManyCreateAssociationMixin,
    BelongsToManyGetAssociationsMixin,
    BelongsToManyHasAssociationMixin,
    BelongsToManyHasAssociationsMixin,
    BelongsToManyRemoveAssociationMixin,
    BelongsToManyRemoveAssociationsMixin,
    BelongsToManySetAssociationsMixin
} from "sequelize";
import sequelizeConnection from './../config';
import { Author } from "./Author";

export interface BookAttributes {
    id: string;
    title: string;
    isbn: string;
    numberOfPages: number;
    createdAt?: Date;
    updatedAt?: Date;
    deletedAt?: Date;
}

export interface BookInput extends Optional<BookAttributes, "id"> { }
export interface BookOutput extends Required<BookAttributes> { }

export class Book extends Model<BookAttributes, BookInput> implements BookAttributes {
    declare id: string;
    declare title: string;
    declare isbn: string;
    declare numberOfPages: number;

    declare readonly createdAt: Date;
    declare readonly updatedAt: Date;
    declare readonly deletedAt: Date;

    declare addAuthor: BelongsToManyAddAssociationMixin<Author, string>
    declare addAuthors: BelongsToManyAddAssociationsMixin<Author, string>
    declare countAuthors: BelongsToManyCountAssociationsMixin
    declare createAuthor: BelongsToManyCreateAssociationMixin<Author>
    declare getAuthors: BelongsToManyGetAssociationsMixin<Author>
    declare hasAuthor: BelongsToManyHasAssociationMixin<Author, string>
    declare hasAuthors: BelongsToManyHasAssociationsMixin<Author, string>
    declare removeAuthor: BelongsToManyRemoveAssociationMixin<Author, string>
    declare removeAuthors: BelongsToManyRemoveAssociationsMixin<Author, string>
    declare setAuthors: BelongsToManySetAssociationsMixin<Author, string>
}

Book.init({
    id: { type: DataTypes.UUID, defaultValue: DataTypes.UUIDV4, primaryKey: true, allowNull: false },
    title: { type: DataTypes.STRING, allowNull: false },
    isbn: { type: DataTypes.STRING, allowNull: false },
    numberOfPages: { type: DataTypes.INTEGER, allowNull: false },
}, { timestamps: true, sequelize: sequelizeConnection, paranoid: true })

Now we have added a bunch of additional methods that can help us communicate with a given model.

For example if we want to gets all books by a certain author, we can find an author and then call author.getBooks() and this will return an array of books by the chosen author. This OOP style interactions can improve code readability and reduces the need for us to create a query manually on fairly simple queries.

Initializing our database

Last step in our Sequelize setup is to initialize our database with the necessary tables.

src/db/init.ts

require('dotenv').config()

import { Author } from "./models/Author"
import { Book } from "./models/Book"
import { BookAuthor } from "./models/BookAuthor"

const isDev = process.env.NODE_ENV === 'development'
const isTest = process.env.NODE_ENV !== 'test'

const dbInit = () => Promise.all([
    Author.sync({ alter: isDev || isTest }),
    Book.sync({ alter: isDev || isTest }),
    BookAuthor.sync({ alter: isDev || isTest })
])

export default dbInit

The sync method is the way Sequelize performs model synchronizations, this ensures what we have describes in our models actually exists in the database and our queries have the correct table attributes.

Be very careful using the sync method as it can cause you to lose data that is stored on a database if you run it in a production environment with force or alter set to true, but it is very useful in development and test environments.

To ensure the database sync occurs when we spin up the server, lets add the dbinit function to our index file.

src/index.ts

import express, { Application } from 'express'
import dbInit from './db/init';

dbinit() // add dbinit as we are starting our server to ensure our models are synchronized

const app: Application = express()
const port = 3000

app.use(express.json());
app.use(express.urlencoded({ extended: true }));

app.listen(port, () => {
    console.log(`Server running on http://localhost:${port}`)
})

add our controller

For this example we will add a simple controller for the Author resource.

We will include the following routes:

POST /authors - Create a new author
GET /authors/:id - get a single author by id
GET authors/:id/books - get all books by author

src/controllers/AuthorController.ts

import { Request, Response } from "express";
import { Author, AuthorInput } from "../db/models/Author";
import { v4 as uuidv4 } from 'uuid';


export class AuthorController {
    public static async addAuthor(req: Request, res: Response) {
        const params: AuthorInput = { ...req.body, id: uuidv4() }

        try {
            const author = await Author.create(params);
            res.status(201).json(author);
        } catch (error) {
            res.status(500).json(error)
        }
    }

    public static async getAuthorById(req: Request, res: Response) {
        const id = req.params.id;

        try {
            const author = await Author.findByPk(id);
            res.status(200).json(author);
        } catch (error) {
            res.status(500).json(error)
        }
    }

    public static async getBooksByAuthorId(req: Request, res: Response) {
        const id = req.params.id;

        try {
            const author = await Author.findByPk(id);
            const books = await author?.getBooks();

            res.status(200).json(books);
        } catch (error) {
            res.status(500).json(error)
        }
    }
}

Define routes

src/routes/AuthorRouter.ts

import express, { Request, Response } from "express";
import { AuthorController } from '../controllers/AuthorController';

const AuthorRouter = express.Router();

AuthorRouter.post('/', (req: Request, res: Response) => AuthorController.addAuthor(req, res));
AuthorRouter.get('/:id', (req: Request, res: Response) => AuthorController.getAuthorById(req, res));
AuthorRouter.get('/:id/books', (req: Request, res: Response) => AuthorController.getBooksByAuthorId(req, res));

export { AuthorRouter }

Then we update our index.ts to include the AuthorRouter.

src.index.ts

import express, { Application } from 'express'
import dbInit from './db/init';
import { AuthorRouter } from './routes/AuthorRouter';

dbInit()

const app: Application = express()
const port = 3000

app.use(express.json());
app.use(express.urlencoded({ extended: true }));

app.use("/authors", AuthorRouter)

app.listen(port, () => {
    console.log(`Server running on http://localhost:${port}`)
})

Next Steps

You should now have a working API that perform a few basic CRUD operations on authors and books.

I hope you found this tutorial helpful and if you have any questions or comments feel free to reach out.

There are lots of areas to improve on the API as a whole, here are a handful of suggestions for what you can do next:

build out the Author and Book Routers with appropriate controllers
add tests
add a domain/business layer so that we aren't performing CRUD operations on the database models directly
add validation