DALT.PHP
Framework Deep Dive3. Container & DB

Migrations

How database schema is created and versioned with migrations

Building the Database Structure

Migrations are like version control for your database schema:

  • Create tables
  • Add columns
  • Modify structure
  • Track what's been run

Think of it like building instructions:

  • Step 1: Create users table
  • Step 2: Create posts table
  • Step 3: Add email_verified column to users

Each step is a migration file.

What is a Migration?

A migration is a SQL file that creates or modifies database structure:

-- database/migrations/001_create_users_table.sql

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Key points:

  • Plain SQL (no abstraction)
  • Numbered for order (001, 002, 003)
  • Descriptive name
  • Idempotent (safe to run multiple times)

The Migration Class (Real Behavior)

DALT’s migration system is intentionally “raw SQL first”, but it supports both SQLite and PostgreSQL.

Two important behaviors to know:

  1. The migration system creates its own migrations tracking table (SQLite or PostgreSQL syntax depending on the driver).
  2. When DB_DRIVER=pgsql but a migration file contains obvious SQLite-only syntax (like AUTOINCREMENT), DALT tries a small automatic conversion for the common cases.

Driver detection

The migration runner checks which PDO driver is active:

private function driver(): string
{
    return (string) $this->database->getConnection()->getAttribute(PDO::ATTR_DRIVER_NAME);
}

The migrations tracking table

The tracking table is created with driver-specific SQL:

public function createMigrationsTable()
{
    $driver = $this->driver();

    if ($driver === 'sqlite') {
        $sql = "CREATE TABLE IF NOT EXISTS migrations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            migration VARCHAR(255) NOT NULL,
            batch INTEGER NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )";
    } elseif ($driver === 'pgsql') {
        $sql = "CREATE TABLE IF NOT EXISTS migrations (
            id BIGSERIAL PRIMARY KEY,
            migration VARCHAR(255) NOT NULL,
            batch INTEGER NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )";
    } else {
        throw new \RuntimeException(\"Unsupported database driver: {$driver}\");
    }

    $this->database->getConnection()->exec($sql);
}

What this stores:

idmigrationbatchcreated_at
1001_create_users_table.sql12024-01-15 10:30:00
2002_create_posts_table.sql12024-01-15 10:30:01
3003_add_email_verified.sql22024-01-16 14:20:00

Why track migrations?

  • Know what's been run
  • Don't run the same migration twice
  • Support rollback (batch numbers)
  • Audit trail

The Migration Flow

Step 1: Find Migration Files

$migrationsPath = base_path('database/migrations');
$files = glob($migrationsPath . '/*.sql');
sort($files);

What glob() does:

  • Finds all .sql files in the directory
  • Returns array of full paths

Why sort()?

  • Ensures migrations run in order
  • 001_... before 002_... before 003_...

Step 2: Get Next Batch Number

public function getNextBatch()
{
    $result = $this->database->query(
        'SELECT MAX(batch) as max_batch FROM migrations'
    )->find();
    
    return ($result['max_batch'] ?? 0) + 1;
}

What is a batch?

  • A group of migrations run together
  • Useful for rollback (undo a batch)

Example:

First run:  batch = 1
Second run: batch = 2
Third run:  batch = 3

Step 3: Check if Already Run

public function hasRun($migration)
{
    $result = $this->database->query(
        'SELECT migration FROM migrations WHERE migration = ?',
        [$migration]
    )->find();
    
    return $result !== false;
}

Returns:

  • true if migration exists in tracking table
  • false if not run yet

Step 4: Run the Migration

$sql = file_get_contents($file);

try {
    $this->database->getConnection()->exec($sql);
    $this->markAsRun($migration, $batch);
    $ranMigrations++;
    echo "✓ Success\n";
} catch (\PDOException $e) {
    throw new \RuntimeException(
        "Migration failed.\n" .
        "File: {$migration}\n" .
        "Error: " . $e->getMessage() . "\n",
        previous: $e
    );
}

What exec() does:

  • Executes SQL directly
  • No prepared statements needed (no user input)
  • Returns number of affected rows

Why stop on error?

  • Stop running migrations if one fails
  • Don't leave database in inconsistent state
  • Fix the error before continuing

Step 5: Mark as Run

public function markAsRun($migration, $batch)
{
    $this->database->query(
        'INSERT INTO migrations (migration, batch) VALUES (?, ?)',
        [$migration, $batch]
    );
}

Adds a row to the tracking table.

Creating a Migration

Manual Creation

touch database/migrations/002_create_posts_table.sql

Naming convention:

  • Number prefix (001, 002, 003)
  • Descriptive name
  • .sql extension

Migration Content

-- database/migrations/002_create_posts_table.sql

CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    published BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published);

Best practices:

  • Use IF NOT EXISTS (idempotent)
  • Add indexes for foreign keys
  • Add indexes for frequently queried columns
  • Include timestamps

Running Migrations

Via CLI

php artisan migrate

Output:

Running migration: 001_create_users_table.sql
✓ Success
Running migration: 002_create_posts_table.sql
✓ Success

Ran 2 migrations.

Automatic on First Run

Remember from Part 3.2, DatabaseManager checks if tables exist:

private function ensureTablesExist()
{
    try {
        $this->database->query("SELECT 1 FROM users LIMIT 1");
    } catch (\Exception $e) {
        $this->runMigrations();
    }
}

First time you run the app:

  1. Try to query users table
  2. Fails (table doesn't exist)
  3. Automatically runs migrations
  4. Creates all tables

Subsequent runs:

  1. Query succeeds
  2. Skip migrations

SQLite vs PostgreSQL Differences

SQLite Syntax

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE
);

PostgreSQL Syntax

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

