SQLite3 and Database Basics

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Flask 3.1.3 (released 2026-02-18) and Werkzeug 3.1.9, with the sqlite3 module of Python 3.13 (SQLite library 3.53.1 in the test environment), checked on 2026-10-07. Flask 3.2 was not released on that date, so what is said about it is marked as upcoming. Every Python block was executed with app.test_client() and app.test_cli_runner() on Python 3.13; nothing needs a database server, because SQLite is a library that stores the whole database in one file. The running scenario is the Bookshelf catalogue of the other pages of this section: authors and books in a small relational database that the application reads and writes with plain SQL.

Why Start With the Standard Library

Flask has no database layer, on purpose. The official tutorial (Flaskr) and the "Patterns for Flask" chapter both start with the sqlite3 module that ships with Python, for three reasons: there is nothing to install or to run, the whole database is one file that can be copied, and the code teaches the two ideas that every Flask database extension is built on. The first is one connection per context: open it on first use, keep it on flask.g, close it when the application context ends. The second is binding: the SQL text and the values travel separately.

SQLite is a good production choice for read-heavy sites, internal tools and small services, and a poor one when many processes write at the same time, because each write takes a lock on the whole database file. The official tutorial says it plainly: concurrent writers slow down as each write happens sequentially, and a growing application may want another database. The SQLite project’s own guidance on when to use it is linked below. The SQL itself (types, joins, constraints, transactions) is covered on the SQL pages of this site and is not repeated here.

Need Plain sqlite3 Move to an ORM or another database

Prototype, script, internal tool, tests

Ideal: zero setup, file or memory database

Not needed

A handful of tables and hand-written queries

Fine; the queries stay visible and fast

Optional

Many models, relationships, migrations

The SQL and the Python classes drift apart

Flask-SQLAlchemy and Alembic (the Flask-SQLAlchemy and Database Migrations pages of this section)

Several app servers or containers writing to the same data

The file is local to one host; writers queue on a lock

A server database such as PostgreSQL (psycopg, SQLAlchemy)

Autoscaled, stateless containers

The file disappears with the container unless a volume is mounted

A managed database

Source: SQLite: Appropriate Uses For SQLite

One Connection Per Context

A database connection is not a global. A module-level sqlite3.connect() is created at import time, is shared by every thread of the server, never closed, and by default raises ProgrammingError when a second thread touches it. The Flask answer is to tie the connection to the application context, the object that lives for the duration of a request (or a CLI command, or a with app.app_context(): block) and that carries g, the namespace for "data that might be accessed by multiple functions during the request". The details of contexts and of the order of the hooks are on the Application and Request Lifecycle and Hooks and View Decorators pages; what matters here is the three-step recipe:

flowchart LR A[Context pushed] --> B[View calls get_db] B -->|first call| C[connect and store on g] B -->|later calls| D[reuse g.db] C --> E[Queries] D --> E E --> F[Context popped] F --> G[close_db on teardown_appcontext]
Figure 1. The life of a connection inside one application context

The first call to get_db() connects, later calls in the same context reuse the connection, and the teardown function closes it if it exists. Teardown functions run even when a before-request hook short-circuited or the view raised, and even when get_db() was never called, so the teardown must check before it closes. This is the original pattern from the Flask documentation, in a single file:

import sqlite3
import tempfile
from pathlib import Path

from flask import Flask, g

WORK = Path(tempfile.mkdtemp(prefix="bookshelf-sqlite-"))
DATABASE = WORK / "pattern.db"

app = Flask(__name__)


def get_db():
    db = getattr(g, "_database", None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
    return db


@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, "_database", None)
    if db is not None:
        db.close()


@app.route("/version")
def version():
    return get_db().execute("select sqlite_version()").fetchone()[0]


client = app.test_client()
print(client.get("/version").status_code)

# outside a request the application context has to be pushed by hand
with app.app_context():
    print(get_db().execute("select 1").fetchone())

Source: Flask: Using SQLite 3 with Flask

Three details are worth knowing. The route returns a plain str, which Flask turns into a text/html response. The teardown_appcontext function receives the exception that ended the context, or None; closing a connection does not need it. And g belongs to the application context, not to the request, which is why the same code works in a CLI command and in a script that pushes app.app_context().

