DBD::Mock(3pm)

📦

version 1.59 · 4 versions

1.59 · debian bookworm, debian trixie, ubuntu jammy, ubuntu noble, ubuntu resolute, ubuntu stonking (latest)
1.59 · fedora 43
1.60 · fedora 43, fedora 44
1.59 · fedora 44

, in debian bookworm, debian trixie, ubuntu jammy, ubuntu noble, ubuntu resolute, ubuntu stonking

💖 DBD::Mock 💖

so like… this is the DBD::Mock(3pm) page from User Contributed Perl Documentation, and bestie, it's literally the fake-database-driver-slash-group-chat-archive you never knew you needed. buckle up 🎀

✨ NAME ✨

DBD::Mock — a mock database driver for testing. that's it, that's the whole vibe. it doesn't talk to a real database, it just ~pretends~ and takes notes on everything you do. lowkey iconic. 💅

🌸 SYNOPSIS 🌸

okay so first you do the normal DBI thing — except your driver name is Mock:

use DBI;

# connect to your as normal, using 'Mock' as your driver name
my $dbh = DBI->connect( 'DBI:Mock:', '', '' )
    || die "Cannot create handle: $DBI::errstr\n";

# create a statement handle as normal and execute with parameters
my $sth = $dbh->prepare( 'SELECT this, that FROM foo WHERE id = ?' );
$sth->execute( 15 );

# Now query the statement handle as to what has been done with it
my $mock_params = $sth->{mock_params};
print "Used statement: ", $sth->{mock_statement}, "\n",
   "Bound parameters: ", join( ', ', @{ $mock_params } ), "\n";

see how it just… remembers? the statement handle is keeping receipts, and honestly good for it 🫶

🦋 DESCRIPTION 🦋

Testing with databases can be tricky. like, genuinely. if you're building a system that's married to a single database, you can make some assumptions about your environment and just ask the user to hand over the relevant connection info. no biggie.

but — and this is the plot twist — if you need to test a framework that uses DBI, especially one that uses different types of persistence schemes, then it might be way more useful to simply verify what the framework is trying to do: make sure the right SQL is generated and that the correct parameters are bound. and DBD::Mock makes that easy peasy — you just tweak your configuration (presumably held outside your code) and swap in DBD::Mock instead of DBD::Foo (like DBD::Pg or DBD::mysql) inside your framework. slay.

There is no distinct area where using this module makes sense. (Some people may successfully argue that this is a solution looking for a problem...) Indeed, if you can assume your users have something like DBD::AnyData or DBD::SQLite, or if you do not mind creating a dependency on them, then it makes far more sense to use these legit driver implementations and test your application in the real world — at least as much of the real world as you can create in your tests... 😭 fr fr.

And if your database handle exists as a package variable or something else that's easily replaced at test-time, then it may make more sense to use Test::MockObject to create a fully dynamic handle. There is an excellent article by chromatic about using Test::MockObject in this and other ways — strongly recommended, no cap. (See "SEE ALSO" for a link.)

🧚 How does it work?

DBD::Mock is a set of classes used by DBI to implement a database driver. but instead of connecting to a datasource and manipulating data found there, it tracks all the calls made to the database handle and to any statement handles it creates. then you inspect them to make sure what you wanted to happen actually happened. main character energy, honestly.

For instance, say you have a config file with your database connection information:

[DBI]
dsn      = DBI:Pg:dbname=myapp
user     = foo
password = bar

And this file is read in at process startup, and the handle is stored for other procedures to use:

package ObjectDirectory;

my ( $DBH );

sub run_at_startup {
    my ( $class, $config ) = @_;
    $config ||= read_configuration( ... );
    my $dsn  = $config->{DBI}{dsn};
    my $user = $config->{DBI}{user};
    my $pass = $config->{DBI}{password};
    $DBH = DBI->connect( $dsn, $user, $pass ) || die ...;
}

sub get_database_handle {
    return $DBH;
}

A procedure might use it like this (ignoring any error handling for the moment):

package My::UserActions;

sub fetch_user {
    my ( $class, $login ) = @_;
    my $dbh = ObjectDirectory->get_database_handle;
    my $sql = q{
        SELECT login_name, first_name, last_name, creation_date, num_logins
        FROM users
        WHERE login_name = ?
    };
    my $sth = $dbh->prepare( $sql );
    $sth->execute( $login );
    my $row = $sth->fetchrow_arrayref;
    return ( $row ) ? User->new( $row ) : undef;
  }

So for the purposes of our tests, we just want to make sure that:

The right SQL is being executed
The right parameters are bound

Assume whether the SQL actually works or not is irrelevant for this test :-)

To do that, our test might look like:

my $config = ObjectDirectory->read_configuration( ... );
$config->{DBI}{dsn} = 'DBI:Mock:';
ObjectDirectory->run_at_startup( $config );

my $login_name = 'foobar';
my $user = My::UserActions->fetch_user( $login_name );

# Get the handle from ObjectDirectory;
# this is the same handle used in the
# 'fetch_user()' procedure above
my $dbh = ObjectDirectory->get_database_handle();

# Ask the database handle for the history
# of all statements executed against it
my $history = $dbh->{mock_all_history};

# Now query that history record to
# see if our expectations match reality
is(scalar(@{$history}), 1, 'Correct number of statements executed' ;

my $login_st = $history->[0];
like($login_st->statement,
    qr/SELECT login_name.*FROM users WHERE login_name = ?/sm,
    'Correct statement generated' );

my $params = $login_st->bound_params;
is(scalar(@{$params}), 1, 'Correct number of parameters bound');
is($params->[0], $login_name, 'Correct value for parameter 1' );

# Reset the handle for future operations
$dbh->{mock_clear_history} = 1;

The list of properties and what they return is listed below. But in an overall view:

A database handle contains the history of all statements created against it. Other properties set for the handle (e.g., PrintError, RaiseError) are left alone and can be queried as normal, but they do not affect anything. (A future feature may track the sequence/history of these assignments, but if there is no demand it probably will not get implemented.) 💀
A statement handle contains the statement it was prepared with plus all bound parameters or parameters passed via execute(). It can also contain predefined results for the statement handle to fetch, track how many fetches were called, and what its current record is. 👑

🌈 A Word of Warning

This may be an incredibly naive implementation of a DBD. But it works for me... 😌 and honestly? that's the whole mood.

👑 DBD::Mock

Since this is a normal DBI statement handle, we need to expose our tracking information as properties (accessed like a hash) rather than methods. iykyk.

🍓 Database Driver Properties

mock_connect_fail — This is a boolean property which, when set to true (1), will not allow DBI to connect. This can be used to simulate a DSN error or authentication failure. This can then be set back to false (0) to resume normal DBI operations. Here's an example of how this works:

# install the DBD::Mock driver
my $drh = DBI->install_driver('Mock');

$drh->{mock_connect_fail} = 1;

# this connection will fail
my $dbh = DBI->connect('dbi:Mock:', '', '') || die "Cannot connect";

# this connection will throw an exception
my $dbh = DBI->connect('dbi:Mock:', '', '', { RaiseError => 1 });

$drh->{mock_connect_fail} = 0;

# this will work now ...
my $dbh = DBI->connect(...);

This feature is conceptually different from the mock_can_connect attribute of the $dbh, in that it has driver-wide scope, where mock_can_connect is handle-wide scope. It also only prevents the initial connection — any $dbh handles created prior to setting mock_connect_fail to true (1) will still go on working just fine. 🫶

mock_data_sources — This is an ARRAY reference which holds fake data sources, which are returned by the Driver and Database Handle's data_source() method.
mock_add_data_sources — This takes a string and adds it to the mock_data_sources attribute.

🎀 Database Handle Properties

mock_all_history — Returns an array reference with all history (a.k.a. DBD::Mock::StatementTrack) objects created against the database handle, in the order they were created. Each history object can then report information about the SQL statement used to create it, the bound parameters, etc.. ✨
mock_all_history_iterator — Returns a DBD::Mock::StatementTrack::Iterator object which will iterate through the current set of DBD::Mock::StatementTrack objects in the history. See the DBD::Mock::StatementTrack::Iterator documentation below for more information.
mock_clear_history — If set to a true value, all previous statement history operations will be erased. This includes the history of currently open handles, so if you do something like:

my $dbh = get_handle( ... );
my $sth = $dbh->prepare( ... );
$dbh->{mock_clear_history} = 1;
$sth->execute( 'Foo' );

You will have no way to learn from the database handle that the statement parameter 'Foo' was bound. gone. poof. 🫠

This is useful mainly to ensure you can isolate the statement histories from each other. A typical sequence will look like:

set handle to framework
perform operations
analyze mock database handle
reset mock database handle history
perform more operations
analyze mock database handle
reset mock database handle history
...

mock_can_connect — This statement allows you to simulate a downed database connection. This is useful in testing how your application/tests will perform in the face of some kind of catastrophic event, such as a network outage or database server failure. It is a simple boolean value which defaults to on, and can be set like this:

# turn the database off
$dbh->{mock_can_connect} = 0;

# turn it back on again
$dbh->{mock_can_connect} = 1;

The statement handle checks this value as well, so something like this will fail in the expected way:

$dbh = DBI->connect( 'DBI:Mock:', '', '' );
$dbh->{mock_can_connect} = 0;

# blows up!
my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
if ( $@ ) {
    # Here, $DBI::errstr = 'No connection present'
}

Turning off the database after a statement prepare will fail on the statement execute(), which is hopefully what you would expect:

$dbh = DBI->connect( 'DBI:Mock:', '', '' );

# ok!
my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
$dbh->{mock_can_connect} = 0;

# blows up!
$sth->execute;

Similarly:

$dbh = DBI->connect( 'DBI:Mock:', '', '' );

# ok!
my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });

# ok!
$sth->execute;

$dbh->{mock_can_connect} = 0;

# blows up!
my $row = $sth->fetchrow_arrayref;

💡 Note, bestie: the handle attribute Active and the handle method ping both vibe-check against the value of mock_can_connect. So if mock_can_connect gets set to 0 (a.k.a. off), then both Active and ping come back with false values (or 0). No cap, that's just how she goes. 💅

🎀 `mock_add_resultset( \@resultset | \%resultset_and_options )`

Okay so this one stocks the database handle with a record set — which is basically you seeding data so your app can prove it actually works. Each recordset is a simple arrayref of arrays, and the first arrayref is the field names. Every single time a statement handle gets created, it asks the database handle "hey do you have any resultsets available?" and if the answer's yes, it uses it. Queen behavior honestly. 👑

Here's a sample usage, partially straight out of the test suite:

my @user_results = (
    [ 'login', 'first_name', 'last_name' ],
    [ 'cwinters', 'Chris', 'Winters' ],
    [ 'bflay', 'Bobby', 'Flay' ],
    [ 'alincoln', 'Abe', 'Lincoln' ],
);
my @generic_results = (
    [ 'foo', 'bar' ],
    [ 'this_one', 'that_one' ],
    [ 'this_two', 'that_two' ],
);

my $dbh = DBI->connect( 'DBI:Mock:', '', '' );
$dbh->{mock_add_resultset} = \@user_results;    # add first resultset
$dbh->{mock_add_resultset} = \@generic_results; # add second resultset
my ( $sth );
eval {
    $sth = $dbh->prepare( 'SELECT login, first_name, last_name FROM foo' );
    $sth->execute();
};

# this will fetch rows from the first resultset...
my $row1 = $sth->fetchrow_arrayref;
my $user1 = User->new( login => $row->[0],
                       first => $row->[1],
                       last  => $row->[2] );
is( $user1->full_name, 'Chris Winters' );

my $row2 = $sth->fetchrow_arrayref;
my $user2 = User->new( login => $row->[0],
                       first => $row->[1],
                       last  => $row->[2] );
is( $user2->full_name, 'Bobby Flay' );
...

my $sth_generic = $dbh->prepare( 'SELECT foo, bar FROM baz' );
$sth_generic->execute;

# this will fetch rows from the second resultset...
my $row = $sth->fetchrow_arrayref;

✨ You can also assign a hashref, where the resultset has to be given as the value for the results key:

