effect-sql-spanner

Cloud Spanner driver for @effect/sql with GoogleSQL compiler and Graph helpers.

Features

• 🎯 Full Cloud Spanner integration with @effect/sql
• 📊 GoogleSQL query compilation via effect-sql-googlesql
• 🔄 Graph database helpers for Spanner Graph
• 🧪 Testing utilities for emulator and live instances
• 🔧 Schema migrations with FileSystem support
• ⚡ Production-ready with Effect.ts patterns

Installation

pnpm add effect-sql-spanner @effect/sql effect

Quick Start

import * as Client from "effect-sql-spanner/Client"
import * as Effect from "effect/Effect"

const program = Effect.gen(function* () {
  const sql = yield* Client.ClientTag

  // Execute GoogleSQL queries
  const users = yield* sql`
    SELECT * FROM users WHERE active = ${true}
  `

  return users
})

// Configure Spanner client
const SpannerLive = Client.layer({
  projectId: "my-project",
  instanceId: "my-instance",
  databaseId: "my-database"
})

Effect.runPromise(program.pipe(Effect.provide(SpannerLive)))

Configuration

Basic Configuration

import * as Client from "effect-sql-spanner/Client"

const config = {
  projectId: "your-project-id",
  instanceId: "your-instance-id",
  databaseId: "your-database-id"
}

const SpannerLayer = Client.layer(config)

Emulator Configuration

const EmulatorLayer = Client.layer({
  projectId: "test-project",
  instanceId: "test-instance",
  databaseId: "test-database",
  emulatorHost: "localhost:9010"
})

Advanced Configuration

const AdvancedLayer = Client.layer({
  projectId: "my-project",
  instanceId: "my-instance",
  databaseId: "my-database",

  // Query name transformation (e.g., camelCase → snake_case)
  transformQueryNames: (name) => toSnakeCase(name),

  // Result name transformation (e.g., snake_case → camelCase)
  transformResultNames: (name) => toCamelCase(name),

  // OpenTelemetry span attributes
  spanAttributes: [
    ["service.name", "my-api"],
    ["deployment.environment", "production"]
  ],

  // Spanner transaction options
  transactionOptions: {
    timeout: 30000
  },

  // Request-level options
  requestOptions: {
    priority: "PRIORITY_HIGH"
  },

  // Authentication
  keyFilename: "/path/to/service-account.json"
  // or
  // credentials: { ... }
})

Graph Database Operations

Spanner Graph support with type-safe helpers:

Insert Graph Node

import * as Helpers from "effect-sql-spanner/helpers"

const program = Effect.gen(function* () {
  const sql = yield* Client.ClientTag

  // Insert a typed graph node
  yield* sql.insertGraphNode({
    label: "User",
    properties: {
      id: "user-123",
      email: "[email protected]",
      name: "Alice"
    }
  })
})

Insert Graph Edge

import * as Helpers from "effect-sql-spanner/helpers"

const program = Effect.gen(function* () {
  const sql = yield* Client.ClientTag

  // Create relationship between nodes
  yield* sql.insertGraphEdge({
    label: "FOLLOWS",
    sourceNodeKey: "user-123",
    targetNodeKey: "user-456",
    properties: {
      since: new Date()
    }
  })
})

Update Graph Node

yield* sql.updateGraphNode({
  label: "User",
  nodeKey: "user-123",
  properties: {
    name: "Alice Smith",
    verified: true
  }
})

Patch Graph Node (Merge Properties)

// Updates only specified properties, keeps others intact
yield* sql.patchGraphNode({
  label: "User",
  nodeKey: "user-123",
  properties: {
    lastLogin: new Date()
  }
})

Migrations

Built-in migration support with filesystem loader:

import * as Migrator from "effect-sql-spanner/Migrator"
import * as FileSystem from "effect-sql-spanner/Migrator/FileSystem"

const program = Effect.gen(function* () {
  const sql = yield* Client.ClientTag

  // Run migrations from ./migrations directory
  yield* Migrator.run({
    loader: FileSystem.loader("./migrations"),
    schemaDirectory: "./migrations"
  })
})

Migration files (e.g., 001_create_users.sql):

