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 thedatasource
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-- CreateTable2CREATE 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,910 CONSTRAINT "Post_pkey" PRIMARY KEY ("id")11);1213-- CreateTable14CREATE TABLE "Profile" (15 "id" SERIAL NOT NULL,16 "bio" TEXT,17 "userId" INTEGER NOT NULL,1819 CONSTRAINT "Profile_pkey" PRIMARY KEY ("id")20);2122-- CreateTable23CREATE TABLE "User" (24 "id" SERIAL NOT NULL,25 "name" VARCHAR(255),26 "email" VARCHAR(255) NOT NULL,2728 CONSTRAINT "User_pkey" PRIMARY KEY ("id")29);3031-- CreateIndex32CREATE UNIQUE INDEX "Profile_userId_key" ON "Profile"("userId");3334-- CreateIndex35CREATE UNIQUE INDEX "User_email_key" ON "User"("email");3637-- AddForeignKey38ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;3940-- AddForeignKey41ALTER 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-- CreateTable2CREATE 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,910 INDEX `authorId`(`authorId`),11 PRIMARY KEY (`id`)12) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;1314-- CreateTable15CREATE TABLE `Profile` (16 `id` INTEGER NOT NULL AUTO_INCREMENT,17 `bio` TEXT NULL,18 `userId` INTEGER NOT NULL,1920 UNIQUE INDEX `userId`(`userId`),21 PRIMARY KEY (`id`)22) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;2324-- CreateTable25CREATE TABLE `User` (26 `id` INTEGER NOT NULL AUTO_INCREMENT,27 `name` VARCHAR(255) NULL,28 `email` VARCHAR(255) NOT NULL,2930 UNIQUE INDEX `email`(`email`),31 PRIMARY KEY (`id`)32) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;3334-- AddForeignKey35ALTER TABLE `Post` ADD CONSTRAINT `Post_ibfk_1` FOREIGN KEY (`authorId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;3637-- AddForeignKey38ALTER 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);1112CREATE 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);1920CREATE 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);2728ALTER TABLE [dbo].[Post] ADD CONSTRAINT [Post_authorId_fkey] FOREIGN KEY ([authorId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;2930ALTER 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 NULL5);67CREATE 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);1617CREATE 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.