Skip to content

Adapters

ORM::ActiveRecord supports three database backends: PostgreSQL, MySQL, and SQLite. Tests run against all three on every CI build. Application code is the same regardless of the backend.

Selecting an adapter

Two mechanisms drive adapter selection. They're checked in this order:

  1. DATABASE_URL environment variable (preferred)
  2. config/application.json (fallback)

DATABASE_URL

1
2
3
4
DATABASE_URL=postgres://user:pass@host:5432/dbname?schema=public
DATABASE_URL=mysql://root:secret@127.0.0.1:3306/dbname
DATABASE_URL=sqlite:db/test.sqlite3
DATABASE_URL=sqlite::memory:

Recognised schemes:

Scheme(s) Adapter
pg, postgres, postgresql PostgreSQL
mysql, mysql2, mariadb MySQL
sqlite, sqlite3 SQLite

Query-string parameters are passed through as adapter options (?schema=public, ?sslmode=require, etc.).

config/application.json

If DATABASE_URL is unset, ORM::ActiveRecord reads config/application.json. Three example templates live in config/:

1
2
3
cp config/application.json-postgresql-example config/application.json
cp config/application.json-mysql-example      config/application.json
cp config/application.json-sqlite-example     config/application.json

The shape is per-environment, with one or more named connections per environment (primary is the default connection):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
{
  "test": {
    "parallel": 4,
    "primary": {
      "adapter": "pg",
      "host": "localhost",
      "port": 5432,
      "name": "ar_test",
      "user": "postgres",
      "password": "",
      "schema": "public"
    }
  },
  "development": {
    "primary": {
      "adapter": "pg",
      "name": "ar_development",
      "user": "postgres"
    }
  }
}

adapter must be one of pg / mysql / sqlite (the same aliases as DATABASE_URL schemes are accepted). MySQL has no separate schema concept — the name field IS the schema. SQLite only needs name (the file path) or ":memory:".

The active environment is chosen by AR_ENV, then RAKU_ENV (bin/active-record defaults to development; the test suite uses test). When DATABASE_URL is set it overrides the active environment's primary connection; any other named connection is still resolved from config/application.json.

Named connections and connects-to

An environment can hold more than one named connection. primary is the default; add others alongside it:

1
2
3
4
5
6
{
  "production": {
    "primary":   { "adapter": "pg", "name": "ar_production" },
    "analytics": { "adapter": "pg", "name": "ar_analytics", "host": "analytics-db" }
  }
}

A model uses primary unless you bind it to another connection with connects-to, declared right after the class:

1
2
3
4
5
6
7
class Event is Model {
  method table-name { 'events' }
}

Event.connects-to('analytics');

Event.connection-name;     # 'analytics'

Each model routes its own queries through its bound connection; DATABASE_URL, when set, overrides only the active environment's primary, so other named connections still resolve from config/application.json.

Connection options (PostgreSQL)

A connection block (or DATABASE_URL query string) may carry extra PostgreSQL connection parameters, passed straight through to the driver:

Key Effect
sslmode disable / prefer / require / verify-full / …
sslrootcert CA certificate path for verified TLS.
sslcert / sslkey Client certificate / key paths.
application_name Labels the session in pg_stat_activity and server logs. 
1
2
3
4
5
{ "production": { "primary": {
  "adapter": "pg", "name": "app", "host": "db",
  "sslmode": "require", "sslrootcert": "/etc/ssl/ca.pem",
  "application_name": "app-web"
} } }

1
DATABASE_URL=postgres://app@db/app?sslmode=require&application_name=app-web

Per-connection migration path

A connection may set its own migration directory with a migration-path (or migrations) key; it defaults to db/migrate. active-record migrates each connection from its configured path.

1
2
3
{ "production": { "analytics": {
  "adapter": "pg", "name": "events", "migration-path": "db/migrate_analytics"
} } }

Statement caching

By default each query prepares a fresh statement, runs it, and disposes it immediately. Set prepared_statements to true to keep prepared statements and reuse them across queries, keyed by their SQL text. A reused statement is reset after each call (releasing the SQLite read lock and resetting MySQL result state) and re-executed with fresh bind values.

