Deploy NestJS with TypeScript and MySQL on Northflank

In my previous guide, I showed you how to set up a new NestJS project with the Nest CLI and then deploy it on Northflank. In this guide, I will show you how to add persistence to your NestJS project through the addition of a MySQL database. The full source code used in this guide can be found in this git repository.

1. In this guide, we will store our data inside a managed MySQL database instance provided by a Northflank addon. To create the MySQL database, inside the same Northflank project as your NestJS service, create a new addon and select the “MySQL” type.

2. To pass the database’s connection details to your NestJS service, we will use a Northflank secret group to automatically inject them into the service’s runtime environment. To do this, also inside the same Northflank project:

   1. Create a new secret group.

   2. Add the secret:

      Key	Value
      SSL_CA_CERTIFICATES	/etc/ssl/certs/ca-certificates.crt

   3. Finally link the following variables from your new MySQL addon:

      Variable name	Alias
      host	DB_HOST
      port	DB_PORT
      username	DB_USERNAME
      password	DB_PASSWORD
      database	DB_DATABASE

      • By linking variables from the addon you ensure that the database’s connection details are automatically injected into your service’s runtime environment without needing to manually manage them.

3. To provide database connectivity, NestJS uses TypeORM, but before we go about using TypeORM we will need to install it and a MySQL driver as dependencies in your NestJS project by running npm install --save @nestjs/typeorm typeorm pg.

4. Next, we need to import the TypeOrmModule into the root AppModule, that the NestJS CLI generated in the previous guide, and specify the environmental variables your database connection details are injected into. Once this is done, your app.module.ts file should contain the following:

   import { Module } from '@nestjs/common';
   import { TypeOrmModule } from '@nestjs/typeorm';
   import * as fs from 'fs';
   import { AppController } from './app.controller';
   import { AppService } from './app.service';
   
   @Module({
     imports: [
       TypeOrmModule.forRoot({
         type: 'mysql',
         host: process.env.DB_HOST,
         port: parseInt(process.env.DB_PORT),
         username: process.env.DB_USERNAME,
         password: process.env.DB_PASSWORD,
         database: process.env.DB_DATABASE,
         ssl: {
           ca: fs.readFileSync(process.env.SSL_CA_CERTIFICATES),
         },
   
         autoLoadEntities: true,
   
         // Only enable this option if your application is in development,
         // otherwise use TypeORM migrations to sync entity schemas:
         // https://typeorm.io/#/migrations
         synchronize: true,
       }),
     ],
     controllers: [AppController],
     providers: [AppService],
   })
   export class AppModule {}

Now your NestJS project is configured to connect to your MySQL database you can go about using it in your project. In the rest of this guide, I will show you how to set up a REST API, to manage a list of holiday destinations to visit, to demonstrate the database in action.

5. In the previous guide, we used the NestJS CLI to create a new NestJS project containing the basic setup we needed to start developing your application. We will now use the same CLI to generate additional elements within our project. In particular, we will create a “resource” that provides us with a set of REST API endpoints through which we can run CRUD (create, read, update, delete) operations on a list of holiday destinations. To do this:

   1. Run nest generate resource.

   2. When prompted enter the resource name destinations.

   3. Select REST API.

   4. Finally enter Y when asked whether or not we want to “generate CRUD entry points”.

6. Once the NestJS CLI has generated the destinations resource your project should contain a new folder named destinations that contains some of the logic needed to add the set of endpoints, however, we will have to complete this logic before we can start using these endpoints to manage the list of holiday destinations.

   Firstly, we need to populate the Destination entity schema with the fields we want to store for each destination:

   • An id, to make it easier to reference each destination.
   • A name of each destination.
   • A boolean value stating whether or not we have visited the destination.

   Once this is done, your destination.entity.ts file should contain the following:

   import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
   
   @Entity()
   export class Destination {
     @PrimaryGeneratedColumn()
     id: number;
   
     @Column()
     name: string;
   
     @Column({ default: false })
     visited: boolean;
   }

   • For more information on how to define an entity’s schema, check out the TypeORM documentation.

7. Next, we need to tell TypeORM that we have defined a new modal in the resource by importing it into DestinationsModule. Once this is done, your destinations.module.ts file should contain the following:

   import { Module } from '@nestjs/common';
   import { TypeOrmModule } from '@nestjs/typeorm';
   import { DestinationsService } from './destinations.service';
   import { DestinationsController } from './destinations.controller';
   import { Destination } from './entities/destination.entity';
   
   @Module({
     imports: [TypeOrmModule.forFeature([Destination])],
     controllers: [DestinationsController],
     providers: [DestinationsService],
   })
   export class DestinationsModule {}

8. Finally, we need to inject the Destination entity as a Repository, a class that allows us to manage instances of the Destination entity in the MySQL database, into the DestinationsService, a class that implements the CRUD operations. Then we need to use the Repository API to implement each method in the DestinationsServices. Once this is done, your destinations.service.ts file should contain the following:

   import { Injectable } from '@nestjs/common';
   import { InjectRepository } from '@nestjs/typeorm';
   import { Repository } from 'typeorm';
   import { CreateDestinationDto } from './dto/create-destination.dto';
   import { UpdateDestinationDto } from './dto/update-destination.dto';
   import { Destination } from './entities/destination.entity';
   
   @Injectable()
   export class DestinationsService {
     constructor(
       @InjectRepository(Destination)
       private destinationsRepository: Repository<Destination>,
     ) {}
   
     create(createDestinationDto: CreateDestinationDto) {
       return this.destinationsRepository.save(createDestinationDto);
     }
   
     findAll() {
       return this.destinationsRepository.find();
     }
   
     findOne(id: number) {
       return this.destinationsRepository.findOne(id);
     }
   
     update(id: number, updateDestinationDto: UpdateDestinationDto) {
       return this.destinationsRepository.update(id, updateDestinationDto);
     }
   
     remove(id: number) {
       return this.destinationsRepository.delete(id);
     }
   }

9. Before we test the API endpoints, we need to commit and push them to your Git repository and then wait for Northflank to build and deploy them. Once this is done we can test each of the API endpoints using Postman:

The full source code used in this guide can be found in this git repository.