Database & Migrations

Nibiru ships with five database drivers behind a unified Db adapter, plus a numbered-SQL migration runner driven by the CLI.

Driver choice

Set the active driver in [DATABASE] driver of your INI:

Driver	Backend	Notes
mysql	Native MySQL/MariaDB (mysqli)	Fastest, no PDO overhead.
pdo	PDO MySQL	Recommended default. Prepared statements, drivers are widely used.
postgres	PostgreSQL via ODBC	When your server only has ODBC available.
psql	PostgreSQL via libpq (pg_*)	Native PostgreSQL.
postgresql	A wrapper that picks the best PostgreSQL transport at runtime.

[DATABASE]
driver   = "pdo"
hostname = "localhost"
port     = 3306
username = "nibiru"
password = "secret"
basename = "nibiru_dev"
encoding = "utf8mb4"
is.active = true

The dispatcher initialises the driver during boot. Switching drivers requires only a config change and a restart.

The Db adapter

Every generated model extends a driver-specific Db adapter (Adapter\MySQL\Db, Adapter\PostgreSQL\Db, Adapter\Odbc\Db). All adapters share the same surface area:

use Nibiru\Pdo;

// Read a single row by primary-key columns
$row = Pdo::fetchRowInArrayById('users', ['user_id' => 42]);

// Run a parameterised query
$rows = Pdo::fetchAll(
    'SELECT * FROM products WHERE category = :cat',
    [':cat' => 'gold-plating']
);

// Insert
Pdo::insert('products', [
    'name' => 'Marduk Gold Plating',
    'price' => 99.0,
]);

// Update
Pdo::update('products',
    ['price' => 89.0],
    ['id' => 1]);

// Delete
Pdo::delete('products', ['id' => 1]);

// Last insert id
Pdo::lastInsertId();

Driver-specific helpers exist on the adapter classes themselves (\Nibiru\Mysql::query(), \Nibiru\Postgresql::pgQuery(), …) when you need raw access.

Schema-first models

When [GENERATOR] database = true, the dispatcher regenerates one PHP class per table on every request. See Models.

In production:

[GENERATOR]
database          = false
database.overwrite = false

Re-generate only when you migrate the schema.

Migrations

Migrations are plain SQL files in application/settings/config/database/, numbered for execution order:

application/settings/config/database/
├── 001-acl.sql
├── 002-account.sql
├── 003-api_registry.sql
├── 004-timeanddate.sql
├── 005-user.sql
├── 006-user_to_account.sql
├── 011-acl-data.sql
├── 012-add-unique-key-user.sql
└── 013-add-account-email.sql

The CLI applies them with:

./nibiru -mi local
APPLICATION_ENV=staging   ./nibiru -mi staging
APPLICATION_ENV=production ./nibiru -mi production

The runner creates a _migrations table (per-driver name) and skips files already applied. Each file runs as a single batch; if a statement errors mid-way through, fix the SQL and re-run.

File naming conventions

• Three-digit zero-padded numeric prefix: NNN-<slug>.sql.
• Slugs describe the change (-add-account-email, -drop-wrong-constraints).
• One logical change per file. Don’t squash.
• For data-only seeds, use -data.sql suffix (011-acl-data.sql).

Idempotency

Use IF NOT EXISTS and IF EXISTS so files can re-run safely:

CREATE TABLE IF NOT EXISTS api_registry (
    api_registry_id     INT(11) NOT NULL AUTO_INCREMENT,
    api_registry_name   VARCHAR(255) NOT NULL,
    api_registry_token  VARCHAR(64)  NOT NULL,
    PRIMARY KEY (api_registry_id),
    UNIQUE KEY api_registry_token_uk (api_registry_token)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE user
    ADD UNIQUE KEY user_login_uk (user_login)
    /* skip if exists; on MySQL 8 you can wrap with IF NOT EXISTS */;

Reset commands (use with care)

./nibiru -mi-reset local                    # forget all applied migrations
./nibiru -mi-reset-file 005-user.sql local  # forget a single file's run record

These don’t drop tables — only the _migrations audit table. Combine with manual DROP if you really want a clean slate.

PostgreSQL specifics

When driver = "psql" or "postgresql":

• Use SERIAL / BIGSERIAL instead of AUTO_INCREMENT.
• Tables in information_schema.tables rather than SHOW TABLES.
• Quoting: identifiers are double-quoted ("user" is the correct form because user is reserved).
• The conditional-compile flag in the CLI means PostgreSQL builds gracefully degrade if libpq isn’t present at compile time — check ./nibiru -v to confirm support.

Connection pooling

There’s no built-in pool. For high-traffic apps use:

• MariaDB / MySQL: ProxySQL or HAProxy in front of the DB.
• PostgreSQL: PgBouncer in transaction-pooling mode.

Nibiru opens one connection per request, so a pooler is usually all you need.

Common pitfalls

• is.active = false silently disables connections — check this when queries return null.
• Generator on in production. With [GENERATOR] database = true, every request rewrites your model files. Turn it off after deploys.
• Mixed drivers. Picking mysql when running PostgreSQL gets you a successful Pdo::fetchAll that returns [] — the connection silently fails. Always confirm with SELECT 1 in a smoke test.