Using Sequelize with TypeScript
Tags: typescript, webdev
Published: 2021-08-04 18:59:20

**Written by [Ibiyemi Adewakun] (https://blog.logrocket.com/author/ibiyemiadewakun/)** ✏️

Writing raw SQL in your API is so passé, or at best it’s reserved for really complex queries. These are simpler times for developing, and for most APIs, using one of many object-relational mappers (ORMs) is sufficient.

ORMs also conveniently encapsulate the intricate details of communicating with a database and its query language.

This means you can use a single ORM on multiple database types like MySQL, PostgreSQL, or MongoDb, making it easy to switch between databases without rewriting your code! You can also connect different types of databases to your project while using the same code to access them.

In this article, you’ll learn how to use the Sequelize ORM with TypeScript. So grab your laptops, open your IDE, and let’s get started!

## Prerequisites

To follow along with this article, install the following:

*   [`Node.js`](https://nodejs.org/en/)
*   [JavaScript package manager; we’ll use yarn](https://blog.logrocket.com/javascript-package-managers-compared/)
*   An IDE or text editor of your choice, like [Sublime Text](https://www.sublimetext.com/) or [Visual Studio Code](https://code.visualstudio.com/)

## Setting up the project

To begin our project, let’s set up [a simple Express.js API](https://expressjs.com/) to create a virtual cookbook that stores recipes and ingredients, and tags our recipes with popular categories.

First, let’s create our project directory by typing the following into our terminal:

```bash
$ mkdir cookbook
$ cd cookbook
```

Inside the new `cookbook` project directory, install the needed project dependencies using `yarn`. First, run `npm init` to initialize the Node.js project with a `package.json` file:

```bash
$ npm init
```

After the Node.js project initializes, install the dependencies starting with `express`:

```bash
$ yarn add express
```

Next, add [TypeScript](https://www.typescriptlang.org/) to the project by running the following:

```bash
$ yarn add -D typescript ts-node @types/express @types/node
```

💡 Note that we’ve added a flag, `-D`, to our installation command. This flag tells Yarn to add these libraries as dev dependencies, meaning these libraries are only needed when the project is in development. We also added type definitions for Express.js and Node.js.

With TypeScript added to our project, let’s initialize it:

```bash
$ npx tsc --init
```

This creates our TypeScript configuration file `ts.config`, and sets the default values:

```typescript
// ts.config
{
    "compilerOptions": {
      "target": "es5",                                
      "module": "commonjs",                           
      "sourceMap": true,                           
      "outDir": "dist",                              
      "strict": true,                                 
      "esModuleInterop": true,                        
      "skipLibCheck": true,                           
      "forceConsistentCasingInFileNames": true        
    }
}
```

Find more information about [customizing `ts.config` here](https://blog.logrocket.com/boost-your-productivity-with-typescript-project-references/).

Finally, let’s define a simple API structure for our project by creating project directories and files to match the outline below:

```
- dist # the name of our outDir set in tsconfig.json
- src
  - api
    - controllers
    - contracts
    - routes
    - services
  - db
    - dal
    - dto
    - models
    config.ts
    init.ts
  - errors
  index.ts
  ts.config
```

Now that we have defined our project structure in the `index.ts` file, which is our application’s starting point, add the following code to create our Express.js server:

```javascript
# src/index.ts

import express, { Application, Request, Response } from 'express'

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

// Body parsing Middleware
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.get('/', async(req: Request, res: Response): Promise<Response> => {
    return res.status(200).send({ message: `Welcome to the cookbook API! \n Endpoints available at http://localhost:${port}/api/v1` })
})

try {
    app.listen(port, () => {
        console.log(`Server running on http://localhost:${port}`)
    })
} catch (error) {
    console.log(`Error occurred: ${error.message}`)
}
```

We must also include some additional libraries to run the application easily and pass in environment variables. These additional libraries are [nodemon using `yarn add -D nodemon`](https://blog.logrocket.com/nodemon-tutorial-automatically-restart-node-js-apps-with-nodemon/), [eslint using `yarn add -D eslint`](https://eslint.org/), and [dotenv using `yarn add dotenv`](https://blog.logrocket.com/customizing-node-js-env-files/).

## Setting up the Sequelize ORM

At this point, the Express.js application is running, so it’s time to bring in the fun stuff: Sequelize ORM!

Start by adding Sequelize to the project by running the following:

```bash
$ yarn add sequelize
$ yarn add mysql2
```

Although we added the database driver for MySQL, which is solely based on personal preference, you can install any driver of your preferred database instead. View [here for other available database drivers](https://sequelize.org/master/manual/getting-started.html#installing).

### Initiating Sequelize’s connection

After installing Sequelize, we must initiate its connection to our database. Once initiated, this connection registers our models:

```javascript
# db/config.ts

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
```

### Creating and registering Sequelize models

Sequelize provides two ways to register models: [using `sequelize.define`](https://sequelize.org/master/manual/model-basics.html#using--a-href-----class-lib-sequelize-js-sequelize-html-instance-method-define---code-sequelize-define--code---a--) or extending the Sequelize model class. In this tutorial, we’ll use the model extension method to register our `Ingredient` model.

We begin by creating the interfaces of the following:

*   `IngredientAttributes` defines all the possible attributes of our model
*   `IngredientInput` defines the type of the object passed to Sequelize’s `model.create`
*   `IngredientOuput` defines the returned object from `model.create`, `model.update`, and `model.findOne`

```typescript
# db/models/Ingredient.ts

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

interface IngredientAttributes {
  id: number;
  name: string;
  slug: string;
  description?: string;
  foodGroup?: string;
  createdAt?: Date;
  updatedAt?: Date;
  deletedAt?: Date;
}
export interface IngredientInput extends Optional<IngredientAttributes, 'id' | 'slug'> {}
export interface IngredientOuput extends Required<IngredientAttributes> {}
```

Next, create an `Ingredient` class that extends, initializes, and exports the`import {Model} from 'sequelize'` Sequelize model class:

```typescript
# db/models/Ingredient.ts

...

class Ingredient extends Model<IngredientAttributes, IngredientInput> implements IngredientAttributes {
  public id!: number
  public name!: string
  public slug!: string
  public description!: string
  public foodGroup!: string

  // timestamps!
  public readonly createdAt!: Date;
  public readonly updatedAt!: Date;
  public readonly deletedAt!: Date;
}

Ingredient.init({
  id: {
    type: DataTypes.INTEGER.UNSIGNED,
    autoIncrement: true,
    primaryKey: true,
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  slug: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  description: {
    type: DataTypes.TEXT
  },
  foodGroup: {
    type: DataTypes.STRING
  }
}, {
  timestamps: true,
  sequelize: sequelizeConnection,
  paranoid: true
})

export default Ingredient
```

💡 Note we added the option `paranoid: true` to our model; this imposes a soft delete on the model by adding a `deletedAt` attribute that marks records as `deleted` when invoking the `destroy` method.

To complete our model and create its target table in the connected database, run the model `sync` method:

```typescript
# db/init.ts

import { Recipe, RecipeTags, Tag, Review, Ingredient, RecipeIngredients } from './models'
const isDev = process.env.NODE_ENV === 'development'

const dbInit = () => {
  Ingredient.sync({ alter: isDev })
}
export default dbInit 
```

💡 The `sync` method accepts the `force` and `alter` options. The `force` option forces the recreation of a table. The `alter` option creates the table if it does not exist or updates the table to match the attributes defined in the model.

💡 Pro tip: reserve using `force` or `alter` for development environments so you don’t accidentally recreate your production database, losing all your data or applying changes to your database that might break your application.

## Using our models in DAL and services

The data access layer (DAL) is where we implement our SQL queries, or in this case, where the Sequelize model queries run:

```typescript
# db/dal/ingredient.ts

import {Op} from 'sequelize'
import {Ingredient} from '../models'
import {GetAllIngredientsFilters} from './types'
import {IngredientInput, IngredientOuput} from '../models/Ingredient'

export const create = async (payload: IngredientInput): Promise<IngredientOuput> => {
    const ingredient = await Ingredient.create(payload)
    return ingredient
}

export const update = async (id: number, payload: Partial<IngredientInput>): Promise<IngredientOuput> => {
    const ingredient = await Ingredient.findByPk(id)
    if (!ingredient) {
        // @todo throw custom error
        throw new Error('not found')
    }
    const updatedIngredient = await (ingredient as Ingredient).update(payload)
    return updatedIngredient
}

export const getById = async (id: number): Promise<IngredientOuput> => {
    const ingredient = await Ingredient.findByPk(id)
    if (!ingredient) {
        // @todo throw custom error
        throw new Error('not found')
    }
    return ingredient
}

export const deleteById = async (id: number): Promise<boolean> => {
    const deletedIngredientCount = await Ingredient.destroy({
        where: {id}
    })
    return !!deletedIngredientCount
}

export const getAll = async (filters?: GetAllIngredientsFilters): Promise<IngredientOuput[]> => {
    return Ingredient.findAll({
        where: {
            ...(filters?.isDeleted && {deletedAt: {[Op.not]: null}})
        },
        ...((filters?.isDeleted || filters?.includeDeleted) && {paranoid: true})
    })
}
```

Adding the `paranoid: true` option to the `findAll` model method includes the soft-deleted records with `deletedAt` set in the result. Otherwise, the results exclude soft deleted records by default.

In our DAL above, we defined some commonly needed CRUD queries using our `ModelInput` type definition and placing any additional types in `db/dal/types.ts`:

```typescript
# db/dal/types.ts

export interface GetAllIngredientsFilters {
    isDeleted?: boolean
    includeDeleted?: boolean
}
```

💡 Sequelize ORM has some really cool model methods, including `findAndCountAll`, which returns a list of records and a count of all records matching the filter criteria. This is really useful for returning paginated list responses in an API.

Now we can create our service, which acts as an intermediary between our controller and DAL:

```typescript
# api/services/ingredientService.ts

import * as ingredientDal from '../dal/ingredient'
import {GetAllIngredientsFilters} from '../dal/types'
import {IngredientInput, IngredientOuput} from '../models/Ingredient'

export const create = (payload: IngredientInput): Promise<IngredientOuput> => {
    return ingredientDal.create(payload)
}
export const update = (id: number, payload: Partial<IngredientInput>): Promise<IngredientOuput> => {
    return ingredientDal.update(id, payload)
}
export const getById = (id: number): Promise<IngredientOuput> => {
    return ingredientDal.getById(id)
}
export const deleteById = (id: number): Promise<boolean> => {
    return ingredientDal.deleteById(id)
}
export const getAll = (filters: GetAllIngredientsFilters): Promise<IngredientOuput[]> => {
    return ingredientDal.getAll(filters)
}
```

## Powering up the model with routes and controllers

We’ve come a long way! Now that we have services fetching our data from our database, it’s time to bring all that magic to the public using routes and controllers.

Let’s start by creating our `Ingredients` routes in `src/api/routes/ingredients.ts`:

```typescript
# src/api/routes/ingredients.ts

import { Router } from 'express'

const ingredientsRouter = Router()
ingredientsRouter.get(':/slug', () => {
  // get ingredient
})
ingredientsRouter.put('/:id', () => {
  // update ingredient
})
ingredientsRouter.delete('/:id', () => {
  // delete ingredient
})
ingredientsRouter.post('/', () => {
  // create ingredient
})

export default ingredientsRouter
```

Our cookbook API will eventually have several routes, such as `Recipes` and `Tags`. So, we must create an `index.ts` file to register the different routes to their base paths and have one central export to connect to our Express.js server from earlier:

```typescript
# src/api/routes/index.ts

import { Router } from 'express'
import ingredientsRouter from './ingredients'

const router = Router()

router.use('/ingredients', ingredientsRouter)

export default router
```

Let’s update our `src/index.ts` to import our exported routes and register them to our Express.js server:

```javascript
# src/index.ts

import express, { Application, Request, Response } from 'express'
import routes from './api/routes'

const app: Application = express()

...

app.use('/api/v1', routes)
```

After creating and connecting the routes, let’s create a controller to link to our routes and call the service methods.

To support typing the parameters and results between the routes and controllers, let’s add data transfer objects (DTOs) and mappers to transform the results:

```typescript
# src/api/controllers/ingredient/index.ts

import * as service from '../../../db/services/IngredientService'
import {CreateIngredientDTO, UpdateIngredientDTO, FilterIngredientsDTO} from '../../dto/ingredient.dto'
import {Ingredient} from '../../interfaces'
import * as mapper from './mapper'

export const create = async(payload: CreateIngredientDTO): Promise<Ingredient> => {
    return mapper.toIngredient(await service.create(payload))
}
export const update = async (id: number, payload: UpdateIngredientDTO): Promise<Ingredient> => {
    return mapper.toIngredient(await service.update(id, payload))
}
export const getById = async (id: number): Promise<Ingredient> => {
    return mapper.toIngredient(await service.getById(id))
}
export const deleteById = async(id: number): Promise<Boolean> => {
    const isDeleted = await service.deleteById(id)
    return isDeleted
}
export const getAll = async(filters: FilterIngredientsDTO): Promise<Ingredient[]> => {
    return (await service.getAll(filters)).map(mapper.toIngredient)
}
```

Now, update the router with the calls to the controller:

```typescript
# src/api/routes/ingredients.ts

import { Router, Request, Response} from 'express'
import * as ingredientController from '../controllers/ingredient'
import {CreateIngredientDTO, FilterIngredientsDTO, UpdateIngredientDTO} from '../dto/ingredient.dto'

const ingredientsRouter = Router()

ingredientsRouter.get(':/id', async (req: Request, res: Response) => {
    const id = Number(req.params.id)
    const result = await ingredientController.getById(id)
    return res.status(200).send(result)
})
ingredientsRouter.put('/:id', async (req: Request, res: Response) => {
    const id = Number(req.params.id)
    const payload:UpdateIngredientDTO = req.body

    const result = await ingredientController.update(id, payload)
    return res.status(201).send(result)
})
ingredientsRouter.delete('/:id', async (req: Request, res: Response) => {
    const id = Number(req.params.id)

    const result = await ingredientController.deleteById(id)
    return res.status(204).send({
        success: result
    })
})
ingredientsRouter.post('/', async (req: Request, res: Response) => {
    const payload:CreateIngredientDTO = req.body
    const result = await ingredientController.create(payload)
    return res.status(200).send(result)
})
ingredientsRouter.get('/', async (req: Request, res: Response) => {
    const filters:FilterIngredientsDTO = req.query
    const results = await ingredientController.getAll(filters)
    return res.status(200).send(results)
})
export default ingredientsRouter 
```

At this point, we can add a build script to run our API:

```json
# package.json

...
"scripts": {
  "dev": "nodemon src/index.ts",
  "build": "npx tsc"
},
...
```

To see the final product, run the API using `yarn run dev` and visit our ingredient endpoints at http://localhost:3000/api/v1/ingredients.

## Conclusion

In this article, we set up a simple TypeScript application with Express.js to use the Sequelize ORM and walked through initializing Sequelize, creating our models, and running queries through the ORM.

Using Sequelize with TypeScript in our project helps us write less code and abstract the database engine while defining strict types for model input and output. This makes our code is more consistent, even if we change database types, and can prevent the occurrence of SQL injection to our tables.

The entire [code from this article is available on Github](https://github.com/ibywaks/cookbook). I hope you found this article easy to follow and I would love to hear any ideas you have on cool ways to use Sequelize in your application or any questions you have in the comment section!