Database Migrations

How to generate and run database migrations with Drizzle ORM

Concordia uses Drizzle ORM for type-safe database migrations. Migrations are automatically generated from schema changes and can be applied to development or production databases.

Migration System Overview

The migration system uses:

• Drizzle Kit: CLI tool for generating migrations from schema files
• PostgreSQL: Target database (local or production)
• TypeScript: Migration scripts with type safety
• Version Control: Migration history tracked in __drizzle_migrations table

Configuration Files

The project has separate configuration files for development and production:

Development Config

// drizzle-dev.config.ts
import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/database/schemas.ts',
  out: './src/database/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL_LOCAL!,
  },
});

Production Config

// drizzle-prod.config.ts
import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/database/schemas/**/*.ts',
  out: './src/database/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL_PROD!,
  },
});

The production config uses a glob pattern to load all schema files, while development uses a single export file.

Environment Variables

Set these in your .env file:

# Local development database
DATABASE_URL_LOCAL=postgresql://user:password@localhost:5432/concordia_dev

# Production database
DATABASE_URL_PROD=postgresql://user:password@host:5432/concordia_prod

# Use production database (default: false)
USE_PROD_DB=false

# Required for production operations
CONFIRM_PROD=oui

Generating Migrations

Basic Generation

After modifying schema files, generate a migration:

npm run db:generate

This command:

1. Scans schema files in src/database/schemas/
2. Compares with the current database state
3. Generates SQL migration files
4. Updates the migration journal

Generation Process

The generation script (scripts/db/db.generate.ts) automatically:

• Validates schema files - Ensures all schema files exist and are not empty
• Checks for missing imports - Prompts to add unamported schema files to schemas.ts
• Creates migration directory - Auto-creates src/database/migrations/meta/ if needed
• Initializes journal - Creates _journal.json if it doesn’t exist
• Detects new migrations - Shows which migration files were created

Interactive Import Management

If you add new schema files, the generator will prompt you:

[INTERACTIF] Certains fichiers ne sont pas importés dans schemas.ts :
  [1] services_bookings.ts
  [2] notification.ts
Voulez-vous ajouter des imports à schemas.ts ? (ex: 1,2 ou rien pour ignorer) :

Enter the numbers of schemas to import, separated by commas.

Production Generation

To generate migrations for production:

USE_PROD_DB=true npm run db:generate

Production generation compares against the production database. Make sure you’re connected to the correct database!

Migration Files

Migrations are stored in src/database/migrations/:

src/database/migrations/
├── 0002_narrow_silver_samurai.sql
├── 0003_calm_chat.sql
├── 0004_tricky_captain_flint.sql
├── 0005_harsh_metal_master.sql
└── meta/
    ├── _journal.json
    ├── 0002_snapshot.json
    ├── 0003_snapshot.json
    ├── 0004_snapshot.json
    └── 0005_snapshot.json

Migration File Format

Each migration is a .sql file with SQL statements:

-- Example: 0005_harsh_metal_master.sql
CREATE TABLE "services_media" (
	"id" text PRIMARY KEY NOT NULL,
	"url" text NOT NULL,
	"content_url" text,
	"type" text NOT NULL,
	"encoding_format" text,
	"width" text,
	"height" text,
	"duration" text,
	"license" text,
	"copyright_holder" text,
	"caption" jsonb,
	"description" jsonb,
	"alt" jsonb,
	"thumbnail_url" text,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "services_categories" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"name" jsonb NOT NULL,
	"description" jsonb,
	"icon" text,
	"featured_image_id" text,
	"parent_id" text,
	"sort_order" integer DEFAULT 0 NOT NULL,
	"display_in_home" boolean DEFAULT false NOT NULL,
	"display_in_menu" boolean DEFAULT true NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"is_featured" boolean DEFAULT false NOT NULL,
	"seo_title" jsonb,
	"seo_description" jsonb,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "services_categories_slug_unique" UNIQUE("slug")
);

Migration Journal

The _journal.json file tracks migration history:

