sqlite-lines - A SQLite extension for reading files line-by-line
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');
Best of all: combining sqlite-lines and SQLite's excellent JSON support gives you one of the fastest NDJSON/JSON Lines tools out there!
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');
Parsing this 185MB NDJSON file with 321k objects takes a little over a second! This is much, much faster than over conventional NDJSON tools like Pandas, sqlite-utils and ndjson-cli.
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();
Check out the full blog post to see sqlite-lines as a WASM/JavaScript library in your browser, and to learn more about its codebase!
That's it for today! Here's a preview for what's to come in the next few weeks:
- New tool:
sqlite-http, a SQLite extension for making HTTP requests in SQLite - New tool:
sqlite-html, a SQLite extension for querying, manipulating, and building HTML elements - Guide: Building SQLite extensions in Go
If these emails are getting repetitive, feel free to follow me on Twitter for updates, or follow the RSS feed for this series!
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!