---
title: "PostgreSQL Protocol Quick Start Guide"
---

# PostgreSQL Protocol Quick Start Guide

**For developers integrating HeliosDB-Lite with PostgreSQL clients**

---

## 5-Minute Quick Start

### 1. Start the Server

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --memory
```

### 2. Connect with psql

```bash
psql -h 127.0.0.1 -p 5432 -U postgres
```

### 3. Run Queries

```sql
-- Simple queries
SELECT * FROM users;
INSERT INTO users VALUES (4, 'David', 'david@example.com');

-- Transactions
BEGIN;
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
COMMIT;
```

### 4. Test Prepared Statements (Python)

```python
import psycopg2

conn = psycopg2.connect(host='127.0.0.1', port=5432, user='postgres')
cur = conn.cursor()

# Prepared statement (automatic)
cur.execute("SELECT * FROM users WHERE id = %s", (1,))
print(cur.fetchone())  # (1, 'Alice', 'alice@example.com')
```

---

## Server Options

### Persistent Data and Custom Port

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir ./mydata --port 5433
```

### Password Authentication and Remote Clients

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir ./mydata --listen 0.0.0.0 \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD"
```

`--auth` accepts `trust` (default, local development only), `password`, `md5` and
`scram-sha-256`. Set `max_connections` in the `[server]` section of `config.toml`
(default 100). See the [CLI Reference](/docs/lite/reference/cli/).

---

## Client Examples

### Python (psycopg2)

```python
import psycopg2

# Connect
conn = psycopg2.connect(
    host='127.0.0.1',
    port=5432,
    user='postgres'
)

# Simple query
cur = conn.cursor()
cur.execute("SELECT * FROM users")
for row in cur.fetchall():
    print(row)

# Prepared statement (automatic with placeholders)
cur.execute("SELECT * FROM users WHERE id = %s", (1,))
print(cur.fetchone())

# Transaction
conn.autocommit = False
cur.execute("INSERT INTO users VALUES (%s, %s)", (2, 'Bob'))
conn.commit()

conn.close()
```

### Node.js (pg)

```javascript
const { Client } = require('pg');

const client = new Client({
  host: '127.0.0.1',
  port: 5432,
  user: 'postgres',
});

await client.connect();

// Simple query
const res = await client.query('SELECT * FROM users');
console.log(res.rows);

// Prepared statement
const res2 = await client.query(
  'SELECT * FROM users WHERE id = $1',
  [1]
);
console.log(res2.rows[0]);

// Transaction
await client.query('BEGIN');
await client.query('INSERT INTO users VALUES ($1, $2)', [2, 'Bob']);
await client.query('COMMIT');

await client.end();
```

### Java (JDBC)

```java
import java.sql.*;

Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://127.0.0.1:5432/postgres",
    "postgres",
    ""
);

// Simple query
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");
while (rs.next()) {
    System.out.println(rs.getInt("id") + ": " + rs.getString("name"));
}

// Prepared statement
PreparedStatement pstmt = conn.prepareStatement(
    "SELECT * FROM users WHERE id = ?"
);
pstmt.setInt(1, 1);
ResultSet rs2 = pstmt.executeQuery();
while (rs2.next()) {
    System.out.println(rs2.getString("name"));
}

// Transaction
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("INSERT INTO users VALUES (?, ?)");
pstmt.setInt(1, 2);
pstmt.setString(2, "Bob");
pstmt.executeUpdate();
conn.commit();

conn.close();
```

### Go (lib/pq)

```go
package main

import (
    "database/sql"
    _ "github.com/lib/pq"
)

