repodb / docs / migrations.md

# 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:

```ocaml
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

```ocaml
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

```ocaml
Migration.drop_table "posts"
```

### Alter Table

```ocaml
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

```ocaml
(* 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

```ocaml
Migration.drop_index "idx_posts_author"
```

### Raw SQL

For operations not covered by the DSL:

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

## Column Options

```ocaml
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

```ocaml
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

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

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

```ocaml
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:

```ocaml
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:

```ocaml
(* 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

```ocaml
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

```ocaml
(* 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

```ocaml
(* 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

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

## Migration Status

```ocaml
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

```ocaml
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

1. **Version numbers**: Use timestamps or sequential integers. Timestamps prevent conflicts in teams.

2. **Always provide down**: Every migration should be reversible when possible.

3. **Keep migrations small**: One logical change per migration.

4. **Don't modify old migrations**: Once applied to production, create new migrations for changes.

5. **Test rollbacks**: Verify `down` migrations work correctly.

6. **Data migrations**: For data changes, use `Migration.execute` with INSERT/UPDATE statements.

## Next Steps

- [Schemas](schemas.md) - Defining schemas in code
- [Repo](repo.md) - Database operations
- [Getting Started](getting-started.md) - Basic setup