A tidier version of the same idea, and the one used for the rest of the page, is the one from the Flaskr tutorial. It has four changes: g.pop("db", None) replaces the getattr dance, the connection is configured once in get_db(), the database path comes from current_app.config (the code does not import the application, so it works with the application factory), and registering the teardown and the command is done by an init_app(app) function that the factory calls.

The Bookshelf Layout

The tutorial’s layout puts the database code in its own module next to the SQL file that creates the tables:

bookshelf/
├── __init__.py        # create_app()
├── db.py              # get_db, close_db, init_db, init-db command, init_app
├── schema.sql         # CREATE TABLE statements, read by init_db
├── catalogue.py       # a blueprint with the views
└── ...
instance/
└── bookshelf.sqlite   # created by "flask --app bookshelf init-db"; never committed

schema.sql is package data: it travels with the code, is opened with current_app.open_resource() (which resolves the path relative to the application’s root folder, wherever the package is installed) and is the single place that describes the tables. The database file is data: it lives in the instance folder (app.instance_path), outside the package and outside version control. Add instance/ to .gitignore.

import os
import sqlite3
from datetime import datetime

import click
from flask import Flask, current_app, g
from flask.cli import with_appcontext

# --- bookshelf/schema.sql (shown as a string so that this example is self-contained) ---
SCHEMA = """
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS author;

CREATE TABLE author (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE book (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    author_id INTEGER NOT NULL REFERENCES author (id),
    title     TEXT NOT NULL,
    isbn      TEXT UNIQUE,
    rating    INTEGER CHECK (rating BETWEEN 1 AND 5),
    created   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX book_author_idx ON book (author_id);
"""
PKG = WORK / "bookshelf"
PKG.mkdir()
(PKG / "schema.sql").write_text(SCHEMA)

# --- bookshelf/db.py ---
def get_db() -> sqlite3.Connection:
    if "db" not in g:
        g.db = sqlite3.connect(
            current_app.config["DATABASE"],
            detect_types=sqlite3.PARSE_DECLTYPES,
        )
        g.db.row_factory = sqlite3.Row
        g.db.execute("PRAGMA foreign_keys = ON")
    return g.db


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


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


@click.command("init-db")
@with_appcontext
def init_db_command() -> None:
    """Clear the existing data and create new tables."""
    init_db()
    click.echo("Initialized the database.")


# SQLite stores TIMESTAMP as text; this converter returns datetime objects.
sqlite3.register_converter("timestamp", lambda v: datetime.fromisoformat(v.decode()))


def init_app(app: Flask) -> None:
    app.teardown_appcontext(close_db)
    app.cli.add_command(init_db_command)

Source: Flask Tutorial: Define and Access the Database

The pieces, one by one:

  • "db" not in g and g.pop("db", None): g supports in, get, pop and setdefault, so no getattr is needed. Because the teardown can run when no connection was opened, pop with a default is the safe form.

  • current_app.config["DATABASE"]: a setting, not a constant, so that tests can point to a temporary file and production can point to a mounted volume. The key name DATABASE is the tutorial’s own; Flask does not define it.

  • detect_types=sqlite3.PARSE_DECLTYPES: the declared column type (TIMESTAMP) selects a converter, so a created value comes back as datetime. SQLite has no real date type; it stores text, numbers or blobs. Python 3.12 deprecated the default adapters and converters of the module, which is why the example registers its own converter instead of relying on the built-in one. Without detect_types the value is just a string, which is often all a JSON API needs.

  • row_factory = sqlite3.Row: rows can be read by index or by column name (case-insensitively), can be iterated and have keys(). Without it a row is a plain tuple. The "Patterns" page calls these rows namedtuple objects; they are in fact a separate C type, and they are not dict: use dict(row) before passing one to jsonify.

  • PRAGMA foreign_keys = ON: SQLite parses REFERENCES clauses but does not enforce them unless this pragma is set on every new connection. It is a pitfall that the Flaskr tutorial does not mention, so it is set here in get_db().

  • open_resource(): a method of the Flask object that opens a file relative to app.root_path. Its default mode is binary, hence .decode("utf8"); pass mode="r" to read text (the Patterns page does).

  • executescript(): runs several statements separated by semicolons. If a transaction is pending it commits it first, and it cannot take parameters, so it is for trusted files such as schema.sql and never for request data.

Wiring It Into the Factory

