Prisma Migrate ↗ is a versatile tool for working with database migrations. One of the cool things Prisma Migrate allows you to do is build your own migration tooling using Prisma Migrate.

This article will explore how you could use the prisma migrate diff ↗ and prisma db execute ↗ commands to:

• Prototype your schema similar to prisma db push
• Generate and apply schema migrations similar to prisma migrate dev

Fun fact: prisma migrate diff and prisma db execute are the building blocks of prisma db push and prisma migrate dev

To successfully follow along this article, it would be helpful if you have some:

• Familiarity working with relational databases
• Familiarity working with Prisma (optional)

What is a schema migration?

A schema migration is a “snapshot” that defines the changes that were made to a database schema at a particular point in time.

Schema migrations are also used to manage the state of your database schema. A migration can deterministically reproduce the final database schema at a specific point in time. This would be essential in a team setting, for example, where you would switch between different branches which might have different migration histories.

What is Prisma Migrate

Prisma Migrate is an imperative database schema migration tool that keeps your database in sync with your Prisma schema ↗.

The Prisma schema is the main configuration file that defines the structure of your database schema.

Database state management with schema migrations

When working with migration tools, such as Prisma Migrate, it is essential to define a source of truth that defines what the final state of the database should be. As for Prisma, the source of truth of the database schema structure is the Prisma schema.

When you update your Prisma schema by adding/ deleting/ updating a field , naturally, your database schema falls behind. In order to sync your database schema with the Prisma schema, you would either run prisma db push or prisma migrate dev. Part of this process of bringing your database in sync with your Prisma schema involves comparing the two schema.

Detecting schema changes in your database

So, how do you compare your Prisma schema and database schema?

Prisma Migrate provides the prisma migrate diff command that allows you to compare schemas or migration histories. A schema in this example could either be a database schema, Prisma schema, or even migration histories — the prisma/migrations directory.

prisma migrate diff diffs or compares two schemas; an initial and a final/ anticipated version. The initial version describes the current state the database schema is in. The final version describes the state you would like the database schema to be.

Therefore, to bring your database to be in sync with your Prisma schema, your database would be the initial state and the Prisma schema the final state. The diff (SQL) between the initial state and final state can then be persisted in a file, becoming the migration file.

The workflow to generate and execute a migration would look like this:

Let’s now get our hands dirty and creating a mini-version of prisma db push and prisma migrate dev.

Generating migrations with prisma migrate diff

mkdir schema-migrations
cd schema-migrations
npm init --yes

npm install -D prisma

npx prisma init --datasource-provider sqlite

touch prisma/dev.db

The following Prisma schema defines two models: User and Post with a one-to-many relation between them:

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User?    @relation(fields: [authorId], references: [id])
  authorId  Int?
}

You can replicate what prisma migrate dev does using prisma db diff by:

1. Comparing from your database schema with your Prisma schema
2. Persisting the output of the command to a file in your migration history
3. (Optional) Reviewing the generated migration
4. Executing the generated migration with prisma db execute

Therefore, to generate a migration, run:

# Create the migrations directory
mkdir -p prisma/migrations/001_init

# diff your schemas and persist it to the folder you created
npx prisma migrate diff \
--from-schema-datasource ./prisma/schema.prisma \
--to-schema-datamodel ./prisma/schema.prisma \
--script > prisma/migrations/001_init/migration.sql

If everything run smoothly 001_init/migration.sql the above command will generate a SQL migration that resembles the folliwng SQL:

-- CreateTable
CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL,
    "name" TEXT
);

-- CreateTable
CREATE TABLE "Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "title" TEXT NOT NULL,
    "content" TEXT,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

To apply the migration, you can use the prisma db execute command:

# execute the migration
npx prisma db execute \
--schema ./prisma/schema.prisma \
--file prisma/migrations/001_init/migration.sql

And finally mark the migration as applied using prisma migrate resolve:

npx prisma migrate resolve --applied 001_init

You could run all 4 steps at once in one step:

# Create the migrations directory
mkdir -p prisma/migrations/001_init

# Diff your schemas and persist it to the folder you created
npx prisma migrate diff \
--from-schema-datasource ./prisma/schema.prisma \
--to-schema-datamodel ./prisma/schema.prisma \
--script > prisma/migrations/001_init/migration.sql

# Apply the migration
npx prisma db execute \
--schema ./prisma/schema.prisma \
--file prisma/migrations/001_init/migration.sql

# Mark the migration as applied
npx prisma migrate resolve --applied 001_init

Schema prototyping

The steps for schema prototyping are similar to the previous step. The difference is that the SQL migration will not be persisted to a file.

The steps for emulating prisma db push using prisma migrate diff and `prisma db push are:

1. Diffing your database schema to your Prisma schema
2. “Piping” the output from the first command as input for prisma db execute

The command for this would be:

 npx prisma migrate diff \
--from-schema-datasource ./prisma/schema.prisma \
--to-schema-datamodel ./prisma/schema.prisma \
--script | npx prisma db execute --stdin \
--schema ./prisma/schema.prisma

Conclusion

While prisma migrate diff allows you extend Prisma Migrate and do cool things like building your own migration tooling, I would not recommend rolling out your own migration tooling unless you’re certain you want to do that unless you REALLY need to.

If you would like to learn more about migrate diff or have a better mental model for Prisma Migrate, I would recommend reading:

• Improving Prisma Migrate DX with two new commands ↗
• Prisma Migrate mental model ↗

If you would be interested in learning how to build safe migration tooling, send me a messsage ↗ and let me know what features you would be interested in.