Cloudflare D1 Dialect

Overview

Cloudflare D1 is SQLite run by
Cloudflare: the same grammar, the same types, the same ? placeholders. So
this dialect builds every statement with SQLiteDialect and replaces only the
part that runs it. Where the SQLite dialect opens a file through
better-sqlite3, this one sends the statement to Cloudflare — through a
Worker binding, or over the D1 HTTP API.

The composition is a Proxy, not a subclass (src/dialects/d1/index.ts).
SQLiteDialect keeps its connection in a private field a subclass could not
replace, so createD1Dialect() wraps a never-connected SQLiteDialect and
routes the driver methods (connect, query, queryStream, the transaction
methods, runRebuildSteps) to a D1 driver while every inherited builder still
runs against D1. D1Dialect is the constructor form of the same thing, so
dialect: 'd1' reaches it like any other dialect.

Property	Value
Dialect name	`d1` (`SqlDialect.D1`); the instance reports `name: 'sqlite'`, `library: 'd1'`
Extends	`SQLiteDialect` (by proxy; lineage `d1 → sqlite`)
Driver	none — a `D1Database` binding, or the global `fetch`
Cloud SDK	none
Default port	none (a binding, or HTTPS to `api.cloudflare.com`)
Local runtime	`wrangler dev` (Miniflare's local D1) through the binding; no Docker image

Quick start

Install

$ npm install ts-prorm-orm

$ pnpm add ts-prorm-orm

$ yarn add ts-prorm-orm

$ bun add ts-prorm-orm

No driver package: the dialect talks to the service over fetch.

Connect

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'd1',
  dialectOptions: {
    accountId: process.env.CF_ACCOUNT_ID,
    databaseId: process.env.CF_D1_DATABASE_ID,
    token: process.env.CF_API_TOKEN, // or binding: env.DB inside a Worker
  },
});

Define a model

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

Query

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Generated SQL

Each block is the statement the model call above it hands the dialect's query(), captured with the driver stubbed out. The model layer inlines the values.

Create table

db.sync() for the model in the Quick start:

CREATE TABLE

Insert, update and delete

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

Upsert

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

upsert() also reads the row by primary key before and after the write, to return the instance.

Pagination

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Transaction

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  await User.update({ name: 'Grace Hopper' }, { where: { email: 'grace@example.com' }, transaction: t });
});

[!WARNING] No savepoints
Nested transactions need savepoints, and sqlite has none. Run the work in a single transaction, or restructure it so nothing nests.

[!CAUTION] Not atomic
Each statement is its own request: a failure part-way through leaves the earlier statements applied. transaction({ requireAtomic: true }) refuses this dialect instead.

Type mapping for the portable descriptors: STRING → VARCHAR(255), auto-increment INTEGER → INTEGER, BOOLEAN → INTEGER, DATE → DATETIME, UUID → TEXT, JSON → TEXT. The type comparison lines the major engines up side by side.

Connection

Inside a Worker: the binding

import { Prorm } from 'ts-prorm-orm';

export default {
  async fetch(request: Request, env: { DB: unknown }) {
    const db = new Prorm({
      dialect: 'd1',
      dialectOptions: { binding: env.DB }, // a D1Database from wrangler.toml
    });
    await db.connect();
    return new Response('ok');
  },
};

With a binding no account id, database id or token is needed: the binding is
already scoped to one database and authenticated by the platform.
getConnection() returns the binding.

Everywhere else: the HTTP API

import { Prorm } from 'ts-prorm-orm';

const db = new Prorm({
  dialect: 'd1',
  dialectOptions: {
    accountId: process.env.CF_ACCOUNT_ID,
    databaseId: process.env.CF_D1_DATABASE_ID, // as `wrangler d1 create` printed it
    token: process.env.CF_API_TOKEN, // needs D1:Edit
    timeoutMs: 30_000, // the default; a statement past it is abandoned
  },
});

Each statement is one POST …/accounts/<accountId>/d1/database/<databaseId>/query
with { sql, params } and a bearer token. database is accepted as an alias
for databaseId. apiBase and fetch replace the API root and the fetch
implementation (for a test double). connect() opens nothing; it only checks
that a binding or all three HTTP fields were given, so a misconfiguration
fails at connect time rather than three layers into a migration.

