Chapter 10: Database Migrations and Operations

In Chapters 7 and 9, we defined our database table structure in the src/db/schema/ directory. But we left a critical question: when you modify the schema (like adding a bio field to usersTable****), how do you safely apply this change to a production database that's already running live?

You can't just manually ALTER TABLE on the production database, right? That's too dangerous, and it can't be rolled back or synchronized across your team.

This is where Database Migrations come in.

10.1. Why Do We Need Migrations? (Schema Version Control)

Coming from the Python/Django world, you're definitely familiar with this concept. Django has makemigrations and migrate commands.

The essence of database migrations is: "version control for database Schema".

• Git manages your code: You know every historical version of your code.
• Migrations manage your database structure: You know every historical version of your database tables.

Why must we use migrations?

1. Consistency: Ensures your teammates' local databases, Staging environment, and Production environment have exactly the same table structure.
2. Safety: Migrations are atomic. If a migration script fails halfway through, the database rolls back to the last known state, preventing data corruption.
3. Traceability: Every SQL file in the migrations folder is a history record. You can know exactly "when the credits field was added to the users table."
4. Automation: In DevOps workflows (see later chapters), you can automatically run database migration scripts before deploying new code, ensuring code and database always match.

In Chapter 7, when we used pnpm db:push, we were prototyping. db:push compares your schema to the database and "brutally" syncs the database to match the schema (it might delete data!).

In production,****db:push is absolutely forbidden. In production, we must use db:generate and db:migrate****.

10.2. [Code Analysis]: Drizzle Kit Workflow in Practice

Drizzle Kit is the companion CLI tool for Drizzle ORM, playing the role of manage.py makemigrations in Django.

Let's define our scripts in package.json:

// package.json (scripts)
"scripts": {
  // ...
  "db:generate": "drizzle-kit generate:pg",
  "db:migrate": "drizzle-kit migrate:pg",
  "db:push": "drizzle-kit push:pg" // Development only
}

Our standard workflow (when src/db/schema/ changes) is as follows:

Step 1: Modify Schema

Suppose we want to add a bio field to usersTable (defined in src/db/schema/users.ts) for user biographies.

// src/db/schema/users.ts (modified)
export const usersTable = pgTable("users", {
  id: varchar("id").primaryKey(),
  name: text("name"),
  email: text("email").notNull().unique(),

  bio: text("bio"), // <-- [New field]

  // ... credits and stripe fields ...
});

Step 2: Generate Migration SQL (pnpm db:generate)

Now, we don't run db:push. We run:

pnpm db:generate

Drizzle Kit will launch and:

1. Read your current src/db/schema/ directory.
2. (Conceptually) Connect to your database and see the database's current state (through the drizzle/_meta table).
3. Compare the two and find differences.
4. Generate a SQL migration file instead of directly modifying the database.

After running, you'll find a new file in the src/db/migrations directory, like: 0001_neat_captain_america.sql (Drizzle automatically generates a random name).

Open this SQL file and see:

-- src/db/migrations/0001_neat_captain_america.sql
ALTER TABLE "users" ADD COLUMN "bio" text;

This is the Drizzle version of Django's makemigrations****!

Drizzle Kit accurately calculated the SQL statements needed to go from v0 to v1. This SQL file should now be committed to your Git repository like code.

Step 3: Execute Migration (pnpm db:migrate)

Now we have a pending migration file. How do we apply it to the database?

Run:

pnpm db:migrate

Drizzle Kit will launch and:

1. Connect to the database.
2. Query the __drizzle_migrations table in the database (automatically created by Drizzle) to see which migrations have already run.
3. Discover that 0001_neat_captain_america.sql hasn't been run yet.
4. Execute in order all pending migration SQL files.
5. Mark 0001_neat_captain_america.sql as run.

Now, your local database, your teammates' databases (pull from Git and run pnpm db:migrate), and your production database (run pnpm db:migrate in the deployment workflow) all safely have the bio field updated.

This is the Drizzle version of Django's migrate****!

This workflow (generate -> migrate) is the only correct way for Drizzle (and all professional ORMs) to manage production databases.

10.3. Backup and Recovery Strategy (PITR)

As an architect, your responsibility is not just building applications, but protecting data. If your database loses all user data due to hardware failure or malicious attack, your SAAS is finished.

Backups are essential.

In traditional Python/Flask operations, you might need to set up a cron job yourself to run pg_dump every morning to backup the database.

In modern cloud architecture, we rely on "managed databases" to solve this problem.

Whether it's Vercel Postgres, Neon (underlying Vercel Postgres), or AWS RDS, they all provide out-of-the-box automatic backups and Point-in-Time Recovery (PITR) features.

What is PITR?

• Regular Backup (Snapshot): Like taking a photo every day. If the database crashes at 3 PM, you can only recover to the 12 AM data, losing 15 hours of user data.
• PITR (Point-in-Time Recovery): Not only has snapshots, but also continuously records the database's "transaction log" (Write-Ahead Log, WAL). This is like recording video while taking photos.

If the database crashes at 3:00 PM, PITR allows you to recover the database to any second before the crash, for example 2:59:59 PM.

Architect's Decision: For our SAAS template, our architectural decision is: "never self-host production Postgres databases."

One of the key reasons we chose Vercel Postgres / Neon / AWS RDS is to purchase their PITR and High Availability services. This lets us focus on writing business logic (src/actions/) instead of waking up at 2 AM to handle database replication failures. This is a huge shift from traditional Python/Django operations thinking, replacing "operations manpower" with "cloud services."