prepared_statement_cache_size bounds the cache (default 1000). When the cache is full the least-recently-used statement is evicted and disposed. The cache is per connection and is cleared when the connection disconnects.

Key Effect
prepared_statements true / false (default false).
prepared_statement_cache_size Max cached statements per connection (1000). 
1
2
3
4
{ "production": { "primary": {
  "adapter": "pg", "name": "app",
  "prepared_statements": true, "prepared_statement_cache_size": 500
} } }

Statement timeouts (PostgreSQL)

A connection block may set PostgreSQL session timeouts, applied with SET on connect. Values are passed through verbatim, so any form PostgreSQL accepts works ('5s', '500ms', or a bare millisecond count).

Key Effect
statement_timeout Aborts any statement running longer than this.
lock_timeout Aborts a statement waiting this long for a lock.
idle_in_transaction_session_timeout Ends a session idle this long inside a transaction. 
1
2
3
4
5
{ "production": { "primary": {
  "adapter": "pg", "name": "app",
  "statement_timeout": "5s", "lock_timeout": "2s",
  "idle_in_transaction_session_timeout": "10s"
} } }

Legacy flat config. The older single-database shape — { "db": { … } } — is still accepted and auto-promoted to the active environment's primary connection (with a deprecation warning). Migrate to the per-environment shape.

Multiple databases

An environment may declare more than one named connection:

1
2
3
4
5
6
{
  "production": {
    "primary":   { "adapter": "pg", "name": "app",        "user": "app" },
    "analytics": { "adapter": "pg", "name": "app_events",  "user": "app" }
  }
}

DB.shared(:name<analytics>) returns a connection by name; DB.shared (no name) is primary. Bind a model to a non-primary connection with the connects-to class method:

1
2
3
class Event is Model {
  Event.connects-to('analytics');
}

Every query a bound model runs — class finders, relations, and its instances' saves — routes to that connection. Unbound models use primary.

Roles (writing / reading)

A model can bind a connection per role — typically a primary for writes and a replica for reads:

1
2
3
class User is Model {
  User.connects-to(database => { writing => 'primary', reading => 'replica' });
}

Queries use the writing role by default. Wrap work in connected-to(role:) to switch — for example to read from the replica:

1
User.connected-to(role => 'reading', { User.all });   # runs against 'replica'

Shards

connects-to(shards: ...) binds a writing/reading pair per shard, and connected-to(shard:) selects one (role and shard compose):

1
2
3
4
5
6
7
8
9
class Order is Model {
  Order.connects-to(shards => {
    default   => { writing => 'primary',  reading => 'replica' },
    shard_one => { writing => 'shard1',   reading => 'shard1_replica' },
  });
}

Order.connected-to(shard => 'shard_one', { Order.count });
Order.connected-to(shard => 'shard_one', role => 'reading', { Order.all });

connected-to blocks nest (an inner block inherits the outer role/shard unless it overrides them), and connected-to(connection => 'name') bypasses role/shard to force a specific connection. Model.connected-to-many(@classes, role => ..., shard => ...) { ... } switches the context for a block covering several models.

Automatic role selection

For request-based read/write splitting, DatabaseSelector (use ORM::ActiveRecord::Connection::Switching;) decides a role: writes — and reads for a short window after a write, so a user sees their own change — go to writing, everything else to reading. A web middleware wraps each request:

1
2
3
4
5
6
7
my $selector = DatabaseSelector.new(delay => 2);

# per request:
Model.connected-to(role => $selector.role-for(:write($is-mutating)), {
  handle-request();
});
$selector.record-write if $is-mutating;

The parallel key (test environment only) sets how many per-worker database copies active-record db:create --parallel / test.raku --parallel create; see Tests.

Adapter-specific notes

PostgreSQL

The default adapter. Uses $N bind placeholders and emits INSERT … RETURNING id for surrogate-key reads. Schema introspection goes through information_schema. Boolean and timestamp values round-trip directly.

MySQL

