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):
    if not isinstance(payload, dict):
        return {"body": "Use a JSON object"}, None, None
    errors = {}
    unknown = set(payload) - {"name", "duration_weeks"}
    if unknown:
        errors["fields"] = "Unknown fields: " + ", ".join(sorted(unknown))
    raw_name = payload.get("name")
    name = raw_name.strip() if isinstance(raw_name, str) else ""
    if not name or len(name) > 120:
        errors["name"] = "Use a name of 1 to 120 characters"
    duration = payload.get("duration_weeks")
    if type(duration) is not int or not 1 <= duration <= 520:
        errors["duration_weeks"] = "Use an integer from 1 to 520"
        duration = None
    return errors, name, duration

@app.post("/courses")
def create_course():
    payload = request.get_json(silent=True)
    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.

Make the input contract stricter than a Python conversion

The validator above deliberately accepts an object with a text name and an integer duration. Its limits of 120 name characters and 520 weeks are example application rules, not Flask requirements. Document them with the API so a frontend can give helpful feedback before submitting.

Converting every name with a string function would turn a null value into the literal text “None”. Converting every duration with an integer function could silently truncate a decimal or accept a Boolean as one. The revised validation checks types first. An array or scalar body becomes a validation response instead of causing an attribute error when the handler expects an object.

This teaching handler deliberately uses silent JSON parsing and returns its own 400 validation response when parsing produces no usable object. A production contract may distinguish an unsupported media type from malformed JSON. Whichever policy you choose, test it explicitly rather than assuming every parsing failure returns the same response across frameworks or versions.

A request matrix that exposes real defects

Start with a valid course named Python, lasting eight weeks. Check the creation response, capture its generated ID, then read that ID and compare the stored fields. Do not assume the ID is always one: previous inserts, deletions and test setup can change generated identifiers.

Next submit a whitespace-only name, a null name, a decimal duration, a Boolean duration, a negative duration and an unexpected field. Each should fail under this contract without adding a row. Try a JSON array and malformed JSON as separate cases. After every rejected request, compare the database row count with its previous value; an error response alone does not prove that no data changed.

Finally, use a name containing an apostrophe. It should remain ordinary data and round-trip correctly through the parameterized statement. This checks that a legitimate customer value is not damaged by unnecessary manual escaping. Keep SQL identifiers fixed or explicitly allowlisted; value placeholders do not turn arbitrary user-selected column names into safe query structure.

Initialize the same database that the application opens

The schema block must be executed against the configured database before the first request. A “no such table” error often means initialization was skipped or ran against a different file. Relative paths can point somewhere unexpected when the working directory changes. For a repeatable project, choose one known database path and record it in the test setup without exposing sensitive filesystem details to API clients.

Use a temporary file for request-based tests. A separate in-memory SQLite connection ordinarily has its own database, so initializing one connection and opening another during a request can produce an empty database. For this beginner project, a temporary file makes connection lifetimes easier to understand.

Keep failures separate from successful writes

Closing a connection and committing a transaction are different responsibilities. Keep writes short, commit at a clear boundary and handle expected database failures deliberately. Do not return a creation response before the intended write succeeds. If several changes form one business action, define their transaction boundary so a later failure does not leave an unexplained partial result.

The snippets implement list, read and create operations; PATCH and DELETE are extension tasks, not completed handlers. For PATCH, validate only allowed supplied fields and decide what an empty update means. For DELETE, document whether repeating the request returns a missing-resource response or an empty success. Keep the behaviour consistent with your tests and API description.

Additional official documentation: Flask request API; Python sqlite3 documentation; Flask application context.

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.