Effortless database schema migration with Prisma

Database schema migrations are some of the most frequent and important tasks performed by developers, especially as product requirements evolve. If these migrations are not performed correctly, there can be disastrous consequences, such as data loss and schema inconsistencies, leading to an inconsistent database state.

However, they are quite tedious to perform and manage. To solve this problem, Prisma ORM created a tool called Prisma Migrate that provides mechanisms to perform and manage database schema migrations seamlessly.

In this article, we will have an in-depth practical look at how to perform database schema migrations using the Prisma ORM. We will start by designing a basic schema of a social media application model and work our way through making simple and complex changes to this schema. In essence, we’ll see how Prisma’s built-in functionality (called Prisma Migrate) makes creating and managing database migrations as simple as possible.

Prerequisites

Due to the practical aspects in later sections of this article, you should have working knowledge of Node.js and JavaScript. You’ll also need to install and set up PostgreSQL locally, following the guide from the official website.

Contents

• What is Prisma?
• What are database schema migrations?
• Setting up the project
• Modeling a social media platform
• Applying simple schema updates
• Applying complex schema updates
• Limitations of Prisma Migrate

What is Prisma?

Prisma is an ORM (Object Relational Mapper) that provides a high-level abstraction over raw database queries and database management functionalities. It provides a type-safe API and JavaScript classes for performing common database queries.

In the context of this article, Prisma provides a robust migration system called Prisma Migrate, which simplifies the process of creating and managing database schema migrations. We’ll see how to leverage this migration system later, but before then, let’s do a quick refresher on database schema migrations from a general database perspective.

What are database schema migrations?

Database schema migrations are a way to manage incremental changes made to an existing database schema, typically due to changing requirements or fixing initial design mistakes. Such changes in a relational database include adding or removing columns and tables, changing specific data types for certain columns, among other things.

We typically perform schema migrations using a migration file that contains SQL code with the necessary changes. It can be pretty challenging to manually manage migration files, especially when the database contains real-world user data. However, Prisma provides mechanisms to make this process less daunting and risky than usual by using a specific feature called Prisma Migrate.

Setting up the project

With that covered, let’s set up a project and get into the meat of the article. First, head over to a suitable directory and run the following command in the terminal:

npm init

The command above initializes the directory and creates a package.json file. When that’s complete, run the following command to add the Prisma package as a dev dependency:

npm install prisma --save-dev

Then, we can initialize a Prisma project by running another command in the terminal:

npx prisma init

The command above initializes a Prisma project by creating a directory called prisma and a file within that directory called schema.prisma. This file is where most of the work will take place, and we’ll get to that in a bit.

Next up, we’ll create a model schema of a simple social media application; this schema will serve as a basis for making incremental changes, mimicking what we’ll typically encounter in a real-world scenario.

Modeling a social media platform

It should be mentioned once again that although this schema is very basic, it will help us understand how we can perform schema migrations in Prisma. With that said, go ahead and paste the following code into the schema.prisma file, and we’ll go through it right after:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url = env("DATABASE_URL")
}

model Users {
  id String @id @db.Uuid @default(uuid())
  fullName String
  email String @unique
  password String
  bio String

  @@map("users")
}

model Posts {
  id String @id @db.Uuid @default(uuid())
  userId String
  postId String
  content String

  @@map("posts")
}

model Followers {
  id String @id @db.Uuid @default(uuid())
  userId String
  followerId String

  @@map("followers")
}

model Likes {
  id String @id @db.Uuid @default(uuid())
  userId String
  postId String

  @@map("likes")
}

model Comments {
  id String @id @db.Uuid @default(uuid())
  userId String
  postId String
  content String

  @@map("comments")
}

The schema file above contains a schema of the application designed using a special syntax provided by Prisma. The models map directly to tables in the underlying relational database.

One key advantage of this method is that Prisma restricts data modeling to the database layer, unlike other ORMs that require some level of modeling on the application level. There may be obvious design issues with the schema above, such as no relationships between tables; this was done on purpose.

In the following sections, we’ll see how we can fix these issues by making simple schema changes (adding columns, changing data types, and establishing relationships) to complex schema changes (making changes to the schema with existing data in the database).

To apply the schema to the database, run this command in the terminal directory:

npx prisma migrate dev --name init

When we ran npx prisma init previously, Prisma generated a .env file for managing application environmental variables. This file contains an environmental variable called DATABASE_URL. Replace the value in there with a URL to connect to your local (or remote) Postgres database connection. For reference, I connected to my local database using this:
postgresql://postgres:admin@localhost:5433/prisma-migration?schema=public

When that command is done running, we should be able to see actual tables and columns existing in that database. Prisma also generates a folder called migrations in the same directory as the schema.prisma file. If we open the newly created migration file, we should be able to see the raw SQL statements automatically generated by Prisma. Under the hood, Prisma converted the contents of the schema.prisma file to raw SQL statements and applied that to the database.

Let’s go ahead and make changes to the database schema.

Applying simple schema updates

Next up, we will make relatively straightforward changes to the schema and apply them.

