Creating Databases on-the-fly with DBD::SQLite

At my current job the main “API” between developers and sales people are CSV sheets. In the past we imported these CSVs into an SQLite database, commited everything to Git, and pushed it to production.

Now, in a new service, we just commit the CSVs and build the DB on-the-fly at startup:

use warnings;
use strict;
use DBI;
my $csv = "blz-aktuell-csv-data.csv";
open( my $CSV, '<', $csv ) or die "$csv: $!\n";
my @titles = split /;/, <$CSV>;
my $dbh = DBI->connect( "dbi:SQLite::memory:", "", "", {} );
my $stmt =
  $dbh->prepare( 'CREATE TABLE csv ('
      . join( ",", ( map { "\"$_\" TEXT" } @titles ) )
      . ')' );
$stmt->execute();
$stmt = $dbh->prepare(
    'INSERT INTO csv VALUES (' . join( ', ', map { '?' } @titles ) . ')' );
while (<$CSV>) {
    my @fields = map { s/^"(.*)"$/$1/r } ( split /;/ );
    $stmt->execute(@fields);
}
close $CSV;