CREATE TABLE users (
  id STRING(36) NOT NULL,
  email STRING(255) NOT NULL,
  name STRING(255),
  created_at TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (id);

Testing

Emulator Testing

import * as Testing from "effect-sql-spanner/testing"
import * as Effect from "effect/Effect"
import { describe, it } from "@effect/vitest"

describe("user queries", () => {
  it.effect("should create and fetch users", () =>
    Effect.gen(function* () {
      const sql = yield* Client.ClientTag

      yield* sql`INSERT INTO users (id, email) VALUES (${id}, ${email})`
      const users = yield* sql`SELECT * FROM users WHERE id = ${id}`

      assert.strictEqual(users.length, 1)
    }).pipe(Effect.provide(Testing.EmulatorPerTestLayer))
  )
})

Live Database Testing

const LiveTestLayer = Testing.liveOrEmulator({
  projectId: "my-project",
  instanceId: "my-instance",
  databaseId: "test-database"
})

Helper Functions

Query Building

import { buildWhereClause, buildOrderByClause, buildLimitOffsetClause } from "effect-sql-spanner"

// Build WHERE clause from filters
const whereClause = buildWhereClause({
  active: true,
  role: "admin"
})
// WHERE active = @active AND role = @role

// Build ORDER BY clause
const orderBy = buildOrderByClause([
  { field: "created_at", direction: "DESC" },
  { field: "name", direction: "ASC" }
])
// ORDER BY created_at DESC, name ASC

// Build LIMIT/OFFSET clause
const limitOffset = buildLimitOffsetClause({ limit: 10, offset: 20 })
// LIMIT 10 OFFSET 20

JSON Operations

import * as Helpers from "effect-sql-spanner/helpers"

// Extract JSON property
const path = Helpers.createJsonPath("$.user.profile.email")

// Build JSON properties fragment
const fragment = Helpers.createJsonPropertiesFragment({
  name: "Alice",
  age: 30,
  verified: true
})

Type System

Effect-sql-spanner uses the GoogleSQL compiler's type system:

import { types, paramOf, nullOf } from "effect-sql-spanner/helpers"

// Typed parameters
const query = sql`
  INSERT INTO users (id, metadata)
  VALUES (
    ${paramOf(types.string(), userId)},
    ${paramOf(types.json(), metadata)}
  )
`

// Typed null values
const query2 = sql`
  UPDATE users SET deleted_at = ${nullOf(types.timestamp())}
  WHERE id = ${userId}
`

API Reference

Client

layer(config: SpannerSqlClientConfig)

Create Spanner client layer with configuration

ClientTag

Service tag for accessing client in Effect context

Helpers

insertGraphNode(options: InsertGraphNodeOptions) insertGraphEdge(options: InsertGraphEdgeOptions) updateGraphNode(options: UpdateGraphNodeOptions) updateGraphEdge(options: UpdateGraphEdgeOptions) patchGraphNode(options: PatchGraphNodeOptions) patchGraphEdge(options: PatchGraphEdgeOptions)

buildWhereClause(filters: Record<string, unknown>) buildOrderByClause(orderBy: OrderBy[]) buildLimitOffsetClause(pagination: { limit?: number; offset?: number })

createJsonPath(path: string) createJsonPropertiesFragment(properties: Record<string, unknown>)

Testing

EmulatorPerTestLayer

Layer that creates isolated emulator instance per test

liveOrEmulator(config)

Layer that uses live database or falls back to emulator

Migrator

run(options: MigratorOptions)

Execute schema migrations

Types

export { types, paramOf, nullOf } from "effect-sql-googlesql/Compiler"

Configuration Options

interface SpannerSqlClientConfig {
  projectId: string
  instanceId: string
  databaseId: string
  emulatorHost?: string
  transformQueryNames?: (name: string) => string
  transformResultNames?: (name: string) => string
  spanAttributes?: ReadonlyArray<readonly [string, unknown]>
  transactionOptions?: SpannerRunTransactionOptions
  disableBuiltInMetrics?: boolean
  requestOptions?: SpannerRequestOptions
  credentials?: SpannerOptions["credentials"]
  keyFilename?: SpannerOptions["keyFilename"]
}

License

Apache-2.0

Author

Ryan Hunter (@artimath)

Links

• Cloud Spanner Documentation
• Cloud Spanner Graph
• @effect/sql Documentation
• GoogleSQL Reference
• Effect.ts Documentation