AssegaiPHP

Migrations are the repeatable history of schema change.

Without them, entity files and real databases drift apart quietly:

one developer adds a column locally
another environment never gets it
production still has the old shape
the code now assumes a schema that only exists on one machine

Migrations solve that by making schema change explicit, reviewable, and reversible.

Migration files

Use this layout:

migrations/
  <driver>/
    <data-source-name>/
      <timestamp>_<migration_name>/
        up.sql
        down.sql

Each migration lives in its own directory:

up.sql moves the schema forward
down.sql rolls the schema back

This keeps each migration easy to review and easy to roll back.

A concrete example

If you want a movies table, the migration could look like this.

up.sql

CREATE TABLE IF NOT EXISTS movies (
  id INTEGER PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  synopsis TEXT NULL,
  release_date DATE NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  deleted_at TIMESTAMP NULL
);

down.sql

DROP TABLE IF EXISTS movies;

The forward change and rollback live side by side in one migration directory.

Standalone workflow

If you are using assegaiphp/orm on its own, use the same migration shape.

Create a directory such as:

migrations/sqlite/local/20260412103000_create_movies_table/

Then place your SQL in:

migrations/sqlite/local/20260412103000_create_movies_table/up.sql
migrations/sqlite/local/20260412103000_create_movies_table/down.sql

A small one-off script can apply the SQL through your DataSource:

<?php

use Assegai\Orm\DataSource\DataSource;
use Assegai\Orm\DataSource\DataSourceOptions;
use Assegai\Orm\Enumerations\DataSourceType;

$dataSource = new DataSource(new DataSourceOptions(
  entities: [],
  name: 'local',
  type: DataSourceType::SQLITE,
  path: __DIR__ . '/storage/local.sqlite',
));

$upSql = file_get_contents(
  __DIR__ . '/migrations/sqlite/local/20260412103000_create_movies_table/up.sql'
);

$dataSource->getClient()->exec($upSql ?: '');

For a real project, you will usually want a small runner that:

finds migration directories in timestamp order
executes each up.sql
records which migrations have already run
can later read down.sql for rollback

Use the same layout whether the project is a full Assegai app or a plain PHP script.

Assegai workflow

Inside an Assegai app, the CLI is the preferred way to work with migrations for:

MySQL
MariaDB
PostgreSQL
SQLite
MSSQL

Create the files with the CLI

Use migration:create instead of creating the directory and files by hand:

assegai migration:create create_movies_table --pgsql --database=cinema

This creates the timestamped migration directory and both SQL files for you:

up.sql
down.sql

That saves time and avoids small mistakes in driver folders, database names, timestamps, and file names.

Typical command flow

assegai database:configure cinema --pgsql
assegai database:setup cinema --pgsql
assegai migration:setup cinema --pgsql
assegai migration:create create_movies_table --pgsql --database=cinema
assegai migration:up cinema --pgsql

What each command is responsible for:

database:configure writes the connection details into app config
database:setup creates the database if needed and the driver permits it
migration:setup prepares the migration workspace
migration:create creates a migration directory with up.sql and down.sql
migration:up runs pending migrations

Useful related commands:

assegai migration:down
assegai migration:redo
assegai migration:refresh
assegai migration:list

The healthy workflow

The safest rhythm is usually:

change the entity model
create a migration that matches the model change
inside an Assegai app, use assegai migration:create when your driver is supported; otherwise create the files by hand
write the SQL in up.sql and down.sql
run the migration locally
verify the feature through the repository or service layer
commit the entity and migration together

That keeps code and schema moving as one unit instead of drifting apart.

When to use migrations and when not to

Use migrations for schema evolution:

creating new tables
adding or dropping columns
renaming tables
changing nullability
adjusting relation tables and foreign keys

Use database:setup or first-run bootstrap for initial environment preparation.

Once the app has real shared environments, schema changes should move through migrations by default.

Manual workflow

You can still write migrations by hand.

That means:

handwritten SQL migrations remain fully supported
generated migrations are meant to help, not replace direct SQL when you need it

Multi-statement migration scripts

up.sql and down.sql can contain more than one SQL statement. That is useful when a migration needs to create a table, add indexes, and seed reference rows together.

For example:

CREATE TABLE IF NOT EXISTS genres (
  id INTEGER PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE
);

INSERT INTO genres (id, name) VALUES
  (1, 'Drama'),
  (2, 'Comedy');

The CLI migration runners execute the whole script and drain any result sets left by statements that return rows before recording the migration as applied.

SQLite gets one extra safety layer: transaction-safe scripts are wrapped so the SQL changes and the migration-table record commit together. If a later statement fails, earlier statements are rolled back and the migration stays pending.

Some SQLite statements cannot run inside an explicit transaction. Scripts that contain VACUUM, autocommit-only PRAGMA statements, or their own BEGIN ... COMMIT block run without an outer framework transaction. If a SQLite script opens its own transaction, make sure it also closes it with COMMIT or ROLLBACK.

Practical advice

Give migrations descriptive names such as create_movies_table or add_genre_id_to_showtimes.
Prefer small reversible changes over one giant migration that does everything at once.
Keep relation changes and their schema changes together.
Check migration:list when you want a quick sanity check on state.

Next steps

Once migrations feel clear, deepen the data layer with: