NAME
DBIx::Squirrel - DBI, minus the boilerplate, plus a pipeline
SYNOPSIS
use DBIx::Squirrel;
my $dbh = DBIx::Squirrel->connect($data_source, $username, $password, \%attr);
# Placeholders in the style you like: ?, ?1, :1, $1, :name or $name
my $sth = $dbh->prepare('SELECT * FROM artist WHERE id = :id OR name = :name');
# Named placeholders bound by name, as pairs or in a hash reference
$sth->execute(id => 1, name => 'AC/DC');
# A numbered placeholder takes one value, wherever it appears
my $row = $dbh->selectrow_hashref(
'SELECT * FROM artist WHERE id = $1 OR parent_id = $1', undef, 2,
);
# A placeholder with "..." takes a list: a "?" for each of its values
my $rows = $dbh->selectall_arrayref(
'SELECT * FROM artist WHERE id IN (...:ids)', ids => [1, 2, 3],
);
# Iterate over the results, each run through a pipeline of stages
my $names = $dbh->iterate(
'SELECT name FROM artist WHERE name LIKE ?', 'A%',
sub ($row) { $row->[0] },
);
while (my($name) = $names->next) {
say $name;
}
# Or have each row as an object, with a method for each column
my $artist = $dbh->results('SELECT * FROM artist WHERE id = :id', id => 1)->single;
say $artist->name;
# A new connection with the same parameters as an existing one
my $other = DBIx::Squirrel->connect($dbh, \%attr);
# Run the database handle through a pipeline of stages
my @results = $dbh->pipe(
sub ($dbh) { ... },
sub ($value) { ... },
);
# The same, as a branch off to the side: get the handle back
$dbh = $dbh->tap(
sub ($dbh) { ... },
sub ($value) { ... },
);
DESCRIPTION
With DBIx::Squirrel, you still write SQL, as you do with the DBI. It takes away the friction around it: placeholders in whatever style you like, iterators over results, and a pipeline of stages written beside the query.
It's a subclass of the DBI, so everything the DBI does still works. Where the two do the same thing, they give the same result. This page notes where they differ.
This is version 3, built on Ekorn::Pipeline. Version 2 was a rewrite of DBIx::Squirrel 1.x for Perl 5.36 or later. Code written for either needs changes to run, and the reference's "Upgrading from 2.x" and "Upgrading from 1.x" list them. To stay on 1.x, install its last version, cpanm DBIx::Squirrel@1.6.5.
The database handle's own methods are in DBIx::Squirrel::db, and the statement handle's in DBIx::Squirrel::st. DBIx::Squirrel::Iterator and DBIx::Squirrel::Results describe what iterate and results return. DBIx::Squirrel::Sugar gives each database a function with its own name.
Importing
use DBIx::Squirrel takes the names the DBI exports, as use DBI does. The DBI exports them:
use DBIx::Squirrel qw(:sql_types neat);
METHODS
connect
$dbh = DBIx::Squirrel->connect($data_source, $username, $password);
$dbh = DBIx::Squirrel->connect($data_source, $username, $password, \%attr);
$dbh = DBIx::Squirrel->connect($dbh, \%attr);
Connects to a data source, as the DBI's connect does, and returns a database handle, a DBIx::Squirrel::db. Its statement handles are DBIx::Squirrel::sts. Both are subclasses of the DBI's own, so they do everything the DBI's handles do.
Unlike the DBI's connect, it always croaks if the connection fails, whatever RaiseError is set to. The DBI returns undef unless RaiseError is set. The message is the one the DBI raises under RaiseError, such as DBIx::Squirrel connect('dbname=music.db','',...) failed: ..., and it's reported at the line that called connect.
Called on a subclass of DBIx::Squirrel, it uses that subclass's database and statement handle classes in place of DBIx::Squirrel's. The DBI requires the subclass to have both, and each must be a subclass of DBIx::Squirrel's:
package My::Squirrel { our @ISA = 'DBIx::Squirrel' }
package My::Squirrel::db { our @ISA = 'DBIx::Squirrel::db' }
package My::Squirrel::st { our @ISA = 'DBIx::Squirrel::st' }
Without them, the DBI warns that the subclass isn't set up, and returns its own handles.
Attributes starting ekorn_ are DBIx::Squirrel's own. They aren't passed on to the DBI. They're kept in the database handle's private_ekorn attribute, as the defaults for each statement it prepares. Attributes passed to "prepare" in DBIx::Squirrel::db override them, for that statement. There are four:
ekorn_dialect-
The driver whose rules statements are read by, described below.
ekorn_finish_active-
Finish a statement still active before executing it again. See "execute" in DBIx::Squirrel::st.
ekorn_slice-
The shape an iterator fetches rows in. See DBIx::Squirrel::Iterator.
ekorn_result_class-
The base class of the rows a result set hands out. See DBIx::Squirrel::Result.
ekorn_dialect names the driver whose rules statements are read by. When it isn't given, connect works it out: it's the driver's own name, such as mysql or Pg. A driver that passes the connection on to another database (DBD::ADO, DBD::Gofer, DBD::JDBC, DBD::ODBC or DBD::Proxy) is asked what the database is, through the DBI's get_info. MariaDB, MySQL, PostgreSQL and SQLite are recognised. Any other database, or one that doesn't say, is read by the standard rules. Name the dialect when connect can't tell:
# MySQL, through a driver that doesn't say what's behind it
$dbh = DBIx::Squirrel->connect($data_source, $username, $password, {
ekorn_dialect => 'mysql',
});
Connecting with an existing handle
Given a database handle in place of a data source, connect makes a new connection with the same parameters, using the DBI's clone. Any attributes passed are applied to the new connection. The handle can come from the DBI itself, or from code that made its own connection. The new handle keeps the first one's ekorn_ defaults. Any passed take their place.
The new handle is a separate connection, not a second name for the first. It doesn't share the first one's transaction, temporary tables or session settings. An in-memory SQLite database, for example, gives a new, empty database.
connect_cached
connect_cached is the DBI's own, unchanged. It calls connect when it needs a new connection, so it croaks on failure too.
HANDLE METHODS
Database and statement handles both have these methods, inherited from Ekorn::Pipeline::Pipes. The examples use a database handle. On a statement handle, the statement handle goes through the stages instead.
pipe
@results = $dbh->pipe(@stages);
$result = $dbh->pipe(@stages);
Runs the database handle through a pipeline of stages, and returns what comes out of the last one.
A stage is a code reference, or a pipeline, which stands for its stages, as Ekorn::Pipeline shows. A code reference is called with one value, as its argument and in $_. It can name the value with a signature, or leave the signature out and use $_:
sub ($row) { $row->[1] } # the value, named
sub { $_->[1] } # the same, in $_
$_ is localised for each call, so the caller's is unchanged. The first stage is called with the database handle. A stage returns nothing to drop the value, one value to pass it on, or several. Each of several goes through the rest of the stages on its own:
my @odd_doubled = $dbh->pipe(
sub ($dbh) { (3, 4, 5) },
sub { $_ % 2 ? $_ : () },
sub { $_ * 2 },
); # (6, 10)
To end the pipeline early, call "Stop". With no stages, pipe returns the database handle.
Called in list context, pipe returns all the values. In scalar context, it returns the first, or undef if there are none. To count the values, assign them to an empty list in scalar context:
my $count = () = $dbh->pipe(@stages);
If a stage dies, pipe croaks at the line that called it, naming the stage by its position, from #1:
Pipeline stage #2 failed at script.pl line 12:
...
Ekorn::Pipeline says more about stages.
tap
$dbh = $dbh->tap(@stages);
Runs the database handle through a pipeline of stages, as a branch off to the side, for their side effects. It returns the handle, so a chain of method calls can carry on after it.
$dbh ───┬───▶ $dbh
│
└───▶ stage #1 ───▶ stage #2 ───▶ ...
The stages work as they do in "pipe", but what comes out of the last one is dropped. For example, to log how many rows a table has, then carry on with the handle:
$dbh->tap(
sub ($dbh) { $dbh->selectrow_array('SELECT COUNT(*) FROM artist') },
sub ($count) { log_count($count) },
)->do('DELETE FROM artist WHERE id = ?', undef, $id);
A stage passes on whatever its last statement gives, even a statement run for its side effects. So each stage except the last should return what the next one needs.
tap returns the handle however the stages end: having run out of values, stopped with "Stop", or with no stages at all. If a stage dies, tap croaks at the line that called it, as "pipe" does.
FUNCTIONS
Stop
use DBIx::Squirrel::Stop;
Stop();
Stop(@values);
Ekorn::Pipeline::Stop->throw(@values);
Called from a stage of a pipeline, ends the pipeline quietly. The rest of that stage doesn't run. The values already out of the pipeline are kept, @values go on through the stages after it, and nothing else is processed. DBIx::Squirrel::Stop exports Stop by default. Ekorn::Pipeline::Stop->throw does the same without importing it.
Stop works by throwing an exception, which the pipeline catches. So a stage that catches errors itself, with eval or try, should throw a stop on again. Check for Ekorn::Pipeline::Stop. Every stop is one, including those the canned stages throw, such as take's:
sub ($row) {
try { ... }
catch ($e) {
die $e if $e isa 'Ekorn::Pipeline::Stop';
...
}
}
Called outside a stage, it dies with "Stop used outside a pipeline". DBIx::Squirrel::Stop says more, including what a stop carries.
gather
use DBIx::Squirrel::Gather;
my $joined = gather {
my @names; # fresh on each run
return (
sub ($name) { push @names, $name; () },
sub () { join ', ', @names }, # once the values run out
);
};
Returns a stage that a pipeline makes afresh for each run, by calling the block. The block returns the stage, and optionally a flush and an end. The flush is called once the values run out, and what it returns goes through the rest of the stages. The end is called once the run has ended, and told how. Make a stage this way if it gathers values, to give them in batches or as one, or keeps state of any kind. DBIx::Squirrel::Gather exports gather by default, and says more, including what happens after a stop.
CANNED STAGES
Four modules of ready-made stages ship with DBIx::Squirrel. Each exports the stages it's usually used for:
DBIx::Squirrel::Stages::Flow:
takeanddrop, withtake_first,take_last,take_single,take_while,drop_while,take_ifanddrop_ifon request;DBIx::Squirrel::Stages::Rows:
pluckandas_hash, for rows of any shape;DBIx::Squirrel::Stages::Batch:
batchandcollect;DBIx::Squirrel::Stages::Taps:
peekandcount_into, withdump_toandteeon request.
use DBIx::Squirrel::Stages::Flow;
use DBIx::Squirrel::Stages::Rows;
my @names = $dbh->iterate(
'SELECT name FROM artist',
pluck('name'),
take(10),
)->all;
A sink goes last, after the stages, and the call gives what the sink returns, in place of an iterator: keep_all gives every result, and count_items how many there were. Ekorn::Pipeline::Sinks has them. Sources, which give a pipeline values other than rows, such as the lines of a file, are in Ekorn::Pipeline::Sources. DBIx::Squirrel::Tooling exports all of these, and the stages above, from one module:
use DBIx::Squirrel::Tooling qw(:stages :sinks);
my @names = $dbh->iterate('SELECT name FROM artist', pluck('name'), keep_all);
my $count = $dbh->iterate('SELECT * FROM artist', count_items);
The reference's "Writing stages" says how to write your own stages to work alongside them.
DIAGNOSTICS
Errors are reported at the line of your code that called DBIx::Squirrel, never at a line inside it or the DBI. That includes the DBI's own errors under RaiseError, such as DBD::SQLite::db prepare failed: near "SELEC": syntax error, which keep the DBI's message.
Sometimes DBIx::Squirrel croaks where the DBI would return undef. The error then has the message the DBI raises under RaiseError, so it reads the same whatever RaiseError is set to.
Each error names the method you called, as plain DBI does: do and the select methods, not the prepare or execute they call. An error from a HandleError callback, or one that isn't a string, is passed on unchanged. The DBI's warnings, under PrintError, are reported the same way plain DBI reports them: at your line, naming the method you called.
- Pipeline stage #%s failed at %s line %d:
-
A stage passed to "pipe" or "tap", or its flush, died. The message it died with follows, indented. A stage inside a pipeline given as a stage is named
#2.3, for the third stage of the pipeline given as stage #2. A stage of a named pipeline is named by its name and its place there too, as#2.2 (main::fussy#2). - Expected stage #%d to be a stage: a code reference, a pipeline, or an object offering a stage's role method
-
Something passed to "pipe" or "tap" wasn't a stage. No stage runs.
- Expected stage #%s, made with gather, to return a code reference or a pipeline, and optionally a flush and an end
-
The block given to "gather" for that stage returned something else.
- Stop used outside a pipeline
-
"Stop" was called where no pipeline was running to catch it.
SEE ALSO
DBI, DBIx::Squirrel::db, DBIx::Squirrel::st, DBIx::Squirrel::Iterator, DBIx::Squirrel::Results, DBIx::Squirrel::Result, DBIx::Squirrel::Context, DBIx::Squirrel::Sugar, Ekorn::Pipeline, Ekorn::Pipeline::Pipes, DBIx::Squirrel::Stop, DBIx::Squirrel::Gather, DBIx::Squirrel::Stages::Flow, DBIx::Squirrel::Stages::Rows, DBIx::Squirrel::Stages::Batch, DBIx::Squirrel::Stages::Taps, DBIx::Squirrel::Tooling
DBIx::Squirrel::Manual::Reference describes every feature in detail, with examples. DBIx::Squirrel::Manual::HowTo, the user guide, is a tour of the package, a layer at a time, with recipes.
AUTHOR
Iain Campbell <cpanic@cpan.org>
LICENSE
Copyright (c) 2024-2026 by Iain Campbell.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.