You Don’t Need a Writer Service: Handling Single-Writer Databases with Retries

Modern SaaS apps skew heavily read‑mostly: list views, dashboards, detail pages, search. But even light write traffic can grind if your primary data store enforces single‑writer semantics or is sensitive to lock contention. We chose SQLite locally and LibSQL/Turso in production for their simplicity, edge‑friendliness, and great read performance. This post explains the patterns we used to make that choice scale: connection strategy, retries and backoff, idempotency, short transactions, and background reconciliation.

We’ll show concrete examples from our Klykd API. What worked, what didn’t, and what we standardized on.

Why SQLite/LibSQL

• Simplicity: zero‑admin locally; one managed endpoint in prod.
• Performance: fast reads, especially with a thoughtful index strategy.
• Edge‑friendly: fits serverless and distributed topologies.
• Tradeoffs: single writer per replica, lock sensitivity, “busy” contention, and transient connection errors across remote streams.

Principles

• Keep writes short: small, bounded transactions.
• Make writes idempotent: constrain in the database, not just the app.
• Prefer “persist‑first, reconcile later” for cross‑system workflows.
• Bias to eventual action with retries and exponential backoff.
• Optimize reads with targeted indexes.

Connection Strategy

Local SQLite and remote LibSQL deserve different pooling and DSN settings.

• Local dev: one connection, WAL, busy timeout.
  • We serialize access to avoid write‑write conflicts during development and CI: db.SetMaxOpenConns(1) and db.SetMaxIdleConns(1).
  • DSN turns on WAL, a 10s busy timeout, and foreign keys.
• Production (LibSQL/Turso): moderate pool, short lifetimes.
  • We tune the pool to cycle connections and avoid stale streams.

These small differences eliminate a surprising amount of local vs prod friction.

Retryable I/O

SQLite/LibSQL can surface “database is locked” (busy) and transient stream errors (driver.ErrBadConn). We wrapped all DB calls with exponential backoff.

• ExecRetry, QueryRetry, QueryRowScanRetry
  • Retries on ErrBadConn and “locked/busy” errors with backoff.
• Transaction lifecycle
  • Begin/Commit wrappers with selective retry.
• Driver‑level logging and error shaping
  • sqlmw interceptor logs queries and demotes transient ErrBadConn noise so the retry layer does the right thing.

This combination turns lock‑spikes and remote blips into latency, not failures.

Write Patterns

• Short transactions
  • Example: org creation begins a transaction, inserts org + membership, and commits with retry.
• Idempotency via unique constraints
  • Credit transactions have a unique reference, so a retried insert doesn’t double‑grant credits.
  • We use the Stripe session ID as the idempotency key when crediting subscriptions.
• Upserts and “persist‑first”
  • Webhook handlers upsert a “checkout session” row and return quickly; background workers finalize effects. See UpsertCheckoutSession and handler flow onward.
• Deferring cross‑system coupling
  • We never perform “do everything now” in the webhook. Instead, we record facts, fan‑out work, and finalize later when it’s safe.

These patterns dramatically reduce how often a single writer blocks user‑visible paths.

Background Reconciliation

Making the webhook and UI path fast allows us to handle the rest asynchronously, with retries and idempotency.

• Reconcile loop
  • Periodically picks up paid sessions that aren’t finalized and applies effects (add credits or activate subscriptions).
• Subscription finalization
  • A bounded, retried transaction to load plan, add credits idempotently, and upsert an active subscription.
• Renewal loop
  • A simple daily worker to extend subscription periods and re‑credit on schedule.

These loops make the system resilient to transient issues and instance restarts.

Read Performance

Reads dominate load, so we indexed the exact filters we rely on.

• Membership checks, org listings, filenames, “latest job per photo,” invite lookups, billing session recency, and more.
• We build indexes in a single transaction to avoid partial states.

Tight indexes plus read‑mostly flows make the single writer a non‑issue most of the time.

Schema Management

We keep migrations in code and additive.

• Baseline schema + “add column if missing” upgrades at startup.
• Billing schema evolves safely with checks and indexes.

This keeps deploys simple while allowing iterative evolution.

Gotchas We Hit

• Treating idempotency as “best effort” in app code without DB constraints resulted in rare double credits under retries.
  • Fix: unique index on the idempotency key (reference) and tolerate conflicts.
• Over‑pooling local SQLite made lock contention much worse.
  • Fix: single connection locally.
• Long transactions (e.g., doing remote API calls inside a transaction) amplified lock contention and “busy” errors.
  • Fix: keep transactions short; do IO before/after.

When We’d Choose Something Else

• High write concurrency where low latency matters more than operational simplicity.
• Heavy analytics or batch updates against the OLTP tables.
• Complex multi‑row invariants that are hard to express with constraints and short transactions.

In those cases, we’d split workloads: keep SQLite/LibSQL for product data and use a write‑friendly store (or queued writes) for high‑throughput paths.

Takeaways

• You can get a lot of mileage out of SQLite/LibSQL with the right patterns: single connection locally, tuned pools in prod, backoff on transient errors, idempotency constraints, short transactions, background reconciliation, and thoughtful indexes.
• The net effect is that “single writer” becomes a manageable operational detail rather than a scaling wall.