Skip to content
Docs
Modules
pgx

pgx

Module pgx is used to connect to and query PostgreSQL databases.

The core functionality is provided by the pgx (opens in a new tab) library.

Functions

connect

Function signature
connect(dsn string, opts object) conn

Connect to the database specified by the dsn string. An optional options object can be provided to configure the connection.

Connection Options

OptionTypeDescription
streambooleanWhen true, queries will return iterators instead of lists. Default is false. This is the recommended mode for most use cases, especially for large result sets.
Recommended Streaming Example
>>> conn := pgx.connect("postgres://user:pass@localhost:5432/db", {"stream": true})
>>> rows := conn.query("SELECT * FROM users")
>>> defer rows.close()
>>> for i, row := range rows {
...     print(i, row.name)
... }

Types

conn

The conn object is a connection to a PostgreSQL database.

Methods

exec
Method signature
exec(sql string, args ...object) string

Execute sql by supplying the name of a prepared statement or a SQL string. Arguments may be referenced positionally in the SQL string as $1, $2, etc.

Example
>>> conn.exec("INSERT INTO users (name) VALUES ($1)", "Alice")
query
Method signature
query(sql string, args ...object) row_iterator|list

Runs a query on the server and returns the results.

Recommended Usage (Streaming Mode)

When the connection is created with {"stream": true}, returns a row_iterator that can be used with range to iterate over the results one row at a time. This is memory-efficient for large result sets and the recommended approach.

Streaming Example
>>> rows := conn.query("SELECT generate_series(1, 10) AS num")
>>> defer rows.close()
>>> for i, row := range rows {
...     print(i, row.num)
... }

The row_iterator object supports the following methods:

  • next(): Advances the iterator and returns the next row, or nil if there are no more rows.
  • close(): Closes the iterator and releases resources.
  • entry(): Returns an iterator entry for the current row or nil if there are no more rows.
close
Method signature
close()

Close the connection.

Example
>>> conn.close()

Legacy Usage (Non-Streaming Mode)

The following documentation describes the legacy non-streaming mode, which loads all results into memory at once. This approach is simple but not recommended for large result sets.

connect (Legacy)

Legacy Example
>>> conn := pgx.connect("postgres://user:pass@localhost:5432/db")
>>> conn.query("SELECT * FROM users")
[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]
>>> conn.close()

query (Legacy)

When streaming is not enabled (default), the query method returns a list of all records generated by the query. This loads all results into memory at once, which can be inefficient for large result sets.

Non-streaming Example
>>> conn.query("SELECT * FROM users")
[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]