Skip to content Rémi Durieu

How to compile SQLite with ICU support (and Ruby integration)

Published on

Introduction

Hello there ~

Being a Magic The Gathering player and Ruby on Rails developer, I developped MTG Translator, a small deck translation tool (it crossed 100k translations in last August!) backed by the SQLite cards database provided by the MTGJSON project. As a translation tool, the app needs to handle Unicode characters to support multiple languages. I especially want it to be flexible in regard to casing and diacritics, so that for example: Étendues de chasse/Etendues de chasse/étendues de chasse/etendues de chasse are treated the same and are all translated to Hunting Wilds in English.

Unfortunately, SQLite doesn't support case-folding on Unicode characters by default. But there is a way to enable it:

Full unicode case folding is supported in SQLite if it is compiled with the -DSQLITE_ENABLE_ICU option and linked against the International Components for Unicode library.

This might seem a bit cryptic and intidimating. "Oh no, I don't want to deal with custom compilation" you might say. At least that was my first thought, even with a formal background in Computer Science. But I still ended up digging into this solution anyway. Turns out there is not that much up-to-date and complete information on the web on this very specific topic and I have gone through a lot of trials and errors when I was looking up at how to do this properly. This blog post is an attempt to write the comprehensive guide I wish existed earlier and to hopefully provide some keys to understand what's happening.

How to get ICU?

ICU stands for International Components Unicode and is a project that provides libraries for handling Unicode characters in C/C++ and Java. Unfortunately, the download instructions are not really user-friendly. Additionnaly, since we are not only going to use the library but also compile against it, we also need the development files. My recommandation is to use your system's package manager. Note that some package managers split the base package and the development files and some don't.

Debian 13

sudo apt install libicu76 libicu-dev

Note that the library version is included in the package name (libicu76) and that other Debian versions may ship the same library with another versioned name. For example, Debian 12 ships libicu72. Find the current version using the search index.

Fedora

sudo dnf install libicu libicu-devel

Arch

sudo pacman -S icu

MacOS (Homebrew)

brew install icu4c@78

Same note as for Debian: watch out for the version number!

Download and compile SQLite

The SQLite download page provides multiple archives. We are going to use the one tagged autoconf which includes the configuration script we need.

Download and extract the archive:

curl -fsSLO https://sqlite.org/2026/sqlite-autoconf-3530400.tar.gz
tar -xzvf sqlite-autoconf-3530400.tar.gz

Now, we're going to use the ./configure script to configure the build environment. We can run ./configure --help to find the options we need.

The --icu-collations option enables ICU.

The --with-icu-ldflags is required when using --icu-collations and tells the compiler where to find the shared libraries to link against (the ICU libraries in our case). We can use pkg-config --libs --static icu-i18n to retrieve the needed flags for our system. We are also going to include --with-icu-cflags for good measure, even though, in my personal experience, these are usually empty.

Note that on macOS, when installing ICU with Homebrew, the package is not linked to system files (it is a keg-only package) and as such, pkg-config fails to find the icu-i18n package. To work around this, run pkg-config with the PKG_CONFIG_PATH environment variable set to $(brew --prefix icu4c@78)/lib/pkgconfig.

The --prefix option is very important! It enables us to install SQLite in a specific directory rather than overriding the system's SQLite.

# Change the working directory to the extracted directory's root
cd sqlite-autoconf-3530400/

# This is going to be our prefix (where SQLite will be installed).
#
# Pick anything that is relevant to your own situation. For the purpose of this
# post, I'm creating a directory specific to this SQLite version.
PREFIX="$HOME/.local/sqlite/sqlite-3.53.4-icu"
mkdir -p $PREFIX

# The configure script will create a Makefile based on the given options
./configure \
  --prefix=$PREFIX \
  --icu-collations \
  --with-icu-ldflags="$(pkg-config --libs --static icu-i18n)" \
  --with-icu-cflags="$(pkg-config --cflags icu-i18n)"

# Install SQLite
make install

Congratulations! You now have a working ICU enabled SQLite at $PREFIX. You can run the binary:

~/.local/sqlite/sqlite-3.53.4-icu/bin/sqlite3

Loading collations

We can use the icu_load_collation function to load ICU collations for a specific locale. This functions takes two arguments: a locale identifier and the name that we want to use for the collation.

SELECT icu_load_collation('fr', 'my_french_collation');
SELECT * FROM things ORDER BY name COLLATE my_french_collation;

The locale identifier can be parameterized with additional keywords. For example we can set the strength of the comparison to "primary" so that it is case-insensitive and accent-insentitive:

SELECT icu_load_collation('fr@colStrength=primary', 'my_french_collation');

