Back
Blog Post|#engineering

Prisma Two Ways

Alex AndersonWednesday, July 3, 2024
Prisma Two Ways

Prisma client makes it incredibly easy to manage and query relational databases. I love how easy Prisma’s schema definition language makes modeling complicated relationships.

Way 1: Schema → Migration → Tables

For example, in this many-to-many relationship with multiple relationships, it’s pretty clear to see what’s going on, what’s required and what isn’t, and where the relationships all point:

model User { id String @id @default(cuid()) createdAt DateTime @default(now()) email String OrgUser OrgUser[] InvitedOrgUser OrgUser[] @relation("InvitedBy") } model Org { id String @id @default(cuid()) createdAt DateTime @default(now()) name String slug String @unique OrgUser OrgUser[] } // This is where org-specific user settings are stored model OrgUser { id String @id @default(cuid()) createdAt DateTime @default(now()) org Org @relation(fields: [orgId], references: [id]) user User? @relation(fields: [userId], references: [id]) invitedBy User? @relation("InvitedBy", fields: [invitedById], references: [id]) name String? role String orgId String userId String? invitedById String? }

And here’s that modeled as an ERD, courtesy Prisma Editor.

An ERD of the schema above

After writing this schema, you can run prisma migrate dev and it will automatically create a migration with all the changes that you have made to your schema since the last migration, and apply it to your database. Handy!

But for some, you might want to start with a SQL Migration and generate your tables from that. Prisma’s got a solution for that too!

Way 2: Migration → Tables → Schema

First, create a new migration in the Prisma migrations folder. Note that migrations are applied in alphanumerical order, so it’s a good idea to name your migration based on the current timestamp, like Prisma does:

// /prisma/migrations/20240607153242_init/migration.sql CREATE TABLE "User" ( "id" TEXT NOT NULL PRIMARY KEY, "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "email" TEXT NOT NULL ); CREATE TABLE "Org" ( "id" TEXT NOT NULL PRIMARY KEY, "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "name" TEXT NOT NULL, "slug" TEXT NOT NULL ); CREATE TABLE "OrgUser" ( "id" TEXT NOT NULL PRIMARY KEY, "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "name" TEXT, "role" TEXT NOT NULL, "orgId" TEXT NOT NULL, "userId" TEXT, "invitedById" TEXT, CONSTRAINT "OrgUser_orgId_fkey" FOREIGN KEY ("orgId") REFERENCES "Org" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT "OrgUser_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT "OrgUser_invitedById_fkey" FOREIGN KEY ("invitedById") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE ); CREATE UNIQUE INDEX "Org_slug_key" ON "Org"("slug");

Once you’ve done that, you can apply the migration to your database by running prisma migrate deploy.

Now that our tables are updated, we need to get that schema back down to our app so Prisma can generate the type safe client for our app. Fortunately, doing that is just a single command: prisma db pull

After running that command, the current structure of the database is put into your Prisma schema, nice and neat.

Mix and Match

And there’s nothing stopping you from doing both ways in the same project. Some folks might prefer to write the schema first. Others might prefer writing raw SQL migrations. If you’re careful, have good version control, and aren’t doing too many manual things in your Prisma schema, overwriting the schema with prisma db pull shouldn’t be too big of a deal. If it is, you can still run it, and then use source control to make sure you’re only changing the stuff that you want to.

Way 3: Schema → Tables

There’s another, forbidden way of using Prisma. I’m referring to prisma db push, which takes the current contents of your schema and puts it directly on your database.

Yes, it gets the job done, but it’s heavy-handed. It doesn’t let you track changes or make subtle adjustments to tables before you alter them, like modifying column contents or moving data between columns.

While it may sound tempting - why complicate things with an extra migrations step? - prisma db push runs the risk of accidentally overwriting or removing data. The Prisma docs have a lot more information about when and why you might want to use prisma db push.


Personally, I prefer the balance of consistency, ease, and explicitness that comes from generating migrations from the schema. But maybe you really like writing SQL by hand. In either case, Prisma has options.

Share this post

twitterfacebooklinkedin

Related Posts:

Interested in working with us?

Give us some details about your project, and our team will be in touch with how we can help.

Get in Touch