Uses ? bind placeholders and LAST_INSERT_ID() for surrogate-key reads (MySQL has no RETURNING). Booleans are stored as TINYINT(1). Identifiers are quoted with backticks.

Two MySQL-specific behaviours to be aware of:

  • Timestamps use DATETIME(6). Plain DATETIME truncates fractional seconds, which would cause created_at drift on round-trip. Microsecond precision avoids it.
  • DATETIME columns are written and read in the local timezone. DBDish::mysql parses DATETIME values with :timezone($*TZ), so coerce-write emits local-TZ strings to keep round-trips symmetric.

libmysqlclient discovery

DBDish::mysql searches for libmysqlclient versions 16..21 by default. Recent installs ship version 24+, which falls outside that range. ORM::ActiveRecord's MySqlAdapter.connect works around this by setting DBIISH_MYSQL_LIB automatically, checking common Homebrew/apt paths.

If it still can't find the library, set DBIISH_MYSQL_LIB yourself:

1
2
3
4
5
# macOS / Homebrew
export DBIISH_MYSQL_LIB=$(brew --prefix mysql-client)/lib/libmysqlclient.dylib

# Debian/Ubuntu
export DBIISH_MYSQL_LIB=/usr/lib/x86_64-linux-gnu/libmysqlclient.so

SQLite

Uses ? bind placeholders. Surrogate keys come from INSERT … RETURNING id on SQLite ≥ 3.35; older versions fall back to last_insert_rowid(). Schema introspection goes through pragma_table_info. Booleans are stored as INTEGER 0/1 and dates/datetimes as ISO TEXT.

SQLite requires LIMIT whenever OFFSET is used, so the adapter emits LIMIT -1 OFFSET N when an offset is set without a limit. (-1 means unbounded in SQLite.)

The database field accepts a file path (created on first connect) or the literal ":memory:" for an ephemeral in-process database — useful in tests.

Connection lifecycle

The adapter on DB.shared exposes a handful of lifecycle primitives. The shared handle connects lazily on first use, so most callers never touch these. They exist for tests, long-running daemons, and code that needs to recycle a connection after an out-of-band drop.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
use ORM::ActiveRecord::DB;

my $db = DB.shared;

$db.exec('SELECT 1');     # forces the lazy connect
$db.is-connected;         # True

$db.disconnect;           # closes the handle, returns True
$db.is-connected;         # False
$db.disconnect;           # no-op on an already-closed handle, returns False

$db.reconnect;            # disconnect (if needed) + connect
$db.is-connected;         # True

Auto-reconnect — once a handle has been built, the next exec / exec-stmt after a disconnect will re-establish the connection automatically. The example above could drop the explicit reconnect call and the next query would still succeed.

1
2
$db.disconnect;
my @rows = $db.exec('SELECT 2');   # exec auto-reconnects, returns 2

Health probes — is-active runs a trivial round-trip and returns False (never throws) on a dropped connection; verify reconnects once if the connection is dead and returns whether it is live afterward.

1
2
$db.is-active;            # True on a live connection, False on a dropped one
$db.verify;               # reconnects if needed; True if live afterward

Deterministic shutdown: close disconnects a connection's primary adapter and any built pool, without forcing a lazy pool into existence. DB.disconnect-shared closes every process-wide shared connection and clears the registry, so the next DB.shared rebuilds from config.

1
2
3
4
$db.close;                # close this connection's adapter (and pool, if built)

DB.disconnect-shared;     # close every shared connection, drop the registry
DB.shared;                # rebuilds a fresh connection from config

An END phaser runs DB.disconnect-shared for you at process exit, so native driver handles (notably libpq) are freed while the runtime is healthy rather than during the shutdown garbage collection, where the finalization order is undefined and the PostgreSQL driver can crash mid-teardown.

Thread safety

A single connection is one wire-protocol stream, and two threads interleaving statements on it would desync that stream. Each adapter therefore serializes access with a reentrant per-connection lock. Statement execution (exec, exec-stmt, exec-stmt-hash), transaction blocks, and the lifecycle calls (disconnect, reconnect) each hold the lock for their full duration.

