(NestJS -3) Integrating TypeORM with MySql in NestJS

12 min readMay 22, 2024

This article will guide you through the steps needed to set up TypeORM with MySQL in a NestJS project in various ways. We will cover everything from the initial configuration to advanced usage, ensuring you have a comprehensive understanding of how to leverage these tools effectively in your application.

If you’re new to NestJS or need a refresher on its basics, we recommend checking out our previous article, “Getting Started with NestJS.” It provides a solid foundation that will help you follow along more easily with this guide.

Additionally, we will show you how to configure your project to access environment variables, building upon the configurations we discussed in our previous article. This will enable you to manage sensitive information and configuration settings securely and efficiently.

By the end of this article, you will have a well-configured NestJS project that uses TypeORM and MySQL, ready to handle complex database operations.

Advantages of using TypeORM

It supports various types of database like MYSQL, SQLITE, MongoDB and soon.
It is helpful for developers to write queries in an object oriented way instead of writing in SQL.
It helps the developer to manage schema, migrations and relationships between tables.
It can be easily configured and used.
It can support small and large applications.
It reduces development time a lot.
You can use query builders for advanced database operations.

Prerequisites

Before we begin, ensure you have the following installed on your machine:

Node.js (version >= 16)
NPM
MySQL server
A basic NestJS application (refer to our article “Getting Started with NestJS” if needed)

Note: This article is a continuation of the previous article.

Mysql Setup

Log in to MySQL: Open your command line interface (CLI) and log in to MySQL as the root user or another user which has the necessary privileges to create a database.

mysql -u root -p

Create database: You can create a new database with the following command.

CREATE DATABASE [database-name]; # I have created nestjs as database

Show databases: To list all databases use the below command.

show databases; //you can list extsting databases

For extra security, Create new Database use and grant the permissions for only created database

Setup Environment Variables and Configurations

Simple, It a file has confidential data values as a key-value pair.

Now, Create a .env file in the root directory of your application and define the environment variables as follows:

APPNAME = 'NestJS-Application'
APPVERSION = 'v1.0'
APP_PORT = 4000

DB_HOST = 'localhost'
DB_PORT = 3306
DB_USERNAME = 'root'
DB_PASSWORD = 'root'
DATABASE = 'nestjs'
DB_SYNC = true // make false in production

Install, dotenv package module to access variables in application:

$ npm install --save dotenv

we can use access the data using process.env.<key> For different different environment setup file we have to config the file-path of file using config function in dotenv. then we can directly access the data in regular way. Example as shown bellow:

import { NestFactory } from '@nestjs/core';
import { AppModule } from './app.module';
import { ConfigService } from '@nestjs/config';
import * as dotenv from 'dotenv';
dotenv.config({ path: process.cwd() + '/.env' }); 
// the cmd method will return the current working directory of the Node.js process.
// .env.development is a file which i am using for storing environmental variables.
// if you are using normal .env file, the above import and config steps are not required.


async function bootstrap() {
  const app = await NestFactory.create(AppModule);
  const configService = app.get(ConfigService);

  // Retrieving the application port from the configuration or using the default value of 3000
  const port = configService.get<number>('app.port', 3000); // Defaults to 3000 if not specified
  
  await app.listen(port);
  // Logging the application URL to confirm successful startup
  console.log(`This application is runnning on: ${await app.getUrl()}`)
}
bootstrap()

For more information visit my article how to setup and read .env in different environments.

Configuring TypeORM with MySQL

Install TypeORM along with the MySQL driver:

$ npm install --save @nestjs/typeorm typeorm mysql2

Note: Use any one method to config TypeOrm.

1. Direct Configuration in `app.module.ts`

