Simon Willison: TILs on sqlite

sqlite Returning related rows in a single SQL query using JSON - 2022-09-16

When building database-backed applications you'll often find yourself wanting to return a row from the database along with its related rows. …

sqlite SQLite VACUUM: database or disk is full - 2022-08-29

I was trying to run VACUUM against a large SQLite database file (~7GB) using sqlite-utils vacuum data.db and I got this error: …

sqlite Seeing which functions are unique to a specific SQLite / Datasette instance - 2022-08-23

In reading Scraping JSON, HTML, and ZIP Files with Pure SQLite by Alex Garcia I got curious to see a full list of functions he had registered in his sqlite-extension-examples.fly.dev Datasette instance that weren't available in a regular Datasette. …

sqlite Sort by number of JSON intersections - 2022-08-17

This post on Reddit asked how to run a query that takes a list of items (in this case ingredients) as the input and returns all rows with at least one of those items in a JSON list, ordered by the most matches. …

sqlite Trying out SQLite extensions on macOS - 2022-08-03

Alex Garcia has been building some really cool new custom extensions for SQLite, working in C and Go. So far he's released two: …

sqlite Related content with SQLite FTS and a Datasette template function - 2022-07-31

Today I added "related TILs" to this TIL website - so each TIL now shows five related TILs at the bottom of the page. …

sqlite One-liner for running queries against CSV files with SQLite - 2022-06-20

I figured out how to run a SQL query directly against a CSV file using the sqlite3 command-line utility: …

sqlite Counting SQLite virtual machine operations - 2022-03-20

When SQLite executes a query, it does so by executing a sequence of virtual machine operations. …

sqlite The simplest recursive CTE - 2022-03-20

I found this really simple recursive CTE useful for ensuring I understood how to write recursive CTEs. …

sqlite Combining substr and instr to extract text - 2022-02-15

Derek Willis has a Datasette instance full of political campaign emails running on Heroku. …

sqlite Ordered group_concat() in SQLite - 2022-02-06

I was trying to use group_concat() to glue together some column values into a stiched together Markdown document. My first attempt looked like this: …

sqlite json_extract() path syntax in SQLite - 2022-01-18

Several of the SQLite JSON functions, such as json_extract() and json_array_length(), take a path argument. This uses custom syntax along the lines of $.a[2].b, as described in the documentation here. …

sqlite Track timestamped changes to a SQLite table using triggers - 2021-08-19

This is more of a "today I figured out" than a TIL. …

sqlite Building a specific version of SQLite with pysqlite on macOS/Linux - 2021-08-14

I wanted the ability to test my Python software against specific version of SQLite on macOS. I found a way to do that using pysqlite3. …

sqlite SQLite aggregate filter clauses - 2021-08-04

SQLite supports aggregate filter clauses (as of 3.30.0, released 2019-10-04), as described in this SQL Pivot in all databases tutorial. …

sqlite Importing CSV data into SQLite with .import - 2021-07-13

I usually use my sqlite-utils insert blah.db tablename file.csv --csv command to import CSV data into SQLite, but for large CSV files (like a 750MB one) this can take quite a long time - over half an hour in this case. …

sqlite Using pysqlite3 on macOS - 2021-07-10

While trying to use pysqlite3 on macOS I got the following error: …

sqlite Querying for items stored in UTC that were created on a Thursday in PST - 2021-03-12

This came up as a question on Hacker News. How can you query a SQLite database for items that were created on a Thursday in PST, when the data is stored in UTC? …

sqlite Splitting on commas in SQLite - 2021-02-01

I had an input string in x,y,z format and I needed to split it into three separate values in SQLite. I managed to do it using a confusing combination of the instr() and substr() functions. …

sqlite Fixing broken text encodings with sqlite-transform and ftfy - 2021-01-18

I was working with a database table that included values that were clearly in the wrong character encoding - values like this: …

sqlite Identifying column combination patterns in a SQLite table - 2021-01-12

Given a large, heterogeneous table I wanted to identify patterns in the rows in terms of which columns were not null. …

sqlite Replicating SQLite with rqlite - 2020-12-28

I tried out rqlite today, a "lightweight, distributed relational database, which uses SQLite as its storage engine". It uses the Raft consensus algorithm to allow multiple SQLite instances to elect a leader and replicate changes amongst themselves. …

sqlite Figuring out if a text value in SQLite is a valid integer or float - 2020-09-27

Given a table with a TEXT column in SQLite I want to figure out if every value in that table is actually the text representation of an integer or floating point value, so I can decide if it's a good idea to change the type of the column (using sqlite-utils transform). …

sqlite Compiling the SQLite spellfix.c module on macOS - 2020-09-19

I wanted to browse a backup copy of my Plex database, which is a SQLite file. I tried this: …

sqlite Enabling WAL mode for SQLite database files - 2020-08-09

I was getting occasional Error: database is locked messages from a Datasette instance that was running against a bunch of different SQLite files that were updated by cron scripts (my personal Dogsheep). …

sqlite SQLite BLOB literals - 2020-07-29

I wanted to construct a string of SQL that would return a blob value: …

sqlite Using LD_PRELOAD to run any version of SQLite with Python - 2020-06-17

I've been wanting to figure this out for ages. This thread on Hacker News plus this Stackoverflow post gave me some initial clues. …

sqlite List all columns in a SQLite database - 2020-05-06

Here's a devious trick for listing ALL columns in a SQLite database, using a SQL query that generates another SQL query. …

sqlite Compile a new sqlite3 binary on Ubuntu - 2020-04-30

I wanted to try the vacuum into backup command that was released in SQLite3 3.27.0 on 2019-02-07. …

sqlite Null case comparisons in SQLite - 2020-04-21

I wanted to say "output this transformed value if it's not null, otherwise nothing". The recipe I figured out was: …

sqlite Lag window function in SQLite - 2020-04-19

Here's how to use a lag window function to calculate new cases per day when the table just has total cases over time on different dates. …