A refused statement is a D1Error with Cloudflare's status and errors
(code, message). Code 10000 is the token, 7003 a path that does not
resolve, and no such table a migration that has not been run.

TLS

The HTTP API is HTTPS to api.cloudflare.com; inside a Worker there is no
network hop at all. There is no ssl option.

What differs from SQLite

No interactive transactions. D1 has no BEGIN that can be held open
across an await; its only atomic unit is batch(), a list of statements
decided up front. startTransaction() therefore returns a bookkeeping
shell: the statements inside transaction(async (t) => …) run immediately,
one at a time, and rollback() cannot undo them — it logs a warning saying
so. Code that needs atomicity on D1 uses the binding's batch() directly.

Table rebuilds are atomic. runRebuildSteps() (the ALTER emulation a
column change needs) sends every step as one unit — batch() on a binding,
or semicolon-joined SQL to /query, which D1 rolls back as a whole if any
step fails — so the PRAGMA defer_foreign_keys at its head holds for the
whole rebuild.

No cursor. queryStream() throws; Model.iterate() and
Model.stream() page through findAll() and work.

PRAGMA foreign_keys = … is accepted and dropped. D1 manages foreign
key enforcement itself and rejects the assignment; the migration runner
brackets its work with it, so it is ignored rather than failing every
migration. PRAGMA defer_foreign_keys is honoured.

Positional parameters only. D1 binds by position, so named parameters
(:name) throw D1Error rather than being bound in object-key order.
Booleans, Dates and plain objects are coerced the way better-sqlite3 needs
(1/0, ISO string, JSON).

Results. A reader (SELECT, WITH, EXPLAIN, a read-only PRAGMA, a
statement with RETURNING) reports rows; a writer reports rowCount from
meta.changes and lastInsertRowid from meta.last_row_id (omitted when it
is 0). Several statements sent at once come back merged: all rows, the
summed change count, the last row id.

Capabilities

Computed from the code by name (transactionCapabilitiesFor('d1'),
whereSupportOf(), sqlObjectCapabilitiesFor('d1')) — see the
capability matrix for how.

Capability	D1
Transactions atomic	no (`atomic: false`; `requireAtomic` refuses it)
Savepoints	no — a nested `transaction()` throws
Isolation	`SERIALIZABLE` (SQLite's; not adjustable)
`RETURNING`	not emitted by the builders; `returning` is accepted and ignored
Upsert	`INSERT … ON CONFLICT (…) DO UPDATE SET … = excluded.…`
JSON	`JSON`/`JSONB` columns map to `TEXT`; SQLite's JSON1 helpers apply
Vector	refused: SQLite has no vector type (Turso's `F32_BLOB` is not on D1)
WHERE operators	the SQLite profile: 32 keys, `$and`/`$or`/`$not`, `$exists`, subqueries
SQL objects	SQLite family: views and triggers; no routines, sequences or RLS

One caveat on the first two rows: the D1 driver reports name: 'sqlite', so
capabilitiesOf(instance) (the by-instance lookup) answers for SQLite —
savepoints and atomic transactions — while the by-name lookup for 'd1'
correctly says neither. Trust this page and the by-name lookup.

How a statement travels

The SQL is built once by the SQLite code and executed by whichever transport
the options named; the response is reshaped into the QueryResult the rest of
the library reads.

Limitations

No interactive transactions and no savepoints (above). The
transaction() API works but is not atomic.

No streaming.

No local Docker image: the local runtime is wrangler dev, which serves the
binding from Miniflare's SQLite.

getConnection() is null over HTTP.

Testing

tests/dialects/d1.test.ts drives the dialect with a fake binding and a fake
fetch, so nothing touches Cloudflare. There is no integration suite.

Cloudflare D1 Dialect

Overview#

Quick start#

Install#

Connect#

Define a model#

Query#

Generated SQL#

Create table#

Insert, update and delete#

Upsert#

Pagination#

Transaction#

Connection#

Inside a Worker: the binding#

Everywhere else: the HTTP API#

TLS#

What differs from SQLite#

Capabilities#

How a statement travels#

Limitations#

Testing#

See also#