Skip to main content

​
Overview

@effect/sql-pg provides a PostgreSQL driver for Effect’s SQL toolkit. It uses the pg library under the hood and provides full support for connection pooling, transactions, prepared statements, and PostgreSQL-specific features.

​
Installation

npm install @effect/sql-pg

​
Basic Usage

​
Creating a Client

Use the PgClient.layer function to create a PostgreSQL client layer: 
import { PgClient } from "@effect/sql-pg"
import { Effect, Layer } from "effect"

const SqlLive = PgClient.layer({
  host: "localhost",
  port: 5432,
  database: "mydb",
  username: "postgres",
  password: Redacted.make("password")
})

​
Connection with URL

You can also connect using a connection URL: 
import { Redacted } from "effect"

const SqlLive = PgClient.layer({
  url: Redacted.make("postgresql://postgres:password@localhost:5432/mydb")
})

​
Executing Queries

import { Effect } from "effect"
import { SqlClient } from "effect/unstable/sql"

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

  // Simple query
  const users = yield* sql<{ id: number; name: string }>`
    SELECT id, name FROM users WHERE active = ${true}
  `

  // Insert with helper
  yield* sql`
    INSERT INTO users ${sql.insert({ name: "Alice", email: "[email protected]" })}
  `

  // Update with helper
  yield* sql`
    UPDATE users SET ${sql.update({ name: "Bob" })} WHERE id = ${1}
  `
})

const runnable = program.pipe(Effect.provide(SqlLive))

​
Configuration Options

​
Connection Options

OptionTypeDescription
urlRedactedPostgreSQL connection URL
hoststringDatabase host
portnumberDatabase port (default: 5432)
databasestringDatabase name
usernamestringUsername for authentication
passwordRedactedPassword for authentication
sslboolean | ConnectionOptionsEnable SSL/TLS connection
pathstringUnix socket path

​
Connection Pool Options

OptionTypeDescription
maxConnectionsnumberMaximum number of connections in the pool
minConnectionsnumberMinimum number of connections to maintain
connectionTTLDuration.InputTime-to-live for connections
idleTimeoutDuration.InputHow long a connection can be idle
connectTimeoutDuration.InputTimeout for establishing connections (default: 5s)

​
Transform Options

OptionTypeDescription
transformResultNames(str: string) => stringTransform column names in results
transformQueryNames(str: string) => stringTransform identifiers in queries
transformJsonbooleanApply transformations to JSON fields

​
Advanced Options

OptionTypeDescription
applicationNamestringApplication name for PostgreSQL (default: “@effect/sql-pg”)
spanAttributesRecord<string, unknown>Custom telemetry attributes
typesPg.CustomTypesConfigCustom PostgreSQL type configuration

​
PostgreSQL-Specific Features

​
JSON Support

Handle JSON/JSONB columns with proper encoding: 
const sql = yield* PgClient.PgClient

const data = { key: "value", nested: { field: 123 } }
yield* sql`
  INSERT INTO documents (data) VALUES (${sql.json(data)})
`

​
LISTEN/NOTIFY

Subscribe to PostgreSQL notifications: 
import { Stream } from "effect"

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

  // Listen for notifications
  const notifications = sql.listen("my_channel")

  yield* Stream.runForEach(notifications, (payload) =>
    Effect.log(`Received: ${payload}`)
  )
})
Send notifications: 
const sql = yield* PgClient.PgClient
yield* sql.notify("my_channel", "Hello, World!")

​
Transactions

Automatic transaction management: 
const sql = yield* SqlClient.SqlClient

const result = yield* sql.withTransaction(
  Effect.gen(function* () {
    yield* sql`INSERT INTO accounts (balance) VALUES (${100})`
    yield* sql`UPDATE accounts SET balance = balance - ${50} WHERE id = ${1}`
    return yield* sql<{ balance: number }>`SELECT balance FROM accounts WHERE id = ${1}`
  })
)

​
Name Transformations

Automatically convert between naming conventions: 
import { String } from "effect"

const SqlLive = PgClient.layer({
  host: "localhost",
  database: "mydb",
  // Transform camelCase to snake_case for queries
  transformQueryNames: String.camelToSnake,
  // Transform snake_case to camelCase for results
  transformResultNames: String.snakeToCamel
})

// Use camelCase in your code
const users = yield* sql<{ userId: number; firstName: string }>`
  SELECT userId, firstName FROM users
`
// Queries become: SELECT user_id, first_name FROM users

​
Error Handling

All SQL errors are wrapped in SqlError: 
import { SqlError } from "effect/unstable/sql"

const program = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient
  return yield* sql`SELECT * FROM users`
}).pipe(
  Effect.catchTag("SqlError", (error) =>
    Effect.log(`Database error: ${error.message}`)
  )
)

​
Streaming Results

Stream large result sets efficiently: 
import { Stream } from "effect"

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

  const users = sql.stream<{ id: number; name: string }>`
    SELECT id, name FROM users
  `

  yield* Stream.runForEach(users, (user) =>
    Effect.log(`Processing user: ${user.name}`)
  )
})

​
Type Safety

The client is fully typed with Effect’s type system: 
interface User {
  id: number
  name: string
  email: string
}

const users = yield* sql<User>`SELECT * FROM users`
// users: ReadonlyArray<User>

Build docs developers (and LLMs) love

Get started for freeTalk to us