⤌ Back

Module connection

Connection — a single, direct PostgreSQL database connection. Connection manages a TCP (optionally TLS) socket to a PostgreSQL server, drives the authentication handshake, and exposes a high-level API for query execution, prepared statements, cursors, and notifications. For connection pooling, use Pool instead.

Example

import postgres { Connection, DataTypeOIDs }
var conn = Connection('postgres://alice:secret@localhost:5432/mydb')
conn.connect()
# Simple query
var result = conn.query('select version()')
echo result.rows[0]
# Parameterised query
var res = conn.query(
  'select * from orders where status = \$1 and total > \$2',
  { params: ['open', 100.0] }
)
conn.close()

Classes

class Connection

A single connection to a PostgreSQL database server.

Connection string formats

Connection('postgres://user:pass@host:5432/database')
Connection('host=localhost port=5432 dbname=mydb user=alice')
Connection({ host: 'localhost', database: 'mydb', user: 'alice' })

This module implements the following decorated functions: @to_string.

Properties

• options (dict)

  The parsed connection options dict. Keys: host, port, user, password, database, ssl.

• type_registry (TypeRegistry)

  The TypeRegistry used to encode / decode values on this connection. Defaults to DefaultRegistry. Assign a custom registry before calling connect() to override type behaviour for this connection only.

• application_name (string)

  The application name sent to PostgreSQL in the startup message. Defaults to 'zuri-postgres'.

• timeout (number)

  Send / receive timeout in milliseconds. Default: 30 000 (30 s).

Methods

• Connection(dsn) (Constructor)

  Creates a Connection from a DSN string or options dict.

  Parameters

  • string | dict dsn Connection string or options dict. See module-level docs for formats.

• connect()

  Opens the TCP (or TLS) connection to the server and completes the PostgreSQL authentication handshake.

• close()

  Closes the connection to the server, sending the Terminate message and closing the underlying socket. Idempotent.

• is_open()

  Returns true if the connection is open.

• query(sql, options)

  Executes a SQL query and returns the result. When options.cursor is false (the default), all rows are fetched eagerly and returned in a QueryResult. When options.cursor is true, the query is executed via a server-side portal and a Cursor is returned for streaming access.

  Options

  Key	Type	Default	Description
  params	list	[]	Positional parameter values ($1, $2, …).
  paramTypes	list	[]	OID for each parameter (server infers if omitted).
  cursor	bool	false	Return a streaming Cursor.
  fetchSize	number	100	Rows per batch when cursor: true.
  rowMode	string	'dict'	'dict' (column names) or 'list'.
  simple	bool	false	Use simple-query protocol (text only).

  Example — cursor

  var result = conn.query(
    'select from large_table',
     { cursor: true, fetchSize: 500 }
  )
  var row
  while ((row = result.cursor.next())) {
    process(row)
  }
  result.cursor.close()
  

  Parameters

  • string sql The SQL statement. Use $1, $2, … placeholders.

  • dict options Query options (see table above).

• execute(sql, options)

  Executes a SQL statement and returns the command tag string. Useful for DDL (CREATE TABLE …) or DML (INSERT, UPDATE, DELETE) where you don't need the result rows.

  Parameters

  • string sql The SQL statement.

  • dict options Same options as query().

• prepare(sql, options)

  Creates and returns a named PreparedStatement for the given SQL. The statement is parsed by the server once; subsequent execute() calls skip the parse phase.

  Options

  Key	Type	Default	Description
  paramTypes	list	[]	OID hints for each parameter.
  name	string	auto	Explicit statement name (must be unique).

  Example

  var stmt = conn.prepare(
    'select from users where id = \$1',
     { paramTypes: [DataTypeOIDs.Int4] }
  )
  var result = stmt.execute({ params: [42] })
  stmt.close()
  

  Parameters

  • string sql Parameterised SQL.

  • dict options Options dict.

• listen(channels)

  Creates a NotificationListener subscribed to the given channel(s).

  Parameters

  • string | list channels Channel name or list of channel names.

• notify(channel, payload)

  Sends a NOTIFY command to the given channel with an optional payload.

  Parameters

  • string channel The channel to notify.

  • string payload Optional payload string (default '').

• begin(isolation)

  Begins a transaction.

  Parameters

  • string isolation Isolation level (e.g. 'SERIALIZABLE').

• commit()

  Commits the current transaction.

• rollback()

  Rolls back the current transaction.

• transaction(fn, isolation)

  Runs fn inside a transaction, automatically committing on success and rolling back on any exception.

  conn.transaction(@() {
    conn.execute('insert into log values (\$1)', { params: ['event'] })
    conn.execute('update counters set n = n + 1')
  })
  

  Parameters

  • function fn The function to run inside the transaction.

  • string isolation Optional isolation level.

• server_params()

  Returns the server-reported parameters (PostgreSQL version, encoding, date style, etc.) as a dict.

• backend_pid()

  Returns the backend PID for this connection, as reported during startup.

• escape_literal(s)

  Escapes a string literal for safe embedding in SQL. Prefer parameterised queries ($1, $2) over this method.

  Parameters

  • string s The string to escape.

• escape_identifier(s)

  Escapes an identifier (table name, column name, etc.) by double-quoting it and escaping any embedded double-quotes.

  Parameters

  • string s The identifier to escape.