Prisma Migrate

SQL

In SQL, you'd have to send three subsequent SQL statements to account for this scenario:

1. Create the User table to store user information (name, email, ...)

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

2. Create two new tables Post and Profile with foreign keys to User

CREATE TABLE "Profile" (
    id SERIAL PRIMARY KEY,
    bio TEXT NOT NULL,
    "user" integer NOT NULL UNIQUE,
    FOREIGN KEY ("user") REFERENCES "User"(id)
);
CREATE TABLE "Post" (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author integer NOT NULL,
    FOREIGN KEY (author) REFERENCES "User"(id)
);

3. Add a new column with a default value to the Post table

ALTER TABLE "Post"
ADD COLUMN published BOOLEAN DEFAULT false;

Prisma Migrate

With Prisma Migrate, you write the desired database schema in the form of a Prisma data model inside your Prisma schema file. To map the data model to your database schema, you then have to run these two commands:

prisma migrate save --experimental
prisma migrate up --experimental

The first command saves a new migration to the prisma/migrations directory in the file system of your project and updates the _Migration table in your database. Each time you run this command to save a new migration, it creates a dedicated directory inside of prisma/migrations for that specific migration, which will have its own README.md file containing detailed information about the migration (e.g. the generated SQL statements which will be executed when you run prisma migrate up).

The second command executes the migration against your database.

1. Create the User table to store user information (name, email, ...)

Add the model to your Prisma schema:

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

Now run the two commands mentioned above:

prisma migrate save --experimental
prisma migrate up --experimental

2. Create two new tables Post and Profile with foreign keys to User

Add two models with relation fields to your Prisma schema:

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

Notice that in addition to the annotated relation fields and its relation scalar field (which represent the foreign keys), you must also specify the Prisma-level relation fields on the other side of the relation.

Now run the two commands mentioned above:

prisma migrate save --experimental
prisma migrate up --experimental

3. Add a new column with default value to the Post table

Add a field to the Post model:

model User {
  id       Int       @id @default(autoincrement())
  name     String?
  email    String    @unique
  posts    Post[]
  profile  Profile?
}
model Profile {
  id   Int    @id @default(autoincrement())
  bio  String
  user User
}
model Post {
  id         Int      @id @default(autoincrement())
  title      String
  published  Boolean  @default(false)
  author     User
}

Now run the two commands mentioned above:

prisma migrate save --experimental
prisma migrate up --experimental

The migrations directory

The migrations directory stores information about the migrations that have been or will be executed against your database. You should never make any manual changes to the files in migrations. The only way to change the content of this directory should be using the prisma migrate save command.

The migrations directory should be checked into version control (e.g. Git).

The _Migration table

The _Migration table additionally stores information about each migration that was ever executed against the database by Prisma Migrate.

Delete the migrations directory

rm -rf migrations

Delete all entries from the _Migration table

TRUNCATE _Migration;