Hazaar DBI Schema Management

Overview

The Hazaar DBI Schema Manager provides a powerful mechanism to version your database schema and automate the rollout of changes. It simplifies database schema management by treating your database structure as code, allowing you to track changes, rollback updates, and ensure consistency across different environments (development, testing, production).

Key features include:

• Schema Versioning: Tracks every change to your database schema, allowing you to move forward or backward in time.
• Automated Rollouts: Apply schema changes automatically without writing manual SQL scripts.
• Snapshotting: Capture the current state of your database schema into migration files.
• Data Pre-population: Seed your database with initial data required for your application to run.
• Checkpointing: Merge multiple migration files into a single baseline to keep your project clean and performant.

Configuration

The Schema Manager is configured via your application's dbi configuration. This is typically found in your application.json, app.php, or app.ini file, depending on your setup.

PHP (Recommended)

<?php
return [
    'dbi' => [
        'default' => [
            'driver' => 'pgsql',
            'host' => 'localhost',
            'dbname' => 'myapp_db',
            'user' => 'dbuser',
            'password' => 'dbpassword',
            'manager' => [
                'schema' => 'public',
                'user' => 'dbadmin',
                'password' => 'adminpassword',
            ],
        ],
    ],
];

JSON

{
    "dbi": {
        "default": {
            "driver": "pgsql",
            "host": "localhost",
            "dbname": "myapp_db",
            "user": "dbuser",
            "password": "dbpassword",
            "manager": {
                "schema": "public",
                "user": "dbadmin",
                "password": "adminpassword"
            }
        }
    }
}

INI (Legacy)

[dbi.default]
driver = "pgsql"
host = "localhost"
dbname = "myapp_db"
user = "dbuser"
password = "dbpassword"

[dbi.default.manager]
schema = "public"
user = "dbadmin"
password = "adminpassword"

The manager key allows you to specify settings specific to the Schema Manager, such as the default schema to manage. You can also override connection details like user and password if you want to use a privileged account for schema changes while keeping the application user restricted.

Automatic Database Creation

If you want dbitool migrate to automatically create the target database when it does not yet exist, add createDatabase to the manager section:

PHP (Recommended)

<?php
return [
    'dbi' => [
        'default' => [
            'driver' => 'pgsql',
            'host' => 'localhost',
            'dbname' => 'myapp_db',
            'user' => 'dbuser',
            'password' => 'dbpassword',
            'manager' => [
                'createDatabase' => true,
                'maintenanceDatabase' => 'postgres',
                'user' => 'dbadmin',
                'password' => 'adminpassword',
            ],
        ],
    ],
];

JSON

{
    "dbi": {
        "default": {
            "driver": "pgsql",
            "host": "localhost",
            "dbname": "myapp_db",
            "user": "dbuser",
            "password": "dbpassword",
            "manager": {
                "createDatabase": true,
                "maintenanceDatabase": "postgres",
                "user": "dbadmin",
                "password": "adminpassword"
            }
        }
    }
}

• createDatabase — when set to true, the Schema Manager will attempt to create the target database if it does not exist when running dbitool migrate.
• maintenanceDatabase — the name of an existing database to connect to when issuing the CREATE DATABASE command. Defaults to the manager.user value if not specified. For PostgreSQL this is typically postgres.
• user / password — the manager credentials must have the CREATEDB privilege (or be a superuser) for automatic database creation to succeed.

Usage with dbitool

The primary way to interact with the Schema Manager is using the dbitool CLI utility.

Snapshotting

Snapshotting captures the current state of your database schema and creates a new migration file. This is useful when you have made changes directly to the database (e.g., usually during development) and want to save them as a discrete version.

To create a snapshot:

dbitool snapshot "Added users table"

This command will:

1. Inspect your current database schema.
2. Compare it against the last known version.
3. Generate a new migration file in your project's db/migrate directory containing the differences.

Migrations

Migrations are the heart of the Schema Manager. They allow you to apply pending changes or revert applied ones.

Apply Migrations: To bring your database up to the latest version:

dbitool migrate

To migrate to a specific version:

dbitool migrate --version=1234567890

Rollback: To undo the last migration:

dbitool rollback

