Flask SQLite Tutorial: Build a Small App With sqlite3 (Tested)

Ninad Pathak

October 7, 2026

A Flask app needs somewhere to keep data between requests, and SQLite does that in a single file. The catch is the connection: one opened at the top of app.py can fail with a threading error once requests come in. In this article I’ll walk you through Flask’s standard pattern by building a small notes API on SQLite, then trying every route with curl.

TLDR

Open one SQLite connection per request with a get_db() helper that stores it on Flask’s g, and close it in a teardown_appcontext function.
Don’t share one module-level connection across requests. Flask’s server handles requests on other threads, and sqlite3 refuses that with a ProgrammingError.

Create the tables with a flask init-db command that runs a schema.sql file.
Pass values with ? placeholders, and call commit() after every write.

What is Flask with SQLite, and what will we build?

Flask doesn’t include a database layer. Its documentation shows how to use Python’s built-in sqlite3 module directly (Flask: Using SQLite 3 with Flask). The connection lives on g, which the Flask tutorial calls “a special object that is unique for each request” (Flask tutorial: Define and Access the Database).

To show you that pattern working, I will build a small notes API. It’s one file, app.py, plus schema.sql.

Prerequisites

Requirement	Version used here
Python	3.13.14 (`sqlite3` is in the standard library)
Flask	3.1.3
`curl`	Any, to call the routes

Python 3.13.14 | Flask 3.1.3 | SQLite 3.53.4

Building a notes API with Flask and SQLite

Each step adds one file or one command to the project folder.

Step 1: Create the project

Make a folder with a virtual environment and install Flask:

$ pip install flask
Successfully installed ... flask-3.1.3 itsdangerous-2.2.0 jinja2-3.1.6 markupsafe-3.0.4 werkzeug-3.1.9

Step 2: Write the schema

Save this as schema.sql. The DROP TABLE line means init-db starts from an empty table each time you run it:

DROP TABLE IF EXISTS notes;
CREATE TABLE notes (
  id         INTEGER PRIMARY KEY,
  title      TEXT NOT NULL,
  done       INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Step 3: Write the app

Save this as app.py:

import sqlite3

import click
from flask import Flask, abort, g, jsonify, request

app = Flask(__name__)
app.config["DATABASE"] = "notes.db"


def get_db():
    """One connection per request, stored on Flask's g object."""
    if "db" not in g:
        g.db = sqlite3.connect(app.config["DATABASE"])
        g.db.row_factory = sqlite3.Row
    return g.db


@app.teardown_appcontext
def close_db(exception):
    db = g.pop("db", None)
    if db is not None:
        db.close()


@app.cli.command("init-db")
def init_db():
    """Create the tables from schema.sql."""
    with app.open_resource("schema.sql") as f:
        get_db().executescript(f.read().decode("utf8"))
    click.echo("Initialized the database.")


@app.get("/notes")
def list_notes():
    rows = get_db().execute("SELECT id, title, done FROM notes ORDER BY id").fetchall()
    return jsonify([dict(r) for r in rows])


@app.post("/notes")
def add_note():
    title = request.form.get("title", "").strip()
    if not title:
        abort(400, "title is required")
    db = get_db()
    cur = db.execute("INSERT INTO notes (title) VALUES (?)", (title,))
    db.commit()
    return jsonify(id=cur.lastrowid, title=title), 201


@app.post("/notes/<int:note_id>/done")
def mark_done(note_id):
    db = get_db()
    cur = db.execute("UPDATE notes SET done = 1 WHERE id = ?", (note_id,))
    db.commit()
    if cur.rowcount == 0:
        abort(404)
    return jsonify(id=note_id, done=True)


@app.delete("/notes/<int:note_id>")
def delete_note(note_id):
    db = get_db()
    db.execute("DELETE FROM notes WHERE id = ?", (note_id,))
    db.commit()
    return "", 204

What each part does:

get_db() opens the connection the first time a request needs it and reuses it for the rest of that request.

close_db() runs at the end of every request, even a failed one.
sqlite3.Row lets you read columns by name, so dict(r) turns a row into JSON.
The init-db command runs schema.sql through the same connection helper.

Step 4: Create the database

$ flask --app app init-db
Initialized the database.

This creates notes.db next to app.py.

Step 5: The same notes with Flask-SQLAlchemy

If you’d rather work with classes than SQL, Flask-SQLAlchemy stores a similar model in SQLite through an ORM:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///orm.db"
db = SQLAlchemy(app, model_class=Base)


class Note(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]


with app.app_context():
    db.create_all()
    db.session.add_all([Note(title="Try Flask-SQLAlchemy"), Note(title="Compare with sqlite3")])
    db.session.commit()
    for note in db.session.execute(db.select(Note).order_by(Note.id)).scalars():
        print(note.id, note.title)

$ pip install flask-sqlalchemy
Successfully installed flask-sqlalchemy-3.1.1 sqlalchemy-2.1.3 typing-extensions-4.16.0
$ python models_sqlalchemy.py
1 Try Flask-SQLAlchemy
2 Compare with sqlite3

See SQLAlchemy SQLite for the full SQLAlchemy setup.

Running it

Start the development server:

flask --app app run --port 5057

In a second terminal, call each route:

$ curl -s -X POST -d 'title=Buy milk' http://127.0.0.1:5057/notes
{"id":1,"title":"Buy milk"}
$ curl -s -X POST -d 'title=Write the report' http://127.0.0.1:5057/notes
{"id":2,"title":"Write the report"}
$ curl -s -X POST http://127.0.0.1:5057/notes/1/done
{"done":true,"id":1}
$ curl -s http://127.0.0.1:5057/notes
[{"done":1,"id":1,"title":"Buy milk"},{"done":0,"id":2,"title":"Write the report"}]
$ curl -s -o /dev/null -w '%{http_code}\n' -X DELETE http://127.0.0.1:5057/notes/2
204
$ curl -s http://127.0.0.1:5057/notes
[{"done":1,"id":1,"title":"Buy milk"}]
$ curl -s -o /dev/null -w '%{http_code}\n' -X POST http://127.0.0.1:5057/notes/99/done
404

Note 1 is marked done and note 2 is gone. The missing note returns 404 because rowcount was 0.

Common problems

The first two errors came up while testing, each with its message first. The third wasn’t reproduced here.

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread

This comes from a connection opened once at import time and shared by every request:

# The anti-pattern: one module-level connection shared by every request.
import sqlite3
from flask import Flask, jsonify

app = Flask(__name__)
conn = sqlite3.connect("notes.db")


@app.get("/count")
def count():
    return jsonify(conn.execute("SELECT count(*) FROM notes").fetchone()[0])

$ curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:5057/count
500

The server terminal shows the cause:

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 8376983936 and this is thread id 6115504128.

Flask’s development server handles each request in its own thread, and Python’s sqlite3 refuses to use a connection from another thread. Use the get_db() pattern from Step 3 instead.

sqlite3.OperationalError: no such table: notes

The app started before the tables existed. Run flask --app app init-db first:

$ curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:5057/notes
500

The server terminal shows:

sqlite3.OperationalError: no such table: notes

database is locked

A second request tried to write while another held the lock, and it waited too long. Keep write transactions short and commit right away. See database is locked.

Conclusion

The g plus teardown_appcontext pattern gives each request its own connection and closes it for you, which is all a small Flask app needs from SQLite. Start with plain sqlite3, and switch to Flask-SQLAlchemy if you’d rather work with model classes. SQLite fits a Flask app that runs on one server. Once several servers need to write to the same data, move to a client/server database.