Adding a new column to an existing table.

Let’s assume business requirements have evolved, and we want users of our application to have unique usernames. To implement this change on the schema level, head over to the schema.prisma file and add the highlighted line in the code snippet below, and we’ll go through it after:

model Users {
  ...
  username String @unique
  ...
}

In the code snippet above, we add a new column to the Users table and enforce a unique constraint, ensuring that multiple users cannot have the same username. To apply this change, all we need to do is tell Prisma to synchronise our changes with the database; we can easily do this by running the following code in the terminal:

npx prisma migrate dev --name added_username_column

Adding a relationship

Now let’s look at a more complex change to our schema. It’s clear that there are no relationships between the tables in our schema due to “bad design,” so let’s see how we can make such changes. Let’s define a one-to-many relationship between the Users table and the Posts table such that a user can have many posts. Change to the schema to implement this:

model Users {
  id String @id @db.Uuid @default(uuid())
  fullName String
  username String @unique
  email String @unique
  password String
  bio String
  posts Posts[]

  @@map("users")
}

model Posts {
  id String @id @db.Uuid @default(uuid())
  postId String
  content String
  user Users @relation(fields: [userId], references: [id])
  userId String // (used in the `@relation` attribute above)

  @@map("posts")
}

Once again, all we need to do is run the following command in the terminal, and Prisma Migrate automatically syncs these changes with the database:

npx prisma migrate dev --name connect_users_posts_tables

This is the simplicity of applying schema updates with Prisma Migrate. However, there are important things to note. After Prisma applies the migration files, they should never be manually edited or deleted. Doing this can lead to inconsistencies, forcing Prisma to give a prompt asking to perform a database reset which can lead to loss of data. In summary, we can end up with mismatched histories between versions of the migration files. Also, migrate dev should only be run in development environments as this may lead to issues if executed in production environments.

Applying complex schema updates

In the past two sections, we’ve looked at how to make changes to our schema and how easily Prisma makes this. However, we’ve only examined this without having any data in the database. It becomes a bit more tricky when we have data, because the wrong changes can lead to data loss or data inconsistencies in the database. In this section, we’ll briefly examine how to apply changes to our schema in such scenarios.

The first step is to add one or more users to the application, add some users manually or run the following SQL:

INSERT INTO users (id, "fullName", email, password, bio, username)
VALUES ('7f93880a-a8c3-4d4f-b3b3-369aa89a73fa', 'John Doe', '[email protected]', 'johndoe', 'Im a software developer', 'johndoe');

With that done, let’s say further down the line, we decide to rename the bio column in the user table to biography; we can seemingly do this by simply changing the User model in the prisma.schema file like so:

model Users {
  ...
  biography String
  ...
}

Now, if we try to commit this change using Prisma Migrate, we’ll get an error along these lines:

Error: ⚠️ We found changes that cannot be executed: Step 0 Added the required column biography to the users table without a default value. There are 1 rows in this table, it is not possible to execute this step.
You can use prisma migrate dev --create-only to create the migration file, and manually modify it to address the underlying issue(s). Then run prisma migrate dev to apply it and verify it works.

This is primarily because we have one or more rows in the users table and the migration generated by Prisma is potentially destructive. To prevent this, Prisma prompts us to create the migration file without syncing it to the database immediately. We can do this by running:

npx prisma migrate dev --name update-biography --create-only

With that, a migration file is generated but not applied immediately. Let’s have a look at it; open the migration file, and it should look like something along these lines:

ALTER TABLE "users" DROP COLUMN "bio",
ADD COLUMN "biography" TEXT NOT NULL;

It might be obvious why this is an issue – the SQL above drops the bio column and then adds a new column called biography, but there is at least a row with a value on that column which means that we will lose all data in that column. So, Prisma throws a warning and allows us to update the file manually.

In this case, to solve the problem, we can easily update or rewrite the SQL in the migration file to this:

ALTER TABLE "users" RENAME COLUMN "bio" TO "biography";

The new SQL statement allows us to rename the column without data loss. With that, all we need to do is tell Prisma to sync the migration file by running the usual:

npx prisma migrate dev

And voilà, our updated schema file is synchronized with the database.

Limitations of Prisma Migrate

Prisma Migrate doesn’t currently support MongoDB database providers; this is only a limitation if you plan on using MongoDB.

In dev environments, Prisma Migrate may sometimes send a prompt to reset the database; this, unfortunately, leads to data loss in your dev environment. If we have seed files, this won’t be much of a problem as the database can be re-seeded with data. It’s important to note that this prompt to reset the database does not happen in production environments.

Finally, Prisma Migrate doesn’t allow us to apply migrations to different database providers specified in the schema.prisma file, i.e., If we create a migration file for a PostgreSQL provider in dev environments, we cannot apply that migration to a MySQL provider in production environments.

Conclusion

In this article, we went through the process of performing schema migrations using Prisma Migrate. We did this by modeling a simple social media platform, making incremental changes to the schema, and using the functionalities provided by Prisma Migrate to create and apply a migration automatically.