Easy database schema migration with Prisma

Database schema migrations are among the most frequent and essential duties carried out by builders, particularly as product necessities evolve. If these migrations usually are not carried out appropriately, there may be disastrous penalties, corresponding to information loss and schema inconsistencies, resulting in an inconsistent database state.

Nonetheless, they’re fairly tedious to carry out and handle. To resolve this downside, Prisma ORM created a device known as Prisma Migrate that gives mechanisms to carry out and handle database schema migrations seamlessly.

On this article, we may have an in-depth sensible take a look at find out how to carry out database schema migrations utilizing the Prisma ORM. We are going to begin by designing a fundamental schema of a social media software mannequin and work our approach by means of making easy and complicated modifications to this schema. In essence, we’ll see how Prisma’s built-in performance (known as Prisma Migrate) makes creating and managing database migrations so simple as potential.

Conditions

Because of the sensible points in later sections of this text, you need to have working information of Node.js and JavaScript. You’ll additionally want to put in and arrange PostgreSQL domestically, following the information from the official web site.

Contents

What’s Prisma?

Prisma is an ORM (Object Relational Mapper) that gives a high-level abstraction over uncooked database queries and database administration functionalities. It supplies a type-safe API and JavaScript lessons for performing frequent database queries.

Within the context of this text, Prisma supplies a sturdy migration system known as Prisma Migrate, which simplifies the method of making and managing database schema migrations. We’ll see find out how to leverage this migration system later, however earlier than then, let’s do a fast refresher on database schema migrations from a common database perspective.

What are database schema migrations?

Database schema migrations are a approach to handle incremental modifications made to an present database schema, usually on account of altering necessities or fixing preliminary design errors. Such modifications in a relational database embody including or eradicating columns and tables, altering particular information sorts for sure columns, amongst different issues.

We usually carry out schema migrations utilizing a migration file that accommodates SQL code with the required modifications. It may be fairly difficult to manually handle migration recordsdata, particularly when the database accommodates real-world consumer information. Nonetheless, Prisma supplies mechanisms to make this course of much less daunting and dangerous than normal by utilizing a selected characteristic known as Prisma Migrate.

Establishing the challenge

With that coated, let’s arrange a challenge and get into the meat of the article. First, head over to an acceptable listing and run the next command within the terminal:

npm init

The command above initializes the listing and creates a package deal.json file. When that’s full, run the next command so as to add the Prisma package deal as a dev dependency:

npm set up prisma --save-dev

Then, we will initialize a Prisma challenge by working one other command within the terminal:

npx prisma init

The command above initializes a Prisma challenge by making a listing known as prisma and a file inside that listing known as schema.prisma. This file is the place many of the work will happen, and we’ll get to that in a bit.

Subsequent up, we’ll create a mannequin schema of a easy social media software; this schema will function a foundation for making incremental modifications, mimicking what we’ll usually encounter in a real-world state of affairs.

It must be talked about as soon as once more that though this schema could be very fundamental, it should assist us perceive how we will carry out schema migrations in Prisma. With that stated, go forward and paste the next code into the schema.prisma file, and we’ll undergo it proper after:

generator shopper {
  supplier = "prisma-client-js"
}

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

mannequin Customers {
  id String @id @db.Uuid @default(uuid())
  fullName String
  electronic mail String @distinctive
  password String
  bio String

  @@map("customers")
}

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

  @@map("posts")
}

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

  @@map("followers")
}

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

  @@map("likes")
}

mannequin Feedback {
  id String @id @db.Uuid @default(uuid())
  userId String
  postId String
  content material String

  @@map("feedback")
}

The schema file above accommodates a schema of the appliance designed utilizing a particular syntax offered by Prisma. The fashions map on to tables within the underlying relational database.

One key benefit of this technique is that Prisma restricts information modeling to the database layer, not like different ORMs that require some stage of modeling on the appliance stage. There could also be apparent design points with the schema above, corresponding to no relationships between tables; this was completed on objective.

Within the following sections, we’ll see how we will repair these points by making easy schema modifications (including columns, altering information sorts, and establishing relationships) to complicated schema modifications (making modifications to the schema with present information within the database).

To use the schema to the database, run this command within the terminal listing:

npx prisma migrate dev --name init

After we ran npx prisma init beforehand, Prisma generated a .env file for managing software environmental variables. This file accommodates an environmental variable known as DATABASE_URL. Change the worth in there with a URL to connect with your native (or distant) Postgres database connection. For reference, I linked to my native database utilizing this:
postgresql://postgres:[email protected]:5433/prisma-migration?schema=public

When that command is completed working, we should always be capable to see precise tables and columns present in that database. Prisma additionally generates a folder known as migrations in the identical listing because the schema.prisma file. If we open the newly created migration file, we should always be capable to see the uncooked SQL statements routinely generated by Prisma. Beneath the hood, Prisma transformed the contents of the schema.prisma file to uncooked SQL statements and utilized that to the database.

Let’s go forward and make modifications to the database schema.

Making use of easy schema updates

Subsequent up, we are going to make comparatively simple modifications to the schema and apply them.

Including a brand new column to an present desk.

