Bilingual

How to corrupt an SQLite database file
a month ago
- SQLite databases are highly resistant to corruption, with automatic rollback on crashes, but they are not immune.
- Corruption can occur due to file descriptor misuse, such as overwriting database files with unrelated data.
- Background backup processes copying during transactions can create corrupt backups; safe methods include sqlite3_rsync, VACUUM INTO, and the backup API.
- Moving, deleting, or renaming hot journal files (like *-journal or *-wal) after a crash prevents recovery and may corrupt the database.
- File locking issues, especially on network filesystems or with POSIX advisory locking quirks, can lead to corruption if multiple processes write concurrently.
- Using multiple links (hard or symbolic) to a database file can cause undefined behavior and potential corruption.
- Forking a process with an open SQLite connection and using it in the child can result in locking problems and corruption.
- Storage devices lying about sync operations (e.g., USB sticks) may cause out-of-order writes, leading to corruption, mitigated by using WAL mode.
- Disabling SQLite protections like PRAGMA synchronous=OFF increases corruption risk during crashes or power failures.
- Bugs in SQLite, though rare, have historically caused corruption, such as the WAL-reset bug and issues with expression indexes.
SQLite improving performance with pre-sort
a month ago
- UUID4's randomness slows down insert speeds, similar to random data like 20-byte session tokens, due to B+ Tree page thrashing.
- Presorting random data before insertion improves SQLite performance by 2-3x by leveraging sequential writes.
- A custom byte comparison function converts first 8 bytes of BLOBs to unsigned longs for fast sorting, matching SQLite's memcmp().
- Batching data allows optimizations like presorting, making it useful for unordered data in performance-critical scenarios.
Hunting a 16-year-old SQLite WAL bug with TLA+
25 days ago
- SQLite recently fixed a 16-year-old bug in its Write Ahead Log (WAL) checkpointing mechanism that could cause database corruption.
- The bug involved a race condition between appending new pages to the WAL and checkpointing, where a writer could reset the WAL while a checkpoint was in progress, leading to data loss.
- Using TLA+, the dqlite team modeled SQLite's behavior to understand the bug's sequence and then created a separate model for dqlite to check if it was affected.
- Dqlite's design prevents the bug because it uses stricter locking: checkpoints are stop-the-world operations that take the write lock, eliminating the concurrency that causes the race condition in SQLite.
- SQLite's fix adds a check during checkpointing to verify that the WAL's salt value hasn't changed since the checkpoint started, preventing the data race.
Avoiding SQLite Database Locks in Production
20 days 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.
SQLite-utils 4.0, now with database schema migrations
18 days 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.
SQLite Is Dynamically Typed (2020)
17 days 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.
Prefer Strict Tables in SQLite
14 days 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.
Lobste.rs is now running on SQLite
12 days 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.
Lobste.rs is now running on SQLite
12 days 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
10 days 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.
SQLite Is All You Need
11 days 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.
SQLite should have (Rust-style) editions
10 days 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.
Nul Characters in Strings in SQLite
10 days 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: The Advantages of Flexible Typing
10 days 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.
Learning a few things about running SQLite
8 days 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.
Stinkpot: SQLite-Backed Shell History
a day 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.

sqlite

How to corrupt an SQLite database file

SQLite improving performance with pre-sort

Hunting a 16-year-old SQLite WAL bug with TLA+

Avoiding SQLite Database Locks in Production

SQLite-utils 4.0, now with database schema migrations

SQLite Is Dynamically Typed (2020)

Prefer Strict Tables in SQLite

Lobste.rs is now running on SQLite

Lobste.rs is now running on SQLite

lobste.rs is now running on SQLite

SQLite Is All You Need

SQLite should have (Rust-style) editions

Nul Characters in Strings in SQLite

SQLite: The Advantages of Flexible Typing

Learning a few things about running SQLite

Stinkpot: SQLite-Backed Shell History