Are there SQLite commands that can output a select query as a SQL insert commands?

Question

I am using Zeoslib to create in-memory databases with SQLite, and I need to the save the database onto disk when the program closes or dump it to a file regularly.

As the SQLite3 program cannot handle in memory databases, is there a way to get SQLite to export the output of a SELECT query as INSERT statements?

PS. sqlite3 can dump an in memory database to disk as indicated in this thread, but the in-memory database is created by a different application, which is why I can't see how to get the example to work.

Answer 1

To save a memory DB to disk, create a copy with SQLite's backup API. This requires that your database driver actually supports the backup API; Zeos apparently does not.

---

What makes a database on disk so slow are not the disk accesses themselves (most could be cached), but the synchronizations to ensure that the DB stays intact even if the program or the computer crash.

There are a number of settings that increase speed, at the cost of making an on-disk DB as unsafe as an in-memory DB:

• Set PRAGMA synchronous to OFF:

  With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, commits can be orders of magnitude faster with synchronous OFF.

• Set PRAGMA journal_mode to MEMORY:

  The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.

• Increase PRAGMA cache_size (the default is typically only 2 MB);

• Disable PRAGMA secure_delete.