def create_app(test_config=None) -> Flask:
    app = Flask(__name__, instance_relative_config=True)
    app.config.from_mapping(DATABASE=os.path.join(app.instance_path, "bookshelf.sqlite"))

    if test_config is None:
        app.config.from_pyfile("config.py", silent=True)
    else:
        app.config.update(test_config)

    os.makedirs(app.instance_path, exist_ok=True)

    init_app(app)
    # Only because this page is not a real package: point the root folder at PKG so that
    # open_resource("schema.sql") finds the file written above.
    app.root_path = str(PKG)
    return app


app = create_app({"DATABASE": str(WORK / "bookshelf.sqlite"), "TESTING": True})
runner = app.test_cli_runner()
result = runner.invoke(args=["init-db"])
print(result.output.strip())
check = sqlite3.connect(app.config["DATABASE"])
print(sorted(name for (name,) in check.execute(
    "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'")))
check.close()

Source: Flask Tutorial: Application Setup

The real factory is shorter than this block (the root-path line is an artefact of running a page, not a recipe). instance_relative_config=True makes from_pyfile("config.py") read from the instance folder, where production values are placed; the Configuration page lists every loader. With the app created, the database is initialised from the shell:

flask --app bookshelf init-db
# Initialized the database.

app.test_cli_runner() returns Click’s test runner bound to the app, as the block above shows. The flask script pushes an application context before it runs a command registered on app.cli (since Flask 2.2 @with_appcontext is no longer required for commands created with @app.cli.command(), which wraps them). The test runner, however, invokes app.cli directly, so a command built with click.command() and attached with add_command() has no context there and get_db() would fail with "Working outside of application context". The @with_appcontext decorator (from flask.cli) on the command makes it work in both places, and costs nothing when a context is already active.

The command is destructive: schema.sql starts with DROP TABLE IF EXISTS, so running init-db twice on a live system erases it. That is acceptable for a tutorial and for tests, and dangerous in production. Safer options are to use CREATE TABLE IF NOT EXISTS and a versioned migration (see Schema Versions Without a Tool), or to make the command ask first. Click’s confirmation_option does that; the next block shows it as a variation and executes it:

@click.command("reset-db")
@click.confirmation_option(prompt="This erases every book. Continue?")
@with_appcontext
def reset_db_command() -> None:
    init_db()
    click.echo("Database reset.")


app.cli.add_command(reset_db_command)
print(runner.invoke(args=["reset-db"], input="n\n").exit_code)    # aborted: 1
print(runner.invoke(args=["reset-db", "--yes"]).output.strip())    # confirmed

Source: Click: confirmation_option

Running Queries

The sqlite3 module follows the Python DB-API (PEP 249): a connection creates cursors, a cursor executes SQL, and fetchone() / fetchall() read the results. connection.execute() is a shortcut that creates a cursor and runs the statement on it. Reading is easy; the discipline is in how values get into the SQL.

Bind Values, Never Format Them

Every value that comes from a request (a query-string argument, a JSON field, a path segment, a header) must reach SQL as a bound parameter. The statement text contains a placeholder, the driver sends the value separately, and the database never parses the value as SQL. The sqlite3 module supports two placeholder styles:

Style Example Parameters

qmark (positional)

WHERE isbn = ?

A sequence: a tuple or list, even for one value: (isbn,)

named

WHERE isbn = :isbn

A mapping: a dict, or a sqlite3.Row. Since Python 3.14, using a sequence with named placeholders raises ProgrammingError

The official Flask page puts the rule in one sentence: never add arguments to the SQL statement with string formatting, because it makes the application attackable with SQL injection. The next example runs the same lookup three times: with the vulnerable string formatting (shown on purpose, on an in-memory demo table), then with ?, then with a named placeholder.

demo = sqlite3.connect(":memory:")
demo.execute("CREATE TABLE secret_note (id INTEGER PRIMARY KEY, owner TEXT, body TEXT)")
demo.executemany(
    "INSERT INTO secret_note (owner, body) VALUES (?, ?)",
    [("ana", "ana's private note"), ("ben", "ben's private note")],
)

attacker_input = "x' OR '1'='1"

# WRONG: the input becomes part of the SQL text.
unsafe = demo.execute(f"SELECT body FROM secret_note WHERE owner = '{attacker_input}'").fetchall()
print("string formatting:", len(unsafe), "rows leaked")

# RIGHT: qmark placeholder and a one-element tuple.
safe = demo.execute("SELECT body FROM secret_note WHERE owner = ?", (attacker_input,)).fetchall()
print("qmark:", len(safe), "rows")