Two consequences follow:

  • A transaction block holds the lock from BEGIN through COMMIT, so no other thread's statement can land inside the transaction. The lock is reentrant, so the statements inside the block still run.
  • Sharing one connection across threads is safe but serial: only one statement or transaction block runs at a time. For parallel query throughput, use connection pooling, which hands each thread its own connection. A pooled connection has a single user at a time, so its lock is uncontended.

Connection pooling

For concurrent work, a connection pool hands out separate connections — each a full adapter with its own driver handle and transaction state. Size it from config (the pool key, with min-threads, checkout-timeout, idle-timeout, reaping-frequency, verify-timeout, and verify-idle-after as further knobs):

1
2
3
4
{ "production": { "primary": {
  "adapter": "pg", "name": "app", "host": "db",
  "pool": 10, "checkout-timeout": 5, "idle-timeout": 300
} } }

with-connection checks a connection out for the block and back in afterward (even if the block throws):

1
2
3
DB.shared.with-connection: -> $conn {
  $conn.exec('SELECT count(*) FROM orders');
};

The lower-level checkout / checkin are also available via DB.shared.pool:

1
2
3
4
my $pool = DB.shared.pool;
my $conn = $pool.checkout;          # waits up to checkout-timeout for a free slot
LEAVE $pool.checkin($conn);
$conn.exec('...');

Connections are created lazily up to the pool size. checkout verifies the connection first, so one dropped while idle is reconnected transparently; a checkout that can't get a free connection within checkout-timeout throws. pool.reap closes connections idle past idle-timeout (down to min-threads), and pool.disconnect-all closes them all.

The checkout health probe is a SELECT 1 round-trip, and by default it runs on every checkout. Set verify-idle-after (seconds) to reserve the probe for connections that have sat idle past that threshold. A connection used more recently checks out without the round-trip, which the statement layer's own dropped-connection recovery makes safe.

Request-scoped connections

A concurrent server wants every query in one request to run on the same pooled connection, so the request sees a consistent view (and one transaction spans it). Connection::Registry provides that: a request-scoped set of checked-out connections, keyed by connection name. The first query on a named connection checks one out of that connection's pool and caches it; every later query on the same connection reuses it. release-all returns them to their pools when the request ends.

Bind an instance to the $*AR-CONNECTION-REGISTRY dynamic variable around a request; models and relations route through it automatically:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
use ORM::ActiveRecord::Connection::Registry;

my $registry = ORM::ActiveRecord::Connection::Registry.new;
{
  my $*AR-CONNECTION-REGISTRY = $registry;
  # every Model / relation query in here checks out (once) and reuses
  # its connection's pooled connection
  User.create({ ... });
  Order.all.list;
}
$registry.release-all;   # return every checked-out connection to its pool

Because it is keyed by connection name, a multi-database app routes each model to its own pooled connection instead of forcing everything onto one. A connects-to or connected-to model routes through the registry under its own connection name. When no registry is bound, models fall back to the shared connection, so scripts and tests need no setup. A $*AR-DB-OVERRIDE, which pins one operation to a single checked-out connection (async queries and the raw-SQL helpers set it), still takes precedence over the registry.

The current connection

Every query path resolves its connection through one method, DB.current:

1
2
DB.current;                # the primary connection
DB.current(:name<replica>);

It returns, in order of precedence:

  1. $*AR-DB-OVERRIDE, when an operation is pinned to one checked-out connection (async queries and the raw-SQL helpers set it),
  2. the connection the bound $*AR-CONNECTION-REGISTRY hands out for that name, or
  3. the process-wide shared connection, outside any request.

Model, relation, and association queries all route through DB.current, so binding a registry (or an override) redirects them without any per-model wiring. When application code drops to raw SQL inside a request, call DB.current.exec(...) rather than DB.shared.exec(...), so the raw statement joins the request's pooled connection instead of driving the shared connection from every request thread at once.

Query cache

The query cache memoises read results for the duration of a unit of work. While it is on, repeating the same SELECT (same SQL and bind values) returns the first result without going back to the database. Any write clears the cache, so a later read never serves stale rows.

