Enabling SQLite WAL journaling in Rails

In 2014, Pillai et al. wrote a paper presented at OSDI that explains just how hard the problem of data consistency is for filesystem and database authors. These challenges were addressed in SQLite 3.7 with the introduction of a write-ahead logging journaling mode.

WAL journaling can be enabled with the following SQL statement:

PRAGMA journal_mode = WAL;

If you use Rails, you can also enable this by creating adding initializer file (e.g., config/initializers/sqlite.rb) with the following:

ActiveRecord::Base.connection.exec_query("PRAGMA journal_mode = WAL")

You will also need to update your .gitignore file because SQLite uses different files for different journaling modes:

diff --git a/.gitignore b/.gitignore
index ec0b196..9ff99a7 100644
--- a/.gitignore
+++ b/.gitignore
@@ -9,7 +9,8 @@
 
 # Ignore the default SQLite database.
 /db/*.sqlite3
-/db/*.sqlite3-journal
+/db/*.sqlite3-shm
+/db/*.sqlite3-wal
 
 # Ignore all logfiles and tempfiles.
 /log/*

This work is licensed under a Creative Commons License.