Prisma migrations in NestJS - Deep Dive

Overview - Prisma migrations

What is it?

Prisma migrations are a way to safely change your database structure over time. They help you update tables, columns, and relationships without losing data. Using migrations, you write changes in a file and apply them step-by-step to your database. This keeps your database and code in sync as your app grows.

Why it matters

Without migrations, changing a database can be risky and confusing. You might lose data or break your app if changes are done manually or inconsistently. Migrations automate and track these changes, making development smoother and safer. They let teams work together without overwriting each other's database updates.

Where it fits

Before learning Prisma migrations, you should understand basic databases and how Prisma models map to tables. After migrations, you can explore advanced database management, deployment automation, and continuous integration with database changes.

Mental Model

Core Idea

Prisma migrations are like a step-by-step recipe that safely updates your database structure as your app evolves.

Think of it like...

Imagine building a LEGO model where each migration is an instruction booklet page that tells you exactly which bricks to add or remove without breaking the model.

┌───────────────┐
│ Prisma Schema │
└──────┬────────┘
       │ changes
       ▼
┌───────────────┐
│ Migration File│
│ (SQL or DSL)  │
└──────┬────────┘
       │ applied
       ▼
┌───────────────┐
│ Database      │
│ (tables, cols)│
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Prisma Schema Basics

Concept: Learn what the Prisma schema file is and how it defines your database structure.

The Prisma schema is a simple text file where you describe your data models. Each model corresponds to a database table, and fields correspond to columns. For example, a User model with id and name fields defines a users table with those columns.

Result

You can write a schema that represents your data structure clearly and simply.

Understanding the schema is key because migrations are generated from changes in this file.

2

Foundation

What Are Migrations in Prisma?

Concept: Introduce the idea that migrations are files that record changes to the schema and update the database accordingly.

When you change the Prisma schema, you create a migration file that describes how to update the database. This file contains SQL commands or Prisma's own format to add, remove, or modify tables and columns.

Result

You have a clear, versioned record of every change made to your database structure.

Migrations make database changes repeatable and reversible, which is safer than manual edits.

3

Intermediate

Creating and Applying Migrations

🤔Before reading on: do you think Prisma migrations update the database automatically when you save the schema, or do you need to run a command? Commit to your answer.

Concept: Learn the commands to generate and apply migrations using Prisma CLI.

You use 'prisma migrate dev' to create a new migration after changing the schema. This command generates a migration file and applies it to your local database. To apply migrations in production, you use 'prisma migrate deploy'.

Result

Your database structure updates to match your schema changes safely and consistently.

Knowing the difference between development and production migration commands prevents accidental data loss or errors.

4

Intermediate

Managing Migration Files and History

🤔Before reading on: do you think migration files are temporary or should be kept in version control? Commit to your answer.

Concept: Understand that migration files are permanent records and should be tracked in your code repository.

Each migration is stored as a folder with SQL or Prisma migration scripts and a timestamped name. These files let you track the history of database changes and share them with your team. You commit them to Git or other version control systems.

Result

You have a clear, shared history of database changes that helps collaboration and debugging.

Treating migrations as code ensures team synchronization and prevents conflicts or lost changes.

5

Intermediate

Handling Migration Conflicts in Teams

🤔Before reading on: do you think two developers can create migrations independently without problems? Commit to your answer.

Concept: Learn how to resolve conflicts when multiple people create migrations at the same time.

If two developers create migrations from different schema changes, their migration files may conflict. You must merge these carefully by reapplying migrations or resetting the database. Prisma provides commands like 'prisma migrate resolve' to help mark migrations as applied.

Result

You can keep your database consistent even when multiple people change it simultaneously.

Understanding conflict resolution prevents broken databases and lost work in team environments.

6

Advanced

Customizing Migrations with SQL Scripts

🤔Before reading on: do you think Prisma migrations only support automatic schema changes, or can you add custom SQL? Commit to your answer.

Concept: Learn how to add manual SQL commands to migrations for advanced database operations.

Sometimes you need to do things Prisma can't generate automatically, like complex data transformations or creating indexes. You can edit migration SQL files to add these commands. Be careful to keep migrations idempotent and reversible.

Result

You can handle complex database changes that Prisma's automatic system doesn't cover.

Knowing how to customize migrations gives you full control over your database evolution.

7

Expert

Internals of Prisma Migration Engine

🤔Before reading on: do you think Prisma applies migrations by comparing schemas at runtime or by running saved scripts? Commit to your answer.

Concept: Understand how Prisma migration engine works under the hood to apply changes safely.

Prisma migration engine generates migration files by comparing the current schema with the last applied schema. It saves these as SQL scripts. When applying, it runs these scripts in order, tracking which have been applied in a special migrations table. This avoids reapplying or skipping steps.

Result

You understand why migrations are reliable and how Prisma prevents accidental data loss.

Knowing the engine's design helps debug migration issues and optimize workflows.

Under the Hood

Prisma migrations work by tracking schema changes in the Prisma schema file. When you run migration commands, Prisma compares the new schema to the last applied one and generates SQL scripts that transform the database structure accordingly. It stores migration history in a dedicated table inside the database to know which migrations have been applied. This ensures migrations run only once and in the correct order.

