• Stars
    star
    3,638
  • Rank 12,161 (Top 0.3 %)
  • Language
    C
  • License
    MIT License
  • Created almost 4 years ago
  • Updated 3 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

The ultimate set of SQLite extensions

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.

Main setIncubatorDownloadingInstallationSister projectsStay tuned

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: 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.

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
  • 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.

Downloading

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.

Installation and usage

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.

Building from source

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.

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.

Sister projects

Projects related to sqlean:

  • sqlpkg.org: a catalog of all SQLite extensions, with search and manual downloads.
  • sqlime.org: an online SQLite playground for debugging and sharing SQL snippets.
  • sqlpkg: an automated packager manager for SQLite (like npm for JS or pip for Python).
  • sqlean.py: a drop-in replacement for the default sqlite3 module.
  • sqlean.js: browser-based SQLite with extensions.
  • shell: custom builds for sqlite and sqlean shells.

License

Copyright 2021+ Anton Zhiyanov, Contributors and Third-party Authors.

The software is available under the MIT License.

Stay tuned

Subscribe to stay on top of new features 🚀

More Repositories

1

redka

Redis re-implemented with SQLite
Go
3,354
star
2

codapi

Interactive code examples for documentation, education and fun
Go
1,436
star
3

sqlime

Online SQLite playground
JavaScript
890
star
4

codapi-js

Interactive code examples for documentation, education and fun.
JavaScript
478
star
5

dataviz

Data visualization guide for presentations, reports, and dashboards
Python
436
star
6

pokitoki

Humble GPT Telegram Bot
Python
313
star
7

sqlpkg-cli

SQLite package manager
Go
199
star
8

zero

Zero Dependency Club
159
star
9

sqlpkg

SQLite extension registry
132
star
10

iuliia-py

Transliterate Cyrillic → Latin in every possible way
Python
112
star
11

sqlean.py

Python's sqlite3 + extensions
C
105
star
12

sqlean.js

Browser-based SQLite with extensions
Makefile
86
star
13

iuliia-js

Transliterate Cyrillic → Latin in every possible way
TypeScript
82
star
14

uuidv7

UUIDv7 in N languages
Racket
79
star
15

podsearch-py

Let's find some podcasts!
Python
72
star
16

iuliia

Transliterate Cyrillic → Latin in every possible way
69
star
17

sqlite

SQLite / Sqlean shell builds
Makefile
62
star
18

git-by-example

Step-by-step guide to Git operations
47
star
19

whatbrowser

Все, что нужно знать о браузере пользователя
JavaScript
43
star
20

tryxinyminutes

Live code tutorials in your browser
Shell
41
star
21

invoice

Ну очень простое выставление счетов для юрлиц
HTML
39
star
22

words

Word and phrase lists in CSV
Python
30
star
23

grep-by-example

Step-by-step guide to grep operations
28
star
24

metro

Metro lines and stations for cities in Belorussia, Kazakhstan, Russia and Ukraine
14
star
25

runno

Runno WASI runtime.
TypeScript
10
star
26

sqlite-stats

Statistical functions for SQLite
C
9
star
27

sqlite-wasi

SQLite WASI build.
C
9
star
28

metalsmith-typography

A Metalsmith plugin to enhance typography
JavaScript
9
star
29

python-wasi

Python WASI build.
JavaScript
8
star
30

php-wasi

PHP WASI build.
JavaScript
8
star
31

curl-by-example

Step-by-step guide to curl operations
8
star
32

sqliter

SQLite на практике
JavaScript
6
star
33

lua-wasi

Lua WASI build.
C
5
star
34

nalgeon.github.io

Everything about SQLite, Python, open data and awesome software
JavaScript
3
star
35

sqlite-ext

All "miscellaneous" SQLite extensions from sqlite.org
Makefile
3
star
36

asylum

«Психбольница» Купера 20 лет спустя
CSS
2
star
37

nalgeon

2
star
38

sql-window-functions

SQL Window Functions Explained
2
star
39

inline-css

A native CSS framework for rapid UI development.
HTML
1
star
40

wee

Toy WSGI-compatible web framework
Python
1
star
41

rosenc

HTML
1
star
42

ruby-wasi

Ruby WASI build.
JavaScript
1
star
43

actionist

GitHub Actions Playground
C
1
star