PostgreSQL Protocol Quick Start Guide

For developers integrating HeliosDB-Lite with PostgreSQL clients

---

5-Minute Quick Start

1. Start the Server

cargo run --example postgres_server_extended

2. Connect with psql

psql -h 127.0.0.1 -p 5432 -U postgres

3. Run Queries

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

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

use heliosdb_lite::{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

use heliosdb_lite::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)

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)

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)

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)

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 (SELECT, INSERT, UPDATE, DELETE)
• Extended query protocol (prepared statements)
• Transactions (BEGIN, COMMIT, ROLLBACK)
• Parameter binding ($1, $2, etc.)
• All common data types (INT, TEXT, FLOAT, BOOLEAN, JSON, etc.)
• Multiple concurrent connections
• Authentication (Trust, CleartextPassword)

⚠️ Partial Support

• MD5 authentication (basic implementation)
• SCRAM-SHA-256 (needs full SASL flow)
• pg_catalog system tables (minimal emulation)

❌ Not Yet Supported

• SSL/TLS encryption (coming in v2.1)
• COPY protocol (coming in v2.1)
• Binary result format (coming in v2.2)
• Listen/Notify (coming in v3.0)
• Cursors (coming in v3.0)

---

Common Use Cases

Case 1: Embedded Database with Network Access

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

// 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.

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

// 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.

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

// 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.

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

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

for i in range(1000):
    cur.execute(f"SELECT * FROM users WHERE id = {i}")

Fast (prepare once, reuse):

for i in range(1000):
    cur.execute("SELECT * FROM users WHERE id = %s", (i,))

2. Batch Operations

Slow (1000 round-trips):

for user in users:
    cur.execute("INSERT INTO users VALUES (%s, %s)", (user.id, user.name))

Fast (1 round-trip):

cur.executemany(
    "INSERT INTO users VALUES (%s, %s)",
    [(u.id, u.name) for u in users]
)

3. Use Transactions

Slow (auto-commit each statement):

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):

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):

def handle_request():
    conn = psycopg2.connect(...)
    # ... use connection ...
    conn.close()

Fast (reuse connections):

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

// Development
.auth_method(AuthMethod::Trust)

// Production
.auth_method(AuthMethod::ScramSha256)

2. Bind to Localhost Only

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

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

3. Limit Connections

.max_connections(50) // Prevent resource exhaustion

4. Use SSH Tunnel for Remote Access

# 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

# 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

use heliosdb_lite::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:

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

Logging Configuration

# Set log level via environment
RUST_LOG=heliosdb_lite=debug cargo run

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

# Production (errors only)
RUST_LOG=error cargo run

---

Migration from PostgreSQL

Step 1: Export Data

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

Step 2: Clean Dump (Remove Unsupported Features)

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

# Start HeliosDB-Lite 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 - Views → Materialized views (coming) - 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: /docs/implementation/POSTGRES_PROTOCOL_IMPLEMENTATION_SUMMARY.md
3. Run tests: cargo test postgres_extended_protocol_tests
4. Report issues: GitHub Issues

Happy coding! 🚀