Database migrations with Prisma

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.

1model Post {
2  id        Int      @default(autoincrement()) @id
3  title     String
4  content   String
5}

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

1npx prisma migrate dev --name create-post

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

1-- CreateTable
2CREATE TABLE "Post" (
3    "id" SERIAL NOT NULL,
4    "title" TEXT NOT NULL,
5    "content" TEXT NOT NULL,
6 
7    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
8);

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 _prisma_migrations 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 paragraphs column and removing the content column.

1model Post {
2  id         Int        @id @default(autoincrement())
3  title      String
4  paragraphs String[]
5}

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

1npx prisma migrate dev --create-only --name add-post-paragraphs

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

1/*
2  Warnings:
3 
4  - You are about to drop the column `content` on the `Post` table. All the data in the column will be lost.
5 
6*/
7-- AlterTable
8ALTER TABLE "Post" DROP COLUMN "content",
9ADD COLUMN     "paragraphs" TEXT[];

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

1ALTER TABLE "Post"
2ADD COLUMN "paragraphs" TEXT[];
3 
4UPDATE "Post"
5SET paragraphs = ARRAY[content];
6 
7ALTER TABLE "Post"
8DROP COLUMN content;

Thanks to the above approach, we:

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

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

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

1npx prisma migrate deploy

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 _prisma_migrations 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 migrate deploy 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 migrate dev 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.

1ALTER TABLE "Post"
2ADD COLUMN description TEXT;

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

1npx prisma migrate dev

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.

1npx prisma db pull

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

1model Post {
2  id          Int        @id @default(autoincrement())
3  title       String
4  paragraphs  String[]
5  description String?
6}

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

1npx prisma migrate dev --name add_description_to_post

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

1-- AlterTable
2ALTER TABLE "Post" ADD COLUMN     "description" TEXT;

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.

1npx prisma migrate dev --name constraints-rename

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

1-- DropForeignKey
2ALTER TABLE "Post" DROP CONSTRAINT "Post_authorId_fkey";
3 
4-- AddForeignKey
5ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
6 
7-- RenameIndex
8ALTER INDEX "User.email_unique" RENAME TO "User_email_key";
9 
10-- RenameIndex
11ALTER INDEX "User_addressId_unique" RENAME TO "User_addressId_key";

An alternative is to run the npx prisma db pull 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.