Using Prisma Migrate

Creating the database schema

In this guide, you'll use Prisma Migrate to create the tables in your database. Add the following data model to your Prisma schema in prisma/schema.prisma:

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

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

To map your data model to the database schema, you need to use the prisma migrate CLI commands:

npx prisma migrate dev --name init

This command does two things:

1. It creates a new SQL migration file for this migration
2. It runs the SQL migration file against the database

Note: generate is called under the hood by default, after running prisma migrate dev. If the prisma-client-js generator is defined in your schema, this will check if @prisma/client is installed and install it if it's missing.

Great, you now created three tables in your database with Prisma Migrate 🚀

In this guide, you'll use Prisma Migrate to create the tables in your database. Add the following Prisma data model to your Prisma schema in prisma/schema.prisma:

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

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

To map your data model to the database schema, you need to use the prisma migrate CLI commands:

npx prisma migrate dev --name init

This command does two things:

1. It creates a new SQL migration file for this migration
2. It runs the SQL migration file against the database

Note: generate is called under the hood by default, after running prisma migrate dev. If the prisma-client-js generator is defined in your schema, this will check if @prisma/client is installed and install it if it's missing.

Great, you now created three tables in your database with Prisma Migrate 🚀

In this guide, you'll use Prisma's db push command to create the tables in your database. Add the following Prisma data model to your Prisma schema in prisma/schema.prisma:

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int

  @@index(authorId)
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique

  @@index(userId)
}

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

You are now ready to push your new schema to your database. Connect to your main branch using the instructions in Connect your database.

Now use the db push CLI command to push to the main branch:

npx prisma db push

Great, you now created three tables in your database with Prisma's db push command 🚀

In this guide, you'll use Prisma Migrate to create the tables in your database. Add the following Prisma data model to your Prisma schema in prisma/schema.prisma:

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

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

To map your data model to the database schema, you need to use the prisma migrate CLI commands:

npx prisma migrate dev --name init

This command does two things:

1. It creates a new SQL migration file for this migration
2. It runs the SQL migration file against the database

Note: generate is called under the hood by default, after running prisma migrate dev. If the prisma-client-js generator is defined in your schema, this will check if @prisma/client is installed and install it if it's missing.

Great, you now created three tables in your database with Prisma Migrate 🚀

SQL

