One Interface, Three Database Engines: Building the Driver Seam Before You Need It

TL;DR — I spent today building the ability to manage a database server from a platform UI: create databases, create users, grant access, rotate passwords. Postgres first, MySQL and MariaDB right behind it. The whole design hinges on one decision made on day one — put a DatabaseAdminContract between the platform and the daemon, and never let the engine leak upward. Here's what that seam actually has to promise to be worth anything.

---

The shape of the problem

You've got a node. On that node there's a database daemon. You want an operator to click "create database" in a web UI and have the right DDL run on the right server, with an audit trail, without SSH-ing anywhere.

The naive version writes psql calls into a job and ships it. That works — until the second engine arrives, and now every job, every policy, every Livewire component has a match ($engine) in it. The engine has leaked into thirty files and you can't add a third one without touching all of them.

The alternative is boring and old: an interface.

interface DatabaseAdminContract
{
    public function serverInfo(): DatabaseServerInfo;

    public function createDatabase(string $name): void;
    public function dropDatabase(string $name): void;

    /** @return list<string> */
    public function listDatabases(): array;

    public function createUser(string $username, string $password): void;
    public function dropUser(string $username): void;
    public function setPassword(string $username, string $password): void;

    public function grant(string $username, string $database, DatabasePrivilege $privilege): void;
    public function revoke(string $username, string $database): void;
}

Nothing surprising there. The interesting part isn't the method list — it's the four promises the interface has to make on top of the method list, because an interface that only constrains shape still lets two implementations behave completely differently.

Promise 1: presets, not raw passthrough

The tempting signature is grant(string $username, string $database, string $sql). Don't. The moment a caller can hand you privilege SQL, the caller has to know which engine it's talking to — and you've re-leaked the thing you just spent an interface trying to contain. Postgres and MySQL don't even agree on what a "role" is.

So privileges become an engine-agnostic enum, and each driver maps a preset to its own statements:

enum DatabasePrivilege: string implements Contract
{
    use InteractsWithEnum;

    case ReadOnly = 'read_only';
    case ReadWrite = 'read_write';
    case Owner = 'owner';

    public function label(): string
    {
        return match ($this) {
            self::ReadOnly  => __('Read only'),
            self::ReadWrite => __('Read and write'),
            self::Owner     => __('Owner'),
        };
    }
}

Three cases is a feature, not a limitation. A closed set of presets is auditable — you can render "this user has ReadOnly on that database" in a UI and mean it. A raw GRANT string is an opaque blob you can only replay, never reason about.

One rule that fell out of this: one privilege per (database, user) pair. Applying a preset replaces whatever was applied before. That sounds like a restriction until you try to build the downgrade path without it — with additive grants, "downgrade this user to read-only" means enumerating and revoking everything else first, and any privilege you forget to enumerate silently survives the downgrade. With replace semantics, a downgrade genuinely narrows.

Promise 2: idempotency is part of the contract

Every method here runs inside a queued job on a machine that can vanish mid-operation. If a re-run isn't safe, an operator hitting Retry is a coin flip.

So idempotency is documented per method, not left to the implementer's taste:

• createDatabase — a database that already exists is left alone.
• createUser — an existing user has its password set instead.
• dropDatabase / dropUser — tolerate absence.
• setPassword — fails loudly on a user that doesn't exist.

That last one is the one people get wrong. It's tempting to make setPassword upsert for symmetry. But "set the password" and "create an account" are different intentions, and quietly creating an account because someone typo'd a username is exactly the kind of helpfulness you regret at 2am. Idempotent doesn't mean permissive — it means a repeat of the same intention converges. A different intention should still fail.

There's a Postgres-specific wrinkle worth knowing: CREATE DATABASE can't run inside a transaction. You don't get to wrap the DDL and roll back on conflict. Every statement gets guarded by an explicit existence check instead, which means the guard is your only safety net — write it first, not after the first duplicate-key incident.

Promise 3: the enum is a promise

The engine enum looks innocent:

enum DatabaseEngine: string implements Contract
{
    use InteractsWithEnum;

    case Postgres = 'postgresql';
    case Mysql    = 'mysql';
    case MariaDb  = 'mariadb';
}

Here's the rule I hold it to: a case in this enum means a driver exists for it. Adding a case ahead of its implementation creates the worst kind of bug — the wizard offers the option, the operator picks it, and nothing downstream can serve it. The enum was making a promise the codebase hadn't kept.

Two small things in there that are easy to get wrong:

• mariadb is its own case, not an alias for mysql. Package managers treat it as a separate slug, and the client binaries have diverged — on recent Debian, mariadb is the binary you're guaranteed, not mysql. Aliasing them saves one enum case and costs you a shell command that isn't there.
• The Laravel driver name for MariaDB is mariadb, not mysql. It's been first-class since Laravel 11. Naming mysql would work, but it records the wrong fact for any workload that later reads its own connection config back.

Now — the honest bit. Today the MySQL and MariaDB admin drivers landed, but the setup pipeline for them didn't. Partial capability is normal. What matters is how you express it: the wizard only offers those engines in adopt mode, and the setup job refuses them with a stated reason rather than half-running and leaving a broken node. Half-shipped is fine. Half-shipped and silent is not.