# RIGHT: named placeholder and a dict.
safe = demo.execute("SELECT body FROM secret_note WHERE owner = :owner", {"owner": attacker_input}).fetchall()
print("named:", len(safe), "rows")
demo.close()

Source: Python: How to use placeholders to bind values in SQL queries

Two things are not protected by placeholders, because they are not values:

  • Table and column names, and the ASC / DESC keyword. A user-selectable sort order must be checked against an allow-list and then inserted as a constant chosen by the code.

  • The % and _ wildcards of LIKE. A bound parameter cannot inject SQL, but a search term of % still matches everything; escape the wildcards with an ESCAPE clause if that matters.

SORTABLE = {"title": "book.title", "rating": "book.rating DESC", "added": "book.created DESC"}


def list_books(order: str = "title", q: str = ""):
    order_by = SORTABLE.get(order, SORTABLE["title"])      # a constant from the code, never the raw input
    like = "%" + q.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_") + "%"
    sql = (
        "SELECT book.id, book.title, book.isbn, book.rating, book.created, author.name AS author "
        "FROM book JOIN author ON author.id = book.author_id "
        "WHERE book.title LIKE ? ESCAPE '\\' "
        f"ORDER BY {order_by}"
    )
    return get_db().execute(sql, (like,)).fetchall()

Source: OWASP: SQL Injection Prevention Cheat Sheet

The f-string above is safe only because order_by can take one of three constant values. Searching the code for an f-string or % formatting that contains a variable and ends up in execute() is a useful review habit. The same rule holds for every other driver (psycopg, PyMySQL, SQLAlchemy’s text() with bind parameters); only the placeholder syntax changes.

The Views

The next block is the Bookshelf blueprint. It reads with fetchone() and fetchall(), writes with INSERT, uses the cursor’s lastrowid, and turns the typical database failures into HTTP answers: a missing row into 404 and a violated UNIQUE or CHECK constraint into 409.

from flask import Blueprint, abort, jsonify, request, url_for

bp = Blueprint("catalogue", __name__, url_prefix="/books")


def book_to_dict(row: sqlite3.Row) -> dict:
    data = dict(row)
    data["created"] = row["created"].isoformat()
    return data


@bp.get("")
def index():
    rows = list_books(request.args.get("order", "title"), request.args.get("q", ""))
    return jsonify([book_to_dict(r) for r in rows])


@bp.post("")
def create():
    payload = request.get_json(silent=True) or {}
    title, author = payload.get("title"), payload.get("author")
    if not isinstance(title, str) or not isinstance(author, str) or not title or not author:
        abort(400, description="title and author are required strings")

    db = get_db()
    try:
        with db:    # one transaction: commit on success, rollback on exception
            db.execute("INSERT INTO author (name) VALUES (?) ON CONFLICT (name) DO NOTHING", (author,))
            author_id = db.execute("SELECT id FROM author WHERE name = ?", (author,)).fetchone()["id"]
            cur = db.execute(
                "INSERT INTO book (author_id, title, isbn, rating) VALUES (?, ?, ?, ?)",
                (author_id, title, payload.get("isbn"), payload.get("rating")),
            )
    except sqlite3.IntegrityError as exc:
        abort(409, description=f"constraint failed: {exc}")

    return jsonify(book_to_dict(fetch_book(cur.lastrowid))), 201, {
        "Location": url_for("catalogue.detail", book_id=cur.lastrowid)
    }


def fetch_book(book_id: int):
    return get_db().execute(
        "SELECT book.id, book.title, book.isbn, book.rating, book.created, author.name AS author "
        "FROM book JOIN author ON author.id = book.author_id WHERE book.id = ?",
        (book_id,),
    ).fetchone()


@bp.get("/<int:book_id>")
def detail(book_id: int):
    row = fetch_book(book_id)
    if row is None:
        abort(404)
    return jsonify(book_to_dict(row))


app.register_blueprint(bp)

client = app.test_client()
print(client.post("/books", json={"title": "Dune", "author": "Frank Herbert", "isbn": "978-0441172719", "rating": 5}).status_code)   # 201
print(client.post("/books", json={"title": "Dune", "author": "Frank Herbert", "isbn": "978-0441172719"}).status_code)               # 409, UNIQUE isbn
print(client.post("/books", json={"title": "Emma", "author": "Jane Austen", "rating": 9}).status_code)                              # 409, CHECK rating
print(client.post("/books", json={"title": "No author"}).status_code)                                                               # 400
print(client.get("/books/1").json["author"], client.get("/books/99").status_code)
print([b["title"] for b in client.get("/books?order=rating&q=du").json])