Open the app.module.ts file and configure TypeORM using MYSQL credentials:

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
   TypeOrmModule.forRoot({
      type: 'mysql',// Specifying the database type as mysql and it also supports mariadb
      host: 'localhost',// Database host, in this case, it's localhost
      port: 3306,// Port number where the database is running, 3306 is the default for MySQL
      username: 'root',// Username for connecting to the database
      password: 'root',// Password for the database user
      database:'nestjs',// Name of the database to connect to
      synchronize: true,// Indicates if database schema should be auto created on every start
      autoLoadEntities: true,
      //Logger setings to log error's and warn's in the ORM.      
      logger:'file',
      logging:["error"]
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule { }

2. Using Environment Variables in `app.module.ts`

Open the app.module.ts file and configure TypeORM using credentials from environment variables:

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { TypeOrmModule } from '@nestjs/typeorm';
import { MyConfigModule } from './config/config.module';

@Module({
  imports: [
    MyConfigModule,
    TypeOrmModule.forRoot({
      type: 'mysql', // Specifying the database type as mysql and it also supports mariadb
      // Replace env config names as you defines in file.
      host: process.env.DB_HOST,
      port: parseInt(process.env.DB_PORT),
      username: process.env.DB_USERNAME,
      password: process.env.DB_PASSWORD,
      database: process.env.DATABASE,
      synchronize: process.env.DB_SYNC == 'true', 
      autoLoadEntities: true,
      //Logger setings to log error's and warn's in the ORM.
      logger: 'file',
      logging: ["error"]
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule { }

Please ensure that the database user has the privilege to make changes in the database, and set synchronization to false in production environments, as well as in development if not required.

Creating a Module

Now, we will create new module, controller, service to handle the requested data and store it in database.

We can generate those files in 2 ways:

Note: Use any one way to generate resourses.

Create individual files at single time. using below command:

# use module, controller, or service in place of file-type
$ nest g [file-type] product

2. Generate an entire module with CRUD endpoints use below command:

$ nest g resource product

If you choose the second step, select REST API as the transport layer and enter Y or YES to generate CRUD entry points, if not N or No. This will create multiple DTO files and entities for better readability.

Try to maintain the same module structure for all.

Define Entity

Entities are the building blocks of your database schema. Simply an entity object represents a database table, we define Entities to interact with the database.

Advantages of using entities

It provide a way to abstract database tables into TypeScript classes, making it easier to work with databases using an object-oriented approach.
It provides different decorators to ensure data integrity and consistency.
It allow you to define relationships between different tables like (e.g., one-to-many, many-to-many).
It simplifies to use database queries and operations.

To maintain a consistent naming convention for your entity files in a NestJS project, you should name them using the [module-name].entity.ts format.

The TypeORM provides an Entity decorator to define Entity. Also provides some decorators to define properties of data table columns like PrimaryColumn, Column and soon.

If you create a module with the CRUD generator try to use the entity file which is present in the entities folder within the module, if not create a file as product.entity.ts in the product directory and define the structure of the product table. Let’s see an example of an entity in the code below:

// product.entity.ts
import { Column, CreateDateColumn, DeleteDateColumn, Entity, PrimaryColumn, UpdateDateColumn } from "typeorm";

@Entity()
export class Product {
    @PrimaryColumn({ generated: "uuid" }) //it is used to generate primary id, when new data inserted.
    id:string;

    @Column() // It is used to mark a specific class property as a table column
    name: string;
  
    @Column()
    description: string;
  
    @Column({ type: 'decimal' })
    price: number;

    @CreateDateColumn()
    createdon:Date;
    
    @UpdateDateColumn()
    updatedon:Date;

    @DeleteDateColumn()
    deletedon:Date;
}

Note: Without the @Entity decorator, TypeORM will not recognize the class as an entity, and it won't map it to a database table.

We import the product entity in the product.module.ts and register it as an TypeORM Feature to use in application:

// product.module.ts
import { Module } from '@nestjs/common';
import { ProductService } from './product.service';
import { ProductController } from './product.controller';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Product } from './entities/product.entity';  // the entity file path can be changed

@Module({
  imports: [TypeOrmModule.forFeature([Product])],
  controllers: [ProductController],
  providers: [ProductService],
})
export class ProductModule {}

Note: If the synchronize is true it will automatically create tables in database, if table exist already it will create the columns which are defined in product class.

From here, we begin to write the business logic in our application.

CRUD Operations:

Create: Add new data to the database.
Read: Retrieve and view existing data.
Update: Modify existing data.
Delete: Remove data from the database.

We will perform these operations using the database.

Define DTO

The DTO (Data Transfer Objects) are used to define the data structure of user requested data.

Advantages of DTO

Used to define the data structure of incoming and outgoing data.
Used to define validation rules for incoming data.
It makes code clean, easier to maintain.
DTOs can be used to generate API documentation automatically.

The DTOs are also used to validate the

To maintain a consistent naming convention for DTO files in a NestJS project, name them using the [module-name].dto.ts format.

Some developers use multiple files in a single module folder by naming that group as dto folder, while others use a single file for the entire module.

Here, we will use only a single DTO file named product.dto.ts to specify the data structures for creating and updating products.

// dto/create-product.dto.ts

export class CreateProductDto {
  name: string;
  description: string;
  price: number;
}

// dto/update-product.dto.ts

import { PartialType } from '@nestjs/mapped-types';
import { CreateProductDto } from './create-product.dto';

export class UpdateProductDto extends PartialType(CreateProductDto) {
    id: string;
}

And I have updated the product.controller.ts to handle product ID as a string:

//product.controller.ts

import { Controller, Get, Post, Body, Param, Delete, Put } from '@nestjs/common';
import { ProductService } from './product.service';
import { CreateProductDto } from './dto/create-product.dto';
import { UpdateProductDto } from './dto/update-product.dto';

@Controller('product')
export class ProductController {
  constructor(
    private readonly productService: ProductService
  ) { }

  @Post()
  async create(@Body() createProductDto: CreateProductDto) {
    return this.productService.create(createProductDto);
  }

  @Get()
  async findAll() {
    return this.productService.findAll();
  }

  @Get(':id')
  async findOne(@Param('id') id: string) {
    return this.productService.findOne(id);
  }

  @Put()
  async update(@Body() updateProductDto: UpdateProductDto) {
    return this.productService.update(updateProductDto);
  }

  @Delete(':id')
  async remove(@Param('id') id: string) {
    return this.productService.remove(id);
  }
}

To use entities in service class, we inject it as repositories in constructor to perform database operations like shown in below code.

//product.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Product } from './entities/product.entity';
import { CreateProductDto } from './dto/create-product.dto';
import { UpdateProductDto } from './dto/update-product.dto';
@Injectable()
export class ProductService {
  constructor(
    @InjectRepository(Product)
    private readonly productRepo: Repository<Product>,
  ) { }

  async create(createProductDto: CreateProductDto): Promise<Product> {
    const newProduct = new Product(); //creating a product object to initialize its values
    newProduct.name = createProductDto.name;
    newProduct.description = createProductDto.description;
    newProduct.price = createProductDto.price;
    return await this.productRepo.save(newProduct); // saving the new product in product table
    //or you can use "insert" in place of save
  }

  async findAll(): Promise<Product[]> {
    return this.productRepo.find(); //returning all the products
  }

  async findOne(id: string): Promise<Product> {
    return this.productRepo.findOneBy({ id: id }) //returning a specific product
  }

  async update(updateProductDto: UpdateProductDto): Promise<string> {
    await this.productRepo.update(updateProductDto.id, updateProductDto); // updating the product
    return "Product updated sucessfullly";
  }

  async remove(id: string): Promise<string> {
    this.productRepo.delete({ id: id }); //deleting the product
    return "Product deleted successfully";
  }
}

In above example, we used a decorator InjectRepository and a class Repository.

Repository: A Repository in TypeORM is a class that allows you to manage and interact with a specific entity in your database. In this case, the UserMaster entity. A repository provides methods to perform common database operations like inserting, finding, updating, and deleting records.
Inject Repository: The @InjectRepository decorator is used in NestJS to inject a repository for a specific entity. This decorator is part of the @nestjs/typeorm package and is necessary because it allows NestJS to know which repository (entity) to inject into the service.

Also we used some TypeORM methods to do CRUD operations to manipulate data in database. Those methods are: save, find, update, delete. We will try to look deep into those methods in the upcoming article of this series.

We will discuss more about the CRUD operation in the upcoming article.

Run application

Make sure the MYSQL synchronize: true or MYSQL_SYNC = true to synchronize database.

Now start the application in the terminal or command prompt using the command below in the root directory path. This command will start the HTTP server on the port you have defined in the file:

$ npm run start

we have another command which will watch the file changes in the application and reload it on save:

$ npm run start:dev

If application is running, you can use web browse or postman to test, use base request URLhttp://localhost:4000

When an application running the tables will be created if it does not exist, which we created as an entity in application.

You can cross check in the database using the below command:

$ mysql -p
$ use [database-name]; #example nestjs
$ show tables;

Issues

Database connectivity error.

ERROR [TypeOrmModule] Unable to connect to the database. Retrying (1)...
Error: connect ECONNREFUSED ::1:3306
    at __node_internal_captureLargerStackTrace (node:internal/errors:490:5)
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1494:16)

Solution:
This error indicates that the connection to the database was refused, likely due to incorrect credentials, misconfiguration, or the database server not running. To resolve this issue:

I. Verify Database Server Status:

for Linux:
Ensure the database server is running. You can check this with a command like:

# for linux environment
sudo systemctl status mysql

2. for Windows:

Press Win + R, type services.msc, and press Enter.
Look for the service named MySQL.
Ensure the status is Running. If not, right-click the service and click Start.

II. Test Database Connectivity from Command Line:
Use the credentials configured in your application to log in via the command line:

mysql -h localhost -u <username> -p<password>

Replace <username> and <password> with your actual credentials.

III. Check Database Host and Port:
Ensure the host and port in your TypeOrmModule configuration are correct.

Testing

If you already have Postman installed, open it. If not, you can download it from the official Postman website.

Create a new request in postman, select request type and add base request URL. Click on body and select raw then JSON format this is for post and put requests.

1.Add New Product

Now add the body for the request. The body structure should be same as which we have defined in CreateProductDto

{
  "name": "Computer",
  "description": "A high-performance desktop computer",
  "price": 12000
}

2. Updating existing product

Now, you can use same request by changing request type to put, or can create new request. In body the Id of product should be mandatory exists and it is same as while it has been created:

{
    "price": "15000",
    "id": "01bca5b8-a8d9-4888-a4b5-1221d70d361a"  #  id can be different
}

3. Get All Products

Retrieve all product.

4. Find single product with Id

Retrieving a product by ID.

5. Delete the product

Deleting a product by ID.

Key Takeaways:

Always use environment variables for sensitive configurations.
Set synchronize to false in production to avoid accidental schema changes.
Use DTOs for data validation and clarity.
Inject repositories for clean and testable service logic.
Leverage TypeORM decorators to define entities and relationships between tables.

For further exploration, you can add features like:

Migrations: To manage schema changes.
Soft Deletes: To mark data as deleted without physically removing it from the database.
Advanced Queries: Using TypeORM’s QueryBuilder for complex operations.

Conclusion

With this setup, you should now have a fully functioning NestJS application integrated with TypeORM and MySQL.

In this article, we covered how to set up a NestJS application integrated with TypeORM and MySQL. Additionally, we explored using a custom config module with TypeORM.

Check out the next article to learn about configuring and using a logger.

To configure Custom config Module, refer to my previous article.

GitHub Repository:
You can find the complete code for this implementation on GitHub: bhargavachary123

Contribution

If you like my Article, please, consider donating through Buy Me a Coffee: https://buymeacoffee.com/bhargavachary.

If you found this article useful, please consider leaving a clap (👏) and a comment.

Thanks for reading this far!

(NestJS -3) Integrating TypeORM with MySql in NestJS

Advantages of using TypeORM

Prerequisites

Mysql Setup

Setup Environment Variables and Configurations

Configuring TypeORM with MySQL

1. Direct Configuration in `app.module.ts`

2. Using Environment Variables in `app.module.ts`

Creating a Module

Define Entity

Advantages of using entities

CRUD Operations:

Define DTO

Advantages of DTO

Run application

Issues

Testing

Key Takeaways:

Conclusion

Contribution

Written by Bhargava Chary

No responses yet

(NestJS -3) Integrating TypeORM with MySql in NestJS

Advantages of using TypeORM

Prerequisites

Mysql Setup

Setup Environment Variables and Configurations

Configuring TypeORM with MySQL

1. Direct Configuration in app.module.ts

2. Using Environment Variables in app.module.ts

Creating a Module

Define Entity

Advantages of using entities

CRUD Operations:

Define DTO

Advantages of DTO

Run application

Issues

Testing

Key Takeaways:

Conclusion

Contribution

Written by Bhargava Chary

No responses yet

1. Direct Configuration in `app.module.ts`

2. Using Environment Variables in `app.module.ts`