There are many cases where we have some data we would like to interrogate where using SQL would make it easier to fetch data. However we would like to make things fast. Having all the data in memory is always much faster than having it on disk. Even with modern SSD-based disks.

SQLite allows us to create a database entirely in memory.

Of course this means the amount of data we can hold is limited to the available free memory and the data, in this format, will be lost once the process ends.

Nevertheless it can be very useful as temporary data storage instead of using hashes, arrays.

In order to create an in-memory SQLite database we need to connect to the database using :memory: pseudo-name instead of a filename.

examples/sqlite_in_memory.pl

use strict;
use warnings;
use Data::Dumper qw(Dumper);
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:");
$dbh->do("CREATE TABLE words (
   id     INTEGER PRIMARY KEY,
   word   VARCHAR(255)
)");

for my $word ("abc", "def") {
    $dbh->do("INSERT INTO words (word) VALUES (?)", undef, $word);
}

my $sth = $dbh->prepare("SELECT * from words");
$sth->execute;
while (my $h = $sth->fetchrow_hashref) {
    print Dumper $h;
}

Once we have the database handle ($dbh) we can do all the usual operations. We will usually start by creating tables and indexes. Then we can INSERT rows.

Except of the connection string our code is not aware that it is talking to an in-memory database.

See also the counter example using Perl Dancer and an in-memory SQLite database