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. For full window function support with framed windows (using OVER (ORDER BY ...)), you should provide an inverse function that reverses the effect of step:
// Cumulative sum aggregate with inverse for window function support
db.aggregate("cumsum", {
start: 0,
step: (sum, value) => sum + (value || 0),
inverse: (sum, value) => sum - (value || 0), // Required for window functions
});
// 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);
Note: Without the
inversefunction, the aggregate will still work but may be less efficient for certain window frame types.
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 },
]);