Status: To see the current status of your migrations (which ones are applied vs. pending):

dbitool status

Data Synchronization

The Schema Manager also supports synchronizing data, which is useful for seeding lookups, configuration tables, or initial user accounts.

For detailed information on how to configure and use data synchronization, please refer to the Hazaar DBI Data Sync documentation.

Checkpointing

Over time, your project may accumulate a large number of migration files. Checkpointing allows you to consolidate these into a single "baseline" migration. This is helpful for cleaning up your project and speeding up new deployments, as the database only needs to process one large migration instead of hundreds of small ones.

To create a checkpoint:

dbitool checkpoint "Baseline for v2.0"

This will:

1. Take a full snapshot of the current schema.
2. Delete existing migration files.
3. Create a single new migration file representing the entire schema at that point in time.
4. Update the internal tracking table to reflect this new baseline.

Programmatic Usage

You can also use the Schema Manager directly within your PHP application. This is useful for self-updating applications, installers, or test suites.

use Hazaar\DBI\Manager;

// 1. Initialize the manager with your DBI config
$config = [
    'driver' => 'pgsql',
    'host' => 'localhost',
    'dbname' => 'myapp_db',
    // ... credentials
];

$manager = new Manager($config);

// 2. Check for updates
if ($manager->hasUpdates()) {
    echo "Database updates are available.\n";
    
    // 3. Run migrations
    if ($manager->migrate()) {
        echo "Database migrated successfully.\n";
    } else {
        echo "Migration failed.\n";
    }
}

// 4. Get the current version
$version = $manager->getCurrentVersion();
echo "Current Schema Version: " . ($version['number'] ?? 'None');

The Hazaar\DBI\Manager class provides methods for all major operations:

• migrate(?int $version): Apply or rollback migrations.
• snapshot(?string $comment): Create a new schema version from current DB state.
• checkpoint(?string $comment): Consolidate migrations.
• getMissingVersions(): List pending migrations.
• rollback(int $version): Rollback a specific version.

Manual Migration Files

While snapshotting is convenient, manual migration files offer finer control, especially during development. You can create a migration file (JSON format) in your db/migrate directory.

A migration file structure looks like this:

{
    "number": 1234567890,
    "comment": "Create users table",
    "up": [
        {
            "action": "create",
            "type": "table",
            "spec": {
                "name": "users",
                "columns": [
                    {
                        "name": "id",
                        "type": "serial",
                        "primarykey": true
                    },
                    {
                        "name": "username",
                        "type": "varchar",
                        "length": 255,
                        "not_null": true
                    }
                ]
            }
        }
    ],
    "down": [
        {
            "action": "drop",
            "type": "table",
            "spec": [ "users" ]
        }
    ]
}

• number: A unique integer timestamp (usually YYYYMMDDHHMMSS).
• up: An array of actions to apply the change.
• down: An array of actions to revert the change.

Each migration file consists of two main sections: up and down. These sections contain an array of actions that defining the changes to be applied or reverted. When migrating forward (up), the actions in the up section are executed in order. When rolling back (down), the actions in the down section are executed.

Actions

Each item in the up or down array represents a single action. An action typically consists of:

• action: The operation to perform. Common values are:
  • create: Create a new database object.
  • alter: Modify an existing object.
  • drop: Remove an object.
• type: The type of database object being manipulated. Supported types are:
  • extension
  • type (custom types — ENUM and composite, see Custom Types below)
  • table
  • view
  • function
  • trigger
  • index
  • constraint
• spec: A specification object or array detailing the object definition. For a table creation, this includes the table name and column definitions. For a drop action, it might just be the name of the object.

Using manual files allows you to leverage DBI's schema builder features directly, giving you precise control over column types, constraints, and database-specific features.

Custom Types (ENUM and Composite)

type actions manage custom PostgreSQL types — ENUMs and composite (struct-like) types. A spec for a type action has a kind of enum or composite, plus values (for enum) or fields (for composite):

{
    "action": "create",
    "type": "type",
    "spec": {
        "name": "mood",
        "kind": "enum",
        "values": ["sad", "ok", "happy"]
    }
}