Promise 4: null means "no evidence"

The DTO the daemon reports itself with:

final readonly class DatabaseServerInfo
{
    public function __construct(
        public bool $reachable,
        public ?string $version = null,
        public ?string $uptime = null,
        public ?string $message = null,
        public ?int $port = null,
        public ?bool $tlsEnabled = null,
    ) {}
}

When reachable is false, message carries the reason and everything else is null — because an unreachable server has no answer, not a stale one and definitely not an invented one.

The port field is the sharp edge. It's the daemon's own answer (SHOW port), not a default filled in because the field looked lonely. A nullable field has to read the same whether the daemon had no answer or nobody asked; consumers treat null as "no evidence". The second you default it to 5432, you can't distinguish "confirmed 5432" from "we guessed", and a UI that shows a guessed endpoint as a fact is a support ticket waiting to happen.

The pipeline: steps that can be re-run

Setting up a fresh database server isn't one operation, it's a sequence — install, move the port, rewrite host-based auth, open the firewall, verify. Same interface trick, one level up:

interface DatabaseServerSetupStepContract
{
    public function name(): string;

    public function run(DatabaseServerSetupContext $context): void;

    public function rollback(DatabaseServerSetupContext $context): void;
}

Two properties make this survivable:

Steps converge, they don't error. Re-running a successful step is a no-op, which is what makes the pipeline resumable after a partial failure. Otherwise "resume" means "figure out by hand which step we died on".

A skip isn't a success. A step that deliberately declines to act throws a StepSkipped carrying the reason. The pipeline records the skip and carries on — and critically, doesn't add that step to the rollback chain. Rolling back something you never did is how a rollback turns a partial failure into a total one.

Provision vs adopt: the read-only twin

The last piece is the one I'd have skipped six months ago and regretted: adopting a daemon the platform didn't install.

Everything the setup pipeline does — move the port, rewrite auth config, open the firewall — is exactly what must not happen to a daemon that's already serving traffic. So adoption is a completely separate path, and its defining property is that it records reality and changes nothing:

• prove the daemon answers
• read the port and TLS state it actually runs with
• inventory the databases it serves
• mark the row active

That's it. Every write after that goes through the ordinary operation actions, whose DDL is already idempotent on things that exist.

The port cross-check earns its keep here. The admin client falls back to the engine's default socket, so a wrongly recorded port can still successfully reach a daemon on 5432 — and you'd cheerfully mark that row active while storing an endpoint no client can actually use. A mismatch fails, naming both numbers. Reaching the daemon is not the same as the recorded connection details being right.

And the jobs run with tries = 1:

class AdoptDatabaseServerJob implements ShouldQueue
{
    use Queueable;

    public int $timeout = 300;

    public int $tries = 1;
}

Automatic retries are a good default for a mailer and a bad one for infrastructure. A re-run against a live database server is an operator's decision — surfaced as a Refresh button — not something a queue worker gets to decide at 3am.

Testing a contract, not an implementation

The nice side effect of a real seam: the test suite is written once, against the interface, and every driver has to pass it.

dataset('admins', [
    'postgres' => fn () => new PostgresDatabaseAdmin(FakeRunner::make()),
    'mysql'    => fn () => new MysqlDatabaseAdmin(FakeRunner::make()),
    'mariadb'  => fn () => new MariaDbDatabaseAdmin(FakeRunner::make()),
]);

it('creates a database idempotently', function (DatabaseAdminContract $admin) {
    $admin->createDatabase('reporting');
    $admin->createDatabase('reporting');

    expect($admin->listDatabases())->toContain('reporting');
})->with('admins');

it('refuses to set a password for a user that does not exist', function (DatabaseAdminContract $admin) {
    expect(fn () => $admin->setPassword('ghost', 'irrelevant'))
        ->toThrow(DatabaseAdminException::class);
})->with('admins');

it('replaces the previous privilege instead of adding to it', function (DatabaseAdminContract $admin) {
    $admin->createDatabase('reporting');
    $admin->createUser('analyst', 'irrelevant');

    $admin->grant('analyst', 'reporting', DatabasePrivilege::ReadWrite);
    $admin->grant('analyst', 'reporting', DatabasePrivilege::ReadOnly);

    expect($admin->privilegeFor('analyst', 'reporting'))
        ->toBe(DatabasePrivilege::ReadOnly);
})->with('admins');

Adding a fourth engine becomes: write the driver, add one line to the dataset, watch which promises you broke. That's the entire payoff of the seam, and it only exists because the promises were written down as behaviour, not just as method signatures.

Takeaway

An interface constrains shape. A contract constrains behaviour — idempotency, failure modes, what null means, what a repeat call does. If your docblocks only restate the type signature, you've got the first one and you're hoping for the second.

Three things I'd carry into any driver-based abstraction:

1. No raw passthrough. The moment a caller can hand you backend-specific input, the backend has leaked through your seam.
2. An enum case is a promise. Never offer an option nothing downstream can serve — and when capability is partial, refuse loudly with a reason.
3. Idempotent ≠ permissive. Repeating an intention should converge. A different intention should still fail.

Next up: the setup pipeline for the remaining engines, so adopt mode stops being the only door in.