Schema prototyping with db push

This guide demonstrates how to use db push to prototype a new schema or a new feature for an existing schema.

Although db push and Prisma Migrate both synchronize your Prisma schema and database schema, they play different roles in the development workflow and should not be used interchangeably.

Prototyping a new schema

The following scenario demonstrates how to use db push to synchronize a new schema with an empty database, and evolve that schema - including what happens when db push detects that a change will result in data loss.

  1. Create a first draft of your schema:

    generator client {
    provider = "prisma-client-js"
    }
    datasource db {
    provider = "postgresql"
    url = env("DATABASE_URL")
    }
    model User {
    id Int @id @default(autoincrement())
    name String
    jobTitle String
    posts Post[]
    profile Profile?
    }
    model Profile {
    id Int @id @default(autoincrement())
    biograpy String // Intentional typo!
    userId Int
    user User @relation(fields: userId, references: id)
    }
    model Post {
    id Int @id @default(autoincrement())
    title String
    published Boolean @default(true)
    content String @db.VarChar(500)
    authorId Int
    author User @relation(fields: [authorId], references: [id])
    categories Category[]
    }
    model Category {
    id Int @id @default(autoincrement())
    name String @db.VarChar(50)
    posts Post[]
    @@unique([name])
    }
  2. Use db push to push the initial schema to the database:

    $npx prisma db push
  3. Create some example content:

    const add = await prisma.user.create({
    data: {
    name: 'Eloise',
    jobTitle: 'Programmer',
    posts: {
    create: {
    title: 'How to create a MySQL database',
    content: 'Some content',
    },
    },
    },
    })
  4. Make an additive change - for example, create a new required field:

    /* ... */
    model Post {
    id Int @id @default(autoincrement())
    title String
    description String
    published Boolean @default(true)
    content String @db.VarChar(500)
    authorId Int
    author User @relation(fields: [authorId], references: [id])
    categories Category[]
    }
    /* ... */
  5. Push the changes:

    $npx prisma db push

    db push will prompt you to reset because you cannot add a required field to a table with existing content unless you provide a default value:

    ⚠️ We found changes that cannot be executed:
    • Added the required column `description` to the `Post` table without a default value. There are 2 rows in this table, it is not possible to execute this.
    ? To apply this step we need to reset the database, do you want to continue? All data will be lost. » (y/N)

    Tip: Use the --accept-data-loss flag to skip this warning, or --force-reset to ignore all warnings. Furthermore, if your schema changes mean that seed scripts will no longer work, you can use the --skip-seed flag to ignore seed scripts.

  6. Confirm data loss and apply changes to your database (or revisit your schema):

    There might be data loss when applying the changes:
    • Added the required column `description` to the `Post` table without a default value.
    ? Do you want to ignore the warning(s)? Some data will be lost. » (y/N)

    Note: Unlike Prisma Migrate, db push does not generate migrations that you can modify to preserve data, and is therefore best suited for prototyping in a development environment.

  7. Continue to evolve your schema until it reaches a relatively stable state.

  8. Initialize a migration history:

    $npx prisma migrate dev --name initial-state

    The steps taken to reach the initial prototype are not preserved - db push does not generate a history.

  9. Push your migration history and Prisma schema to source control.

At this point, the final draft of your prototyping is preserved in a migration and can be pushed to other environments (testing, production, or other members of your team).

Prototyping with an existing migration history

The following scenario demonstrates how to use db push to prototype a change to a Prisma schema where a migration history already exists.

  1. Check out the latest Prisma schema and migration history:

    generator client {
    provider = "prisma-client-js"
    }
    datasource db {
    provider = "postgresql"
    url = env("DATABASE_URL")
    }
    model User {
    id Int @id @default(autoincrement())
    name String
    jobTitle String
    posts Post[]
    profile Profile?
    }
    model Profile {
    id Int @id @default(autoincrement())
    biograpy String // Intentional typo!
    userId Int
    user User @relation(fields: userId, references: id)
    }
    model Post {
    id Int @id @default(autoincrement())
    title String
    published Boolean @default(true)
    content String @db.VarChar(500)
    authorId Int
    author User @relation(fields: [authorId], references: [id])
    categories Category[]
    }
    model Category {
    id Int @id @default(autoincrement())
    name String @db.VarChar(50)
    posts Post[]
    @@unique([name])
    }
  2. Prototype your new feature, which can involve any number of steps. For example, you might:

    • Create a tags String[] field, then run db push
    • Change the field type to tags Tag[] and add a new model named Tag, then run db push
    • Change your mind and restore the original tags String[] field, then call db push
    • Make a manual change to the tags field in the database - for example, adding a constraint

    After experimenting with several solutions, the final schema change looks like this:

    model Post {
    id Int @id @default(autoincrement())
    title String
    description String
    published Boolean @default(true)
    content String @db.VarChar(500)
    authorId Int
    author User @relation(fields: [authorId], references: [id])
    categories Category[]
    tags String[]
    }
  3. To create a migration that adds the new tags field, run the migrate dev command:

    $npx prisma migrate dev --name added-tags

    Prisma Migrate will prompt you to reset because the changes you made manually and with db push while prototyping are not part of the migration history:

    √ Drift detected: Your database schema is not in sync with your migration history.
    We need to reset the PostgreSQL database "prototyping" at "localhost:5432".
    Do you want to continue? All data will be lost. ... yes
  4. Prisma Migrate replays the existing migration history, generates a new migration based on your schema changes, and applies those changes to the database.

At this point, the final result of your prototyping is preserved in a migration, and can be pushed to other environments (testing, production, or other members of your team).

Edit this page on GitHub