Database Migrations

Manage database schema changes with Prisma Migrate for safe deployments.

Migrations track and apply schema changes to your database in a controlled, version-controlled manner. We use Prisma Migrate to generate migration files as a result of schema changes (changing a property, adding a table, etc.).

The Prisma team provides excellent documentation on managing migrations which you should read up on if you want to know more.

Practical Example: adding a new table

Let's say we want to add a new table to the database called projects. We can do this by adding the following to the packages/database/src/prisma/schema.prisma file:

packages/database/src/prisma/schema.prisma

model Project {
  id             String       @id @default(uuid())
  name           String
  organizationId String       @map("organization_id")
  createdAt      DateTime     @default(now()) @map("created_at")
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  @@map("projects")
}

Don't forget to add the reverse relation to the Organization model:

model Organization {
  // ... existing fields
  projects Project[]
}

Now, we can generate a migration file by running the following command:

pnpm --filter "@kit/database" prisma:generate

Then create the migration:

pnpm --filter "@kit/database" prisma:migrate

Prisma will prompt you for a migration name. Enter something like add_projects_table. This creates a migration file in the packages/database/src/prisma/migrations directory.

If you open the SQL migration file, you will see something like:

-- migrations/20241226_add_projects_table/migration.sql
CREATE TABLE "projects" (
  "id" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  "organization_id" TEXT NOT NULL,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "projects_pkey" PRIMARY KEY ("id")
);
ALTER TABLE "projects" ADD CONSTRAINT "projects_organization_id_fkey"
  FOREIGN KEY ("organization_id") REFERENCES "organization"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

The migration is automatically applied when running pnpm --filter "@kit/database" prisma:migrate. We can now use the project model in our codebase:

import { db } from '@kit/database';
// Create a project
const project = await db.project.create({
  data: {
    name: 'My Project',
    organizationId: currentOrgId,
  },
});
// Query projects
const projects = await db.project.findMany({
  where: { organizationId: currentOrgId },
});

In the next section, we'll learn how to use the Prisma client to interact with the database.

Next: Client →