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
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
Option | Type | Description |
---|---|---|
stream | boolean | When 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. |
>>> 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
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.
>>> conn.exec("INSERT INTO users (name) VALUES ($1)", "Alice")
query
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.
>>> 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, ornil
if there are no more rows.close()
: Closes the iterator and releases resources.entry()
: Returns an iterator entry for the current row ornil
if there are no more rows.
close
close()
Close the connection.
>>> 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)
>>> 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.
>>> conn.query("SELECT * FROM users")
[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]