Typescript ORM with MySQL example

Sequelize is a promise-based Node.js ORM that supports the dialects for Postgres, MySQL, SQL Server… Using an ORM can simplify database interactions, improve code maintainability, and speed up development by providing an object-oriented approach to working with relational databases. In this tutorial, I will show you step by step to implement CRUD operations using Typescript ORM with MySQL example.

You should install MySQL in your machine first. The installation instructions can be found at Official MySQL installation manual.

Working with following front-end:
– Angular 8 / Angular 10 / Angular 11 / Angular 12 / Angular 13 / Angular 14 / Angular 15 / Angular 16
– Vue 2 Typescript / Vue 3 Typescript
– React Typescript Component / React Typescript Hooks

Contents

Typescript
ORM
Import Typescript ORM and MySQL
Configure MySQL database
Initialize Database and ORM
Define the ORM Model
Create the Repository
Create the Controller and Routes
Run and Check
Conclusion
Further Reading
Source code

Typescript

TypeScript is an open-source programming language developed and maintained by Microsoft. It is a superset of JavaScript, which means that any valid JavaScript code is also valid TypeScript code. However, TypeScript adds static typing and other features to JavaScript to enhance its development experience and help catch errors at compile-time rather than runtime.

Here are some key features and concepts of TypeScript:

Static Typing: TypeScript introduces static typing, allowing you to explicitly declare the types of variables, function parameters, and return types. This helps catch type-related errors during development and provides better code documentation and editor support.
Type Inference: TypeScript has a powerful type inference system that can automatically infer the types of variables based on their initial values. This reduces the need for explicit type annotations while still providing the benefits of static typing.
Interfaces: TypeScript supports the definition of interfaces, which are used to define contracts for object structures. Interfaces specify the names and types of properties or methods that an object must have to conform to the interface.
Classes: TypeScript introduces classes, allowing you to use object-oriented programming concepts such as inheritance, encapsulation, and polymorphism. Classes in TypeScript can have properties, methods, constructors, and support for access modifiers like public, private, and protected.
Modules: TypeScript provides a module system that helps organize and encapsulate code into reusable units. Modules allow you to define public and private members and provide a way to structure larger applications.
Generics: TypeScript supports generics, which enable the creation of reusable components that can work with different types. Generics allow you to write code that is more flexible and type-safe by parameterizing types and functions.
Decorators: TypeScript supports decorators, which are a way to add metadata or modify the behavior of classes, methods, or properties at design time. Decorators are heavily used in frameworks like Angular for features like dependency injection, component declaration, and more.
Tooling and Integration: TypeScript integrates well with modern development tools and workflows. It provides excellent editor support with features like autocompletion, type checking, and refactoring. TypeScript code is transpiled to JavaScript, allowing it to run in any JavaScript environment.

ORM

ORM stands for Object-Relational Mapping. It is a programming technique and a software design pattern that allows developers to interact with relational databases using object-oriented programming languages. The main goal of an ORM is to bridge the gap between the relational database and the programming language, providing a high-level abstraction layer to work with database entities as objects.

Here are some key concepts and benefits of using an ORM:

Object-Relational Mapping: ORM frameworks provide mechanisms to map database tables and columns to corresponding objects and attributes in the programming language. This allows developers to interact with the database using familiar object-oriented concepts, such as classes, objects, and methods.
Abstraction of Database Queries: With an ORM, developers can perform database operations (such as creating, reading, updating, and deleting records) using high-level methods and APIs provided by the ORM framework. This abstracts away the need to write low-level SQL queries manually, simplifying the code and making it more maintainable.
Automatic Query Generation: ORMs can generate SQL queries dynamically based on the defined object mappings and the operations performed on the objects. This eliminates the need for developers to write complex SQL queries manually, reducing the risk of errors and saving development time.
Data Validation and Type Safety: ORM frameworks often provide built-in mechanisms for data validation, ensuring that the data being stored or retrieved from the database adheres to specified rules and constraints. Additionally, ORMs enforce type safety by mapping database columns to specific data types in the programming language.
Database Independence: ORMs can abstract away the specific database implementation details, allowing developers to write database-agnostic code. This makes it easier to switch between different database systems without having to rewrite the entire data access layer.
Relationship Management: ORMs typically provide features to handle relationships between database tables/entities, such as one-to-one, one-to-many, and many-to-many relationships. These relationships can be defined through object associations or annotations, and the ORM handles the necessary database operations transparently.
Performance Optimization: Many ORM frameworks offer features like lazy loading and caching to optimize performance. Lazy loading delays the loading of related data until it is explicitly accessed, reducing unnecessary database queries. Caching allows frequently accessed data to be stored in memory, improving performance by reducing database round trips.

Some popular ORM frameworks in the JavaScript ecosystem:
– Sequelize: A widely used ORM for Node.js and JavaScript, supporting various databases like MySQL, PostgreSQL, SQLite, and more.
– TypeORM: An ORM for TypeScript and JavaScript that supports multiple databases, including MySQL, PostgreSQL, SQLite, and MongoDB.
– Prisma: A modern database toolkit and ORM that provides a type-safe and auto-generated query builder for Node.js and TypeScript.

