ForgeDatabaseSql

SQL database support for Forge Kernel. Multi-database support (SQLite, MySQL, PostgreSQL), flexible migrations (attribute-based and raw SQL), seeders with auto-rollback, and comprehensive connection management.

Overview

ForgeDatabaseSql provides comprehensive SQL database support for Forge Kernel applications. It offers multi-database driver support, flexible migration systems, seeders, and direct database access through a clean PDO-based abstraction.

Key Features

Multi-database support (SQLite, MySQL, PostgreSQL)
Attribute-based migrations
Raw SQL migrations
Migration scoping (app, engine, module)
Migration grouping
Seeders with auto-rollback
Schema formatters for cross-database compatibility
Full transaction support

What ForgeDatabaseSql Provides

  • Multi-Database Support: SQLite, MySQL, and PostgreSQL with automatic SQL formatting
  • Flexible Migrations: Both attribute-based and raw SQL migration approaches
  • Migration Scoping: Organize migrations by app, engine, or module
  • Migration Grouping: Group migrations by feature or domain
  • Seeders: Database seeding with automatic rollback support
  • Schema Formatters: Database-specific SQL generation for cross-database compatibility
  • Connection Management: PDO-based abstraction with transaction support
  • Direct Database Access: Full access to PDO methods for custom queries

Core Module: ForgeDatabaseSql is a core database module (type: 'database', order: 0), providing essential database functionality for Forge Kernel applications.

Architecture & Design Philosophy

ForgeDatabaseSql is built with flexibility, cross-database compatibility, and developer experience in mind.

PDO-Based Connection Abstraction

ForgeDatabaseSql uses PDO as the foundation:

  • PDO provides consistent interface across database drivers
  • Connection class wraps PDO with Forge-specific features
  • Implements DatabaseConnectionInterface for dependency injection
  • Full transaction support with beginTransaction(), commit(), rollBack()

Database Driver Abstraction

DatabaseDriverInterface provides driver abstraction:

  • PdoDatabaseDriver implements driver-specific DSN generation
  • Automatic driver detection from connection
  • Driver-specific SQL formatting via schema formatters

Schema Formatters for Cross-Database Compatibility

Schema formatters ensure SQL compatibility:

  • MySqlFormatter for MySQL-specific SQL
  • PostgreSqlFormatter for PostgreSQL-specific SQL
  • SqliteFormatter for SQLite-specific SQL
  • Automatic formatter selection based on driver
  • Column type mapping for cross-database compatibility

Flexible Migration System

Two migration approaches for different needs:

  • Attribute-Based: Use PHP 8 attributes to define schema, automatic SQL generation
  • Raw SQL: Full control with createTable(), dropTable(), execute() methods
  • Both approaches support migration scoping and grouping

Installation

ForgeDatabaseSql is a core module and is typically available by default. It can also be installed via ForgePackageManager.

Using ForgePackageManager

# Install with wizard (interactive)
php forge.php package:install-module

# Install directly (skip wizard)
php forge.php package:install-module --module=ForgeDatabaseSql

# Install specific version
php forge.php package:install-module --module=ForgeDatabaseSql@0.2.1

Database Configuration

Configure your database connection in your environment configuration: 

// Database configuration
'database' => [
    'driver' => 'sqlite', // or 'mysql', 'pgsql'
    'host' => 'localhost',
    'Database' => 'forge_app', // or file path for SQLite
    'username' => 'root',
    'password' => '',
]

Connection Setup

The module automatically sets up the database connection via DatabaseSetup: 

public function register(Container $container): void
{
    $env = Environment::getInstance();
    DatabaseSetup::setup($container, $env);
}

Database Drivers

ForgeDatabaseSql supports three database drivers, each with specific features and use cases.

SQLite

File-based database, perfect for development and small applications:

  • No server required — single file database
  • Perfect for development and testing
  • Foreign keys enabled by default (PRAGMA foreign_keys = ON)
  • Busy timeout configured (2000ms)
  • Limited ALTER TABLE support (foreign keys handled differently)
'database' => [
    'driver' => 'sqlite',
    'Database' => BASE_PATH . '/storage/database.sqlite',
]

MySQL

Production-ready database with InnoDB engine:

  • InnoDB engine with utf8mb4 charset
  • Full foreign key support
  • Production-ready for high-traffic applications
  • Auto-increment support
