Prototyping your schema

The Prisma CLI has a dedicated command for prototyping schemas: db push

db push uses the same engine as Prisma Migrate to synchronize your Prisma schema with your database schema. The db push command:

1. Introspects the database to infer and executes the changes required to make your database schema reflect the state of your Prisma schema.

2. By default, after changes have been applied to the database schema, generators are triggered (for example, Prisma Client). You do not need to manually invoke prisma generate.

3. If db push anticipates that the changes could result in data loss, it will:

   • Throw an error
   • Require the --accept-data-loss option if you still want to make the changes

Notes:

• db push does not interact with or rely on migrations. The migrations table _prisma_migrations will not be created or updated, and no migration files will be generated.
• When working with PlanetScale, we recommend that you use db push instead of migrate. For details refer to our Getting started documentation, either Start from scratch guide or Add to existing project guide depending on your situation.

db push works well if:

• You want to quickly prototype and iterate on schema design locally without the need to deploy these changes to other environments such as other developers, or staging and production environments.
• You are prioritizing reaching a desired end-state and not the changes or steps executed to reach that end-state (there is no way to preview changes made by db push)
• You do not need to control how schema changes impact data. There is no way to orchestrate schema and data migrations—if db push anticipates that changes will result in data loss, you can either accept data loss with the --accept-data-loss option or stop the process. There is no way to customize the changes.

See Schema prototyping with db push for an example of how to use db push in this way.

db push is not recommended if:

• You want to replicate your schema changes in other environments without losing data. You can use db push for prototyping, but you should use migrations to commit the schema changes and apply these in your other environments.
• You want fine-grained control over how the schema changes are executed - for example, renaming a column instead of dropping it and creating a new one.
• You want to keep track of changes made to the database schema over time. db push does not create any artifacts that allow you to keep track of these changes.
• You want the schema changes to be reversible. You can use db push again to revert to the original state, but this might result in data loss.

You can use db push and Prisma Migrate together in your development workflow . For example, you can:

• Use db push to prototype a schema at the start of a project and initialize a migration history when you are happy with the first draft
• Use db push to prototype a change to an existing schema, then run prisma migrate dev to generate a migration from your changes (you will be asked to reset)

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"
   
     output   = "./generated"
   
   }
   
   datasource db {
   
     provider = "postgresql"
   
   }
   
   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    @unique
   
     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:

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma db push

   pnpm prisma db push

   yarn prisma db push

   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:

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:

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma db push

   pnpm prisma db push

   yarn prisma db push

   npx prisma db push

   db push will fail because you cannot add a required field to a table with existing content unless you provide a default value.

   6. Reset all data in your database and re-apply migrations.

   db push will fail because you cannot add a required field to a table with existing content unless you provide a default value.

6. Reset all data in your database and re-apply migrations.

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate reset

   pnpm prisma migrate reset

   yarn prisma migrate reset

   npx prisma migrate reset

   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:

   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:

   • Tab 1
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev --name initial-state

   pnpm prisma migrate dev --name initial-state

   yarn prisma migrate dev --name initial-state

   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 (e.g. Git).

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).

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"
   
     output   = "./generated"
   
   }
   
   datasource db {
   
     provider = "postgresql"
   
   }
   
   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    @unique
   
     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:

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate dev --name added-tags

   pnpm prisma migrate dev --name added-tags

   yarn prisma migrate dev --name added-tags

   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".

   [!WARNING] This will result in total data loss.

   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".

   • bun
   • pnpm
   • yarn
   • npm

   bunx prisma migrate reset

   pnpm prisma migrate reset

   yarn prisma migrate reset

   npx prisma migrate reset

   4. Prisma Migrate replays the existing migration history, generates a new migration based on your schema changes, and applies those changes to the database.

   [!NOTE] When using migrate dev, if your schema changes mean that seed scripts will no longer work, you can use the --skip-seed flag to ignore seed scripts.

   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).

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).