CSV
Datasette for Publishing CSV as a Searchable Site in Minutes
Turn a CSV into a browsable, searchable, filterable website with sqlite-utils and Datasette, with a JSON and CSV API, facets and full-text search built in.
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.
CSV
Using sqlite utils to Load CSV Fast Into a SQLite Database
Load a CSV into SQLite with one sqlite-utils command, set a primary key, control type detection, add full-text search, and see what 2 million rows cost.
6 min read
CSV
Exporting a CSV With Headers From SQLite on the Command Line
Export SQLite to CSV with a header row from the sqlite3 shell: CRLF endings by default, NULL against empty string, types and 2,000,000 rows in 3.5 s.
10 min read
CSVGuide
Exporting Query Results to CSV From Five Major Databases
The same eight awkward rows exported from PostgreSQL, MySQL, SQLite, DuckDB and MongoDB, then read back: four tools return all 8, one recipe returns 4.
25 min read
YAML
Using DuckDB to Query Data Stored as YAML With read_yaml
Install DuckDB's yaml community extension and query YAML files with read_yaml: inferred types, nested structs, unnest, globs, mixed types and broken files.
6 min read
YAML
Flattening Nested YAML Into Rows of Path and Value
Turn a nested YAML document into path, type and value rows with a short recursive generator, then load the rows into SQL to search and compare configs.
6 min read
YAML
Reading YAML Into SQLite for Quick Queries With json_extract
Load a YAML list into an in-memory SQLite table as JSON text and query it with json_extract and json_each, with a json_valid check that rejects bad rows.
6 min read
JSON
Validating Text as JSON With json_valid in SQLite Tables
What json_valid accepts in SQLite with each flag, why a CHECK can pass a NULL, how to find bad rows, add a check to an existing table and check JSONB blobs.
7 min read
JSON
Storing Settings as JSON in SQLite and Updating One Key
A SQLite settings table with a JSON column: a CHECK that it is an object, json_set for one key, json_patch upserts, defaults on read, counters and a trigger.
8 min read
YAML
Fixtures in YAML for Database Tests With pytest and Rollback
Keep test data in YAML files, load it inside a transaction per test and roll back after, with pytest and PostgreSQL. Three tests ran in 0.13 seconds.
7 min read
JSON
Using the JSONB Format Added in SQLite 3.45 Safely
SQLite 3.45 added JSONB, a binary JSON blob. Convert a column, measure size and speed, learn the function pairs and the pitfalls with older drivers.
8 min read
YAML
Storing YAML Text in a Database Column and Its Pitfalls
What goes wrong when YAML is kept as text in a column: LIKE misses, equal documents compare unequal and garbage is accepted. The jsonb alternative fixes each.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.