'database' => [
    'driver' => 'mysql',
    'host' => 'localhost',
    'Database' => 'forge_app',
    'username' => 'root',
    'password' => 'password',
]

PostgreSQL

Advanced database with JSON support and advanced features:

  • Advanced JSON support
  • SERIAL types for auto-increment (instead of AUTO_INCREMENT)
  • Full foreign key support
  • Advanced data types and features
'database' => [
    'driver' => 'pgsql',
    'host' => 'localhost',
    'Database' => 'forge_app',
    'username' => 'postgres',
    'password' => 'password',
]

Driver Detection

The driver is automatically detected from the connection: 

$driver = $connection->getDriver(); // Returns: 'sqlite', 'mysql', or 'pgsql'

Connection Management

ForgeDatabaseSql provides a PDO-based connection abstraction through DatabaseConnectionInterface.

Connection Methods

// Execute DDL statements (CREATE, DROP, ALTER)
$connection->exec("CREATE TABLE users (id INTEGER PRIMARY KEY)");

// Execute SELECT queries
$stmt = $connection->query("SELECT * FROM users LIMIT 10");
$results = $stmt->fetchAll();

// Prepare parameterized queries
$stmt = $connection->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([':id' => 1]);
$user = $stmt->fetch();

// Get underlying PDO instance
$pdo = $connection->getPdo();

// Get database driver
$driver = $connection->getDriver(); // 'sqlite', 'mysql', or 'pgsql'

Transaction Support

Full transaction support for data integrity: 

$connection->beginTransaction();
try {
    $stmt = $connection->prepare("INSERT INTO users (name) VALUES (:name)");
    $stmt->execute([':name' => 'John']);
    $connection->commit();
} catch (\Exception $e) {
    $connection->rollBack();
    throw $e;
}

Using in Controllers

Inject DatabaseConnectionInterface in your controllers: 

use Forge\Core\Contracts\Database\DatabaseConnectionInterface;

final class HomeController
{
    public function __construct(
        public readonly DatabaseConnectionInterface $connection,
    ) {
    }

    public function index(): Response
    {
        $stmt = $this->connection->query("SELECT * FROM users LIMIT 5");
        $users = $stmt->fetchAll();
        // ...
    }
}

Migrations Overview

ForgeDatabaseSql provides two migration approaches: attribute-based and raw SQL. Both support migration scoping, grouping, and batching.

Two Migration Approaches

Attribute-Based

  • Use PHP 8 attributes
  • Automatic schema reflection
  • Automatic SQL generation
  • Type-safe column definitions
  • Best for standard schemas

Raw SQL

  • Full SQL control
  • Use createTable(), dropTable()
  • Custom SQL with execute()
  • Driver-specific optimizations
  • Best for complex schemas

Migration Tracking

Migrations are tracked in the forge_migrations table: 

CREATE TABLE forge_migrations (
    migration VARCHAR(255) PRIMARY KEY,
    batch INT NOT NULL,
    type VARCHAR(50) NOT NULL,        -- 'app', 'core', or 'module'
    module VARCHAR(255) NULL,         -- Module name if type='module'
    migration_group VARCHAR(255) NULL -- Group name if grouped
)

Migration Batching

Migrations run in batches for atomic execution:

  • All migrations in a batch run in a single transaction
  • If any migration fails, the entire batch is rolled back
  • Batch numbers increment automatically
  • Rollback can target specific batches

Attribute-Based Migrations

Use PHP 8 attributes to define your database schema. The migration system automatically reflects the schema and generates SQL.

Basic Structure

use App\Modules\ForgeDatabaseSQL\DB\Attributes\Table;
use App\Modules\ForgeDatabaseSQL\DB\Attributes\Column;
use App\Modules\ForgeDatabaseSQL\DB\Migrations\Migration;
use App\Modules\ForgeDatabaseSQL\DB\Enums\ColumnType;

#[Table('users')]
class CreateUsersTable extends Migration
{
    #[Column('id', ColumnType::INTEGER, primaryKey: true, autoIncrement: true)]
    public int $id;

    #[Column('email', ColumnType::STRING, length: 255, unique: true)]
    public string $email;

    #[Column('password', ColumnType::STRING, length: 255)]
    public string $password;

    // up() and down() are automatically generated
}

Column Attributes

