API with NestJS #115. Database migrations with Prisma

NestJS SQL

This entry is part 115 of 158 in the API with NestJS

One of the characteristics of relational databases is a strict data structure. We need to specify the shape of every table with its fields, indexes, and relationships. Even if we design our database carefully, the requirements that our application must meet are changing. Because of that, our database needs to evolve as well. When restructuring our database, we need to be careful not to lose any existing data.

While we could manually run SQL queries to make changes to our database, it would not be straightforward to repeat across different application environments. Instead, with database migrations, we can modify our database with a set of controlled changes, such as adding tables and changing columns. Altering the structure of the database is a delicate process that can damage the existing data. With database migrations, we commit the SQL queries to the repository, where they have a chance to undergo a rigorous review before merging them into the master branch. In this article, we learn about migrations with Prisma.

Introducing Prisma migrations

In one of the previous parts of this series, we defined a simple schema of a post.

postSchema.prisma

Whenever we create new models or adjust the existing ones, we should create a migration using the Prisma CLI.

Running the above command generates a new file in the directory.

20230702195845_create_post/migration.sql

Prisma also automatically runs the above SQL query. It results in adjusting two tables in our database.

First, it creates the Post table based on our model. Then, it adds a row to the table so that Prisma can track which migrations were applied.

Adjusting migrations manually

In a previous part of this series, we modified the post model by adding the column and removing the column.

Unfortunately, relying on a migration generated by Prisma would destroy all data in the column. Since we want to avoid that, let’s use the flag.

Doing the above generates a migration but does not run it in our database yet.

20230702212422_add_post_paragraphs/migration.sql

We now have a chance to modify the above migration to avoid data loss.

20230702212422_add_post_paragraphs/migration.sql

Thanks to the above approach, we:

  • add the array column first,
  • copy the text from the column and set it as the first element of each column,
  • drop the column.

Thanks to the above approach, we restructure our table while keeping the data.

Now, we need to tell Prisma to run the migration.

Applying migration 20230702212422_post_paragraphs

The following migration have been applied:

migrations/
└─ 20230702212422_post_paragraphs/
└─ migration.sql

When we run the above command, Prisma compares the table with the migrations we have in our project. If there is a migration that hasn’t run yet, Prisma applies it to our database.

We should make the command a part of our automated CI/CD pipeline so that our changes can be populated to a production database.

Dealing with the schema drift

When we run the command, Prisma creates a temporary shadow database. It runs all our migrations there and compares the state of the shadow database with our regular development database. If they don’t match, it means there is a schema drift.

The shadow database is deleted automatically afterwards.

A schema drift might happen when we adjust the database manually instead of doing it through migration. Let’s simulate this problem by adding a new column without using a migration.

Now, let’s tell Prisma to look for the schema drift.

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[*] Changed the Post table
[+] Added column description

We need to reset the “public” schema at “localhost:5432”
Do you want to continue? All data will be lost. › (y/N)

Accepting the above would remove the changes we manually made to our database. However, there is a different solution. We can ask Prisma to update our schema based on the current state of the database.

When we run the above command, Prisma compares our database with our schema and makes changes to our file.

We can now create a migration based on our modified schema.

While the above command still resets our database, it preserves our manual changes and creates a new migration that includes them.

20230702221824_add_description_to_post/migration.sql

Breaking changes with new Prisma versions

Sometimes Prisma changes the naming conventions that they use with Prisma Migrate. A good example was switching from Prisma 2 to Prisma 3 when the approach to constraint and index names changed. The most straightforward solution for dealing with this is letting Prisma generate a migration that changes the affected constraint and indexes.

Running the above constraint can create a migration that updates the naming convention used in our project.

20230702013827_constraints_rename/migration.sql

An alternative is to run the command and let Prisma modify our schema so that we can keep the old naming convention in existing constraints and avoid creating a migration.

Summary

In this article, we’ve learned the concept of database migrations. With them, we can change our databases from various environments in a controlled way. Therefore, we should use them instead of modifying the database manually.

We used Prisma Migrate to perform our migrations. Besides the most basic situations, we’ve learned how to deal with some issues, such as the need to adjust migrations manually to avoid data loss. We’ve also seen what schema drift is and how to eliminate it. Besides the above, there might be some breaking changes in various versions of Prisma Migrate, such as changing the naming convention. Fortunately, we dealt with that in a straightforward way by creating a designated migration. Learning all of the above gave as a solid understanding of what migrations are and how to work with them in a project with NestJS and Prisma.

Series Navigation<< API with NestJS #114. Modifying data using PUT and PATCH methods with PrismaAPI with NestJS #116. REST API versioning >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments