Data Migrations
Loading "Intro to Data Migrations"
Run locally for transcripts
It's great that Prisma allows us to easily represent our database models and
their relationships in a
schema.prisma
file. And we can easily make our
database just look like our schema by running npx prisma db push
. But what
if we make a "breaking schema change" that requires us to migrate our data?Let's talk about what I mean by "breaking schema change". Let's say we have a
sandwich ordering shop, like in the example from the first exercise. Here's the
order model:
model Order {
id String @id @default(uuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
status String
userId String
user User @relation(fields: [userId], references: [id])
orderItems OrderItem[]
}
What if we decided to add a
Reviews
model and have reviews on the order?
That's a pretty straightforward change using the knowledge we've accumulated so
far. Simply create the Reviews
model and add it to the order model:model Order {
id String @id @default(uuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
status String
userId String
user User @relation(fields: [userId], references: [id])
orderItems OrderItem[]
reviews Review[]
}
model Review {
id String @id @default(uuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
rating Int
text String?
orderId String
order Order @relation(fields: [orderId], references: [id])
}
We can
npx prisma db push
this change with no issues.But let's look at the
User
model:model User {
id String @id @default(uuid())
email String @unique
username String
name String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
orders Order[]
}
What if we decided we wanted to make the
name
required? Or what if we decided
we want to make the username
unique? These are "breaking schema changes" which
necessitate a data migration.Managing these data migrations is tricky business and often involves custom SQL
or an intrusive ORM that may not quite fit with what you need done so you end up
having to workaround the limitations.
Prisma takes the middle ground approach. Instead of completely hiding the
details of the migration, Prisma generates the SQL for the migration and saves
it to disk. From there you are free to alter it as needed.
This SQL file should be committed to your git repository and deployed alongside
your app. That way you can track database changes over time. Prisma will also
keep track of which migrations have been applied to your database so you don't
have to worry about that. (It creates a very small table in your database for
this). So when you deploy your app, you simply run
npx prisma migrate deploy
and Prisma will take care of the rest.Example
I've found this to be helpful in my own work. As a personal example, on
kentcdodds.com I have user accounts and originally I
had my user roles field optional. I later realized this didn't work well for me,
so I decided to switch it to a required field. I couldn't just
npx prisma db push
this change because I already had users who didn't have an
assigned role
.So I created a migration to make the
role
field required and default to
MEMBER
. But then I modified the SQL to set the role
to MEMBER
for all
users and then updated the role
field for my own membership to ADMIN
.
Here's the migration file:-- CreateEnum
CREATE TYPE "Role" AS ENUM ('ADMIN', 'MEMBER');
-- AlterTable
ALTER TABLE "User" ADD COLUMN "role" "Role" DEFAULT E'MEMBER';
-- Manually written stuff:
-- Update all users to be members:
update "User" set role = E'MEMBER';
-- update me@kentcdodds.com to be ADMIN:
update "User" set role = E'ADMIN' where email = 'me@kentcdodds.com';
-- make role required
ALTER TABLE "User" ALTER COLUMN "role" SET NOT NULL;
And because I had prisma migrations set up to run whenever I deploy new code
changes I got everything updated and working as expected.
However, I could have had some issues if I had users signing up while I was
deploying this change. I would have had to do some extra work to make sure that
I didn't lose any data.
π¦ If you're curious what a migration directory looks like after a few
migrations, check
the migrations directory of kentcdodds.com.
Zero Downtime Migrations
At scale, dealing with migrations can be a little tricky. With lots of data and
big changes, migrations can take a lot of time. We don't want to pull down our
website while we're deploying a migration. But we also don't want to deploy a
version of our app that doesn't work with the data in the database.
This is where the practice of "widen then narrow" comes in. The idea is that you
make your database schema more permissive, deploy your app, then make your
database schema more restrictive. For more on this subject, check out
the Epic Stack database migrations docs.
When to use Migrate vs Push
The short answer is you use
push
when you're experimenting with schema changes
and migrate
when you're ready to commit to a schema change.The longer answer is:
Read the Prisma docs about it π