Hasty Briefsbeta

All tags

sqlite

23 stories total

Bilingual

SQLite Critical CVEs or LLM Slop?
2 months ago
- A GitHub repo published multiple SQLite vulnerability advisories that were quickly flagged as critical by NVD and CISA, but JFrog researchers found them to be AI-generated and false.
- The advisories cited non-existent code, referenced functions that didn't exist in the claimed versions, and provided PoCs that failed to trigger any crashes.
- None of the CVEs appeared on SQLite's official advisory page, and AI detection tools flagged the content as machine-generated.
- Red Hat initially assigned a 10.0 critical score to CVE-2026-51302 but later downgraded it to 7.6 high after further review.
- The CVE submission process lacks identity verification, and the NVD's pause on deep analysis in February 2024 has allowed fabricated advisories to enter databases.
- An audit of 55 advisories from the same account found 54 completely fabricated and one real but with unverified metadata.
- These fake CVEs waste organizational resources, pollute vulnerability databases, and can mislead AI-driven triage tools into generating unnecessary patches.
- Recommendations include verifying CVEs with official sources, investigating critical scores, checking affected environments, and reproducing PoCs in safe environments.
Choose DuckDB rather than SQLite
2 months ago
- DuckDB outperforms SQLite on writes by 4x to 15x and on read cliffs by 100x on a $16/month server.
- Read cliffs (maximum rows for dashboard queries) moved from 1M/100k/100k rows (SQLite) to 100M/10M/10M rows (DuckDB) for metrics, spans, and logs respectively.
- DuckDB stored 1 billion metric points in 10.8 GB on disk and ingested them in under an hour.
- Logs, previously kept off SQLite, became DuckDB's strongest result with 15x faster writes and 100x larger read cliffs.
- A critical ingest bug (lack of admission control) was fixed to produce reliable numbers; stock DuckDB config was used except for increased memory and checkpoint thresholds.
- The DuckDB build is recommended for self-hosting observability unless volume is very low or Alpine compatibility is required.
SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
2 months ago
- SQLite can be used in production for low-latency app servers by eliminating network overhead through local storage.
- Enable Write-Ahead Logging (WAL) mode with PRAGMA journal_mode=WAL to allow concurrent reads and writes.
- Manage checkpointing explicitly (e.g., PASSIVE or RESTART) to prevent WAL file growth and latency spikes.
- Use PRAGMA busy_timeout (e.g., 5000ms) to handle SQLITE_BUSY errors gracefully with exponential backoff.
- Begin write transactions with BEGIN IMMEDIATE to avoid deadlocks and lock escalation issues.
- Optimize memory with cache_size (e.g., -64000 for 64MB) and mmap_size (e.g., 2GB) for memory-mapped I/O.
- Custom VFS layers (e.g., Litestream, LiteFS) enable replication and durability in cloud environments.
- A production-ready configuration includes WAL, NORMAL synchronous, busy timeout, cache size, mmap, foreign keys, journal size limit, and incremental auto-vacuum.
- SQLite is suitable for read-heavy workloads under a few hundred GB, offering high performance and simplicity.
How to make forbidden changes to SQLite tables
2 months ago
- Some SQLite table changes require more than a simple ALTER TABLE command, such as adding ON DELETE CASCADE to a foreign key.
- A workaround is to create a new table with the correct structure, copy data from the old table, rename the old table, rename the new table, and then drop the old table if everything is fine.
- The process is demonstrated with SQL commands: CREATE TABLE, INSERT INTO, ALTER TABLE RENAME, and DROP TABLE.
- This method avoids hacky solutions and ensures data integrity.
What to do about SQLITE_BUSY errors despite setting a timeout - Bert Hubert's writings
2 months ago
- SQLite's WAL mode allows concurrent reads and writes, but transactions that upgrade from read-only to read-write can cause immediate SQLITE_BUSY errors regardless of timeout settings.
- To avoid SQLITE_BUSY errors, use 'BEGIN IMMEDIATE' or start transactions with a write statement to prevent upgrades, as upgrades fail immediately when another write transaction is active.
- SQLITE_BUSY errors from transaction upgrades are a fundamental issue with serializable isolation, also present in PostgreSQL and other databases, requiring careful transaction management.
- Strategies to mitigate serialization failures include declaring transactions as READ ONLY, controlling active connections via pooling, minimizing transaction scope, and avoiding idle transactions.
- Alternative approaches to prevent SQLITE_BUSY errors are starting writes immediately, using 'BEGIN IMMEDIATE', avoiding multi-statement transactions, or sequentially opening connections to avoid SQLITE_BUSY_RECOVERY
Stevens: a hackable AI assistant using a single SQLite table and a handful of cron jobs
2 months ago
- Stevens is a hackable AI assistant built using a simple architecture: a single SQLite table for memories and a handful of cron jobs, hosted on Val.town.
- Every morning, Stevens sends a daily brief via Telegram including calendar schedules, weather forecast, postal mail updates, and reminders, written in a formal butler-like tone.
- Users can interact with Stevens on-demand via Telegram, email, or by forwarding information, which is stored as log entries in the notebook (SQLite table).
- The memory system is extensible: importers (e.g., Google Calendar API, weather API, USPS OCR via Claude) populate the table with arbitrary text, fed into an LLM later.
- Reflections highlight the value of broader context beyond silos, starting simple with memory (time-bounded queries), and the fun of 'vibe coding' for personalization.
Tool: SQLite Query Explainer
2 months ago
- The SQLite Query Explainer tool runs SQL queries in the browser and annotates both EXPLAIN QUERY PLAN and low-level EXPLAIN bytecode outputs with plain-English descriptions.
- It is built using Python in Pyodide in WebAssembly, allowing SQLite to run entirely in the browser.
- The author acknowledges limited expertise in verifying the accuracy of the query plan explanations, describing the results as 'cromulent enough'.
Stinkpot: SQLite-Backed Shell History
2 months ago
- stinkpot is a minimal SQLite-backed shell-history searcher for bash, inspired by atuin but much smaller.
- It is written in Go (about 400 lines) and focuses only on session-agnostic history management and a TUI searcher.
- It can be installed via Nix home-manager or by evaluating an init script in .bashrc.
- Existing bash history can be imported with 'stinkpot import', and reverse search is triggered by Ctrl+R.
- The history database is stored in ~/.local/share/stinkpot; if upgrades break it, simply delete and reimport.
- The project includes performance improvements, using a covering index in SQLite for faster reverse search.
Learning a few things about running SQLite
2 months ago
- Using SQLite in production for small websites is viable but requires learning about database operations.
- Running ANALYZE on SQLite databases significantly improves query performance, as seen when a query dropped from 5 seconds to 0.05 seconds.
- Cleanup operations like deleting rows can cause timeouts and crashes if they take too long, so handling them in small batches or during maintenance is recommended.
- Django ORM queries generally perform well without optimization for small databases (e.g., 10,000 rows).
- SQLite backups can be done using tools like Restic for full backups or Litestream for incremental backups, with considerations for memory usage and efficiency.
- Splitting tables into multiple SQLite database files can be beneficial for some projects, as demonstrated with Mess with DNS.
- Learning basic SQLite features, such as ANALYZE, can take time, even after years of use.
SQLite: The Advantages of Flexible Typing
2 months ago
- SQLite's flexible type system allows storing any data type in any column regardless of column definition, promoting adaptability and simplifying schema design.
- Flexible typing supports various real-world use cases, such as key-value storage, handling mixed or dirty data (e.g., CSV files), and seamless evolution of data types over time without migrations.
- The feature enhances interoperability with other SQL databases by accepting diverse type names and is beneficial for dynamic programming languages like TCL, aligning with SQLite's original design goals.
- Arguments against flexible typing often stem from unfamiliarity or the belief that strict type enforcement prevents bugs, but SQLite's single 'sqlite3_value' superclass reduces the need for such enforcement in high-level abstractions.
- SQLite offers STRICT tables for rigid type enforcement if desired, but after years, no evidence shows rigid types preventing bugs, while flexibility aids long-term resilience, as illustrated by the Fossil version control system's SHA hash migration example.
Nul Characters in Strings in SQLite
2 months ago
- SQLite supports storing NUL characters within string values, but this can cause unexpected behavior, such as functions like length() and quote() only processing up to the first NUL.
- The CLI's .dump command and output omit text after the first NUL character, which can be misleading as seen in an example where a string appears shorter than it actually is.
- To detect embedded NULs, use the instr() function, and to remove them along with following text, apply an UPDATE statement with substr() and instr().
SQLite should have (Rust-style) editions
2 months ago
- SQLite is a library-based relational database management system (RDBMS) widely used in embedded and server projects, but has problematic defaults.
- Foreign key constraints are ignored by default in SQLite, unlike other RDBMSes, leading to potential data consistency issues and dangling references.
- SQLite's loose typing system allows columns to store incorrect data types by default, though strict tables can enforce type safety with the 'strict' keyword.
- Concurrent writers in SQLite result in immediate SQLITE_BUSY errors by default, but can be configured with 'busy_timeout' to wait for locks.
- Performance defaults are suboptimal, with Write-Ahead Log (WAL) disabled; enabling it along with 'synchronous = NORMAL' improves write speed and reduces disk syncs.
- The author proposes introducing a 'super pragma' based on Rust-style editions, such as 'PRAGMA edition = 2026', to bundle improved defaults without breaking backward compatibility.
SQLite Is All You Need
2 months ago
- SQLite with WAL mode can handle a social network of 50,000 users, 1 million posts, and millions of interactions in a single 343MB file.
- The heaviest query, a timeline join with counts, served 3,654 requests per second on a laptop, equivalent to 315 million requests per day.
- WAL mode eliminates SQLITE_BUSY errors and allows concurrent reads and writes, unlike the older rollback journal which causes significant slowdowns.
- Limitations include: reads scale poorly with concurrent writes due to cache invalidation, only one global writer, no built-in failover, and it's unsuitable for high-write contention or extensive analytics.
- On a cheap VPS, a single core can still serve over 110 million timeline requests per day, making SQLite feasible for most applications.
- Recommendations: use STRICT tables for type safety, prioritize single-core speed and enough RAM for the database, avoid network storage, and consider dedicated cores for better p99 latency.
- Development benefits include: local database as a file, easy testing with isolated databases, simple deployments, and minimal operational overhead.
- Backups are straightforward file copies, and tools like Litestream enable continuous replication.
- Bun with bun:sqlite performed better on lightweight endpoints but worse on heavy queries compared to Node with better-sqlite3, emphasizing the need to measure specific workloads.
- The article argues that premature scaling with complex databases like Postgres is often unnecessary; SQLite suffices for most projects until clear scaling signals emerge.
lobste.rs is now running on SQLite
2 months ago
- Lobste.rs migrated from MariaDB to SQLite after planning since 2018, initially targeting PostgreSQL but switching to SQLite last year.
- The migration was completed recently and is now considered stable, with improvements in CPU usage, memory usage, performance, and reduced VPS costs.
- The Lobsters Rails app runs on a single VPS with multiple SQLite database files, including a 3.8GB primary database and smaller cache, queue, and security databases.
- The migration involved significant code changes detailed in a PR by Thomas Dziedzic, building on previous work, and serves as a case study for using SQLite in 2026.
Lobste.rs is now running on SQLite
2 months ago
- SQLite deployment completed successfully with improved performance and reduced costs.
- Migration process involved multiple PR attempts and performance fixes.
- Lessons learned include SQLite's concurrency model and backup strategies.
- Community collaboration and healthy expectations contributed to the project's success.
- SQLite chosen over PostgreSQL for simplicity and meeting project needs.
Lobste.rs is now running on SQLite
2 months ago
- SQLite migration was successfully deployed on Saturday, with positive results including reduced CPU and memory usage, faster site performance, and lower VPS costs.
- The migration project was initiated due to concerns about MariaDB's future after K1's acquisition, with initial plans to move to PostgreSQL but ultimately choosing SQLite for pragmatism and simplicity.
- Multiple pull request attempts were made, with a failed first deploy due to performance issues from full table scans and n+1 queries, which were fixed in subsequent attempts.
- A successful second deploy occurred on July 11th, after thorough testing and fixes, with the site handling Monday traffic spikes well and minor issues promptly addressed.
- Key lessons included SQLite's concurrency model (multiple readers, single writer via WAL mode), the importance of performance tuning, and the benefits of using simpler solutions like SQLite over more complex databases like PostgreSQL.
- Backup strategy involves a nightly job using restic, and deployment uses Hatchbox with Capistrano for zero-downtime updates, leveraging SQLite's local file system and WAL mode for concurrent access.
- Community contributions were acknowledged, and the migration is seen as a step toward ensuring the site's long-term sustainability, with SQLite proving suitable for the site's needs despite initial skepticism about concurrency.
Prefer Strict Tables in SQLite
2 months ago
- SQLite strict tables enforce rigid typing to prevent type mismatches on insert/update.
- Strict tables only allow valid column types (INT, INTEGER, REAL, TEXT, BLOB, ANY) and require explicit column definitions.
- Strict tables cannot be applied to existing tables; migration involves copying data to a new strict table.
- The ANY datatype in strict tables allows flexibility for any value type.
- Strict tables were introduced in SQLite 3.37.0 (November 2021) and may not be compatible with older versions.
SQLite Is Dynamically Typed (2020)
3 months ago
- SQLite columns are flexibly typed, allowing any value in any column.
- Columns have a 'type affinity', and SQLite performs implicit conversions, like turning numeric strings into integers.
- The NULL type remains special, and NOT NULL constraints are enforced, unlike other flexible typing aspects.
- SQLite can store mismatched types (e.g., writing bytes as-is) when conversions aren't possible, unlike many databases that might reject such data.
SQLite-utils 4.0, now with database schema migrations
3 months ago
- sqlite-utils 4.0 introduces database schema migrations, nested transactions via db.atomic(), and support for compound foreign keys.
- Migrations allow defining sequential changes to SQLite databases with tracking via a _sqlite_migrations table, using Python files and the table.transform() method.
- Key breaking changes include upserts using SQLite's ON CONFLICT syntax, db.query() now requiring db.execute() for writes, and CSV imports detecting column types by default.
- The release integrates the previously separate sqlite-migrate package, making migrations a core feature of the sqlite-utils/Datasette/LLM ecosystem.
- Claude Fable 5 significantly assisted in identifying bugs and improving API design, contributing to a higher-quality release with comprehensive documentation generated by AI models.
Avoiding SQLite Database Locks in Production
3 months ago
- The article discusses encountering SQLite3::BusyException in a Rails app using Solid Queue due to SQLite's single-writer locking mechanism.
- Key solutions include proper SQLite configuration with busy_timeout and timeout set to the same value, enabling WAL mode, and optimizing pragmas like synchronous and mmap_size.
- A misconfiguration in deploy.yml (SOLID_QUEUE_IN_PUMA env var) caused Solid Queue to run twice, leading to lock issues; fixing it resolved the problem.
- Recurring jobs in Solid Queue should be scheduled to avoid simultaneous execution to identify lock causes through logged exception times.
- Avoid using .find_each for batch updates in jobs as it holds a read cursor open; instead, pluck IDs and process in small batches to release locks quickly.
- An opencode skill was created to prevent SQLite lock issues by guiding AI agents to avoid read cursors during writes and batch writes in transactions.

firstprev0next

sqlite

SQLite Critical CVEs or LLM Slop?

Choose DuckDB rather than SQLite

SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

How to make forbidden changes to SQLite tables

What to do about SQLITE_BUSY errors despite setting a timeout - Bert Hubert's writings

Stevens: a hackable AI assistant using a single SQLite table and a handful of cron jobs

Tool: SQLite Query Explainer

Stinkpot: SQLite-Backed Shell History

Learning a few things about running SQLite

SQLite: The Advantages of Flexible Typing

Nul Characters in Strings in SQLite

SQLite should have (Rust-style) editions

SQLite Is All You Need

lobste.rs is now running on SQLite

Lobste.rs is now running on SQLite

Lobste.rs is now running on SQLite

Prefer Strict Tables in SQLite

SQLite Is Dynamically Typed (2020)

SQLite-utils 4.0, now with database schema migrations

Avoiding SQLite Database Locks in Production