{
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1771714997015,
      "tag": "0000_harsh_lady_vermin",
      "breakpoints": true
    },
    {
      "idx": 5,
      "version": "7",
      "when": 1772543861570,
      "tag": "0005_harsh_metal_master",
      "breakpoints": true
    }
  ]
}

Running Migrations

Apply Pending Migrations

Run all pending migrations:

npm run db:migrate

The migration script (scripts/db/db.migrate.ts):

1. Connects to the database
2. Creates __drizzle_migrations table if needed
3. Checks which migrations have been applied
4. Runs pending migrations in order
5. Records each migration in the tracking table

Migration Output

═══════════════════════════════════════════════════════
   🚀 Migration Drizzle - Connexion local/dev 🚀
═══════════════════════════════════════════════════════

Cible DB: postgresql://***@localhost:5432/concordia_dev (local/dev)
Connexion au client PostgreSQL...

[migrate] → 0005_harsh_metal_master

✔️  Toutes les migrations ont été appliquées.

═══════════════════════════════════════════════════════

Production Migrations

To run migrations on production:

USE_PROD_DB=true npm run db:migrate

You’ll be prompted for confirmation:

 PROD ATTENTION !! Vous ciblez la base de production : concordia_prod

Êtes-vous sûr de vouloir exécuter les migrations sur PROD ? (oui/non):

Or set CONFIRM_PROD=oui to skip the prompt:

USE_PROD_DB=true CONFIRM_PROD=oui npm run db:migrate

No Pending Migrations

If all migrations are already applied:

✔️  Aucun changement — aucune migration en attente pour la base concordia_dev (local/dev).

✔️  Aucune migration à appliquer.
Toutes les migrations sont déjà appliquées.

Migration Tracking

Migrations are tracked in the __drizzle_migrations table:

CREATE TABLE __drizzle_migrations (
  id text PRIMARY KEY,
  hash text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL DEFAULT now()
);

Query applied migrations:

SELECT * FROM __drizzle_migrations ORDER BY created_at ASC;

Database Reset (Development)

To completely reset the database and rerun all migrations:

npm run db:migrate -- --reset

This will DROP ALL TABLES in the database. Only use this in development!

The reset process:

1. Prompts for confirmation
2. Drops all tables in the public schema
3. Clears the migration history
4. Reruns all migrations from scratch

Migration Best Practices

1. Always Generate Before Migrating

# Generate migrations first
npm run db:generate

# Review the generated SQL files
cat src/database/migrations/0006_*.sql

# Then apply them
npm run db:migrate

2. Version Control

Commit migration files to git:

git add src/database/migrations/
git commit -m "Add user profile table migration"

3. Never Modify Applied Migrations

Once a migration has been applied (especially in production), never modify it. Instead:

1. Create a new migration to fix the issue
2. Generate it with npm run db:generate
3. Apply it with npm run db:migrate

4. Test Migrations Locally First

Always test migrations in development before production:

# 1. Generate migration
npm run db:generate

# 2. Test locally
npm run db:migrate

# 3. Verify application works
npm run dev

# 4. Deploy to production
USE_PROD_DB=true CONFIRM_PROD=oui npm run db:migrate

5. Handle Schema Changes Carefully

Adding Columns

Safe - Add new nullable columns or columns with defaults:

// Safe: nullable column
export const user = pgTable("user", {
  // ... existing fields
  bio: text("bio"), // nullable by default
});

// Safe: column with default
export const post = pgTable("post", {
  // ... existing fields
  views: integer("views").default(0).notNull(),
});

Removing Columns

Be careful - Ensure no code references the column:

// Before removing, search codebase for references
// Then remove from schema
export const user = pgTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  // oldField: text("old_field"), // REMOVED
});

Renaming Columns

Requires data migration:

// 1. Add new column
export const user = pgTable("user", {
  // ... existing
  fullName: text("full_name"), // new
});

// 2. Generate migration
// 3. Manually add data copy to migration:
// UPDATE user SET full_name = name;

