Connecting clients: psql, Python, JDBC

What you’ll get in 15 minutes

After this tutorial, you will have three working methods to connect to a locally running AngaraBase instance:

1. psql — interactive PostgreSQL console.
2. Python via psycopg[binary] — a typical application script.
3. JDBC via standard org.postgresql:postgresql — a typical Java/Kotlin/Scala stack.

All three methods work via the standard pgwire protocol: AngaraBase presents itself to clients as PostgreSQL, so no special drivers are needed.

Prerequisites

• AngaraBase running locally according to Quickstart. We assume the server is listening on 127.0.0.1:5432, and there is a user angara and a database angara_demo.
• Installed psql (any PostgreSQL version ≥ 13).
• Python 3.10+ (for Step 2).
• JDK 17+ and Maven/Gradle (for Step 3).

Verify that the server is responding:

psql --version
# psql (PostgreSQL) 16.4

ss -ltnp 'sport = 5432'
# LISTEN ... 127.0.0.1:5432 ...

If the port is not listening — go back to Quickstart and make sure angarabased started without errors.

Step 1. psql — interactive console

1.1. Connecting

psql 'postgresql://angara@127.0.0.1:5432/angara_demo'

Password (if set) — at the prompt. Success sign: the angara_demo=> prompt.

1.2. Minimal scenario: create a table, insert, select

-- Inside psql:
CREATE TABLE products (
    id    BIGINT PRIMARY KEY,
    name  TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

INSERT INTO products (id, name, price) VALUES
    (1, 'Coffee', 4.50),
    (2, 'Tea',    3.00);

SELECT id, name, price FROM products ORDER BY id;

Expected output:

 id | name   | price
----+--------+-------
  1 | Coffee |  4.50
  2 | Tea    |  3.00
(2 rows)

1.3. Useful \ commands

1.4. If something goes wrong

• could not connect to server: Connection refused — the server isn’t running or isn’t listening on 127.0.0.1. Check ss -ltnp 'sport = 5432' and the angarabased logs.
• authentication failed for user "angara" — password isn’t set or doesn’t match. See Authentication.
• feature_not_supported (0A000) — you hit an SQL construct that AngaraBase does not support. This is an explicit fail-closed contract; see Known Issues and SQLSTATE.

Step 2. Python via psycopg

2.1. Installing the driver

We use psycopg version 3 (binary wheel — without local compilation):

python3 -m venv .venv
source .venv/bin/activate
pip install 'psycopg[binary]>=3.1,<4'

2.2. Minimal script

Create a file connect_demo.py:

import psycopg

DSN = "postgresql://angara@127.0.0.1:5432/angara_demo"

with psycopg.connect(DSN) as conn:
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO products (id, name, price) VALUES (%s, %s, %s)",
            (3, "Espresso", 4.25),
        )
        cur.execute("SELECT id, name, price FROM products ORDER BY id")
        for row in cur.fetchall():
            print(row)
    conn.commit()

Run:

python3 connect_demo.py

Expected output:

(1, 'Coffee', Decimal('4.50'))
(2, 'Tea', Decimal('3.00'))
(3, 'Espresso', Decimal('4.25'))

2.3. Important things to know about the Python client

• Parameterized queries are mandatory. Don’t substitute values via f"...{value}..." — this is a path to SQL injections. psycopg substitutes parameters on the driver side via server-side prepared statements.
• with conn: and conn.commit() are different things. The with conn: context manager guarantees connection closure, but does not auto-commit. The transaction is committed only by an explicit conn.commit().
• AngaraBase predictably returns SQLSTATE. Catch psycopg.errors.FeatureNotSupported and check e.diag.sqlstate == "0A000" to gracefully handle unsupported constructs (fail-closed contract).

Step 3. JDBC via org.postgresql:postgresql

3.1. Dependency

Maven (pom.xml):

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.7.4</version>
</dependency>

Gradle (build.gradle.kts):

dependencies {
    implementation("org.postgresql:postgresql:42.7.4")
}

3.2. Minimal class

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ConnectDemo {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://127.0.0.1:5432/angara_demo";
        java.util.Properties props = new java.util.Properties();
        props.setProperty("user", "angara");
        props.setProperty("preferQueryMode", "simple");

        try (Connection conn = DriverManager.getConnection(url, props)) {
            conn.setAutoCommit(false);

            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO products (id, name, price) VALUES (?, ?, ?)")) {
                ps.setLong(1, 4L);
                ps.setString(2, "Cappuccino");
                ps.setBigDecimal(3, new java.math.BigDecimal("4.75"));
                ps.executeUpdate();
            }

            try (PreparedStatement ps = conn.prepareStatement(
                    "SELECT id, name, price FROM products ORDER BY id");
                 ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.printf(
                            "%d %s %s%n",
                            rs.getLong("id"),
                            rs.getString("name"),
                            rs.getBigDecimal("price"));
                }
            }

            conn.commit();
        }
    }
}

3.3. Important things to know about the JDBC client

• preferQueryMode=simple is the recommended default for AngaraBase. This disables the aggressive probe mode of the extended protocol that the driver uses for compatibility with PostgreSQL extensions. AngaraBase implements pgwire by contract, and some extended protocol checks are not needed.
• assumeMinServerVersion=9.0 — add to props if you plan to work via DBeaver/DataGrip; see Client compatibility → DBeaver.
• Transactions and setAutoCommit(false). AngaraBase implements MVCC via UNDO-log; explicit transactions provide a predictable snapshot. Do not leave long transactions open — this slows down AngaraGC.

What’s Next

• SQL Compatibility Overview — what exactly from the SQL standard is available via all three clients.
• Client Compatibility — reference guide for specific GUI tools and drivers (DBeaver, IntelliJ DataGrip, etc.).
• Security Model — how to set up TLS, SCRAM, and certificate authentication for production connections.
• Known Issues and SQLSTATE — what codes the client should expect instead of “magic” values.