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 { /\@/ ? '' : $_ } @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 "", a coderef receives "($sql, @binds)" and returns the list to report (if it dies, a single "" 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 '' 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: 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.