Source: Flask: Using SQLite 3 with Flask

The last request in the block uses an order and a search term: with rating as the order and du as the term, only Dune comes back. The blueprint is registered on an application that was already created, which Flask 2.3 and later forbids once the app has handled a request; here the app had not yet served any. In a real project the registration belongs in the factory (see the Blueprints page). The same view with curl:

curl -X POST http://127.0.0.1:5000/books -H "Content-Type: application/json" \
     -d '{"title": "Dune", "author": "Frank Herbert", "isbn": "978-0441172719", "rating": 5}'
curl "http://127.0.0.1:5000/books?order=rating&q=du"

The helper query_db(query, args=(), one=False) of the official Patterns page wraps execute, fetchall and a "first row or `None`" in one function. It is a taste decision: it shortens views, and it hides which cursor is open. Keep the helper if the application has dozens of queries; write the SQL in the view if it has a handful.

Reading Rows

Call Result

cur.fetchone()

The next row, or None when there is none; test with is None before indexing

cur.fetchall()

A list of every remaining row; fine for a page of results, wasteful for a whole table

cur.fetchmany(n)

At most n rows, for batching

for row in cur:

Iterates lazily, one row at a time

cur.lastrowid

The rowid generated by the last INSERT on that cursor

cur.rowcount

Rows changed by the last INSERT, UPDATE or DELETE; check it to answer 404 for an update that matched nothing

INSERT ... RETURNING

The inserted row in one statement (SQLite 3.35 and later), instead of lastrowid followed by a SELECT

If a single custom type is wanted, row_factory can be any callable that receives (cursor, row). The Patterns page shows a make_dicts function that builds a dict from cursor.description; the example below maps rows to a dataclass, which gives attribute access and type hints:

from dataclasses import dataclass


@dataclass(frozen=True, slots=True)
class BookRow:
    id: int
    title: str
    author: str


def book_factory(cursor: sqlite3.Cursor, row: tuple) -> BookRow:
    names = [col[0] for col in cursor.description]
    return BookRow(**dict(zip(names, row)))


with app.app_context():
    db = get_db()
    db.row_factory = book_factory
    print(db.execute("SELECT book.id, book.title, author.name AS author "
                     "FROM book JOIN author ON author.id = book.author_id").fetchall())

Source: Python: sqlite3, DB-API 2.0 interface for SQLite databases

Transactions

By default (autocommit left at its legacy value) the sqlite3 module opens a transaction before an INSERT, UPDATE, DELETE or REPLACE and keeps it open until commit() or rollback(). A SELECT does not start one. Closing a connection with a pending transaction discards it, so a write that is never committed is lost without an error. That is the second most common beginner bug after the missing g check: the view "works", the response is 201, and the row is not there.

The connection is also a context manager, but not the way a file is: with db: commits when the block ends normally and rolls back if it raises, and it does not close the connection. The create() view above relies on it so that the author row and the book row appear together or not at all.

with app.app_context():
    db = get_db()
    db.row_factory = sqlite3.Row
    before = db.execute("SELECT count(*) FROM author").fetchone()[0]
    try:
        with db:
            db.execute("INSERT INTO author (name) VALUES (?)", ("Temporary Author",))
            db.execute("INSERT INTO book (author_id, title) VALUES (?, ?)", (9999, "Orphan"))   # no author 9999
    except sqlite3.IntegrityError as exc:
        print("rolled back:", exc)
    after = db.execute("SELECT count(*) FROM author").fetchone()[0]
    print(before == after, db.in_transaction)

Source: Python: Transaction control

The foreign key that makes the second insert fail only exists because get_db() enabled the pragma. Without it the orphan row would have been written. A summary of the transaction knobs:

Setting Effect

autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL (default)

Pre-3.12 behaviour driven by isolation_level. Implicit BEGIN before data-changing statements; commit() and rollback() are yours to call

autocommit=False (Python 3.12 and later)

PEP 249 behaviour: a transaction is always open; commit() or rollback() close it and the next statement opens another. The documentation recommends it for new code

autocommit=True

SQLite’s own autocommit: every statement commits by itself. Use explicit BEGIN …​ COMMIT where several statements must stay together

isolation_level="DEFERRED" (default), "IMMEDIATE", "EXCLUSIVE", None

