Skip to main content

Data Migration using TypeORM and MYSQL database in NestJs

This tutorial assumes you already installed Node, VS Code, and MYSQL server on your local development machine. From your selected working directory, execute the following command to install nestjs cli and create nestjs-app project:

npm i -g @nestjs/cli

nest new nestjs-app

The nestjs-app directory is created and the project is initialized.  In the nestjs-app/src folder, app.module.ts, app.controller.ts, app.service.ts, and main.ts files are generated automatically. The main.ts file bootstraps your application. In the main.js file, you can specify port to run your application, folders to serve static contents, etc. The app.mudule is the root module of the application. The app.controller and app. service are basic controller and service. Execute the below command to run nestjs-app:

npm run start:dev

To work with MYSQL database using TypeORM, install the following required dependencies:

npm install --save @nestjs/typeorm typeorm mysql2

To connect to MYSQL database, import and config TypeOrmModule in the app.module. We set synchronize to true to automatically create database schema on every application launch. This option should be set to false in production to avoid data loss.

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

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'user',
      password: 'password',
      database: 'database_name',
      entities: [Product],
      //synchronize: false,
      synchronize: true,
    }),
   
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Create entities directory in src. Then, create Product entity in the entities directory.

entities/product.entity.ts
import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  description: string;

  @Column({ type: "float", default:0.0 })
  price: number;

 
  @Column()
  thumbnail: string;

 
  @Column()
  created_at: Date;

  @Column()
  updated_at: Date;


}

Save change to the project. After the application reloads., product table should be now created in the database.

In a real project, you may need to add new or rename columns in table without losing its data. To achieve the goal, you have to create migrations.  Let create a migration file to add user column to the product table.

First, create data-source.ts file in the src folder. The data source file specifies database connection credentials, entities, and migration files.

import "reflect-metadata"
import { DataSource } from "typeorm"
import { Product } from "./entities/product.entity"
export const AppDataSource = new DataSource({
    type: "mysql",
    host: "localhost",
    port: 3306,
    username: "user",
    password: "password",
    database: "database_name",
    entities: [Product],
    migrationsTableName: 'migrations',
    migrations:['src/migrations/*{.ts,.js}'],
    synchronize: false,
});

After that, execute the following command to create a migration file in src/migrations directory:

npx typeorm migration:create  ./src/migrations/AddUserColumnToProduct

The command will create <timestamp>-AddUserColumnToProduct.ts file in the src/migrations. Modify the <timestamp>-AddUserColumnToProduct.ts file to add and drop the user column:

import { MigrationInterface, QueryRunner } from "typeorm"

export class AddUserColumnToProduct1665571821346 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE product ADD user INT DEFAULT 0`)
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE product DROP COLUMN user`)
    }

}


To add user column to the product table in the database. Run the command below:

npx typeorm-ts-node-commonjs migration:run -d src/data-source.ts

If you would like to remove the user column from the product table, execute the command:

npx typeorm-ts-node-commonjs migration:revert -d src/data-source.ts

Comments

Post a Comment

Popular posts from this blog

Authentication & Role-Based Access Control Using Passport & JWT in NestJs MYSQL

In the earlier tutorials, you learnt to create API endpoints to access MYSQL database and upload files in NestJs. In a real world application, it is common to protect API endpoints by restricting the APIs and grant permissions to only authenticated users.  From a client app, a visitor can register a new account using username, email, password. The default role of the visitor is user. The password is encrypted using bcrypt package. Then after successful registration, he/she can login using username and password. Upon successful login, a valid JWT (JSON Web Token) will be generated and returned to the client. To get permission to access subsequent API endpoints, the client has to send the token in headers to be validated on server. The very popular package to do authentication in NestJs is Passport. In the nestjs app, execute the following commands to install dependencies: npm install --save @nestjs/passport passport passport-local npm install --save-dev @types/passport-local npm ins...
Post a Comment
Read more

Filter and count rows in Nestjs & MYSQL database

In the earlier tutorial, you learnt to connect NestJs app with MYSQL database and do migration to add new column to the product table. Now we move on creating APIs to filter data by name with counting the number of products, by id, and delete a specific product from the database.  Execute the following commands to create products module, controller, and service in products folder: npx nest g module products npx nest g controller products npx nest g service products Update products/products.module.ts file to specify the products repository used in the current scope using forFeature method.  import { TypeOrmModule } from '@nestjs/typeorm' ; import { Product } from 'src/entities/product.entity' ; import { ProductsController } from './products.controller' ; import { ProductsService } from './product.service' ; @ Module ({   imports: [ TypeOrmModule . forFeature ([ Product ])],   controllers: [ ProductsController ],   providers: [ ProductServi...
Post a Comment
Read more

Nestjs & Angular

NestJs is currently a popular server-side framework from Node. It is a useful tool to build secure and high performance enterprise-level applications. NestJs built on top of Express and Fastify is influenced by Angular (front-end framework) and OOP (Object-Oriented Programming).  With NestJs, you can build applications in any size with scalability with TypeScript and JavaScript . Your large applications can be divided in to different modules . Each module may have its own controllers ,  providers and services .  A Controller manages requests and responses. The route system maps the controller to specific request actions. The Controller frequently has more than one route.  A provider can be a service, factory, or helper class that can be injected as dependency so you create various relationships of different parts of your applications. Commonly we used providers to manage user authentication and authorization  and data sources from Mysql, PostgreSQL, MongoDB, ...
Post a Comment
Read more