API with NestJS #69. Database migrations with TypeORM

NestJS SQL

This entry is part 69 of 77 in the API with NestJS

When working with relational databases, we define the structure of the data rather strictly. For example, we need to specify the format of every table along with fields, relations, indexes, and other structures. By doing that, we also tell the database how to validate the incoming data.

It is crucial to think about the structure of our database carefully. Even if we do that, the requirements that our application has to meet change. Because of the above, we rarely can avoid having to modify the structure of our database. When doing that, we need to be careful not to lose any existing data.

With database migrations, we can define a set of controlled changes that aim to modify the structure of the data. They can include adding or removing tables, changing columns, or changing the data types, for example. While we could manually run SQL queries that make the necessary adjustments, this is not the optimal approach. Instead, we want our migrations to be easy to repeat across different application environments.

Also, we need to acknowledge that modifying the structure of the database is a delicate process where things can go wrong and damage the existing data. Fortunately, writing database migrations includes committing them to the repository. Therefore, they can undergo a rigorous review before merging to the master branch. In this article, we go through the idea of migrations and learn how to perform them with TypeORM.

Working with migrations using TypeORM

When configuring TypeORM, we can set the property to . This causes TypeORM to synchronize the database with our entities automatically. However, using it in production is highly discouraged because it might lead to unexpected data loss.

Instead, TypeORM has a tool that helps us create and run migrations. Unfortunately, its migration documentation is outdated and does not match the latest version.

Configuring the TypeORM CLI

To start working with migrations using TypeORM, we need to properly configure its command line interface (CLI). To do that, we need to create a designated configuration file.

typeOrm.config.ts

Above, we use dotenv to make sure the ConfigService loaded the environment variables before using it.

We also need to add some entries to the in our .

package.json

Unfortunately, the feature is not supported by yarn.

Generating our first migration

Let’s define a straightforward entity of a post.

post.entity.ts

If you want to know more about identity columns, check out Serial type versus identity columns in PostgreSQL and TypeORM

There is a significant caveat regarding the directory in our configuration. Let’s take a look at our NestJS database configuration.

database.module.ts

The @nestjs/typeorm library implements the that analyzes our NestJS application and identifies all of our entities. Unfortunately, the basic TypeORM configuration can’t do that.

We still need to add the entities we don’t use through to the array.

Because of the above, we need to manually add the to our array in our CLI configuration.

typeOrm.config.ts

We might be able to figure out a better approach if the CLI would support asynchronous DataSource creation. Once the pull request with the improvement is merged, we could create a NestJS application in our file and take advantage of the property.

Once we have all of the above set up, we can use the command to let TypeORM generate the migration file.

Running the migration command creates a file with the code that can bring our database from one state to another and back. Its filename consists of the current timestamp followed by the name provided when using the command.

1658694616973-CreatePost.ts

I used prettier on the generated file.

Above, there are two methods:

  • – performs the migration,
  • – reverts it.

Running the migrations

To run a migration, we must add it to the array in our file. Unfortunately, using strings with the array is deprecated and will stop working in TypeORM 0.4. Because of that, we should import the migration classes manually.

typeOrm.config.ts

Once we have the migration added to the array, we can run the command to execute it.

The above command yields the following logs:

Running the migration command does a few things. First, it identifies that the array contains a migration that wasn’t executed yet. It runs the method and creates the posts table.

Besides that, it also adds an entry to the table in the database. It indicates that the migration was executed.

Reverting migrations

To revert a migration, we need to use the command.

The above command produces the following logs:

Running the command executes the method in the latest performed migration and removes the respective row from the array. Therefore, if we need to revert more than one migration, we must use the command multiple times.

Creating migrations manually

Besides relying on TypeORM to generate the migrations for us, we can write their logic manually. Let’s start by making a slight change to the .

post.entity.ts

If you want to know more about managing dates with PostgreSQL, check out Managing date and time with PostgreSQL and TypeORM

Now, let’s run a command that tells TypeORM to create the basics of the migration for us.

By doing the above, we end up with the following file:

1658701645714-PostCreationDate.ts

I used prettier on the generated file.

When generating migrations, TypeORM uses and provides a raw SQL query. While that’s a viable approach, we can also use the migration API.

1658701645714-PostCreationDate.ts

To run our migration, we also need to add it to the array.

typeOrm.config.ts

Now, we can execute the migration by running the appropriate command.

Running the command gives us the following logs:

We can notice that using the migrations API seems to produce bigger queries when executing the migrations.

If we run our new migration, the table gets a new row:

Summary

In this article, we’ve learned what migrations are and how to manage them with TypeORM. To do that, we’ve set up the TypeORM CLI and used commands for creating, generating, running, and reverting migrations. We’ve also used the migrations API and noticed that it produces rather big SQL queries. All of the above gave us a thorough understanding of how to deal with migrations using TypeOrm.

Series Navigation<< API with NestJS #68. Interacting with the application through REPLAPI with NestJS #70. Defining dynamic modules >>
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Dave Lowe
Dave Lowe
1 month ago

this is super helpful – thanks so much for taking the time to put it together, i ran into a slight issue running on windows – the $npm_config_ process doesnt seem to work (at all) so i resolved by adding cross-env and then updating the npm scripts as follows

Tri
Tri
21 days ago
Reply to  Dave Lowe

Thanks you, but why cross-env solve that kind of problem, can you provide some info ?