Migrations#

Migrations version your database schema, allowing you to evolve it over time in a controlled way.

Defining Migrations#

Each migration has a version number, name, and up/down operations:

open Repodb

let migration_001 = Migration.migration
  ~version:1L
  ~name:"create_users"
  ~up:[
    Migration.create_table "users" [
      Migration.column "id" "BIGSERIAL" ~primary_key:true ~nullable:false;
      Migration.column "name" "VARCHAR(255)" ~nullable:false;
      Migration.column "email" "VARCHAR(255)" ~nullable:false ~unique:true;
      Migration.column "inserted_at" "TIMESTAMPTZ" ~nullable:false ~default:"NOW()";
      Migration.column "updated_at" "TIMESTAMPTZ" ~nullable:false ~default:"NOW()";
    ];
  ]
  ~down:[
    Migration.drop_table "users";
  ]

Migration Operations#

Create Table#

Migration.create_table "posts" [
  Migration.column "id" "BIGSERIAL" ~primary_key:true ~nullable:false;
  Migration.column "author_id" "BIGINT" ~nullable:false
    ~references:("users", "id");
  Migration.column "title" "VARCHAR(255)" ~nullable:false;
  Migration.column "body" "TEXT";
  Migration.column "published" "BOOLEAN" ~default:"FALSE";
]

Drop Table#

Migration.drop_table "posts"

Alter Table#

Migration.alter_table "users" [
  Migration.add_column (
    Migration.column "bio" "TEXT"
  );
  Migration.drop_column "legacy_field";
  Migration.rename_column ~from:"name" ~to_:"full_name";
]

Create Index#

(* Simple index *)
Migration.create_index "posts" ["author_id"]

(* Named index *)
Migration.create_index "posts" ["author_id"] ~name:"idx_posts_author"

(* Unique index *)
Migration.create_index "users" ["email"] ~unique:true

Drop Index#

Migration.drop_index "idx_posts_author"

Raw SQL#

For operations not covered by the DSL:

Migration.execute "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\""

Column Options#

Migration.column "field_name" "SQL_TYPE"
  ~nullable:false         (* NOT NULL constraint *)
  ~primary_key:true       (* PRIMARY KEY constraint *)
  ~unique:true            (* UNIQUE constraint *)
  ~default:"expression"   (* DEFAULT value/expression *)
  ~references:("table", "column")  (* FOREIGN KEY reference *)

Common Patterns#

Timestamps Helper#

let migration_001 = Migration.migration
  ~version:1L
  ~name:"create_users"
  ~up:[
    Migration.create_table "users" (
      [
        Migration.column "id" "BIGSERIAL" ~primary_key:true ~nullable:false;
        Migration.column "name" "VARCHAR(255)" ~nullable:false;
      ]
      @ Migration.timestamps ()  (* adds inserted_at and updated_at *)
    );
  ]
  ~down:[Migration.drop_table "users"]

Foreign Keys#

Migration.column "author_id" "BIGINT" ~nullable:false
  ~references:("users", "id")

For more control over foreign key behavior, use raw SQL:

Migration.execute
  "ALTER TABLE posts ADD CONSTRAINT fk_posts_author
   FOREIGN KEY (author_id) REFERENCES users(id)
   ON DELETE CASCADE ON UPDATE CASCADE"

Generating SQL#

Convert operations to SQL strings:

let sql_statements = Migration.generate_up_sql migration_001
(* [
     "CREATE TABLE users (
       id BIGSERIAL PRIMARY KEY NOT NULL,
       name VARCHAR(255) NOT NULL,
       email VARCHAR(255) NOT NULL UNIQUE,
       ...
     )";
   ] *)

let rollback_sql = Migration.generate_down_sql migration_001
(* ["DROP TABLE users"] *)

Schema Migrations Table#

repodb tracks applied migrations in a schema_migrations table:

(* SQL to create the tracking table *)
let create_table_sql = Migration.create_schema_migrations_sql

(* The table has: version (BIGINT PK), name (VARCHAR), inserted_at (TIMESTAMPTZ) *)

Planning Migrations#

Get Pending Migrations#

let all_migrations = [migration_001; migration_002; migration_003]
let applied_versions = [1L; 2L]  (* from schema_migrations table *)

let pending = Migration.pending_migrations ~applied_versions all_migrations
(* [migration_003] *)

Plan Migration Run#

(* Plan to run all pending *)
let actions = Migration.plan_migrate ~applied_versions ~target:None all_migrations

(* Plan to run up to specific version *)
let actions = Migration.plan_migrate ~applied_versions ~target:(Some 5L) all_migrations

Plan Rollback#

(* Rollback last migration *)
let actions = Migration.plan_rollback ~applied_versions ~step:None all_migrations

(* Rollback last 3 migrations *)
let actions = Migration.plan_rollback ~applied_versions ~step:(Some 3) all_migrations

Execute Plan#

let sql_list = Migration.actions_to_sql actions
(* Execute each SQL statement in order *)

Migration Status#

let status = Migration.format_status ~applied_versions ~migrations:all_migrations
(*
   Applied migrations:
     [✓] 1: create_users
     [✓] 2: create_posts

   Pending migrations:
     [ ] 3: add_comments
     [ ] 4: add_tags
*)

Complete Migration Runner Example#

let run_migrations conn migrations =
  (* Ensure schema_migrations table exists *)
  let create_table_sql = Migration.create_schema_migrations_sql in
  match Repo.exec conn create_table_sql ~params:[||] with
  | Error _ -> ()  (* Table might already exist *)
  | Ok () -> ();

  (* Get applied versions *)
  match Repo.query conn Migration.get_applied_versions_sql ~params:[||] with
  | Error e -> Error e
  | Ok rows ->
      let applied_versions = List.map (fun row ->
        Driver.row_int64 row 0
      ) rows in

      (* Plan and run *)
      let actions = Migration.plan_migrate
        ~applied_versions
        ~target:None
        migrations
      in

      Repo.transaction conn (fun conn ->
        let rec run_actions = function
          | [] -> Ok ()
          | action :: rest ->
              let sqls = Migration.action_to_sql action in
              let rec run_sqls = function
                | [] -> run_actions rest
                | sql :: more ->
                    match Repo.exec conn sql ~params:[||] with
                    | Error e -> Error e
                    | Ok () -> run_sqls more
              in
              run_sqls sqls
        in
        run_actions actions
      )

let rollback_last conn migrations =
  match Repo.query conn Migration.get_applied_versions_sql ~params:[||] with
  | Error e -> Error e
  | Ok rows ->
      let applied_versions = List.map (fun row ->
        Driver.row_int64 row 0
      ) rows in

      let actions = Migration.plan_rollback
        ~applied_versions
        ~step:(Some 1)
        migrations
      in

      Repo.transaction conn (fun conn ->
        let sqls = Migration.actions_to_sql actions in
        let rec run = function
          | [] -> Ok ()
          | sql :: rest ->
              match Repo.exec conn sql ~params:[||] with
              | Error e -> Error e
              | Ok () -> run rest
        in
        run sqls
      )

Best Practices#

Version numbers: Use timestamps or sequential integers. Timestamps prevent conflicts in teams.
Always provide down: Every migration should be reversible when possible.
Keep migrations small: One logical change per migration.
Don't modify old migrations: Once applied to production, create new migrations for changes.
Test rollbacks: Verify down migrations work correctly.
Data migrations: For data changes, use Migration.execute with INSERT/UPDATE statements.

Next Steps#

Schemas - Defining schemas in code
Repo - Database operations
Getting Started - Basic setup