Define columns using the #[Column] attribute: 

#[Column(
    name: 'email',
    type: ColumnType::STRING,
    length: 255,
    nullable: false,
    unique: true,
    default: null
)]
public string $email;

Special Attributes

Use special attributes for common patterns: 

use App\Modules\ForgeDatabaseSQL\DB\Attributes\Timestamps;
use App\Modules\ForgeDatabaseSQL\DB\Attributes\SoftDelete;
use App\Modules\ForgeDatabaseSQL\DB\Attributes\Status;
use App\Modules\ForgeDatabaseSQL\DB\Attributes\MetaData;
use App\Modules\ForgeDatabaseSQL\DB\Attributes\Index;

#[Table('posts')]
class CreatePostsTable extends Migration
{
    // Timestamps (created_at, updated_at)
    #[Timestamps]
    public string $created_at;
    public string $updated_at;

    // Soft delete (deleted_at)
    #[SoftDelete]
    public ?string $deleted_at;

    // Status enum column
    #[Status(values: ['draft', 'published', 'archived'])]
    public string $status;

    // JSON metadata column
    #[MetaData]
    public ?array $metadata;

    // Index
    #[Index(name: 'idx_posts_user_id', columns: ['user_id'], unique: false)]
    public int $user_id;
}

Relationship Attributes

Define relationships using relationship attributes: 

use App\Modules\ForgeDatabaseSQL\DB\Attributes\Relations\BelongsTo;
use App\Modules\ForgeDatabaseSQL\DB\Attributes\Relations\HasMany;
use App\Modules\ForgeDatabaseSQL\DB\Attributes\Relations\ManyToMany;

#[Table('posts')]
class CreatePostsTable extends Migration
{
    // BelongsTo relationship
    #[BelongsTo(related: 'User', foreignKey: 'user_id', onDelete: 'CASCADE')]
    public int $user_id;

    // ManyToMany relationship (creates join table)
    #[ManyToMany(
        related: 'Tag',
        joinTable: 'post_tags',
        foreignKey: 'post_id',
        relatedKey: 'tag_id'
    )]
    public array $tags;
}

Raw SQL Migrations

For full control, use raw SQL migrations with helper methods for cross-database compatibility.

Basic Structure

use App\Modules\ForgeDatabaseSQL\DB\Attributes\GroupMigration;
use App\Modules\ForgeDatabaseSQL\DB\Migrations\Migration;

#[GroupMigration(name: 'security')]
class CreateRateLimitsTable extends Migration
{
    public function up(): void
    {
        $sql = $this->createTable('rate_limits', [
            'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
            'ip_address' => 'VARCHAR(255) NOT NULL',
            'request_count' => 'INTEGER NOT NULL DEFAULT 1',
            'last_request' => 'TIMESTAMP NULL',
        ]);
        $this->execute($sql);
        
        $indexSql = $this->createIndex('rate_limits', 'idx_rate_limits_id', ['id']);
        $this->execute($indexSql);
    }

    public function down(): void
    {
        $this->execute($this->dropTable('rate_limits'));
    }
}

Helper Methods

Migration class provides helper methods for common operations: 

// Create table
$sql = $this->createTable('users', [
    'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
    'name' => 'VARCHAR(255) NOT NULL',
    'email' => 'VARCHAR(255) UNIQUE',
], ifNotExists: true);
$this->execute($sql);

// Drop table
$sql = $this->dropTable('users');
$this->execute($sql);

// Create index
$sql = $this->createIndex('users', 'idx_users_email', ['email'], unique: true);
$this->execute($sql);

// Add foreign key (returns null for SQLite)
$sql = $this->addForeignKey(
    'posts',
    'user_id',
    'users',
    'id',
    'CASCADE'
);
if ($sql !== null) {
    $this->execute($sql);
}

// Execute custom SQL
$this->execute("ALTER TABLE users ADD COLUMN status VARCHAR(50)");

Driver-Specific SQL Normalization

Helper methods automatically normalize SQL for different drivers:

  • SQLite: AUTOINCREMENT, INTEGER type
  • MySQL: AUTO_INCREMENT, INT type, InnoDB engine
  • PostgreSQL: SERIAL/BIGSERIAL types, no AUTO_INCREMENT

Complete Example

