Cloudflare D1

The @effect/sql-d1 package provides Cloudflare D1 support for Effect SQL. D1 is Cloudflare's serverless SQLite database, designed for edge computing with Cloudflare Workers.

Installation

npm install @effect/sql @effect/sql-d1

Setup

Cloudflare Worker

// src/index.ts
import { Effect, Layer } from "effect"
import { SqlClient } from "@effect/sql"
import { D1Client } from "@effect/sql-d1"

interface Env {
  DB: D1Database
}

export default {
  async fetch(request: Request, env: Env, ctx: ExecutionContext) {
    const DatabaseLive = D1Client.layer({
      binding: env.DB
    })

    const program = Effect.gen(function* () {
      const sql = yield* SqlClient.SqlClient
      const users = yield* sql`SELECT * FROM users`
      return new Response(JSON.stringify(users), {
        headers: { "Content-Type": "application/json" }
      })
    })

    return Effect.runPromise(program.pipe(Effect.provide(DatabaseLive)))
  }
}

wrangler.toml

name = "my-worker"
main = "src/index.ts"
compatibility_date = "2024-01-01"

[[d1_databases]]
binding = "DB"
database_name = "my-database"
database_id = "your-database-id"

Configuration

import { D1Client } from "@effect/sql-d1"

const DatabaseLive = D1Client.layer({
  // D1 binding from Cloudflare Workers environment
  binding: env.DB,
  
  // Name transformations
  transformQueryNames: String.camelToSnake,
  transformResultNames: String.snakeToCamel
})

D1-Specific Considerations

SQLite Compatibility

D1 is based on SQLite, so it follows SQLite's syntax and limitations:

// Create table (SQLite syntax)
yield* sql`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`

No Connection Pool

D1 doesn't use connection pooling—each request gets a fresh connection. This is handled automatically.

Batch Operations

D1 supports batching multiple statements:

yield* sql.withTransaction(
  Effect.gen(function* () {
    yield* sql`INSERT INTO users ${sql.insert({ name: "Alice" })}`
    yield* sql`INSERT INTO profiles ${sql.insert({ userId: 1 })}`
  })
)

Row Limits

D1 has limits on result set sizes. Use pagination for large results:

const page = 1
const pageSize = 100

const users = yield* sql`
  SELECT * FROM users
  ORDER BY id
  LIMIT ${pageSize}
  OFFSET ${(page - 1) * pageSize}
`

Migrations

Run migrations using Wrangler:

wrangler d1 migrations apply my-database

Or programmatically:

import { D1Migrator } from "@effect/sql-d1"
import { Migrator } from "@effect/sql"

const MigratorLive = D1Migrator.layer({
  loader: Migrator.fromGlob(import.meta.glob("./migrations/*.ts"))
})

// Run during worker initialization
const program = Effect.gen(function* () {
  const migrations = yield* Migrator.Migrator
  yield* migrations
})

Local Development

Use Wrangler's local D1:

wrangler d1 execute my-database --local --command "SELECT * FROM users"

Or in your test setup:

// Using miniflare for testing
import { Miniflare } from "miniflare"

const mf = new Miniflare({
  d1Databases: ["DB"],
  modules: true
})

const db = await mf.getD1Database("DB")

Example: REST API

import { Effect, Schema } from "effect"
import { SqlClient, SqlSchema } from "@effect/sql"
import { D1Client } from "@effect/sql-d1"

// Schema
class User extends Schema.Class<User>("User")({
  id: Schema.Number,
  name: Schema.NonEmptyString,
  email: Schema.NonEmptyString
}) {}

// Handlers
const listUsers = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient
  return yield* sql<User>`SELECT * FROM users`
})

const createUser = (data: { name: string; email: string }) =>
  Effect.gen(function* () {
    const sql = yield* SqlClient.SqlClient
    yield* sql`INSERT INTO users ${sql.insert(data)}`
    const [user] = yield* sql<User>`SELECT * FROM users WHERE email = ${data.email}`
    return user
  })

// Worker
export default {
  async fetch(request: Request, env: Env) {
    const DatabaseLive = D1Client.layer({ binding: env.DB })
    const url = new URL(request.url)

    if (request.method === "GET" && url.pathname === "/users") {
      return Effect.runPromise(
        listUsers.pipe(
          Effect.map((users) => Response.json(users)),
          Effect.provide(DatabaseLive)
        )
      )
    }

    if (request.method === "POST" && url.pathname === "/users") {
      const body = await request.json()
      return Effect.runPromise(
        createUser(body).pipe(
          Effect.map((user) => Response.json(user, { status: 201 })),
          Effect.provide(DatabaseLive)
        )
      )
    }

    return new Response("Not Found", { status: 404 })
  }
}

Next Steps

• SQLite - SQLite-specific features
• LibSQL - SQLite with replication