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

# PostgreSQL Protocol Quick Start Guide

**For developers integrating HeliosDB Nano with PostgreSQL clients**

---

## 5-Minute Quick Start

### 1. Start the Server

```bash
cargo run --example postgres_server_extended
```

### 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')
```

---

## Programmatic Usage

### Starting a Server

```rust
use heliosdb_nano::{EmbeddedDatabase, protocol::postgres::PgServerBuilder};
use std::sync::Arc;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Create database
    let db = Arc::new(EmbeddedDatabase::new_in_memory()?);

    // Setup schema
    db.execute("CREATE TABLE users (id INT, name TEXT)")?;
    db.execute("INSERT INTO users VALUES (1, 'Alice')")?;

    // Start PostgreSQL server
    let server = PgServerBuilder::new()
        .address("127.0.0.1:5432".parse()?)
        .build(db);

    server.serve().await?;
    Ok(())
}
```

### Custom Configuration

```rust
use heliosdb_nano::protocol::postgres::{PgServerBuilder, AuthMethod};

let server = PgServerBuilder::new()
    .address("0.0.0.0:5432".parse()?)
    .auth_method(AuthMethod::CleartextPassword) // Or Trust, Md5, ScramSha256
    .max_connections(50)
    .build(db);
```

---

## 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
- DML with `RETURNING` for `INSERT`, `UPDATE`, and `DELETE`
- Text-format parameters for common scalar and document types
- Binary-format parameters for common scalar types, including integers, floats, booleans, text, UUID, and timestamp values
- Binary-format results for common scalar types, including booleans, integers, floats, bytea, text/varchar, and UUID
- Multiple concurrent connections with a configurable connection limit
- Authentication with Trust, CleartextPassword, MD5, and SCRAM-SHA-256
- SSL/TLS negotiation when an `SslConfig` is supplied

### Partial Support

- `pg_catalog` and `information_schema` compatibility cover common client and ORM introspection paths, but they are not a complete PostgreSQL catalog clone.
- Binary result formats fall back to text for types that do not yet have a PostgreSQL binary encoder, such as JSON, date/time, interval, vectors, and custom internal references.
- 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

- COPY protocol
- LISTEN/NOTIFY over the PostgreSQL wire protocol

---

## Common Use Cases

### Case 1: Embedded Database with Network Access

**Scenario:** SQLite-style embedded database that also accepts network connections.

```rust
// Create embedded database
let db = Arc::new(EmbeddedDatabase::new("./mydata.helio")?);

// Access locally
db.execute("CREATE TABLE logs (message TEXT)")?;

// Also expose via PostgreSQL protocol
tokio::spawn(async move {
    let server = PgServerBuilder::new()
        .address("127.0.0.1:5432".parse()?)
        .build(db.clone());
    server.serve().await
});

// Local and remote access to same database
```

### Case 2: Testing PostgreSQL Applications

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

```rust
#[tokio::test]
async fn test_my_app() {
    let db = Arc::new(EmbeddedDatabase::new_in_memory()?);

    // Start server in background
    let db_clone = db.clone();
    tokio::spawn(async move {
        let server = PgServerBuilder::new()
            .address("127.0.0.1:15432".parse().unwrap())
            .build(db_clone);
        server.serve().await
    });

    // Wait for server to start
    tokio::time::sleep(Duration::from_millis(100)).await;

    // Connect with your application
    let conn = postgres::Config::new()
        .host("127.0.0.1")
        .port(15432)
        .user("postgres")
        .connect(postgres::NoTls)?;

    // Run tests...
}
```

### Case 3: Lightweight PostgreSQL Alternative

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

```rust
// Single binary, no installation
let db = Arc::new(EmbeddedDatabase::new("./data")?);

let server = PgServerBuilder::new()
    .address("0.0.0.0:5432".parse()?)
    .auth_method(AuthMethod::CleartextPassword)
    .max_connections(20)
    .build(db);

// Compatible with existing PostgreSQL tools
// psql, pgAdmin, DBeaver, etc.
server.serve().await?;
```

---

## Troubleshooting

### Connection Refused

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

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

```bash
# Check server logs
RUST_LOG=debug cargo run --example postgres_server_extended

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

### Authentication Failed

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

**Fix:** Check authentication method.

```rust
// For testing, use Trust auth (no password)
.auth_method(AuthMethod::Trust)

// For production, use CleartextPassword or ScramSha256
.auth_method(AuthMethod::CleartextPassword)
```

### 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

```rust
// Development
.auth_method(AuthMethod::Trust)

// Production
.auth_method(AuthMethod::ScramSha256)
```

### 2. Bind to Localhost Only

```rust
// Development (accessible from network)
.address("0.0.0.0:5432".parse()?)

// Production (local only)
.address("127.0.0.1:5432".parse()?)
```

### 3. Limit Connections

```rust
.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

### Custom Authentication

```rust
use heliosdb_nano::protocol::postgres::{AuthManager, AuthMethod};

// Create custom auth manager
let mut auth = AuthManager::new(AuthMethod::CleartextPassword);
auth.add_user("alice".to_string(), "secret123".to_string());
auth.add_user("bob".to_string(), "password456".to_string());

// Use custom auth
let server = PgServerBuilder::new()
    .address("127.0.0.1:5432".parse()?)
    .auth_manager(auth)
    .build(db);
```

### Statement Cache Tuning

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

These are hardcoded currently but can be made configurable:

```rust
// In your connection handler (future enhancement)
let prepared_statements = PreparedStatementManager::with_capacity(1000, 500);
```

### Logging Configuration

```bash
# Set log level via environment
RUST_LOG=heliosdb_nano=debug cargo run

# Fine-grained control
RUST_LOG=heliosdb_nano::protocol::postgres=trace cargo run

# Production (errors only)
RUST_LOG=error cargo run
```

---

## 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 '/GRANT/d' dump.sql  # RBAC not yet supported
sed -i '/ALTER DEFAULT/d' dump.sql
```

### Step 3: Import to HeliosDB Nano

```bash
# Start HeliosDB Nano server
cargo run --example postgres_server_extended

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

### Compatibility Notes

**Supported PostgreSQL Features:**
- CREATE TABLE, DROP TABLE
- INSERT, SELECT, UPDATE, DELETE
- BEGIN, COMMIT, ROLLBACK
- Indexes (limited)
- JSON/JSONB
- Basic data types

**Unsupported (Use Alternatives):**
- Extensions → Native HeliosDB features
- Triggers → Application logic
- Regular views → Materialized views when stored results are acceptable
- SERIAL → Manual ID generation
- Foreign keys → Application-level validation

---

## Next Steps

1. **Try the example:** `cargo run --example postgres_server_extended`
2. **Read the docs:** [PostgreSQL Wire Protocol](/docs/nano/features/postgresql_wire_protocol/)
3. **Run tests:** `cargo test postgres_extended_protocol_tests`
4. **Report issues:** GitHub Issues