Migrations & Schema

Migrations are versioned changes to your database schema. Fast runs them synchronously from the command line, completely outside the event loop, so they are plain PHP scripts you invoke directly. There is no server involved.

Running migrations

Migrations run through a migrate.php runner using a DSN from the environment:

DB_DSN='sqlite:/path/app.sqlite' php migrate.php status
DB_DSN='sqlite:/path/app.sqlite' php migrate.php up
DB_DSN='sqlite:/path/app.sqlite' php migrate.php down

status shows which migrations have run, up applies pending ones, and down rolls the last batch back. By default Fast looks for migration files under database/migrations; point it elsewhere with the DB_MIGRATIONS environment variable.

Writing a migration

A migration file returns an array with up and down keys. Each is a list of SQL statements to run. You can write raw SQL, or use the schema builder to generate it, and you can mix both in the same list:

use Fast\Database\Schema;
use Fast\Database\Blueprint;

return [
	'up' => [
		Schema::create('users', function (Blueprint $t): void {
			$t->id();
			$t->text('name');
			$t->text('email')->nullable();
			$t->boolean('active')->default(true);
			$t->json('tags')->default('[]');
			$t->datetime('created')->nullable();
		}),
	],
	'down' => [
		Schema::drop('users'),
	],
];

The whole up list runs inside a single transaction, so a migration either fully applies or not at all.

The schema builder

Schema has a static method for each kind of change, and each returns SQL that drops straight into your migration list:

Schema::create('posts', fn (Blueprint $t) => /* ... */);
Schema::createIfNotExists('posts', fn (Blueprint $t) => /* ... */);
Schema::table('posts', fn (Blueprint $t) => /* ... */);   // alter an existing table
Schema::drop('posts');
Schema::dropIfExists('posts');
Schema::rename('posts', 'articles');

Defining columns

Inside the Blueprint closure, each method adds a column of a given type:

Schema::create('posts', function (Blueprint $t): void {
	$t->id();                    // auto-incrementing primary key
	$t->text('title');
	$t->integer('views');
	$t->real('rating');
	$t->boolean('published');
	$t->json('meta');
	$t->datetime('created');
	$t->timestamps();            // created + updated timestamps
});

Column definitions are chainable, so you can layer on modifiers:

$t->text('email')->nullable()->unique();
$t->integer('author_id')->references('users', 'id')->onDelete('cascade');
$t->text('slug')->default('untitled')->index();

The available modifiers include nullable(), default(), unique(), primary(), autoincrement(), index(), references(), onDelete(), and onUpdate(), plus raw() for a literal SQL expression when you need one.

Indexes and altering tables

You can add indexes explicitly, and inside a Schema::table() call you can rename or drop columns:

Schema::create('events', function (Blueprint $t): void {
	$t->id();
	$t->text('kind');
	$t->datetime('at');
	$t->index(['kind', 'at']);
	$t->unique('kind');
});

Schema::table('events', function (Blueprint $t): void {
	$t->renameColumn('kind', 'type');
	$t->dropColumn('at');
});

Testing migrations

Because migrations are just SQL against a real database, test them against real SQLite rather than a fake. This catches genuine schema and constraint issues that a mock would happily let through. There is more on this in the Testing section.

For very large reads, the final database topic is Streaming Results.