I run sqlite3 regularly on both Linux and Macos. Over the last couple of days, I have been trying to get a custom extension to load in sqlite3 using the .load command.

The sqlite3 shipped by Apple does not allow extensions to be loaded. As with many Mac users, I use Homebrew as a package manager which currently loads sqlite3 3.39.2. When installed, Homebrew displays the message:

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

For compilers to find sqlite you may need to set:
  export LDFLAGS="-L/usr/local/opt/sqlite/lib"
  export CPPFLAGS="-I/usr/local/opt/sqlite/include"

For pkg-config to find sqlite you may need to set:
  export PKG_CONFIG_PATH="/usr/local/opt/sqlite/lib/pkgconfig"

The compile line for Macos displayed on the Run-Time Loadable Extensions is:

gcc -g -fPIC -dynamiclib YourCode.c -o YourCode.dylib

This will not work at all as of Macos 12.4 Monterey. It fails with:

Undefined symbols for architecture x86_64:
  "_main", referenced from:
     implicit entry/start for main executable
ld: symbol(s) not found for architecture x86_64
clang: error: linker command failed with exit code 1 (use -v to see invocation)

The fix for this error that I used is:

gcc -shared -g -fPIC -dynamiclib YourCode.c -o YourCode.dylib

This will result in a successful compile; however the library is compiled to run with the sqlite3 version shipped by Apple. Since extensions are not enabled, this code will not work and will fail to initialize if attempted to be loaded in the Homebrew 3.39.2 version of sqlite3.

The fix needed to address that is:

gcc -shared -g -fPIC -dynamiclib YourCode.c -o YourCode.dylib -L/usr/local/opt/sqlite-autoconf/lib -I/usr/local/opt/sqlite-autoconf/include

I believe that Run-Time Loadable Extensions should be updated to include

• sqlite3 shipped with Macos does not have extensions enabled. A custom install will be needed
• add -shared to the compile command
• when using package managers such as Homebrew, Macports, Finjk ..., include explicit -Llib and -Iinclude in the gcc command

To all of the sqlite3 devs and contributors, Thank You very much!! I love sqlite3 and use it very often!!

lbe