sqlite-regex

The sqlite-regex project provides two distinct Python packages depending on your use case:

1. sqlite-regex: The core package for general Python usage. Use this if you are working with standard Python SQLite integrations.
2. datasette-sqlite-regex: A specialized plugin for Datasette. It acts as a lightweight wrapper around the core sqlite-regex package to enable regex support within the Datasette environment.

A regexset allows you to group multiple patterns together and check if a string matches any of them, or which specific patterns match.

• regexset(p1, p2, ...): Creates a regexset object (returns NULL via pointer passing).
• regexset_is_match(regexset, text): Returns 1 if any pattern in the set matches the text, 0 otherwise.
• regexset_matches(regexset, text): A table function that returns the key (index) and pattern for every pattern in the set that found a match in the text.

-- Check if any pattern matches
select regexset_is_match(regexset('bar', 'foo'), 'foobar'); -- 1

-- Find which patterns matched
select key, pattern
from regexset_matches(
  regexset('\w+', '\d+', '\pL+'),
  'foo'
);

The regex(pattern) function creates a regex "object" using SQLite's pointer passing interface. This is highly recommended for heavy queries using table functions like regex_split() or regex_find_all() to avoid re-parsing the pattern for every row.

Note: The return value will appear as NULL in SQL results due to the pointer passing interface, but it is a valid object. Use regex_print() to verify the pattern.

-- Caching a pattern for performance
select rowid, *
from regex_find_all(
  regex('\b\w{13}\b'),
  'Retroactively relinquishing remunerations is reprehensible.'
);

-- Debugging a regex object
select regex_print(regex('[abc]')); -- '[abc]'

To use sqlite-regex as a runtime-loadable extension, download the appropriate binary for your platform (regex0.dylib for MacOS, regex0.so for Linux, or regex0.dll for Windows) and load it into your SQLite environment.

Note: The 0 in the filename denotes the major version. Since the project is currently pre-v1, breaking changes may occur in future versions.

SQLite CLI

.load ./regex0

Python (sqlite3)

import sqlite3
con = sqlite3.connect(":memory:")
con.enable_load_extension(True)
con.load_extension("./regex0")

Node.js (better-sqlite3)

const Database = require("better-sqlite3");
const db = new Database(":memory:");
db.loadExtension("./regex0");

Datasette

datasette data.db --load-extension ./regex0

The sqlite-regex-linux-x64 package provides a pre-compiled SQLite extension specifically for Linux systems with x64 architecture. When you install the main sqlite-regex package on a compatible Linux machine, this platform-specific package is automatically selected and downloaded. It includes the pre-compiled extension located at lib/regex0.so.

npm install sqlite-regex

Once installed, the sqlite-regex package (which resolves to sqlite-regex-linux-x64 on Linux x64) can be used as a loadable extension with the following Node.js SQLite drivers:

• better-sqlite3
• node-sqlite3

The extension provides regex capabilities to your SQLite instance via the bundled lib/regex0.so binary.

You can install sqlite-regex via various package managers depending on your environment:

• Python: pip install sqlite-regex
• Datasette: datasette install datasette-sqlite-regex
• Node.js: npm install sqlite-regex
• Deno: deno.land/x/sqlite_regex
• Ruby: gem install sqlite-regex
• Rust: cargo add sqlite-regex

Pre-built binaries for Linux x86_64, MacOS, and Windows are also available on the GitHub Releases page.

The sqlite-regex-darwin-arm64 package provides a pre-compiled SQLite extension specifically for macOS systems running on arm64 architecture (Apple Silicon).

When you install the main sqlite-regex package, it will automatically resolve to this platform-specific package if your host computer is running darwin with an arm64 architecture. The extension is bundled as lib/regex0.dylib and is designed for use with better-sqlite3 or node-sqlite3 drivers.

For users on Windows with an x64 architecture, the sqlite-regex package will automatically resolve to sqlite-regex-windows-x64. This package provides a pre-compiled SQLite extension bundled as lib/regex0.dll.

This extension is designed for use with the following Node.js SQLite drivers:

• better-sqlite3
• node-sqlite3

npm install sqlite-regex

To enable regex support in your Datasette instance, install the datasette-sqlite-regex plugin. This plugin loads the sqlite-regex extension, allowing you to use regular expressions directly within your SQL queries.

datasette install datasette-sqlite-regex