sqlite-diff-log

Zero-dependency, trigger-based audit logging for SQLite databases. Automatically tracks INSERT, UPDATE, and DELETE operations as structured JSON diffs — no ORM, no schema changes, no external services.

Why this exists

Existing ecosystem tools like sqlite-utils require running CLI commands out-of-band or manually computing diffs inside application code. sqlite-diff-log takes a different approach: it delegates diff generation to SQLite's own C core using native AFTER triggers and json_object().

• Zero runtime dependencies — built strictly with the Python standard library (sqlite3, json).
• Language- and process-agnostic — because the logic lives entirely in SQLite triggers, not in Python code, any process that writes to the database gets audited automatically, whether it's another Python script, a Node.js service, a Go binary, or the sqlite3 CLI. You attach the trigger once; every writer downstream is covered.
• SQL-injection protected — all identifiers (table names, column names, trigger names) are quoted and validated against sqlite_master before use; all literals are escaped.
• Litestream / replication friendly — audit logs live inside a standard SQLite table, so they replicate transparently with tools like Litestream or Turso.

Installation

pip install sqlite-diff-log

Quick Start

import sqlite3
from sqlite_diff_log import SQLiteDiffLog

conn = sqlite3.connect("app.db")
audit = SQLiteDiffLog(conn)

# Attach triggers to a table (pk_col defaults to "id")
audit.attach_to_table("users", pk_col="id")

# Any INSERT / UPDATE / DELETE on "users" — from this process
# or any other process writing to the same database file —
# is captured automatically from this point on.
with conn:
    conn.execute("UPDATE users SET role = 'admin' WHERE id = 42")

# Query structured diffs
diffs = audit.get_logs("users")
print(diffs[0]["action"])     # "UPDATE"
print(diffs[0]["old_data"])   # {'id': 42, 'name': 'Alice', 'role': 'user'}
print(diffs[0]["new_data"])   # {'id': 42, 'name': 'Alice', 'role': 'admin'}

How it works

+------------------+         SQL Operations         +--------------------+
|  Any Writer       |  --------------------------->  | Target Table       |
|  (Python, Node,   |   (INSERT / UPDATE / DELETE)   +--------------------+
|   Go, sqlite3 CLI…)|                                        |
+------------------+                          Native SQLite Triggers
                                                               |
                                                               v
                              +--------------------+   JSON Diffs
                              | _audit_log Table   |  <----------------
                              +--------------------+
                                       ^
                                       |
                              +--------------------+
                              | Audit Reader API   |
                              +--------------------+

attach_to_table() installs three AFTER triggers (INSERT, UPDATE, DELETE) directly in SQLite. Each trigger fires inside the same transaction as the write it audits and inserts a JSON snapshot of the old and/or new row into _audit_log. Because the triggers are part of the database schema itself — not application code — they apply to every writer, not just the process that called attach_to_table().

Audit log schema

API

SQLiteDiffLog(conn: sqlite3.Connection)

Wraps an existing connection and creates the _audit_log table if it doesn't exist.

attach_to_table(table_name: str, pk_col: str = "id") -> None

Installs INSERT / UPDATE / DELETE triggers on table_name. Idempotent — safe to call multiple times (CREATE TRIGGER IF NOT EXISTS). Raises ValueError if the table doesn't exist or pk_col isn't a valid column.

get_logs(table_name: Optional[str] = None) -> List[Dict[str, Any]]

Returns audit entries as a list of dicts, with old_data / new_data already parsed from JSON. Pass a table name to filter, or omit to get every audited table.

Limitations

• Single-column primary keys only. Tables must use a single PK column (e.g. id). Composite primary keys are not currently supported.
• No caller identity. Triggers only see row data (OLD.* / NEW.*), not who made the change or from which process — SQLite triggers have no concept of a connected user or session. If you need "who changed this," you'll need to pass that context into the row data yourself (e.g. an updated_by column) or handle it at the application layer.
• Schema changes require re-attaching. If you add or drop columns on an audited table, drop and recreate the triggers (attach_to_table again) so the generated json_object() calls reflect the current schema.

Running Tests

python -m unittest test_sqlite_diff_log.py

License

MIT — see LICENSE.

Author

MigMarGil