Which BEGIN the module issues; only effective with the legacy autocommit. IMMEDIATE takes the write lock at the start, which avoids a failed upgrade from reader to writer in the middle of a transaction

Changing the mode has to happen when the connection is created, in get_db(). For a read-modify-write sequence such as "decrease stock, then insert the order" prefer isolation_level="IMMEDIATE": the second writer waits at the beginning instead of failing at the end with database is locked.

Threads, Workers and Locking

Flask servers are concurrent: the development server and Waitress use threads, Gunicorn uses worker processes (and threads with the gthread worker), and any of them can run several requests at the same time. SQLite handles this correctly if each request has its own connection, which the g pattern guarantees. What changes between servers is the cost.

Topic What to know

check_same_thread=True (default)

A connection may only be used by the thread that created it, otherwise ProgrammingError. Leave it on. A connection on g stays inside one context, so it is never shared. Setting it to False to "fix" an error usually hides a global connection that should not exist

sqlite3.threadsafety

Reports how much of the module can be shared across threads (0, 1 or 3; it is read from the SQLite library since Python 3.11). It is 3 here, but a connection per context makes the value irrelevant

timeout (default 5.0 seconds)

How long a connection waits for a lock held by another before raising OperationalError: database is locked. Raise it for write-heavy endpoints

Journal mode WAL

PRAGMA journal_mode = WAL lets readers and one writer work at the same time, instead of readers blocking the writer. It is a property of the database file and persists once set, so setting it in init_db() is enough. It does not work over network file systems

PRAGMA synchronous = NORMAL

Commonly paired with WAL; fewer fsyncs, with the durability trade-off described in the SQLite documentation

PRAGMA busy_timeout

Per-connection wait time in milliseconds; the timeout argument of connect() sets the same thing

Several machines or containers

The file must be on the local disk of one host. A shared network volume is not safe for SQLite locking

 
import threading

wal_db = sqlite3.connect(WORK / "wal.db", timeout=10)
print(wal_db.execute("PRAGMA journal_mode = WAL").fetchone()[0])
wal_db.execute("CREATE TABLE hits (n INTEGER)")
wal_db.commit()
wal_db.close()


def worker(count: int) -> None:
    conn = sqlite3.connect(WORK / "wal.db", timeout=10)        # one connection per thread
    for _ in range(count):
        with conn:
            conn.execute("INSERT INTO hits VALUES (1)")
    conn.close()


threads = [threading.Thread(target=worker, args=(50,)) for _ in range(4)]
for t in threads:
    t.start()
for t in threads:
    t.join()
check = sqlite3.connect(WORK / "wal.db")
print(check.execute("SELECT count(*) FROM hits").fetchone()[0])
check.close()

Source: SQLite: Write-Ahead Logging

Four threads, each with its own connection, wrote 200 rows with no error. WAL mode is the standard advice for web applications on SQLite; it adds two side files (-wal and -shm) next to the database, which matters for backups and for container volumes.

Gunicorn with several workers means several processes sharing the file: this works, and the lock is what serialises writes. A g connection is also not an object pool: opening a SQLite connection costs microseconds, so no pooling is needed. For a server database, the equivalent of this section is a connection pool created once in the factory and borrowed per context.

Testing and Throw-Away Databases

A test needs a fresh database each time. The Flaskr tutorial’s testing chapter uses a fixture that creates a temporary file, passes its path as DATABASE, runs init_db() inside an application context and removes the file afterwards. The full pytest layout belongs to the Testing page later in this section; the idea in the form of a context manager, which runs here:

import contextlib
import shutil


@contextlib.contextmanager
def make_test_app():
    tmp = Path(tempfile.mkdtemp(prefix="bookshelf-test-"))
    test_app = create_app({"DATABASE": str(tmp / "test.sqlite"), "TESTING": True})
    test_app.register_blueprint(bp)        # blueprint objects can be registered on more than one app
    with test_app.app_context():
        init_db()
    try:
        yield test_app
    finally:
        shutil.rmtree(tmp, ignore_errors=True)


with make_test_app() as test_app:
    c = test_app.test_client()
    assert c.get("/books").json == []
    assert c.post("/books", json={"title": "Emma", "author": "Jane Austen"}).status_code == 201
    assert len(c.get("/books").json) == 1

with make_test_app() as test_app:       # a second app: its database is empty again
    assert test_app.test_client().get("/books").json == []
