sqlean: The SQLite Extension Pack You Didn't Know You Needed

SQLite is fine on its own, but sqlean makes it actually useful for real work. Here's why you should probably be using it.

| 4 min read
sqlite database extensions tooling

SQLite is everywhere. Your phone, your browser, probably half the apps on your computer. It’s the most deployed database in the world, which is impressive until you actually try to do something with it and realize it’s missing a bunch of stuff you’d expect from a database.

Want to do a regex search? Nope. Need some basic crypto functions? Not included. Math beyond addition? Good luck. This is where sqlean comes in.

What Even Is sqlean?

It’s a collection of SQLite extensions that add the functionality SQLite should’ve had from the start. Created by Anton Zhiyanov (nalgeon on GitHub), it bundles together a bunch of useful extensions into one package. No hunting down individual extensions or compiling them yourself.

The main stuff you get:

  • crypto: Hash functions, encoding/decoding (MD5, SHA, base64, hex, URL encoding)
  • fileio: Read and write files directly from SQL (surprisingly useful)
  • fuzzy: Fuzzy string matching and phonetics
  • ipaddr: IP address manipulation
  • math: Actual math functions beyond the basics
  • regexp: Regular expressions that actually work
  • stats: Statistical functions (median, percentiles, etc.)
  • text: String manipulation that doesn’t make you want to cry
  • unicode: Proper Unicode support
  • uuid: Generate UUIDs without external tools

Why This Matters

SQLite’s philosophy is to be minimal and embedded. That’s great for compatibility but annoying when you’re trying to get work done. You end up either:

  1. Doing string manipulation in your application code (gross)
  2. Writing complex SQL workarounds (also gross)
  3. Switching to PostgreSQL for a single-user app (overkill)

sqlean lets you keep using SQLite but without the constant friction. Need to validate an email format? Use regex. Want to hash a password? Use the crypto functions. Need to calculate a median? Use stats.

The Real Use Case

I built a SQLite extension for crypto functions before I found sqlean. Spent way too much time on it. Then I discovered sqlean does that plus a dozen other things I didn’t know I needed.

Here’s the thing: SQLite is perfect for local-first apps, CLI tools, embedded systems, and anything that doesn’t need a server. But vanilla SQLite makes you work too hard. sqlean bridges that gap without adding bloat.

Example: You’re building a CLI tool that needs to store data and occasionally search with fuzzy matching. With vanilla SQLite, you’re writing fuzzy match logic in your app code or doing weird LIKE hacks. With sqlean, you just:

SELECT * FROM products
WHERE text_fuzzy(name, 'pyton') > 0.8;

That finds “Python” even though you typo’d it. In SQL. Without writing any matching logic.

Installation Is Weirdly Easy

Most SQLite extensions are a pain to install. Compile from source, find the right shared library, set obscure flags. sqlean is just:

# macOS/Linux
curl -fsSL https://sqlpkg.org/install.sh | sh
sqlpkg install sqlean

# Or download the pre-compiled binary

Then load it in your SQLite connection:

import sqlite3
conn = sqlite3.connect('mydb.db')
conn.enable_load_extension(True)
conn.load_extension('sqlean')

Done. All extensions loaded at once.

What I Actually Use It For

I have a few projects that use sqlean:

CLI tool for managing dev environments: Uses the crypto functions to hash secrets, fileio to export configs, and regexp for pattern matching in queries. All stuff I’d have to do in application code otherwise.

Local-first web app: Uses stats functions for analytics dashboards and text functions for search. Keeps everything in the database layer instead of scattering logic across the codebase.

Data migration scripts: The text and unicode functions make cleaning messy data way easier. Running a SQL script beats writing Python for simple transformations.

Downsides (Because Nothing Is Perfect)

Extra dependency: You need to bundle sqlean with your app. It’s not huge, but it’s there.

Not standard SQLite: If someone opens your database with a vanilla SQLite client, queries using sqlean functions will fail. Document your dependencies.

Platform support: Works great on Linux, macOS, and Windows. Other platforms might need manual compilation.

Loading overhead: There’s a tiny startup cost to load all the extensions. Negligible for most use cases, but worth noting if you’re optimizing startup time.

Should You Use It?

If you’re using SQLite for anything beyond the most basic CRUD operations, probably yes. The alternative is reinventing these wheels in your application code or switching to a heavier database.

sqlean isn’t flashy. It just makes SQLite less annoying to work with. That’s exactly what a good tool should do.

Check it out at https://github.com/nalgeon/sqlean. There’s also sqlean.py if you’re working in Python, which makes installation even easier.

It’s one of those tools that once you start using, you wonder how you worked without it.

Back to blog