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 transformations 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 2, a rewrite of DBIx::Squirrel 1.x for Perl 5.36 or later. Code written for 1.x needs changes to run, and the reference's "Upgrading from 1.x" lists 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 DBIx::Squirrel::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 DBIx::Squirrel::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:
    ...

DBIx::Squirrel::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);

DBIx::Squirrel::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. DBIx::Squirrel::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 an end function. The end is called once the values run out, and what it returns goes through the rest of the stages. 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:

use DBIx::Squirrel::Stages::Flow;
use DBIx::Squirrel::Stages::Rows;

my @names = $dbh->iterate('SELECT name FROM artist', pluck('name'), take(10))->all;

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 end function, 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.

Expected stage #%d to be a code reference or a pipeline

Something passed to "pipe" or "tap" wasn't a code reference or a pipeline. No stage runs.

Expected stage #%s, made with gather, to return a code reference or a pipeline, and optionally an end function

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, DBIx::Squirrel::Pipes, DBIx::Squirrel::Pipeline, DBIx::Squirrel::Stop, DBIx::Squirrel::Gather, DBIx::Squirrel::Stages::Flow, DBIx::Squirrel::Stages::Rows, DBIx::Squirrel::Stages::Batch, DBIx::Squirrel::Stages::Taps

DBIx::Squirrel::Manual::Reference describes every feature in detail, with examples. DBIx::Squirrel::Manual::HowTo shows how to do common tasks.

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.