#[GroupMigration(name: 'security')]
class CreateCircuitBreakerTable extends Migration
{
    public function up(): void
    {
        $sql = $this->createTable('circuit_breaker', [
            'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
            'ip_address' => 'VARCHAR(45) NOT NULL',
            'fail_count' => 'INT NOT NULL DEFAULT 1',
            'first_failure' => 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP',
        ]);
        $this->execute($sql);
    }

    public function down(): void
    {
        $this->execute($this->dropTable('circuit_breaker'));
    }
}

Migration Scoping

Migrations can be organized by scope: app, engine, or module. The framework supports both folder-based and attribute-based migration discovery.

Migration Discovery Methods

Migrations are discovered using two methods:

  • Folder-based: Migrations in traditional Database/Migrations/ directories
  • Attribute-based: Any migration class with #[Migration] attribute, regardless of folder location

Both methods work together - migrations discovered through either method are included when running migrations.

App Migrations

Application-specific migrations can be placed in app/Database/migrations/ or anywhere in app/ with the #[Migration] attribute: 

php forge.php db:migrate --type=app

Engine Migrations

Core engine migrations in engine/Database/Migrations/: 

php forge.php db:migrate --type=engine

Module Migrations

Module-specific migrations can be placed in modules/{ModuleName}/src/Database/Migrations/ or anywhere in modules/{ModuleName}/src/ with the #[Migration] attribute: 

# Run all module migrations
php forge.php db:migrate --type=module

# Run specific module migrations
php forge.php db:migrate --type=module --module=ForgeAuth

Tenant Migrations

Tenant-specific migrations in modules/{ModuleName}/src/Database/Migrations/Tenants/:

Automatically discovered when running module migrations.

Attribute-Based Migration Example

use Forge\Core\DI\Attributes\Migration;
use App\Modules\ForgeDatabaseSQL\DB\Migrations\Migration as BaseMigration;

#[Migration(scope: 'app')]
class CreateCustomTable extends BaseMigration
{
    // Migration code here
}

Migration Grouping

Group migrations using the #[GroupMigration] attribute to organize related migrations.

Using GroupMigration Attribute

use App\Modules\ForgeDatabaseSQL\DB\Attributes\GroupMigration;

#[GroupMigration(name: 'security')]
class CreateRateLimitsTable extends Migration
{
    // Migration code
}

#[GroupMigration(name: 'security')]
class CreateCircuitBreakerTable extends Migration
{
    // Migration code
}

Running Migrations by Group

# Run only security group migrations
php forge.php db:migrate --group=security

# Run security group for specific module
php forge.php db:migrate --type=module --module=ForgeAuth --group=security

Use Cases

  • Feature Groups: Group migrations by feature (e.g., 'users', 'products')
  • Domain Groups: Group by domain (e.g., 'security', 'billing')
  • Module Groups: Group module-specific migrations

Migration Commands

Run migrations using the db:migrate command with various options.

Basic Commands

# Run all migrations (starts wizard)
php forge.php db:migrate

# Run app migrations
php forge.php db:migrate --type=app

# Run engine migrations
php forge.php db:migrate --type=engine

# Run all module migrations
php forge.php db:migrate --type=module

# Run specific module migrations
php forge.php db:migrate --type=module --module=ForgeAuth

Group Filtering

# Run migrations by group
php forge.php db:migrate --group=security

# Combine type and group
php forge.php db:migrate --type=module --module=ForgeAuth --group=security

Preview Migrations

# Preview pending migrations without running
php forge.php db:migrate --preview

# Preview with filters
php forge.php db:migrate --type=app --preview
php forge.php db:migrate --group=security --preview

Rollback Migrations

# Rollback last batch
php forge.php db:migrate:rollback

# Rollback specific number of batches
php forge.php db:migrate:rollback --steps=3

# Rollback by type
php forge.php db:migrate:rollback --type=app

# Rollback by group
php forge.php db:migrate:rollback --group=security

Seeders

Seeders populate your database with test or initial data. They support automatic rollback for easy cleanup.

Basic Structure

use App\Modules\ForgeDatabaseSQL\DB\Seeders\Seeder;

class UserSeeder extends Seeder
{
    public function up(): void
    {
        $this->insertBatch('users', [
            ['email' => 'admin@example.com', 'name' => 'Admin'],
            ['email' => 'user@example.com', 'name' => 'User'],
        ]);
    }

