How to save in-memory sqlite database to a file in perl?

Question

I create an SQLite database in memory using:

my $dsn      = "dbi:SQLite:dbname=:memory:"; # better in read/write operations performance than disk-file saved database.
my $user     = "";
my $password = "";
my $dbh = DBI->connect($dsn, $user, $password,{});

#… Doing some processing on the database (Creating tables/Inserting rows/Updating fields)

#… After finishing, I need to save the database to a local disk file.

What I need to do is after finishing playing with the in-memory database, I need to save it to disk file file.db.

---

Updated (Answer Summarised):
• Helpful Commands: Thanks to Schwern for his answer and comment.

• $dbh->sqlite_backup_to_file( $file_path ) Copies database from memory to a file.
• $dbh->sqlite_backup_from_file( $file_path ) Copies database to memory from a file.
• my $dbh = DBI->connect($dsn, $user, $password,{AutoCommit => 0}) Disabling AutoCommit seems to be a better and simpler option to optimize performance like using the previous two commands. I just need to make sure that when turning off AutoCommit, SQLite SELECT operations doesn't do any disk activity (other question).
  • Update: Performance testing by Schwern (mentioned here) shows that operating and querying on whether an in-memory or in-disk database results the same performance.

Answer 1

Yes, you can use $dbh->sqlite_backup_to_file( $filename ) and then connect to that file like a normal SQLite database. For more see the SQLite Backup API docs.

But you can accomplish basically the same thing with the same performance by turning off AutoCommit and only committing your transaction when you're done with your bulk inserts. SQLite will probably hold all your inserts in memory until they're committed.

my $dbh = DBI->connect(
    "dbi:SQLite:dbname=test.sqlite", undef, undef, { RaiseError => 1, AutoCommit => 0 }
);

...do your inserts...

$dbh->commit;

A simple benchmark shows this is just as fast and it's more flexible.

Turning off AutoCommit will give you a big boost with either option.