IdeaWell

Project ideas from Hacker News discussions.

SQLite as a Document Database (2020)

Original Article

Hacker News Discussion

📝 Discussion Summary (Click to expand)

Theme 1 – Using SQLite (or similar) as a document‑oriented store
Many commenters describe leveraging SQLite’s JSON/JSONB support, virtual/generated columns, and separate blob columns to model document‑like data while still gaining relational features such as indexing and change‑data‑capture.
- “I am using SQLite as document db for a side project for years now… the repo class stores write and delete timestamps as separate columns so I can have CDC.” – stanac
- “In my personal project (a game) I use an indexed key + a JSONB to store the save state…” – delduca
- “JSON extensibility and virtual columns help a lot with variable metadata.” – rcarmo

Theme 2 – What counts as a “document database” versus a relational store
A recurring thread debates the semantics of the term document: whether JSON blobs in a relational DB truly make it a document store, and how the idea contrasts with classic document‑oriented systems like CouchDB or MongoDB.
- “Why do people say document database when they really just mean json database?” – WhitneyLand
- “The 1st edition CouchDB book… explained it like this: …Self‑Contained Data … An invoice contains all the pertinent information …” – QuantumNomad_
- “The 'relational' in relational databases is not about department number … a database with a single table is still relational.” – fipar

Theme 3 – Trade‑offs, alternatives, and practical considerations
Commenters weigh the simplicity of SQLite/JSONB against purpose‑built document stores, discuss backup strategies (Litestream, custom CDC), and note when a relational model may be preferable for analytics or scaling.
- “Why don’t you use MongoDB? MongoDB is web scale.” – mayankbpatel (tongue‑in‑cheek)
- “I know there are things like Litestream, I wanted something in process and something that can send alerts on failed backups.” – stanac
- “Most data is actually inherently relational… Even in your example … the individual components … are better represented as relational datastores.” – SOLAR_FIELDS
- “Immutable records which are retained for legal purposes should be in object storage, not a database.” – sgarland

🚀 Project Ideas

SQLite JSON Document Repository (SJDR)

Summary

  • A lightweight library that provides a repository base class for SQLite JSONB document storage, automatic blob separation, CDC timestamps, and push‑to‑object‑storage NDJSON backups with failure alerts.
  • Core value: gives developers document‑db flexibility on SQLite while adding reliability features (change tracking, backup, alerting) out of the box.

Details

Key Value
Target Audience Indie developers, side‑project builders, and small teams using SQLite for document‑like data
Core Feature Repository abstraction with JSONB columns, blob columns, write/delete timestamps, automated periodic NDJSON export to S3/GCS, and webhook/email alerts on backup failure
Tech Stack SQLite (with JSONB), Python (or Go) library, optional rclone/restic for object storage, Prometheus‑style alert hooks
Difficulty Medium
Monetization Hobby

Notes

  • HN users praised custom repository base classes with blob handling and CDC (“Made a custom repository base class that can also store blobs in separate columns… CDC is used for building cached view models…” – stanac).
  • Would spark discussion on balancing document flexibility with relational safety and could become a go‑to utility for SQLite‑based side projects.

SQLite CDC Backup & Alert Agent

Summary

  • A daemon that runs alongside any SQLite‑based app, monitors the WAL, takes consistent snapshots every N minutes, uploads them as NDJSON to object storage, and sends alerts via Slack/email on any failure.
  • Core value: zero‑configuration, in‑process backup solution with reliable alerting, eliminating the need for external tools like Litestream for simple use cases.

Details

Key Value
Target Audience Developers running SQLite on VPS, home servers, or edge devices who need reliable backup and alerting
Core Feature WAL‑based snapshotter, periodic NDJSON export, exponential backoff retry, alert integration (webhook, email, Slack), lightweight HTTP health endpoint
Tech Stack Go (or Rust) binary, SQLite C bindings, object storage SDK (AWS S3, GCS, Backblaze), optional Prometheus metrics
Difficulty Medium
Monetization Hobby

Notes

  • Stanac expressed desire for “something in process and something that can send alerts on failed backups” – exactly what this agent provides.
  • Could generate practical utility for hobbyists and small businesses, and discussion around optimal snapshot frequency and storage costs.

JSON Field Indexer for SQLite

Summary

  • A CLI tool that inspects JSONB columns in an existing SQLite schema, recommends generated/virtual columns for frequently queried keys, creates the columns and indexes, and generates migration scripts.
  • Core value: automates the tedious process of making JSON fields queryable, turning ad‑hoc JSON storage into a hybrid document/relational model without manual SQL.

Details

Key Value
Target Audience SQLite developers using JSONB for semi‑structured data who want better query performance
Core Feature Schema scan, field frequency analysis, automatic ALTERTABLE … GENERATED ALWAYS AS VIRTUAL + INDEX creation, migration SQL output
Tech Stack Python script using sqlite3 module, optional integration with Alembic/Flyway, JSON schema inference libraries
Difficulty Low
Monetization Hobby

Notes

  • Commenters discussed extracting JSON keys into generated columns for efficient queries (nchmy’s mariadb example, vidarh’s Postgres note) – this tool would automate that workflow.
  • Useful for turning prototype JSONB tables into production‑ready indexed structures, likely to be well‑received on HN for its simplicity and immediate performance gains.

Read Later