@effect/sql

import { Effect, pipe } from "effect"
import { PgClient } from "@effect/sql-pg"
import { SqlClient } from "@effect/sql"

const SqlLive = PgClient.layer({
  database: "effect_pg_dev"
})

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

  const people = yield* sql<{
    readonly id: number
    readonly name: string
  }>`SELECT id, name FROM people`

  yield* Effect.log(`Got ${people.length} results!`)
})

pipe(program, Effect.provide(SqlLive), Effect.runPromise)

const limit = 10
const statement = sql`SELECT * FROM people LIMIT ${limit}`
// SELECT * FROM people LIMIT ?

const table = "people"
const statement = sql`SELECT * FROM ${sql(table)} LIMIT 10`
// SELECT * FROM "people" LIMIT 10

const names = ["Alice", "Bob", "Charlie"]
const cursor = new Date()

// AND
const query = sql`SELECT * FROM people WHERE ${sql.and([
  sql.in("name", names),
  sql`created_at < ${cursor}`
])}`

// OR
const query = sql`SELECT * FROM people WHERE ${sql.or([
  sql.in("name", names),
  sql`created_at < ${cursor}`
])}`

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

class Person extends Schema.Class<Person>("Person")({
  id: Schema.Number,
  name: Schema.String,
  createdAt: Schema.DateFromSelf,
  updatedAt: Schema.DateFromSelf
}) {}

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

  const people = yield* sql`SELECT * FROM people`.pipe(
    SqlSchema.findAll(Person)
  )

  return people
})

import { Effect, Schema } from "effect"
import { SqlResolver, SqlClient } from "@effect/sql"

class Person extends Schema.Class<Person>("Person")({
  id: Schema.Number,
  name: Schema.String
}) {}

const makePersonService = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient

  const GetById = yield* SqlResolver.findById("GetPersonById", {
    Id: Schema.Number,
    Result: Person,
    ResultId: (_) => _.id,
    execute: (ids) => sql`SELECT * FROM people WHERE ${sql.in("id", ids)}`
  })

  const getById = (id: number) =>
    Effect.withRequestCaching("on")(GetById.execute(id))

  return { getById }
})

// src/migrations/0001_add_users.ts
import { Effect } from "effect"
import { SqlClient } from "@effect/sql"

export default Effect.flatMap(
  SqlClient.SqlClient,
  (sql) => sql`
    CREATE TABLE users (
      id serial PRIMARY KEY,
      name varchar(255) NOT NULL,
      created_at TIMESTAMP NOT NULL DEFAULT NOW()
    )
  `
)

import { Effect, Layer } from "effect"
import { NodeContext, NodeRuntime } from "@effect/platform-node"
import { PgClient, PgMigrator } from "@effect/sql-pg"
import { fileURLToPath } from "node:url"

const SqlLive = PgClient.layer({
  database: "example_database"
})

const MigratorLive = PgMigrator.layer({
  loader: PgMigrator.fromFileSystem(
    fileURLToPath(new URL("migrations", import.meta.url))
  ),
  schemaDirectory: "src/migrations"
}).pipe(Layer.provide(SqlLive))

const program = Effect.gen(function* () {
  // Your application code
})

const EnvLive = Layer.mergeAll(SqlLive, MigratorLive).pipe(
  Layer.provide(NodeContext.layer)
)

pipe(program, Effect.provide(EnvLive), NodeRuntime.runMain)