Learn how to extend SQLite with custom functions, aggregate functions, and loadable extensions.
Scalar functions operate on individual values and return a single result.
import { DatabaseSync } from "@photostructure/sqlite";
const db = new DatabaseSync(":memory:");
// Simple function
db.function("double", (x) => x * 2);
// Use in SQL
const result = db.prepare("SELECT double(21) as answer").get();
console.log(result.answer); // 42
// Multiple parameters
db.function("multiply", (a, b) => a * b);
const product = db.prepare("SELECT multiply(6, 7) as result").get();
console.log(product.result); // 42
// Deterministic function - same input always produces same output
db.function(
"hash",
{
deterministic: true,
},
(value) => {
const crypto = require("crypto");
return crypto.createHash("sha256").update(String(value)).digest("hex");
},
);
// Direct-only function - cannot be used in triggers or views
db.function(
"random_uuid",
{
directOnly: true,
},
() => {
const crypto = require("crypto");
return crypto.randomUUID();
},
);
// Variable arguments function
db.function(
"concat",
{
varargs: true,
},
(...args) => {
return args.join("");
},
);
// Use varargs function
const text = db
.prepare('SELECT concat("Hello", " ", "World", "!") as msg')
.get();
console.log(text.msg); // "Hello World!"
// Reverse string function
db.function("reverse", (str) => {
return str ? str.split("").reverse().join("") : null;
});
// Extract domain from email
db.function("email_domain", (email) => {
if (!email || !email.includes("@")) return null;
return email.split("@")[1].toLowerCase();
});
// Slugify function
db.function("slugify", (text) => {
if (!text) return null;
return text
.toLowerCase()
.replace(/[^\w\s-]/g, "")
.replace(/\s+/g, "-")
.replace(/-+/g, "-")
.trim();
});
// Usage
db.exec(`
CREATE TABLE articles (
id INTEGER PRIMARY KEY,
title TEXT,
slug TEXT GENERATED ALWAYS AS (slugify(title)) STORED
)
`);
// Parse ISO date to Unix timestamp
db.function("iso_to_timestamp", (isoDate) => {
const date = new Date(isoDate);
return Math.floor(date.getTime() / 1000);
});
// Format timestamp as relative time
db.function("time_ago", (timestamp) => {
const seconds = Math.floor(Date.now() / 1000) - timestamp;
const intervals = [
{ label: "year", seconds: 31536000 },
{ label: "month", seconds: 2592000 },
{ label: "day", seconds: 86400 },
{ label: "hour", seconds: 3600 },
{ label: "minute", seconds: 60 },
];
for (const interval of intervals) {
const count = Math.floor(seconds / interval.seconds);
if (count >= 1) {
return `${count} ${interval.label}${count > 1 ? "s" : ""} ago`;
}
}
return "just now";
});
// Usage
const posts = db
.prepare(
`
SELECT title, time_ago(created_at) as posted
FROM posts
ORDER BY created_at DESC
`,
)
.all();
// Parse JSON and extract field
db.function("json_get", (jsonStr, path) => {
try {
const obj = JSON.parse(jsonStr);
return path.split(".").reduce((o, p) => o?.[p], obj);
} catch {
return null;
}
});
// Check if JSON contains value
db.function("json_contains", (jsonStr, value) => {
try {
const obj = JSON.parse(jsonStr);
return JSON.stringify(obj).includes(value) ? 1 : 0;
} catch {
return 0;
}
});
Aggregate functions process multiple rows and return a single result.
// Custom sum aggregate
db.aggregate("custom_sum", {
start: 0,
step: (total, value) => total + (value || 0),
});
// Custom average with null handling
db.aggregate("custom_avg", {
start: { sum: 0, count: 0 },
step: (acc, value) => {
if (value != null) {
acc.sum += value;
acc.count += 1;
}
return acc;
},
result: (acc) => (acc.count > 0 ? acc.sum / acc.count : null),
});
// Standard deviation
db.aggregate("stddev", {
start: { values: [] },
step: (acc, value) => {
if (value != null) acc.values.push(value);
return acc;
},
result: (acc) => {
if (acc.values.length === 0) return null;
const mean = acc.values.reduce((a, b) => a + b) / acc.values.length;
const variance =
acc.values.map((x) => Math.pow(x - mean, 2)).reduce((a, b) => a + b) /
acc.values.length;
return Math.sqrt(variance);
},
});
// Median
db.aggregate("median", {
start: { values: [] },
step: (acc, value) => {
if (value != null) acc.values.push(value);
return acc;
},
result: (acc) => {
if (acc.values.length === 0) return null;
acc.values.sort((a, b) => a - b);
const mid = Math.floor(acc.values.length / 2);
return acc.values.length % 2 === 0
? (acc.values[mid - 1] + acc.values[mid]) / 2
: acc.values[mid];
},
});
// Group concat with custom separator
db.aggregate("group_concat_custom", {
start: () => ({ values: [], separator: null }),
step: (acc, value, separator) => {
if (value != null) acc.values.push(value);
if (separator != null && acc.separator === null) {
acc.separator = separator;
}
return acc;
},
result: (acc) => acc.values.join(acc.separator || ","),
});
// Usage
const tags = db
.prepare(
`
SELECT group_concat_custom(tag, ' | ') as all_tags
FROM post_tags
WHERE post_id = ?
`,
)
.get(postId);
// Aggregate into JSON array
db.aggregate("json_array_agg", {
start: [],
step: (acc, value) => {
if (value != null) acc.push(value);
return acc;
},
result: (acc) => JSON.stringify(acc),
});
// Aggregate into JSON object
db.aggregate("json_object_agg", {
start: {},
step: (acc, key, value) => {
if (key != null) acc[key] = value;
return acc;
},
result: (acc) => JSON.stringify(acc),
});
// Usage
const userPrefs = db
.prepare(
`
SELECT json_object_agg(key, value) as preferences
FROM user_settings
WHERE user_id = ?
`,
)
.get(userId);
Aggregate functions can be used as window functions in SQLite:
// Cumulative sum aggregate
db.aggregate("cumsum", {
start: 0,
step: (sum, value) => sum + (value || 0),
});
// Use as window function
const results = db
.prepare(
`
SELECT
date,
amount,
cumsum(amount) OVER (ORDER BY date) as running_total
FROM transactions
WHERE account_id = ?
`,
)
.all(accountId);
SQLite supports loadable extensions to add functionality at runtime.
// Must be enabled in constructor
const db = new DatabaseSync("myapp.db", {
allowExtension: true,
});
// Enable extension loading
db.enableLoadExtension(true);
// Load an extension
db.loadExtension("./extensions/vector.so");
// Disable for security
db.enableLoadExtension(false);
const path = require("path");
const os = require("os");
function loadExtension(db, extensionName) {
// Determine platform-specific extension
let ext;
switch (os.platform()) {
case "win32":
ext = ".dll";
break;
case "darwin":
ext = ".dylib";
break;
default:
ext = ".so";
}
const extensionPath = path.join(__dirname, "extensions", extensionName + ext);
db.enableLoadExtension(true);
try {
db.loadExtension(extensionPath);
console.log(`Loaded extension: ${extensionName}`);
} finally {
db.enableLoadExtension(false);
}
}
// Usage
loadExtension(db, "vector"); // Loads vector.so/.dll/.dylib
loadExtension(db, "crypto"); // Loads crypto extension
// After loading vector extension
db.exec(`
CREATE VIRTUAL TABLE products_vec USING vec0(
embedding float[384]
);
`);
// Insert embeddings
const insertVec = db.prepare(`
INSERT INTO products_vec(rowid, embedding) VALUES (?, ?)
`);
// Search similar vectors
const similar = db
.prepare(
`
SELECT rowid, distance
FROM products_vec
WHERE embedding MATCH ?
ORDER BY distance
LIMIT 10
`,
)
.all(queryEmbedding);
// FTS5 is built into this package
db.exec(`
CREATE VIRTUAL TABLE articles_fts USING fts5(
title,
content,
tags,
tokenize = 'porter unicode61'
);
`);
// Populate FTS table
db.exec(`
INSERT INTO articles_fts(title, content, tags)
SELECT title, content, tags FROM articles;
`);
// Search
const results = db
.prepare(
`
SELECT title, snippet(articles_fts, 1, '<b>', '</b>', '...', 32) as excerpt
FROM articles_fts
WHERE articles_fts MATCH ?
ORDER BY rank
`,
)
.all("sqlite NEAR extension");
db.function("safe_divide", (a, b) => {
if (b === 0) {
throw new Error("Division by zero");
}
return a / b;
});
// More graceful error handling
db.function("safe_json_parse", (jsonStr) => {
try {
return JSON.stringify(JSON.parse(jsonStr));
} catch (e) {
return null; // Return null instead of throwing
}
});
// Deterministic functions can be optimized by SQLite
db.function(
"expensive_calculation",
{
deterministic: true, // SQLite can cache results
},
(input) => {
// Complex calculation
return complexMath(input);
},
);
// Avoid creating closures in hot paths
const cache = new Map();
db.function("cached_lookup", (key) => {
if (!cache.has(key)) {
cache.set(key, expensiveLookup(key));
}
return cache.get(key);
});
// Test helper
function testFunction(db, functionName, testCases) {
for (const { input, expected } of testCases) {
const sql = `SELECT ${functionName}(${input.map(() => "?").join(",")}) as result`;
const result = db.prepare(sql).get(...input);
if (result.result !== expected) {
throw new Error(
`${functionName}(${input.join(", ")}) = ${result.result}, expected ${expected}`,
);
}
}
console.log(`✓ ${functionName} passed all tests`);
}
// Test custom functions
testFunction(db, "double", [
{ input: [5], expected: 10 },
{ input: [0], expected: 0 },
{ input: [-3], expected: -6 },
]);