Awesome

sqlite-ecosystem

All the SQLite extensions, tools, and guides that I (Alex Garcia 👋🏼) have written and open sourced.

Feel free to start a discussion in this repo about meta-level SQLite extension and tooling talk! Though if you're having a specific issue with a particular extension or tool, then file an issue in that project's repository.

Overview

These are all the loadable SQLite extensions I have built, along with which programming language they are written in and where they are distributed.

Extension	Description	Language	Python	Node.js	Deno	Ruby	Datasette	sqlite-utils
`sqlite-http`	Make HTTP requests	Go	✅	✅	✅	✅	✅	✅
`sqlite-html`	parse HTML documents	Go	✅	✅	✅	✅	✅	✅
`sqlite-lines`	Read files/blob line-by-line	C	✅	✅	✅	✅	✅	✅
`sqlite-path`	Parse and generate filepaths	C	✅	✅	✅	✅	✅	✅
`sqlite-url`	Parse and generate URLs	C	✅	✅	✅	✅	✅	✅
`sqlite-xsv`	Query CSVs	Rust	✅	✅	✅	✅	🚧	🚧
`sqlite-regex`	Regular expression functions	Rust	✅	✅	✅	✅	✅	✅
`sqlite-ulid`	Work with ULIDs	Rust	✅	✅	✅	✅	✅	✅
`sqlite-jsonschema`	Validate JSON objects with JSON Schema	Rust	✅	✅	✅	✅	✅	✅
`sqlite-fastrand`	Generate fast numbers/blobs quickly	Rust	✅	✅	✅	✅	✅	✅
`sqlite-vss`	Vector search in SQLite	C++	✅	✅	✅	✅	✅	✅

Extensions

`sqlite-http`

A SQLite extension for making HTTP requests purely in SQL.

Create GET, POST, and other HTTP requests, like curl, wget, and fetch
Download response bodies, header, status codes, timing info
Set rate limits, timeouts

select http_get_body('https://text.npr.org/');
select
  http_get_body('https://api.github.com/repos/sqlite/sqlite') ->> '$.description';

Language/Platform	Install
Python	`pip install sqlite-http`
Node.js	`npm install sqlite-http`
Deno	`deno.land/x/sqlite_http`
Datasette	`datasette install datasette-sqlite-http`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-http`
Ruby	`gem install sqlite-http`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)
windows-x86_64 (Windows x86_64)

`sqlite-html`

A SQLite extension for querying, manipulating, and creating HTML elements.

Extract HTML or text from HTML with CSS selectors, like .querySelector(), .innerHTML, and .innerText
Generate a table of matching elements from a CSS selector, like .querySelectorAll()
Safely create HTML elements in a query, like .createElement() and .appendChild()

select html_extract('<p> Anakin <b>Skywalker</b> </p>', 'b'); -- "<b>Skywalker</b>"

Language/Platform	Install
Python	`pip install sqlite-html`
Node.js	`npm install sqlite-html`
Deno	`deno.land/x/sqlite_html`
Datasette	`datasette install datasette-sqlite-html`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-html`
Ruby	`gem install sqlite-html`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)
windows-x86_64 (Windows x86_64)

`sqlite-lines`

A SQLite extension for efficiently reading large files or blobs line-by-line.

select line from lines_read('logs.txt');

Language/Platform	Install
Python	`pip install sqlite-lines`
Node.js	`npm install sqlite-lines`
Deno	`deno.land/x/sqlite_lines`
Datasette	`datasette install datasette-sqlite-lines`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-lines`
Ruby	`gem install sqlite-lines`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)

`sqlite-path`

A SQLite extension for parsing, generating, and querying paths.

select path_dirname('foo/bar.txt'); -- 'foo/'
select path_basename('foo/bar.txt'); -- 'bar.txt'
select path_extension('foo/bar.txt'); -- '.txt'

Language/Platform	Install
Python	`pip install sqlite-path`
Node.js	`npm install sqlite-path`
Deno	`deno.land/x/sqlite_path`
Datasette	`datasette install datasette-sqlite-path`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-path`
Ruby	`gem install sqlite-path`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)
windows-x86_64 (Windows x86_64)

`sqlite-url`

A SQLite extension for parsing, generating, and querying URLs and query strings

Extract specific parts of a URL, like schemes, hostnames, and paths
Generate URLs with parts safely
Iterate through all key and values in a query string

select url_valid('https://sqlite.org'); -- 1
select url_scheme('https://www.sqlite.org/vtab.html#usage'); -- 'https'
select url_host('https://www.sqlite.org/vtab.html#usage'); -- 'www.sqlite.org'

