Baseline your database

Create an initial migration

To use Prisma Migrate with the database you introspected in the last section, you will need to baseline your database.

Baselining refers to initializing your migration history for a database that might already contain data and cannot be reset, such as your production database. Baselining tells Prisma Migrate to assume that one or more migrations have already been applied to your database.

To baseline your database, use prisma migrate diff to compare your schema and database, and save the output into a SQL file.

First, create a migrations directory and add a directory inside with your preferred name for the migration. In this example, we will use 0_init as the migration name:


$mkdir -p prisma/migrations/0_init

-p will recursively create any missing folders in the path you provide.

Next, generate the migration file with prisma migrate diff. Use the following arguments:

--from-empty: assumes the data model you're migrating from is empty
--to-schema-datamodel: the current database state using the URL in the datasource block
--script: output a SQL script


$npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/0_init/migration.sql

Review the migration

The command will generate a migration that should resemble the following script:

prisma/migrations/0_init/migration.sql
1-- CreateTable
2CREATE TABLE "Post" (
3    "id" SERIAL NOT NULL,
4    "title" VARCHAR(255) NOT NULL,
5    "createdAt" TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
6    "content" TEXT,
7    "published" BOOLEAN NOT NULL DEFAULT false,
8    "authorId" INTEGER NOT NULL,
9
10    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
11);
12
13-- CreateTable
14CREATE TABLE "Profile" (
15    "id" SERIAL NOT NULL,
16    "bio" TEXT,
17    "userId" INTEGER NOT NULL,
18
19    CONSTRAINT "Profile_pkey" PRIMARY KEY ("id")
20);
21
22-- CreateTable
23CREATE TABLE "User" (
24    "id" SERIAL NOT NULL,
25    "name" VARCHAR(255),
26    "email" VARCHAR(255) NOT NULL,
27
28    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
29);
30
31-- CreateIndex
32CREATE UNIQUE INDEX "Profile_userId_key" ON "Profile"("userId");
33
34-- CreateIndex
35CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
36
37-- AddForeignKey
38ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
39
40-- AddForeignKey
41ALTER TABLE "Profile" ADD CONSTRAINT "Profile_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

prisma/migrations/0_init/migration.sql
1-- CreateTable
2CREATE TABLE `Post` (
3    `id` INTEGER NOT NULL AUTO_INCREMENT,
4    `title` VARCHAR(255) NOT NULL,
5    `createdAt` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
6    `content` TEXT NULL,
7    `published` BOOLEAN NOT NULL DEFAULT false,
8    `authorId` INTEGER NOT NULL,
9
10    INDEX `authorId`(`authorId`),
11    PRIMARY KEY (`id`)
12) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
13
14-- CreateTable
15CREATE TABLE `Profile` (
16    `id` INTEGER NOT NULL AUTO_INCREMENT,
17    `bio` TEXT NULL,
18    `userId` INTEGER NOT NULL,
19
20    UNIQUE INDEX `userId`(`userId`),
21    PRIMARY KEY (`id`)
22) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
23
24-- CreateTable
25CREATE TABLE `User` (
26    `id` INTEGER NOT NULL AUTO_INCREMENT,
27    `name` VARCHAR(255) NULL,
28    `email` VARCHAR(255) NOT NULL,
29
30    UNIQUE INDEX `email`(`email`),
31    PRIMARY KEY (`id`)
32) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
33
34-- AddForeignKey
35ALTER TABLE `Post` ADD CONSTRAINT `Post_ibfk_1` FOREIGN KEY (`authorId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
36
37-- AddForeignKey
38ALTER TABLE `Profile` ADD CONSTRAINT `Profile_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

prisma/migrations/0_init/migration.sql
1CREATE TABLE [dbo].[Post] (
2    [id] INT NOT NULL IDENTITY(1,1),
3    [createdAt] DATETIME2 NOT NULL CONSTRAINT [Post_createdAt_df] DEFAULT CURRENT_TIMESTAMP,
4    [updatedAt] DATETIME2 NOT NULL,
5    [title] VARCHAR(255) NOT NULL,
6    [content] NVARCHAR(1000),
7    [published] BIT NOT NULL CONSTRAINT [Post_published_df] DEFAULT 0,
8    [authorId] INT NOT NULL,
9    CONSTRAINT [Post_pkey] PRIMARY KEY ([id])
10);
11
12CREATE TABLE [dbo].[Profile] (
13    [id] INT NOT NULL IDENTITY(1,1),
14    [bio] NVARCHAR(1000),
15    [userId] INT NOT NULL,
16    CONSTRAINT [Profile_pkey] PRIMARY KEY ([id]),
17    CONSTRAINT [Profile_userId_key] UNIQUE ([userId])
18);
19
20CREATE TABLE [dbo].[User] (
21    [id] INT NOT NULL IDENTITY(1,1),
22    [email] NVARCHAR(1000) NOT NULL,
23    [name] NVARCHAR(1000),
24    CONSTRAINT [User_pkey] PRIMARY KEY ([id]),
25    CONSTRAINT [User_email_key] UNIQUE ([email])
26);
27
28ALTER TABLE [dbo].[Post] ADD CONSTRAINT [Post_authorId_fkey] FOREIGN KEY ([authorId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;
29
30ALTER TABLE [dbo].[Profile] ADD CONSTRAINT [Profile_userId_fkey] FOREIGN KEY ([userId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

prisma/migrations/0_init/migration.sql
1CREATE TABLE "User" (
2  id INT8 PRIMARY KEY DEFAULT unique_rowid(),
3  name STRING(255),
4  email STRING(255) UNIQUE NOT NULL
5);
6
7CREATE TABLE "Post" (
8  id INT8 PRIMARY KEY DEFAULT unique_rowid(),
9  title STRING(255) UNIQUE NOT NULL,
10  "createdAt" TIMESTAMP NOT NULL DEFAULT now(),
11  content STRING,
12  published BOOLEAN NOT NULL DEFAULT false,
13  "authorId" INT8 NOT NULL,
14  FOREIGN KEY ("authorId") REFERENCES "User"(id)
15);
16
17CREATE TABLE "Profile" (
18  id INT8 PRIMARY KEY DEFAULT unique_rowid(),
19  bio STRING,
20  "userId" INT8 UNIQUE NOT NULL,
21  FOREIGN KEY ("userId") REFERENCES "User"(id)
22);

Review the SQL migration file to ensure everything is correct.

Next, mark the migration as applied using prisma migrate resolve with the --applied argument.


$npx prisma migrate resolve --applied 0_init

The command will mark 0_init as applied by adding it to the _prisma_migrations table.

You now have a baseline for your current database schema. To make further changes to your database schema, you can update your Prisma schema and use prisma migrate dev to apply the changes to your database.

Introspection Install Prisma Client

Was this helpful?

Edit this page on GitHub

Prisma ORM

Prisma Data Platform

Create an initial migration

Review the migration

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter

Prisma ORM

Prisma Data Platform

Latest from the blog

Baseline your database

Create an initial migration

Review the migration

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter