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, for PII / PCI (default: 0) "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 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". errors All errors as arrayref (read-only). 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 an exception via "croak", 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 only when it was recorded within the last few seconds (likely causal context). 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-zA-Z_][a-zA-Z0-9_.]*$/", 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. 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-zA-Z_][a-zA-Z0-9_.]*$/". 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. 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.