Packages

crystoline/xpsr-database

Database connections and migrations for XPSR applications, backed by Doctrine DBAL. DBAL gives one API for SQLite, MySQL, MariaDB, PostgreSQL, and others: queries with bound parameters, a query builder, transactions, and portable schema changes. This package configures the connections and adds migrate, migrate:rollback, migrate:status, and make:migration. It is not an ORM.

composer require crystoline/xpsr-database

Add the provider to app.registry in config/app.php:

Crystoline\Xpsr\Database\DatabaseServiceRegistry::class,

The skeleton's config/database.php already has SQLite, MySQL, and PostgreSQL connections. The default is SQLite in var/database.sqlite, which needs no server; the pdo_sqlite extension must be loaded. To use MySQL, set in .env:

DB_CONNECTION=mysql
DATABASE_URL=mysql://app:secret@127.0.0.1:3306/app

Queries

Inject Doctrine\DBAL\Connection, the default connection. It connects on the first query.

use Doctrine\DBAL\Connection;

public function __construct(private readonly Connection $db) {}

$user = $this->db->fetchAssociative('SELECT id, name FROM users WHERE id = ?', [$id]);  // array|false
$names = $this->db->fetchFirstColumn('SELECT name FROM users ORDER BY name');
$this->db->insert('users', ['name' => 'Ada', 'email' => 'ada@example.com']);
$this->db->update('users', ['name' => 'Ada Lovelace'], ['id' => $id]);

$this->db->transactional(function (Connection $db) use ($order): void {
    $db->insert('orders', $order);
    $db->executeStatement('UPDATE stock SET quantity = quantity - ? WHERE sku = ?', [1, $order['sku']]);
});

Always pass values as parameters, never by building SQL strings. See the DBAL documentation for the query builder, types, and the rest of the API.

Configuration

config/database.php:

return [
    'default' => env('DB_CONNECTION', 'sqlite'),
    'connections' => [
        'sqlite' => ['driver' => 'pdo_sqlite', 'path' => env('DB_DATABASE', 'var/database.sqlite')],
        'mysql' => [
            'url' => env('DATABASE_URL'),
            'driver' => 'pdo_mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            // ...
        ],
        'reporting' => ['url' => env('REPORTING_DATABASE_URL')],
    ],
    'migrations' => ['path' => 'migrations', 'table' => 'migrations'],
    'log_queries' => false,
];

A relative SQLite path is resolved against the project root, and its directory is created if needed. Invalid settings fail when the connection is first used, with the setting's name.

Other connections come from Crystoline\Xpsr\Database\ConnectionManager:

$reports = $connections->connection('reporting');

Migrations

php xpsr make:migration create_users_table --create=users

This writes migrations/20261028120000_create_users_table.php: a UTC timestamp (year, month, day, hour, minute, second), an underscore, and the name. Migrations run in file-name order, which is the order they were created. A migration file returns a Crystoline\Xpsr\Database\Migration\Migration, usually an anonymous class:

use Crystoline\Xpsr\Database\Migration\Migration;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Schema\Column;
use Doctrine\DBAL\Schema\PrimaryKeyConstraint;
use Doctrine\DBAL\Schema\Table;
use Doctrine\DBAL\Types\Types;

return new class extends Migration {
    public function up(Connection $db): void
    {
        $db->createSchemaManager()->createTable(
            Table::editor()
                ->setUnquotedName('users')
                ->setColumns(
                    Column::editor()->setUnquotedName('id')->setTypeName(Types::INTEGER)->setAutoincrement(true)->create(),
                    Column::editor()->setUnquotedName('email')->setTypeName(Types::STRING)->setLength(255)->create(),
                )
                ->setPrimaryKeyConstraint(PrimaryKeyConstraint::editor()->setUnquotedColumnNames('id')->create())
                ->create(),
        );
    }

    public function down(Connection $db): void
    {
        $db->createSchemaManager()->dropTable('users');
    }
};

DBAL's schema API produces the right SQL for each database. Plain SQL works too: $db->executeStatement('CREATE INDEX ...'). To change an existing table, make:migration add_name_to_users --table=users starts from the alterTable() helper:

$this->alterTable($db, 'users', static function (TableEditor $table): void {
    $table->addColumn(Column::editor()->setUnquotedName('name')->setTypeName(Types::STRING)->setLength(100)->setNotNull(false)->create());
});

migrate, migrate:rollback, and migrate:status accept --connection=<name>. Each connection keeps its own migrations table.

