Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Honker is a clever SQLite extension that brings Postgres-style NOTIFY/LISTEN semantics, offering durable pub/sub, task queues, and event streams directly within your SQLite database file. It cleverly bypasses external brokers and achieves atomic transactions by observing WAL file changes, providing single-digit millisecond cross-process notifications. This project resonates with the growing "just use SQLite" movement, providing robust messaging capabilities without the operational complexity of additional datastores.
The Lowdown
Honker is a SQLite extension and set of language bindings designed to bring advanced messaging capabilities typically found in server-based databases like PostgreSQL (e.g., NOTIFY/LISTEN) directly to SQLite. By leveraging the SQLite Write-Ahead Log (WAL) file, Honker enables push-style event delivery and cross-process notifications with single-digit millisecond latency, all without requiring a daemon or separate message broker.
Key features and concepts include:
- WAL-based Notification: Instead of traditional polling, Honker monitors the WAL file for changes, providing a lightweight, efficient mechanism for inter-process communication.
- Atomic Transactions: Queue enqueues, stream publishes, and notifications can be committed atomically with your business writes within the same SQLite transaction, ensuring data consistency and simplifying rollback scenarios.
- Durable Work Queues: It offers features like retries, priority, delayed jobs, dead-letter tables, and even crontab-style periodic tasks, akin to systems like pg-boss or Oban.
- Event Streams: Durable pub/sub with per-consumer offsets allows for reliable event replay and processing.
- Ephemeral Pub/Sub: Simple NOTIFY/LISTEN functionality for non-durable, real-time messaging.
- Language Agnostic: Provided as a SQLite loadable extension, it offers bindings for Python, Node.js, Rust, Go, Ruby, Bun, Elixir, and C++, making it widely accessible.
- Single-Machine Focus: Designed for single-server architectures where SQLite is the primary datastore, avoiding the complexities of distributed systems.
Honker addresses a common pain point for developers building applications around SQLite: the need for robust messaging and task management without introducing additional database systems or complex infrastructure. It provides a pragmatic and performant solution for "shipped projects" that increasingly rely on SQLite for their primary data storage needs.
The Gossip
Polling vs. Platform Notifications
A significant discussion revolved around Honker's choice of `stat(2)` polling on the WAL file over native OS notification mechanisms like `inotify`, `kqueue`, or `FSEvents`. While `stat(2)` might seem less efficient, the author clarified that cross-platform inconsistencies (e.g., `FSEvents` on Darwin dropping same-process notifications) made it the only reliable solution across all target environments. Commenters also explored the surprising affordability of `stat(2)` at millisecond intervals, noting its minimal CPU overhead due to fast syscall performance.
Use Case Utility and Atomic Transactions
Commenters explored the primary use cases and benefits of Honker, particularly highlighting its value for applications using SQLite as a primary datastore. The ability to perform atomic transactions, where queueing data or publishing events commits or rolls back with the main business write, was highly praised as a solution to the 'dual-write problem'. Some questioned if it's primarily useful for languages with process-based concurrency (Python, JS, Ruby) versus those with stronger in-process concurrency (Java, Go, C#), while others saw its potential for creating database-agnostic backends or working with read-only Litestream replicas.
Design Ingenuity and Efficiency
The technical design of Honker sparked interest, with questions about its durability, concurrency under load, and the specific choices made. The author's explanation of how the system piggybacks on SQLite's native transactions for atomicity was well-received. Discussions also touched on potential optimizations, such as storing subscriber states to avoid waking all listeners on every WAL change, though the current 'over-triggering' design (where listeners filter on `SELECT`) was defended for its simplicity and the efficiency of many small SQLite queries.