grimoire

Catalog / @effect/sql-pg / SQL Statements and Compiler
grimoire show @effect/sql-pg sql-statements-compiler

SQL Statements and Compiler

Tagged template SQL, the Pg compiler, helpers (insert, update, in, and), and custom types

sqlcompilerstatementinsertupdatequery

SQL Statements and Compiler

Overview

PgClient uses @effect/sql's tagged template literal system for SQL composition. The Pg-specific compiler converts statements to PostgreSQL syntax with $1-style placeholders, identifier escaping with double quotes, and support for the PgJson custom type.

Key Concepts

Tagged Template SQL

The client itself is a tagged template function:

const sql = yield* PgClient.PgClient

// Simple query
const rows = yield* sql`SELECT * FROM people WHERE id = ${id}`

// Identifier escaping
sql`SELECT * FROM ${sql("tableName")}` // → "tableName"

SQL Helpers

sql.insert(record) - Generates INSERT column/value lists:

sql`INSERT INTO people ${sql.insert({ name: "Tim", age: 10 })}`
// → INSERT INTO people ("name","age") VALUES ($1,$2)

sql.update(record, omit?) - Generates SET clauses:

sql`UPDATE people SET ${sql.update({ name: "Tim", age: 10 }, ["age"])}`
// → UPDATE people SET "name" = $1
// (age is omitted)

sql.updateValues(records, alias) - Generates VALUES with alias for bulk updates:

sql`UPDATE people SET name = data.name FROM ${
  sql.updateValues([{ name: "Tim" }, { name: "John" }], "data")
}`
// → UPDATE people SET name = data.name FROM (values ($1),($2)) AS data("name")

Both update and updateValues support .returning("*"):

sql.updateValues([...], "data").returning("*")
// → ... RETURNING *

sql.in(values) / sql.in(column, values) - IN clause:

sql`WHERE id IN ${sql.in([1, 2, 3])}`        // → WHERE id IN ($1,$2,$3)
sql`WHERE ${sql.in("id", [1, 2, 3])}`        // → WHERE "id" IN ($1,$2,$3)
sql`WHERE ${sql.in("id", [])}`               // → WHERE 1=0

sql.and(fragments) - AND composition:

sql`WHERE ${sql.and([
  sql.in("name", ["Tim", "John"]),
  sql`created_at < ${now}`
])}`
// → WHERE ("name" IN ($1,$2) AND created_at < $3)

JSON Handling

The sql.json() method wraps a value with the PgJson custom type:

sql`INSERT INTO people ${sql.insert({
  name: "Tim",
  data: sql.json({ a: 1 })
})}`

PostgreSQL also handles inline JSON via ::jsonb cast:

const rows = yield* sql`SELECT ${{ testValue: 123 }}::jsonb AS json`
// rows[0].json → { testValue: 123 }

Multi-Statement Queries

PostgreSQL supports multiple statements in a single query. Results come back as an array of arrays:

const result = yield* sql`
  CREATE TABLE test (id TEXT PRIMARY KEY, name TEXT);
  INSERT INTO test VALUES ('1', 'a') RETURNING *;
  INSERT INTO test VALUES ('2', 'b') RETURNING *;
`
// result[0] → [] (CREATE has no rows)
// result[1] → [{ id: "1", name: "a" }]
// result[2] → [{ id: "2", name: "b" }]

The Compiler

PgClient.makeCompiler(transform?, transformJson?) creates a Statement.Compiler with:

const compiler = PgClient.makeCompiler(String.camelToSnake)
const [query] = compiler.compile(
  sql`SELECT * FROM ${sql("peopleTest")}`, false
)
// → SELECT * FROM "people_test"

Disabling Transforms

Use sql.withoutTransforms() to get a client that skips name transforms:

const raw = (yield* PgClient.PgClient).withoutTransforms()
raw`INSERT INTO people ${raw.insert({ first_name: "Tim" })}`
// Uses column names as-is, no camelToSnake

Related Files