Mastering Prisma: Database Setup, Migrations, and Schema Changes for Dev and Production

When working with Prisma, there are several considerations to keep in mind for both development and production environments. A common challenge is handling database schema changes as your project requirements evolve. Thankfully, Prisma makes managing database migrations straightforward.

Initial Setup

Prisma uses its own schema.prisma file to define your database schema, which can work seamlessly across different databases. Here’s how to get started.

Step 1: Set up your DATABASE_URL in your .env or .env.local file:

DATABASE_URL=postgres://postgres:password@127.0.0.1:5432/database

Step 2: Define where your schema will be located. By default, Prisma looks for schema.prisma in the prisma directory. You can change this by specifying the path in your package.json file if you’d like to store it elsewhere.

"prisma": { "schema": "db/schema" }

Step 3: Create schema.prisma in your schema directory with the following content:

datasource db { 
 provider = "postgresql" url = env("DATABASE_URL") }
 generator client { provider = "prisma-client-js"
 previewFeatures = ["prismaSchemaFolder", "driverAdapters"] }

The prismaSchemaFolder feature allows you to split your Prisma schema into multiple files for better organization. For example, you might create a blog.prisma file inside your schema directory:

model BlogPost { 
 id Int @id @default(autoincrement()) authorId String title 
 String coverImage String? published Boolean
 @default(false)  summary String @default("") content
 String @default("") reaction Int @default(0) deprecated
 Boolean @default(false) createdAt DateTime @default(now())
 updatedAt DateTime @updatedAt 
}

Step 4: To create your database tables based on this schema, run:

npx prisma db push

Step 5: Generate TypeScript types for your Prisma models:

npx prisma generate

Working with Migrations

So far, we’ve been working on a development database. Now let’s discuss database migrations for transitioning to production. Database migration refers to transferring or transforming the schema or data across environments. Prisma offers a few techniques for this, and you’ll need to proceed carefully to avoid data loss.

Step 1: Create your first migration:

npx prisma migrate dev --name first_db_setup

This command generates an SQL script inside the migrations directory, which creates all required tables. When you’re ready to deploy, you can copy this script to your database editor and execute it.

Step 2: Modifying Your Schema Each time you update your database schema, you can create a new migration script:

npx prisma migrate dev --name added_user_type

Running this command automatically updates your development database, so npx prisma push isn’t needed. Every migration is logged in the _prisma_migrations table, which tracks changes. This tracking helps detect database drift if migration files are manually modified.

Handling Complex Schema Changes

Some changes, like adding a non-nullable foreign key, require special handling. For instance, if you want to add a foreign key to a new BlogType table, making it non-nullable might require data deletion—something you might want to avoid.

In this case, generate the migration SQL script without applying it:

npx prisma migrate dev --name added_blogType_schema --create-only

This command generates an SQL file without making changes to your database. You can then manually edit the SQL to add the foreign key as nullable initially, create the BlogType schema, and later modify the foreign key to be non-nullable.

Once you’ve made these edits, run:

prisma migrate dev

Copy the SQL file to execute it against your production database when you’re ready.

Troubleshooting Migration Errors

Occasionally, you may see an error like:

The migration 20241102170118_added_blog_type_schema was modified after it was applied.

If you’re certain the changes were intended and not due to database drift, this can be resolved by carefully removing the record from your _prisma_migrations table. Proceed with caution here, as removing records can disrupt migration tracking.