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

Upload form data with image file in NestJs & MYSQL

 In the previous tutorial, you learned how to filter and count rows in NestJs with TypeORM & MYSQL database . This tutorial teaches you how to upload form data in NestJs & MYSQL. We will create two more routes to insert and update product data. A client app is able to submit form data with an image file. The max image file size in byte is 200000 and it must be jpg, jpeg, png, or gif type.  To upload files in NestJs, it is required to install Multer typings package. npm install @types/multer Create public/uploads folder in the nestjs project to store uploaded files. Then, update the products/ProductsController.tsx file to add the following code: ............................. import { UseInterceptors, ParseFilePipe , FileTypeValidator ,     MaxFileSizeValidator , UploadedFile } from '@nestjs/common' ; import { Express } from 'express' ; import { diskStorage } from 'multer' ; import * as path from 'path' ; import { Product } from ...
Post a Comment
Read more