Generated migrations. An ORM adapter such as crystoline/xpsr-orm-doctrine binds Crystoline\Xpsr\Database\Schema\SchemaProviderInterface, which describes the tables the application expects. php xpsr make:migration <name> --diff then compares the database with it and writes a migration whose up() runs the SQL for the difference and whose down() reverses it. Only tables the schema names are compared, so the migrations table and other tables are never dropped. Pending migrations must run first, and nothing is written when there is no difference. Without a schema provider, --diff exits with 1 and says so.

Production. With APP_ENV=production, migrate and migrate:rollback ask for confirmation in a terminal and refuse without --force otherwise. Deployment scripts run php xpsr migrate --force.

Failures. A migration that throws stops the run; later migrations do not run, and the command exits with 1 naming the migration. Migrations that completed before it stay applied and recorded. On PostgreSQL and SQLite, a migration runs in a transaction with its bookkeeping, so a failed migration leaves nothing behind. MySQL and MariaDB commit each schema change immediately: a migration that fails part-way stays partly applied and needs repairing by hand, so keep each migration small there. Set protected bool $transactional = false; in a migration whose statements cannot run in a transaction, such as PostgreSQL's CREATE INDEX CONCURRENTLY.

Rolling back. down() is optional. Without it, rolling back that migration fails with a message, and nothing after it in the rollback runs. A migration whose file was deleted after it ran shows as "ran, file missing" and cannot be rolled back.

Testing

Use an in-memory SQLite database for tests, in config/testing/database.php:

return [
    'default' => 'testing',
    'connections' => ['testing' => ['driver' => 'pdo_sqlite', 'memory' => true]],
];

Each test gets a new application and therefore an empty database. Run the migrations in setUp():

protected function setUp(): void
{
    [$status] = $this->console('migrate');
    self::assertSame(0, $status);
}

SQLite differs from MySQL and PostgreSQL in places, such as type checking and some SQL functions. Run the suite against your production database type as well before relying on it.

To test against a real database without leaving data behind, migrate it once and wrap each test in a transaction with Crystoline\Xpsr\Database\Testing\DatabaseTransactions:

use Crystoline\Xpsr\Database\Testing\DatabaseTransactions;

final class OrderTest extends TestCase
{
    use DatabaseTransactions;

    protected function setUp(): void
    {
        $this->beginDatabaseTransaction();           // or beginDatabaseTransaction('main', 'reporting')
    }
}

The transaction is rolled back after every test, together with any transactions the code under test left open. It covers everything that uses XPSR's connections. MySQL and MariaDB commit implicitly on schema changes, so do not change the schema inside such a test.

Sessions

The package provides the store for SESSION_STORE=database. Create its table, named by session.database.table (default sessions):

php xpsr make:session-table
php xpsr migrate

The table has id, payload, and an indexed last_activity (Unix time). session.database.connection picks a connection other than the default. Expired rows are deleted by session:prune and by about one request in session.prune_every. See the XPSR repository's docs/SESSIONS.md.

Users and API tokens

For config/auth.php, the package provides the database user provider, which reads users from a table, and the token guard driver: revocable API tokens stored as SHA-256 hashes.

'users' => ['driver' => 'database', 'table' => 'users', 'login' => 'email', 'password' => 'password'],
'guards' => ['api' => ['driver' => 'token']],

php xpsr make:token-table && php xpsr migrate
php xpsr auth:token ada@example.com --scope=tasks:read --expires="90 days"
php xpsr auth:token-revoke 12

Each token belongs to one guard (its guard column), and only that guard accepts it; --guard and the guard argument of TokenRepository::create(), forUser(), and revokeAll() choose it, defaulting to the first guard with the token driver. Crystoline\Xpsr\Database\Auth\TokenRepository creates, lists, and revokes tokens from code. See the XPSR repository's docs/AUTH.md.

Using another migration runner

The migrate, migrate:rollback, and migrate:status commands talk only to Crystoline\Xpsr\Database\Migration\MigratorInterface. They get it from the factory named in database.migrations.runner, which is XPSR's own runner, Crystoline\Xpsr\Database\MigratorFactory, by default. A package can provide another runner, for example one backed by Doctrine Migrations, and the commands and their output stay the same:

'migrations' => ['runner' => App\Database\DoctrineMigratorFactory::class],

A runner implements MigratorInterface and a factory implementing MigratorFactoryInterface. Prove it with the shared contract suite by extending Crystoline\Xpsr\Database\Testing\MigratorContractTestCase and implementing its four hooks: migrator(), addMigration(), addFailingMigration(), and tableExists(). XPSR's runner passes the same suite. make:migration writes files for XPSR's runner only; another runner brings its own generator.

Services

This package's tests run against SQLite, and against MySQL 8.4, MariaDB 11, and PostgreSQL 17 when XPSR_TEST_MYSQL_URL or XPSR_TEST_PGSQL_URL is set.

Requires PHP 8.4+ and the PDO extension for your database.