The cache is off by default. Wrap a unit of work in cache to turn it on for that block; it is cleared and turned back off when the block exits:

1
2
3
4
DB.shared.cache: {
  Order.where(:status<open>).all;   # hits the database
  Order.where(:status<open>).all;   # served from the cache
};

uncached forces queries inside it to bypass the cache (and not populate it), even within a surrounding cache block:

1
2
3
DB.shared.uncached: {
  Order.find($id);                  # always hits the database
};

The cache can also be controlled directly: enable-query-cache and disable-query-cache toggle it (disabling also clears it), and clear-query-cache empties it without changing the on/off state. Each pooled connection has its own cache, and disconnecting clears it.

Schema metadata cache

Column introspection (get-fields, column-details) reads from information_schema (or pragma_table_info on SQLite), which is costly to repeat on every model operation. The adapter memoises this metadata per table for the life of the connection.

The cache invalidates itself when it can no longer be trusted: any schema-change statement (CREATE, ALTER, DROP, RENAME) run through the adapter clears it, and disconnecting drops it with the rest of the connection state. Call clear-schema-cache to empty it explicitly, for example after issuing DDL through a route the adapter cannot observe.

Advisory locks

An advisory lock is a named, application-level lock the database tracks but does not tie to any row or table. with-advisory-lock takes the lock for the duration of a block and releases it on exit (including when the block throws):

1
2
3
DB.shared.adapter.with-advisory-lock('import-orders', {
  # only one process runs this block at a time
});

A connection that already holds the lock blocks others. Pass a timeout (in seconds) to give up waiting; with-advisory-lock then throws X::AdvisoryLock rather than blocking forever:

1
2
3
$adapter.with-advisory-lock('import-orders', :timeout(5), {
  ...
});

The lower-level get-advisory-lock(name, :timeout) and release-advisory-lock(name) return a Bool and are available when a block does not fit. PostgreSQL hashes the name into a 64-bit key for pg_advisory_lock; MySQL uses GET_LOCK. SQLite has no advisory-lock primitive, so supports-advisory-locks is False there and with-advisory-lock runs the block without locking.

Set advisory_locks to false in a connection block to disable locking entirely; with-advisory-lock then just runs the block.

Key Effect
advisory_locks true / false (default true).

Raw SQL with bound parameters

sanitize-sql and sanitize-sql-array turn a SQL fragment + values into a ready-to-execute statement with adapter-correct placeholders. They're how ORM::ActiveRecord avoids string-interpolating values into SQL internally; the same helpers are available for application code that needs to drop down to raw SQL.

The examples below call DB.shared for brevity. Inside a request, prefer DB.current (see the current connection) so the raw statement runs on the request's pooled connection rather than the shared one.

Positional ? placeholders

1
2
3
4
5
6
my $stmt = DB.shared.sanitize-sql-array([
  'name = ? AND age = ?',
  'Bob', 30,
]);

DB.shared.exec-stmt($stmt);

Each ? consumes the next value in order. PostgreSQL rewrites them to $N; MySQL and SQLite keep them as ?. An arity mismatch (too many or too few values for the ?s in the template) raises.

Named :name placeholders

1
2
3
4
my $stmt = DB.shared.sanitize-sql-array([
  'name = :name AND age = :age',
  { name => 'Bob', age => 30 },
]);

Names that appear in the template but are missing from the hash raise. You can't mix ? and :name in the same template.

String-literal preservation

Anything inside single quotes — including ? characters that look like placeholders, escaped quotes (''), and :name-shaped tokens — is passed through verbatim. sanitize-sql-array only substitutes placeholders that appear outside string literals.

1
2
3
4
5
DB.shared.sanitize-sql-array([
  q{name = ? AND label = '???' AND tag = ':notbound'},
  'Bob',
]);
# → only one ? was actually a placeholder; one bind, 'Bob'

sanitize-sql dispatch

sanitize-sql accepts any of three shapes and dispatches on type:

Input Behavior
Str Wraps the SQL with no binds — useful for static SELECT 1-style queries.
Positional Equivalent to sanitize-sql-array.
SqlStmt Returned unchanged.