Skip to content
HeliosDB HeliosDB Lite

PostgreSQL Protocol Quick Start Guide

For developers integrating HeliosDB-Lite with PostgreSQL clients

5-Minute Quick Start

1. Start the Server

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

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

Server Options

Persistent Data and Custom Port

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

Password Authentication and Remote Clients

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

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

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

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

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

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

# 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

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

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

config.toml
[server]
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

Additional Users

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

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

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 '/ALTER DEFAULT/d' dump.sql

Step 3: Import to HeliosDB-Lite

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