Language/Platform	Install
Python	`pip install sqlite-url`
Node.js	`npm install sqlite-url`
Deno	`deno.land/x/sqlite_url`
Datasette	`datasette install datasette-sqlite-url`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-url`
Ruby	`gem install sqlite-url`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)

`sqlite-xsv`

A fast and performant SQLite extension for CSV files, written in Rust!

Query CSVs, TSVs, and other-SVs as SQLite virtual tables
The "reader" interface lets you query CSVs from other data sources, such as sqlite-http
Builtin support for querying CSVs with gzip or zstd compression

create virtual table temp.students using csv(
  filename="students.csv"
);

select * from temp.students;

create virtual table temp.students_gz using csv(
  filename="students.csv.gz"
);

create virtual table temp.students_reader using csv_reader(
  id integer,
  name text,
  age integer,
  progess real
);
select * from temp.students_reader('./target/students.csv');

Language/Platform	Install
Python	`pip install sqlite-xsv`
Node.js	`npm install sqlite-xsv`
Deno	`deno.land/x/sqlite_xsv`
Ruby	`gem install sqlite-xsv`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)
windows-x86_64 (Windows x86_64)

`sqlite-regex`

A fast SQLite extension for regular expressions.

select regex_find(
  '[0-9]{3}-[0-9]{3}-[0-9]{4}',
  'phone: 111-222-3333'
);

select rowid, *
from regex_find_all(
  '\b\w{13}\b',
  'Retroactively relinquishing remunerations is reprehensible.'
);
/*
┌───────┬───────┬─────┬───────────────┐
│ rowid │ start │ end │     match     │
├───────┼───────┼─────┼───────────────┤
│ 0     │ 0     │ 13  │ Retroactively │
│ 1     │ 14    │ 27  │ relinquishing │
│ 2     │ 28    │ 41  │ remunerations │
│ 3     │ 45    │ 58  │ reprehensible │
└───────┴───────┴─────┴───────────────┘
*/

Language/Platform	Install
Python	`pip install sqlite-regex`
Node.js	`npm install sqlite-regex`
Deno	`deno.land/x/sqlite_regex`
Datasette	`datasette install datasette-sqlite-regex`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-regex`
Ruby	`gem install sqlite-regex`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)
windows-x86_64 (Windows x86_64)

`sqlite-ulid`

A SQLite extension for generating and working with ULIDs.

select ulid(); -- '01gqr4j69cc7w1xdbarkcbpq17'
select ulid_bytes(); -- X'0185310899dd7662b8f1e5adf9a5e7c0'
select ulid_with_prefix('invoice'); -- 'invoice_01gqr4jmhxhc92x1kqkpxb8j16'
select ulid_datetime('01gqr4j69cc7w1xdbarkcbpq17') -- '2023-01-26 22:53:20.556'

Language/Platform	Install
Python	`pip install sqlite-ulid`
Node.js	`npm install sqlite-ulid`
Deno	`deno.land/x/sqlite_ulid`
Datasette	`datasette install datasette-sqlite-ulid`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-ulid`
Ruby	`gem install sqlite-ulid`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)
windows-x86_64 (Windows x86_64)

`sqlite-jsonschema`

A SQLite extension for validating JSON objects with JSON Schema.

select jsonschema_matches('{"maxLength": 5}', json_quote('alex')); -- 1

Language/Platform	Install
Python	`pip install sqlite-jsonschema`
Node.js	`npm install sqlite-jsonschema`
Deno	`deno.land/x/sqlite_jsonschema`
Datasette	`datasette install datasette-sqlite-jsonschema`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-jsonschema`
Ruby	`gem install sqlite-jsonschema`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)
windows-x86_64 (Windows x86_64)

`sqlite-fastrand`

A SQLite extension for quickly generating random numbers, booleans, characters, and blobs

select fastrand_int(); -- 556823563
select fastrand_alphabetic(); -- 's'
select fastrand_uppercase();-- 'M'
select fastrand_double(); -- 0.740834390248454

Language/Platform	Install
Python	`pip install sqlite-fastrand`
Node.js	`npm install sqlite-fastrand`
Deno	`deno.land/x/sqlite_fastrand`
Datasette	`datasette install datasette-sqlite-fastrand`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-fastrand`
Ruby	`gem install sqlite-fastrand`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)
windows-x86_64 (Windows x86_64)

`sqlite-vss`

A SQLite extension for efficient vector search, based on Faiss!

create virtual table vss_articles using vss0(
  headline_embedding(384),
  description_embedding(384),
);

insert into vss_articles(rowid, headline_embedding)
  select rowid, headline_embedding from articles;

select rowid, distance
from vss_articles
where vss_search(
  headline_embedding,
  (select headline_embedding from articles where rowid = 123)
)
limit 100;

Language/Platform	Install
Python	`pip install sqlite-vss`
Node.js	`npm install sqlite-vss`
Deno	`deno.land/x/sqlite_vss`
Datasette	`datasette install datasette-sqlite-vss`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-vss`
Ruby	`gem install sqlite-vss`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)

`sqlite-hello`

The smallest "Hello world" SQLite extension possible.

sqlite> .load ./hello0
sqlite> select hello('Alex');
'Hello, Alex!'

Language/Platform	Install
Python	`pip install sqlite-hello`
Node.js	`npm install sqlite-hello`
Deno	`deno.land/x/sqlite_hello`
Datasette	`datasette install datasette-sqlite-hello`
sqlite-utils	`sqlite-utils install sqlite-utils-sqlite-hello`
Ruby	`gem install sqlite-hello`
Github Release

