Trying out SQLite extensions on macOS

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-lines, written in C, which adds lines(text, optional-delimiter) and lines_read(filepath, optional-delimiter) table-valued functions, for processing files line-by-line.
sqlite-html, written in Go, which provides a whole family of functions for parsing and constructing HTML strings.

Both of these have interactive demos, which you can try out in your browser in Alex's Observable notebooks:

Getting them to run on a macOS laptop is harder. Here's how I got them to work.

Don't use the sqlite3 command that came with macOS

The sqlite3 command that comes built in to macOS has a frustrating limitation: it has been compiled without the ability to load new extensions.

You can confirm this by running the following:

sqlite3 :memory: 'select * from pragma_compile_options()'

On my machine part of the ouput from this says OMIT_LOAD_EXTENSION.

You can run which sqlite3 to see where it is located - on my system that outputs /usr/bin/sqlite3.

If you use Homebrew you can run brew install sqlite - this will install a modern version of SQLite, but it won't link it into your path (it's a "keg-only package" in Homebrew jargon) to avoid conflicting with the macOS default installation. Running brew info sqlite confirms this.

% brew info sqlite 
sqlite: stable 3.39.2 (bottled) [keg-only]
Command-line interface for SQLite
https://sqlite.org/index.html
/usr/local/Cellar/sqlite/3.39.2 (11 files, 4.4MB)
  Poured from bottle on 2022-07-24 at 14:46:49
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/sqlite.rb
License: blessing
==> Dependencies
Required: readline ✔
==> Caveats
sqlite is keg-only, which means it was not symlinked into /usr/local,
because macOS already provides this software and installing another version in
parallel can cause all kinds of trouble.

If you need to have sqlite first in your PATH, run:
  echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> ~/.zshrc
...

So the command is installed but is not on your path. It lives at /usr/local/opt/sqlite/bin/sqlite3 - so you can run this version using that full path.

Running this confirms that it doesn't have that OMIT_LOAD_EXTENSION option:

/usr/local/opt/sqlite/bin/sqlite3 :memory: \
  'select * from pragma_compile_options()'

(It also reveals some exciting extra extensions: ENABLE_GEOPOLY and ENABLE_RTREE are both listed there.)

You can also download a precompiled SQLite binary for macOS from the SQLite downloads page - though this isn't signed, so you'll need to follow the steps described next to get it to run.

Download the .dylib files using wget

I figured this out after first writing this TIL. If you download a .dylib extension using wget it will work straight away:

% cd /tmp
/tmp % wget https://github.com/asg017/sqlite-lines/releases/download/v0.1.1/lines0.dylib
...
Saving to: ‘lines0.dylib’
/tmp % /usr/local/opt/sqlite/bin/sqlite3
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load lines0
sqlite> .mode column
sqlite> select
  line ->> 'color' as color, 
  sum(line ->> 'value') as sum
from lines('{"color":"red","value":56}
{"color":"red","value":79}
{"color":"blue","value":52}
{"color":"blue","value":15}') group by color;
color  sum
-----  ---
blue   67 
red    135

And for sqlite-html

Grab the html0.dylib extension from Alex's releases page, again using wget:

% cd /tmp
% wget https://github.com/asg017/sqlite-html/releases/download/v0.1.0/html0.dylib
 % /usr/local/opt/sqlite/bin/sqlite3
sqlite> .load html0.dylib
sqlite> select name from pragma_function_list where name like 'html_%';
html_valid
html_count
html_text
html_text
html_group_element_div
html_attr_get
html_attribute_has
html_group_element_span
html_attr_has
html_element
html_trim
html_table
html_attribute_get
html_extract
html_version
html_debug
html_escape
html_unescape
sqlite> .mode column
sqlite> select * from html_each('<ul>
<li>Alpha</li>
<li>Bravo</li>
<li>Charlie</li>
<li>Delta</li>', 'li');
html              text   
----------------  -------
<li>Alpha</li>    Alpha  
<li>Bravo</li>    Bravo  
<li>Charlie</li>  Charlie
<li>Delta</li>    Delta

If you download with your browser you'll have to jump through more hoops

I first tried downloading the lines0.dylib file from the 0.1.1 release page.

The first time I tried loading the extension I got this error:

% /usr/local/opt/sqlite/bin/sqlite3
sqlite> .load /Users/simon/Downloads/lines0.dylib
Error: dlopen(/Users/simon/Downloads/lines0.dylib.dylib, 0x000A): tried: '/Users/simon/Downloads/lines0.dylib.dylib' (no such file), '/usr/local/lib/lines0.dylib.dylib' (no such file), '/usr/lib/lines0.dylib.dylib' (no such file)

macOS popped up up a helpful dialog box saing what went wrong:

This is because the code hasn't been signed. You can still open it though - the trick is to head over to the macOS Security tab in System Preferences:

Click "Allow Anyway", then try running the .load command again. Click "Open" one more time in this dialog:

And the extension will load from now on!

And for Python

See Loading SQLite extensions in Python on macOS.

Created 2022-08-03T10:31:02-07:00, updated 2023-10-27T13:51:34-07:00 · History · Edit