Database & Migrations

Database & Migrations

StackCTL uses a straightforward file-based migration system. Each migration is a plain PHP file with an up function (to apply the change) and a down function (to roll it back). The Stack CLI tracks which migrations have run and ensures they only ever run once.

For querying the database inside controllers, see the Query doc. This doc covers the migration system specifically.

Migration Files

Migration files live in build/database/ and are numbered sequentially so they always run in the correct order.

build/database/
├── 001_create_users.php
├── 002_create_password_resets.php
├── 003_create_email_verifications.php
├── 004_create_mfa_methods.php
├── 005_create_mfa_challenges.php
├── 006_trusted_browsers.php
└── 007_create_articles.php   ← your migrations go here

The framework ships with migrations 001–006 for the built-in auth system. Your own migrations start from 007 onwards.

Creating a Migration

Use the Stack CLI to generate a new migration file with the correct number prefix:

php stack db:create create_articles

This creates build/database/007_create_articles.php with a ready-to-fill template:

<?php

return [

    'up' => function ($db) {

        $db->exec("
            CREATE TABLE articles (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

                -- Add your columns here

                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ");

    },

    'down' => function ($db) {

        $db->exec("DROP TABLE IF EXISTS articles");

    }

];

Fill in your columns in the up function, and make sure down cleanly reverses whatever up does.

A Real Migration Example

Here's a complete, realistic migration for an articles table with foreign keys and common column patterns:

<?php

return [

    'up' => function ($db) {

        $db->exec("
            CREATE TABLE articles (
                id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

                -- Core content
                title       VARCHAR(255) NOT NULL,
                slug        VARCHAR(255) NOT NULL UNIQUE,
                content     LONGTEXT NULL,
                status      ENUM('draft', 'published') DEFAULT 'draft',

                -- Ownership
                created_by  BIGINT UNSIGNED NULL,

                -- Timestamps
                created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

                -- Foreign key
                CONSTRAINT fk_articles_created_by
                    FOREIGN KEY (created_by)
                    REFERENCES users(id)
                    ON DELETE SET NULL

            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ");

    },

    'down' => function ($db) {

        $db->exec("DROP TABLE IF EXISTS articles");

    }

];

Adding Columns to Existing Tables

Never edit an already-applied migration to add columns — create a new migration instead. This keeps the history intact and ensures the change can be applied cleanly on other environments.

php stack db:create add_excerpt_to_articles

<?php

return [

    'up' => function ($db) {

        $db->exec("
            ALTER TABLE articles
            ADD COLUMN excerpt VARCHAR(500) NULL AFTER title;
        ");

    },

    'down' => function ($db) {

        $db->exec("
            ALTER TABLE articles
            DROP COLUMN excerpt;
        ");

    }

];

The Migration Workflow

db:apply — Run pending migrations

Runs all migration files that haven't been applied yet, in sequential order. Safe to run multiple times — already-applied migrations are tracked in the schema_migrations table and skipped.

php stack db:apply

db:status — Check what's been run

Displays every migration file and whether it has been applied (✔) or is still pending (○).

php stack db:status

// Output:
// [✔] 001_create_users.php
// [✔] 002_create_password_resets.php
// [○] 007_create_articles.php

db:rollback — Undo a specific migration

Runs the down function of a specific migration and removes it from the applied log. Useful during development when you need to tweak a table you just created.

php stack db:rollback 007_create_articles.php

After rolling back, edit the migration file and run db:apply again to re-apply it.

db:describe — Inspect a table

Prints the column structure of any table — a quick way to check a table without opening a database client.

php stack db:describe articles

// Output:
// Field        Type          Null  Key    Default
// id           bigint(20)    NO    PRI
// title        varchar(255)  NO
// slug         varchar(255)  NO    UNI
// status       enum(...)     YES          draft
// created_at   datetime      YES          CURRENT_TIMESTAMP

How Migrations Are Tracked

StackCTL creates a schema_migrations table in your database the first time db:apply runs. Each applied migration's filename is recorded there with a timestamp. You never need to interact with this table directly.

Best Practices

• Always write a down function — Even a simple DROP TABLE. It makes rollbacks possible during development.
• Never edit an applied migration — Create a new one instead. Editing an applied migration won't re-run it and will cause your migration history to drift out of sync.
• Keep migrations small and focused — One table or one change per file. It's easier to roll back a targeted change than a large one.
• Use utf8mb4 — All tables should use ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 for full Unicode support including emoji.
• Run db:apply after pulling changes — Get into the habit of running migrations whenever you pull new code from a teammate.