Now I have an SQL machine gun
Where do you keep your SQL?
Do you use a database with Perl? I don't mean one of those new-fangled No-SQL ones; I mean one of those SQL classics. You do? Where do you keep your SQL?
You've got an ORM, check! You've got abstractions to hide your queries, check! But do you have any SQL lying around in files? Maybe your schema? Maybe you've got several different schemas? Different versions? Maybe ones for testing? Maybe for different database engines? (Thank you SQL::Translator!)
How do you run your SQL?
Do you use the command line client for each database? Do you remember the syntax for each one? If you run SQL from automated tests, do you always use the same type of database? Do you use the same connection parameters? Or do you mix it up?
If you're the type of person who like to keep things DRY ("don't repeat yourself"), then doing things one way for SQL code with command line tools and then another way in your Perl code probably drives you crazy.
Why not just use DBI, instead?
DBIx::RunSQL is your SQL machine gun. It takes an SQL file, chops it up into statements, and fires them at your database through good, old DBI. You probably don't want to do that for a database dump with a million INSERT statements, but for schemas, it's great!
Here's an example adapted from the synopsis to initialize a new database for testing. First, we drop our schema into sql/create.sql:
...and probably in reality we've got quite a few more tables to create. The we read in the SQL and fire it off:
You can also reuse an existing DBI handle:
There you go. Now you can keep your SQL files, but execute them from inside your Perl code with the connection you already have. Ho-Ho-Ho!