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:

1
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.

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

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

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:

1
/usr/local/opt/sqlite/bin/sqlite3 :memory: \
2
  '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:

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

And for sqlite-html#

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

1
% cd /tmp
2
% wget https://github.com/asg017/sqlite-html/releases/download/v0.1.0/html0.dylib
3
 % /usr/local/opt/sqlite/bin/sqlite3
4
sqlite> .load html0.dylib
5
sqlite> select name from pragma_function_list where name like 'html_%';
6
html_valid
7
html_count
8
html_text
9
html_text
10
html_group_element_div
11
html_attr_get
12
html_attribute_has
13
html_group_element_span
14
html_attr_has
15
html_element
16
html_trim
17
html_table
18
html_attribute_get
19
html_extract
20
html_version
21
html_debug
22
html_escape
23
html_unescape
24
sqlite> .mode column
25
sqlite> select * from html_each('<ul>
26
<li>Alpha</li>
27
<li>Bravo</li>
28
<li>Charlie</li>
29
<li>Delta</li>', 'li');
30
html              text
31
----------------  -------
32
<li>Alpha</li>    Alpha
33
<li>Bravo</li>    Bravo
34
<li>Charlie</li>  Charlie
35
<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:

1
% /usr/local/opt/sqlite/bin/sqlite3
2
sqlite> .load /Users/simon/Downloads/lines0.dylib
3
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:

lines0.dylib cannot be opened because the developer cannot be verified. macOS cannot verify that this app is free from malware. Buttons: Move to Trash or Cancel

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:

The security panel now has an extra line saying "lines0.dylib was blocked from use because it is not from an identified developer" - with a Allow Anyway button.