Part 8: 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. The same code runs on Postgres later, once we're in containers (Part 10: 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 a database. Use SQLite and SQLAlchemy

Use an environment variable to configure which DB the server should connect to

Make it database-agnostic - later we will add support for other databases (e.g. postgres)

The assistant picks the database from an environment variable. In our run it named it SNAKE_ROYALE_DATABASE_URL, and we use that name from here on. It defaults to a SQLite file at backend/snake_royale.db, so the backend already persists without any extra configuration.

Point local development at SQLite

To set the database explicitly for local development, create backend/.env:

SNAKE_ROYALE_DATABASE_URL=sqlite:///./snake_royale.db

Start the backend again and the tables are created in snake_royale.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 database

They should exercise full flows:

- sign up
- log in
- submit a score
- read it back from the leaderboard

Now we can run the tests:

cd backend
uv run pytest
uv run pytest tests_integration/

We add a matching target for the integration suite:

backend-integration-tests:
    cd backend && uv run pytest tests_integration

Now make test runs the fast unit tests and make backend-integration-tests runs the slower ones.

We keep them separate for our CI/CD pipeline later. The pipeline runs the fast unit tests first, and the slower integration tests only after those pass.

Commit the database work:

git add .
git commit -m "Add SQLite persistence"

With real persistence in place, we package the app into containers in Part 9: Package as one container.