SQL Packages Overview

import * as Effect from "effect/Effect"
import * as Sql from "effect/unstable/sql"

const getUser = (id: number) =>
  Effect.gen(function* () {
    const sql = yield* Sql.SqlClient
    const users = yield* sql`SELECT * FROM users WHERE id = ${id}`
    return users[0]
  })

const transferFunds = (from: number, to: number, amount: number) =>
  Effect.gen(function* () {
    const sql = yield* Sql.SqlClient
    
    yield* sql`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${from}`
    yield* sql`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${to}`
  }).pipe(
    sql.withTransaction
  )

import * as Migrator from "effect/unstable/sql/Migrator"

const migrate = Migrator.make({
  loader: Migrator.fromFileSystem("./migrations"),
  schemaDirectory: "sql"
})

import * as Stream from "effect/Stream"

const watchUser = (id: number) =>
  Effect.gen(function* () {
    const sql = yield* Sql.SqlClient
    
    return sql.reactive(
      ["users", id],
      sql`SELECT * FROM users WHERE id = ${id}`
    )
  })

// Returns a Stream that emits new values when the user is updated

// PostgreSQL
import { PgClient } from "@effect/sql-pg"

const PgLive = PgClient.layer({
  host: "localhost",
  port: 5432,
  database: "mydb",
  username: "user",
  password: Redacted.make("pass"),
  maxConnections: 10
})

// MySQL
import { MysqlClient } from "@effect/sql-mysql2"

const MysqlLive = MysqlClient.layer({
  host: "localhost",
  port: 3306,
  database: "mydb",
  username: "user",
  password: Redacted.make("pass")
})

import * as SqlSchema from "effect/unstable/sql/SqlSchema"
import * as Schema from "effect/Schema"

// Define your schema
class User extends SqlSchema.make({
  id: Schema.Int,
  name: Schema.String,
  email: Schema.String,
  createdAt: SqlSchema.DateTimeInsert
}) {}

// Insert with type safety
const insertUser = (user: typeof User.jsonInsert.Type) =>
  Effect.gen(function* () {
    const sql = yield* Sql.SqlClient
    yield* sql`INSERT INTO users ${sql.insert(user)}`
  })

import { SqlError } from "effect/unstable/sql/SqlError"

const safeQuery = (id: number) =>
  getUser(id).pipe(
    Effect.catchTag("SqlError", (error) =>
      Effect.succeed({ error: error.message })
    )
  )