Since 2024, the icu_load_collation accepts a third argument to specify the strength of the collation. So the previous example can be rewritten like this:

SELECT icu_load_collation('fr', 'my_french_collation', 'PRIMARY');

If the locale identifier is empty or not recognized, the fallback is the root locale that comes with a default collation that has technical tradeoffs but it supports case folding with diacritics correctly. Perfect for my use case:

SELECT icu_load_collation('', 'unicode', 'PRIMARY');

Sources

Integration with Ruby

The sqlite3 gem

We now need to enable our program to use the newly built SQLite. When it comes to Ruby, the sqlite3 gem comes bundled with its own SQLite by default but can be instructed to use another one.

The following commands use Bundle to configure the installation of the sqlite3 gem and reinstalls it afterwards to pick the new configuration.

Note the --local flag that keeps this configuration per-project: this means that you now have a machine-dependant .bundle/config that must probably be .gitignored. Whether that is OK for you depends on your project.

bundle config set --local build.sqlite3 "--platform=ruby --enable-system-libraries --with-opt-dir=$PREFIX"
bundle install --no-cache --redownload

System calls

To handle calls to the sqlite3 binary, we must provide the right PATH so that programs can find our custom build instead of relying on the system's SQLite. For a Ruby on Rails project, I have the following initializer that prepends the SQLite installation directory to the process' PATH.

prefix = ENV.fetch("SQLITE_PREFIX", Rails.root.join(".sqlite/install"))
path = File.join(prefix, "bin")
ENV["PATH"] = "#{path}:#{ENV["PATH"]}"

Now, calls to system("sqlite3") and rails dbconsole use the right binary.

A final Makefile

As a bonus, here is a Makefile that automates the process described in this post.

SQLITE_DOWNLOAD_URL ?= https://sqlite.org/2026/sqlite-autoconf-3530300.tar.gz
SQLITE_BUILD_DIR ?= .sqlite/build
SQLITE_BUILD_DIR := $(abspath $(SQLITE_BUILD_DIR))
SQLITE_PREFIX ?= .sqlite/install
SQLITE_PREFIX := $(abspath $(SQLITE_PREFIX))

ARCHIVE := $(SQLITE_BUILD_DIR)/$(notdir $(SQLITE_DOWNLOAD_URL))
SOURCE_DIR := $(ARCHIVE:.tar.gz=)

all: compile bundle

download: $(ARCHIVE)

$(ARCHIVE):
	mkdir -p $(SQLITE_BUILD_DIR)
	curl -o $(ARCHIVE) -fsSL $(SQLITE_DOWNLOAD_URL)

extract: $(SOURCE_DIR)

$(SOURCE_DIR): $(ARCHIVE)
	tar -C $(SQLITE_BUILD_DIR) -xzf $(ARCHIVE)

compile: $(SOURCE_DIR)
	cd $(SOURCE_DIR) && \
	icu_cflags=$$(pkg-config --cflags icu-i18n) && \
	icu_ldflags=$$(pkg-config --libs --static icu-i18n) && \
	./configure \
		--prefix=$(SQLITE_PREFIX) \
		--fts5 \
		--with-icu-cflags="$$icu_cflags" \
		--with-icu-ldflags="$$icu_ldflags" \
		--icu-collations && \
	make install

bundle:
	bundle config set --local build.sqlite3 "--enable-system-libraries --with-opt-dir=$(SQLITE_PREFIX)"

clean:
	rm -rf $(SQLITE_BUILD_DIR) $(SQLITE_PREFIX)

help:
	@echo "Usage: make -f sqlite.mk [all|download|extract|compile|bundle|clean|help]"
	@echo ""
	@echo "  all      download + extract + compile + bundle (default)"
	@echo "  download download the SQLite tarball into $(SQLITE_BUILD_DIR)"
	@echo "  extract  unpack it (skips if already extracted)"
	@echo "  compile  configure with ICU/collations, make install into $(SQLITE_PREFIX)"
	@echo "  bundle   point the sqlite3 gem at the custom install"
	@echo "  clean    remove $(SQLITE_BUILD_DIR) and $(SQLITE_PREFIX)"
	@echo ""
	@echo "Defaults:"
	@echo "  SQLITE_DOWNLOAD_URL  $(SQLITE_DOWNLOAD_URL)  (used by download)"
	@echo "  SQLITE_BUILD_DIR     $(SQLITE_BUILD_DIR)"
	@echo "  SQLITE_PREFIX        $(SQLITE_PREFIX)"
	@echo ""
	@echo "Overridable: SQLITE_DOWNLOAD_URL, SQLITE_BUILD_DIR, SQLITE_PREFIX"

.PHONY: all download extract compile bundle clean help