How to Connect to a MySQL Database in NestJS

Overview

In this tutorial, we’ll explore the steps to establish a connection between a NestJS application and a MySQL database using TypeORM. This will include setting up the necessary modules, configuration, and different ways to interact with the database.

Before proceeding, ensure you have the following installed:

• Node.js
• NestJS CLI
• MySQL Server

Setting Up a New NestJS Project

First, let’s create a new NestJS project if you haven’t already:

nest new your-project-name

Navigate to the new directory:

cd your-project-name

Installing TypeORM and MySQL Driver

Within your project directory, install TypeORM and the MySQL Node.js driver:

npm install @nestjs/typeorm typeorm mysql2

Setting Up MySQL Module

Edit the ‘app.module.ts’ file to import TypeORMModule and set up a connection:

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'your_password',
      database: 'your_db_name',
      entities: [],
      synchronize: true,
    }),
  ],
})
export class AppModule {}

Creating a Database Connection Provider

If you prefer to configure the database connection asynchronously or outside ‘app.module.ts’, you can:

import { Module } from '@nestjs/common';
import { TypeOrmModule} from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRootAsync({
      useFactory: () => ({
        type: 'mysql',
        // ...other options
      }),
    }),
  ],
})
export class AppModule {}

Defining Entities

Define an entity that represents a table in your MySQL database:

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

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

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  // Other columns...
}

Add the entity to your module:

TypeOrmModule.forRoot({
  // ...
  entities: [User],
  // ...
}),

Performing CRUD Operations

Use the repository pattern to perform CRUD operations:

Creating a Record

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User)
    private usersRepository: Repository,
  ) {}

  create(firstName: string, lastName: string): Promise {
    const user = this.usersRepository.create({ firstName, lastName });

    return this.usersRepository.save(user);
  }
}

Reading Records

async findAll(): Promise<User[]> {
  return this.usersRepository.find();
}

Updating a Record

async update(id: number, userFields: Partial): Promise {
  await this.usersRepository.update(id, userFields);
  return this.usersRepository.findOne(id);
}

Deleting a Record

delete(id: number): Promise {
  return this.usersRepository.delete(id);
}

Advanced Configurations

To handle multiple databases, configurations can be split into separate files and connected through the ‘ConfigModule’ or using environment variables.

TypeOrmModule.forRootAsync({
  useFactory: (configService: ConfigService) => configService.get('database'),
  inject: [ConfigService],
}),

Conclusion

You’ve now learned how to connect your NestJS application to a MySQL database. We’ve covered installation, configuration, defining entities, and performing database operations using TypeORM. With these foundations, you can now continue building and evolving your application’s data layer.