D1 Specifics

D1 Specifics

Reference for Cloudflare D1 behaviour that differs from standard SQLite. Load this when troubleshooting D1 issues or when you need to write raw SQL against D1.

D1 vs Standard SQLite

JSON in D1

JSON functions are always available (no extension loading needed).

Storage

JSON is stored as TEXT columns. Drizzle handles serialisation with { mode: 'json' }.

Extraction Functions

Type Coercion

Generated Columns from JSON

D1 supports generated columns — extract JSON fields as indexable columns:

CREATE TABLE sensor_data (
  raw_data TEXT,
  location AS (json_extract(raw_data, '$.location')) STORED
);
CREATE INDEX idx_location ON sensor_data(location);

JSON Gotcha

json_extract() throws malformed JSON (error 9015) if the column contains non-JSON text. Guard with json_valid():

SELECT * FROM events
WHERE json_valid(metadata) AND json_extract(metadata, '$.country') = 'AU'

Query Result Formats

.all<T>() — Array of row objects

const { results, success, meta } = await env.DB
  .prepare("SELECT * FROM users WHERE role = ?")
  .bind("admin")
  .all<UserRow>()
// results: UserRow[]
// meta: { duration, rows_read, rows_written, last_row_id, changes, size_after }

.first() — Single row or null

const row = await env.DB.prepare("SELECT * FROM users WHERE id = ?").bind(id).first()
// row: Record<string, unknown> | null

// With column name — returns scalar:
const count = await env.DB.prepare("SELECT COUNT(*) as count FROM users").first('count')
// count: number | null

.run() — Execute mutation (no rows returned)

const result = await env.DB
  .prepare("INSERT INTO users (id, name) VALUES (?, ?)")
  .bind(id, name)
  .run()
// result: { success, meta: { changes, last_row_id, ... } }

.raw() — Array of arrays (no column names)

const rows = await env.DB.prepare("SELECT id, name FROM users").raw()
// rows: [["abc", "Alice"], ["def", "Bob"]]

// With column names:
const rows = await env.DB.prepare("SELECT id, name FROM users").raw({ columnNames: true })
// rows: [["id", "name"], ["abc", "Alice"], ["def", "Bob"]]

.batch() — Multiple statements in one transaction

const [r1, r2] = await env.DB.batch([
  env.DB.prepare("INSERT INTO users VALUES (?, ?)").bind(id1, name1),
  env.DB.prepare("INSERT INTO users VALUES (?, ?)").bind(id2, name2),
])
// Returns: D1Result[] — one per statement, all in single transaction

Batch Insert Calculation

D1's 100 parameter limit means: max_rows_per_insert = Math.floor(100 / columns_per_row)

Symptoms of hitting the limit: silent failure, partial data, or cryptic "Failed to insert" error.