NAME
DBIx::Fast::SQL - Query and CRUD methods of DBIx::Fast
SYNOPSIS
# These methods are composed into DBIx::Fast itself:
my $rows = $db->all('SELECT * FROM users WHERE status = ?', 1);
my $id = $db->insert('users', { name => 'Alice' });DESCRIPTION
Object::Pad role composed into DBIx::Fast. It owns the statement state (sql, p, results, last_id, last_sql - same accessors as always) and provides every query and CRUD method. You never use this module directly: all methods below are called on the DBIx::Fast object.
Every method below takes raw SQL; values are passed as ? placeholders (or :name for "execute") and bound, never interpolated. For a task-oriented overview see "RAW SQL" in DBIx::Fast.
QUERY METHODS
Lists: IN (?)
my $rows = $db->all('SELECT * FROM users WHERE id IN (?)', \@ids);
$db->exec('DELETE FROM sessions WHERE user_id IN (?) AND kind = ?', \@ids, 'web');
$db->execute('SELECT * FROM users WHERE id IN (:ids)', { ids => \@ids });In every raw-SQL method (all, hash, val, flat, array, query, kv, exec, execute) an arrayref bound to a placeholder inside IN ( ... ) becomes one placeholder per element. Only IN (?) / NOT IN (?) placeholders are expanded - an arrayref elsewhere stays a single bind (PostgreSQL arrays, = ANY(?)) - and ? inside quoted literals is ignored. An empty list throws: IN () is invalid SQL.
all
$db->all('SELECT * FROM users WHERE id > ?', 10);
my $rows = $db->results; # arrayref of hashrefsExecutes SQL and stores all rows in results.
hash
$db->hash('SELECT * FROM users WHERE id = ?', 1);
my $row = $db->results; # hashrefExecutes SQL and stores a single row in results.
val
my $name = $db->val('SELECT name FROM users WHERE id = ?', 1);Executes SQL and returns a single scalar value: the first column of the first row. Intended for single-column SELECTs; with multiple columns the extra ones are ignored.
flat
my @names = $db->flat('SELECT name FROM users');Executes SQL and returns a flat list of values.
array
$db->array('SELECT name FROM users');
my $names = $db->results; # arrayrefExecutes SQL and stores a single column as arrayref in results.
query
my $rs = $db->query('SELECT id, name FROM users WHERE active = ?', 1);
while (my $row = $rs->hash) {
say "$row->{id}: $row->{name}";
}Executes SQL with bind parameters and returns a DBIx::Fast::Result iterator. Unlike all, this does not load the entire result set into memory - rows are fetched one at a time via hash, array, all, or flat on the result object. The statement handle is automatically finished when the result object goes out of scope.
metrics
my $m = $db->metrics;Statement counters when the metrics option is on (undef otherwise). See "MONITORING" in DBIx::Fast.
reset_metrics
Zeroes the metrics counters.
stream
my $rs = $db->stream('SELECT * FROM logs WHERE day = ?', $day);
while (my $row = $rs->hash) { ... }Like "query", for result sets too big for memory: on MariaDB/MySQL the rows come from the server as they are read (mariadb_use_result / mysql_use_result) instead of being loaded into the client first - 200,000 rows of ~180 bytes: +45 MB with query, +0 MB with stream. While a stream is open, that connection cannot run other statements (MySQL protocol): drain or finish it first, or read through another connection. SQLite streams natively; DBD::Pg always buffers results, so on PostgreSQL stream behaves like query.
count
my $total = $db->count('users');
my $active = $db->count('users', { status => 1 });Returns row count, optionally filtered by WHERE conditions.
exists
if ($db->exists('users', { email => $email })) { ... }Returns 1 if at least one row matches (SELECT 1 ... LIMIT 1), else 0. Takes the same WHERE hashref (and operator whitelist) as count.
kv
my $names = $db->kv('SELECT id, name FROM users WHERE active = ?', 1);
# { 1 => 'Alice', 2 => 'Bob' }First column to second column, as a hashref. A later duplicate key wins.
all_by
my $users = $db->all_by('id', 'SELECT * FROM users WHERE active = ?', 1);
# { 1 => { id => 1, name => 'Alice', ... }, ... }Rows as a hashref keyed by the given column (DBI's fetchall_hashref).
where
my ($where, @binds) = $db->where({
status => 'active',
country => $form->{country}, # undef: filter skipped
id => \@ids, # IN (...)
name => { CONTAINS => $search }, # escaped LIKE
created => { '>=' => $since },
deleted => { IS => undef }, # explicit IS NULL
});
my $rows = $db->all("SELECT * FROM users $where ORDER BY id", @binds);Builds ('WHERE ...', @binds) from a filter hashref (or ('') when no filter is left), for hand-written SQL. Columns are validated and quoted; operators are whitelisted: = != <> < <= > >= LIKE NOT BETWEEN IS IN, NOT IN, and CONTAINS / PREFIX / SUFFIX, which match user text literally (LIKE ? ESCAPE '!' with "like"). A filter whose value is undef is skipped (an empty form field means "do not filter"); use { IS => undef } for IS NULL. count and exists accept the same conditions, except that there an undef value means IS NULL.
like
$db->all(q{SELECT * FROM users WHERE name LIKE ? ESCAPE '!'},
$db->like($search)); # contains (default)
$db->like($s, 'prefix'); $db->like($s, 'suffix'); $db->like($s, 'exact');Turns user text into a LIKE pattern that matches it literally: %, _ and the escape character ! are escaped with !. Pair it with ESCAPE '!', which behaves the same on SQLite, MariaDB/MySQL and PostgreSQL (a backslash does not).
page
my $page = $db->page(
'SELECT id, name, email FROM users WHERE status = ?', ['active'],
page => $req->param('page'), per => 50,
order => $req->param('sort'), allowed_order => [qw(id name email)],
);
# { rows => [...], total => 1234, page => 2, per => 50, pages => 25 }One page of a query plus its total (COUNT(*) over the statement). order is a column from allowed_order ("name", "name DESC" or "-name"), or an arrayref of them; anything else throws, so it can come straight from a request. page and per must be positive integers, per at most max_per (default 1000). The statement must not carry its own ORDER BY or LIMIT.
in_chunks
my @rows = map { @$_ } $db->in_chunks(\@ids, 500, sub ($chunk) {
$db->all('SELECT * FROM products WHERE id IN (?)', $chunk);
});Calls the coderef once per slice of at most $size elements and returns the results in order - for lists too long for one statement.
rows
$db->delete('sessions', { user_id => $id });
my $n = $db->rows;Rows affected by the last write (insert/update/up/delete/ upsert/*_many/exec): a plain number (0, never '0E0'), or undef when the driver does not know.
exec
$db->exec('CREATE TABLE foo (id INT)');
$db->exec('INSERT INTO foo VALUES (?)', 42);
$db->exec('UPDATE foo SET id = ? WHERE id = ?', 99, 42);
my $sth = $db->exec('SELECT * FROM foo WHERE id > ?', 0);The general-purpose raw-SQL method: runs any statement (DDL, DML or vendor-specific SQL) with optional ? bind parameters and returns the statement handle. Records the query in the profiler when one is active. For a SELECT you usually want all/hash/val/query instead, which fetch the rows for you.
execute
$db->execute('SELECT * FROM users WHERE name = :name', { name => 'Alice' });
$db->execute('SELECT * FROM users WHERE id = :id', { id => 1 }, 'hash');Executes SQL with named parameter substitution (:name syntax). Third argument selects result type: arrayref (default) or hash.
CRUD METHODS
insert
$db->insert('users', { name => 'Alice', status => 1 });
$db->insert('users', { name => 'Alice' }, time => 'created_at');
my $id = $db->last_id;Inserts a row using SQL::Abstract. Sets last_id automatically based on the database driver. On PostgreSQL the id comes back in the same round trip (INSERT ... RETURNING the table's primary key, looked up once per table) - any key type: serial, identity, uuid... A table without a single-column primary key falls back to DBD::Pg's last_insert_id (a serial column), as insert_ignore does for an ignored row.
update
$db->update('users', {
sen => { name => 'Bob', status => 1 },
where => { id => 1 },
});
$db->update('users', {
sen => { name => 'Bob' },
where => { id => 1 },
}, time => 'updated_at');Updates rows using SQL::Abstract. Pass time => 'column_name' to auto-set a timestamp column to now().
up
$db->up('users', { name => 'Bob' }, { id => 1 });
$db->up('users', { name => 'Bob' }, { id => 1 }, 'updated_at');Shortcut for update. Arguments: table, data hashref, where hashref, optional time column name (positional).
delete
$db->delete('users', { id => 1 });Deletes rows using SQL::Abstract.
upsert
$db->upsert('products',
{ sku => 'X', name => 'Widget', stock => 5, price => 9.99 },
['sku'], # conflict keys (UNIQUE / PRIMARY KEY)
[qw(stock price)], # optional: columns to update on conflict
);Inserts the row, or updates the conflict-targeted columns if a row with matching conflict keys already exists. Translates to driver-native syntax (ON CONFLICT (...) DO UPDATE for PostgreSQL and SQLite 3.24+, ON DUPLICATE KEY UPDATE for MariaDB and MySQL).
If update_cols is omitted, every row column that is not in conflict_keys is updated. If after that the update set is empty, an exception is raised - use a regular insert if you do not want to update anything on conflict.
last_id is not updated by upsert because the operation may have been an insert or an update.
update_cols can also be a hashref saying how each column is updated:
$db->upsert('points', { user_id => $uid, balance => 50, visits => 1 },
['user_id'],
{
balance => '+', # balance = balance + 50
visits => { '+' => 1 }, # visits = visits + 1
last_name => 'new', # take the incoming value
updated_at => \'CURRENT_TIMESTAMP', # literal SQL (code only)
});'new' takes the incoming value, '+' adds the incoming value to the stored one (the column must be in the row), { '+' => N } adds a numeric constant. These are translated per driver (VALUES(col) on MariaDB, the new.col row alias on MySQL 8.0.19+, excluded.col on PostgreSQL/SQLite, with the stored row qualified where the dialect needs it). A scalarref is literal SQL - only ever from code; on PostgreSQL a literal that reads the stored row must name it dbix_t.col, so prefer the modes. upsert_many accepts the same forms.
insert_ignore
my $inserted = $db->insert_ignore('subscribers', { email => $email });Inserts the row unless it would violate a UNIQUE / PRIMARY KEY, in which case nothing happens. Returns 1 if inserted, 0 if the row already existed (INSERT IGNORE on MariaDB/MySQL, INSERT OR IGNORE on SQLite, ON CONFLICT DO NOTHING on PostgreSQL). Note that on MariaDB/MySQL IGNORE also turns other errors (invalid values, missing NOT NULL columns) into warnings.
find_or_create
my $tag = $db->find_or_create('tags', { slug => $slug }, { name => $name });
my ($row, $created) = $db->find_or_create('tags', { slug => $slug });Returns the row matching the key (which must be covered by a UNIQUE index), inserting { %defaults, %key } first when there is none. Safe against concurrent callers: the insert is an insert_ignore, so a racing duplicate is skipped and the existing row is read back. In list context also returns whether this call created the row.
insert_many
$db->insert_many('orders', [
{ customer_id => 1, total => 99 },
{ customer_id => 2, total => 45 },
]);Inserts multiple rows in a single statement. All rows must share the same column set (same keys); rows whose keys differ from the first one raise an exception. Every row's values must be bindable: a reference (other than a blessed object, or an arrayref on Pg) raises an exception instead of being stored as its "HASH(0x...)" stringification. Returns the number of rows inserted.
last_id follows the driver's convention for a multi-row INSERT, which is not portable: MariaDB/MySQL report the first inserted id, SQLite and Pg the last. If you need every id, use INSERT ... RETURNING (Pg, SQLite 3.35+, MariaDB 10.5+) via all().
For very large arrays you should chunk the input manually to stay within the driver's max_allowed_packet / statement-size limits.
Options (after the rows): chunk => N splits the rows into statements of at most N rows (max_allowed_packet / bind-count limits) and returns the total; each chunk is its own statement, so wrap the call in txn when it must be all-or-nothing. ignore => 1 skips rows that violate a UNIQUE / PRIMARY KEY (as "insert_ignore") and returns the number actually inserted.
$db->insert_many('events', \@rows, chunk => 500);
my $new = $db->insert_many('subscribers', \@rows, ignore => 1);upsert_many
$db->upsert_many('products', \@rows, ['sku'], [qw(stock price)]);Bulk INSERT with per-row conflict resolution, combining insert_many and upsert. Same column-uniformity rule as insert_many. Returns the number of rows in the batch.
Takes the same update_cols forms as "upsert", and chunk => N after them: $db->upsert_many($t, \@rows, ['sku'], undef, chunk => 500).
make_sen
$db->sql('SELECT * FROM users WHERE name = :name AND age = :age');
$db->make_sen({ name => 'Alice', age => 30 });
# $db->sql is now 'SELECT * FROM users WHERE name = ? AND age = ?'
# $db->p is ['Alice', 30]Replaces named placeholders (:name) with ? in order of appearance in the SQL string. Supports duplicate placeholders. Quoted string literals ('...' / "...") and PostgreSQL ::cast operators are left untouched.
Named parameters are intended for simple SQL: the literal-skipping is based on straight single/double quotes and does not understand backslash escapes ('a\'b'), PostgreSQL dollar-quoting ($$...$$), or SQL comments. For SQL using those constructs, use positional ? placeholders instead.
q
$db->q('SELECT * FROM users WHERE id = ?', 1);Sets sql and p (bind parameters) for subsequent use.
execute_prepare
$db->sql('INSERT INTO users (name) VALUES (?)');
$db->execute_prepare('Alice');Prepares and executes the current sql with bind parameters.
SEE ALSO
DBIx::Fast - constructor, accessors, subsystems and the SECURITY notes that apply to these methods.
AUTHOR
SeHarrys
LICENSE
This is free software under the Artistic License 2.0.