Hello! Welcome to the first post in my new SQLite blog.
As a reminder - this is a newsletter created by Alex Garcia about SQLite - new tools, guides, best practices, and other stuff. There is at most 1 email per week, with many gaps in between.
This week: a new tool for SQLite! sqlite-lines is a SQLite extension for reading lines from a file. While a simple feature on it’s surface, reading data line-by-line is not possible using built-in SQLite functions, and unlocks the ability to import several new datasets into SQLite databases.
.load ./lines0 select line from lines_read('./logs.txt');
For example, if you want to count the total number of strokes in all calendar drawings in Google’s Quick, Draw! dataset, you can run:
select sum(json_array_length(line, '$.drawing')) as num_strokes from lines_read('../_data/calendar.ndjson');
It’s distributed as a runtime-loadable SQLite extension, which you can download pre-built binaries for on the Releases page (or build yourself if you’re adventerous).
.load ./lines0 select lines_version();
That’s it for today! Here’s a preview for what’s to come in the next few weeks:
sqlite-http, a SQLite extension for making HTTP requests in SQLite
sqlite-html, a SQLite extension for querying, manipulating, and building HTML elements
And as always, if you ever want to chat about SQLite tooling or have questions, feel free to email me, DM me on Twitter, or file an issue in the Github repo!