/ 
DBIx-Fast-0.22
/ DBIx::Fast

NAME

DBIx::Fast - DBI fast & easy

SYNOPSIS

use DBIx::Fast;

# Connect via a DBI DSN
my $db = DBIx::Fast->new(
    dsn      => 'dbi:MariaDB:dbname=mydb;host=localhost',
    user     => 'root',
    password => 'secret',
);

# Connect via a URI (postgres|postgresql|mariadb|mysql|sqlite)
my $db = DBIx::Fast->new( dsn => 'mariadb://root:secret@localhost:3306/mydb' );

# SQLite shortcut
my $db = DBIx::Fast->new( SQLite => '/path/to/db.sqlite' );

# Dependency injection
my $db = DBIx::Fast->new( db => $connector, driver => 'SQLite' );

# Queries
$db->all('SELECT * FROM users WHERE active = ?', 1);
my $rows = $db->results;

my $user = $db->hash('SELECT * FROM users WHERE id = ?', $id);
my $name = $db->val('SELECT name FROM users WHERE id = ?', $id);
my @ids  = $db->flat('SELECT id FROM users');
my $total = $db->count('users', { active => 1 });

# CRUD
$db->insert('users', { name => 'Alice', status => 1 }, time => 'created_at');
$db->up('users', { name => 'Bob' }, { id => 1 });
$db->up('users', { name => 'Bob' }, { id => 1 }, 'updated_at');
$db->delete('users', { id => 1 });

# Transactions
$db->txn(sub {
    $db->insert('orders', { total => 100 });
    $db->insert('order_items', { order_id => $db->last_id, product => 'Widget' });
});

# Named parameters
$db->execute('SELECT * FROM users WHERE name = :name', { name => 'Alice' });

# Row iterator (does not load all rows into memory)
my $rs = $db->query('SELECT * FROM logs ORDER BY id DESC LIMIT 1000');
while (my $row = $rs->hash) {
    process($row);
}

# Transactions with automatic deadlock retry, savepoints and isolation
$db->txn(sub {
    $db->insert('accounts', { id => 1, balance => 100 });
    $db->transaction->savepoint('sp1');
    $db->up('accounts', { balance => 90 }, { id => 1 });
}, { max_retries => 3, isolation => 'SERIALIZABLE' });

# Cached reads (TTL + tag invalidation; CRUD writes auto-invalidate the table)
my $cdb = DBIx::Fast->new( SQLite => 'app.db', cache => { default_ttl => 60 } );
my $row = $cdb->cached(ttl => 60, tag => 'users')
              ->hash('SELECT * FROM users WHERE id = ?', $id);

# Query profiling
$db->tracker;                                 # enable tracking
$db->all('SELECT * FROM users');
my $stats = $db->tracker->get_stats;          # timings, slow queries, by type

DESCRIPTION

DBIx::Fast is a lightweight database access layer built on DBI and SQL::Abstract. It sits between a bare DBI handle and a full ORM: you keep writing SQL, but the everyday plumbing - connection resilience, transactions, result shaping, caching, profiling and identifier safety - is handled for you, behind one small API that works the same across SQLite, MariaDB, MySQL and PostgreSQL.

Built with Object::Pad; requires Perl v5.38 or later.

FEATURES

  • Multi-driver, dialect-aware - one API over SQLite, MariaDB, MySQL and PostgreSQL. Driver differences (identifier quoting, upsert syntax, last-insert id, isolation-level SQL, schema introspection) are handled internally.

  • Resilient connections - fork-safe handles with lazy connect, throttled server-side ping to detect dropped connections, automatic reconnect, and a guard that fails loudly (rather than silently committing) if a connection is lost mid-transaction. See DBIx::Fast::Connector.

  • Transactions that retry - txn runs a block in a transaction with automatic deadlock / lock-wait retry (detected by driver error code), nested transactions, named savepoints and per-transaction isolation levels. See DBIx::Fast::Transaction.

  • Result caching - optional in-process (or CHI-backed) cache of query results with TTL and tag-based invalidation; CRUD writes auto-invalidate the affected table. See DBIx::Fast::Cache and DBIx::Fast::Cached.

  • Query profiling - a built-in tracker (timings, slow-query detection, per-type statistics) plus driver-native diagnostics such as EXPLAIN and index / connection analysis. See DBIx::Fast::Profiler, DBIx::Fast::Profile::MariaDB, DBIx::Fast::Profile::mysql and DBIx::Fast::Profile::Pg.

  • Security-conscious - values always go through placeholders; identifiers are validated and quoted; count operators, isolation levels and savepoint names are whitelisted; error messages can optionally redact quoted literals (PII / PCI). See "SECURITY".

  • Flexible result shapes - all / hash / val / flat / array, a memory-efficient row iterator (query), and both positional (?) and named (:name) parameters.

When to use it

DBIx::Fast is a good fit when you want to write SQL directly but not re-implement connection handling, transactions, caching and safety every time. It is not an ORM: there are no result classes, relationships or migrations. If you need to map an object graph, reach for a full ORM instead (see "SEE ALSO").

Supported drivers

SQLite (DBD::SQLite), MariaDB (DBD::MariaDB), MySQL (DBD::mysql) and PostgreSQL (DBD::Pg). The test suite runs against SQLite always, and against live MariaDB and PostgreSQL when DBIX_FAST_TEST_MARIADB / DBIX_FAST_TEST_PG are set.

CONSTRUCTOR

new

my $db = DBIx::Fast->new(%args);

Accepted parameters:

dsn - a native DBI DSN (dbi:DRIVER:..., case-insensitive) or a URI. URI userinfo is optional, so a credential-less mariadb://localhost/db (socket / peer authentication) is accepted as well as mariadb://user:pass@host:port/db.
SQLite - Path to SQLite database file (shortcut). For an in-memory database use SQLite => ':memory:' or the DSN dbi:SQLite::memory:.
db - Database name string or pre-built DBIx::Fast::Connector object
driver - Database driver: SQLite, Pg, MariaDB, mysql
user, password, host, port - Connection credentials
RaiseError, PrintError, AutoCommit - DBI attributes (defaults: 1, 0, 1)
mysql_enable_utf8 - Enable UTF-8 on MySQL/MariaDB (mapped to the driver-appropriate attribute: mariadb_enable_utf8mb4 / mysql_enable_utf8mb4). Default: 0
tn - Enable table-name validation against the schema (default: 0)
abstract - Enable SQL::Abstract for CRUD (default: 1)
cache - Enable result caching: a hashref of DBIx::Fast::Cache options (e.g. { default_ttl => 60, max_size => 1000 }). Omit to disable. See "cached".
max_errors - Cap on the retained error history, FIFO (default: 100)
errors_redact - Redact quoted literals in captured error messages, and the bind values recorded with each error, for PII / PCI (default: 0)
on_error - Coderef called with every error entry as it is recorded (see "errors"), e.g. to forward it to a logger or error tracker. Exceptions thrown by the callback are caught and warned, never propagated into DBI.
strict_reads - With RaiseError => 0, make the read methods (all, hash, val, flat, array, query, count, exists, kv, all_by, execute) throw read failed on a failed prepare or execute, like the write methods already do, instead of returning an empty result (default: 0). Without it a failed read returns [] / undef and only records the error.
timestamps - Automatic created/updated columns for the CRUD writers:
timestamps => {
    default => { created => 'created_at', updated => 'updated_at' },
    tables  => {
        orders => { created => 'date_purchased', updated => 'last_modified' },
        logs   => {},                       # no timestamps for this table
    },
    source  => 'host',    # 'host' (now()), 'utc' (now_utc()) or 'server'
}

insert, insert_ignore, find_or_create and insert_many fill both columns, update/up the updated one, upsert/upsert_many both on insert and only the updated one on conflict (created is never overwritten). A value the row already sets wins, and the caller's hash is not modified. A tables entry replaces default for that table. source => 'server' uses the database's CURRENT_TIMESTAMP instead of the host's clock.

json_columns - Columns stored as JSON text:
json_columns => { '*' => ['meta'], tareas => ['tarea', 'result'] }

On writes (every CRUD method), a hashref/arrayref value of a listed column ('*' = in any table) is encoded with Cpanel::JSON::XS (JSON::PP as a fallback), canonically, before validation. On reads (all, hash, execute), columns with those names are decoded when the value looks like a JSON object or array; other text and malformed JSON are left untouched. Non-ASCII round-trips on every driver, whatever its UTF-8 flags. val, flat and query return the raw text.

on_slow_query - { ms => 200, cb => sub ($q) {...} }: call cb for every statement that took ms milliseconds or more, with { sql => ..., binds => [...], ms => 312.4, caller => 'lib/App.pm:42' } (caller is the application line that ran it). Optional keys: explain (attach the query plan) and redact (hide the binds; defaults to errors_redact). A hook that dies is warned about; the query result is unaffected. See "MONITORING".
metrics - 1 or a label: count statements, durations, slow statements and errors for "metrics_text" (Prometheus). The label defaults to the registry name ("connect") or default. See "MONITORING".
sql_stats - 1 or a number: count executions, total and maximum time per statement for "sql_stats", keeping that many distinct statements (1 = 500). See "MONITORING".
on_repeated_query - { n => 10, cb => sub ($r) {...} }: call cb when one statement runs n times within a "scope_start" .. "scope_end" scope (an N+1 loop). See "MONITORING".
after_write - Coderef called after every completed CRUD write (insert, insert_ignore, update/up, delete, upsert, insert_many, upsert_many) with a hashref describing it:
after_write => sub ($w) {
    # { op => 'update', table => 'orders', data => {...}, where => {...},
    #   rows => 1 }   (inserts also carry last_id; upserts conflict_keys)
    $audit->record($w);
}

data is what was written (after timestamps and json_columns). Raw SQL (exec) is not reported - DBIx::Fast does not know its table. The write is already done when the hook runs: a hook that dies is reported with warn and never fails or undoes the write.

on_connect_sql - Arrayref of trusted SQL statements run on every (re)connect, e.g. [ "SET NAMES utf8mb4", "SET time_zone = '+00:00'" ]. Unlike a one-off exec after construction, the session settings survive a reconnect.
profile_output - Output routing for the profiler subsystem, applied to both "tracker" and "profiler": 'text' (default, formatted reports), 'json' (one JSON line per event) or a coderef called as $cb->($event, $data, $meta). See DBIx::Fast::Output.
profile_output_fh - Filehandle the text/json profiler output is written to (default: the currently selected handle, normally STDOUT). For a log file: open my $fh, '>>', $path; chmod 0600, $path; - profiler events carry cleartext SQL and bind values.
trace, profile - DBI tracing / profiling options

CONNECTION REGISTRY

# once, at startup
DBIx::Fast->configure(
    app  => { dsn => $conf->{dsn},  user => $conf->{user}, password => $conf->{pass} },
    logs => { dsn => 'mariadb://...', RaiseError => 0, strict_reads => 1 },
);

# anywhere: models, jobs, scripts
my $db = DBIx::Fast->connect('app');

configure

DBIx::Fast->configure(name => { constructor args }, ...);

Registers named connections (nothing connects yet). Configuring a name again replaces its arguments and drops the instance built from the old ones.

connect

my $db = DBIx::Fast->connect('app');

The process-wide instance for a configured name: built on first use and then returned to every caller, so a process holds one connection per name instead of one per object that needs the database. In a forked child (preforking servers, job workers) the first connect builds the child's own instance; the inherited one is neutralized (see "FORK SAFETY" in DBIx::Fast::Connector). An unknown name throws a DBIx::Fast::X::Usage.

configured

my @names = DBIx::Fast->configured;

forget

DBIx::Fast->forget('app');

Drops a name's instance and configuration (an instance still referenced elsewhere keeps working).

configure_file

# each connection's arguments at a path of the JSON file
DBIx::Fast->configure_file("/home/pws/conf/db.$stage.json", pws => 'db');
DBIx::Fast->configure_file('/opt/itti/conf/iTTi.json', itti => 'db.itti');

# or the whole file is name => { constructor args }
DBIx::Fast->configure_file('/etc/myapp/databases.json');

"configure" from a JSON file (UTF-8), read once at startup instead of in every place that needs a connection. A dotted path walks nested objects and must lead to an object. Errors are DBIx::Fast::X::Usage; an invalid file is reported without the parser's message, which would quote the file's content (credentials).

disconnect_all

DBIx::Fast->disconnect_all;

Disconnects and drops every instance this process built; the configuration stays, so the next connect reconnects. For orderly shutdown and tests.

MONITORING

my $db = DBIx::Fast->new(
    dsn           => $dsn,
    metrics       => 1,
    on_slow_query => { ms => 250, cb => sub ($q) { $log->warn("slow $q->{ms} ms: $q->{sql}") } },
);

# Mojolicious: one /metrics route for every registry connection
$r->get('/metrics' => sub ($c) {
    $c->render(text => DBIx::Fast->metrics_text, format => 'txt');
});

Both options are off by default and then cost nothing; switched on they add about 3 microseconds per statement (timing it, and the cached-statement fast path of val/hash/all is skipped): some 5% of a query over the network, more on an in-memory SQLite one. Statements are timed in every path: reads, writes, exec, stream (the execute; rows are read later). A statement that fails counts as an error, not as a query; a cached hit never reaches the database and counts as a cache hit.

Slow statements

on_slow_query => {
    ms      => 100,
    explain => 1,                                  # development
    redact  => sub ($sql, @binds) { map { /\@/ ? '<email>' : $_ } @binds },
    cb      => sub ($q) { ... },
}

cb receives one hashref per slow statement:

sql, binds, ms - the statement, its bind values and its duration in milliseconds.
caller - "file:line" of the first frame outside DBIx::Fast: the application code that ran the statement.
explain - with explain => 1, the plan of a slow SELECT (or WITH) as rows of hashrefs, the driver's own columns: EXPLAIN on MariaDB/MySQL (type, key, rows...) and Pg (QUERY PLAN), EXPLAIN QUERY PLAN on SQLite (detail). The plan costs one more statement on the same connection (not timed, not counted, its errors ignored), so it is meant for development. undef when it fails or could disturb the connection: another result set still being read (stream, query), a Pg transaction, or Pg with redact on (Pg plans print the bound values).

redact hides the binds from cb: 1 replaces each one with <redacted>, a coderef receives ($sql, @binds) and returns the list to report (if it dies, a single <redacted> is reported). Without the key it follows errors_redact.

metrics

my $m = $db->metrics;
# { queries => 1234, seconds => 0.84, slow => 2,
#   errors  => { Duplicate => 3, Deadlock => 1 },
#   buckets => { 0.001 => 900, 0.005 => 1200, ... },   # cumulative, seconds
#   cache_hits => 50, cache_misses => 9 }               # with a cache

A snapshot of the counters, or undef when metrics is off. Error keys are the DBIx::Fast::X class without its prefix.

reset_metrics

Zeroes the counters.

metrics_text

print $db->metrics_text;           # this connection
print DBIx::Fast->metrics_text;    # every registry connection of this process

The counters in the Prometheus text format: dbix_fast_queries_total, dbix_fast_query_duration_seconds (histogram), dbix_fast_slow_queries_total, dbix_fast_errors_total{class=...} and, with a cache, dbix_fast_cache_hits_total / dbix_fast_cache_misses_total. Every sample carries db (the label) and pid: in a preforking server each worker counts its own statements and a scrape reaches one worker, so the pid label keeps each worker's counters monotonic - aggregate with sum by (db) (rate(...)). Empty when no connection has metrics on.

A scrape still sees only the worker that answered it: under hypnotoad or another preforking server, successive scrapes land on different workers, and a worker that never answers is never seen. To export them all, let each worker write its own text and serve the lot from any of them (the same applies to "sql_stats", also per process):

# every worker, every 15 s (Mojolicious)
Mojo::IOLoop->recurring(15 => sub {
    Mojo::File->new("/run/app-metrics/$$.prom")->spew(DBIx::Fast->metrics_text);
});

