SQLite setup and usage — practical guide for CS50 Web Apps

"You designed the schema with perfect normalization, your routes are clean, templates render like poetry — now your app needs a place to put all that glorious data. Enter SQLite: the tiny, reliable filing cabinet that lives in a file and quietly does its job."

---

This guide picks up where Relational modeling basics and Normalization and denormalization left off and builds that design into a working database for your Flask app. You already know how to route requests and render Jinja templates — now let's connect those routes to persistent data using SQLite. We'll cover setup, best practices, and common pitfalls for CS50-style web projects.

Why SQLite for CS50 projects?

• Lightweight: no server process — just a file (good for development and small apps).
• Easy to inspect with the sqlite3 CLI or GUI tools.
• Fast enough for many hobby and prototype apps.

But also: limited concurrency and fewer advanced features than Postgres. For class projects, SQLite is usually ideal.

---

Quick roadmap (what you'll learn)

1. How to configure SQLite in a Flask app (connection per request pattern)
2. How to initialize the DB from a schema file
3. Safe querying (parameterized queries, row factories)
4. Enforcing foreign keys and other SQLite gotchas
5. When to use raw SQL vs an ORM (quick comparison)

---

1) Flask pattern: one DB connection per request

The idiomatic Flask pattern uses the application context and g to store a connection. This keeps code clean and lets you close connections automatically.

# db.py
import sqlite3
from flask import g, current_app

def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect(
            current_app.config['DATABASE'],
            detect_types=sqlite3.PARSE_DECLTYPES
        )
        g.db.row_factory = sqlite3.Row  # rows behave like dicts
        g.db.execute('PRAGMA foreign_keys = ON')
    return g.db

def close_db(e=None):
    db = g.pop('db', None)
    if db is not None:
        db.close()

Then register with your app factory:

def create_app(config=None):
    app = Flask(__name__)
    app.config.from_mapping(DATABASE='instance/flaskr.sqlite')

    from . import db
    app.teardown_appcontext(db.close_db)

    return app

Micro explanation: Using g ensures each request reuses the same connection while it's processing, and closing it on teardown prevents file descriptor leaks.

---

2) Initialize the DB from a schema file

Put your DDL in a plain text schema.sql.

-- schema.sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

A helper to run it:

import click
from flask import current_app
from .db import get_db

def init_db():
    db = get_db()
    with current_app.open_resource('schema.sql') as f:
        db.executescript(f.read().decode('utf8'))

@click.command('init-db')
def init_db_command():
    init_db()
    click.echo('Initialized the database.')

def init_app(app):
    app.cli.add_command(init_db_command)

Then: flask init-db to create the file and schema.

---

3) Safe querying and row handling

Always use parameterized queries (placeholders) to avoid SQL injection. Never do string interpolation with user data.

# insecure: BAD
db.execute(f"SELECT * FROM users WHERE username = '{username}'")

# secure: GOOD
db.execute('SELECT * FROM users WHERE username = ?', (username,))

Because we set row_factory = sqlite3.Row, fetched rows behave like dicts and can be passed to templates easily:

user = db.execute('SELECT * FROM users WHERE id = ?', (user_id,)).fetchone()
# in a view
return render_template('profile.html', user=user)

In Jinja: {{ user['username'] }} or {{ user.username }} depending on how you pass it.

---

4) SQLite gotchas (aka the things that will make you shriek)

• Foreign keys are NOT enforced by default. Use PRAGMA foreign_keys = ON (see get_db above).
• Concurrency: SQLite locks the database file for writes. It's fine for light loads; don't expect high concurrency.
• Typing is flexible — SQLite is dynamically typed. Use CHECK constraints if you need stricter validation.
• Migrations: SQLite doesn't handle migrations like Postgres. For production, consider Alembic/Flask-Migrate if you outgrow your simple schema.

Quick debugging tips:

• Inspect DB: sqlite3 instance/flaskr.sqlite then .tables and SQL queries.
• Dump schema: sqlite3 instance/flaskr.sqlite .schema

---

5) When to use raw SQL vs ORM

Short answer: use raw SQL for clarity and learning; use an ORM for larger apps with many relationships.

Pros of raw SQL:

• You see exactly what's executed (good for CS50 learning).
• No magic: performance and queries are explicit.

Pros of ORM (e.g., SQLAlchemy):

• Faster to prototype objects and relationships.
• Migrations support and database-agnostic code.

If you use SQLAlchemy, the SQLite URL for an in-memory DB is sqlite:///:memory: for tests. For file DBs, sqlite:////absolute/path/to/file.db.

---

Example: a simple route that lists posts

from flask import Blueprint, render_template
from .db import get_db

bp = Blueprint('blog', __name__)

@bp.route('/')
def index():
    db = get_db()
    posts = db.execute(
        'SELECT p.id, p.title, p.body, u.username'
        ' FROM posts p JOIN users u ON p.user_id = u.id'
        ' ORDER BY p.id DESC'
    ).fetchall()
    return render_template('index.html', posts=posts)

This ties together: your relational model (joins), Flask route, and templates.

---

Key takeaways

• Use the Flask app context (g) for a request-scoped SQLite connection.
• Initialize the DB from a schema.sql and provide a flask init-db command.
• Always use parameterized queries and enable PRAGMA foreign_keys = ON.
• SQLite is great for CS50 projects — simple, fast to iterate — but watch concurrency and migrations.

"If your schema is your app's mind, then SQLite is the notebook you keep in your jacket. Keep it tidy, don't write secrets in plain text, and back it up."

---

If you want, I can:

• Produce a minimal Flask project with files wired up to this pattern, or
• Show how to migrate this pattern to SQLAlchemy models, or
• Give a checklist of common SQLite errors and fixes.

Which one should we do next? 😈