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.

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
  • 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.

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.