print("isolated")

Source: Flask Tutorial: Test Coverage

Notes on testing:

  • An in-memory database (":memory:") is a poor fit for a per-context connection: every sqlite3.connect(":memory:") creates a different database, so the tables created by init-db do not exist in the connection that the next request opens. Use a temporary file, or a shared-cache URI (file:name?mode=memory&cache=shared with uri=True) and keep one connection open for the life of the test.

  • Python 3.13 emits a ResourceWarning when a connection is garbage-collected without close(). The teardown_appcontext function exists to prevent that, so a warning in the test output means a connection was opened somewhere other than get_db().

  • Test the CLI with app.test_cli_runner(), as above, and test constraint violations by posting the bad data: the 409 of the create() view above is a behaviour worth a test.

Schema Versions Without a Tool

A schema.sql that drops and recreates tables is only right for the first deployment. After that, changes to a schema have to keep the data. A full migration tool is the subject of the Database Migrations page of this section (Alembic through Flask-Migrate, for SQLAlchemy models); for a plain sqlite3 application a numbered list of statements is often enough, and SQLite has a free integer in the file header for it, PRAGMA user_version:

MIGRATIONS = [
    "CREATE TABLE IF NOT EXISTS shelf (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)",
    "ALTER TABLE book ADD COLUMN shelf_id INTEGER REFERENCES shelf (id)",
]


def migrate(db: sqlite3.Connection) -> int:
    current = db.execute("PRAGMA user_version").fetchone()[0]
    for number, statement in enumerate(MIGRATIONS, start=1):
        if number > current:
            with db:
                db.execute(statement)
                db.execute(f"PRAGMA user_version = {number}")    # a pragma takes no bound value; number is our own int
    return db.execute("PRAGMA user_version").fetchone()[0]


with app.app_context():
    print(migrate(get_db()), migrate(get_db()))       # the second call changes nothing

Source: SQLite: PRAGMA user_version

ALTER TABLE in SQLite is limited (adding a column is easy; changing or dropping a constraint usually means creating a new table, copying the rows and renaming it), which is one of the reasons Alembic has a "batch" mode for SQLite. The migrate function can be called from init_db_command or from its own CLI command, and it must run before the workers start, not in each of them. The PRAGMA statement accepts no placeholders, which is why the number is formatted in, and why it must never be request data.

Backups and Inspection

A SQLite database in WAL mode cannot be safely backed up by copying the main file while the application is writing. The Connection.backup() method copies a consistent snapshot to another database, even while other connections are in use:

def backup_database(target: Path) -> None:
    source = sqlite3.connect(app.config["DATABASE"])
    dest = sqlite3.connect(target)
    with dest:
        source.backup(dest)
    source.close()
    dest.close()


backup_database(WORK / "bookshelf-backup.sqlite")
restored = sqlite3.connect(WORK / "bookshelf-backup.sqlite")
print(restored.execute("SELECT count(*) FROM book").fetchone()[0])
restored.close()

Source: Python: Connection.backup

For quick inspection the sqlite3 command-line shell, if installed, opens the file (sqlite3 instance/bookshelf.sqlite, then .tables and .schema), and Python itself provides python -m sqlite3 instance/bookshelf.sqlite as an interactive shell (since Python 3.12).

Using the Database Outside a Request

Seed scripts, report commands and background jobs use the same get_db(); they only need the context. A command registered on app.cli already has one, and a plain script pushes it:

@click.command("seed")
@with_appcontext
def seed_command() -> None:
    db = get_db()
    with db:
        db.execute("INSERT OR IGNORE INTO author (name) VALUES (?)", ("Ursula K. Le Guin",))
    click.echo(f"{db.execute('SELECT count(*) FROM author').fetchone()[0]} authors")


app.cli.add_command(seed_command)
print(runner.invoke(args=["seed"]).output.strip())

with app.app_context():
    print(get_db().execute("SELECT count(*) FROM book").fetchone()[0])

Source: Flask: Command Line Interface

A thread started from a view does not inherit the context of the request, so it must push its own with app.app_context() before calling get_db(). Never hand the request’s connection to another thread.

Pitfalls

Pitfall Fix

SQL built with f-strings, + or % from request data

Placeholders for every value; allow-lists for identifiers and sort orders

A module-level connection

A connection per context on g, created lazily, closed in teardown_appcontext

g.db in the teardown

g.pop("db", None): the teardown also runs when no connection was opened

