Docs
/ Get started / Set up Prisma / Start from scratch / Relational databases

Using Prisma Migrate

JavaScript
MySQL

Creating the database schema

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:

prisma/schema.prisma
1model Post {
2  id        Int      @id @default(autoincrement())
3  createdAt DateTime @default(now())
4  updatedAt DateTime @updatedAt
5  title     String   @db.VarChar(255)
6  content   String?
7  published Boolean  @default(false)
8  author    User     @relation(fields: [authorId], references: [id])
9  authorId  Int
10}
11

12model Profile {
13  id     Int     @id @default(autoincrement())
14  bio    String?
15  user   User    @relation(fields: [userId], references: [id])
16  userId Int     @unique
17}
18

19model User {
20  id      Int      @id @default(autoincrement())
21  email   String   @unique
22  name    String?
23  posts   Post[]
24  profile Profile?
25}

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:

prisma/schema.prisma
1model Post {
2  id        Int      @id @default(autoincrement())
3  createdAt DateTime @default(now())
4  updatedAt DateTime @updatedAt
5  title     String   @db.VarChar(255)
6  content   String?
7  published Boolean  @default(false)
8  author    User     @relation(fields: [authorId], references: [id])
9  authorId  Int
10}
11

12model Profile {
13  id     Int     @id @default(autoincrement())
14  bio    String?
15  user   User    @relation(fields: [userId], references: [id])
16  userId Int     @unique
17}
18

19model User {
20  id      Int      @id @default(autoincrement())
21  email   String   @unique
22  name    String?
23  posts   Post[]
24  profile Profile?
25}

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:

prisma/schema.prisma
1model Post {
2  id        Int      @id @default(autoincrement())
3  createdAt DateTime @default(now())
4  updatedAt DateTime @updatedAt
5  title     String   @db.VarChar(255)
6  content   String?
7  published Boolean  @default(false)
8  author    User     @relation(fields: [authorId], references: [id])
9  authorId  Int
10

11  @@index(authorId)
12}
13

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

20  @@index(userId)
21}
22

23model User {
24  id      Int      @id @default(autoincrement())
25  email   String   @unique
26  name    String?
27  posts   Post[]
28  profile Profile?
29}

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:

prisma/schema.prisma
1model Post {
2  id        Int      @id @default(autoincrement())
3  createdAt DateTime @default(now())
4  updatedAt DateTime @updatedAt
5  title     String   @db.VarChar(255)
6  content   String?
7  published Boolean  @default(false)
8  author    User     @relation(fields: [authorId], references: [id])
9  authorId  Int
10}
11

12model Profile {
13  id     Int     @id @default(autoincrement())
14  bio    String?
15  user   User    @relation(fields: [userId], references: [id])
16  userId Int     @unique
17}
18

19model User {
20  id      Int      @id @default(autoincrement())
21  email   String   @unique
22  name    String?
23  posts   Post[]
24  profile Profile?
25}

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
Tables
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;
SQL
Tables
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;
SQL
Tables
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;
SQL
Tables
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

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:

prisma/schema.prisma
1model Post {
2  id        BigInt   @id @default(sequence())
3  createdAt DateTime @default(now())
4  updatedAt DateTime @updatedAt
5  title     String   @db.VarChar(255)
6  content   String?
7  published Boolean  @default(false)
8  author    User     @relation(fields: [authorId], references: [id])
9  authorId  BigInt
10}
11

12model Profile {
13  id     BigInt  @id @default(sequence())
14  bio    String?
15  user   User    @relation(fields: [userId], references: [id])
16  userId BigInt  @unique
17}
18

19model User {
20  id      BigInt   @id @default(sequence())
21  email   String   @unique
22  name    String?
23  posts   Post[]
24  profile Profile?
25}

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 databaseInstall Prisma Client
Connect your databaseInstall Prisma Client
Connect your databaseInstall Prisma Client
Connect your databaseInstall Prisma Client
Connect your databaseInstall Prisma Client
Connect your databaseInstall Prisma Client
Connect your databaseInstall Prisma Client
Connect your databaseInstall Prisma Client
Connect your databaseInstall Prisma Client
Connect your databaseInstall Prisma Client
Edit this page on GitHub

Product

ClientMigrate
Accelerate
PulseEarly Access
Pricing

Developers

DocsGet StartedPrisma ExamplesData GuidePrisma in your StackSupportCommunityData Platform StatusVS Code Extension

Use Cases

Customer StoriesEnterprise

Company

AboutBlogCareers EventsCausesTerms & Privacy

Newsletter

prisma_logo
© 2023 Prisma Data, Inc.