CREATE TABLE "Post" (
  "id" SERIAL,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL,
  "title" VARCHAR(255) NOT NULL,
  "content" TEXT,
  "published" BOOLEAN NOT NULL DEFAULT false,
  "authorId" INTEGER NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "Profile" (
  "id" SERIAL,
  "bio" TEXT,
  "userId" INTEGER NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "User" (
  "id" SERIAL,
  "email" TEXT NOT NULL,
  "name" TEXT,
  PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "Profile.userId_unique" ON "Profile"("userId");
CREATE UNIQUE INDEX "User.email_unique" ON "User"("email");
ALTER TABLE "Post" ADD FOREIGN KEY("authorId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "Profile" ADD FOREIGN KEY("userId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Tables

User

Column name	Type	Primary key	Foreign key	Required	Default
id	INTEGER	✔️	No	✔️	autoincrementing
name	TEXT	No	No	No	-
email	TEXT	No	No	✔️	-

Post

Column name	Type	Primary key	Foreign key	Required	Default
id	INTEGER	✔️	No	✔️	autoincrementing
createdAt	TIMESTAMP	No	No	✔️	now()
updatedAt	TIMESTAMP	No	No	✔️
title	VARCHAR(255)	No	No	✔️	-
content	TEXT	No	No	No	-
published	BOOLEAN	No	No	✔️	false
authorId	INTEGER	No	✔️	✔️	-

Profile

Column name	Type	Primary key	Foreign key	Required	Default
id	INTEGER	✔️	No	✔️	autoincrementing
bio	TEXT	No	No	No	-
userId	INTEGER	No	✔️	✔️	-

SQL

CREATE TABLE "Post" (
  "id" SERIAL,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL,
  "title" TEXT NOT NULL,
  "content" TEXT,
  "published" BOOLEAN NOT NULL DEFAULT false,
  "authorId" INTEGER NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "Profile" (
  "id" SERIAL,
  "bio" TEXT,
  "userId" INTEGER NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "User" (
  "id" SERIAL,
  "email" TEXT NOT NULL,
  "name" TEXT,
  PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "Profile.userId_unique" ON "Profile"("userId");
CREATE UNIQUE INDEX "User.email_unique" ON "User"("email");
ALTER TABLE "Post" ADD FOREIGN KEY("authorId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "Profile" ADD FOREIGN KEY("userId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Tables

User

Column name	Type	Primary key	Foreign key	Required	Default
id	INTEGER	✔️	No	✔️	autoincrementing
name	TEXT	No	No	No	-
email	TEXT	No	No	✔️	-

Post

Column name	Type	Primary key	Foreign key	Required	Default
id	INTEGER	✔️	No	✔️	autoincrementing
createdAt	TIMESTAMP	No	No	✔️	now()
updatedAt	TIMESTAMP	No	No	✔️
title	VARCHAR(255)	No	No	✔️	-
content	TEXT	No	No	No	-
published	BOOLEAN	No	No	✔️	false
authorId	INTEGER	No	✔️	✔️	-

Profile

Column name	Type	Primary key	Foreign key	Required	Default
id	INTEGER	✔️	No	✔️	autoincrementing
bio	TEXT	No	No	No	-
userId	INTEGER	No	✔️	✔️	-

SQL

CREATE TABLE `Post` (
  `id` int NOT NULL AUTO_INCREMENT,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '0',
  `authorId` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Post_authorId_idx` (`authorId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `Profile` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bio` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `userId` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Profile_userId_key` (`userId`),
  KEY `Profile_userId_idx` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `User` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `User_email_key` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Tables

User

Column name	Type	Primary key	Foreign key	Required	Default
id	int	✔️	No	✔️	autoincrementing
name	varchar(191)	No	No	No	-
email	varchar(191)	No	No	✔️	-

Post

Column name	Type	Primary key	Foreign key	Required	Default
id	int	✔️	No	✔️	autoincrementing
createdAt	datetime(3)	No	No	✔️	now()
updatedAt	datetime(3)	No	No	✔️
title	varchar(255)	No	No	✔️	-
content	varchar(191)	No	No	No	-
published	tinyint(1)	No	No	✔️	false
authorId	int	No	No	✔️	-

Profile

Column name	Type	Primary key	Foreign key	Required	Default
id	int	✔️	No	✔️	autoincrementing
bio	varchar(191)	No	No	No	-
userId	int	No	No	✔️	-

SQL

BEGIN TRY

BEGIN TRAN;

-- CreateTable
CREATE TABLE [dbo].[Post] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [Post_createdAt_df] DEFAULT CURRENT_TIMESTAMP,
    [updatedAt] DATETIME2 NOT NULL,
    [title] VARCHAR(255) NOT NULL,
    [content] NVARCHAR(1000),
    [published] BIT NOT NULL CONSTRAINT [Post_published_df] DEFAULT 0,
    [authorId] INT NOT NULL,
    CONSTRAINT [Post_pkey] PRIMARY KEY ([id])
);

-- CreateTable
CREATE TABLE [dbo].[Profile] (
    [id] INT NOT NULL IDENTITY(1,1),
    [bio] NVARCHAR(1000),
    [userId] INT NOT NULL,
    CONSTRAINT [Profile_pkey] PRIMARY KEY ([id]),
    CONSTRAINT [Profile_userId_key] UNIQUE ([userId])
);

-- CreateTable
CREATE TABLE [dbo].[User] (
    [id] INT NOT NULL IDENTITY(1,1),
    [email] NVARCHAR(1000) NOT NULL,
    [name] NVARCHAR(1000),
    CONSTRAINT [User_pkey] PRIMARY KEY ([id]),
    CONSTRAINT [User_email_key] UNIQUE ([email])
);

-- AddForeignKey
ALTER TABLE [dbo].[Post] ADD CONSTRAINT [Post_authorId_fkey] FOREIGN KEY ([authorId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Profile] ADD CONSTRAINT [Profile_userId_fkey] FOREIGN KEY ([userId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

COMMIT TRAN;

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK TRAN;
END;
THROW

END CATCH

Tables

User

Column name	Type	Primary key	Foreign key	Required	Default
id	INT	✔️	No	✔️	autoincrementing
name	NVARCHAR(1000)	No	No	No	-
email	NVARCHAR(1000)	No	No	✔️	-

Post

Column name	Type	Primary key	Foreign key	Required	Default
id	INT	✔️	No	✔️	autoincrementing
createdAt	DATETIME2	No	No	✔️	now()
updatedAt	DATETIME2	No	No	✔️
title	VARCHAR(255)	No	No	✔️	-
content	NVARCHAR(1000)	No	No	No	-
published	BIT	No	No	✔️	false
authorId	INT	No	✔️	✔️	-

Profile

Column name	Type	Primary key	Foreign key	Required	Default
id	INT	✔️	No	✔️	autoincrementing
bio	NVARCHAR(1000)	No	No	No	-
userId	INT	No	✔️	✔️	-

In this guide, you'll use Prisma Migrate to create the tables in your database. Add the following Prisma data model to your Prisma schema in prisma/schema.prisma:

model Post {
  id        BigInt   @id @default(sequence())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  BigInt
}

model Profile {
  id     BigInt  @id @default(sequence())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId BigInt  @unique
}

model User {
  id      BigInt   @id @default(sequence())
  email   String   @unique
  name    String?
  posts   Post[]
  profile Profile?
}

To map your data model to the database schema, you need to use the prisma migrate CLI commands:

npx prisma migrate dev --name init

This command does two things:

1. It creates a new SQL migration file for this migration
2. It runs the SQL migration file against the database

Note: generate is called under the hood by default, after running prisma migrate dev. If the prisma-client-js generator is defined in your schema, this will check if @prisma/client is installed and install it if it's missing.

Great, you now created three tables in your database with Prisma Migrate 🚀

Connect your database

Install Prisma Client

Connect your database

Install Prisma Client

Connect your database

Install Prisma Client

Connect your database

Install Prisma Client

Connect your database

Install Prisma Client

Connect your database

Install Prisma Client

Connect your database

Install Prisma Client

Connect your database

Install Prisma Client

Connect your database

Install Prisma Client

Connect your database

Install Prisma Client