Build a user management API with Nest.js and MySQL

Nest.js is an excellent option for building efficient Node.js applications. Paired with MySQL for the database, you can spin up a robust, scalable app with ease.

In this tutorial, you will learn how to connect a Nest.js RESTful API to a PlanetScale MySQL database. With this API, you will be able to:

• Create a new user.
• Retrieve a list of users from your database.
• Edit the details of a particular user.
• Retrieve and delete a single user.

To keep focus on the configuration of the MySQL database and connecting it to a Nest.js application, we will clone an existing application. This application will have a single user table, but will be sufficient to get you started with integrating Nest.js with PlanetScale.

The final source code for the application can be found in the repository.

Set up the project

To follow this tutorial, you will need the following:

• Node (version >=16.19.0)
• Nest CLI
• A PlanetScale account
• Basic knowledge of TypeScript

As mentioned, you will clone an existing Nest.js project from GitHub:

git clone https://github.com/planetscale/planetscale-nestjs nest-user-api

Navigate into the project and install its dependencies:

# Change directory
cd nest-user-api


# Install dependencies
npm install

Some of the existing dependencies in the project required for successful database connection and integration include:

• mysql2 — A MySQL database driver module for Nodejs applications.
• typeorm — A TypeScript ORM (Object relational mapper) library for connecting an application to a relational database.
• @nestjs/config — A configuration module for Nest.js application to load environment variables.
• @nestjs/typeorm — A TypeORM module for Nest.js.

Set up PlanetScale

Sign up for a PlanetScale account if you haven’t done that already.

Creating the MySQL database

After successfully setting up an account, you can now create the database for your application.

The image above depicts what your dashboard will look like if you haven’t created any databases. Click Create to set up your database or New database → Create new database if you have other databases. This will prompt you to specify your:

• Database name.
• Region.

Click Create database once you are done.

PlanetScale utilizes a git-like database branching workflow to make safe schema changes.

For this tutorial, you'll work in the main branch, which is created by default. If you ever deploy the Nest.js application you're working on, you can promote this database branch to production. Then, whenever you need to make schema changes, you'll make a new dev branch off of production.

Connect the app to PlanetScale

You can connect your application to the PlanetScale database in one of two ways:

• Using the PlanetScale proxy
• Entering your username and password

While you can use either of these options, this guide covers connecting with username and password.

Back in your PlanetScale dashboard, click on the "Branches" tab of your database, and select main.

1. Click the Connect button.
2. Copy the full set of credentials and store them somewhere, as you won’t be able to see them again when you leave the page. You can always generate a new set of credentials if you do forget to store them.

Next, you need to provide the connection parameters that will enable your app to connect to PlanetScale. To do this, create a new file named .env at the root directory of your application. In it, add the following:

DATABASE_HOST=<YOUR_DATABASE_HOST>
DATABASE_USER=<YOUR_DATABASE_USER_NAME>
DATABASE_PASSWORD=<YOUR_DATABASE_PASSWORD>
DATABASE_NAME=<YOUR_DATABASE_NAME>

These come from the database credential values that you just copied.

Run the application

With your database parameters defined, run the app using the following command:

npm run start:dev

The output should be similar to the screenshot below.

The application loads successfully, and sending a GET request to the index route (http://localhost:3000 by default) should show a Hello World! message.

In addition to the index route, you have four endpoints that allow you to perform CRUD (Create, Read, Update, and Delete) functions for users. You can find the relevant endpoints in src/user/user.controller.ts.

Viewing database schema

Something interesting to note is how Nest.js automatically generated and executed the necessary migrations for the application to run. Without issuing any additional commands, a user table was created on PlanetScale with all the relevant columns. To confirm this, open the dashboard overview of your application and click on Console.

Next, click Connect to open up a console in the browser. In the console, issue the following command.

SHOW TABLES;

This shows you all the tables in the database. At the moment, there is only one table — user, as shown below:

You can go one step further to see the structure of the user table with the following command:

DESCRIBE user;

This shows you all the columns in the user table, along with their data types, default values, and other descriptors. The result is shown below:

You can compare the results of the query with the entity definition for the User class in src/user/user.entity.ts, defined below:

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


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


  @Column()
  firstName: string


  @Column()
  lastName: string


  @Column()
  age: string


  @Column()
  address: string
}

Update the Nest.js database schema

As we’ve already established, Nest.js is able to automatically update the database schema when the entity is changed. This process also happens in real time, as you will see next. To test this, try adding two more fields to the User entity. Add the following code to src/user/user.entity.ts.

@Column()
profession: string;


@Column()
country: string;


@Column()
state: string;

Because Nest.js is watching the files, there is no need to stop the application before making any changes. Once a file change is detected, Nest.js will start an incremental compilation and re-serve the application. Once this is done, head back to your PlanetScale dashboard and view the updated table schema by running the DESCRIBE user command again.

As you can see, three new columns have been added for the user’s profession, country, and state.

Testing the Nest.js API using Postman

Next, you can test your API to see that it functions as expected. To do this, you can make API requests using Postman or any API testing platform, as shown below:

Deploy development database branch to production

In the early stages of product development, having the main branch and making changes directly to the database schema is fine, as the application is not serving any external clients. However, once customers start making use of your application and interacting with your database, this approach exposes you to several risks. There is the risk of data loss as a result of human error in the event that a column or table is accidentally dropped. Also, as the team grows, it may become more difficult to manage change if developers across the team are not aware of recent database changes.

To protect you from this, PlanetScale provides a branching workflow. The main branch can be promoted to production, where it will receive an additional replica and be protected from direct schema changes.

To update a production branch, a deploy request has to be created. PlanetScale compares the production version of your database schema with the version from the deploy request. It is able to detect dangerous schema alterations, as well as missing unique keys, and warn you accordingly. Additionally, the deploy request can be reviewed by the team, approved, and then queued. Queued requests are handled in a non-blocking manner, which means that there is no downtime while the migrations are executed. This ensures that your database is always available.

To promote your main branch to production, go to your database dashboard, and click Promote a branch to production. To protect this production branch from direct schema changes, you need to click the toggle to turn on safe migrations.

In the modal that pops up, click Promote branch and wait for the process to complete.

Once the branch has been promoted to production, head back to src/user/user.entity.ts and try adding a new column, as shown below:

@Column()
catchPhrase: string;

Nest.js will detect the changes and try to run the migration again. However, you will encounter an error this time, as shown below:

Because the branch has been promoted to production, you can no longer directly update the schema. Instead, what you will have to do is create a new branch and connect your development application to that branch. After making the changes, issue a deploy request as described above.

Conclusion

In this tutorial, you learned how to connect a Nest.js project to a MySQL database using PlanetScale. You’ve also been able to experience some of the safety measures put in place by PlanetScale to ensure that your database is not compromised once users start patronizing your application.

The knowledge gained here can be used for any Nest.js project, either built from scratch or existing.

I hope that you were able to understand the basics of creating APIs with Nest.js while connecting to PlanetScale using the mysql2 package.