Docs / Clients
PostgreSQL wire
Connect with psql, psycopg, node-postgres or pgjdbc. The wire protocol is PostgreSQL's; the SQL is not.
Available View Markdown
# 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
Connect this project to my existing Gauntlet database.
Context:
- Documentation index for agents: https://gauntlet-db.stavros.xyz/llms.txt
- Quickstart: https://gauntlet-db.stavros.xyz/docs/quickstart.md
- SQL compatibility and error codes: https://gauntlet-db.stavros.xyz/docs/sql/compatibility.md
- Database: the one already configured for this project
- Protocol: PostgreSQL wire or the native HTTP JSON API, whichever this project already uses
- Connection values are in environment variables: GAUNTLET_DATABASE_URL (PostgreSQL wire),
GAUNTLET_HTTP_URL (HTTP base URL) and GAUNTLET_DATABASE_TOKEN (the database token).
These names are a convention, not a shipped SDK setting. Keep this project's own names if it has them.
Before editing, read the Gauntlet documentation above and inspect how this project
already talks to its database. Gauntlet is pre-beta software; where the documentation
and my deployment disagree, tell me instead of guessing.
What Gauntlet is:
- A distributed SQL database. SQL is the SQLite family, run by the Turso engine.
- The PostgreSQL wire protocol is a transport only. There is no PostgreSQL SQL dialect,
no pg_catalog or information_schema, no extensions, and no ORM is certified yet.
- libSQL client compatibility is not available yet. Do not assume it.
Connecting:
- PostgreSQL wire: dbname is the database name, the user name is ignored, and the
password is the database token. Authentication is SCRAM-SHA-256. Keep TLS
verification on (sslmode=verify-full with the deployment's CA certificate).
- HTTP: POST <GAUNTLET_HTTP_URL>/db/<name>/sql with a JSON body
{"sql": "...", "params": [...]} and the header "Authorization: Bearer <token>".
POST /db/<name>/tx runs a list of statements atomically.
SQL rules that differ from PostgreSQL:
- Every table needs an explicit primary key, and INSERT supplies the key value.
- CREATE TABLE is a standalone statement outside a transaction. There is no ALTER TABLE,
DROP TABLE or CREATE INDEX; an index comes only from a UNIQUE constraint at creation.
- Parameters are $1, ?1 or ?. Names are single-part (no schema.table).
- Column types follow SQLite affinity. In prepared statements ordinary columns are
described as text; CAST(x AS INTEGER) when the driver needs a number.
Errors you must handle (SQLSTATE):
- 40001: conflict. Roll back and retry the whole transaction. Locking is no-wait, so
this is normal under contention.
- 40003: commit outcome unknown. Do NOT retry. Look the transaction up with
GET /db/<name>/txn/<txid> and replay only if the status is `aborted`.
- 57P03: cannot serve right now. Roll back, then retry the whole transaction with backoff.
- 53300: too many connections. Back off; keep the pool below the node's limit.
Credentials and scope:
- Use only the database token for this one database. Keep it server-side and out of
source, logs, browser bundles and this conversation.
- Never use or ask for a deployment admin token, a client token or bucket credentials.
- Do not create, drop, back up, restore or branch databases, and do not rotate credentials.
Task:
1. Add the smallest connection integration that fits this project.
2. Run one explicit `SELECT 1` connectivity check. It may activate this database if it
is idle; do not probe or wake any other database.
3. Do not change the schema or write application data unless I ask for it.
Report the files changed, the protocol and client used, the result of the check, and
anything this project needs that Gauntlet does not support.- 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,
\dtand 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 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
Shell
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
JavaScript
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
trueandfalseas untyped text, so they are stored as the text'true'and'false'. - A
Bufferparameter is stored as a BLOB. - Unverified: the project's node-postgres suite runs on loopback without TLS. The
ssloption 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.
setQueryTimeoutis 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
textfor ordinary columns, because SQLite affinity does not fix a type. Stable expressions keep their type:CAST(x AS INTEGER)isint8,count(*)isint8. 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.
numericis not arbitrary precision. A numeric parameter becomes an INTEGER if it has no fraction and fits in 64 bits, otherwise the nearest REAL.SETworks only when it keeps the server's value:client_encodingUTF8,DateStyleISO,TimeZoneUTC,extra_float_digits1 to 3.application_namecan 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.