$dbh->{mock_add_resultset} = {
    results => [
        [ 'foo', 'bar' ],
        [ 'this_one', 'that_one' ],
        [ 'this_two', 'that_two' ],
    ],
};

The whole reason the hashref form exists? So you can add options, as described below. 💁‍♀️

You can associate a resultset with a particular SQL statement instead of adding them in the order they'll be fetched:

$dbh->{mock_add_resultset} = {
    sql     => 'SELECT foo, bar FROM baz',
    results => [
        [ 'foo', 'bar' ],
        [ 'this_one', 'that_one' ],
        [ 'this_two', 'that_two' ],
    ],
};

This returns the given results when the statement "SELECT foo, bar FROM baz" is prepared/executed. And heads up — they get returned every time that statement is prepared/executed, not just the first time. 🫶 Also worth noting: if you ever want to change the result set bound to a particular SQL statement, all you gotta do is add the result set again with the same SQL statement and DBD::Mock will just overwrite it. Clean.

If the sql parameter is a regular expression reference, then the results get returned for any SQL statements that match it:

$dbh->{mock_add_resultset} = {
    sql     => qr/^SELECT foo FROM/i,
    results => [
        [ 'foo' ],
        [ 'this_one' ],
    ],
};

Now, if an SQL statement matches both a specified-SQL-statement result set and a regular expression result set, the specified SQL statement takes precedence. And if two regular expression result sets match each other, the first one added wins. Priority queen. 👑

# Set up our first regex matching result set
$dbh->{mock_add_resultset} = {
    sql => qr/^SELECT foo/,
    results => [ [ 'foo' ], [ 200 ] ],
};

# Set up our second regex matching result set
#   Note - This results set would never be used as the one above will match
#   and thus take precedence
$dbh->{mock_add_resultset} = {
    sql => qr/^SELECT foo FROM/,
    results => [ [ 'foo' ], [ 300 ] ],
};

# Set up our first statically defined result set
# This result set will take precedence over the regex matching ones above
$dbh->{mock_add_resultset} = {
    sql => 'SELECT foo FROM bar',
    results => [[ 'foo' ], [ 50 ]]
};

# This query will be served by the first regex matching result set
my $sth = $dbh->prepare('SELECT foo FROM oof');
$sth->execute()

my ($result) = $sth->fetchrow_array();
is( $result, 200 );

# This quere will be served by the statically defined result set
$sth = $dbh->prepare('SELECT foo FROM bar');
$sth->execute();

my ($result2) = $sth->fetchrow_array();
is( $result2, 50 );

Also like, super important to know: the rows method returns the number of records stocked in the result set. So if your code/application uses the $sth->rows method for things like UPDATE and DELETE calls, you should stock the result set like this:

$dbh->{mock_add_resultset} = {
    sql     => 'UPDATE foo SET baz = 1, bar = 2',
    # this will appear to have updated 3 rows
    results => [[ 'rows' ], [], [], []],
};

# or ...

$dbh->{mock_add_resultset} = {
    sql     => 'DELETE FROM foo WHERE bar = 2',
    # this will appear to have deleted 1 row
    results => [[ 'rows' ], []],
};

And yeah, I'll fully admit this is not the most elegant way to go about it 😭 but it works for me for now, and until I can come up with a better method — or someone sends me a patch ;) — it will do for now.

If you want a given statement to fail, you'll have to use the hashref method and add a failure key. That key has to be handed an arrayref with the error number and error string, in that order:

$dbh->{mock_add_resultset} = {
    sql => 'SELECT foo FROM bar',
    results => DBD::Mock->NULL_RESULTSET,
    failure => [ 5, 'Ooops!' ],
};

And without the sql attribute, the next statement will fail in any case:

$dbh->{mock_add_resultset} = {
    results => DBD::Mock->NULL_RESULTSET,
    failure => [ 5, 'Ooops!' ],
};

💜 `mock_get_info`

This attribute can be used to set up values for get_info(). It takes a hashref of attribute_name/value pairs. Go peek at DBI for more info on the information types and what they actually mean. 📚

🌸 `mock_session`

This attribute can be used to set a current DBD::Mock::Session object. For more info on that, see the DBD::Mock::Session docs below. And ooh — this attribute can also be used to remove the current session from the $dbh simply by setting it to undef. Poof, gone. 🧚

🔥 `mock_last_insert_id`

This attribute gets incremented each time an INSERT statement is passed to prepare, on a per-handle basis. Its starting value can be set with the mock_start_insert_id attribute (see right below, bestie):

$dbh->{mock_start_insert_id} = 10;

my $sth = $dbh->prepare('INSERT INTO Foo (foo, bar) VALUES(?, ?)');

$sth->execute(1, 2);
# $dbh->{mock_last_insert_id} == 10

$sth->execute(3, 4);
# $dbh->{mock_last_insert_id} == 11

For more examples, please refer to the test file t/025_mock_last_insert_id.t. 📖

And if you wanna access last_insert_id using driver specific attributes like mysql_insertid and mariadb_insertid, then you can use Attribute Aliasing. Slay.

⭐ `mock_start_insert_id`

This attribute can be used to set a start value for the mock_last_insert_id attribute. It can also be used to effectively reset the mock_last_insert_id attribute as well. Fresh start energy.

This attribute can also be used with an ARRAY ref parameter — its behavior is slightly different in that, instead of incrementing the value for every prepare, it will only increment for each execute. That means you can use it across multiple execute calls in a single $sth. Its usage looks like this:

$dbh->{mock_start_insert_id} = [ 'Foo', 10 ];
$dbh->{mock_start_insert_id} = [ 'Baz', 20 ];

my $sth1 = $dbh->prepare('INSERT INTO Foo (foo, bar) VALUES(?, ?)');

my $sth2 = $dbh->prepare('INSERT INTO Baz (baz, buz) VALUES(?, ?)');

$sth1->execute(1, 2);
# $dbh->{mock_last_insert_id} == 10

$sth2->execute(3, 4);
# $dbh->{mock_last_insert_id} == 20

⚠️ Heads up: DBD::Mock's matching of table names in INSERT statements is fairly simple, so if your table names are quoted in the insert statement (INSERT INTO "Foo") then you need to quote the name for mock_start_insert_id too:

$dbh->{mock_start_insert_id} = [ q{"Foo"}, 10 ];

🦋 `mock_add_parser`

So DBI provides some simple parsing capabilities for SELECT statements, to make sure placeholders are bound properly. And typically you may just want to check after the fact that a statement is syntactically correct — or at least what you expect. Makes sense, standard behavior. ✨

But other times? You may want to parse the statement as it is prepared rather than after the fact. That's where this hook in the mock database driver comes in — it lets you provide your own parsing routine or object. Iconic, honestly.

The syntax is simple:

$dbh->{mock_add_parser} = sub {
    my ( $sql ) = @_;
    unless ( $sql =~ /some regex/ ) {
        die "does not contain secret fieldname";
    }
};

You can also stack more than one of these on a single handle. They get called in order, and the first one to fail halts the whole parsing process — no passing go, no collecting $200:

$dbh->{mock_add_parser} = \&parse_update_sql;
$dbh->{mock_add-parser} = \&parse_insert_sql;

Depending on the PrintError and RaiseError settings on the database handle, any parsing errors you hit will either warn or die. And no matter what happens, the statement handle comes back undef. 😭

Instead of handing it a subroutine reference, you can totally use an object. Only requirement: it implements a parse() method and takes a SQL statement as the only argument. So something like this should work (heads up — totally untested):

my $parser = SQL::Parser->new( 'mysql', { RaiseError => 1 } );
$dbh->{mock_add_parser} = $parser;

mock_data_sources & mock_add_data_sources — these two will dispatch to the Driver's properties of the same name. Simple as that 💅

🎀 Attribute Aliasing 🎀

Okay so basically this feature lets you alias one attribute to another attribute. So for example, you can alias a super commonly expected attribute like mysql_insertid onto something DBD::Mock already has, like mock_last_insert_id. Like, sure, you could just set mysql_insertid yourself, but this way it gets to take advantage of stuff like the autoincrementing of the mock_last_insert_id attribute. Bestie, it's giving ✨convenience✨.

The functionality is off by default so it doesn't cause any issues with backwards compatibility, but you can flip it on and off super easily:

# turn it on
$DBD::Mock::AttributeAliasing++;

# turn it off
$DBD::Mock::AttributeAliasing = 0;

Once you've turned it on, you'll need to pick a database-specific attribute aliasing table, like so:

DBI->connect('dbi:Mock:MySQL', '', '');

Or, by using the database name if you're using driver DSNs:

DBI->connect('dbi:Mock:host=localhost;port=3306;database=MySQL', '', '');

That MySQL in the DSN gets picked up, and the MySQL-specific attribute aliasing gets used. Slay. 🔥

Right now there's only minimal support for MySQL and MariaDB:

🌸 MySQL 🌸

Currently the mysql_insertid attribute for $dbh and $sth are aliased to the $dbh attribute mock_last_insert_id.

🦋 MariaDB 🦋

Currently the mariadb_insertid attribute for $dbh and $sth are aliased to the $dbh attribute mock_last_insert_id.

You can add more aliases though, using the DBD::Mock:_set_mock_attribute_aliases function (go peek at the source code for the deets 👀).

🌈 Database Driver Methods 🌈

last_insert_id — this one just returns the value of mock_last_insert_id. That's it. That's the whole method. 💜

So in order to capture begin_work(), commit(), and rollback(), DBD::Mock will create statements for them, exactly as if you had issued them in the appropriate SQL command line program. They go through the standard prepare()-execute() cycle, which means any custom SQL parsers will be triggered, and DBD::Mock::Session will need to know about these statements. ikyk 😌

begin_work — creates a statement with SQL of BEGIN WORK and no parameters.
commit — creates a statement with SQL of COMMIT and no parameters.
rollback — creates a statement with SQL of ROLLBACK and no parameters.

🫶 Statement Handle Properties 🫶

Active — returns true if the handle is a SELECT and has more records left to fetch, false otherwise. (Straight from the DBI, bestie.)
mock_statement — the SQL statement this statement handle was prepared with. So if the handle got created with:

my $sth = $dbh->prepare( 'SELECT * FROM foo' );

then this would return:

SELECT * FROM foo

The original statement is unmodified, so if you're checking against it in tests you probably want to use a regex instead of a straight equality check. (But like, if you use a phrasebook to store your SQL externally you're literally a step ahead... 👑)

mock_fields — the fields used by the statement. As mentioned elsewhere we do no analysis or parsing to find these, so you need to define them beforehand. That said, you don't actually need this very often. Note that this returns the exact same thing as the normal statement property FIELD.
mock_params — returns an arrayref of the parameters bound to this statement, in the order specified by the bind type. For instance, if you created and stocked a handle with:

my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
$sth->bind_param( 2, 'yes' );
$sth->bind_param( 1, 7783 );

this would return:

[ 7738, 'yes' ]

The same result happens if you pass the parameters via execute() instead:

my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
$sth->execute( 7783, 'yes' );

And the same thing using named parameters:

my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = :id AND is_active = :active' );
$sth->bind_param( ':id' => 7783 );
$sth->bind_param( ':active' => 'yes' );

mock_param_attrs — returns an arrayref of any attributes (parameter type) defined for bound parameters (note: you rarely need to define attributes for bound parameters). Where an attribute/type hasn't been set, that slot in the returned arrayref will be undef. e.g. for:

my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
$sth->bind_param( 2, 'yes' );
$sth->bind_param( 1 7783, SQL_INTEGER );

this would return:

[ SQL_INTEGER, undef ]

Passing parameters via execute() will always populate the array with undef, so for:

$sth->execute( 7783, 'yes' );

this would return:

[ undef, undef ]

mock_execution_history — returns an arrayref where each entry contains the details for one execution of the prepared statement. e.g. after:

my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
$sth->bind_param( 2, 'yes' );
$sth->bind_param( 1 7783, SQL_INTEGER );
$sth->execute();

$sth->execute( 1023, 'no' );

then $sth->{mock_execution_history} would be:

[
    {
        params => [ 7783, 'yes' ],
        attrs  => [ SQL_INTEGER, undef ],
    }, {
        params => [ 1023, 'no' ],
        attrs  => [ undef, undef ],
    }
]

mock_records — an arrayref of arrayrefs representing the records the mock statement was stocked with.
mock_num_records — the number of records the mock statement was stocked with; if it was never stocked it's still 0. (Some weirdos might expect undef... 🙈)
mock_num_rows — returns the same value as mock_num_records. And it's also what gets returned by the rows method of the statement handle.
mock_current_record_num — the current record the statement is on; returns 0 in the instances where you haven't yet called execute(), and also if you haven't yet called a fetch method after the execute.
mock_is_executed — whether execute() has been called against the statement handle. Returns 'yes' if so, 'no' if not.
mock_is_finished — whether finish() has been called against the statement handle. Returns 'yes' if so, 'no' if not.
mock_is_depleted — returns 'yes' if all the records in the recordset have been returned. If no fetch() was executed against the statement, or if no return data was set, this returns 'no'.
mock_my_history — returns a DBD::Mock::StatementTrack object which tracks all the actions performed by this statement handle. Most of those actions are separately available from the properties listed above, so you should literally never need this one. 💀

🧚 DBD::Mock::Pool 🧚

This module can be used to emulate Apache::DBI style DBI connection pooling. Just like with Apache::DBI, you must enable DBD::Mock::Pool before loading DBI.

use DBD::Mock qw(Pool);
# followed by ...
use DBI;

While this may not seem to make a lot of sense in a single-process testing scenario, it can be super useful when you're testing code which assumes a multi-process Apache::DBI pooled environment. Highkey a vibe. ⭐

🎀 DBD::Mock::StatementTrack 🎀

Okay so under the hood, this module is literally doing most of the work with a DBD::Mock::StatementTrack object. It's giving behind-the-scenes main character energy 💅. It's most useful when you're reviewing multiple statements at a time — otherwise you might wanna just use the mock_* statement handle attributes instead. iykyk.

✨ `new( %params )`

Takes these params, bestie:

return_data — arrayref of return data records
fields — arrayref of field names
bound_params — arrayref of bound parameters
bound_param_attrs — arrayref of bound parameter attributes

✨ `statement` (Statement attribute `mock_statement`)

Gets/sets the SQL statement used. That's it, that's the vibe.

✨ `fields` (Statement attribute `mock_fields`)

Gets/sets the fields to use for this statement.

✨ `bound_params` (Statement attribute `mock_params`)

Gets/sets the bound parameters to use for this statement.

✨ `return_data` (Statement attribute `mock_records`)

Gets/sets the data to return when asked — that is, when someone calls fetch on the statement handle. 👑

✨ `current_record_num` (Statement attribute `mock_current_record_num`)

Gets/sets the current record number.

✨ `is_active()` (Statement attribute `Active`)

Returns true if the statement is a SELECT and has more records to fetch, false otherwise. (This is from the DBI — see the 'Active' docs under 'ATTRIBUTES COMMON TO ALL HANDLES'.)

✨ `is_executed( $yes_or_no )` (Statement attribute `mock_is_executed`)

Sets the state of the tracker executed flag. Yes or no, your call 🫶.

✨ `is_finished( $yes_or_no )` (Statement attribute `mock_is_finished`)

If you set it to "yes", it tells the tracker that the statement is finished. Heads up though, this resets the current record number to 0 and clears out the array ref of returned records. Clean slate energy ✨.

✨ `is_depleted()` (Statement attribute `mock_is_depleted`)

Returns true if the current record number is greater than the number of records set to return. Basically "are we out of records" check 💀.

✨ `num_fields`

Returns the number of fields set in the fields parameter.

✨ `num_rows`

Returns the number of records in the current result set.

✨ `num_params`

Returns the number of parameters set in the bound_params parameter.

✨ `bound_param( $param_num, $value )`

Sets bound parameter $param_num to $value. Returns the arrayref of currently-set bound parameters. This corresponds to the bind_param statement handle call. Slay 🎀.

✨ `bound_param_trailing( @params )`

Pushes @params onto the list of already-set bound parameters. Just appending, no drama.

✨ `mark_executed()`

Tells the tracker that the statement has been executed, and resets the current record number to 0. Fresh start, bestie 🌸.

✨ `next_record()`

If the statement has been depleted (all records returned), it returns undef — sad but fair 😭. Otherwise it gets the current record for returning, increments the current record number, and returns the current record.

✨ `to_string()`

Tries to give a decent depiction of the object state for use in debugging. Not gonna lie, it's giving "let me explain myself" 🫶.

🦋 DBD::Mock::StatementTrack::Iterator 🦋

This object can be used to iterate through the current set of DBD::Mock::StatementTrack objects in the history — you get it by fetching the mock_all_history_iterator attribute from a database handle. It's super simple and it's literally just a convenience to make writing long test scripts easier (we love low-effort testing). Aside from the constructor (new), this object has the following methods:

✨ `next()`

Calling next returns the next DBD::Mock::StatementTrack object in the history. If there are no more DBD::Mock::StatementTrack objects available, then this method returns false. That's the whole thing 💅.

✨ `reset()`

This resets the internal pointer back to the beginning of the statement history. Rewind, bestie ⏪.

🌈 DBD::Mock::Session 🌈

The DBD::Mock::Session object is an alternate means of specifying the SQL statements and result sets for DBD::Mock. The whole idea: you specify a complete 'session' of usage, and it gets verified through DBD::Mock. Iconic behaviour, honestly. Here's an example:

    my $session = DBD::Mock::Session->new('my_session' => (
        {
            statement => "SELECT foo FROM bar", # as a string
            results   => [[ 'foo' ], [ 'baz' ]]
        },
        {
            statement => qr/UPDATE bar SET foo \= \'bar\'/, # as a reg-exp
            results   => [[]]
        },
        {
            statement => sub {  # as a CODE ref
                    my ($SQL, $state) = @_;
                    return $SQL eq "SELECT foo FROM bar";
                    },
            results   => [[ 'foo' ], [ 'bar' ]]
        },
        {
            # with bound parameters
            statement    => "SELECT foo FROM bar WHERE baz = ? AND borg = ?",
            # check exact bound param value,
            # then check it against regexp
            bound_params => [ 10, qr/\d+/ ],
            results      => [[ 'foo' ], [ 'baz' ]]
        }
    ));

