ForgeDatabaseSQL

Overview

This isn't a framework layer you inherit. It's a component your app pulls in so it can talk to a relational database. You decide when to add it, which driver to use, and how your app queries — the capability supplies the mechanics.

When to Reach for It

Add ForgeDatabaseSQL when your app needs a relational store. Common cues:

• Your app has entities to persist and query.
• You want schema migrations that travel with your code.
• You need transactions for multi-step writes.
• You'd rather inject a connection than deal with raw drivers directly.

If your app does none of this, skip it — the Kernel is happy without a database. If you want a higher-level, object-style interface over this raw connection, pair it with ForgeSqlOrm.

Installation

As a core capability it's available by default. If your project doesn't have it, install it the same way you'd add any capability:

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

# Or name it directly
php forge.php package:install-module --module=ForgeDatabaseSQL

Once installed, the capability wires its connection and registers its db: commands in your project's CLI.

Configuration

Your connection settings live in your environment configuration, under a small set of DB_* keys. For SQLite you give a path instead of a host, user, and password:

# Server databases (MySQL, PostgreSQL)
DB_DRIVER=mysql            # or pgsql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=my_app
DB_USER=my_app
DB_PASS=secret

# File-based (SQLite) — no host/user/pass needed
DB_DRIVER=sqlite
SQLITE_PATH=/storage/database
SQLITE_DB=/my_app.sqlite

DB_DRIVER is sqlite by default; switch it to mysql or pgsql to change databases. For SQLite the file is resolved under your project's base path.

Using the Connection

The capability registers a DatabaseConnectionInterface in the container. Inject it anywhere the container builds your app's classes, then run queries:

use Forge\Core\Contracts\Database\DatabaseConnectionInterface;

final class UserRepository
{
    public function __construct(
        private DatabaseConnectionInterface $connection,
    ) {
    }

    public function recent(int $limit): array
    {
        return $this->connection
            ->query("SELECT * FROM users ORDER BY id DESC LIMIT " . $limit)
            ->fetchAll();
    }
}

Prepared statements and transactions

Always parameterize user input, and wrap multi-step writes in a transaction:

// Parameterized query — safe against injection
$stmt = $this->connection->prepare(
    "SELECT * FROM users WHERE email = :email"
);
$stmt->execute([':email' => $email]);
$user = $stmt->fetch();

// Transaction — all-or-nothing writes
$this->connection->beginTransaction();
try {
    $stmt = $this->connection->prepare(
        "INSERT INTO accounts (owner_id, balance) VALUES (:id, 0)"
    );
    $stmt->execute([':id' => $user['id']]);
    $this->connection->commit();
} catch (\Throwable $e) {
    $this->connection->rollBack();
    throw $e;
}

Migrations

Schema changes travel with your code. You scope a migration to your app or to a specific module, write it either with PHP attributes or raw SQL methods, and run it through the CLI.

Running migrations

Migrations run in batches inside a single transaction — if one fails, the whole batch rolls back. Scope them by app, module, or all, optionally filtered to a module or group:

# Your app's migrations
php forge.php db:migrate --type=app

# A specific module's migrations
php forge.php db:migrate --type=module --module=ForgeAuth

# Everything, or just a group
php forge.php db:migrate --type=all
php forge.php db:migrate --type=module --module=ForgeAuth --group=security

# Preview what will run, without running it
php forge.php db:migrate --type=app --preview

Rolling back

# Undo the last batch (or N batches)
php forge.php db:migrate:rollback
php forge.php db:migrate:rollback --steps=3

# Roll back by scope or group
php forge.php db:migrate:rollback --type=app
php forge.php db:migrate:rollback --group=security

A migration example

use Modules\ForgeDatabaseSQL\DB\Migrations\Migration;
use Modules\ForgeDatabaseSQL\DB\Attributes\Column;
use Modules\ForgeDatabaseSQL\DB\Attributes\Table;
use 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;
}

Seeders

Seeders fill your database with test or initial data. Scoped and run the same way as migrations:

# Your app's seeders
php forge.php db:seed --type=app

# Preview or roll back
php forge.php db:seed:preview
php forge.php db:seed:rollback --steps=1

A seeder extends the base Seeder and uses insertBatch() for bulk inserts. The #[AutoRollback] attribute cleans up exactly what the seeder inserted:

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

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

Database Sessions

If you'd rather keep sessions in your database than in files, this capability can back them with your connection. Set your session driver to database:

SESSION_DRIVER=database
SESSION_LIFETIME=120

When the driver is database and the connection is available, the capability swaps in a database-backed session driver automatically.