One-liner for running queries against CSV files with SQLite

One-liner for running queries against CSV files with SQLite
til.simonwillison.net

Slightly tangentially, when doing aggregated queries, SQLite has a very useful group_concat(..., ',') function that will concatenate the expression in the first arg for each row in the group, separated by the separator in the 2nd arg.

In many situations SQLite is a suitable alternative to jq for simple tabular JSON.

  sqlite3 :memory: -cmd '.mode csv' ...

For instance, if the user query is:

SELECT COUNT(*) FROM my_vtab;

... the query your virtual table will effectively see is:

SELECT * FROM my_vtab;

SQLite does the counting. That's great, unless you already know the count and could have reported it directly rather than actually returning every row in the table. You're forced to retrieve and return every row because you have no idea that it was actually just a count.

As another example, if the user query includes a join, you won't see the join. Instead, you will receive a series of N queries for individual IDs, even if you could have more efficiently retrieved them in a batch.

The join one is particularly nasty. If you're writing a virtual table that accesses a remote resource with some latency, any join will absolutely ruin your performance as you pay a full network roundtrip for each of those N queries.

I wrote a module that exposes remote SQL Server/PostgreSQL/MySQL servers as SQLite virtual tables, and joins basically don't work at all if your server is not on your local network. There's nothing I can do about it (other than heuristically guessing what IDs might be coming and request them ahead of time) because SQLite doesn't provide enough information to the virtual table layer. It's my understanding that PostgreSQL's foreign data wrappers (a similar feature to SQLite's virtual tables) push much more information about the query down to the wrapper layer, but I haven't used it myself.