Database Migrations

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:

Scans schema files in src/database/schemas/
Compares with the current database state
Generates SQL migration files
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):

Connects to the database
Creates __drizzle_migrations table if needed
Checks which migrations have been applied
Runs pending migrations in order
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:

Prompts for confirmation
Drops all tables in the public schema
Clears the migration history
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:

Create a new migration to fix the issue
Generate it with npm run db:generate
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
Removing Columns
Renaming Columns
Adding Constraints

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(),
});

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
});

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

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

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(),
});

Export from schemas.ts

Add to src/database/schemas.ts:

export * from './schemas/my_table.schema';

Generate migration

npm run db:generate

Apply migration

npm run db:migrate

Modifying Existing Table

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
});

Generate migration

npm run db:generate

Review the generated SQL:

ALTER TABLE "user" ADD COLUMN "bio" text;

Apply migration

npm run db:migrate

Adding Foreign Key

Update schema

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

Generate and apply

npm run db:generate
npm run db:migrate

Data Migration

For complex migrations involving data transformation:

Generate schema migration

npm run db:generate

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";

Apply migration

npm run db:migrate

Troubleshooting

Migration Fails

If a migration fails:

Check the error message - It will show which statement failed
Fix the schema - Correct the schema definition
Delete the failed migration - Remove the generated .sql file
Regenerate - Run npm run db:generate again
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”:

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

Production Migration Failed

If a production migration fails:

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

​Migration System Overview

​Configuration Files

​Development Config

​Production Config

​Environment Variables

​Generating Migrations

​Basic Generation

​Generation Process

​Interactive Import Management

​Production Generation

​Migration Files

​Migration File Format

​Migration Journal

​Running Migrations

​Apply Pending Migrations

​Migration Output

​Production Migrations

​No Pending Migrations

​Migration Tracking

​Database Reset (Development)

​Migration Best Practices

​1. Always Generate Before Migrating

​2. Version Control

​3. Never Modify Applied Migrations

​4. Test Migrations Locally First

​5. Handle Schema Changes Carefully

​6. Use Transactions

​7. Handle Duplicate Objects Gracefully

​Common Migration Scenarios

​Adding a New Table

​Modifying Existing Table

​Adding Foreign Key

​Data Migration

​Troubleshooting

​Migration Fails

​Schema Out of Sync

​Missing Migration Journal

​Schema File Not Found

​Production Migration Failed

​Next Steps

Schema Reference

Relationships

Migration System Overview

Configuration Files

Development Config

Production Config

Environment Variables

Generating Migrations

Basic Generation

Generation Process

Interactive Import Management

Production Generation

Migration Files

Migration File Format

Migration Journal

Running Migrations

Apply Pending Migrations

Migration Output

Production Migrations

No Pending Migrations

Migration Tracking

Database Reset (Development)

Migration Best Practices

1. Always Generate Before Migrating

2. Version Control

3. Never Modify Applied Migrations

4. Test Migrations Locally First

5. Handle Schema Changes Carefully

6. Use Transactions

7. Handle Duplicate Objects Gracefully

Common Migration Scenarios

Adding a New Table

Modifying Existing Table

Adding Foreign Key

Data Migration

Troubleshooting

Migration Fails

Schema Out of Sync

Missing Migration Journal

Schema File Not Found

Production Migration Failed

Next Steps