Support SQLite's REGEXP operator

[behrangsa]

For example:

SELECT * FROM my_table WHERE 'Hello, world' REGEXP pattern

Looks like it's not currently supported out of the box:

SQLiteError: no such function: REGEXP

[manimovassagh]

SQLite's REGEXP operator requires a user-defined function to be registered — it's not built into SQLite itself. The REGEXP keyword in SQL is just syntactic sugar that calls a function named regexp(pattern, string), and SQLite ships without one.

In Bun, you can register it using db.run or the Database constructor with a custom function:

import { Database } from "bun:sqlite";

const db = new Database("my.db");

// Unfortunately bun:sqlite doesn't expose create_function directly,
// so use the query builder with LIKE/GLOB as alternatives:

// Option 1: Use GLOB (supports * and ? wildcards)
const rows = db.query("SELECT * FROM my_table WHERE pattern GLOB ?").all("Hello*");

// Option 2: Use LIKE (supports % and _ wildcards) 
const rows2 = db.query("SELECT * FROM my_table WHERE column LIKE ?").all("%world%");

If you truly need regex, the best approach currently is to filter in JS:

import { Database } from "bun:sqlite";

const db = new Database("my.db");
const allRows = db.query("SELECT * FROM my_table").all();
const pattern = /Hello,\s+world/;
const filtered = allRows.filter(row => pattern.test(row.column_name));

For actual REGEXP support, Bun would need to expose SQLite's sqlite3_create_function API so you can register a custom regexp() function in Rust/C or JS. better-sqlite3 supports this via .function(), and so does Python's sqlite3 module.

This is worth filing as a feature request specifically for bun:sqlite to support custom SQL functions — something like:

// Hypothetical API
db.createFunction("regexp", (pattern: string, value: string) => {
  return new RegExp(pattern).test(value) ? 1 : 0;
});

// Then this would work:
db.query("SELECT * FROM my_table WHERE column REGEXP ?").all("^Hello");

better-sqlite3 already supports this if you need it today:

import Database from "better-sqlite3";

const db = new Database("my.db");
db.function("regexp", (pattern, value) =>
  new RegExp(pattern).test(value) ? 1 : 0
);

db.prepare("SELECT * FROM my_table WHERE column REGEXP ?").all("^Hello");

[behrangsa]

Awesome. Yes, I did some digging after I reported this bug report and figured out how to do this (registers a customer function). But I had to switch to Node.js.

My confusion stemmed from the fact that REGEXP was working in my SQLite GUI (SQLite Studio IIRC) out of the box.

[manimovassagh]

SQLite doesn't include a built-in REGEXP implementation — it defines the operator syntax but requires you to register a user-defined function for it. This is a SQLite design decision, not a Bun limitation.

With Bun's SQLite API, you can register a custom function to enable it:

import { Database } from 'bun:sqlite';

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

// Register the REGEXP function
db.run(`SELECT 1`); // ensure db is open
// Unfortunately Bun's bun:sqlite doesn't expose create_function yet,
// so REGEXP isn't directly usable.

The underlying issue is that bun:sqlite doesn't currently expose sqlite3_create_function() which is what you'd need to register a custom regexp() handler.

Workarounds:

1. Use LIKE or GLOB instead — they're built into SQLite and cover many pattern matching use cases:

db.query("SELECT * FROM my_table WHERE pattern LIKE '%world%'").all();
db.query("SELECT * FROM my_table WHERE pattern GLOB '*world*'").all();

2. Filter in JS — query the rows and filter with native RegExp:

const rows = db.query('SELECT * FROM my_table').all();
const pattern = /Hello, world/;
const filtered = rows.filter(row => pattern.test(row.column));

3. Use better-sqlite3 which does support db.function() for registering custom functions, including REGEXP.

A feature request for db.function() / sqlite3_create_function support in bun:sqlite would be the right way to get native REGEXP working.

[behrangsa]

Let's close this and create a FR for registering custom functions.

I was aware of the workarounds (thanks to LLMs), but I was looking for built-in support.

With the exception of better-sqlite, which I was trying to avoid, none of the other options are good enough, especially if you want to perform the matching against two columns of one or more tables.

[truongan07]

SELECT * FROM my_table WHERE 'Hello, world' REGEXP pattern

For detail examples about REGEXP pattern