func main() {
    db, _ := sql.Open("postgres",
        "host=127.0.0.1 port=5432 user=postgres sslmode=disable")
    defer db.Close()

    // Simple query
    rows, _ := db.Query("SELECT * FROM users")
    defer rows.Close()
    for rows.Next() {
        var id int
        var name string
        rows.Scan(&id, &name)
        println(id, name)
    }

    // Prepared statement
    stmt, _ := db.Prepare("SELECT * FROM users WHERE id = $1")
    defer stmt.Close()
    rows2, _ := stmt.Query(1)
    defer rows2.Close()

    // Transaction
    tx, _ := db.Begin()
    tx.Exec("INSERT INTO users VALUES ($1, $2)", 2, "Bob")
    tx.Commit()
}
```

---

## Supported Features

### Fully Supported

- Simple query protocol, including multi-statement batches
- Extended query protocol: Parse, Bind, Describe, Execute, Close, Flush, and Sync
- Prepared statements, portals, and parameter binding (`$1`, `$2`, etc.)
- Transactions (`BEGIN`, `COMMIT`, `ROLLBACK`) in simple and extended protocol paths
- Text-format parameters for common scalar and document types
- Binary-format parameters for common scalar types, including integers, floats, booleans, text, varchar, and unknown byte payloads
- Multiple concurrent connections with a configurable connection limit
- Authentication with trust, cleartext password, MD5 and SCRAM-SHA-256 (`--auth`)
- SSL/TLS negotiation when the server is started with `--tls-cert` and `--tls-key`
- COPY FROM STDIN and COPY TO STDOUT for text and CSV bulk transfer
- LISTEN, UNLISTEN, and NOTIFY over the PostgreSQL wire protocol

### Partial Support

- `pg_catalog` and `information_schema` compatibility cover common client and ORM introspection paths, but they are not a complete PostgreSQL catalog clone.
- Result format preferences are tracked by portals, but row data is still emitted through the text row encoder for most query paths.
- SQL cursor declarations (`DECLARE CURSOR`, `FETCH`) are not exposed as PostgreSQL server-side cursors; extended-protocol portals are supported for prepared query execution.

### Not Yet Supported

- Full PostgreSQL-compatible binary result encoding for every data type
- Complete PostgreSQL catalog emulation

---

## Common Use Cases

### Case 1: Local Database with Network Access

**Scenario:** A single-node database on a workstation or edge box, reachable by local
tools and services.

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir ./mydata
# psql, pgAdmin, DBeaver and your services connect to 127.0.0.1:5432
```

### Case 2: Testing PostgreSQL Applications

**Scenario:** Run PostgreSQL-compatible tests without Docker.

```bash
# Start a throwaway in-memory server for the test run
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --memory --port 15432 --http-port 18080 &
SERVER_PID=$!

# Run the test suite against it
DATABASE_URL="postgresql://postgres@127.0.0.1:15432/heliosdb" pytest

kill $SERVER_PID
```

### Case 3: Lightweight PostgreSQL Alternative

**Scenario:** Replace PostgreSQL for small deployments.

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir /var/lib/heliosdb --listen 0.0.0.0 \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD"
# Compatible with existing PostgreSQL tools: psql, pgAdmin, DBeaver, etc.
```

---

## Troubleshooting

### Connection Refused

```
psql: could not connect to server: Connection refused
```

**Fix:** Ensure server is running and listening on correct port.

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
# Check server logs
RUST_LOG=debug heliosdb-lite start --data-dir ./mydata

# Verify port
lsof -i :5432
netstat -an | grep 5432
```

### Authentication Failed

```
psql: FATAL: password authentication failed for user "postgres"
```

**Fix:** Check the authentication method the server was started with. With
`--auth scram-sha-256` (or `password`/`md5`), connect as `postgres` with the
`--password` value, or as a role created with `CREATE ROLE ... LOGIN PASSWORD ...`.

### Prepared Statement Not Found

```
ERROR: prepared statement "xyz" does not exist
```

**Fix:** Ensure statement is prepared before use.

```python
# Prepare first
cur.execute("PREPARE my_stmt AS SELECT * FROM users WHERE id = $1")

# Then execute
cur.execute("EXECUTE my_stmt(1)")

# Or use parameterized queries (automatic preparation)
cur.execute("SELECT * FROM users WHERE id = %s", (1,))
```

### Type Mismatch

```
ERROR: Type conversion error: cannot convert STRING to INT4
```

**Fix:** Ensure parameter types match column types.

```python
# Wrong
cur.execute("INSERT INTO users (id) VALUES (%s)", ("1",))  # String

# Correct
cur.execute("INSERT INTO users (id) VALUES (%s)", (1,))  # Integer
```

---

## Performance Tips

### 1. Use Prepared Statements

**Slow (re-parses every time):**
```python
for i in range(1000):
    cur.execute(f"SELECT * FROM users WHERE id = {i}")
```

**Fast (prepare once, reuse):**
```python
for i in range(1000):
    cur.execute("SELECT * FROM users WHERE id = %s", (i,))
```

### 2. Batch Operations

