A template literal for working with Cloudflare Durable Objects SQL storage
npm install durable-objects-sql-tagdurable-objects-sql-tag
A library for working with SQLite in Cloudflare Durable Objects.
npm install durable-objects-sql-tag
- Type-safe SQL template literals with parameterized queries
- Database wrapper with convenient query helpers (queryOne, queryMany, etc.)
- Built-in migration system with schema versioning
- SQL injection prevention through proper parameterization
- Support for all SQLite types including binary data (ArrayBuffer, Uint8Array)
The sql template literal builds parameterized SQL queries:
``ts
import { sql } from "durable-objects-sql-tag";
// Simple query with parameters
const query = sqlSELECT * FROM users WHERE id = ${userId};
const { query: sqlString, values } = query.build();
// sqlString: "SELECT * FROM users WHERE id = ?"
// values: [userId]
// Using sql.join() for IN clauses
const ids = [1, 2, 3];
const listQuery = sqlSELECT * FROM users WHERE id IN (${sql.join(ids)});
// Builds: "SELECT * FROM users WHERE id IN (?, ?, ?)"
// Composing fragments
const whereClause = sqlWHERE status = ${"active"};SELECT * FROM users ${whereClause}
const fullQuery = sql;`
The wrapDatabase function provides a convenient API for executing queries:
`ts
import { DurableObject } from "cloudflare:workers";
import { sql, wrapDatabase, type MigrationVersionDefinition } from "durable-objects-sql-tag";
const migrations: MigrationVersionDefinition[] = [
{
name: "Create users table",
migrate(db) {
db.run(sql
CREATE TABLE users (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
) STRICT
);
},
},
];
export class MyDurableObject extends DurableObject {
private db;
constructor(ctx: DurableObjectState, env: Env) {
super(ctx, env);
this.db = wrapDatabase(ctx.storage, { migrations });
}
async fetch(request: Request): Promise
// Query a single row (throws if not exactly one row)
const user = this.db.queryOne<{ id: string; name: string }>(
sqlSELECT * FROM users WHERE id = ${userId}
);
// Query zero or one row
const maybeUser = this.db.queryNoneOrOne<{ id: string; name: string }>(
sqlSELECT * FROM users WHERE id = ${userId}
);
// Query multiple rows
const users = this.db.queryMany<{ id: string; name: string }>(
sqlSELECT * FROM users WHERE status = ${"active"}
);
// Execute a write operation
const { rowsRead, rowsWritten } = this.db.run(
sqlINSERT INTO users (id, name, email) VALUES (${id}, ${name}, ${email})
);
// Execute without expecting rows (throws if rows returned)
this.db.queryNone(sqlDELETE FROM users WHERE id = ${userId});
return new Response("OK");
}
}
`
| Method | Description |
|--------|-------------|
| queryOne | Returns exactly one row. Throws if 0 or 2+ rows. |queryNoneOrOne
| | Returns one row or null. Throws if 2+ rows. |queryMany
| | Returns all matching rows as an array. |queryNone(statement)
| | Executes statement. Throws if any rows returned. |run(statement)
| | Executes statement. Returns { rowsRead, rowsWritten }. |pragma(name)
| | Executes PRAGMA, returns single value. |pragmaFull
| | Executes PRAGMA, returns full result set. |transactionSync(fn)
| | Runs function in a synchronous transaction. |
Migrations run automatically when wrapDatabase is called. The system tracks applied migrations in a metadata table:
`tsCREATE TABLE users (...) STRICT
const migrations: MigrationVersionDefinition[] = [
{
name: "Initial schema",
migrate(db) {
db.run(sql);CREATE TABLE posts (...) STRICT
},
},
{
name: "Add posts table",
beforeMigrate(db) {
// Optional: run before migration
},
migrate(db) {
db.run(sql);
},
},
];
// Check migration status without applying
import { getMigrationStatus } from "durable-objects-sql-tag";
const { currentVersion, targetVersion, migrationsToApply } = getMigrationStatus(
ctx.storage,
migrations
);
`
Add callbacks for logging or instrumentation:
`ts
const db = wrapDatabase(ctx.storage, {
migrations,
beforeQuery: (query) => console.log("Executing:", query),
afterQuery: (query, result) => console.log("Result:", result),
beforeMigration: (migrations) => console.log("Applying:", migrations),
});
// Or add callbacks to an existing wrapper
const dbWithLogging = db.withCallbacks({
beforeQuery: (query) => console.log(query),
});
`
Input types (Primitive):
- stringnumber
- boolean
- (stored as "true"/"false" strings)null
- undefined
- (stored as null)ArrayBuffer
- Uint8Array
-
Output types (SqlStorageValue):
- stringnumber
- null
- ArrayBuffer`
-