Why designed this way?

This design balances automation with control. Automatically generating SQL from schema changes reduces human error and speeds development. Storing migration history inside the database prevents accidental reapplication and keeps multiple environments consistent. Alternatives like manual SQL scripts or schema diffs without history were error-prone and hard to maintain.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Prisma Schema │──────▶│ Migration Gen │──────▶│ Migration File│
└──────┬────────┘       └──────┬────────┘       └──────┬────────┘
       │                       │                       │
       │                       │                       ▼
       │                       │               ┌───────────────┐
       │                       │               │ Database      │
       │                       │               │ (migrations  │
       │                       │               │  table + data)│
       │                       │               └───────────────┘
       │                       │                       ▲
       │                       └───────────────────────┘
       │
       └─────────────────────────────────────────────────────▶

Myth Busters - 4 Common Misconceptions

Quick: Do you think Prisma migrations automatically update your production database when you run 'prisma migrate dev'? Commit to yes or no.

Common Belief: Running 'prisma migrate dev' updates all environments including production automatically.

Reality: 'prisma migrate dev' only updates your local development database. Production databases require running 'prisma migrate deploy' explicitly.

Why it matters: Assuming automatic production updates can cause unexpected downtime or data loss if migrations run without proper testing.

Quick: Do you think you can safely delete migration files after applying them? Commit to yes or no.

Common Belief: Once migrations are applied, migration files are no longer needed and can be deleted.

Reality: Migration files must be kept in version control to track database history and allow new environments to apply all changes from scratch.

Why it matters: Deleting migration files breaks the ability to recreate or synchronize databases, causing errors in team or deployment workflows.

Quick: Do you think Prisma migrations can handle all database changes automatically without manual edits? Commit to yes or no.

Common Belief: Prisma migrations automatically handle every possible database schema change without manual intervention.

Reality: Some complex changes, like data migrations or custom indexes, require manual SQL edits in migration files.

Why it matters: Ignoring this leads to incomplete migrations and runtime errors in production.

Quick: Do you think two developers can create migrations independently and merge them without conflicts? Commit to yes or no.

Common Belief: Multiple developers can create migrations independently and merge them without any extra steps.

Reality: Migrations can conflict and require careful merging and sometimes resetting the database to keep consistency.

Why it matters: Not handling conflicts properly causes broken migrations and lost data.

Expert Zone

1

Migration files are not just SQL scripts; they include metadata that Prisma uses to track applied migrations and dependencies.

2

Prisma's migration engine uses a shadow database to safely generate migration SQL without affecting your real data.

3

The order of migrations matters deeply; reordering or editing applied migrations can corrupt your database state.

When NOT to use

Prisma migrations are not ideal for databases with heavy manual tuning or legacy schemas that require complex migrations. In such cases, using dedicated database migration tools like Flyway or Liquibase might be better.

Production Patterns

In production, teams use CI/CD pipelines to run 'prisma migrate deploy' after testing migrations locally. They keep migration files in Git and review SQL changes before applying. Rollbacks are handled by writing explicit down migrations or restoring backups.

Connections

Version Control Systems

Prisma migrations build on the idea of version control by tracking database changes as code files.

Understanding how migrations are versioned like code helps grasp why collaboration and history tracking are essential for database safety.

Continuous Integration/Continuous Deployment (CI/CD)

Migrations integrate into CI/CD pipelines to automate safe database updates during app deployment.

Knowing this connection shows how migrations fit into modern software delivery, ensuring database and app stay in sync.

Legal Document Revision History

Like legal documents track every change with timestamps and authors, migrations record every database change step-by-step.

This cross-domain link highlights the importance of traceability and accountability in managing evolving complex systems.

Common Pitfalls

#1 Running 'prisma migrate dev' directly on production database.

Wrong approach:

npx prisma migrate dev --schema=./prisma/schema.prisma --preview-feature --url="production_db_url"

Correct approach:

npx prisma migrate deploy --schema=./prisma/schema.prisma --url="production_db_url"

Root cause:

Confusing development and production commands leads to unsafe migrations on live data.

#2 Deleting migration folders after applying them.

Wrong approach:

rm -rf prisma/migrations/20230601000000_add_user_table

Correct approach:

Keep all migration folders under version control and never delete them.

Root cause:

Misunderstanding that migration files are needed for database history and new environment setup.

#3 Editing applied migration SQL files to fix bugs.

Wrong approach:

Changing SQL inside an already applied migration folder to fix a typo.

Correct approach:

Create a new migration with the fix instead of editing applied migrations.

Root cause:

Not realizing that applied migrations must remain immutable to keep database consistency.

Key Takeaways

Prisma migrations safely and clearly manage database changes by generating and applying step-by-step scripts from schema updates.

Migration files are permanent records that must be version controlled and shared among team members to avoid conflicts and data loss.

Different commands exist for development and production environments to prevent accidental data corruption.

Advanced users can customize migrations with manual SQL for complex changes beyond Prisma's automatic capabilities.

Understanding the migration engine internals helps debug issues and design robust database update workflows.