Sequelize Models with Typescript using PostgreSQL and MySQL

Sequelize is an ORM (Object-Relational Mapping) library for Node.js. When combined with TypeScript, it provides type-checking for your database models. This comprehensive example tutorial will teach you everything you need to set up a Node.js TypeScript project with Sequelize while using MySQL and PostgreSQL.

In summary, you will learn:

• Creating TypeScript apps with MySQL or PostgreSQL alongside Sequelize (Sequelize ts).
• This example guide will create a Sequelize Typescript model with Node.js.
• Connecting a Typescript app with Sequelize and executing models to MySQL and PostgreSQL.
• Use Express.js for handling HTTP requests and routing alongside Sequelize ORM for database interactions.

Now, Dive into this step-by-step guide and have a Node.js TypeScript project ready to create Sequelize models with MySQL or PostgreSQL.

Related: Build a TypeScript Express App with PostgreSQL/pg Node.js Example

Setting up a Node.js TypeScript and Sequelize app

You need a working Node.js app. Create one using npm init -y. You will also need to use TypeScript. Therefore, TypeScript must be installed using the following command:

npm install typescript -g

Within this project, make the typescript ready before attempting to use Sequelize with the following command:

npx tsc --init

This should create a typescript configuration tsconfig.json file. It’s time to get Sequelize ready alongside MySQL or PostgreSQL. This means you need the following libraries:

npm install sequelize sequelize-typescript express mysql2 pg

The following packages should be installed as dev dependencies to support typescript types:

npm install --save-dev typescript ts-node @types/express @types/pg @types/node

Creating a typescript Sequelize Db Connection to MySQL/PostgreSQL

Once you have all dependencies ready, it is time to dive right into creating a typescript script that will use Node.js and Sequelize to create database connections.

In your working directory, create an src folder and add a db.ts file:

import { Sequelize } from 'sequelize-typescript';

const sequelize = new Sequelize({
  database: 'Task',
  host: 'localhost',
  username: 'postgres',
  password: 'pass',
  dialect: 'postgres', // or 'mysql' for MySQL
  //Specify the path to your models
  // Add each model to the sequelize instance
  models: [__dirname + '/models'],
});

export default sequelize;

What you need to know:

• Sequelize uses sequelize-typescript dependency to inherit typescript characteristics.
• You must have a database Task created in either MySQL or PostgreSQL.
• Add username and password based on your database.
• Check dialect. It can be postgres or mysql based on your choice.
• Note the path /models. It extends to the typescript Sequelize model. Based on your data, Let’s create a Sequelize typescript model for the task here.

Creating a typescript Sequelize Model

In Sequelize, a TypeScript model represents a structured database table definition. A TypeScript Sequelize model allows you to interact with the database table using TypeScript classes. This way, you define the schema, relationships, and methods associated with the table.

Create a models directory in your src folder and add Task.ts. Here’s the example of a TypeScript Sequelize model you need:

// models/Task.ts
import { Table, Column, DataType,Model} from 'sequelize-typescript';

@Table({
   timestamps: true ,
   tableName: "tasks",
   modelName: "Task"
})

export class Task extends Model {

  @Column({
    type: DataType.INTEGER,
    autoIncrement: true,
    primaryKey: true
  })

  declare id: number;

  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
   title!: string;

  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
  description!: string;
}

Note that you are explicitly using the Sequelize-typescript package here to:

• Create a tasks table using the above Sequelize TypeScript model.
• Adding table items and their related attributes such as ID, title, and description.

How to Add Data using TypeScript, Node.js and Sequelize

Now, you need Sequelize to communicate with your Postgres/MySQL database and add some records to the table tasks.

Here, you will Sequelize TypeScript methods with a controller and create a POST request to populate your table with data. First, create a controllers folder inside the src directory and add a taskController.ts file.

taskController.ts requires the use of the Sequelize model and express to dictate how TypeScript will add your data, so add the following imports:

// controllers/taskController.ts
import { Request, Response } from 'express';
import { Task } from '../models/Task';

Go ahead and add a POST Request using the following createTask method:

export const createTask = async (req: Request, res: Response) => {
  const { title, description } = req.body;
  const task = await Task.create({ title, description });
  res.json(task);
};

Here, you will use create() from Sequelize to POST data from your request body. The server will give you a JSON Response containing the added Task if created.

Fetching Data using TypeScript Express and Sequelize

I hope you already know that by using ORM such as Sequelize, you don’t need to execute SQL such as SELECT in your code. Now, with Sequelize, you only need to use findAll(), and Express will fetch all available items in your database table as follows:

export const getTasks = async (_: Request, res: Response) => {
  const tasks = await Task.findAll();
  res.json(tasks);
};

At the same time, Node.js and TypeScript will need to use Sequelize findByPk() if you only want to fetch a single item using the id as a parameter.

The same findByPk should be used to update or delete a specific item as follows:

export const updateTask = async (req: Request, res: Response) => {
  const { id } = req.params;
  const { title, description } = req.body;

  // Find the task by its primary key (ID)
  const task = await Task.findByPk(id);

  // Check if the task exists
  if (!task) {
    return res.status(404).json({ error: 'Task not found' });
  }

  // Update task properties with the new values
  task.title = title;
  task.description = description;

  // Save the updated task to the database
  await task.save();

  res.json(task);
};

export const deleteTask = async (req: Request, res: Response) => {
  // Find the task by its primary key (ID)
  const { id } = req.params;
  const task = await Task.findByPk(id);

  // Check if the task exists
  if (!task) {
    return res.status(404).json({ error: 'Task not found' });
  }

  // Delete the task from the database
  await task.destroy();
  // Respond with a success message
  res.json({ message: 'Task deleted successfully' });
};

Creating Node.js Express Routes

You need every method assigned to a Route to execute the above controllers. Create a routes folder in src and add taskRoutes.ts file.

Go ahead and use Node.js TypeScript and Express to design the following routes:

import express from 'express';
import {
  getTasks,
  createTask,
  updateTask,
  deleteTask,
} from '../controllers/taskController';

// Create an Express router
const router = express.Router();

// Define routes for different task-related actions

/**
 * Get all tasks
 * @route GET /tasks
 */
router.get('/tasks', getTasks);

/**
 * Create a new task
 * @route POST /tasks
 */
router.post('/tasks', createTask);

/**
 * Update a task by ID
 * @route PUT /tasks/:id
 */
router.put('/tasks/:id', updateTask);

/**
 * Delete a task by ID
 * @route DELETE /tasks/:id
 */
router.delete('/tasks/:id', deleteTask);

// Export the router for use in other parts of the application
export default router;

Sequelize Initialization

Initialize Sequelize and synchronize models with the database. Create an index.ts file in the src directory. Here:

• You will execute the app routes.
• Synchronize the models with the database.
• Expose the application on a specific PORT number.

Your index.ts file will look as follows:

import express from 'express';
import sequelize from './db'; 
import taskRoutes from './routes/taskRoutes';
import 'reflect-metadata';

// Create an Express application
const app = express();
// Set the port to default 3000
const PORT = process.env.PORT || 3000;  

// Middleware to parse incoming JSON requests
app.use(express.json());

// Use the taskRoutes for handling task-related routes
app.use(taskRoutes);

// Sync the Sequelize models with the database and start the server
sequelize.sync().then(() => {
  app.listen(PORT, () => {
    console.log(`Server is running at http://localhost:${PORT}`);
  });
});

Up to this point, the TypeScript Sequelize app is ready as follows:

Running Sequelize with TypeScript, Node.js and Express

Let’s test if the app is working or not. First, Go to your tsconfig.json file and make sure you have the following lines of code added inside "compilerOptions": {}

    "experimentalDecorators": true,
    "emitDecoratorMetadata": true,
    "rootDir": "./src", 
    "outDir": "./dist",

You have dist as the output folder for all emitted files. This means to run the server, you will execute the dist/index.js file. Therefore, you must also build your TypeScript code to JS using tsc.

Go to package.json and edit script tags. Add the following line:

"dev": "npx tsc && node dist/index.js"

In your rut directory, run the following command to start the server:

npm run dev

Sequelize will run your database, and Node.js will expose the server as follows:

At the same time, your database should now be ready and populated with your Model:

Testing the API with Express

The last step is to run the routes using Postman to verify the server is OK. Open Postman and send your first POST request to http://localhost:3000/tasks as follows:

{
  "title": "TypeScript and Sequelize",
  "description": "Sequelize Models with Typescript using MySQL and PostgreSQL"
}

These changes should be reflected in your database. Confirm that:

Or you can send a GET request to http://localhost:3000/tasks:

Conclusion

In this tutorial, you have learned how to create Sequelize Models with Typescript using MySQL and PostgreSQL. I hope you can use TypeScript to create database connections and Models using Sequelize alongside Node.js and Express. Check all code on this GitHub Repo.