{
    "action": "create",
    "type": "type",
    "spec": {
        "name": "point",
        "kind": "composite",
        "fields": [
            { "name": "x", "type": "integer" },
            { "name": "y", "type": "integer" }
        ]
    }
}

Once a custom type exists, a column can reference it by name like any built-in type (e.g. "type": "mood") — no special column syntax is needed.

Altering enum values / composite fields: PostgreSQL can only append new enum values or composite fields in place (ALTER TYPE ... ADD VALUE / ADD ATTRIBUTE). When snapshotting, appending a value/field to the end of the list produces an alter action containing just the new entries. Removing, reordering, renaming, or retyping an existing value/field can't be expressed as a simple ALTER, so the snapshot instead raises a warning and emits a drop followed by a create to recreate the type from scratch. Because PostgreSQL CASCADEs that drop to everything using the type (columns, tables, functions, etc.), always review an auto-generated migration that recreates a type before applying it — especially in production.

Custom types have no equivalent in SQLite, so type actions are PostgreSQL-only.

Executing Raw SQL

When the structured actions above aren't expressive enough — for example a data back-fill, a database-specific statement, or ad-hoc DDL — you can run raw SQL directly using the exec action. Instead of the usual action/type/spec keys, an exec action is a single object with an exec key:

{
    "number": 1234567890,
    "comment": "Backfill user status",
    "up": [
        {
            "exec": "UPDATE users SET status = 'active' WHERE status IS NULL"
        }
    ],
    "down": [
        {
            "exec": "UPDATE users SET status = NULL WHERE status = 'active'"
        }
    ]
}

The exec value may be a single SQL statement string, or an array of statements that are executed sequentially in the order given:

{
    "exec": [
        "CREATE INDEX CONCURRENTLY idx_users_status ON users (status)",
        "ANALYZE users"
    ]
}

Execution stops at the first statement that fails, causing the migration to fail and roll back. Because raw SQL is opaque to the schema manager, exec actions are never generated by snapshots and cannot be diffed — they are purely author-written steps. Remember to provide a matching down action to reverse the change, as one cannot be inferred automatically.

Externalising Function, Trigger and View Bodies

function, trigger and view actions can define their SQL body inline in the migration JSON via the spec.body field. For anything non-trivial, embedding a large escaped SQL string in JSON is awkward, so the body may instead be kept in a separate .sql file that sits alongside the migration file. When an action of these types has no inline body, the Schema Manager loads it from disk using this layout (relative to the directory containing the migration .json):

db/migrate/
├── 20260101120000_Add_audit_trigger.json
└── triggers/
    └── 20260101120000/
        └── audit_row.sql

The directory is named for the object type (functions, triggers, or views), then the migration version number, then {object_name}.sql. The file's contents are loaded into spec.body at migration time.

To allow the up and down migrations within the same version to define different bodies for the same object — for example replacing a trigger's body on the way up and restoring the previous body on the way down — a direction-specific file takes precedence over the shared file:

triggers/
└── 20260101120000/
    ├── audit_row.up.sql     # used by the up migration
    └── audit_row.down.sql   # used by the down migration

Resolution order for each direction is:

1. {object_name}.{up|down}.sql — the direction-specific body, if present.
2. {object_name}.sql — the shared body, used as a fallback.

This means existing migrations that use a single shared {object_name}.sql file continue to work unchanged; you only need the .up.sql / .down.sql split when the two directions genuinely differ. If neither file exists for an action that requires a body, the migration fails with an error naming both candidate paths.

Advanced dbitool Commands for Development

During development, you often need to iterate on schema changes. dbitool provides specific commands to help with this workflow:

Rollback

If you apply a migration and realize it has an error or isn't quite right, you can roll it back.

dbitool rollback

This commands undoes the last applied migration. You can essentially "undo" your last step, fix your migration file, and then run migrate again.

Replay

The replay command is a shortcut for rolling back and immediately re-applying a specific version. This is incredibly useful when you are tweaking a specific migration file and want to test your changes instantly.

dbitool replay --version=1234567890

This will:

1. Rollback version 1234567890.
2. Generally, immediately re-apply version 1234567890.

This rapid feedback loop saves you from manually running rollback then migrate repeatedly.