-   [](https://platform.exaloop.io/)
-   Databases
-   [PostgreSQL](https://platform.exaloop.io/db/postgres/)
-   Client applications

# 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](https://platform.exaloop.io/start/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.

```python title="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:

```sh title="Terminal"
exaloop --with postgres run -release client.codon
```

```text title="Output"
4.0
```

Use an individual SDK without the CLI

```sh title="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](https://platform.exaloop.io/db/reference/#postgresql) 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:

| API | Contract |
| --- | --- |
| `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.types` | Column names and PostgreSQL type OIDs |
| `result.command`, `result.rowcount` | Command 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](https://platform.exaloop.io/db/reference/#sql-values) 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:

```python title="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:

```python title="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:

```python title="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](https://platform.exaloop.io/db/reference/#compatibility) when planning a deployment.

## Next Steps

-   [Package the client](https://platform.exaloop.io/db/postgres/deployment/#client-applications) with its native dependencies.
-   [Create a server function](https://platform.exaloop.io/db/postgres/functions/) when a calculation belongs inside a SQL query.
-   Check [SQL value conversions](https://platform.exaloop.io/db/reference/#sql-values) for exact decimals and dates.

Source: https://platform.exaloop.io/db/postgres/client/