In this tutorial, we will use sequelize-typescript as our ORM.

Import Typescript ORM and MySQL

We need to install necessary modules: sequelize, sequelize-typescript, typescript, ts-node, @types/node and mysql2.

Run the command:

npm install typescript ts-node @types/node --save-dev
npm install sequelize sequelize-typescript mysql2

The tsconfig.json file needs the following flags:

{
  "compilerOptions": {
    ...
    "target": "es6", // or a more recent ecmascript version
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true,
    ...
}

Configure MySQL database

In the src folder, we create a separate config folder for configuration with db.config.ts file like this:

export const config = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

export const dialect = "mysql";

First five parameters are for MySQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

max: maximum number of connection in pool
min: minimum number of connection in pool
idle: maximum time, in milliseconds, that a connection can be idle before being released
acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Database and ORM

Using the configuration above, we’re gonna initialize Sequelize ORM with the following code:

src/db/index.ts

import { Sequelize } from "sequelize-typescript";
import { config, dialect } from "../config/db.config";
import Tutorial from "../models/tutorial.model";

class Database {
  public sequelize: Sequelize | undefined;

  constructor() {
    this.connectToDatabase();
  }

  private async connectToDatabase() {
    this.sequelize = new Sequelize({
      database: config.DB,
      username: config.USER,
      password: config.PASSWORD,
      host: config.HOST,
      dialect: dialect,
      pool: {
        max: config.pool.max,
        min: config.pool.min,
        acquire: config.pool.acquire,
        idle: config.pool.idle
      },
      models: [Tutorial]
    });

    await this.sequelize
      .authenticate()
      .then(() => {
        console.log("Connection has been established successfully.");
      })
      .catch((err) => {
        console.error("Unable to connect to the Database:", err);
      });
  }
}

export default Database;

Additional to Sequelize database configuration fields, we also need to specify path to models, which should be loaded for Sequelize instance:
new Sequelize({ ..., models: [Tutorial]});

Define the ORM Model

In models folder, create tutorial.model.ts file like this:

import { Model, Table, Column, DataType } from "sequelize-typescript";

@Table({
  tableName: "tutorials",
})
export default class Tutorial extends Model {
  @Column({
    type: DataType.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    field: "id"
  })
  id?: number;

  @Column({
    type: DataType.STRING(255),
    field: "title"
  })
  title?: string;

  @Column({
    type: DataType.STRING(255),
    field: "description"
  })
  description?: string;

  @Column({
    type: DataType.BOOLEAN,
    field: "published"
  })
  published?: boolean;
}

This Sequelize Model represents tutorials table in MySQL database. These columns will be generated automatically: id, title, description, published (and createdAt, updatedAt by default).

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them:

create a new Tutorial: create(object)
find a Tutorial by id: findByPk(id)
get all Tutorials: findAll()
update a Tutorial by id: update(data, where: { id: id })
remove a Tutorial: destroy(where: { id: id })
remove all Tutorials: destroy(where: {})
find all Tutorials by title: findAll({ where: { title: ... } })

These functions will be used in our Repository.

Create the Repository

Inside srx/repositories folder, let’s create tutorial.repository.ts with these CRUD functions:

save
retrieveAll
retrieveById
update
delete
deleteAll

import Tutorial from "../models/tutorial.model";

interface ITutorialRepository {
  save(tutorial: Tutorial): Promise<Tutorial>;
  retrieveAll(searchParams: {title: string, published: boolean}): Promise<Tutorial[]>;
  retrieveById(tutorialId: number): Promise<Tutorial | null>;
  update(tutorial: Tutorial): Promise<number>;
  delete(tutorialId: number): Promise<number>;
  deleteAll(): Promise<number>;
}

class TutorialRepository implements ITutorialRepository {
  async save(tutorial: Tutorial): Promise<Tutorial> { }

  async retrieveAll(searchParams: {title?: string, published?: boolean}): Promise<Tutorial[]> { }

  async retrieveById(tutorialId: number): Promise<Tutorial | null> { }

  async update(tutorial: Tutorial): Promise<number> { }

  async delete(tutorialId: number): Promise<number> { }

  async deleteAll(): Promise<number> { }
}

export default new TutorialRepository();

Let’s implement each CRUD operation.

Create new object

async save(tutorial: Tutorial): Promise<Tutorial> {
  try {
    return await Tutorial.create({
      title: tutorial.title,
      description: tutorial.description,
      published: tutorial.published
    });
  } catch (err) {
    throw new Error("Failed to create Tutorial!");
  }
}

Retrieve objects (with conditions)

import { Op } from "sequelize";

async retrieveAll(searchParams: {title?: string, published?: boolean}): Promise<Tutorial[]> {
  try {
    let condition: SearchCondition = {};

    if (searchParams?.published) condition.published = true;

    if (searchParams?.title)
      condition.title = { [Op.like]: `%${searchParams.title}%` };

    return await Tutorial.findAll({ where: condition });
  } catch (error) {
    throw new Error("Failed to retrieve Tutorials!");
  }
}

Retrieve object by Id

async retrieveById(tutorialId: number): Promise<Tutorial | null> {
  try {
    return await Tutorial.findByPk(tutorialId);
  } catch (error) {
    throw new Error("Failed to retrieve Tutorials!");
  }
}

Update an object

async update(tutorial: Tutorial): Promise<number> {
  const { id, title, description, published } = tutorial;

  try {
    const affectedRows = await Tutorial.update(
      { title, description, published },
      { where: { id: id } }
    );

    return affectedRows[0];
  } catch (error) {
    throw new Error("Failed to update Tutorial!");
  }
}

Delete an object

async delete(tutorialId: number): Promise<number> {
  try {
    const affectedRows = await Tutorial.destroy({ where: { id: tutorialId } });

    return affectedRows;
  } catch (error) {
    throw new Error("Failed to delete Tutorial!");
  }
}

Delete all objects

async deleteAll(): Promise<number> {
  try {
    return Tutorial.destroy({
      where: {},
      truncate: false
    });
  } catch (error) {
    throw new Error("Failed to delete Tutorials!");
  }
}

Create the Controller and Routes

Inside src/controllers folder, we create tutorial.controller.ts with these CRUD functions:

create
findAll
findOne
update
delete
deleteAll
findAllPublished

When a client sends request for an endpoint using HTTP request (GET, POST, PUT, DELETE), we need to determine how the server will response by setting up the routes.

These are our routes:

/api/tutorials: GET, POST, DELETE
/api/tutorials/:id: GET, PUT, DELETE
/api/tutorials/published: GET

Inside src/routes folder, create index.ts file.

import { Application } from "express";
import tutorialRoutes from "./tutorial.routes";

export default class Routes {
  constructor(app: Application) {
    app.use("/api/tutorials", tutorialRoutes);
  }
}

We import TutorialRoutes for handling HTTP Requests with /api/tutorials endpoint.
The tutorial.routes.ts will look like this:

So this is our project structure:

For more details about implementing the Controller and Routes, kindly visit:
Express Typescript example

Run and Check

Run the Node.js Typescript MySQL Rest APIs with command:
npm run start

The console shows:

Server is running on port 8080.
Executing (default): SELECT 1+1 AS result
Executing (default): SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'tutorials' AND TABLE_SCHEMA = 'testdb'
Connection has been established successfully.
Executing (default): CREATE TABLE IF NOT EXISTS `tutorials` (`id` INTEGER auto_increment , `title` VARCHAR(255), `description` VARCHAR(255), `published` TINYINT(1), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `tutorials` FROM `testdb`

Using Postman, we’re gonna test all the APIs above.

– Create a new Tutorial: POST http://localhost:8080/api/tutorials

After creating some new Tutorials, you can check MySQL table:

– Retrieve all Tutorials: GET http://localhost:8080/api/tutorials

– Retrieve a single Tutorial by id: GET http://localhost:8080/api/tutorials/[id]

– Update a Tutorial: PUT http://localhost:8080/api/tutorials/[id]

Check tutorials table after some rows were updated:

– Find all Tutorials which title contains ‘sql’: GET http://localhost:8080/api/tutorials?title=sql

– Find all published Tutorials: GET http://localhost:8080/api/tutorials/published

– Delete a Tutorial: DELETE http://localhost:8080/api/tutorials/[id]

Tutorial with id=4 was removed from tutorials table.

– Delete all Tutorials: DELETE http://localhost:8080/api/tutorials

Now there are no rows in tutorials table.

You can use the Simple HTTP Client using Axios to check it.

Or: Simple HTTP Client using Fetch API

Conclusion

Today, we’ve learned how to implement Typescript ORM with MySQL database and Sequelize. We also know way to add configuration for MySQL database & Sequelize, create a Sequelize Model, write a controller and define routes for handling all CRUD operations in a Node.js Rest API with an Express web server.

You can find more interesting thing in the next tutorial:
– Server side Pagination in Node.js with Sequelize and MySQL
Return pagination data in response:

{
    "totalItems": 8,
    "tutorials": [...],
    "totalPages": 3,
    "currentPage": 1
}

– Deploying/Hosting Node.js app on Heroku with MySQL database

Or you can save Image to MySQL database:
Upload/store images in MySQL using Node.js, Express & Multer

Happy learning! See you again.

Source code

You can find the complete source code for this example on Github.

If you want to add Comments for each Tutorial. It is the One-to-Many Association, there is a tutorial for that Relationship: Sequelize Associations: One-to-Many example – Node.js, MySQL

Or you can add Tags for each Tutorial and add Tutorials to Tag (Many-to-Many Relationship):
Sequelize Many-to-Many Association example with Node.js & MySQL

Without Sequelize: Node.js Typescript Rest APIs with Express & MySQL