# the /metrics route: every live worker's file
$r->get('/metrics' => sub ($c) {
    my @files = grep { kill 0, $_->basename('.prom') }
                Mojo::File->new('/run/app-metrics')->list->each;
    $c->render(text => DBIx::Fast->metrics_merge(map { $_->slurp } @files), format => 'txt');
});

metrics_merge

my $text = DBIx::Fast->metrics_merge(@worker_texts);

Several "metrics_text" outputs as one exposition. Concatenating them is not valid Prometheus text (a family's samples must come in one group, under one # HELP/# TYPE); this groups them per family. The pid label keeps the workers' samples apart.

metrics_on

True when the metrics option is set.

metrics_label

The db label of the samples.

sql_stats

my $db = DBIx::Fast->new(dsn => $dsn, sql_stats => 1);
...
for my $s (@{ $db->sql_stats(top => 10) }) {
    printf "%8.1f ms %6d x  %s\n", $s->{seconds} * 1000, $s->{count}, $s->{sql};
}
# [ { sql => 'SELECT * FROM t WHERE id IN (?, ...)', count => 812,
#     seconds => 1.94, max => 0.21 }, ... ]

Where the database time goes, by statement: the sql_stats option keeps, per distinct SQL, its executions, total seconds and slowest run. order is seconds (default), count or max, descending; top limits the list. undef when the option is off.

The key is the SQL text with placeholder lists collapsed: IN (?) and IN (?, ?, ?) are both IN (?, ...), and multi-row VALUES of any length one statement. SQL that interpolates its values (instead of binding them) is a new statement for every value: once the limit of distinct statements is reached (sql_stats => 1 = 500), new ones are added up under (other) - memory stays bounded, and a large (other) points at that interpolation. The statements are not exported by "metrics_text": SQL text as a Prometheus label would be unbounded cardinality.

reset_sql_stats

Empties the sql_stats counters.

scope_start

scope_end

my $db = DBIx::Fast->new(
    dsn               => $dsn,
    on_repeated_query => { n => 20, cb => sub ($r) {
        # { sql => 'SELECT * FROM stock WHERE product_id = ?', count => 20,
        #   seconds => 0.012, caller => 'lib/App/Controller/Cat.pm:88' }
        $log->warn("N+1? $r->{sql} x$r->{count} at $r->{caller}");
    } },
);

# Mojolicious: one scope per request
$app->hook(before_dispatch => sub ($c) { $c->app->db->scope_start });
$app->hook(after_dispatch  => sub ($c) {
    my $s = $c->app->db->scope_end or return;
    # { queries => 307, seconds => 0.195, statements => 4,
    #   repeated => [ { sql => ..., count => 300, seconds => 0.17 }, ... ] }
});

A scope counts every statement run between scope_start and scope_end (the same keys as "sql_stats"; it works without that option). scope_end closes it and returns the queries, seconds and distinct statements of the scope, with repeated listing those run more than once, most repeated first. undef when no scope was open; a new scope_start discards an open one.

on_repeated_query fires inside a scope, once per statement, when it reaches n runs. caller is the application line of that n-th run: usually the loop that should become one IN (?) query or a join. A hook that dies is warned about.

While a scope is open, or with sql_stats on, every statement is timed and counted: the cost of metrics plus under a microsecond. The counters belong to the object: in a non-blocking server that interleaves requests on the same connection object, scopes overlap and count each other's statements.

ACCESSORS

db

DBIx::Fast::Connector instance (read/write).

dbd

Database driver name: SQLite, Pg, MariaDB, or mysql (read-only).

dsn

Processed DSN string (read-only).

Q

SQL::Abstract instance (read-only).

sql

Current SQL statement (read/write; part of the q/make_sen workflow).

last_sql

Last executed SQL statement (read-only).

p

Current bind parameters arrayref (read/write).

results

Last query result (read-only). Reflects only the most recent query operation.

last_id

Last insert id (read-only). Reflects only the most recent insert; the authoritative value is the return value of insert / insert_many.

rows

Rows affected by the last write, as a plain number (0, never '0E0'), or undef when the driver does not know. See "rows" in DBIx::Fast::SQL.

errors

All retained errors as an arrayref (read-only, capped by max_errors). Each entry is a hashref:

{ id => $driver_err, error => $message, time => $epoch, seq => $n,
  sql => $statement, binds => [ @bind_values ] }

sql/binds are the statement in flight when the error was recorded (binds are '<redacted>' under errors_redact).

error_mark / errors_since

my $mark = $db->error_mark;
... handle a request / run a job ...
my $new = $db->errors_since($mark);   # arrayref of entries

Scope errors to a unit of work. Sequence-based, so it stays correct after the max_errors cap has dropped older entries.

last_error

Last error message string (read-only).

args

Processed constructor configuration hashref (read-only).

QUERY AND CRUD METHODS

All query methods (all, flat, hash, val, array, query, count, exec, execute) and CRUD methods (insert, update, up, delete, upsert, insert_many, upsert_many), together with q, make_sen and execute_prepare, are composed into this class from the DBIx::Fast::SQL role - the API is identical to previous releases, only the reference documentation lives on that page.

RAW SQL

You are never limited to the CRUD helpers: write whatever SQL you like and pick the method by what you want back. Values always go in as ? placeholders (bound, injection-safe) - never interpolate data into the string yourself.

# SELECT - pick the return shape:
my $rows  = $db->all ('SELECT * FROM users WHERE age > ?', 18);   # arrayref of hashrefs
my $row   = $db->hash('SELECT * FROM users WHERE id = ?', 42);    # one hashref
my $count = $db->val ('SELECT COUNT(*) FROM users');              # one scalar
my @names = $db->flat('SELECT name FROM users');                  # flat list of a column

# Big result set - stream it row by row instead of slurping:
my $rs = $db->query('SELECT id, name FROM users ORDER BY id');
while (my $u = $rs->hash) { say "$u->{id}: $u->{name}" }

# Anything else (DDL / INSERT / UPDATE / DELETE / vendor SQL):
$db->exec('UPDATE users SET seen = ? WHERE id = ?', $now, 42);
$db->exec('CREATE INDEX idx_users_email ON users (email)');

# Named placeholders (:name) when positional ? is awkward:
$db->execute('SELECT * FROM users WHERE name = :n AND age > :a',
             { n => 'Alice', a => 18 });

The last statement, its bind values and result are always available via $db->sql, $db->p and $db->results.

Placeholders are for values, not identifiers. Table and column names cannot be bound; if one must be dynamic, validate it against your own allow-list or quote it with $db->_quote_id($name) - never build it from raw user input. See "SECURITY".

Full reference for each method: DBIx::Fast::SQL.

SUBSYSTEMS

schema

my $schema = $db->schema;

Returns the DBIx::Fast::Schema instance (lazy-loaded) for table introspection.

transaction

my $tx = $db->transaction;

Returns the DBIx::Fast::Transaction instance (lazy-loaded).

txn

$db->txn(sub { ... });
$db->txn(sub { ... }, { max_retries => 5 });

Shortcut for $db->transaction->do(...). Executes a code block inside a transaction with automatic commit/rollback and deadlock retry.

profiler

my $profiler = $db->profiler;

Returns the driver-specific profile instance (DBIx::Fast::Profile::MariaDB, DBIx::Fast::Profile::mysql, DBIx::Fast::Profile::Pg, DBIx::Fast::Profile::SQLite, etc.) for native database diagnostics. Lazy-loaded on first access.

tracker

my $tracker = $db->tracker;

Returns the DBIx::Fast::Profiler instance for query tracking. Once activated, all queries executed through all, hash, val, flat, array, exec, insert, update, up, and delete are recorded with timing information.

# Activate tracking
$db->tracker;

# Run some queries
$db->all('SELECT * FROM users');
$db->insert('logs', { action => 'login' });
$db->up('users', { last_login => $db->now }, { id => 1 });

# Get statistics
my $stats = $db->tracker->get_stats;
printf "Queries: %d, Total: %.4fs, Avg: %.4fs\n",
    $stats->{total_queries}, $stats->{total_time}, $stats->{avg_time};

# Slow queries
my $slow = $db->tracker->get_slow_queries(5);
for my $q (@$slow) {
    printf "%.4fs - %s\n", $q->{duration}, $q->{sql};
}

# Stats by type (SELECT, INSERT, UPDATE, DELETE)
my $by_type = $db->tracker->get_detailed_stats;

# Print formatted report
$db->tracker->print_stats;

# Or route every report as structured data instead of text:
$db->tracker->output('json');          # one JSON line per event
$db->tracker->output(sub ($event, $data, $meta) { $log->info(...) });

# Clear recorded queries
$db->tracker->clear;

cached

# Enable caching at construction:
my $db = DBIx::Fast->new( SQLite => 'app.db', cache => { default_ttl => 60 } );

# Read through the cache; tag it with the table so writes can invalidate it:
my $row  = $db->cached(ttl => 60, tag => 'products')
              ->hash('SELECT * FROM products WHERE id = ?', $id);
my $rows = $db->cached(tag => 'products')->all('SELECT * FROM products');

Returns a DBIx::Fast::Cached proxy that wraps the query methods (hash/val/all/array/flat) with a cache lookup keyed on (method, SQL, bind values). On a miss the query runs and the result is stored; on a hit the stored value is returned (including cached "no row" results).

Options: ttl (seconds; falls back to the cache's default_ttl) and tag/tags (labels for invalidation). Writes through insert/update/ up/delete/upsert automatically invalidate cached reads tagged with the affected table name. Reads that are untagged, span multiple tables, or are mutated via raw exec/query/execute are not auto-invalidated - tag them and/or call "invalidate" yourself.

If the instance was constructed without cache, the proxy transparently passes through with no caching and no overhead.

cache

Returns the underlying DBIx::Fast::Cache instance (or undef if caching is disabled) for direct get/set/invalidate/stats access.

invalidate

$db->invalidate('products');            # after a raw-SQL write
$db->invalidate(@tables);

Drops every cached entry tagged with the given tag(s). Use it after mutations that bypass the CRUD auto-invalidation (raw exec/query/execute, or reads spanning several tables). No-op when caching is disabled.

load_extension

my $ext = $db->load_extension('Schema');

Dynamically loads and caches a DBIx::Fast::* extension module.

UTILITY METHODS

now

Returns the current local timestamp as YYYY-MM-DD HH:MM:SS.

now_utc

Like "now" but in UTC (gmtime). Use it when the database session timezone differs from the host's, so stored timestamps stay comparable.

set_error

$db->set_error($code, $message);

Appends an error to the errors array and updates last_error.

TableName

my $table = $db->TableName('users');

Validates a table name. When tn => 1 is set, checks that the table exists in the schema cache.

Exception

$db->Exception("Something went wrong");

Throws a DBIx::Fast::X::Usage exception, unconditionally - validation and usage errors are never silenced by RaiseError/PrintError settings (a swallowed validation error once let rejected identifiers flow on and become an injection vector). The message honors errors_redact, and the current last_error is appended (as Last error: [id] message, without a timestamp so identical failures produce identical text) only when it was recorded within the last few seconds (likely causal context).

Typed exceptions

Everything DBIx::Fast throws is a DBIx::Fast::X object that stringifies to the same text a plain string exception used to carry, so $@ =~ /.../ and logging keep working. Database errors are classified by driver error code / SQLSTATE:

eval { $db->insert('users', { email => $email }) };
if    ($@ isa DBIx::Fast::X::Duplicate) { ... $@->constraint ... }
elsif ($@ isa DBIx::Fast::X::Deadlock)  { ... retry ... }
elsif ($@ isa DBIx::Fast::X::DB)        { log($@->code, $@->sql) }
elsif ($@) { die $@ }

Classes: Usage (validation), and DB with Duplicate, ForeignKey, NotNull, Deadlock, LockTimeout, Syntax, Connection. See DBIx::Fast::X for the fields (code, state, sql, binds, constraint, ...).

SECURITY

DBIx::Fast uses multiple layers of defense against SQL injection:

  • Bind parameters - All query methods (all, hash, val, flat, array, exec, query) pass values through DBI placeholders (?). Values are never interpolated into SQL strings.

  • SQL::Abstract - CRUD operations (insert, update, delete) delegate SQL generation to SQL::Abstract, which produces parameterized queries.

  • Identifier validation - Table and column names are validated by _safe_id against the pattern /\A[a-zA-Z_][a-zA-Z0-9_.]*\z/, rejecting any special characters, spaces, or SQL syntax.

  • Identifier quoting - After validation, identifiers are quoted via DBI's quote_identifier() (_quote_id) as defense-in-depth.

  • Operator whitelist - The count WHERE builder only accepts operators from a fixed whitelist: =, !=, <>, <, <=, >, >=, LIKE, NOT, BETWEEN, IS. The SQL::Abstract-backed CRUD methods (insert, update, delete, upsert) enforce their own whitelist on every nested operator key of a WHERE/SET spec (_safe_op_tree), because SQL::Abstract interpolates those keys verbatim into the statement. The whitelist is enforced for every WHERE shape SQL::Abstract accepts - hashref, arrayref (OR-of-conditions) and nested combinations; a scalarref (literal SQL) WHERE is rejected outright from these methods.

  • Named parameter substitution - make_sen converts :name placeholders to ? with a bind array, never interpolating values.

Caveat - literal SQL values: following SQL::Abstract semantics, a scalar-ref value in a CRUD spec ({ col => \"NOW()" }) is inserted as literal SQL, not bound. Only ever pass references you wrote yourself; data coming from a request must always be a plain scalar. Everything a JSON/form decoder can produce is locked down: in insert/update data a hashref or arrayref value is rejected (SQL::Abstract would read ['(SELECT ...)'] or { -literal => ... } as raw SQL), and in a WHERE spec only the bind-only dash-ops are accepted (-and, -or, -in, -not_in, -between, -not_between, -like, -not_like, -is, -is_not, -value); -literal, -func, -ident and the rest are refused.

Caveat - profiler log: the profile option writes a DBI profile log (dbix-fast-PID.log, containing SQL text) to the current working directory with default permissions. Do not enable it when the CWD is a shared or world-writable directory.

Internal security methods

_safe_id

$db->_safe_id($identifier);

Validates that $identifier matches /\A[a-zA-Z_][a-zA-Z0-9_.]*\z/. Throws an exception on failure. Used for table names, column names, and any identifier that cannot be passed as a bind parameter.

_quote_id

my $quoted = $db->_quote_id($identifier);

Validates via _safe_id then quotes with $dbh->quote_identifier(). Returns driver-appropriate quoted identifier (backticks for MySQL/MariaDB, double quotes for SQLite/Pg).

REPORTING SECURITY ISSUES

If you believe you have found a security vulnerability in DBIx::Fast, please do not file a public GitHub issue.

Report it privately via GitHub's private vulnerability reporting: https://github.com/SeHarrys/dbix-fast/security/advisories/new

See SECURITY.md in the distribution root for the full security policy.

SEE ALSO

Subsystems: DBIx::Fast::Connector, DBIx::Fast::Transaction, DBIx::Fast::Result, DBIx::Fast::Schema, DBIx::Fast::Cache, DBIx::Fast::Cached, DBIx::Fast::Profiler, DBIx::Fast::Profile::MariaDB, DBIx::Fast::Profile::Pg; typed exceptions: DBIx::Fast::X; Redis cache backend: DBIx::Fast::Cache::Redis; unit tests without a server: DBIx::Fast::Test::Mock.

Built on: DBI, SQL::Abstract, Object::Pad.

Other database layers on CPAN, depending on what you need: full ORMs (DBIx::Class, Rose::DB::Object, Teng), thinner query helpers (DBIx::Simple), framework-integrated layers (Mojo::Pg, Mojo::mysql), and standalone connection management (DBIx::Connector).

AUTHOR

SeHarrys

LICENSE AND COPYRIGHT

This is free software under the Artistic License 2.0. http://www.perlfoundation.org/artistic_license_2_0