    public function down(): void
    {
        // Optional: define rollback logic
    }
}

Automatic Rollback

Use #[AutoRollback] attribute for automatic rollback: 

use App\Modules\ForgeDatabaseSQL\DB\Seeders\Attributes\AutoRollback;

#[AutoRollback(table: 'users', where: ['email' => 'admin@example.com'])]
class UserSeeder extends Seeder
{
    public function up(): void
    {
        $this->insertBatch('users', [
            ['email' => 'admin@example.com', 'name' => 'Admin'],
        ]);
    }

    // down() automatically deletes where email = 'admin@example.com'
}

Batch Insertion

Use insertBatch() for efficient bulk inserts: 

// Insert in batches of 500 (default)
$this->insertBatch('users', $users);

// Custom batch size
$this->insertBatch('users', $users, batchSize: 1000);

Seeder Commands

Run seeders using the db:seed command.

Basic Commands

# Run all seeders (starts wizard)
php forge.php db:seed

# Run app seeders
php forge.php db:seed --type=app

# Run engine seeders
php forge.php db:seed --type=engine

# Run module seeders
php forge.php db:seed --type=module --module=ForgeAuth

Preview Seeders

# Preview seeders without running
php forge.php db:seed:preview

Rollback Seeders

# Rollback seeders
php forge.php db:seed:rollback

Schema Formatters

Schema formatters generate database-specific SQL, ensuring cross-database compatibility.

Automatic Formatter Selection

Formatters are automatically selected based on the database driver: 

$driver = $connection->getDriver();
$formatter = match ($driver) {
    'mysql' => new MySqlFormatter(),
    'sqlite' => new SqliteFormatter(),
    'pgsql' => new PostgreSqlFormatter(),
    default => throw new RuntimeException("Unsupported driver: $driver")
};

MySqlFormatter

MySQL-specific SQL generation:

  • Uses backticks for identifiers
  • AUTO_INCREMENT for auto-increment columns
  • InnoDB engine with utf8mb4 charset
  • INT type (not INTEGER)

PostgreSqlFormatter

PostgreSQL-specific SQL generation:

  • Uses double quotes for identifiers
  • SERIAL/BIGSERIAL for auto-increment (not AUTO_INCREMENT)
  • Advanced JSON support

SqliteFormatter

SQLite-specific SQL generation:

  • Uses double quotes for identifiers
  • AUTOINCREMENT (not AUTO_INCREMENT)
  • INTEGER type
  • Limited ALTER TABLE support

Column Types

ForgeDatabaseSql supports a comprehensive set of column types, automatically mapped to database-specific types.

Supported Column Types

use App\Modules\ForgeDatabaseSQL\DB\Enums\ColumnType;

// Available types:
ColumnType::UUID        // CHAR(36) in MySQL, UUID in PostgreSQL
ColumnType::STRING      // VARCHAR(255)
ColumnType::TEXT        // TEXT
ColumnType::INTEGER     // INT in MySQL, INTEGER in SQLite, INT in PostgreSQL
ColumnType::BOOLEAN     // BOOLEAN
ColumnType::FLOAT       // FLOAT
ColumnType::DECIMAL     // DECIMAL
ColumnType::DATE        // DATE
ColumnType::DATETIME    // DATETIME
ColumnType::TIMESTAMP   // TIMESTAMP
ColumnType::ENUM        // ENUM (MySQL), VARCHAR with check (PostgreSQL)
ColumnType::JSON        // JSON
ColumnType::BLOB        // BLOB
ColumnType::ARRAY       // JSON (mapped to JSON type)

Type Mapping

Column types are automatically mapped to database-specific types:

ColumnType MySQL PostgreSQL SQLite
UUID CHAR(36) UUID TEXT
STRING VARCHAR(255) VARCHAR(255) TEXT
INTEGER INT INT INTEGER
JSON JSON JSON TEXT

Direct Database Access

ForgeDatabaseSql provides direct access to database operations through DatabaseConnectionInterface.

Executing DDL Statements

Use exec() for DDL statements: 

// Create table
$this->connection->exec(
    "CREATE TABLE IF NOT EXISTS example_table (id INTEGER PRIMARY KEY, name TEXT)"
);

// Alter table
$this->connection->exec("ALTER TABLE users ADD COLUMN status VARCHAR(50)");

Querying Data

