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.

Could you start with an on-disk database, work in a transaction, and only commit at the end of the program? What are you trying to accomplish? — Schwern
– Schwern, Commented Nov 14, 2018 at 16:01
No, I want to start it in memory, then save it to a disk file. If I was to start with a saved to disk database scenario, I'll load an already existing in-disk DB, then create a duplicate copy of it to a separate new empty in-memory DB (I don't know a simple way of how to do this). Then do all the sql processing on the in-memory DB (to avoid the lag caused by disk read/write if processing on the in-disk DB directly). At end, copy back the updated version (the in-memory DB) to the in-disk DB. — Omar
– Omar, Commented Nov 14, 2018 at 16:12
If it's just for optimization purposes, try using a normal on-disk database, turning off AutoCommit, and only committing your transaction at the end (or every few thousand inserts). I ran some tests and it's just as fast as long as AutoCommit is off which it generally should be. And you can load a database into an existing in memory database with sqlite_backup_from_file — Schwern
– Schwern, Commented Nov 14, 2018 at 16:22

Schwern · Accepted Answer · 2018-11-14 16:57:14Z

7

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.

edited Nov 14, 2018 at 16:57

answered Nov 14, 2018 at 16:11

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Omar Over a year ago

When querying data (using SELECT SQL statement), does SQLite read data from disk file or memory? If SQLite reads data from memory then turning off AutoCommit will be exactly the same and a simpler option. If it reads data from disk, then disabling AutoCommit will still have a disk-read lag.

Schwern Over a year ago

@Omar I can't say for sure, but SQLite is very mature and should do its own memory caching. These questions can be answered with performance testing. I did my own and, again, there seems to be no difference.

Schwern Over a year ago

@Omar Temporary databases, for example... "Even though a disk file is allocated for each temporary database, in practice the temporary database usually resides in the in-memory pager cache and hence is very little difference between a pure in-memory database created by ":memory:" and a temporary database created by an empty filename. The sole difference is that a ":memory:" database must remain in memory at all times whereas parts of a temporary database might be flushed to disk if database becomes large or if SQLite comes under memory pressure."

Omar Over a year ago

Thanks a lot for your effort. Your test give a very good indication. They're seemingly the same in performance.

TonySalimi Over a year ago

@Schwern I have the same problem but with a different use-case. In my case, I have a GUI, and the user may do lots of changes to the db from the GUI. The changes will be affected if and only if the user presses the save button. If I disable auto-commit, it means that a transaction will be open for some minutes/hours. Is it still a good idea to disable auto-commit in this case?

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related