Installing and using SQLite extensions on macOs (and maybe Windows & Linux too)

Installing and using SQLite extensions on macOs

SQLite is brilliant and … lite. Deliberately. Even for maths functions the view is “it's really, really easy to add extensions and we don't want to bloat the core.”

This is fine if you are used to C development. This page is for if you aren't. The first section is specific to macOs, which is the “hardest” case. Linux and Windows are easier and you can skip the first section.

1. For macOs: Be able to load SQLite extensions

1. Install SQLite from homebrew, because the apple-shipped SQLite will probably not allow you to load extensions. If you try to load an extension, you will just get a "not authorized" error.

   brew install sqlite

   Note that homebrew tells you that it has not put sqlite in the path because the apple-shipped version is in the path. Fix this either by editing your profile file to extend the path, or else by adding a link to the updated sqlite3 in /usr/local/bin:

   ln -s /usr/local/opt/sqlite/bin/sqlite3 /usr/local/bin/

2. Now if you start sqlite3 you should see a new improved version number:

   SQLite version 3.33.0 2020-08-14 13:23:32
   Enter ".help" for usage hints.
   

2. Download a Loadable Module

1. We'll take Spatialite as a great example. Get the .dylib file (macOs) or .dll (for Windows or Linux) or .so file (for Linux) for your extension and confirm it is somewhere you can find it. For Windows the homepage has links to 7z archive file containing the loadable module and sqlite.exe too. For macOs:

   brew install libspatialite
   # ... lots of homebrew output ...
   
   ls /usr/local/lib/*spatialite*
   # /usr/local/lib/libspatialite.dylib
   # /usr/local/lib/mod_spatialite.dylib
   # /usr/local/lib/libspatialite.7.dylib
   # /usr/local/lib/mod_spatialite.7.dylib
   

2. Add the directory /usr/local/lib to your LD_PATH if it isn't already there. (The alternative to this step is, in the next step, to use the absolute path to load the module.)
   • echo $LD_PATH #Check if you already have it
   • export LD_PATH="$LD_PATH:/usr/local/lib"
   • Edit your profile file to make the change repeatable. For instance:
   • zsh : echo 'export LD_PATH="$LD_PATH:/usr/local/lib"' >> ~/.zshrc
   • bash: echo 'export LD_PATH="$LD_PATH:/usr/local/lib"' >> ~/.bash_profile
   • fish: echo 'set -x LD_PATH "$LD_PATH:/usr/local/lib"' >> ~/.config/fish/config.fish
3. Start sqlite again and now load the module. There are two ways to do it, either should work:
   • Either with .load :
     .load mod_spatialite.dylib #*if you set LD_PATH above*
     .load /full/path/to/the/file/mod_spatialite.dylib # *if you didn't*
   • Or with Select:

     Select load_extension('mod_spatialite');

   Either way you should now be able to select the spatialite version number:

   select spatialite_version() ;
   # 4.3.0a
   

3. Other Extensions

Spatialite was the easy example because there are pre-compiled binaries available for all platforms. Other extensions mostly exist as .c files. But good news! Many of them are single files and easy to compile and install.

1. Download some extensions, usually as a single .c file

   For instance, look at https://www.sqlite.org/contrib and notice extension-functions.c at the bottom of the page. Let's install this, which has common maths, string and stats functions functions such as power(), exp(), reverse(), stdev() :

2. Having downloaded the .c file, compile it in your download directory.

   For macOs:

   gcc -g -fPIC -dynamiclib extension-functions.c -o extension-functions.dylib
   

   For Windows, use one of:

   gcc -g -shared YourCode.c -o YourCode.dll
   cl YourCode.c -link -dll -out:YourCode.dll
   

   For linux/*nix:

   gcc -g -fPIC -shared YourCode.c -o YourCode.so
   

3. Copy it to your lib directory and use it. e.g. for macOs:

   mv extension-functions.dylib /usr/local/lib/
   sqlite3
   > .load extension-functions.dylib
   Select sqrt(10), log(exp(2)) ;
   # sqrt(10)          log( exp(2) )
   # ----------------  -------------
   # 3.16227766016838  2.0
   

Even more extensions

There are more extensions in the SQLite repository which you can download and install from the .c file in the same way. https://sqlite.org/src/file/ext/misc/ includes files for json & csv formatting, regex, uuids, and other requirements.

If you have a not-latest version of SQLite installed, you may need the advice on Forums - How do I compile the newest .... I ended up with:

for f in *.c
  set b (basename -s .c $f)
  gcc -g -fPIC  -DSQLITE_INNOCUOUS=0 -DSQLITE_VTAB_INNOCUOUS=0  -DSQLITE_VTAB_DIRECTONLY=0  -DSQLITE_SUBTYPE=0 -dynamiclib $f -o $b.dylib
  rm -r $b.dylib.dSYM
end

4. Making it permanent-ish

You can use the file ~/.sqliterc to permanently include your loaded functions. Here's mine:

.headers ON
.mode column
.load extension-functions.dylib

For the same functionality on another machine, you must replicate these steps. The sqlite developers' solution would be, compile your own distribution of sqlite with all the bits you want.

References

• julifos.wordpress.com - mac-sqlite3-command-line-one-liner-load-extension
• https://www.sqlite.org/loadext.html#build
• https://sqlite.org/src/file/ext/misc/
• https://www.sqlite.org/loadext.html
• Forums - How do I compile the newest ...
• medium.com/@carusot42/installing-and-loading-spatialite-on-macos-28bf677f0436