Testing Strategies — In-Memory and Fixtures

~12 min · testing, fixtures, production

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

SQLite is the testing-database superpower

SQLite makes test setup almost free. Three strategies that show up in every well-tested codebase:

:memory: per test — open sqlite3.connect(':memory:'), run migrations, run the test, the database disappears at process end. Fastest possible setup.
tempfile per test — like :memory: but file-backed. Useful when the code under test must close + reopen the connection.
shared template + COPY — build a fixture database once, copy it for each test. Fast when migrations are expensive.

Self-reference: Pippa's tests use the :memory: pattern via a pytest fixture — every store-level test gets a fresh empty database in milliseconds. That's why the backend test suite finishes in ~2 seconds even with 100+ tests.

Code

pytest fixture — fresh in-memory store per test·python

import pytest, asyncio, aiosqlite

@pytest.fixture
async def store(tmp_path):
    # tempfile so we exercise file locking too
    path = tmp_path / 'test.db'
    s = await ConversationStore.open(str(path))
    yield s
    await s.conn.close()

async def test_create_and_list(store):
    cid = await store.create_conversation('Hello')
    convs = await store.list_conversations()
    assert len(convs) == 1
    assert convs[0]['id'] == cid

Pure :memory: — fastest, no file I/O at all·python

import sqlite3

def test_pure_in_memory():
    conn = sqlite3.connect(':memory:')
    conn.execute('CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)')
    conn.execute('INSERT INTO t(v) VALUES (?)', ('hello',))
    assert conn.execute('SELECT v FROM t').fetchone() == ('hello',)

External links

Exercise

Take any sqlite3-using Python project (yours or Pippa's) and add a pytest fixture that gives each test a fresh database. Confirm the test suite runs without leaking state between tests. Then add a fixture variant that uses tempfile + WAL — verify both behave identically for normal CRUD tests.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousSQLite Limits Next →Production Migrations — Zero-Downtime Patterns

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.