Ctiso Migration

Database migration framework for PHP with schema builder, runner, and SQL compiler for SQLite, MySQL, and PostgreSQL.

Features

• Fluent schema builder — define tables, columns, indexes, and foreign keys in PHP
• Cross-database support — same migration code runs on SQLite, MySQL, or PostgreSQL
• Version tracking — automatic migration versioning with sub-version support (e.g. 108.5)
• Idempotent operations — addColumn, createIndex, addForeignKey, insertIgnore skip if already applied
• Rollback support — reverse migrations with down functions
• Detailed error reporting — failed SQL includes migration file, version, and estimated line number

Installation

composer require ctiso/migration

Requires PHP 8.2+ with ext-pdo.

Quick Start

1. Create migration files

Place migration files in a migrations/ directory. Each file follows the convention <version>_<label>.php and returns an array with up and down callables:

<?php
// migrations/001_create_users.php

use Ctiso\Migration\Schema;
use Ctiso\Migration\Column;

return [
    'up' => function (Schema $s) {
        $s->createTable('users', [
            Column::integer('id')->primaryKey()->autoIncrement(),
            Column::text('name')->notNull(),
            Column::text('email')->unique()->notNull(),
            Column::dateTime('created_at')->default('CURRENT_TIMESTAMP'),
        ]);
    },
    'down' => function (Schema $s) {
        $s->dropTable('users');
    },
];

2. Run migrations

use Ctiso\Migration\Migration;

$migration = new Migration(':memory:');  // SQLite in-memory
// $migration = new Migration('sqlite:/path/to/db.sqlite');
// $migration = new Migration('mysql:host=localhost;dbname=mydb', $existingPdo);

// Run all pending migrations
$applied = $migration->migrate();

// Run up to a specific version
$applied = $migration->migrate(5);

// Rollback to version 0 (full rollback)
$rolledBack = $migration->rollback(0);

// Rollback to version 2
$rolledBack = $migration->rollback(2);

3. Check status

$status = $migration->status();
// [
//   ['version' => 1.0, 'label' => 'create_users', 'applied' => true],
//   ['version' => 2.0, 'label' => 'create_posts', 'applied' => false],
// ]

Schema API

Column types

Factory	Description
Column::integer('name')	Integer column
Column::text('name')	Text/VARCHAR column
Column::dateTime('name')	DateTime/Timestamp column
Column::boolean('name')	Boolean column

Column modifiers (fluent)

Method	Description
->primaryKey()	Mark as primary key
->autoIncrement()	Auto-incrementing column
->notNull()	NOT NULL constraint
->nullable()	Allow NULL values
->default($value)	Default value
->unique()	UNIQUE constraint
->unsigned()	Unsigned integer (MySQL)
->length($len)	Column length (VARCHAR)
->comment($text)	Column comment (MySQL)
->references($table, $column)	Foreign key reference

Table operations

$s->createTable('users', [
    Column::integer('id')->primaryKey()->autoIncrement(),
    Column::text('name')->notNull(),
]);

$s->dropTable('users');

$s->addColumn('users', Column::text('bio'));
$s->dropColumn('users', 'bio');

// Composite primary key
$s->createTable('pivot', [
    Column::integer('id_course'),
    Column::integer('id_student'),
], ['primaryKey' => ['id_course', 'id_student']]);

Index operations

$s->createIndex('users', ['email'], 'idx_users_email');
$s->createIndex('users', ['slug'], 'idx_users_slug', true);  // unique index
$s->dropIndex('idx_users_email', 'users');

Foreign keys

$s->addForeignKey('posts', 'fk_posts_user', ['user_id'], 'users', ['id']);
$s->dropForeignKey('posts', 'fk_posts_user', ['user_id'], 'users', ['id']);

Data operations

$s->insert('users', ['name' => 'Alice', 'age' => 30]);
$s->insertIgnore('users', ['name' => 'Alice']);  // idempotent insert
$s->delete('users', ['id' => 1]);

Raw SQL

$s->raw('UPDATE users SET name = "test"');

Migration file naming

• 001_initial.php → version 1
• 108-5_fix.php → version 108.5 (sub-version, placed between 108 and 109)

Database drivers

Driver	DSN example	Version tracking
SQLite	sqlite:/path/to/db.sqlite or :memory:	PRAGMA user_version
MySQL	mysql:host=localhost;dbname=mydb	__migrations table
PostgreSQL	pgsql:host=localhost;dbname=mydb	__migrations table

Custom migrations directory

$migration = new Migration(':memory:', null, '/path/to/migrations');

// Or set globally
Migration::setDefaultMigrationsDir('/path/to/migrations');

Development

composer install
composer test        # Run PHPUnit tests
composer phpstan     # Run PHPStan level 7
composer ci          # Run both