Using alternative compilation of SQLite doesn't work

[fractaledmind]

I am trying to get a project-local, custom build of SQLite working with my Rails app. I have been studying and using the installation instructions, but I can't quite get things to work and I don't know what I'm doing wrong. I'm hoping someone here can help steer me in the right direction.

I have the following script which compiles a custom, optimized version of SQLite:

#!/usr/bin/env sh

cd ../vendor
mkdir -p sqlite/bin
mkdir -p sqlite/lib
mkdir -p sqlite/include

# ============================================================================================================
# Compile and install sqlite3 (for performance turning and build customizations)
# SEE: https://www.sqlite.org/compile.html
# NOTE: The sqlite3 Ruby gem will not work with the following compile time flags
#       * -DSQLITE_OMIT_DEPRECATED
# ============================================================================================================
curl --remote-name --remote-header-name https://www.sqlite.org/2023/sqlite-amalgamation-3430000.zip && unzip sqlite-amalgamation-3430000.zip

cd sqlite-amalgamation-3430000

PREPROCESSOR_FLAGS=(
  -DSQLITE_DEFAULT_MEMSTATUS=0
  -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1
  -DSQLITE_DQS=0
  -DSQLITE_ENABLE_FTS5
  -DSQLITE_LIKE_DOESNT_MATCH_BLOBS
  -DSQLITE_MAX_EXPR_DEPTH=0
  -DSQLITE_OMIT_PROGRESS_CALLBACK
  -DSQLITE_OMIT_SHARED_CACHE
  -DSQLITE_USE_ALLOCA
)

# compile the executable
gcc "${PREPROCESSOR_FLAGS[@]}" shell.c sqlite3.c -lpthread -ldl -lm -o ../sqlite/bin/sqlite3

# compile and setup shared library
gcc "${PREPROCESSOR_FLAGS[@]}" shell.c sqlite3.c -lpthread -ldl -lm -fPIC -shared -o ../sqlite/lib/libsqlite3.so && \
chmod 755 ../sqlite/lib/libsqlite3.so && \
ln -s ../sqlite/lib/libsqlite3.so ../sqlite/lib/libsqlite3.so.0

# copy header/include files
mkdir -p ../sqlite/include/sqlite3 && \
cp sqlite3.h ../sqlite/include/ && \
cp sqlite3ext.h ../sqlite/include/

# cleanup
PREPROCESSOR_FLAGS=""

This creates a vendor/sqlite directory in my Rails app with this structure:

sqlite
├── bin
│   └── sqlite3
├── include
│   ├── sqlite3.h
│   └── sqlite3ext.h
└── lib
    ├── libsqlite3.so
    └── libsqlite3.so.0 -> ../sqlite/lib/libsqlite3.so

4 directories, 5 files

I setup the Bundle config like so:

bundle config set build.sqlite3 --enable-system-libraries --with-opt-dir=./vendor/sqlite

which creates a .bundle/config file like so:

---
BUNDLE_BUILD__SQLITE3: "--enable-system-libraries --with-opt-dir=./vendor/sqlite"

In my Gemfile, I specify the dependency on SQLite like so:

gem "sqlite3", force_ruby_platform: true

However, whenever I use bin/rails c to start a console and run ActiveRecord::Base.connection.execute 'PRAGMA compile_options', I see the compile_options of my system SQLite installation at /usr/bin/sqlite3.

When I manually enter the compiled SQLite prompt via vendor/sqlite/bin/sqlite3 and run PRAGMA compile_options, I see the specific compile options that I set.

---

So, how can I get the SQLite installation that lives in vendor/sqlite to be the one that the gem uses in my Rails app?

[flavorjones]

@fractaledmind I wrote a bit about this topic in this discussion: #389

Specifically these comments:

• Has anyone compiled this gem with SEE (SQLite Encryption Extension)? #389 (reply in thread)
• Has anyone compiled this gem with SEE (SQLite Encryption Extension)? #389 (reply in thread)

Can you take a look and see if that's helpful? If so I should probably add an explanation to the README.

[flavorjones]

Ah, I already wrote it up in a markdown file!

https://github.com/sparklemotion/sqlite3-ruby/blob/master/INSTALLATION.md#how-do-i-use-an-alternative-sqlite3-implementation

That presumes you've downloaded the autoconf source package from https://www.sqlite.org/download.html and have built it using ./configure --prefix=/opt/whatever along with whatever compile-time options you need.

[fractaledmind]

This was the section I was following. I believe I have followed the details in that section of the INSTALLATION.md doc. The issue is that when I introspect my database from my Rails console via PRAGMA compile_options, the compile options I used to compile my SQLite aren't present. Calling that PRAGMA from within the sqlite3 terminal started by pointed at the executable I compiled in vendor/sqlite/bin does show the options I set, which leads me to believe that the gem is using a different SQLite executable

[flavorjones]

I'm realizing now that you may be asking about a different thing: how do you get your system to prefer loading this library from your custom sqlite instead of the system's?

Try setting the environment variable LD_LIBRARY_PATH=/path/to/rails/app/vendor/sqlite/lib

[fractaledmind]

Let me try. You are write, all I want to do is set compile options for SQLite. Is there any way to do this thru bundler such that this can be integrated into an app repo and not system level?

[flavorjones]

Well, you can ensure you get the source gem (not a precompiled gem) with:

# Gemfile
gem "sqlite3", force_ruby_platform: true # needs bundler 2.3.18 or higher

But the CFLAGS environment variable you'll have to set outside of bundler and rails. If you're building a docker image, I'd recommend doing it in the Dockerfile. Or else consider using direnv or something else that lets you use a .envrc file in the repository? Be careful with how you build your production images, though, that those environment variables take effect.

[flavorjones]

Thinking 🤔 We could add an extconf option to allow people to pass the cflags via the bundler config ... that would hit your use case. Something like

bundle config build.nokogiri --sqlite-cflags="-DSQLITE_DEFAULT_PAGE_SIZE=16300"

WDYT?

[fractaledmind]

Yea, I was trying to find the gem that I have a fuzzy memory of that allowed compilation options passed via a bundled config. But my dizzy memory looked like that.

FWIW, this discussion does show a clear benefit of Docker. I just want to try and find something that would work with a Hatchbox deployment, for example, without Docker. Because I think that opens things up to everyone.

So, short answer, if we could get the gem to support a builder config like the above, I think we would unlock a really cool feature (fine tuning your db for your app) for every Rails app regardless of deployment strategy, which would be insanely cool IMO

[flavorjones]

👍 see #401

[flavorjones]

Closing the loop on this, @fractaledmind and I spec'ed out the DX to allow developers to set compiler flags at gem-install time in a bundler config. See Release 1.6.5 / 2023-09-08 · sparklemotion/sqlite3-ruby for details.