Test::SQL::Data(3)

💖 Test::SQL::Data — the manual, but make it cute 💖

okay bestie, gather round, because this is Test::SQL::Data, and it's literally here to help you run SQL tests — like, preparing a database and matching your results, all in one lil package. 🫶

---

✨ NAME ✨

Test::SQL::Data — helps you run SQL tests, specifically: getting the database prepped and matching your results. slay. 💅

---

🌸 SYNOPSYS 🌸

(yeah the original spells it like that, and honestly? iconic. we keep it.)

use Test::More;
use Test::SQL::Data;

my $test = Test::SQL::Data->new() or exit;

use_ok('My::Module');
my $n_matches = 1;

$My::Module->run_something( dbh => $test->dbh );

$n_matches += $test->match_table('tablename','expected_tablename.sql');

done_testing($n_matches);

---

🦋 DESCRIPTION 🦋

So the whole point of Test::SQL::Data is to hand your module a clean database to play with. When the module loads, it preps the database for you. You can have it totally empty, or you can pre-load some SQL code before your tests even run. Then you get to use the module again to check whether your expected results actually match what's in the database tables. no cap.

🧼 Clean Database for each test

Every single test gets its own clean database connection. It can be completely empty, or you can make it load up tables and rows so your code has something to run against.

And here's the best part: you do not need a SQL server on the computer running the tests. It's backed on SQLite, so literally all you need is to install the module DBD::SQLite and you're good. That's the only driver supported by now, fr fr.

Heads up bestie: the database contents are not removed after the test, so you can go inspect the results. It only gets erased the next time you run the test. 💖

🔌 Database test connection

Once the database gets created at the construction of the test, you're handed a connection to the test database. Both DBI and DBIx::Connector objects are available, and you have to use one of them to call the module you want to test. pick your fighter. 🎀

🎯 Table matching

If you wanna match results, you've got two roads: either you issue SQL select statements, or you use the Test::SQL::Data API to match large amounts of information. To do the API thing, you have to provide expected SQL dumps of tables. Every field of every row of the tables gets matched and it'll raise an ok or a not_ok. The number of tests run gets returned to you. 📊

At the end of the test you must call done_testing from Test::More with the number of tests run. do not forget. that's the rule. periodt.

🧾 REQUIREMENTS

• DBD::SQLite
• DBIx::Connector
• Test::More

---

👑 CONSTRUCTOR 👑

new

Creates a brand new database file with SQLite and returns a new test object. If some required module is missing, it raises a skip. (not me being forgiving about your missing deps 💀)

my $test_sql = Test::SQL::Data->new();

my $test_sql = Test::SQL::Data->new( config => "t/etc/test_config.cfg");

my $test_sql = Test::SQL::Data->new( require => 'Some::Package');
my $test_sql = Test::SQL::Data->new( require => ['Some::Package'
                                          ,'Another::Package']);

Peep Test Configuration down below to learn how to easily pre-load SQL data into that empty database it creates for you. ✨

---

🛠️ METHODS 🛠️

connect

Connects to the database. Honestly? You don't need to do this. It already runs at new().

my $connection = connect();

my $connection = connect('t/file.db');

sql

Runs SQL in the internal test database. your own personal sandbox 🏖️

$test_sql->sql('CREATE TABLE a (field_one int, ... )');

load_sql_file

Loads SQL statements into the temporary database from a file.

parameters

• SQL file

file_db

Returns the current database file we're using to store data. So far it's a SQLite database.

dbh

Returns the current database handler DBI. 🫶

connector

Returns the current DBIx::Connector.

match_table

Matches all the fields and rows of a table against expected SQL data. The SQL file must have the tablename with the prefix expected, and insert statements as the rows to match against:

parameters

• table
• expected_sql_file

returns

number of ok matches

expected_sql_file example

/* etc/expected_something.sql; */
CREATE TABLE(expected_something) ( id integer, name char(10) );
INSERT INTO expected_something VALUES( 3,'foo' );

This will try to match the same rows in the table something, and it will return a 2 if it succeeded. That comes from number_of_rows*number_of_fields. (math ate, honestly. 🧮)

One way to generate those files is using the .dump command in sqlite and then editing the output:

sqlite t/db/something.db .dump > t/etc/expected_something.sql

dir_db

Returns the directory where it saves the temporary database. 📁

---

🌈 TESTS CONFIGURATION 🌈

📄 One SQL file per test

On starting, it loads into the just-created database the sql file related to the test. It searches for a file in the t/etc directory called like the test but ended with the extension sql.

ie: t/35_foo.t -> t/etc/35_foo.sql

Multiple SQL statements can be declared in the sql file. So you can put a CREATE TABLE, then do some inserts, or whatever. go off. 💅

📚 Multiple SQL files

Instead of a single sql file, sometimes you wanna execute other sql files from other tests. You can create a file in t/etc/name_of_the_test.cfg. Add there a list of SQL files to run:

t/etc/40_bar.cfg

sql:
  - 35_foo.sql
  - 55_whoosa.sql

At the creation of the object it will search for a file called t/etc/name_of_the_test.cfg and it will be used as a config. You can also pass it straight to the constructor:

my $test_sql = Test::SQL::Data->new( config => "t/etc/another_config_file.cfg");

---

🐬 Dumping data from other DataBases 🐬

MySQL

Contents of MySQL tables can be dumped and used for the tests. After the dump, you may have to manually edit the contents of the SQL file to be loaded in the SQLite backend. (manual labor, unfortunately 😭)

$ mysqldump --compatible=ansi --skip-extended-insert --compact database table

Manual Changes

• Auto Increment field: id_foo integer primary key autoincrement
• There is no unsigned int, use integer
• There is no enum, use varchar
• Timestamps: "date_updated" datetime default current_timestamp
• Escape ' with '' instead \'

---

🔍 Debugging 🔍

You can inspect the execution doing:

DEBUG=1 make test

The result SQLite database for each test is in t/db_name_of_test.db. go look, bestie, the receipts are right there. 🕵️

---

👀 SEE ALSO 👀

• Test::More

---

💜 AUTHOR 💜

Francesc Guasch <frankie@etsetb.upc.edu>

---

©️ COPYRIGHT AND LICENSE ©️

This software is copyright (c) 2013 by Francesc Guasch.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

---

and that's the whole page, bestie!! ✨ this lil number came to you courtesy of perl v5.42.0, dated 2025-07-25, and it lives its life as Test::SQL::Data(3) in the manual pages. 💖 🎀 🦋