What is the "-journal" SQLite database?

Question

In my Android application's database directory (/data/data/com.me.myApp/databases), for every SQLite database I create, there is a corresponding database of the same name with -journal appended to it's name.

E.g: myDatabase, myDatabase-journal, myOtherDatabase.db, myOtherDatabase.db-journal.

What is this?

If I'm providing pre-filled databases for my app (as per: this tutorial), do I need to include these as well?

Kelp

Answer 1

Such -journal files do not need to (and should not) be distributed.

This is because the various journal files represent temporary data (ref. SQLite's Use Of Temporary Disk Files) used by SQLite. In particular a -journal file is a rollback journal.

A rollback journal is a temporary file used to implement atomic commit and rollback capabilities in SQLite. (For a detailed discussion of how this works, see the separate document titled Atomic Commit In SQLite.) The rollback journal is always located in the same directory as the database file and has the same name as the database file except with the 8 characters "-journal" appended.

The rollback journal is usually created when a transaction is first started and is usually deleted when a transaction commits or rolls back. The rollback journal file is essential for implementing the atomic commit and rollback capabilities of SQLite. Without a rollback journal, SQLite would be unable to rollback an incomplete transaction, and if a crash or power loss occurred in the middle of a transaction the entire database would likely go corrupt without a rollback journal.

In general these -journal files should only exist when there is an open SQLite database - or rather, a running transaction - but can be controlled via PRAGMA journal_mode. With default pragma settings the -journal files will be deleted.

The DELETE journaling mode is the normal behavior. In the DELETE mode, the rollback journal is deleted at the conclusion of each transaction. Indeed, the delete operation is the action that causes the transaction to commit.

Make sure to only copy the actual database files when the database is not opened and all journals have been deleted (or cleared) by SQLite itself; this implies all transactions have been completed and the database is in a consistent state.

Finalist

What if the -journal file is consistently present and non-trivial size (e.g. 30kB), yet one's app is not performing any activity to the database, and doesn't leave databases open? – Cervix

@Cervix That is a good question - but I'm afraid it will be buried in the comment, as I don't have an immediate expert knowledge answer. Consider creating another SO question, linking to this (and other) questions/answers to present a good foundation. – Finalist

@Cervix I'm sorry, I'm afraid I arrived maybe too late here, but I've got some interesting info here. It says that when a transaction is committed, the header is usually zeroed. At every commit, the header is written, then the pages are written, overwriting the original content, then only the header is zeroed when success. A general solution to 'clean' (e.g. remove) the file, if another commit doesn't do it, could be to execute VACUUM in the DB. – Stultz

The link in a prior comment by user stackoverflow.com/users/6843237/lutarisco is a dead link. But I found it in the wayback machine and has interesting forensic sqlite commentary: web.archive.org/web/20190209094656/http://… – Piperidine