read-only sqlite database with temporary changes

Question

I have an sqlite database, and I would like to keep it read-only without any write operations on the database file.

Is there a way to make temporary modifications to the database, without flushing them to disk permanently?

Right now I am doing this in a workaround way, by storing the temp data in an in-memory database with the same schema structure as the main database file. The problem with this approach is that it increases code complexity, as I have to run all my queries on both databases.

Ideally, the solution to the problem would treat the additional temporary data as if it were part of the main database but without actually committing it to disk.

Answer 1

Because SQLite is transactional it should be enough to not to COMMIT transaction (SQLite will rollback it automatically on connection close). You should set autocommit to off using BEGIN statement first.

Answer 2

You could create a temporary table, which will be automatically removed from the db when the connection is closed.

Answer 3

BEGIN IMMEDIATE TRANSACTION;
do a bunch of stuff;
ROLLBACK TRANSACTION;

If another thread is reading from the database then the ROLLBACK will fail with SQLITE_BUSY, but you can execute it again after the pending reads finish. If another thread wants to write to the database then they will hit your lock.

Now, there is something a bit funny about using transactions in this way, how about having a programmatic ORM-style layer between you and the database that works directly with your native objects and keeps your in-memory temporary changes?

I mean, if you don't want to change the database, perhaps you need another code layer, not a database feature?