d1-sql-tag


A template literal for working with Cloudflare D1
database.

npm install d1-sql-tag

- Changelog

Usage with Cloudflare Workers

If you have created a D1 database and configured it with the binding name DB,
in wrangler.toml, you can create a template literal tag with createD1SqlTag().

We also set up a callback to log stats for each query, like so:

``
D1 batch: 286ms · 1 queries
1: SELECT ?1 AS message
↳ 0.3053ms · 0 changed · 0 read · 0 written
`

`ts
import { createD1SqlTag, logQueryResults } from "d1-sql-tag";

export interface Env {
DB: D1Database;
}

function createSqlTag(db: D1Database) {
return createD1SqlTag(db, {
afterQuery(batchId, queries, results, duration) {
logQueryResults(queries, results, duration);
},
});
}

export default {
async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise {
const sql = createSqlTag(env.DB);
const result = await sqlSELECT ${"hello world"} AS message.all<{
message: string;
}>();
return new Response(Message: ${result.results[0].message});
},
};
`

Usage with Hono on Cloudflare Workers

If you have created a D1 database and configured it with the binding name DB,
in wrangler.toml, you can create a template literal tag with createD1SqlTag().

We also set up a callback to log stats for each query, like so:

`
D1 batch: 286ms · 1 queries
1: SELECT ?1 AS message
↳ 0.3053ms · 0 changed · 0 read · 0 written
`

Additionally, we use hono/timing
to send Server-Timing headers for the total response time, how long we wait for each batch, and how
long each query takes. Open the network tab in your browser's devtools, select
the request and look at the "Timing" tab.

`ts
import { createD1SqlTag, logQueryResults } from "d1-sql-tag";
import { Hono, type Context } from "hono";
import { endTime, setMetric, startTime, timing } from "hono/timing";

type Bindings = {
DB: D1Database;
};

const app = new Hono<{ Bindings: Bindings }>();

function createSqlTag(c: Context<{ Bindings: Bindings }>) {
return createD1SqlTag(c.env.DB, {
beforeQuery(batchId, queries) {
startTime(c, db-${batchId});
},
afterQuery(batchId, queries, results, duration) {
endTime(c, db-${batchId});
results.forEach((result, i) => {
setMetric(c, db-${batchId}-query-${i + 1}, result.meta.duration);
});
logQueryResults(queries, results, duration);
},
});
}

app.use("*", timing());

app.get("/", async (c) => {
const sql = createSqlTag(c);
const result = await sqlSELECT ${"hello world"} AS message.all<{
message: string;
}>();
return c.text(Message: ${result.results[0].message});
});

export default app;
`

Testing with Mock SQL Tag

For testing, you can use createMockSqlTag to create a sql tag that properly builds
queries but delegates execution to your mock implementation. This allows you to:

- Verify that correct SQL queries are generated
- Mock responses for different queries
- Test without a real D1 database

The function is generic and preserves your mock types via sql.handler, so you can
use vitest/jest mock methods without type casting.

$3

`ts
import { createMockSqlTag } from "d1-sql-tag";
import { vi, expect, test } from "vitest";

test("queries user by id", async () => {
const sql = createMockSqlTag({
all: vi.fn().mockResolvedValue({ results: [], success: true, meta: {} }),
run: vi.fn().mockResolvedValue({ success: true, meta: {} }),
batch: vi.fn().mockResolvedValue([]),
});

// Mock specific response - no type casting needed!
sql.handler.all.mockResolvedValueOnce({
results: [{ id: 1, name: "Alice" }],
success: true,
meta: {},
});

const result = await sqlSELECT * FROM users WHERE id = ${1}.all<{
id: number;
name: string;
}>();

// Verify the query was built correctly
expect(sql.handler.all).toHaveBeenCalledWith("SELECT * FROM users WHERE id = ?1", [1]);

// Verify the response
expect(result.results[0].name).toBe("Alice");
});
`

$3

`ts
interface MockSqlTagHandler {
all(query: string, values: Primitive[]): Promise>;
run(query: string, values: Primitive[]): Promise;
batch(statements: Array<{ query: string; values: Primitive[] }>): Promise[]>;
}
``

License

MIT