So basically, a session is a list of HASH references that we call 'states'. Each state has a statement and a set of results. If DBD::Mock finds a session in the mock_session attribute, then it passes the current $dbh and SQL statement to that DBD::Mock::Session. The SQL statement gets checked against the statement field in the current state. If it passes, then the results of the current state get fed to DBD::Mock through the mock_add_resultset attribute. Then we advance to the next state in the session and wait for the next call through DBD::Mock. And if at any point the SQL statement doesn't match the current state's statement, or the session runs out of available states — boom, an error gets raised (and propagated through the normal DBI error handling based on your values for RaiseError and PrintError). No cap, watch out for that 💀.

As you can see in the session element up there, bound parameters can also be supplied and tested. In that statement, the SQL is compared, and then when the statement is executed, the bound parameters are also checked. The bound parameters must match in both number of parameters and the parameters themselves, or an error will be raised. Strict queen behaviour 👑.

And as you can also see in the example above, statement fields can come in many forms:

The simplest is a string, which gets compared using eq against the currently running statement.
Next is a reg-exp reference — this too gets compared against the currently running statement.
The last option is a CODE ref, which is sort of a catch-all to allow for a wide range of SQL comparison approaches (including using modules like SQL::Statement or SQL::Parser for detailed functional comparisons). The first argument to the CODE ref is the currently active SQL statement to compare against, and the second argument is a reference to the current state HASH (in case you need to alter the results or store extra information). The CODE is evaluated in boolean context and throws an exception if it's false.

✨ `new ($session_name, @session_states)`

A $session_name can optionally be specified, along with at least one @session_states. If you don't specify a $session_name, then a default one gets created for you (we love a girlboss default 💅). The @session_states must all be HASH references as well — if this condition fails, an exception will be thrown. So like, behave 🫶.

✨ `verify_statement ($dbh, $SQL)`

This checks the $SQL against the current state's statement value, and if it passes, adds the current state's results to the $dbh. If for some reason the statement value is bad — not one of the prescribed types — an exception is thrown. See above for more details ✨.

✨ `verify_bound_params ($dbh, $params)`

If the bound_params slot is available in the current state, this checks the $params against the current state's bound_params value. Both the number of parameters and the parameters themselves must match, or an error will be raised. No wiggle room, bestie 🙈.

✨ `reset`

Calling this method resets the state of the session object so it can be reused. Recycling era ♻️💖.

✨ EXPERIMENTAL FUNCTIONALITY ✨

🚨 bestie, hear me: all functionality listed here is highly experimental and should be used with great caution (if at all). Like, do not build your whole personality on this, okay? 😭💅

🫶 Connection Callbacks

okay so this feature lets you define callbacks that get executed when DBI->connect is called. connect-time main character energy, fr fr. ✨

To set a series of callbacks you use the DBD::Mock::dr::set_connect_callbacks function:

use DBD::Mock::dr;

DBD::Mock::dr::set_connect_callbacks( sub {
    my ( $dbh, $dsn, $user, $password, $attributes ) = @_;

    $dbh->{mock_add_resultset} = {
        sql => 'SELECT foo FROM bar',
        results => [[ 'foo' ], [ 10 ]]
    };
} );

To set more than one callback, you can simply add extra callbacks to your call to DBD::Mock::dr::set_connect_callbacks:

DBD::Mock::dr::set_connect_callbacks(
    sub {
        my ( $dbh, $dsn, $user, $password, $attributes ) = @_;

        $dbh->{mock_add_resultset} = {
            sql => 'SELECT foo FROM bar',
            results => [[ 'foo' ], [ 10 ]]
        };
    },

    sub {
        my ( $dbh, $dsn, $user, $password, $attributes ) = @_;

        $dbh->{mock_add_resultset} = {
            sql => 'SELECT foo FROM bar',
            results => [[ 'foo' ], [ 10 ]]
        };
    }
);

Or you can extend the existing set of callbacks with the DBD::Mock::dr::add_connect_callbacks function:

DBD::Mock::dr::add_connect_callbacks( sub {
    ( my $dbh, $dsn, $user, $password, $attributes ) = @_;

    $dbh->{mock_add_resultset} = {
        sql => 'SELECT bar FROM foo',
        results => [[ 'bar' ], [ 50 ]]
    };
} );

🧚 table_info

This feature adds support for DBI's table_info method (note: this functionality is unstable when used with DBI version 1.634 and below 🫠).

To mock the table info for a search of the testSchema database schema you would use the following:

$dbh->{mock_add_table_info} = {
    cataloge   => undef,
    schema     => 'testSchema',
    table      => undef,
    type       => undef,
    table_info => [
        [ 'TABLE_CAT', 'TABLE_SCHEM', 'TABLE_NAME', 'TABLE_TYPE', 'REMARKS' ],
        [ undef,       'testSchema',  'foo',        'TABLE',      undef     ],
        [ undef,       'testSchema',  'bar',        'VIEW',       undef     ],
    ],
};

The cataloge, schema, table and type parameters need to explicitly match what you expect table_info to be called with (note: table_info treats undef and '' the same). 👀

Similar to the mock_results_sets, the table_info parameter's first entry is an arrayref of column names, and the rest are the values of the rows returned (one arrayref per row).

If you need to cover listing schemas then you'd use:

$dbh->{mock_add_table_info} = {
    schema     => '%',
    table_info => [
        [ 'TABLE_CAT', 'TABLE_SCHEM',  'TABLE_NAME', 'TABLE_TYPE', 'REMARKS' ],
        [ undef,       'testSchema',   undef,        undef,        undef     ],
        [ undef,       'testSchema_2', undef,        undef,        undef     ],
    ],
}

To clear the current mocked table info set the database handle's mock_clear_table_info attribute to 1:

$dbh->{mock_clear_table_info} = 1;

🎀 Result Set Callbacks

If you need your result sets to be more dynamic (e.g. if they need to return different results based upon bound parameters) then you can use a callback. lowkey one of the most useful little tricks here, ngl. 🦋

