SQLite taste

SQLite taste

Lists production conventions for SQLite: WAL mode, foreign keys, busy timeouts, a single write connection, strict tables and integrity checks.

Prompt

# SQLite taste of an engineer who ships it to production - Use SQLite for single-node apps, CLIs, desktop and mobile apps, and edge caches before reaching for a database server. Confidence: 0.85 - Turn on WAL mode (`PRAGMA journal_mode = WAL`) so readers never block the writer. Confidence: 0.95 - Set `PRAGMA foreign_keys = ON` on every connection, since it is off by default. Confidence: 0.95 - Set a `busy_timeout` of a few seconds instead of letting writes fail immediately with `SQLITE_BUSY`. Confidence: 0.90 - Use `PRAGMA synchronous = NORMAL` with WAL for a good balance of durability and speed. Confidence: 0.75 - Funnel all writes through a single connection or queue, and use a pool of read-only connections for reads. Confidence: 0.80 - Start write transactions with `BEGIN IMMEDIATE` to avoid deadlocks when a read transaction tries to upgrade. Confidence: 0.80 - Declare tables `STRICT` on new schemas so type affinity does not silently store text in integer columns. Confidence: 0.80 - Store timestamps as ISO 8601 text or Unix epoch integers consistently, and pick one convention per database. Confidence: 0.85 - Use `INTEGER PRIMARY KEY` for row ids unless you need globally unique ids, then add a separate UUID or ULID column. Confidence: 0.75 - Batch many inserts inside one transaction; thousands of autocommit inserts are slow. Confidence: 0.95 - Check query plans with `EXPLAIN QUERY PLAN` and add covering indexes for hot queries. Confidence: 0.85 - Run `PRAGMA optimize` periodically or on connection close so the planner has fresh statistics. Confidence: 0.70 - Track the schema version with `PRAGMA user_version` and run numbered migrations at startup. Confidence: 0.80 - Remember that many `ALTER TABLE` changes need the create-copy-drop-rename pattern, and wrap it in a transaction. Confidence: 0.80 - Back up with the online backup API, `VACUUM INTO` or Litestream, never by copying the file while it is being written. Confidence: 0.95 - Use FTS5 for full-text search and JSON functions for semi-structured columns before adding another service. Confidence: 0.75 - Keep the database file on local disk, never on a network file system where locking is unreliable. Confidence: 0.90 - Use an in-memory or temporary-file database per test for fast, isolated test runs. Confidence: 0.85 - Run `PRAGMA integrity_check` in a scheduled job and alert on anything other than `ok`. Confidence: 0.65

@OpenSourceBuffGuy

I find openly licensed prompts and post them here.

0 followers · 2,873 prompts

More by @OpenSourceBuffGuy

See all
Ethereum Developer prompt image

Ethereum Developer

Asks the AI to act as an experienced Ethereum developer who writes a Solidity smart contract for an on-chain messenger with public reads and owner-only writes.

act-as, crypto, coding

1 view
Linux Terminal prompt image

Linux Terminal

Makes the AI act as a Linux terminal that replies to each typed command with only its output inside one code block.

simulator, linux, coding

5 views
English Translator and Improver prompt image

English Translator and Improver

Turns the AI into an English translator and editor that detects the input language and returns only an improved, more literary English version.

act-as, translation, rewriting, proofreading

2 views

Reviews

Loading reviews…

Discussion

Loading comments…