Database migrations in NextJS - Deep Dive

Overview - Database migrations

What is it?

Database migrations are a way to change a database's structure over time in a safe and organized manner. They help developers add, remove, or modify tables and columns without losing data. Migrations keep track of these changes so everyone working on the project has the same database setup. This is especially useful when multiple people or environments are involved.

Why it matters

Without migrations, changing a database would be risky and chaotic. Developers might overwrite each other's changes or lose data. It would be like trying to rebuild a house while people still live inside, without a plan. Migrations make database updates predictable and reversible, saving time and preventing costly mistakes.

Where it fits

Before learning migrations, you should understand basic databases and how to write queries. Knowing how to connect a Next.js app to a database is helpful. After migrations, you can learn about seeding data, database version control, and advanced schema design.

Mental Model

Core Idea

Database migrations are like a step-by-step recipe that safely updates your database structure over time.

Think of it like...

Imagine you have a recipe book that tells you exactly how to bake a cake step by step. Each migration is like adding a new step or changing an ingredient in the recipe, so the cake (database) changes exactly how you want it without ruining it.

┌───────────────┐
│ Initial Setup │
└──────┬────────┘
       │
       ▼
┌───────────────┐      ┌───────────────┐      ┌───────────────┐
│ Migration 1   │ ──▶ │ Migration 2   │ ──▶ │ Migration 3   │
│ (Add column)  │      │ (Change type) │      │ (Remove table)│
└───────────────┘      └───────────────┘      └───────────────┘
       │                    │                    │
       ▼                    ▼                    ▼
┌─────────────────────────────────────────────────────┐
│ Final Database Structure after all migrations applied│
└─────────────────────────────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

What are database migrations

Concept: Introduce the basic idea of migrations as controlled database changes.

A database migration is a file or script that describes a change to the database structure. For example, adding a new table or column. These files are applied in order to update the database safely. They keep track of what changes have been made.

Result

You understand that migrations are instructions for changing the database step by step.

Understanding migrations as instructions helps you see how databases evolve without breaking existing data.

2

Foundation

Why migrations matter in Next.js apps

Concept: Explain the role of migrations in web apps like those built with Next.js.

Next.js apps often use databases to store user data. When the app changes, the database structure must change too. Migrations help keep the database in sync with the app code, especially when multiple developers work together or when deploying to different environments.

Result

You see migrations as a bridge between app code and database structure.

Knowing migrations keep app and database aligned prevents bugs caused by mismatched data structures.

3

Intermediate

Creating and running migrations

🤔Before reading on: do you think migrations are run manually or automatically? Commit to your answer.

Concept: Learn how to create migration files and apply them to the database.

In Next.js projects, tools like Prisma or Knex help create migration files. You write commands to add or change tables and columns. Then you run a command like `npx prisma migrate dev` or `knex migrate:latest` to apply these changes. The tool records which migrations have run.

Result

You can create migration files and update your database safely.

Understanding the workflow of creating and applying migrations helps avoid manual errors and keeps history clear.

4

Intermediate

Migration rollback and version control

🤔Before reading on: do you think migrations can be undone easily? Commit to yes or no.

Concept: Introduce the idea of undoing migrations and tracking versions.

Good migration tools allow you to rollback changes if something goes wrong. For example, `prisma migrate reset` or `knex migrate:rollback` reverses the last migration. Migrations are stored in order, so you can move forward or backward between versions. This helps fix mistakes or test changes safely.

Result

You know how to undo migrations and manage database versions.

Knowing rollback exists gives confidence to experiment and fix errors without permanent damage.

5

Intermediate

Handling data changes with migrations

Concept: Explain how migrations can also modify data, not just structure.

Sometimes migrations need to update existing data, like filling a new column with default values. You can write scripts inside migrations to change data safely. This ensures the database stays consistent with the new structure and app expectations.

Result

You understand migrations can handle both structure and data changes.

Recognizing data updates in migrations prevents bugs caused by missing or inconsistent data.

6

Advanced

Migrations in production environments

🤔Before reading on: do you think running migrations in production is automatic or requires special care? Commit to your answer.

Concept: Discuss best practices for applying migrations safely in live apps.

In production, migrations must be tested and applied carefully to avoid downtime or data loss. Teams often run migrations during maintenance windows or use zero-downtime deployment strategies. Tools may support transactional migrations that apply all changes or none if errors occur.

Result

You know how to safely update production databases with migrations.

Understanding production risks helps prevent costly outages and data corruption.

7

Expert

Complex migration challenges and solutions

🤔Before reading on: do you think migrations always run smoothly on large databases? Commit to yes or no.

Concept: Explore tricky cases like large data, schema conflicts, and migration conflicts.

Large databases can make migrations slow or risky. Conflicts happen when multiple developers create migrations that clash. Solutions include splitting migrations, using feature flags, or manual intervention. Some teams use branching strategies and migration squashing to keep history manageable.

Result

You appreciate the complexity and strategies to handle real-world migration challenges.

