Skip to main content
Markdown

PostgreSQL Client Applications

Use the native PostgreSQL client to execute parameterized SQL, convert results to typed Codon values, and write computed results back. The API also provides explicit transactions, server cursors, and COPY for bulk transfer.

The example below performs the full read–compute–write cycle using temporary tables, so the server needs no permanent schema changes.

Before you begin: install the all-SDK distribution and have access to a PostgreSQL development database. The example uses temporary tables and does not require a Codon server extension or a database superuser.

Read, Compute, and Write​

Save the example as client.codon. It inserts readings into a temporary table, computes their total in Codon, and writes the summary back. The temporary tables are removed when the connection closes.

client.codon
import exaloop.databases.postgres as postgres

with postgres.connect(dbname="postgres") as connection:
  connection.execute("CREATE TEMP TABLE readings (id bigint PRIMARY KEY, value double precision)")
  connection.executemany("INSERT INTO readings VALUES ($1, $2)", [(1, 1.5), (2, 2.5)])
  with connection.query("SELECT id, value FROM readings WHERE value >= $1 ORDER BY id", (1.0,)) as result:
    readings = result.fetchall(Tuple[int, float])
  total = sum(value for identity, value in readings)
  connection.execute("CREATE TEMP TABLE summary (total double precision)")
  connection.execute("INSERT INTO summary VALUES ($1)", (total,))
  print(total)

Run with an installed SDK and your usual libpq connection configuration:

Terminal
exaloop --with postgres run -release client.codon
Output
4.0
Use an individual SDK without the CLI
Terminal
codon run -release -plugin /path/to/sdk/postgres client.codon

The SQL placeholders keep values separate from the query, while Tuple[int, float] describes the rows used in the calculation. For another server, pass connection settings to postgres.connect() or use your existing libpq environment configuration.

Connections and Security​

For remote connections, use sslmode="verify-full" and provide a trusted CA through sslrootcert. The libpq default, prefer, may fall back to plaintext; it is not a substitute for server verification. Keep passwords in private configuration or a permission-restricted password file.

Each concurrent worker needs its own connection, with a with block providing deterministic cleanup. A connection loss during a write leaves its outcome uncertain: establish whether the operation is safe to repeat before retrying.

See the connection options for timeouts, connection strings, and client certificates.

Parameters and Results​

Use $1, $2, and so on for parameter values, with a separate tuple such as (identity,). Parameters cannot stand in for table or column names; use connection.quote_identifier() when a name must be chosen dynamically.

The main query and fetching methods are:

APIContract
query(sql, params=())Owned, fully buffered Result, including for INSERT ... RETURNING
execute(sql, params=())Discard result rows and return affected count, or -1 when unavailable
executemany(sql, parameters, atomic=True)Execute each parameter set; atomic transaction/savepoint by default
result.columns, result.typesColumn names and PostgreSQL type OIDs
result.command, result.rowcountCommand tag and affected/selected count
result.fetchone(Row)Optional[Row]; absent when exhausted
result.fetchmany(Row, size=2048)Up to size owned rows
result.rows(Row), result.batches(Row, size=2048)Consume rows or batches
result.fetchall(Row)Materialize all remaining rows
result.close()Idempotently release native result storage

Choose a row type with one field per column and use Optional[T] for nullable fields. Values copied into Codon remain usable after the result closes.

For database-specific types, reading text is often the simplest way to preserve the value. This is especially useful for exact numeric values and timestamps with time zones. The type reference explains when to use text, native dates, or JSON.

Transactions​

Outside an explicit transaction, statements commit individually. A transaction context rolls back unless you explicitly call commit(). Put the commit at the end of the successful path:

Source
import exaloop.databases.postgres as postgres

with postgres.connect(dbname="postgres") as connection:
  connection.execute("CREATE TEMP TABLE accounts (id bigint PRIMARY KEY, balance bigint)")
  connection.executemany("INSERT INTO accounts VALUES ($1, $2)", [(1, 100), (2, 0)])
  with connection.transaction() as transaction:
    connection.execute("UPDATE accounts SET balance=balance-$1 WHERE id=$2", (25, 1))
    connection.execute("UPDATE accounts SET balance=balance+$1 WHERE id=$2", (25, 2))
    transaction.commit()

If either update fails, execution leaves the block before commit() and the transfer is rolled back. For a callback-based alternative, connection.run_transaction(operation, *args) commits when the operation returns and rolls back if it raises.

Nested scopes use savepoints. Keep transaction control in one style rather than mixing these scopes with raw BEGIN and COMMIT statements. For bulk inserts, executemany() makes the batch atomic by default; use COPY when throughput is more important than issuing individual statements.

Large Queries​

query() buffers the complete PostgreSQL result. Batching that result bounds Codon conversion allocations, not libpq's result buffer. Use a server cursor for bounded fetching:

Source
import exaloop.databases.postgres as postgres

with postgres.connect(dbname="postgres") as connection:
  with connection.cursor("SELECT generate_series(1, $1)::bigint", (10003,), batch_size=1024) as cursor:
    total = 0
    for batch in cursor.batches(Tuple[int], size=2048):
      total += sum(row[0] for row in batch)
    assert total == 10003 * 10004 // 2

A cursor keeps a server transaction open, so finish consuming it promptly and close it before ending the transaction. The with blocks handle cleanup here. Fetching in batches limits client memory, but PostgreSQL may still need working memory for a sort or join.

Bulk Transfer with COPY​

copy_from(sql, chunks) accepts bytes or text chunks for COPY ... FROM STDIN. copy_to(sql, destination) writes bytes to a binary file-like object whose write() returns the byte count. Both return the copied row count. PostgreSQL handles CSV, text, or binary framing according to the supplied COPY options:

Source
import exaloop.databases.postgres as postgres

with postgres.connect(dbname="postgres") as connection:
  connection.execute("CREATE TEMP TABLE imported (id bigint, name text)")
  assert connection.copy_from("COPY imported FROM STDIN WITH (FORMAT csv)",
                [b'1,"hello, world"\n', b'2,last\n']) == 2
  with open("export.csv", "wb") as output:
    assert connection.copy_to("COPY (SELECT * FROM imported ORDER BY id) TO STDOUT WITH (FORMAT csv)", output) == 2

The example imports two CSV rows and writes them to export.csv. For a large file, supply a generator of byte chunks instead of holding the file in memory. Use the dedicated COPY methods and reserve the connection for the transfer until it finishes.

Handling Errors​

Catch DatabaseError to handle a rejected query and inspect sqlstate when you need to distinguish causes, such as a duplicate key. Inside a transaction, roll back before continuing. A ConnectionError means you need a new connection.

This client is synchronous and does not provide a connection pool or automatic retries. See the compatibility reference when planning a deployment.

Next Steps​