# PostgreSQL wire

> Status: Available. Connect with psql, psycopg, node-postgres or pgjdbc. The wire protocol is PostgreSQL's; the SQL is not.

| | |
|---|---|
| **Works** | Simple and extended protocols, prepared statements, portals with a row limit, pipelining, SCRAM-SHA-256 and SCRAM-SHA-256-PLUS, TLS. |
| **Differs** | Ordinary columns are described as text in prepared statements. The user name is ignored. There are no date or time types. |
| **Missing** | COPY, cancel requests, named (server-side) cursors, per-user authorization, `\dt` and other catalog-backed psql commands. |
| **Operational impact** | A connection activates the database it names. An idle connection is closed after 300 s. |
| **Evidence** | psql 16.2 and 17, psycopg 3.3.6, node-postgres 8.23.1 and pgjdbc 42.7.14, on source builds. node-postgres and pgjdbc were run without TLS. |

Any node serves any database. Use the database name as `dbname`, any user name, and the [database token](https://gauntlet-db.stavros.xyz/docs/concepts/databases.md) as the password. Authentication is SCRAM-SHA-256; cleartext passwords are not offered. With TLS configured, a plaintext connection is refused with `28000`.

On a loopback development node, drop the TLS options from each example.

## psql

```sh
PGPASSWORD=$TOKEN psql "host=n1.example.net port=7151 dbname=app user=app \
  sslmode=verify-full sslrootcert=ca/ca.crt channel_binding=require" \
  -c "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL)" \
  -c "INSERT INTO items VALUES (1, 'first')" -c "SELECT * FROM items"
```

`\dt`, `\copy` and `FETCH_COUNT` are not supported. Use `SHOW TABLES`.

## psycopg 3

```python
import os, psycopg

with psycopg.connect(host="n2.example.net", port=7151, dbname="app", user="app",
                     password=os.environ["GAUNTLET_DATABASE_TOKEN"], sslmode="verify-full",
                     sslrootcert="ca/ca.crt") as conn:
    print(conn.execute("SELECT name FROM items WHERE id = %s", (1,)).fetchone())
```

## node-postgres

```js
import fs from "node:fs";
import pg from "pg";

const client = new pg.Client({
  host: "n3.example.net", port: 7151, database: "app", user: "app",
  password: process.env.GAUNTLET_DATABASE_TOKEN,
  ssl: { ca: fs.readFileSync("ca/ca.crt", "utf8") },
});
await client.connect();
const { rows } = await client.query("SELECT name FROM items WHERE id = $1", [1]);
console.log(rows);
await client.end();
```

- node-postgres sends `true` and `false` as untyped text, so they are stored as the text `'true'` and `'false'`.
- A `Buffer` parameter is stored as a BLOB.
- **Unverified:** the project's node-postgres suite runs on loopback without TLS. The `ssl` option above has no recorded test.

## JDBC (pgjdbc)

```java
String url = "jdbc:postgresql://n1.example.net:7151/app"
           + "?sslmode=verify-full&sslrootcert=ca/ca.crt";
Properties props = new Properties();
props.setProperty("user", "app");
props.setProperty("password", System.getenv("GAUNTLET_DATABASE_TOKEN"));
try (Connection conn = DriverManager.getConnection(url, props);
     PreparedStatement st = conn.prepareStatement("SELECT name FROM items WHERE id = ?")) {
    st.setLong(1, 1);
    try (ResultSet rs = st.executeQuery()) { while (rs.next()) System.out.println(rs.getString(1)); }
}
```

- pgjdbc's default settings work, including binary transfer.
- `setQueryTimeout` is not enforced: cancel requests are ignored.
- **Unverified:** as with node-postgres, the JDBC suite runs without TLS.

## Driver behaviour

- **Column types in prepared statements are `text`** for ordinary columns, because SQLite affinity does not fix a type. Stable expressions keep their type: `CAST(x AS INTEGER)` is `int8`, `count(*)` is `int8`. Cast when your driver needs a number.
- **Results are text unless the driver asks for binary.** Binary works for int8, float8, text, bytea, numeric, bool, int2, int4, oid, float4, name, char and varchar.
- **`numeric` is not arbitrary precision.** A numeric parameter becomes an INTEGER if it has no fraction and fits in 64 bits, otherwise the nearest REAL.
- **`SET` works only when it keeps the server's value:** `client_encoding` UTF8, `DateStyle` ISO, `TimeZone` UTC, `extra_float_digits` 1 to 3. `application_name` can be set.
- **Per connection:** 256 prepared statements, 64 portals, 4,096 parameters, 1 MiB of SQL per statement.

## ORMs

No ORM is certified. Most ORMs start by reading PostgreSQL catalogs that Gauntlet does not have, so expect their migrations and introspection to fail. ORM support through the libSQL protocol is a [v1 roadmap item](https://gauntlet-db.stavros.xyz/docs/clients/libsql.md).

---
Source: https://gauntlet-db.stavros.xyz/docs/clients/pgwire