**Slow (1000 round-trips):**
```python
for user in users:
    cur.execute("INSERT INTO users VALUES (%s, %s)", (user.id, user.name))
```

**Fast (1 round-trip):**
```python
cur.executemany(
    "INSERT INTO users VALUES (%s, %s)",
    [(u.id, u.name) for u in users]
)
```

### 3. Use Transactions

**Slow (auto-commit each statement):**
```python
cur.execute("INSERT INTO users VALUES (1, 'Alice')")
cur.execute("INSERT INTO users VALUES (2, 'Bob')")
cur.execute("INSERT INTO users VALUES (3, 'Charlie')")
```

**Fast (batch in transaction):**
```python
conn.autocommit = False
cur.execute("INSERT INTO users VALUES (1, 'Alice')")
cur.execute("INSERT INTO users VALUES (2, 'Bob')")
cur.execute("INSERT INTO users VALUES (3, 'Charlie')")
conn.commit()
```

### 4. Connection Pooling

**Slow (new connection each request):**
```python
def handle_request():
    conn = psycopg2.connect(...)
    # ... use connection ...
    conn.close()
```

**Fast (reuse connections):**
```python
from psycopg2 import pool

db_pool = pool.SimpleConnectionPool(1, 20, host='127.0.0.1', ...)

def handle_request():
    conn = db_pool.getconn()
    # ... use connection ...
    db_pool.putconn(conn)
```

---

## Security Best Practices

### 1. Use Strong Authentication

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
# Development
heliosdb-lite start --data-dir ./mydata                 # --auth trust (default)

# Production
heliosdb-lite start --data-dir ./mydata --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD"
```

### 2. Bind to Localhost Only

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
# Accessible from the network (needs password auth and a firewall)
heliosdb-lite start --data-dir ./mydata --listen 0.0.0.0 --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD"

# Local only (default)
heliosdb-lite start --data-dir ./mydata --listen 127.0.0.1
```

### 3. Limit Connections

```toml
# config.toml
[server]
max_connections = 50   # Prevent resource exhaustion
```

### 4. Use SSH Tunnel for Remote Access

```bash
# On remote machine
ssh -L 5432:localhost:5432 remote-host

# Then connect to localhost
psql -h 127.0.0.1 -p 5432 -U postgres
```

### 5. Always Use Prepared Statements

```python
# Vulnerable to SQL injection
cur.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

# Safe (parameter binding)
cur.execute("SELECT * FROM users WHERE name = %s", (user_input,))
```

---

## Advanced Configuration

### Additional Users

```sql
CREATE ROLE alice LOGIN PASSWORD 'a-long-random-password';
CREATE ROLE bob LOGIN PASSWORD 'another-long-password';
```

### Statement Cache Tuning

The prepared statement cache is per-connection with default limits:
- Max statements: 100
- Max portals: 50

### Logging Configuration

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
# Debug logging
RUST_LOG=debug heliosdb-lite start --data-dir ./mydata

# Production (errors only)
RUST_LOG=error heliosdb-lite start --data-dir ./mydata
```

---

## Migration from PostgreSQL

### Step 1: Export Data

```bash
pg_dump -h postgres-host -U user dbname > dump.sql
```

### Step 2: Clean Dump (Remove Unsupported Features)

```bash
# Remove unsupported PostgreSQL-specific syntax
sed -i '/CREATE EXTENSION/d' dump.sql
sed -i '/ALTER DEFAULT/d' dump.sql
```

### Step 3: Import to HeliosDB-Lite

```bash
# Start HeliosDB-Lite server
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir ./mydata

# Import dump
psql -h 127.0.0.1 -p 5432 -U postgres -f dump.sql
```

### Compatibility Notes

**Supported PostgreSQL Features:**
- CREATE TABLE, DROP TABLE, CREATE VIEW
- INSERT, SELECT, UPDATE, DELETE
- BEGIN, COMMIT, ROLLBACK
- Indexes, foreign keys, triggers, SERIAL columns
- Roles, GRANT and REVOKE
- JSON/JSONB
- Basic data types

**Unsupported (Use Alternatives):**
- Extensions → Native HeliosDB features (vector search, time travel, branching)

---

## Next Steps

1. **Start a server:** `heliosdb-lite start --memory` (after exporting `HELIOSDB_JWT_SECRET`)
2. **Report issues:** support@heliosdb.com