Working With SQLite From an Application

sqlite1-1 and sqlite1-2 described the "no connection ceremony" workflow. This chapter shows it in real, working application code, in two languages, so the simplicity claim is demonstrated rather than just asserted.

Python's Built-in sqlite3 Module

SQLite support is genuinely built into Python's own standard library — no pip install required at all. This is a real, distinctive fact worth naming: neither MySQL nor Postgres connectivity ships in Python's own standard library; both require a third-party package (mysql-connector-python, psycopg2) just to connect.

import sqlite3

with sqlite3.connect("notes.db") as conn:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")

    # Parameterized — never string-concatenate user input into SQL
    cursor.execute("INSERT INTO notes (body) VALUES (?)", ("First real note",))
    conn.commit()

    cursor.execute("SELECT * FROM notes")
    for row in cursor.fetchall():
        print(row)

The parameterized execute(sql, (param,)) form is deliberate — a direct callback to sqli1's own parameterization material and postgres1-8's own dynamic-SQL-injection warning. That lesson applies here unchanged, in a completely different language and context. The with sqlite3.connect(...) context-manager form is the idiomatic Python way to guarantee the connection is properly committed and closed.

Node.js's better-sqlite3

better-sqlite3 is a real, popular SQLite binding for Node — and it's deliberately, notably synchronous, in contrast to Node's usual async-everything idioms. The library's own documented reasoning: SQLite operations are so fast that the overhead of async/await machinery genuinely isn't worth it for typical use — a real design decision, not an oversight, and a direct, concrete instance of sqlite1-1's own "near-instant, no handshake" claim.

const Database = require('better-sqlite3');
const db = new Database('notes.db');

db.exec('CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)');

// Parameterized here too
db.prepare('INSERT INTO notes (body) VALUES (?)').run('First real note');

const notes = db.prepare('SELECT * FROM notes').all();
console.log(notes);

Side-by-Side — The Simplicity Gap

Closing Connections & Resource Management

Even though there's no network connection to manage, a SQLite connection object still wraps real operating-system file handles and should still be explicitly closed — via a context manager, a try/finally, or an explicit close() call — when done. It's easy to assume this doesn't matter "since it's just a file," but proper cleanup still matters, especially in the long-running server process context sqlite1-6 covered.

Hands-On Exercises