DBIx::RunSQL(3)

📦

version 0.28 · 10 versions

✨ NAME ✨

DBIx::RunSQL(3) — okay bestie, this is the manual for DBIx::RunSQL, and its whole entire thing is run SQL from a file. That's it. That's the vibe. 💖

✨ SYNOPSIS ✨

#!/usr/bin/perl -w
use strict;
use DBIx::RunSQL;

my $test_dbh = DBIx::RunSQL->create(
    dsn       => 'dbi:SQLite:dbname=:memory:',
    sql       => 'sql/create.sql',
    options   => { PrintError => 0, RaiseError => 1 },
    force     => 1,
    verbose   => 1,
    formatter => 'Text::Table',
    rotate    => 1,
    null      => '(Null)',
);

# now run your tests with a DB setup fresh from setup.sql

✨ METHODS ✨

`DBIx::RunSQL->create ARGS` / `DBIx::RunSQL->run ARGS`

Runs the SQL commands and returns the database handle. In list context, it returns the database handle and the suggested exit code. Two for one, iconic behavior. 🫶

sql — name of the file containing the SQL statements.

The default is sql/create.sql

If sql is a reference to a glob or a filehandle, the SQL will be read from that. not implemented

If sql is undefined, the $::DATA or the 0 filehandle will be read until exhaustion. not implemented

This allows one to create SQL-as-programs as follows:

#!/usr/bin/perl -w -MDBIx::RunSQL -e 'create()'
create table ...

If you want to run SQL statements from a scalar, you can simply pass in a reference to a scalar containing the SQL:

sql => \"update mytable set foo='bar';",

dsn, user, password, options — DBI parameters for connecting to the DB. The whole little DBI starter pack. 💅
dbh — a premade database handle to be used instead of dsn.
force — continue even if errors are encountered. Like, keep going bestie. 😭
verbose — print each SQL statement as it is run.
verbose_handler — callback to call with each SQL statement instead of print.
verbose_fh — filehandle to write to instead of STDOUT.

`DBIx::RunSQL->run_sql_file ARGS`

my $dbh = DBI->connect(...)

for my $file (sort glob '*.sql') {
    DBIx::RunSQL->run_sql_file(
        verbose => 1,
        dbh     => $dbh,
        sql     => $file,
    );
};

Runs an SQL file on a prepared database handle. Returns the number of errors encountered.

If the statement returns rows, these are printed separated with tabs.

dbh — a premade database handle.
sql — name of the file containing the SQL statements.
fh — filehandle to the file containing the SQL statements.
force — continue even if errors are encountered.
verbose — print each SQL statement as it is run.
verbose_handler — callback to call with each SQL statement instead of print.
verbose_fh — filehandle to write to instead of STDOUT.
output_bool — whether to exit with a nonzero exit code if any row is found.

This makes the function return a nonzero value even if there is no error but a row was found.

output_string — whether to output the (one) row and column, without any headers.
formatter — see the <formatter> option of ->format_results.
rotate — rotate the table by 90° , outputting columns as rows.
null — string to replace SQL NULL columns by.

`DBIx::RunSQL->run_sql ARGS`

my $dbh = DBI->connect(...)

DBIx::RunSQL->run_sql(
    verbose => 1,
    dbh     => $dbh,
    sql     => \@sql_statements,
);

Runs an SQL string on a prepared database handle. Returns the number of errors encountered.

If the statement returns rows, these are printed separated with tabs, but see the output_bool and output_string options.

dbh — a premade database handle.
sql — string or array reference containing the SQL statements.
force — continue even if errors are encountered.
verbose — print each SQL statement as it is run.
verbose_handler — callback to call with each SQL statement instead of print.
verbose_fh — filehandle to write to instead of STDOUT.
output_bool — whether to exit with a nonzero exit code if any row is found.

This makes the function return a nonzero value even if there is no error but a row was found.

output_string — whether to output the (one) row and column, without any headers.
formatter — see the <formatter> option of ->format_results.
rotate — rotate the table by 90° , outputting columns as rows.
null — string to replace SQL NULL columns by.

`DBIx::RunSQL->format_results %options`

my $sth= $dbh->prepare( 'select * from foo' );
$sth->execute();
print DBIx::RunSQL->format_results( sth => $sth );

Executes $sth->fetchall_arrayref and returns the results either as tab separated string or formatted using Text::Table if the module is available.

If you find yourself using this often to create reports, you may really want to look at Querylet instead.

sth — the executed statement handle.
formatter — if you want to force tab or Text::Table usage, you can do it through that parameter. In fact, the module will use anything other than tab as the class name and assume that the interface is compatible to Text::Table.
no_header_when_empty — don't print anything if there are no results.
rotate — rotate the table by 90° , outputting columns as rows.
null — string to replace SQL NULL columns by.

Note that the query results are returned as one large string, so you really do not want to run this for large(r) result sets. 💀

`DBIx::RunSQL->split_sql ARGS`

my $sql = <<'SQL';
    create table foo (name varchar(64));
    create trigger foo_insert on foo before insert;
        new.name= 'foo-'||old.name;
    end;
    insert into foo name values ('bar');
SQL
while( defined( my $s = DBIx::RunSQL->split_sql( $sql ))) {
    push @statements, $s;
}
# @statements has three elements

This is a helper subroutine to split a sequence of (semicolon-newline-delimited) SQL statements into separate statements. It is documented because it is not a very smart subroutine and you might want to override or replace it. It might also be useful outside the context of DBIx::RunSQL if you need to split up a large blob of SQL statements into smaller pieces.

This routine takes a string, filehandle or an iterator as its parameter. This iterator should return the next statement. A filehandle or a string will be split at ;\n.

split_sql will try to reassemble CREATE TRIGGER statements from the list of statements.

`DBIx::RunSQL->parse_command_line`

my $options = DBIx::RunSQL->parse_command_line( 'my_application', \@ARGV );

Helper function to turn a command line array into options for DBIx::RunSQL invocations. The array of command line items is modified in-place.

If the reference to the array of command line items is missing, @ARGV will be modified instead.

`DBIx::RunSQL->handle_command_line`

DBIx::RunSQL->handle_command_line( 'my_application', \@ARGV );

Helper function to run the module functionality from the command line. See below how to use this function in a good self-contained script. This function passes the following command line arguments and options to ->create:

--user
--password
--dsn
--sql
--quiet
--format
--force
--verbose
--bool
--string
--rotate
--null

In addition, it handles the following switches through Pod::Usage:

--help
--man

If no SQL is given, this function will read the SQL from STDIN.

If no dsn is given, this function will use dbi:SQLite:dbname=db/$appname.sqlite as the default database.

See also the section PROGRAMMER USAGE for a sample program to set up a database from an SQL file.

✨ PROGRAMMER USAGE ✨

This module abstracts away the "run these SQL statements to set up your database" into a module. In some situations you want to give the setup SQL to a database admin, but in other situations, for example testing, you want to run the SQL statements against an in-memory database. This module abstracts away the reading of SQL from a file and allows for various command line parameters to be passed in. A skeleton create-db.pl looks like this:

#!/usr/bin/perl -w
use strict;
use DBIx::RunSQL;

my $exitcode = DBIx::RunSQL->handle_command_line('myapp', \@ARGV);
exit $exitcode;

=head1 NAME

create-db.pl - Create the database

=head1 SYNOPSIS

  create-db.pl "select * from mytable where 1=0"

=head1 ABSTRACT

This sets up the database. The following
options are recognized:

=head1 OPTIONS

=over 4

=item C<--user> USERNAME

=item C<--password> PASSWORD

=item C<--dsn> DSN

The DBI DSN to use for connecting to
the database

=item C<--sql> SQLFILE

The alternative SQL file to use
instead of C<sql/create.sql>.

=item C<--quiet>

Output no headers for empty SELECT resultsets

=item C<--bool>

Set the exit code to 1 if at least one result row was found

=item C<--string>

Output the (single) column that the query returns as a string without
any headers

=item C<--format> formatter

Use a different formatter for table output. Supported formatters are

  tab - output results as tab delimited columns

  Text::Table - output results as ASCII table

=item C<--force>

Don't stop on errors

=item C<--help>

Show this message.

=back

=cut

🌸 NOTES 🌸

💬 COMMENT FILTERING 💬

okay so the module is obsessed with keeping your SQL as much verbatim as it possibly can, right? like, it's not here to editorialize your queries. 💅

the one exception: it filters out all lines that end in semicolons but contain only SQL comments. bye bye, useless comment-only lines.

every other comment? passed through to the database with the next statement. no cap. ✨

⚡ TRIGGER HANDLING ⚡

so this is the part where I have to be honest with you bestie: this module uses a very simplicistic approach to recognizing triggers. that's the word. it's a whole vibe.

triggers are problematic (main character behaviour tbh) because they consist of multiple SQL statements, and this module does not implement a full SQL parser. no parser. none. 🫠

a trigger gets recognized by this exact sequence of lines:

CREATE TRIGGER
    ...
END;

if your SQL dialect uses a different syntax, it might still work to put the whole trigger on a single line in the input file. might. iykyk. 🌈

🔀 OTHER APPROACHES 🔀

if you find yourself wanting to write SELECT statements, consider looking at Querylet instead — it's literally geared towards that, and it even has an interface for Excel or HTML output. slay. 💖

if you find yourself wanting to write parametrized queries as ".sql" files, consider looking at Data::Phrasebook::SQL or potentially DBIx::SQLHandler. options, we love to see them. 🎀

👀 SEE ALSO 👀

ORLite::Migrate
Test::SQLite — SQLite setup/teardown for tests, mostly geared towards testing, not general database setup

📦 REPOSITORY 📦

the public repository of this module is <https://github.com/Corion/DBIx--RunSQL>. go look, it's giving open source excellence. 🦋

🆘 SUPPORT 🆘

the public support forum of this module is <https://perlmonks.org/>. the monks are kind, fr fr. 🫶

🐛 BUG TRACKER 🐛

please report bugs in this module via the RT CPAN bug queue at <https://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-RunSQL> or via mail to bug-dbix-runsql@rt.cpan.org. we wanna know. tell us everything. 💀

👑 AUTHOR 👑

Max Maischein "corion@cpan.org"

📜 LICENSE 📜

this module is released under the same terms as Perl itself. iconic. periodt. 💅

and that's the whole page, bestie!! this was DBIx::RunSQL(3) from perl v5.42.2, dated 2026-07-20 — run your SQL, stay hydrated, and don't forget the semicolons 💖✨🎀

📦 where this page comes from

fedora 44: perl-DBIx-RunSQL 0.28-1.fc44 · GPL-1.0-or-later OR Artistic-1.0-Perl
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-10-14: a language model's rewrite of the original, under its terms