Knowing these challenges prepares you for scaling and teamwork issues in database evolution.

Under the Hood

Migrations work by storing a list of ordered change scripts and a record of which have been applied in a special database table. When you run migration commands, the tool compares applied migrations with available ones and runs only the new scripts. Each migration script contains SQL or commands that alter tables, columns, or data. Some tools wrap these changes in transactions to ensure all-or-nothing application.

Why designed this way?

Migrations were designed to solve the problem of evolving databases without losing data or causing conflicts. Before migrations, developers manually edited databases, leading to errors and inconsistencies. The ordered, recorded approach ensures everyone applies the same changes in the same order, making collaboration and deployment reliable.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Migration 1   │──────▶│ Migration 2   │──────▶│ Migration 3   │
└──────┬────────┘       └──────┬────────┘       └──────┬────────┘
       │                       │                       │
       ▼                       ▼                       ▼
┌─────────────────────────────────────────────────────────┐
│ Migration History Table (records applied migrations)    │
│ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐   │
│ │ Migration 1   │ │ Migration 2   │ │ Migration 3   │   │
│ └───────────────┘ └───────────────┘ └───────────────┘   │
└─────────────────────────────────────────────────────────┘
       │
       ▼
┌─────────────────────────────┐
│ Database Structure & Data   │
│ (Updated by migrations)     │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think migrations automatically update your app code? Commit to yes or no.

Common Belief: Migrations automatically update the app code to match the database changes.

Reality: Migrations only change the database structure; you must update your app code separately to use the new schema.

Why it matters: Assuming migrations update app code can cause runtime errors and broken features.

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

Common Belief: Once migrations are applied, old migration files can be deleted safely.

Reality: Migration files should be kept in version control because they document the database history and are needed for new environments or rollbacks.

Why it matters: Deleting migration files breaks the ability to set up or rollback databases consistently.

Quick: Do you think migrations always run instantly regardless of database size? Commit to yes or no.

Common Belief: Migrations run quickly and smoothly no matter how big the database is.

Reality: Large databases can make migrations slow or risky, requiring special strategies to avoid downtime or data loss.

Why it matters: Ignoring this can cause long outages or corrupted data in production.

Quick: Do you think migrations can fix data bugs automatically? Commit to yes or no.

Common Belief: Migrations automatically fix data bugs and inconsistencies.

Reality: Migrations change structure and can modify data, but fixing data bugs often requires separate scripts or manual fixes.

Why it matters: Relying on migrations alone for data fixes can leave bugs unresolved.

Expert Zone

1

Migration order matters deeply; changing the order or editing applied migrations can cause serious inconsistencies.

2

Transactional migrations prevent partial application but are not supported by all databases or all migration commands.

3

Squashing migrations (combining many into one) helps keep history clean but requires careful coordination to avoid conflicts.

When NOT to use

Migrations are not ideal for very simple or static databases where structure never changes. For rapid prototyping, schema-less databases or manual schema changes might be faster. Also, for very large databases with complex data, specialized tools or manual DBA intervention may be better.

Production Patterns

In production, teams use CI/CD pipelines to run migrations automatically during deployment with backups and monitoring. Blue-green deployments or feature flags help avoid downtime. Migration scripts are reviewed and tested separately. Some use separate migration branches to avoid conflicts.

Connections

Version Control Systems

Migrations build on the idea of ordered, tracked changes like commits in version control.

Understanding version control helps grasp why migrations must be ordered and recorded to keep databases consistent.

Software Deployment Pipelines

Migrations are a key step in deployment pipelines to update databases alongside app code.

Knowing deployment pipelines clarifies how migrations fit into automated, safe app updates.

Construction Project Management

Like managing building renovations step-by-step, migrations manage database changes carefully to avoid damage.

Seeing migrations as project steps helps appreciate the planning and coordination needed for safe changes.

Common Pitfalls

#1 Running migrations without backing up production data

Wrong approach:

npx prisma migrate deploy

Correct approach:

# Backup database first
pg_dump mydb > backup.sql
npx prisma migrate deploy

Root cause:

Assuming migrations are always safe without considering data loss risks.

#2 Editing migration files after they have been applied

Wrong approach:

// Edit old migration file to fix a typo
// Then run migrations again

Correct approach:

// Create a new migration file with the fix
npx prisma migrate dev

Root cause:

Misunderstanding that applied migrations are part of history and should not be changed.

#3 Ignoring migration errors and forcing app to run

Wrong approach:

Ignoring migration failures and starting Next.js app anyway

Correct approach:

Fix migration errors before running app to ensure database matches code

Root cause:

Not realizing app depends on database schema matching migrations.

Key Takeaways

Database migrations are step-by-step instructions to safely change database structure over time.

They keep track of changes so multiple developers and environments stay in sync.

Migrations must be created, applied, and sometimes rolled back using tools integrated with Next.js.

Running migrations in production requires careful planning to avoid downtime or data loss.

Understanding migration order, rollback, and data updates is key to managing evolving databases successfully.