> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ooneex.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Migrations

> Apply versioned, transactional schema changes to your database with up/down rollbacks and dependency ordering.

The `@ooneex/migrations` component is a database migration runner for Bun's native SQL client. Each migration is a class implementing `IMigration` with `up` and `down` methods, a timestamp-based version, and an optional dependency list. Migrations are registered with a decorator, sorted by version, and run inside a transaction — applied versions are tracked in a database table so each one runs exactly once.

## Why this component

* **Versioned and ordered.** Every migration carries a timestamp version (`YYYYMMDDHHMMSSMMM`); the runner sorts by version and applies them in order.
* **Up and down.** `up()` applies the change, `down()` reverses it — so schema changes stay symmetric and reviewable.
* **Transaction-safe.** Each migration runs inside `sql.begin()`; a failure rolls back the whole migration and stops the run.
* **Run-once tracking.** Applied versions are recorded in a tracking table (default `migrations`); already-applied migrations are skipped.
* **Dependency-aware.** A migration can declare other migrations as dependencies, and they run first.
* **Container-managed.** Register a migration class with a decorator and the runner resolves it from the container.

## How it works

You write a migration class that implements `IMigration` and register it with `@decorator.migration()`. The `up()` runner loads every registered migration, sorts it by version, and applies each pending one inside a transaction, recording the version when it succeeds.

| Member              | Purpose                                                                                |
| ------------------- | -------------------------------------------------------------------------------------- |
| `up(tx, sql)`       | Apply the schema change. Runs inside a transaction (`tx`); `sql` is the pooled client. |
| `down(tx, sql)`     | Reverse the schema change — drop exactly what `up` added.                              |
| `getVersion()`      | Return the timestamp version string; used for ordering and tracking.                   |
| `getDependencies()` | Return the migration classes that must run before this one.                            |

The `up()` runner drives the lifecycle:

| Function                               | Purpose                                                              |
| -------------------------------------- | -------------------------------------------------------------------- |
| `up(config?)`                          | Run all pending migrations in version order, each in a transaction.  |
| `migrationCreate(config?)`             | Generate a new migration file (and its test) from the template.      |
| `getMigrations()`                      | Return every registered migration instance, sorted by version.       |
| `generateMigrationVersion()`           | Produce a `YYYYMMDDHHMMSSMMM` timestamp version string.              |
| `createMigrationTable(sql, tableName)` | Create the tracking table (`id VARCHAR(20) PRIMARY KEY`) if missing. |

The tracking table stores one row per applied version. Before running a migration, the runner checks the table for its `id`; if present, the migration is skipped. Passing `--drop` to the runner first drops and recreates the `public` schema — destructive, for development only.

## Environment variables

| Variable       | Required                                       | Purpose                                                                                         |
| -------------- | ---------------------------------------------- | ----------------------------------------------------------------------------------------------- |
| `DATABASE_URL` | Yes (unless `databaseUrl` is passed in config) | Postgres connection string used by the runner, e.g. `postgres://user:pass@localhost:5432/mydb`. |

```bash theme={null}
DATABASE_URL=postgres://user:pass@localhost:5432/mydb
```

## Usage

A migration is a class implementing `IMigration`, registered with the decorator. `up()` applies the change and `down()` reverses it:

```typescript theme={null}
import { decorator } from "@ooneex/migrations";
import type { IMigration, MigrationClassType } from "@ooneex/migrations";
import type { SQL, TransactionSQL } from "bun";

@decorator.migration()
export class Migration20240115103045 implements IMigration {
  public async up(tx: TransactionSQL, sql: SQL): Promise<void> {
    await tx`CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) NOT NULL UNIQUE,
      created_at TIMESTAMP DEFAULT NOW()
    )`;
  }

  public async down(tx: TransactionSQL, sql: SQL): Promise<void> {
    await tx`DROP TABLE IF EXISTS users`;
  }

  public getVersion(): string {
    return "20240115103045";
  }

  public getDependencies(): MigrationClassType[] {
    return [];
  }
}
```

Run all pending migrations with `up()`. It reads `DATABASE_URL` by default, or takes an explicit connection string and tracking table name:

```typescript theme={null}
import { up } from "@ooneex/migrations";

// Uses DATABASE_URL and the default "migrations" table
await up();

// Or with explicit config
await up({
  databaseUrl: "postgres://user:pass@localhost:5432/mydb",
  tableName: "schema_migrations",
});
```

Declare dependencies to guarantee ordering beyond the version timestamp — a migration's dependencies always run first:

```typescript theme={null}
public getDependencies(): MigrationClassType[] {
  return [Migration20240115103045];
}
```

## Best practices

* **Make `down()` reverse `up()` exactly.** Drop precisely what `up` adds; an asymmetric or irreversible `down()` is a bug.
* **Never edit an applied migration.** On a shared database, add a new corrective migration instead of rewriting one that already ran.
* **Index what you query.** Add indexes for foreign keys, `WHERE` / `ORDER BY` columns, and unique constraints in `up()`, and drop them in `down()` before the table or column they cover.
* **Match the entity.** Keep column types, nullability, and lengths in sync with the entity definition — a non-nullable column must be `NOT NULL` in the migration.
* **Reserve `--drop` for development.** It wipes the whole schema; never run it against a shared or production database.
* **Use dependencies for cross-migration ordering.** When one change must precede another regardless of timestamps, declare it in `getDependencies()`.

## CLI command

Scaffold a migration with the generator. `ooneex migration:create` writes a timestamped migration class under `modules/<module>/src/migrations/`, a matching test under `modules/<module>/tests/migrations/`, refreshes the `migrations.ts` barrel export, and creates the module's `bin/migration/up.ts` runner if it is missing.

```bash theme={null}
# Generate a migration in the shared module
ooneex migration:create

# Target a specific module
ooneex migration:create --module=auth
```

| Option     | Description                                    | Default  |
| ---------- | ---------------------------------------------- | -------- |
| `--module` | Target module the migration is generated into. | `shared` |

The migration file name is derived from a timestamp version automatically — there is no `--name` option. Capture the schema change when you implement `up()` and `down()`.

The generated class is a ready-to-fill stub:

```typescript theme={null}
import { decorator, type IMigration, type MigrationClassType } from "@ooneex/migrations";
import type { TransactionSQL } from "bun";

@decorator.migration()
export class Migration20240115103045 implements IMigration {
  public async up(tx: TransactionSQL): Promise<void> {
    // await tx`...`;
  }

  public async down(tx: TransactionSQL): Promise<void> {
    // await tx`...`;
  }

  public getVersion(): string {
    return "20240115103045";
  }

  public getDependencies(): MigrationClassType[] {
    return [];
  }
}
```

Apply pending migrations across every module with `ooneex migration:up`:

```bash theme={null}
# Run all pending migrations
ooneex migration:up

# DROP the schema first, then run every migration (destructive — dev only)
ooneex migration:up --drop
```

| Option   | Description                                                      | Default |
| -------- | ---------------------------------------------------------------- | ------- |
| `--drop` | Drop and recreate the `public` schema before running migrations. | `false` |

See [migration:create](/cli/commands/migration-create) and [migration:up](/cli/commands/migration-up) for the full command references.

## Use with Claude and Codex

The generator ships a matching `migration:create` skill. It runs the scaffold and then guides your AI agent through completing the migration — implementing `up()` with the schema change, `down()` with the reverse, and the indexes your queries need. Initialize the skills once for your agent:

<Tabs>
  <Tab title="Claude">
    ```bash theme={null}
    ooneex claude:init
    ```

    Then ask Claude in natural language — it maps the request to the generator, runs it, and fills in the implementation:

    ```text Prompt icon="terminal" wrap theme={null}
    Create a migration that adds an avatar column to the users table.
    ```
  </Tab>

  <Tab title="Codex">
    ```bash theme={null}
    ooneex codex:init
    ```

    Then ask Codex in natural language — it maps the request to the generator, runs it, and fills in the implementation:

    ```text Prompt icon="terminal" wrap theme={null}
    Create a migration that adds an avatar column to the users table.
    ```
  </Tab>
</Tabs>

For example, the prompt above maps to `migration:create`, then implements `up()` to add the `avatar` column and `down()` to drop it.