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.
🌟 Python package: a drop-in replacement for the default sqlite3
module.
🌟 JavaScript package: browser-based SQLite with extensions.
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: hashing, encoding and decoding data
- define: user-defined functions and dynamic sql
- fileio: read and write files
- fuzzy: fuzzy string matching and phonetics
- ipaddr: IP address manipulation
- math: math functions
- regexp: regular expressions
- stats: math statistics
- text: string functions
- unicode: Unicode support
- uuid: Universally Unique IDentifiers
- vsv: CSV files as virtual tables
The single-file sqlean
bundle contains all extensions from the main set.
P.S. Please share your use cases to help the project focus on important aspects.
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
- 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
- json1: JSON functions
- math2: additional math functions and bit arithmetics
- 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
- uint: natural string sorting and comparison
- 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.
There are precompiled binaries for every OS:
sqlean-win-x64.zip
- for Windowssqlean-linux-x86.zip
- for Linuxsqlean-macos-x86.zip
- for Intel-based macOSsqlean-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.
The easiest way to try out sqlean
extensions is to use the pre-bundled shell. But you can also load them individually.
For example, using the SQLite command-line interface:
sqlite> .load ./sqlean
sqlite> select median(value) from generate_series(1, 99);
See How to Install an Extension for usage with IDE, Python, JavaScript, etc.
Download the dependencies:
make prepare-dist
make download-sqlite
make download-external
Then build for your OS (choose one of the following):
make compile-linux
make compile-windows
make compile-macos
You'll find the compiled extensions in the dist
folder.
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.
Copyright 2021+ Anton Zhiyanov, Contributors and Third-party Authors.
The software is available under the MIT License.
Follow @ohmypy on Twitter to keep up with new features 🚀