// 4. Update code to use new column
// 5. Generate another migration to drop old column

Adding Constraints

Check existing data first:

// Before adding NOT NULL constraint:
// SELECT COUNT(*) FROM user WHERE email IS NULL;

export const user = pgTable("user", {
  id: text("id").primaryKey(),
  email: text("email").notNull(), // Adding NOT NULL
});

6. Use Transactions

The migration runner automatically wraps each statement in a transaction:

• If a migration fails, it rolls back
• The migration is marked as failed
• Fix the issue and rerun

7. Handle Duplicate Objects Gracefully

The migration system ignores duplicate object errors:

// Error codes ignored:
// 42P07 - relation already exists
// 42710 - constraint already exists  
// 42701 - column already exists

This allows reruns without breaking on existing objects.

Common Migration Scenarios

Adding a New Table

1. Create schema file

   Create src/database/schemas/my_table.schema.ts:

   import { pgTable, text, timestamp } from "drizzle-orm/pg-core";
   
   export const myTable = pgTable("my_table", {
     id: text("id").primaryKey(),
     name: text("name").notNull(),
     createdAt: timestamp("created_at").defaultNow().notNull(),
   });
   
2. Export from schemas.ts

   Add to src/database/schemas.ts:

   export * from './schemas/my_table.schema';
   
3. Generate migration

   npm run db:generate
   
4. Apply migration

   npm run db:migrate
   

Modifying Existing Table

1. Edit schema file

   Modify the schema definition in src/database/schemas/:

   export const user = pgTable("user", {
     id: text("id").primaryKey(),
     name: text("name").notNull(),
     bio: text("bio"), // NEW FIELD
   });
   
2. Generate migration

   npm run db:generate
   

   Review the generated SQL:

   ALTER TABLE "user" ADD COLUMN "bio" text;
   
3. Apply migration

   npm run db:migrate
   

Adding Foreign Key

1. Update schema

   export const post = pgTable("post", {
     id: text("id").primaryKey(),
     authorId: text("author_id")
       .notNull()
       .references(() => user.id, { onDelete: "cascade" }),
   });
   
2. Generate and apply

   npm run db:generate
   npm run db:migrate
   

Data Migration

For complex migrations involving data transformation:

1. Generate schema migration

   npm run db:generate
   
2. Edit SQL file

   Open the generated migration file and add data transformation:

   -- Generated by Drizzle
   ALTER TABLE "user" ADD COLUMN "full_name" text;
   
   -- Manual data migration
   UPDATE "user" SET "full_name" = "name" WHERE "full_name" IS NULL;
   
   -- Generated by Drizzle
   ALTER TABLE "user" DROP COLUMN "name";
   
3. Apply migration

   npm run db:migrate
   

Troubleshooting

Migration Fails

If a migration fails:

1. Check the error message - It will show which statement failed
2. Fix the schema - Correct the schema definition
3. Delete the failed migration - Remove the generated .sql file
4. Regenerate - Run npm run db:generate again
5. Reapply - Run npm run db:migrate

Schema Out of Sync

If your database schema doesn’t match the code:

# Reset local database (CAUTION: drops all data)
npm run db:migrate -- --reset

For production, create corrective migrations instead.

Missing Migration Journal

If _journal.json is missing:

mkdir -p src/database/migrations/meta
echo '{"entries":[]}' > src/database/migrations/meta/_journal.json
npm run db:generate

Schema File Not Found

If generation fails with “schema file not found”:

1. Check src/database/schemas.ts exists
2. Verify it exports all schema files
3. Ensure schema files use correct imports

Production Migration Failed

If a production migration fails:

1. Don’t panic - Migrations use transactions and roll back
2. Check logs - Identify the failing statement
3. Create fix migration - Generate a corrective migration
4. Test locally - Apply fix to local database first
5. Apply to production - Run fix migration on production

Next Steps

Schema Reference

View complete database schema documentation

Relationships

Learn about table relationships and foreign keys