Let’s assume enterprise necessities have advanced, and we wish customers of our software to have distinctive usernames. To implement this variation on the schema stage, head over to the schema.prisma file and add the highlighted line within the code snippet beneath, and we’ll undergo it after:

mannequin Customers {
  ...
  username String @distinctive
  ...
}

Within the code snippet above, we add a brand new column to the Customers desk and implement a singular constraint, making certain that a number of customers can’t have the identical username. To use this variation, all we have to do is inform Prisma to synchronise our modifications with the database; we will simply do that by working the next code within the terminal:

npx prisma migrate dev --name added_username_column

Including a relationship

Now let’s take a look at a extra complicated change to our schema. It’s clear that there aren’t any relationships between the tables in our schema on account of “dangerous design,” so let’s see how we will make such modifications. Let’s outline a one-to-many relationship between the Customers desk and the Posts desk such {that a} consumer can have many posts. Change to the schema to implement this:

mannequin Customers {
  id String @id @db.Uuid @default(uuid())
  fullName String
  username String @distinctive
  electronic mail String @distinctive
  password String
  bio String
  posts Posts[]

  @@map("customers")
}

mannequin Posts {
  id String @id @db.Uuid @default(uuid())
  postId String
  content material String
  consumer Customers @relation(fields: [userId], references: [id])
  userId String // (used within the `@relation` attribute above)

  @@map("posts")
}

As soon as once more, all we have to do is run the next command within the terminal, and Prisma Migrate routinely syncs these modifications with the database:

npx prisma migrate dev --name connect_users_posts_tables

That is the simplicity of making use of schema updates with Prisma Migrate. Nonetheless, there are essential issues to notice. After Prisma applies the migration recordsdata, they need to by no means be manually edited or deleted. Doing this will result in inconsistencies, forcing Prisma to offer a immediate asking to carry out a database reset which might result in lack of information. In abstract, we will find yourself with mismatched histories between variations of the migration recordsdata. Additionally, migrate dev ought to solely be run in improvement environments as this will likely result in points if executed in manufacturing environments.

Making use of complicated schema updates

Previously two sections, we’ve checked out find out how to make modifications to our schema and the way simply Prisma makes this. Nonetheless, we’ve solely examined this with out having any information within the database. It turns into a bit extra difficult when now we have information, as a result of the flawed modifications can result in information loss or information inconsistencies within the database. On this part, we’ll briefly look at find out how to apply modifications to our schema in such situations.

Step one is so as to add a number of customers to the appliance, add some customers manually or run the next SQL:

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

With that completed, let’s say additional down the road, we determine to rename the bio column within the consumer desk to biography; we will seemingly do that by merely altering the Person mannequin within the prisma.schema file like so:

mannequin Customers {
  ...
  biography String
  ...
}

Now, if we attempt to commit this variation utilizing Prisma Migrate, we’ll get an error alongside these strains:

Error: ⚠️ We discovered modifications that can not be executed: Step 0 Added the required column biography to the customers desk with out a default worth. There are 1 rows on this desk, it's not potential to execute this step.
You need to use prisma migrate dev --create-only to create the migration file, and manually modify it to deal with the underlying difficulty(s). Then run prisma migrate dev to use it and confirm it really works.

That is primarily as a result of now we have a number of rows within the customers desk and the migration generated by Prisma is doubtlessly damaging. To forestall this, Prisma prompts us to create the migration file with out syncing it to the database instantly. We are able to do that by working:

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

With that, a migration file is generated however not utilized instantly. Let’s take a look at it; open the migration file, and it ought to appear like one thing alongside these strains:

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

It is likely to be apparent why this is a matter – the SQL above drops the bio column after which provides a brand new column known as biography, however there’s a minimum of a row with a price on that column which implies that we’ll lose all information in that column. So, Prisma throws a warning and permits us to replace the file manually.

On this case, to resolve the issue, we will simply replace or rewrite the SQL within the migration file to this:

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

The brand new SQL assertion permits us to rename the column with out information loss. With that, all we have to do is inform Prisma to sync the migration file by working the standard:

npx prisma migrate dev

And voilà, our up to date schema file is synchronized with the database.

Limitations of Prisma Migrate

Prisma Migrate doesn’t at present help MongoDB database suppliers; that is solely a limitation in case you plan on utilizing MongoDB.

In dev environments, Prisma Migrate might typically ship a immediate to reset the database; this, sadly, results in information loss in your dev surroundings. If now we have seed recordsdata, this received’t be a lot of an issue because the database may be re-seeded with information. It’s essential to notice that this immediate to reset the database doesn’t occur in manufacturing environments.

Lastly, Prisma Migrate doesn’t enable us to use migrations to totally different database suppliers specified within the schema.prisma file, i.e., If we create a migration file for a PostgreSQL supplier in dev environments, we can’t apply that migration to a MySQL supplier in manufacturing environments.

Conclusion

On this article, we went by means of the method of performing schema migrations utilizing Prisma Migrate. We did this by modeling a easy social media platform, making incremental modifications to the schema, and utilizing the functionalities offered by Prisma Migrate to create and apply a migration routinely.