All the missing SQLite functions

SQLite has few functions compared to other database management systems. SQLite authors see this as a feature rather than a problem, because SQLite has an extension mechanism in place.

There are a lot of SQLite extensions out there, but they are incomplete, inconsistent and scattered across the internet. sqlean brings them together, neatly packaged into domain modules, documented, tested, and built for Linux, Windows and macOS.

We do not try to gather all the existing extensions into one giant pile — that would not be very useful. The goal is to create a well-thought set of domain modules with a convenient API. A kind of standard library for SQLite.

To achieve it, we split extensions that are too broad, merge the ones that are too narrow, refactor, add missing features, test, document, and do a ton of other small things.

The main set

These are the most popular functions. They are tested, documented and organized into the domain modules with clear API.

Think of them as the extended standard library for SQLite:

crypto: secure hashes
define: user-defined functions and dynamic sql
fileio: read and write files
fuzzy: fuzzy string matching and phonetics
ipaddr: IP address manipulation
json1: JSON functions
math: math functions
re: regular expressions
stats: math statistics
text: string functions
unicode: Unicode support
uuid: Universally Unique IDentifiers
vsv: CSV files as virtual tables

The incubator

These extensions haven't yet made their way to the main set. They may be untested, poorly documented, too broad, too narrow, or without a well-thought API.

Think of them as candidates for the standard library:

array: one-dimensional arrays
besttype: convert string value to numeric
bloom: a fast way to tell if a value is already in a table
btreeinfo, memstat, recsize and stmt: various database introspection features
cbrt and math2: additional math functions and bit arithmetics
classifier: binary classifier via logistic regression
closure: navigate hierarchic tables with parent/child relationships
compress and sqlar: compress / uncompress data
cron: match dates against cron patterns
dbdump: export database as SQL
decimal, fcmp and ieee754: decimal and floating-point arithmetic
envfuncs: read environment variables
isodate: additional date and time functions
lines: read files line-by-line
path: parsing and querying paths
pearson: Pearson correlation coefficient between two data sets
pivotvtab: pivot tables
prefixes: generate string prefixes
rotate: string obfuscation
spellfix: search a large vocabulary for close matches
stats2 and stats3: additional math statistics functions
text2: additional string functions
uint: natural string sorting and comparison
unhex: reverse for hex()
unionvtab: union similar tables into one
xmltojson: convert XML to JSON string
zipfile: read and write zip files
zorder: map multidimensional data to a single dimension

Vote for your favorites! We'll refactor and merge popular ones into the main set.

Download

There are precompiled binaries for every OS:

sqlean-win-x64.zip - for Windows
sqlean-linux-x86.zip - for Linux
sqlean-macos-x86.zip - for Intel-based macOS
sqlean-macos-arm64.zip - for Apple silicon (ARM-based) macOS

Binaries are 64-bit and require a 64-bit SQLite version. If you are using SQLite shell on Windows (sqlite.exe), its 64-bit version is available at https://github.com/nalgeon/sqlite.

Incubator extensions are also available.

Usage

CLI usage:

sqlite> .load ./stats
sqlite> select median(value) from generate_series(1, 99);

IDE usage:

select load_extension('c:\Users\anton\sqlite\stats.dll');
select median(value) from generate_series(1, 99);

In-app usage:

import sqlite3

connection = sqlite3.connect(":memory:")
connection.enable_load_extension(True)
connection.load_extension("./stats.so")
connection.execute("select median(value) from generate_series(1, 99)")
connection.close()

You can specify any other supported extension instead of stats.

Contributing

Contributions are welcome! Submit your own or third-party extension to the incubator:

We want every extension to be self-contained. So we limit the project scope to extensions without external dependencies (other than the C standard library and SQLite itself).

Please note that we only accept extensions with permissive licenses (MIT License, Apache License etc) or public domain. Copyleft licenses like GPL won't do.

License

The software is available under the MIT License.

Stay tuned

Follow @ohmypy on Twitter to keep up with new features 🚀