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: https://github.com/SeHarrys/dbix-fast/security/advisories/new

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

AUTHOR

SeHarrys

LICENSE AND COPYRIGHT

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

To install DBIx::Fast, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::Fast

CPAN shell

perl -MCPAN -e shell
install DBIx::Fast

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	Go to GitHub issues (only if GitHub is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)