Skip to content
HeliosDB Nano

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

Terminal window
cargo run --example postgres_server_extended

2. Connect with psql

Terminal window
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_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

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)

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.

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

Terminal window
# 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_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:

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

Logging Configuration

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

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

Step 2: Clean Dump (Remove Unsupported Features)

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

Terminal window
# 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
  • 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! 🚀