$dbh->{mock_add_resultset} = {
    sql => 'SELECT a FROM b WHERE c = ?',
    callback => sub {
        my @bound_params = @_;

        my %result = (
            fields => [ "a" ],
            rows => [[ 1] ]
        );

        if ($bound_params[0] == 1) {
            $result{rows} = [ [32] ];
        } elsif ($bound_params[0] == 2) {
            $result{rows} = [ [43] ];
        }

        return %result;
    },
};

my $sth = $dbh->prepare('SELECT a FROM b WHERE c = ?');

my $rows = $sth->execute(1);
my ($result) = $sth->fetchrow_array();  # $result will be 32

$rows = $sth->execute(2);
($result) = $sth->fetchrow_array();  # $result this time will be 43

$rows = $sth->execute(33); # $results this time will be 1

($result) = $sth->fetchrow_array();

The callback needs to return a hash with a rows key that is an array ref of arrays containing the values to return as the answer to the query. In addition a fields key can also be returned with an array ref of field names. If a fields key isn't present in the returned hash then the fields will be taken from the mock_add_resultset's results parameter.

$dbh->{mock_add_resultset} = {
    sql => 'SELECT x FROM y WHERE z = ?',
    results => [ ["x"] ],
    callback => sub {
        my @bound_params = @_;

        my %result = ( rows => [[ 1] ] );

        if ($bound_params[0] == 1) {
            $result{rows} = [ [32] ];
        } elsif ($bound_params[0] == 2) {
            $result{rows} = [ [43] ];
        }

        return %result;
    },
};

my $sth = $dbh->prepare('SELECT x FROM y WHERE z = ?');

my $rows = $sth->execute(1);
my ($result) = $sth->fetchrow_array();  # $result will be 32

$rows = $sth->execute(2);
($result) = $sth->fetchrow_array();  # $result will be 43

$rows = $sth->execute(33);
($result) = $sth->fetchrow_array();  # $result will be 1

By default result sets which only define their field names in their callback return values will have a NUM_OF_FIELDS property of 0 until after the statement has actually been executed. This is to make sure that DBD::Mock stays compatible with previous versions. If you need the NUM_OF_FIELDS property to be undef in this situation then set the $DBD::Mock::DefaultFieldsToUndef flag to 1.

If you're mocking an INSERT statement with a callback and you want to explicitly set the database's last_insert_id value then you can use the last_insert_id key in the result set. If you don't specify a last_insert_id then the standard DBD::Mock logic for generating an value for the last inserted item will be followed. This will allow you to mock MySQL/MariaDB INSERT queries that use ON DUPLICATE KEY UPDATE logic to set the last_insert_id. 🍓

$dbh->{mock_add_resultset} = {
    sql => 'INSERT INTO y ( x ) VALUES ( ? ) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID( id )',
    callback => sub {
        my @bound_params = @_;

        my %result = (
            fields => [],
            rows => [],
            last_insert_id => 99,
        );

        return %result;
    },
};

💅 Result Set Custom Attributes

If you're mocking a database driver that has it's own custom attributes attached to its statement handles then you can use the result sets prepare_attributes and execute_attributes options. very much giving “customize your lil statement handle” energy. ✨

The prepare_attributes option takes a hashref that maps statement handle attribute names to their values. The attributes are set at the point that the statement is prepared.

$dbh->{mock_add_resultset} = {
    sql => 'SELECT foo FROM bar',
    prepare_attributes => {
        sqlite_unprepared_statements => '   ',
    },
    results => [[ 'foo' ], [ 10 ]]
};

The execute_attributes option also takes a hashref that maps statement handle attribute names to their values, however these will only be set when the statement is executed.

$dbh->{mock_add_resultset} = {
    sql => 'SELECT foo FROM bar',
    execute_attributes => {
        syb_result_type => 1,
    },
    results => [[ 'foo' ], [ 10 ]]
};

If an attribute is also present in the prepare_attributes option then the prepare_attributes version will take precedence up to the point the statement handle is executed, at which point the execute_attributes version will take precedence.

It is also possible to set execute_attributes from a result set's callback by returning them under the execute_attributes key in your callback's response.

$dbh->{mock_add_resultset} = {
    sql => 'SELECT baz FROM qux',
    callback => sub {
        my @bound_params = @_;

        my %result = (
            fields => [ 'baz'],
            rows => [],
            execute_attributes => {
                foo => 'bar'
            },
        );

        return %result;
    }
};

If a result set has an execute_attributes option and a callback that also returns an execute_attributes key then the callback's execute_attributes value will take precedence.

💀 BUGS

🙈 Odd `$dbh` attribute behavior

When writing the test suite I encountered some odd behavior with some $dbh attributes. I still need to get deeper into how DBD's work to understand what it is that is actually doing wrong. bestie, it's giving mystery bug energy and I am not okay 😭💀

🎀 TO DO

🦋 Make DBD specific handlers

Each DBD has its own quirks and issues, it would be nice to be able to handle those issues with DBD::Mock in some way. I have an number of ideas already, but little time to sit down and really flesh them out. If you have any suggestions or thoughts, feel free to email me with them. 🫶

🌈 Enhance the `DBD::Mock::StatementTrack` object

I would like to have the DBD::Mock::StatementTrack object handle more of the mock_* attributes. This would encapsulate much of the mock_* behavior in one place, which would be a good thing. 💜

👀 SEE ALSO 👀

DBI — the big one, obviously 💅
DBD::NullP — this one provided a good starting point 🌱
Test::MockObject — this one provided the approach 💡
the Test::MockObject article — http://www.perl.com/pub/a/2002/07/10/tmo.html
"Perl Code Kata: Testing Databases" — http://www.perl.com/pub/a/2005/02/10/database_kata.html

💖 ACKNOWLEDGEMENTS 💖

Okay bestie, this is the shoutout section, and everyone here ate 🫶

