Flask REST API with SQLite: Build and Test a CRUD Application

Python course module guide: Web development with Python

A Flask REST API with SQLite is a practical capstone for connecting Python functions, HTTP, validation, SQL, exceptions and tests. This guide builds a small course API while keeping database and request boundaries explicit.

Define the CRUD contract

The sample API manages courses. Use GET /courses to list, GET /courses/<id> to read one, POST /courses to create, PATCH /courses/<id> to update and DELETE /courses/<id> to remove. Decide response fields and error shapes before writing handlers.

If HTTP methods, JSON and status codes are unfamiliar, first read Python APIs for beginners. To understand when Flask is preferable to other frameworks, use the Django vs Flask vs FastAPI comparison.

Create the schema

CREATE TABLE IF NOT EXISTS course (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    duration_weeks INTEGER NOT NULL CHECK (duration_weeks > 0),
    active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1))
);

SQLite is convenient for learning and small applications because Python includes the sqlite3 module and no separate database server is required. Concurrent write needs are limited, so production systems may later move to a server database.

Open and close a request-scoped connection

import sqlite3
from flask import Flask, g

app = Flask(__name__)
app.config["DATABASE"] = "courses.sqlite"

def get_db():
    if "db" not in g:
        connection = sqlite3.connect(app.config["DATABASE"])
        connection.row_factory = sqlite3.Row
        g.db = connection
    return g.db

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

The Flask application context stores one connection for the current request and closes it during teardown. This is a framework example of the lifecycle principles in Python context managers and safe cleanup.

List and read courses

from flask import jsonify

@app.get("/courses")
def list_courses():
    rows = get_db().execute(
        "SELECT id, name, duration_weeks, active FROM course ORDER BY id"
    ).fetchall()
    return jsonify([dict(row) for row in rows])

@app.get("/courses/<int:course_id>")
def get_course(course_id):
    row = get_db().execute(
        "SELECT id, name, duration_weeks, active FROM course WHERE id = ?",
        (course_id,),
    ).fetchone()
    if row is None:
        return {"error": {"code": "not_found", "message": "Course not found"}}, 404
    return dict(row)

Returning a dictionary or list allows Flask to create a JSON response. Keep the error structure consistent so clients do not need a different parser for every endpoint.

Validate and create a record

from flask import request

def validate_course(payload):
    errors = {}
    name = str(payload.get("name", "")).strip()
    if not name:
        errors["name"] = "Name is required"
    try:
        duration = int(payload.get("duration_weeks"))
        if duration <= 0:
            raise ValueError
    except (TypeError, ValueError):
        errors["duration_weeks"] = "Use a positive integer"
        duration = None
    return errors, name, duration

@app.post("/courses")
def create_course():
    payload = request.get_json(silent=True) or {}
    errors, name, duration = validate_course(payload)
    if errors:
        return {"error": {"code": "validation_error", "fields": errors}}, 400

    db = get_db()
    cursor = db.execute(
        "INSERT INTO course (name, duration_weeks) VALUES (?, ?)",
        (name, duration),
    )
    db.commit()
    return {"id": cursor.lastrowid, "name": name, "duration_weeks": duration}, 201

SQL placeholders keep data separate from SQL syntax. Never build a query by concatenating request values. Larger applications can raise domain-specific validation errors and translate them at the HTTP boundary, as explained in the Python custom exceptions guide.

Update and delete carefully

A PATCH handler should distinguish a missing record from an invalid field and should update only allowed columns. A DELETE handler can return 204 No Content after a successful deletion. Check the affected row count so a nonexistent ID does not receive a false success response.

Test with pytest and Flask’s client

def test_create_course(client):
    response = client.post(
        "/courses",
        json={"name": "Python", "duration_weeks": 8},
    )
    assert response.status_code == 201
    assert response.json["name"] == "Python"

def test_rejects_invalid_duration(client):
    response = client.post(
        "/courses",
        json={"name": "Python", "duration_weeks": 0},
    )
    assert response.status_code == 400
    assert "duration_weeks" in response.json["error"]["fields"]

Create a fresh temporary database for each test or test group so results do not depend on order. The pytest guide explains fixtures, parametrization and controlled dependencies.

Production safeguards

• Disable debug mode and keep secrets outside source code.
• Add authentication and authorization before exposing private data.
• Limit request sizes and validate every accepted field.
• Use database migrations instead of editing production tables manually.
• Add structured logs without storing tokens or sensitive data.
• Use a production WSGI server and reverse proxy rather than Flask’s development server.
• Choose a server database when concurrency and operational needs outgrow SQLite.

Capstone extensions

Add search, pagination, enrolments, role-based permissions and an audit trail. Publish an OpenAPI description, create a small frontend and add integration tests. Keep each extension behind a clear requirement and test rather than turning the project into an unstructured collection of features.

Flask REST API FAQs

Is SQLite suitable for production?

It can suit small, low-concurrency applications. A server database is usually better when write concurrency, scaling and operations become more demanding.

Should every response return status 200?

No. Use meaningful status codes such as 201 for creation, 400 for invalid input, 404 for missing records and 204 for deletion without a body.

Does Flask include authentication automatically?

No. Authentication and authorization require deliberate design or suitable extensions. Do not expose private CRUD endpoints before adding those controls.

Official reference: Flask tutorial for defining and accessing a database.