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.

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.