JSON
Indexing JSON Fields in SQLite With Expression Indexes
Index a field inside a SQLite JSON document with an expression index or a generated column, and see which query spellings use it and what it costs.
7 min read
Topic
SQLite
An entire database in one file. Hacks for scripts, tests, mobile and the surprising number of production apps that never need more.
JSON
Building JSON Results With json_group_array in SQLite
Build nested JSON from SQLite tables with json_group_array, json_group_object and json_object, and avoid the lost-subtype bug that turns arrays into strings.
7 min read
YAML
Exporting a Table to YAML From PostgreSQL With Python
Export a PostgreSQL table to YAML with psycopg and PyYAML. Fix the UUID error, keep numerics exact as strings and compare with a to_jsonb pipeline.
7 min read
JSON
Expanding JSON Arrays With json_each and json_tree
Turn SQLite JSON arrays and objects into rows with json_each and walk whole documents with json_tree, with every column explained and the edge cases checked.
7 min read
YAML
Loading YAML Seed Data Into PostgreSQL With Python
Load a YAML list into PostgreSQL 18 with psycopg: adapt dates, arrays and jsonb, rerun safely with ON CONFLICT and let constraints reject bad rows.
6 min read
JSON
The SQLite Arrow Operators for JSON Explained With Examples
SQLite's -> and ->> operators read JSON like PostgreSQL and MySQL, with their own rules for bare keys, negative indexes, precedence and the types they return.
7 min read
JSON
Querying JSON Text With json_extract in SQLite Step by Step
What json_extract returns for each JSON type in SQLite, how paths and multiple paths behave, the number and text traps, and what repeated calls cost.
7 min read
YAMLGuide
Using YAML With Databases for Seeds Exports and Queries
Five jobs YAML does next to a database: seed data, exports, stored documents, test fixtures and queries, with tested code for PostgreSQL, SQLite and DuckDB.
19 min read
JSONGuide
JSON Functions in SQLite and What You Can Do With Them
A tested guide to SQLite's JSON functions: reading, arrows, editing, json_each, aggregates, JSONB, expression indexes and the type traps, on 200,000 rows.
18 min read
CSV
Why SQLite Stores Your CSV Numbers as Text and How to Fix It
SQLite .import stores every CSV value as text, so ORDER BY puts 10.25 before 2.75 and amount > 9 matches every row. See the cause and three ways to fix it.
8 min read
CSV
Turning a Folder of CSV Files Into a SQLite Database
One table per CSV file, with safe names and types inferred from the whole file. A short Python script that keeps ZIP codes as text and refuses ragged files.
9 min read
CSV
Loading Many CSV Files Into One SQLite Database in Bash
A bash loop that imports monthly CSV files into one SQLite table, records which file each row came from, and refuses to load a file twice.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.