SQLite

Introduction

If you've run python manage.py runserver on a fresh Django project, you've already used SQLite. It's the db.sqlite3 file that appears in your project folder, and for years the advice has been to treat it as a toy and swap it out before launch. That advice is getting outdated. SQLite is the most widely deployed database in the world, it sits inside every phone and browser, and it's a perfectly good production choice for a surprising number of sites, including this one.

The Problem SQLite Solves

Most databases are servers. Before you can store a single row, you have to deal with:

• Installing and configuring a database server
• Creating users, passwords, and network permissions
• Keeping that server running, patched, and backed up
• Paying for a second machine (or a managed service) in production

For a small app, a personal blog, or a prototype, that's a lot of ceremony for a handful of tables. The database ends up being more work to operate than the application itself.

The SQLite Approach

SQLite flips the model. There is no server. The entire database is a single file, and your application reads and writes it directly through a library.

Core idea: A database doesn't have to be a separate service. It can be a file that lives next to your code.

Simple Example

-- Create a table
CREATE TABLE posts (
    id           INTEGER PRIMARY KEY,
    title        TEXT NOT NULL,
    summary      TEXT,
    is_published INTEGER DEFAULT 0,
    created_at   TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Insert and query it
INSERT INTO posts (title, is_published) VALUES ('Hello, SQLite', 1);

SELECT title FROM posts WHERE is_published = 1 ORDER BY created_at DESC;

You can run this straight from the command line with sqlite3 blog.db. Python ships with SQLite built in, so import sqlite3 works with nothing to install.

How This Blog Uses SQLite

This blog stores its posts and tags in a single SQLite file. That choice shapes how everything else works.

Zero-Setup Development

A new contributor clones the repo, runs migrate, and has a working database in seconds. There's no Docker container to start, no credentials to share, and no "works on my machine" mismatch between developers.

# settings.py
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "db.sqlite3",
    }
}

What's happening:

• Django opens the file at NAME, creating it if it doesn't exist
• Every query runs inside your application's process, with no network round trip
• Backing up the database means copying one file (more on doing that safely below)

Result: Queries that complete in microseconds, because there's no network between your code and your data.

Production-Friendly Settings

Out of the box, SQLite is tuned conservatively. A few settings make it much better for a web app that handles concurrent requests. Django 5.1 and newer lets you apply them directly in your settings:

# settings.py
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "db.sqlite3",
        "OPTIONS": {
            "transaction_mode": "IMMEDIATE",
            "init_command": (
                "PRAGMA journal_mode=WAL;"
                "PRAGMA synchronous=NORMAL;"
            ),
        },
    }
}

• journal_mode=WAL (write-ahead logging) lets readers keep reading while a write is in progress
• synchronous=NORMAL trades a tiny durability window for noticeably faster writes, and is considered safe in WAL mode
• transaction_mode: IMMEDIATE takes the write lock at the start of a write transaction, which avoids "database is locked" errors in busy moments

Full-Text Search

SQLite includes a full-text search engine called FTS5. It's a built-in feature, not an extension you have to install.

CREATE VIRTUAL TABLE post_fts USING fts5(
    title, summary,
    content='blog_post', content_rowid='id'
);

SELECT rowid FROM post_fts WHERE post_fts MATCH 'database' ORDER BY rank;

# views.py
from django.db import connection


def search(request):
    query = request.GET.get("q", "").strip()
    with connection.cursor() as cursor:
        cursor.execute(
            "SELECT rowid FROM post_fts WHERE post_fts MATCH %s ORDER BY rank",
            [query],
        )
        ids = [row[0] for row in cursor.fetchall()]
    posts = Post.published.filter(id__in=ids)
    return render(request, "blog/partials/post_cards.html", {"posts": posts})

Two things to know: an external-content FTS table needs triggers (or a rebuild step) to stay in sync with your posts, and raw user input can contain characters that FTS5 treats as query syntax, so wrap the input in double quotes or sanitize it before matching.

Why This Matters

1. Nothing to operate — No server process, no ports, no credentials. The database is just a file, so there's nothing to crash separately from your app.

2. Fast for read-heavy sites — Reads happen in-process with no network hop. For a blog, where nearly every request is a read, that's close to ideal.

3. Trivial backups and portability — Your whole database is one file you can copy, version, email, or open with any SQLite tool.

4. Remarkably reliable — SQLite is public domain, famously well tested, and has a long-term commitment to backward compatibility. Files written years ago still open today.

Key SQLite Features

SQLite + Django

Django supports SQLite out of the box, and it's the default for every new project. The ORM hides most of the differences, but a few are worth knowing:

# Works the same on SQLite and PostgreSQL
posts = Post.published.filter(tags__slug="django").distinct()

# JSONField works on SQLite too
Post.objects.filter(metadata__featured=True)

Flow:

1. A request arrives and the Django ORM builds a query
2. Python's built-in sqlite3 module runs it inside your process
3. SQLite reads the pages it needs from the database file
4. Rows come back and Django turns them into model instances
5. Your template renders the result

Features that rely on server-only extensions, such as django.contrib.postgres fields and lookups, aren't available. For most blogs and small apps, you won't miss them.

Real-World Use Cases

SQLite isn't just for development. It works well for:

• Personal sites and blogs — Read-heavy traffic and a single author is its sweet spot
• Internal tools — Small teams, low write volume, no infrastructure to maintain
• Prototypes and MVPs — Start fast, migrate later only if you need to
• Embedded and local-first apps — Desktop software, mobile apps, and CLI tools
• Test suites — Fast, disposable databases for CI
• Edge and per-tenant databases — One small file per customer or region

Getting Started

1. Check that SQLite is already there. Python includes it:

   bash python -c "import sqlite3; print(sqlite3.sqlite_version)"

2. Open a database file with the command-line shell:

   bash sqlite3 db.sqlite3

3. Explore it with .tables, .schema, and .headers on.

4. Turn on WAL mode and the Django options shown above before you deploy.

5. Back it up safely. Use sqlite3 db.sqlite3 ".backup backup.db" rather than copying the file while the app is writing to it.

Know the Limits

SQLite is honest about what it isn't built for:

• One writer at a time. Many readers are fine, but heavy concurrent writes will queue up.
• No network access. Multiple app servers can't share one database file safely, so it fits a single-server deployment.
• Fewer built-in features than PostgreSQL, with no advanced types, extensions, or role-based access control.

If you hit those walls, moving to PostgreSQL is a well-trodden path. Django's ORM keeps most of your code unchanged.

The Philosophy

SQLite isn't about being the most powerful database. It's about recognizing that most applications are smaller than we plan for, and that the simplest tool that works is often the best one. Fewer moving parts means fewer things to break at 3 a.m.

Start with a file. Measure honestly. Move to a database server when your traffic, not your anxiety, tells you to. Until then, you get a fast, dependable, zero-maintenance foundation, and that's exactly what this blog runs on.

This post is part of a series on the tools behind this blog: Django, HTMX, Tailwind CSS, PostgreSQL, and SQLite. Posts are authored in the Django admin as Markdown and rendered to HTML on save.

Read more about the tech stack