Forgetting commit()

with db: around each write, or call db.commit(); a request that ends with an open transaction loses it

Foreign keys silently ignored

PRAGMA foreign_keys = ON on every connection

init-db erasing production data

Confirmation option, IF NOT EXISTS and numbered migrations; never run the destructive schema on a live file

:memory: databases in tests

A temporary file or a shared-cache URI

database is locked

Short transactions, timeout, WAL mode, one writer path; consider a server database when writes are heavy

The database file inside the package or committed to git

The instance folder, ignored by git; mount a volume in containers

A dictionary-like row sent to jsonify

dict(row) first; convert datetime explicitly if the format matters

executescript() with request data

It takes no parameters and commits implicitly; use it only for files you ship

Returning raw sqlite3.IntegrityError text to clients

Map it to 409 or 422 with a message you wrote (see the Error Handling page); constraint names reveal the schema

Security Notes

  • Placeholders are the defence against SQL injection. Add input validation as a second layer (types, lengths, allow-lists), and keep the database account’s rights as small as possible on servers that support it; SQLite has no accounts, so its protection is the operating system’s file permissions on the file and on the instance folder.

  • The database file contains the data in the clear. Keep it out of any directory served by the web server or by Flask’s static folder, and out of the container image layers if it holds real data.

  • Never put credentials in code: a server database URL belongs in the environment or the instance configuration, as described on the Configuration page; SQLite needs only a path.

  • Do not show database error messages to the user, and do not run the debugger in production.

  • Back up files that are in WAL mode with Connection.backup() or the SQLite shell’s .backup, not with a file copy.

Recent and Upcoming Changes

Version Component Change

Flask 2.2

Flask

Commands run through the flask script and registered on app.cli or blueprint.cli run inside an application context; @with_appcontext is no longer needed on commands created with app.cli.command()

Flask 2.3

Flask

before_first_request was removed, so the one-time database setup moves to the factory or to init-db; registering blueprints after the app has served a request is an error

Python 3.11

sqlite3

threadsafety is set from the SQLite library instead of a fixed 1

Python 3.12

sqlite3

The autocommit parameter and attribute were added; the default adapters and converters (date, datetime, timestamp) are deprecated, so register your own

Python 3.13

sqlite3

Positional use of most connect() parameters beyond the first is deprecated (keyword-only in 3.15); a ResourceWarning is emitted for connections that are not closed

Python 3.14

sqlite3

Named placeholders with a sequence of parameters raise ProgrammingError

Flask 3.2 (unreleased)

Flask

The request context is merged into the application context and all teardown functions run even if one fails. g, current_app and teardown_appcontext stay as they are, so this pattern does not change; make close_db safe to run on its own, as above

Source: Python: sqlite3

Where the Books Are Outdated

The books of the bibliography use databases only in passing before they move to SQLAlchemy, and some of their habits are no longer current:

In the books Today

One global connection, or sqlite3.connect() in each view without closing it

A connection on g, closed by teardown_appcontext

A database created with db.create_all() or by an import side effect, or before_first_request

An explicit init-db command, the factory, or a migration tool; before_first_request was removed in Flask 2.3

SQL assembled with % or .format()

Bound parameters, always

Model.query.get(id) once the examples move to SQLAlchemy

The SQLAlchemy 2.0 style on the Flask-SQLAlchemy page (db.session.get, db.session.execute(db.select(...)))

@with_appcontext on every command

Not needed for commands created with app.cli.command() since Flask 2.2; still useful on click.command() objects added with add_command() and exercised by app.test_cli_runner()

Secrets and database passwords in source code

Environment variables or the instance folder; see the Configuration page

datetime handling through the default sqlite3 converters

Deprecated since Python 3.12; register converters or store ISO strings

When to Move to SQLAlchemy

Stay with sqlite3 while the schema is small and the queries are few. Move when most of these start to hurt: the same SQL for the same entity is repeated in several views, relationships need joins in many places, you maintain the schema with hand-written migrations, a second database engine is on the horizon, or a test needs the same models in several states. Flask-SQLAlchemy (the next page of this section) keeps the pattern of this page, because it also keeps a session per application context and removes it in teardown_appcontext; it replaces the SQL strings with a model layer, a connection pool and a migration path through Alembic. The sqlite3 knowledge carries over: placeholders become bound parameters automatically, transactions become session.commit(), and PRAGMA foreign_keys becomes an engine event.

References