effect-sql-db

mepuka's avatarfrom mepuka

Effect patterns for SQL databases (Drizzle/Kysely/Prisma): services, transactions, retries, streaming, and observability.

⭐0stars🔀0forks📁View on GitHub🕐Updated Dec 25, 2025⬇️1downloads
[SQL and Databases]

When & Why to Use This Skill

This Claude skill provides production-ready patterns and architectural guidance for integrating SQL databases with the Effect TS ecosystem. It enables developers to build robust backend systems using Drizzle, Kysely, or Prisma by leveraging Effect's powerful features for typed error handling, automated retries, transactional integrity, and deep observability.

Use Cases

  • Case 1: Implementing type-safe database services that centralize client management and map low-level driver errors to meaningful domain errors.
  • Case 2: Managing complex database transactions with built-in exponential backoff retry logic to handle transient failures like deadlocks.
  • Case 3: Enhancing system monitoring by implementing spans for distributed tracing and logging thresholds for slow-running SQL queries.
  • Case 4: Efficiently processing large-scale datasets using Effect's Stream module to prevent memory exhaustion during heavy read operations.
  • Case 5: Creating highly testable database layers by utilizing Effect Layers to swap real database clients with in-memory mocks during testing.
nameeffect-sql-db
descriptionEffect patterns for SQL databases (Drizzle/Kysely/Prisma): services, transactions, retries, streaming, and observability.
allowed-toolsRead, Grep, Glob, Edit, Write

SQL & Databases

When to use

  • You are integrating a SQL client (Drizzle, Kysely, Prisma, mysql2/pg) with Effect
  • You want typed errors, retries, transactions, and observability

Provide DB config via Layer

import { Config, Effect, Layer } from "effect"

class DbConfig extends Effect.Service<DbConfig>()("DbConfig", {
  sync: () => ({
    url: process.env.DATABASE_URL ?? ""
  })
}){}

// Provide once at app boundary
export const DbConfigLive = DbConfig.Default

Wrap a client in a Service (typed errors)

import { Effect } from "effect"

class DatabaseError extends Data.TaggedError("DatabaseError")<{ cause: unknown }>{}

export class Database extends Effect.Service<Database>()("Database", {
  effect: Effect.gen(function* () {
    // drizzle() / new PrismaClient() / kysely instance
    const client = makeDbClient() // your factory (validated url)
    return {
      // use-pattern ensures client is passed from a single place
      use: <A>(cb: (c: typeof client) => Promise<A>) =>
        Effect.tryPromise({ try: () => cb(client), catch: (cause) => new DatabaseError({ cause }) })
    }
  })
}){}

Real-world snippet: Drizzle wrapper with use(cb)

// Pattern adapted from Cap
export class Database extends Effect.Service<Database>()("Database", {
  effect: Effect.gen(function* () {
    return {
      use: <T>(cb: (_: DbClient) => Promise<T>) =>
        Effect.tryPromise({ try: () => cb(db()), catch: (cause) => new DatabaseError({ cause }) })
    }
  })
}){}

Transactions with retry-on-deadlock

// Generic transactional helper with selective retry
const isDeadlock = (e: unknown) => typeof e === "object" && e !== null && (e as any).code === "1213" // MySQL

export const transactional = <A>(eff: Effect.Effect<A, DatabaseError, Database>) =>
  Effect.gen(function* () {
    const db = yield* Database
    return yield* Effect.tryPromise({
      try: () => db.use((c) => c.transaction(async (tx) => await Effect.runPromise(eff))),
      catch: (cause) => new DatabaseError({ cause })
    })
  }).pipe(
    Effect.retry({ schedule: Schedule.exponential("100 millis"), times: 3, while: isDeadlock })
  )

Query helpers (Drizzle examples)

// Fallback to existing column if value is undefined
export const updateIfDefined = <T>(v: T | undefined, col: AnyMySqlColumn) =>
  sql`COALESCE(${v === undefined ? sql`NULL` : v}, ${col})`

// JSON_EXTRACT helper that returns string | undefined
export function jsonExtractString(column: MySqlColumn<any>, field: string) {
  return sql<string | undefined>`JSON_UNQUOTE(JSON_EXTRACT(${column}, ${`$.${field}`}))`
}

Streaming large result sets

// If your driver supports async iteration over rows
const rowsAsync = client.executeStream("SELECT * FROM events") // placeholder
const stream = Stream.fromAsyncIterable(rowsAsync).pipe(
  Stream.mapEffect(processRow, { concurrency: 16 })
)
yield* Stream.runDrain(stream)

Observability: spans and slow query logging

const withSqlSpan = <A, E, R>(name: string, eff: Effect.Effect<A, E, R>) =>
  eff.pipe(Effect.withSpan(name, { attributes: { "db.system": "mysql" } }))

const logSlow = <A, E, R>(thresholdMs: number, eff: Effect.Effect<A, E, R>) =>
  Effect.timed(eff).pipe(
    Effect.tap(([d]) => Duration.toMillis(d) > thresholdMs ? Effect.logWarning(`slow query: ${Duration.toMillis(d)}ms`) : Effect.void),
    Effect.map(([_, a]) => a)
  )

Testing patterns

// Provide an in-memory or test DB client via Layer
export const DatabaseTest = Layer.succeed(Database, {
  use: <A>(cb: (c: any) => Promise<A>) => Effect.tryPromise({ try: () => cb(makeTestDb()), catch: (cause) => new DatabaseError({ cause }) })
})

// Or fully mock repository methods at the service boundary

Guidance

  • Centralize DB usage in a service; callers never touch the client directly
  • Map low-level driver errors to a single DatabaseError; consider domain mapping at repository layer
  • Use transactions for multi-row invariants; add retry policy for deadlocks/timeouts
  • Always add timeouts for long-running operations; log slow queries
  • Keep queries in repositories; route/handler stays thin

Pitfalls

  • Leaking raw client/connection across layers → hard to test and observe
  • No retry on deadlocks/timeouts → transient failures bubble to users
  • Building SQL strings manually → prefer query builder/typed ORM where possible
  • Resources & Scope for wrapping external clients and spans
  • Errors & Retries for selective retry policies
  • Config & Schema for validated DB configuration

Local Source Reference

CRITICAL: Search local Effect source before implementing

The full Effect source code is available at docs/effect-source/. Always search the actual implementation before writing Effect code.

Key Source Files

  • SqlClient: docs/effect-source/sql/src/SqlClient.ts
  • SqlConnection: docs/effect-source/sql/src/SqlConnection.ts
  • SqlResolver: docs/effect-source/sql/src/SqlResolver.ts
  • Drizzle integration: docs/effect-source/sql-drizzle/src/
  • SQLite Node: docs/effect-source/sql-sqlite-node/src/

Example Searches

# Find SqlClient patterns
grep -F "SqlClient" docs/effect-source/sql/src/SqlClient.ts

# Study transaction patterns
grep -rF "withTransaction" docs/effect-source/sql/src/

# Find Drizzle integration
grep -rF "export" docs/effect-source/sql-drizzle/src/

# Look at SQL test examples
grep -rF "SqlClient." docs/effect-source/sql/test/

Workflow

  1. Identify the SQL API you need (e.g., SqlClient, transactions)
  2. Search docs/effect-source/sql/src/ for the implementation
  3. Study the types and transaction patterns
  4. Look at test files for usage examples
  5. Write your code based on real implementations

Real source code > documentation > assumptions

References