linux-x86_64 (Linux x86_64)
macos-x86_64 (MacOS x86_64)
macos-aarch64 (MacOS M1 and M2 chips)
windows-x86_64 (Windows x86_64)

Distribution

SQLite extensions have historically been hard to shared with other people. Installing and using them can be tricky and confusing, but these are some strategies I've used to make that easier!

`pip` for Python Developers

Most of my SQLite extensions are additional placed in a small Python library and distributed on PyPi. Python developers can then install these extensions with a pip install command like so:

pip install sqlite-regex

The actual Python library is small and meant to be used with the builtin sqlite3 Python module, like so:

import sqlite3
import sqlite_regex

db = sqlite3.connect(':memory:')

db.enable_load_extension(True)
sqlite_regex.load(db)

db.execute("select regex_version(), '[abc]' regexp 'a';").fetchone()
# ('v0.1.0', 1)

See _Making SQLite extensions pip install-able _ (February 2023) for more details.

`npm` for Node.js Developers

These extensions are also distributed on npm for use in Node.js. they can be install with a npm install command like so:

npm install sqlite-regex

And then used in Node.js programs with better-sqlite3 or node-sqlite3 like so:

import Database from "better-sqlite3";
import * as sqlite_regex from "sqlite-regex";

const db = new Database(":memory:");

db.loadExtension(sqlite_regex.getLoadablePath());

const version = db.prepare("select regex_version()").pluck().get();
console.log(version); // "v0.2.0"

See Making SQLite extensions npm install'able for Node.js, and on deno.land/x for Deno (March 2023) for more details.

`deno.land/x` for Deno Developers

These extensions are also distributed on deno.land/x for use in Deno programs. It's meant to work with the x/sqlite3 client like so:

import { Database } from "https://deno.land/x/sqlite3@0.8.0/mod.ts";
import * as sqlite_regex from "https://deno.land/x/sqlite_regex@v0.2.3-alpha.2/mod.ts";

const db = new Database(":memory:");

db.enableLoadExtension = true;
sqlite_regex.load(db);

const [version] = db.prepare("select regex_version()").value<[string]>()!;

console.log(version);

See the "Deno" section of Making SQLite extensions npm install'able for Node.js, and on deno.land/x for Deno (March 2023) for more details.

`gem` for ruby Developers

These extensions are also distributed on rubygems.org for use in Ruby applications. It's meant to work with the sqlite3 client like so:

require 'sqlite3'
require 'sqlite_regex'

db = SQLite3::Database.new(':memory:')
db.enable_load_extension(true)
SqliteRegex.load(db)
db.enable_load_extension(false)

result = db.execute("select regex_version(), '[abc]' regexp 'a';")
puts result.first.first # "v0.2.3-alpha.7"
puts result.first.last  # 1

See Making SQLite extension gem install'able for Ruby Developers (June 2023) for more details.

As Datasette Plugins

Most of these SQLite extensions are also distributed as Datasette Plugins. They are small wrappers around their corresponding pip packages.

They can be installed like so:

datasette install datasette-sqlite-regex

Now all future Datasette instances will include sqlite-regex functions. Here we test it using datasette --get.

$ datasette --get '/_memory.csv?sql=select+regex_version()'
regex_version()
v0.1.0

As sqlite-utils Plugins

Most of these SQLite extensions are also distributed as sqlite-utils plugins. They are small wrappers around their corresponding pip packages.

They can be installed like so:

sqlite-utils install sqlite-utils-sqlite-regex

Now when using the sqlite-utils CLI, the sqlite-regex functions will be available.

$ sqlite-utils memory 'select regex_version()'
[{"regex_version()": "v0.2.3"}]

Roadmap

Various extensions and tools that I plan to build and open source in the future! Most of the development of these happen in private repos, to make my life easier.

If you're interested in any of these, just send me a message!

Future SQLite Extensions

sqlite-img: Query and manipulate images (cropping, thumbnails, rotation, etc.)
sqlite-xml: Query XML documents with XPath strings
sqlite-assert: Make assertions of your data at query-time
sqlite-parquet: Query Parquet files from SQLite
sqlite-md: Query Markdown documents as their AST
sqlite-geo: A GIS extension, a slimmed-down alternative to spatialite
sqlite-python: Write loadable SQLite extensions in pure Python
sqlite-qjs: Create custom scalar, aggregate, and table functions in JavaScript with QuickJS
sqlite-duckdb: Query and insert data in DuckDB tables, query CSVs/Parquet/JSON with DuckDB
sqlite-pg: Query and insert data into Postgres tables
sqlite-notion: Query and insert data into Notion databases
sqlite-google-sheets: query and insert data into Google Sheet

Future Extension Bindings

Elixir
Rust
Go

Future Tooling

sqlite-package-manager: A command-line tool for manage SQLite extensions for your project, similar to npm/pip/brew
sqlite-docs: Document SQLite tables and columns with in-line comments on CREATE TABLE statements, like JSDoc/rustdoc/docstrings
sqlitex: A modern sqlite3 alternative, a new CLI with Parquet/S3 support, a bigger stdlib, syntax highlighting, and more