Key differences:

  • INTEGER vs SERIAL
  • AUTOINCREMENT vs auto-increment by default
  • TEXT vs VARCHAR(255)

DALT's Solution

The Migration class has conversion logic:

private function convertSqliteToPostgres($sql)
{
    $sql = str_replace('INTEGER PRIMARY KEY AUTOINCREMENT', 'SERIAL PRIMARY KEY', $sql);
    $sql = str_replace('TEXT', 'VARCHAR(255)', $sql);
    $sql = str_replace('DATETIME', 'TIMESTAMP', $sql);
    $sql = str_replace('BOOLEAN', 'BOOLEAN', $sql);
    
    return $sql;
}

When it's used:

if ($driver === 'pgsql') {
    $sql = $this->convertSqliteToPostgres($sql);
}

Limitations:

  • Simple string replacement
  • Won't handle complex SQL
  • Better to write driver-specific migrations for production

Migration Patterns

Creating a Table

CREATE TABLE IF NOT EXISTS table_name (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    column1 TEXT NOT NULL,
    column2 INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Adding a Column

-- SQLite doesn't support ALTER TABLE ADD COLUMN with constraints
-- So we need to be careful

ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT 0;

Creating an Index

CREATE INDEX IF NOT EXISTS idx_posts_user_id ON posts(user_id);

Adding Foreign Key

-- In the CREATE TABLE statement
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

What ON DELETE CASCADE means:

  • If a user is deleted
  • All their posts are automatically deleted
  • Maintains referential integrity

Common Issues

Issue 1: Migration Already Run

Problem:

Running migration: 001_create_users_table.sql
✗ Failed: table users already exists

Solution:

  • Migration was already run
  • Check migrations table
  • Don't run it again

Issue 2: Syntax Error

Problem:

Running migration: 002_create_posts_table.sql
✗ Failed: near "AUTOINCREMENT": syntax error

Solution:

  • Check SQL syntax
  • Verify it works in your database
  • Test in SQLite/PostgreSQL CLI first

Issue 3: Dependency Order

Problem:

Running migration: 002_create_posts_table.sql
✗ Failed: foreign key constraint failed

Solution:

  • posts references users
  • users must be created first
  • Check migration numbering

Issue 4: Can't Rollback

Problem:

  • Ran a bad migration
  • Want to undo it

Solution:

  • DALT doesn't have rollback (yet)
  • Manually drop tables or write a "down" migration
  • For production, you'd want: 
    public function rollback($steps = 1) {
    // Undo last $steps batches
}

Best Practices

1. Always Use IF NOT EXISTS

CREATE TABLE IF NOT EXISTS users (...);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

Makes migrations idempotent (safe to run multiple times).

2. Number Migrations

001_create_users_table.sql
002_create_posts_table.sql
003_create_comments_table.sql

Ensures correct order.

3. One Logical Change Per Migration

Good:

001_create_users_table.sql
002_add_email_verified_to_users.sql

Bad:

001_create_everything.sql  (creates 10 tables)

4. Test Before Committing

# Test in SQLite
sqlite3 test.db < database/migrations/001_create_users_table.sql

# Test in PostgreSQL
psql -d testdb -f database/migrations/001_create_users_table.sql

5. Never Modify Existing Migrations

Once a migration is run in production, don't change it.

Instead:

  • Create a new migration
  • Example: 003_add_column_to_users.sql

Key Takeaways

  1. Migrations are SQL files - Plain SQL, no abstraction
  2. Tracking table prevents duplicates - Knows what's been run
  3. Batch numbers enable rollback - Group migrations together
  4. Order matters - Dependencies must run first
  5. Idempotent is safe - Use IF NOT EXISTS

What's Good Here

✅ Simple implementation (just SQL files)
✅ No abstraction (learn real SQL)
✅ Automatic on first run (convenient)
✅ Batch tracking (supports rollback)
✅ Forces you to write real SQL migrations

Design Note

Rollback functionality and migration generators are intentionally omitted. DALT keeps migrations simple - just SQL files that run once. This teaches you database schema management without the complexity of up/down migrations. For production, use Laravel's migrations which handle all of this.

Section 3 Complete!

You now understand:

  • How the dependency injection container works
  • How database connections are created
  • How queries are executed securely
  • How migrations manage schema changes

Next, explore sessions and flash data.

  • "Down" SQL in each migration
  • More sophisticated tracking
  • Code generation

Part 3 Complete!

You now understand the container and database:

  • How dependency injection works
  • How the container manages services
  • How database connections are created
  • How queries are executed safely
  • How migrations version your schema

Query Execution

How SQL queries are executed safely with prepared statements

Session Helper Class

How the Session class wraps PHP's $_SESSION for cleaner code

On this page

Building the Database StructureWhat is a Migration?The Migration Class (Real Behavior)Driver detectionThe migrations tracking tableThe Migration FlowStep 1: Find Migration FilesStep 2: Get Next Batch NumberStep 3: Check if Already RunStep 4: Run the MigrationStep 5: Mark as RunCreating a MigrationManual CreationMigration ContentRunning MigrationsVia CLIAutomatic on First RunSQLite vs PostgreSQL DifferencesSQLite SyntaxPostgreSQL SyntaxDALT's SolutionMigration PatternsCreating a TableAdding a ColumnCreating an IndexAdding Foreign KeyCommon IssuesIssue 1: Migration Already RunIssue 2: Syntax ErrorIssue 3: Dependency OrderIssue 4: Can't RollbackBest Practices1. Always Use IF NOT EXISTS2. Number Migrations3. One Logical Change Per Migration4. Test Before Committing5. Never Modify Existing MigrationsKey TakeawaysWhat's Good HereDesign NoteSection 3 Complete!Part 3 Complete!