PostgreSQL with std.sql

std.sql connects moon applications directly to PostgreSQL. It provides parameterized queries, typed row decoding, transactions, connection pools, prepared statements, streaming, COPY, and PostgreSQL error details.

Database operations are asynchronous and return Result, so call them with await and handle or propagate the error.

Connect and query

Set DATABASE_URL to a PostgreSQL connection URL, then connect:

use std.sql

struct User {
  id: int,
  email: string,
}

async fn list_users() -> Result<void, Error> {
  let connection = (await sql.connect(std.os.getenv("DATABASE_URL")))?
  let users = (await connection.execute_as<User>(sql.raw(
    "SELECT id, email FROM users WHERE active = $1 ORDER BY id",
    [SqlValue.Bool(true)],
  )))?

  for user in users {
    println("${user.id}: ${user.email}")
  }

  await connection.close()
}

async fn main() {
  match await list_users() {
    ok(_) => println("done"),
    err(error) => println("database error: ${error.message}"),
  }
}

Both postgres:// and postgresql:// URLs are accepted. Put credentials in the environment or a secrets manager, not in source code.

Parameters and values

Keep query text separate from values. SqlValue supports nulls, booleans, integers, floats, text, bytes, dates and times, JSON, decimals, and UUIDs:

let query = sql.raw(
  "SELECT id, email FROM users WHERE id = $1 AND active = $2",
  [SqlValue.Int(user_id), SqlValue.Bool(true)],
)

Do not build SQL by interpolating user input. Bound values handle quoting and avoid SQL injection.

The query builder is useful when clauses are assembled conditionally:

let query = sql.select("users", ["id", "email"])
  .where(sql.eq("active", SqlValue.Bool(true)))
  .order_by("id", "desc", "last")
  .limit(50)

Builder identifiers are quoted and values remain parameters. sql.fragment is an explicit escape hatch for reviewed SQL syntax; never pass untrusted text to it.

Decode rows

For a known result shape, execute_as<T> maps columns directly into a struct. Use Optional<T> for nullable columns:

struct Video {
  id: int,
  title: string,
  published_at: Optional<DateTime>,
}

Column names match field names by default. Configure exceptions with sql.codec<T>:

sql.codec<Video>([
  sql.field("published_at").rename("published"),
])

Use execute_raw when the columns are dynamic or when the command tag and affected-row count matter. A SqlRow offers checked accessors such as text("email"), int_col("id"), and datetime("created_at").

Ordinary queries and struct decoding do not require PostgreSQL type IDs. Only an application-specific PostgreSQL enum or domain may need register_text_codec(type_id, "type_name").

Stream large results

execute and execute_as collect their results. For an unbounded or large query, use query and read rows incrementally:

let rows = (await connection.query(sql.raw(
  "SELECT id, email FROM users ORDER BY id",
  [],
)))?

while let some(row) = (await rows.next())? {
  println(row.text("email")?.unwrap_or(""))
}
rows.close()

Close a stream early when the caller stops reading it. This makes the connection available for another operation.

Use prepare for a statement that runs repeatedly. Cancelling an active query asks PostgreSQL to stop it; applications can recognize the PostgreSQL cancellation code 57014.

Transactions

Begin a transaction, perform work through it, and finish with exactly one commit or rollback:

let transaction = (await connection.begin(SqlTransactionOptions{}))?

let result = await transaction.execute(sql.raw(
  "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
  [SqlValue.Int(amount), SqlValue.Int(account_id)],
))

match result {
  ok(_) => (await transaction.commit())?,
  err(error) => {
    (await transaction.rollback())?
    return err(error)
  }
}

Transactions also support savepoints. Keep their lifetime short and avoid waiting on unrelated external work while locks are held.

Connection pools

Use sql.pool in a server or worker that handles several database operations at once. A pool bounds the number of open connections and waits when they are all in use.

Size it from the database's connection budget and the application's measured concurrency. A streamed result or open transaction keeps its connection until it is closed or completed.

PostgreSQL features

The driver also supports:

• listen, unlisten, and notifications;
• copy_in and copy_out for bulk byte streams;
• pipeline for sending a bounded batch of operations together;
• SQLSTATE codes and server details on SqlError;
• optional redacted metrics and traces.

Retry based on the operation and SQLSTATE, not on error text. Only retry a statement when the application knows it is safe to run again.

Transport security

For production, use sslmode=verify-full. It verifies both the certificate chain and the server name. sslrootcert selects a private certificate authority when needed.

Useful URL options include connection and query timeouts, an application name, and result-size limits. Passwords and parameter values are excluded from diagnostics and tracing; application logs should follow the same rule.

Browse complete signatures in the std.sql API reference. For higher-level model mapping, see std.orm.