Use query() for SELECT statements: 

$stmt = $this->connection->query("SELECT * FROM users LIMIT 5");
$users = $stmt->fetchAll();

Parameterized Queries

Use prepare() for parameterized queries: 

$stmt = $this->connection->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([':id' => 1]);
$user = $stmt->fetch();

Transactions

Full transaction support for data integrity: 

// Commit example
$this->connection->beginTransaction();
try {
    $stmt = $this->connection->prepare("INSERT INTO example_table (name) VALUES (:name)");
    $stmt->execute([':name' => 'transaction_test_commit']);
    $this->connection->commit();
    return ['status' => 'committed'];
} catch (\Exception $e) {
    $this->connection->rollBack();
    return ['status' => 'error', 'message' => $e->getMessage()];
}

// Rollback example
$this->connection->beginTransaction();
try {
    $stmt = $this->connection->prepare("INSERT INTO example_table (name) VALUES (:name)");
    $stmt->execute([':name' => 'transaction_test_rollback']);
    throw new \Exception('Simulated error');
} catch (\Exception $e) {
    $this->connection->rollBack();
    // Changes are rolled back
}

Usage Examples

Complete examples from the Forge Kernel codebase.

Raw SQL Migration Example

use App\Modules\ForgeDatabaseSQL\DB\Attributes\GroupMigration;
use App\Modules\ForgeDatabaseSQL\DB\Migrations\Migration;

#[GroupMigration(name: 'security')]
class CreateRateLimitsTable extends Migration
{
    public function up(): void
    {
        $sql = $this->createTable('rate_limits', [
            'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
            'ip_address' => 'VARCHAR(255) NOT NULL',
            'request_count' => 'INTEGER NOT NULL DEFAULT 1',
            'last_request' => 'TIMESTAMP NULL',
        ]);
        $this->execute($sql);
        
        $indexSql = $this->createIndex('rate_limits', 'idx_rate_limits_id', ['id']);
        $this->execute($indexSql);
    }

    public function down(): void
    {
        $this->execute($this->dropTable('rate_limits'));
    }
}

Direct Database Access Example

use Forge\Core\Contracts\Database\DatabaseConnectionInterface;

final class HomeController
{
    public function __construct(
        public readonly DatabaseConnectionInterface $connection,
    ) {
    }

    private function forgeDatabaseSQLExecExample(): array
    {
        $this->connection->exec(
            "CREATE TABLE IF NOT EXISTS example_table (id INTEGER PRIMARY KEY, name TEXT)"
        );
        return ['status' => 'exec executed'];
    }

    private function forgeDatabaseSQLQueryExample(): array
    {
        $stmt = $this->connection->query("SELECT * FROM users LIMIT 5");
        return $stmt->fetchAll();
    }

    private function forgeDatabaseSQLPrepareExample(): array
    {
        $stmt = $this->connection->prepare("SELECT * FROM users WHERE id = :id");
        $stmt->execute([':id' => 1]);
        return $stmt->fetchAll();
    }
}

Best Practices

Recommendations for using ForgeDatabaseSql effectively.

Things That Often Help

  • • Use attribute-based migrations for standard schemas
  • • Use raw SQL migrations for complex or driver-specific features
  • • Group related migrations using #[GroupMigration]
  • • Use migration scoping to organize by app, engine, module
  • • Use transactions for data integrity
  • • Use parameterized queries to prevent SQL injection
  • • Use insertBatch() for efficient bulk inserts in seeders
  • • Use #[AutoRollback] for easy seeder cleanup

Things to Consider

  • • Don't mix attribute-based and raw SQL in the same migration
  • • Don't forget to implement down() methods for rollback
  • • Don't use raw SQL without parameterization for user input
  • • Don't forget to test migrations on all target databases
  • • Don't create migrations that depend on specific data
  • • Don't forget to handle SQLite's limited ALTER TABLE support
  • • Don't use seeders for production data
  • • Don't forget to preview migrations before running

When to Use Attribute-Based vs Raw SQL

Attribute-Based

Use when:

  • Schema is standard and straightforward
  • You want type-safe column definitions
  • You need automatic relationship handling
  • Cross-database compatibility is important

Raw SQL

Use when:

  • You need driver-specific optimizations
  • Schema is complex or non-standard
  • You need full control over SQL generation
  • You're migrating from existing SQL