Using in-memory SQLite for testing

Saturday, August 05, 2023

SQLite is a small, fast, fully-featured embedded SQL database engine. It’s available in many platforms and is provided as a library that programs can link to. (There’s bindings available for various languages.) The database is (generally) a single file and portable across platforms.

With these properties, it’s one of the go-to databases when one does not require concurrent access by many users. Some that claim that SQLite should be good enough for a majority of use cases. Since it works similar to other relational databases that use SQL, it can work in place of them in many cases.

That said, there’s one place it can be especially useful in – testing. And the killer feature of SQLite that doesn’t get mentioned much, but super helpful in this context is in-memory mode.

In-memory database mode

SQLite can operate in in-memory mode, which allows it to run super-fast, and that’s what the official website mostly talks about. However, there’s another property about in-memory mode, its ephemeral nature makes it very helpful for testing.

One of the important aspects of reliable tests is to have a consistent, known initial state. This is crucial in having reproducibility of tests which is essential to have meaningful results.

When a test involves a databases or anything that has state (and especially a persistent one!), we need to ensure that it’s in the state we want it. This can be particularly tricky for databases. But for a database that doesn’t have persistence, that’s trivial – just start with a clean, empty database on each execution! That’s what the in-memory mode of SQLite provides. 😉

The best part is that with SQLite it’s trivial! When opening a SQLite database, rather than specifying a filename, setting the filename to :memory: will trigger the in-memory database mode. When using the C interface, something as simple as this:

sqlite3_open(":memory:", &db);

From there, just interact with it as usual. When we disconnect from the database, it will disappear into thin air.

SQLite in-memory mode in action

Here’s an example illustrating a “unit test”¹ of a part of code that’s accessing the database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#[test]
fn has_dupes() {
    // given
    let connection = Connection::open(":memory:").unwrap();
    let database = Database::new(&connection);
    database.init_for("/path/to", 1000, false).unwrap();

    let entry1 = &Entry::new_simple(
        "to/file1", "/path/to/file1", "file1", "/path/to", "00deadbeef", 100, 100, 100
    );
    let entry2 = &Entry::new_simple(
        "to/file2", "/path/to/file2", "file2", "/path/to", "00deadbeef", 100, 100, 100
    );
    let entry3 = &Entry::new_simple(
        "to/file3", "/path/to/file3", "file3", "/path/to", "00cafecafe", 100, 100, 100
    );
    database.add_entry(entry1);
    database.add_entry(entry2);
    database.add_entry(entry3);

    // when
    let dupe_files = database.find_dupes().unwrap();

    // then
    assert_eq!(2, dupe_files.len());
    assert_eq!(entry1.path, dupe_files.get(0).unwrap().path);
    assert_eq!(entry2.path, dupe_files.get(1).unwrap().path);
}

This is a test case from the backup comparison tool I wrote about last year written in Rust.

In this tool, each file in a set of backup files is represented as an Entry that gets written to a table in the SQLite database, using Database::add_entry.

This test case is verifying whether Database::find_dupes is returning a list of duplicate entries. (Entrys that have the same hash, in the 5th parameter, is considered to be the same.)

To facilitate this test case, we create an in-memory mode database by setting ":memory:" as the filename as seen on line 4. At this point, obviously, the database is empty, so it’s a well-known initial state. From there, the database is set up in Database::init_for, then proceeds to adding entries for the test case, and then executes the function under test, Database::find_dupes.

Under the hood, the Database::find_dupes method is using an SQL statement to find the duplicates. Therefore, this methodology can be used to test SQL statements. In fact, in this example, I wrote the test first and used the test case to correct my initially misbehaving SQL statement.

From here, when adding test cases with this pattern, and each test case will be isolated from each other, as the SQLite database is initialized each time. No state leakage between tests, so tests are reproducible and reliable. No need to do any teardown because the database disappears at end of a test. Yet, we get a fully-functional database on the next test case with trivial set up.

Isn’t this just so convenient? 😊

Yes, it’s kind of weird to call this a unit test when it’s closer to an integration test. But again, SQLite is just a library, so one could make the argument that it can be considered one. 😉 ↩

coobird.net

Using in-memory SQLite for testing

In-memory database mode

SQLite in-memory mode in action