Part 7: Real persistence with SQLite

The app works end to end now, but the backend keeps everything in memory, so every restart wipes the accounts and scores. In this part we give it a real database with SQLAlchemy. We use SQLite, because it's a single file with no server to install - the lowest-friction way to get real persistence on a laptop. The same code runs on Postgres later, once we're in containers (Part 9: Postgres in a container).

Add SQLAlchemy persistence

Ask the assistant to replace the in-memory store with SQLAlchemy models and a database session. Have it choose the database from one environment variable so SQLite and Postgres both work without code changes.

Replace the in-memory store with SQLAlchemy. Add ORM models for users,
leaderboard entries, and active games, and a database module that creates the
engine and sessions. Pick the database from a DATABASE_URL environment
variable, defaulting to a local SQLite file, and make sure the same code works
with Postgres too. Keep the existing tests passing. Follow AGENTS.md.

Two small pieces make the SQLite-or-Postgres design work. The first lives in app/config.py, which converts the postgres:// URLs some hosts hand out into the postgresql:// form SQLAlchemy needs.

The conversion looks like this:

@property
def database_url(self) -> str:
    url = self._raw_database_url
    if url.startswith("postgres://"):
        url = url.replace("postgres://", "postgresql://", 1)
    return url

The second lives in app/database.py, where SQLite needs one extra flag that Postgres doesn't, so the engine sets it only for SQLite URLs.

The engine sets it like this:

connect_args = {}
if settings.database_url.startswith("sqlite"):
    connect_args = {"check_same_thread": False}

engine = create_engine(
    settings.database_url,
    connect_args=connect_args,
    pool_pre_ping=True,
)

That check_same_thread flag lets the same SQLite connection be used across FastAPI's threads. It's the one SQLite-specific concession in the whole backend. Everything else is the same on both databases.

pool_pre_ping=True tests each pooled connection before use and quietly replaces any that died. Without it, the first request after the database restarts hits a stale connection and fails. It matters once we move to Postgres in Part 9: Postgres in a container, where restarting the database container is something we actually do.

Point local development at SQLite

For local development, tell the backend to use a SQLite file.

Create backend/.env:

DATABASE_URL=sqlite:///./snake.db

Start the backend again and the tables are created in snake.db on first run. Sign up, submit a score, restart the server - the data is still there. The file is local state, so add it to .gitignore.

Add integration tests

The existing tests are unit tests: fast, isolated, each running against a fresh in-memory SQLite database. They prove a single request behaves. They don't prove the app still works when data has to survive across separate database connections - exactly what broke when the store was in memory.

So we add a second suite that uses a real SQLite file on disk:

Add integration tests in a tests_integration/ folder that run against a
temporary SQLite file on disk, not in-memory. They should exercise full flows:
sign up, then log in, then submit a score, then read it back from the
leaderboard.

The integration fixture creates a temporary database file and swaps it in for the app's real engine for the duration of the test:

db_fd, db_path = tempfile.mkstemp(suffix=".db")
engine = create_engine(f"sqlite:///{db_path}",
                       connect_args={"check_same_thread": False})
Base.metadata.create_all(bind=engine)
database.engine = engine
database.SessionLocal = sessionmaker(bind=engine)

A file-based database, unlike an in-memory one, keeps its data when a new connection opens, so these tests catch persistence bugs the unit tests can't.

Run both suites by hand first:

cd backend
uv run pytest                      # unit tests
uv run pytest tests_integration/   # integration tests

make test from Part 6: A Makefile for the project already runs the unit suite.

We add a matching target for the integration suite:

test-integration:
    cd backend && uv run pytest tests_integration/

Now make test runs the fast unit tests and make test-integration runs the slower ones. Keeping them as separate targets matters for Part 12: CI/CD with GitHub Actions: the pipeline runs the fast unit tests first and the slower integration tests only after those pass. With real persistence in place, we package the app into containers in Part 8: Package as one container.