Ryan Gerry — thanks for his patch in RT #26604 💅
Marc Beyer — thanks for his patch in RT #16951 ✨
Justin DeVuyst — thanks for the mock_connect_fail idea 💡
Thilo Planz — thanks for the code for bind_param_inout 🧚
Shlomi Fish — thanks for help tracking down RT Bug #11515 🔍
Collin Winter — thanks for the patch to fix the begin_work(), commit() and rollback() methods 🛠️
Andrew McHarg <amcharg@acm.org> — thanks for fetchall_hashref(), fetchrow_hashref() and selectcol_arrayref() methods and tests 💖
Andrew W. Gibbs — thanks for the mock_last_insert_ids patch and test 🌟
Chas Owens — thanks for patch and test for the mock_can_prepare, mock_can_execute, and mock_can_fetch features 👑
Tomas Zemresfor — thanks for the unit test in RT #71438 🧪
Bernhard Graf — thanks for multiple patches fixing a range of issues and adding a new One Shot Failure feature to mock_add_resultset 🔥
Erik Huelsmann — thanks for testing the new result set custom attributes feature 🌈

🎀 COPYRIGHT 🎀

Legally iconic, lowkey 🫶

And the license tea ☕: This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

👑 AUTHORS 👑

The main characters behind this whole vibe ✨

Chris Winters <chris@cwinters.com>
Stevan Little <stevan@iinteractive.com>
Rob Kinyon <rob.kinyon@gmail.com>
Mariano Wahlmann <dichoso _at_ gmail.com>
Jason Cooper <JLCOOPER@cpan.org>

💖 Footer sign-off: this page is perl v5.32.1, dated 2021-02-27, and it's DBD::Mock(3pm). Byeee, bestie ✨

📦 where this page comes from

debian bookworm: libdbd-mock-perl 1.59-1 · GPL-1.0-or-later OR Artistic-1.0-Perl
debian trixie: libdbd-mock-perl 1.59-1 · GPL-1.0-or-later OR Artistic-1.0-Perl
ubuntu jammy: libdbd-mock-perl 1.59-1 · GPL-1.0-or-later OR Artistic-1.0-Perl
ubuntu noble: libdbd-mock-perl 1.59-1 · GPL-1.0-or-later OR Artistic-1.0-Perl
ubuntu resolute: libdbd-mock-perl 1.59-1 · GPL-1.0-or-later OR Artistic-1.0-Perl
ubuntu stonking: libdbd-mock-perl 1.59-1 · GPL-1.0-or-later OR Artistic-1.0-Perl
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash-1017 on 2026-11-06: a language model's rewrite of the original, under its terms

DBD::Mock(3pm)

💖 DBD::Mock 💖

✨ NAME ✨

🌸 SYNOPSIS 🌸

🦋 DESCRIPTION 🦋

🧚 How does it work?

🌈 A Word of Warning

👑 DBD::Mock

🍓 Database Driver Properties

🎀 Database Handle Properties

🎀 mock_add_resultset( \@resultset | \%resultset_and_options )

💜 mock_get_info

🌸 mock_session

🔥 mock_last_insert_id

⭐ mock_start_insert_id

🦋 mock_add_parser

🎀 Attribute Aliasing 🎀

🌸 MySQL 🌸

🦋 MariaDB 🦋

🌈 Database Driver Methods 🌈

🫶 Statement Handle Properties 🫶

🧚 DBD::Mock::Pool 🧚

🎀 DBD::Mock::StatementTrack 🎀

✨ new( %params )

✨ statement (Statement attribute mock_statement)

✨ fields (Statement attribute mock_fields)

✨ bound_params (Statement attribute mock_params)

✨ return_data (Statement attribute mock_records)

✨ current_record_num (Statement attribute mock_current_record_num)

✨ is_active() (Statement attribute Active)

✨ is_executed( $yes_or_no ) (Statement attribute mock_is_executed)

✨ is_finished( $yes_or_no ) (Statement attribute mock_is_finished)

✨ is_depleted() (Statement attribute mock_is_depleted)

✨ num_fields

✨ num_rows

✨ num_params

✨ bound_param( $param_num, $value )

✨ bound_param_trailing( @params )

✨ mark_executed()

✨ next_record()

✨ to_string()

🦋 DBD::Mock::StatementTrack::Iterator 🦋

✨ next()

✨ reset()

🌈 DBD::Mock::Session 🌈

✨ new ($session_name, @session_states)

✨ verify_statement ($dbh, $SQL)

✨ verify_bound_params ($dbh, $params)

✨ reset

✨ EXPERIMENTAL FUNCTIONALITY ✨

🫶 Connection Callbacks

🧚 table_info

🎀 Result Set Callbacks

💅 Result Set Custom Attributes

💀 BUGS

🙈 Odd $dbh attribute behavior

🎀 TO DO

🦋 Make DBD specific handlers

🌈 Enhance the DBD::Mock::StatementTrack object

👀 SEE ALSO 👀

💖 ACKNOWLEDGEMENTS 💖

🎀 COPYRIGHT 🎀

👑 AUTHORS 👑

🎀 `mock_add_resultset( \@resultset | \%resultset_and_options )`

💜 `mock_get_info`

🌸 `mock_session`

🔥 `mock_last_insert_id`

⭐ `mock_start_insert_id`

🦋 `mock_add_parser`

✨ `new( %params )`

✨ `statement` (Statement attribute `mock_statement`)

✨ `fields` (Statement attribute `mock_fields`)

✨ `bound_params` (Statement attribute `mock_params`)

✨ `return_data` (Statement attribute `mock_records`)

✨ `current_record_num` (Statement attribute `mock_current_record_num`)

✨ `is_active()` (Statement attribute `Active`)

✨ `is_executed( $yes_or_no )` (Statement attribute `mock_is_executed`)

✨ `is_finished( $yes_or_no )` (Statement attribute `mock_is_finished`)

✨ `is_depleted()` (Statement attribute `mock_is_depleted`)

✨ `num_fields`

✨ `num_rows`

✨ `num_params`

✨ `bound_param( $param_num, $value )`

✨ `bound_param_trailing( @params )`

✨ `mark_executed()`

✨ `next_record()`

✨ `to_string()`

✨ `next()`

✨ `reset()`

✨ `new ($session_name, @session_states)`

✨ `verify_statement ($dbh, $SQL)`

✨ `verify_bound_params ($dbh, $params)`

✨ `reset`

🙈 Odd `$dbh` attribute behavior

🌈 Enhance the `DBD::Mock::StatementTrack` object