Store product data with SQLite

SQLite is a practical default for a small single-service product. It runs in-process, stores data in one file, supports transactions, and lets you learn relational modeling before taking on a managed database.

Let constraints protect data

Primary keys, NOT NULL, UNIQUE, and CHECK rules prevent invalid states even if a future code path forgets validation.

Use parameters

Never build SQL by concatenating user input. Parameterized queries protect values and handle quoting correctly.

Use transactions

A with connection block commits successful work and rolls back when an exception occurs. Multi-step updates should succeed or fail together.

Back up the file

A database file is still production data. Test a backup and restore process before customers depend on it.

Working example

import sqlite3

connection = sqlite3.connect("product.db")
connection.row_factory = sqlite3.Row

with connection:
    connection.execute("""
        CREATE TABLE IF NOT EXISTS leads (
            id INTEGER PRIMARY KEY,
            email TEXT NOT NULL UNIQUE,
            status TEXT NOT NULL CHECK(status IN ('new', 'contacted'))
        )
    """)
    connection.execute(
        "INSERT OR IGNORE INTO leads(email, status) VALUES (?, ?)",
        ("buyer@example.com", "new"),
    )

rows = connection.execute("SELECT id, email, status FROM leads").fetchall()
for row in rows:
    print(dict(row))

Remember: A small database becomes dependable through constraints, parameters, transactions, and backups.