---
title: "SQLite and D1"
description: "The SQLite entry: codecs, functions, table DDL, drivers and Cloudflare D1."
---

> Documentation Index
> Fetch the complete documentation index at: https://effect-orm.maple.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# SQLite and D1

Import `@maple-dev/effect-orm/sqlite` for the shared query builder, SQLite codecs and functions.
Compilation uses positional `?` bindings. Each occurrence of a parameter gets its own binding.
The package opens no connection: use the Effect `SqlClient` you already provide.

## Define and compile a query

```ts title="sqlite-quickstart.ts"
import * as SQLite from "@maple-dev/effect-orm/sqlite"

export const Acks = SQLite.table("acknowledgments", {
	id: SQLite.text,
	org_id: SQLite.text,
	entry_id: SQLite.text,
}, { tenantColumn: "org_id" })

export const byOrg = SQLite.from(Acks)
	.select("entry_id")
	.where(($) => [$.org_id.eq(SQLite.param.string("orgId"))])
	.orderBy(["entry_id", "asc"])

export const compiled = SQLite.compileUnsafe(byOrg, { orgId: "org_1" })
// compiled.parameters is ["org_1"]; compiled.tenantScope is "single-tenant".
```

`table(name, columns, options?)` is a query schema only: it carries no DDL.

## Declare a table with its DDL

Pass `{ columns, primaryKey, indexes }` to `table` to declare the table's DDL too. The value is
still a query `Table`. `sqliteEntitiesOf` from `/schema` checks a set of tables as one schema,
and `renderSqliteSchema` writes their `CREATE TABLE` and `CREATE INDEX` statements:

```ts title="sqlite-ddl.ts"
import { Effect } from "effect"
import * as S from "@maple-dev/effect-orm/schema"
import * as SQLite from "@maple-dev/effect-orm/sqlite"

export const ShortLinks = SQLite.table("short_link", {
	columns: {
		id: SQLite.text,
		code: SQLite.text,
		orgId: SQLite.column(SQLite.nullable(SQLite.text), { name: "org_id" }),
		clickCount: SQLite.column(SQLite.integer, { name: "click_count", default: 0 }),
		createdAt: SQLite.column(SQLite.text, { name: "created_at", defaultExpr: "(CURRENT_TIMESTAMP)" }),
	},
	primaryKey: ["id"],
	indexes: [SQLite.uniqueIndex("short_link_code", ["code"]), SQLite.index("short_link_org", ["orgId"])],
	tenantColumn: "orgId",
})

export const statements = S.sqliteEntitiesOf([ShortLinks]).pipe(Effect.map(S.renderSqliteSchema))
// CREATE TABLE IF NOT EXISTS "short_link" (
// 	"id" text PRIMARY KEY NOT NULL,
// 	"code" text NOT NULL,
// 	"org_id" text,
// 	"click_count" integer NOT NULL DEFAULT 0,
// 	"created_at" text NOT NULL DEFAULT (CURRENT_TIMESTAMP)
// )
// CREATE UNIQUE INDEX IF NOT EXISTS "short_link_code" ON "short_link" ("code")
// CREATE INDEX IF NOT EXISTS "short_link_org" ON "short_link" ("org_id")
```

- A column is `NOT NULL` unless its type is `nullable(...)`. A primary key column cannot be
  nullable: SQLite would accept NULL keys in it.
- One primary key column is written inline (`PRIMARY KEY`); several become a table constraint
  `PRIMARY KEY ("a", "b")`. SQLite stores no constraint name, so there is none to give.
- `default` takes a literal of the column type. `defaultExpr` takes SQL as written, so add the
  parentheses SQLite requires around an expression, or a DSL callback, which is parenthesized
  for you. Either lets an insert leave the column out.
- `unique: true` writes an inline `UNIQUE` constraint, as drizzle-kit 1.0 does for `.unique()`.
  Its name, `<table>_<column>_unique` or the string you give, is kept in the schema entities;
  SQLite itself names the backing index `sqlite_autoindex_...`.
- Column types declare their SQLite type: `text`, `timestamp` and `json` are `text`; `integer`
  and `boolean` are `integer`; `real` is `real`; `blob` is `blob`. A `custom(sql, schema)` type
  declares `sql`, for example `numeric`.
- `table(name, { external: true, columns })` declares a table another tool owns: no DDL.

The tests apply this DDL and drizzle-kit's SQL for the same tables to real SQLite, and compare
`PRAGMA table_info`, `index_list` and `index_xinfo`. One difference is deliberate: drizzle-kit
1.0 drops `NOT NULL` from a primary key column, so SQLite then accepts a NULL key in a `text`
key column. This entry keeps `NOT NULL`.

`effect-orm generate` with `dialect: "sqlite"` diffs these tables against the migrations folder
and writes the next migration, in the drizzle-kit 1.0 folder layout. See
[SQLite migrations](https://effect-orm.maple.dev/sqlite/database/migrations/#sqlite). effect-orm does not apply SQLite migrations yet,
and has no SQLite introspection or drift check: apply the folder with a tool that reads
drizzle-kit folders, such as Alchemy's D1 resource. `WITHOUT ROWID`,
`STRICT`, `AUTOINCREMENT`, partial indexes, foreign keys and `CHECK` constraints are not
declared yet.

The entry exports `text`, `integer` (a safe JavaScript integer), `real` (finite numbers),
`boolean` (0/1), `blob`, `timestamp`, `json(schema)`, `nullable`, `custom` and `brand`.

`integer` refuses a stored value past 2^53 with a decode error: as a JavaScript number it would
no longer be the stored integer (D1 rounds it; node:sqlite refuses it). Use `custom` with a
bigint codec for such columns.

`timestamp` stores an instant as SQLite's own text form, `YYYY-MM-DD HH:MM:SS.SSS` in UTC, the
form `strftime('%Y-%m-%d %H:%M:%f')` and `datetime(..., 'subsec')` write. It reads that form
and ISO-8601; text without a zone is UTC. SQLite compares text by bytes, so a timestamp column
orders by time only when every value in it has one form: write them through the builder or with
`strftime('%Y-%m-%d %H:%M:%f', ...)`. `CURRENT_TIMESTAMP` writes no milliseconds
(`2026-01-01 00:00:00`), which compares as earlier than the same instant written with them, so
a `timestamp` column refuses it as a `defaultExpr`; use `defaultExpr: SQLite.timestampNow`.
`blob` accepts the Uint8Array wire form from Node and the array-of-bytes form from D1.
`json` always uses the supplied schema to validate decoded JSON.
Use `text` when a stored date/time must remain a string.

SQLite aggregates include `count`, `countDistinct`, `countIf`, `sum`, `avg`, `min` and `max`.
The last four return NULL over an empty input. Scalar helpers include `lower`, `upper`,
`length` and `coalesce`. SQLite's own text and case rules apply. Foreign built-in function
sets, such as ClickHouse's `count()`, fail compilation instead of producing invalid SQL.

## Execute on SQLite

Install a compatible Effect SQL driver, for example `@effect/sql-sqlite-node` on Node 24+.
No driver is a runtime dependency of this package. Use current SQLite with RETURNING support.

```ts title="sqlite-node.ts"
import * as SqliteClient from "@effect/sql-sqlite-node/SqliteClient"
import { Layer } from "effect"
import * as Db from "@maple-dev/effect-orm/database"
import { sqliteDialect } from "@maple-dev/effect-orm/sqlite"

export const SQLiteLive = Db.layerSqlClient({
	dialect: sqliteDialect,
	requireTenantScope: true,
}).pipe(Layer.provide(SqliteClient.layer({ filename: ":memory:" })))
```

`Db.run(query, params)` compiles for the database dialect and decodes the selected rows.
SQLite transactions and nested savepoints use the supplied client's `withTransaction`.
`serializable` is the only accepted explicit isolation level. Per-transaction access modes
and deferrable settings are refused. The driver must support the declared transaction behavior;
use the D1 profile for a D1 client.

## Execute on D1

Install `@effect/sql-d1` with a compatible Effect version. Provide the Worker's D1 binding:

```ts title="sqlite-d-one.ts"
import * as D1Client from "@effect/sql-d1/D1Client"
import { Layer } from "effect"
import * as Db from "@maple-dev/effect-orm/database"
import { d1Dialect } from "@maple-dev/effect-orm/sqlite"

export const d1DatabaseLayer = (db: D1Client.D1ClientConfig["db"]) =>
	Db.layerSqlClient({ dialect: d1Dialect, requireTenantScope: true }).pipe(
		Layer.provide(D1Client.layer({ db })),
	)
```

Use `d1Dialect` when compiling a query separately as well:
`SQLite.compile(query, params, { dialect: SQLite.d1Dialect })`, or provide it once for an Effect
with `Effect.provideService(SQLite.CurrentDialect, SQLite.d1Dialect)`. A query compiled for `sqlite`
cannot later run on a database configured as `d1`; pass the builder to `Db.run` instead.

D1 compilation refuses more than [100 bound parameters per statement](https://developers.cloudflare.com/d1/platform/limits/).
It never splits a write implicitly. Chunk an import explicitly if needed; several chunks do
not make one atomic write. SQLite compilation uses its modern default limit of 32766; a driver
with a lower limit needs a custom dialect profile.

`Database.transaction` on D1 returns `TransactionUnsupported` before executing its body.
D1's [atomic batch API](https://developers.cloudflare.com/d1/worker-api/d1-database/#batch)
executes a fixed list of statements. It cannot run an arbitrary Effect transaction body with
reads that determine later writes. There is no ORM batch API in this first implementation.

## Safety and syntax limits

Derived schemas validate returned rows. A row with the wrong type returns
`CompiledQueryDecodeError`. Input values use each column's encoder before binding.

Tenant enforcement is **opt-in**, through `requireTenantScope: true`. A compiled query over a
tenant table must pin its `tenantColumn` to one value, or explicitly use `.crossTenant()` for
an administrative read/write. Raw SQL passed to `query` or `execute` has no tenant proof and
bypasses this guard. It is not a replacement for an application's authorization checks.

The dialect handles OFFSET without LIMIT, branch-local ordering/limits in UNION ALL, and
INSERT SELECT with ON CONFLICT. `LIKE` patterns escape `%`, `_` and `\` with `\` as on every
dialect: the builder writes `ESCAPE '\'`, which SQLite needs. A fraction is bound rather than
written as decimal text, because SQLite reads some decimals as a neighbouring double, and a
fraction compared with an `integer` column (`Amount > 9.75`) compiles as on Postgres. It refuses these unsupported forms at compile time:

- `DISTINCT ON`, row locks and `ILIKE`. Use `LIKE`, which on SQLite ignores ASCII case
  (`'acme' LIKE 'ACME'` is true, unlike Postgres and ClickHouse). For text search,
  `.contains()`, `.startsWith()` and `.endsWith()` match case as on the other dialects (they
  write `GLOB`), and `{ insensitive: true }` compares `lower()` of both sides; see
  [Text search](https://effect-orm.maple.dev/sqlite/queries/expressions/#text-search). A row lock is refused in the
  type as well: `SQLite.compile(q.forUpdate())` and `run` on a SQLite or D1 `Database` do not
  typecheck. Claim a row with a conditional `update(…).where(…).returning(…)` instead. A
  Postgres or ClickHouse function in a SQLite query, and a SQLite function in a Postgres or
  ClickHouse one, are type errors too; see
  [Checked in the type](https://effect-orm.maple.dev/sqlite/queries/params-and-compilation/#checked-in-the-type).
- Text containing U+0000. SQLite stores it, but its drivers end text there, so the value would
  read back cut short; the builder refuses it in literals and bound params.
- `ON CONSTRAINT` conflict targets. Name the indexed columns instead.
- Per-cell `DEFAULT` in a VALUES tuple. Omit a defaulted column from every row in the statement,
  or group rows by their present columns. All-default rows are not supported by the builder.
- ClickHouse FORMAT/SETTINGS and built-in functions from another dialect.

The tests run real SQLite through `@effect/sql-sqlite-node` and local workerd/D1 through
Miniflare. They require no deployed database or Cloudflare credentials. Both run the shared core
builder suite (`tests/core.sqlite.test.ts`) and every fuzz property (`tests/fuzz`).

Source: https://effect-orm.maple.dev/sqlite/specifics/overview/index.mdx