Connect from Python and TypeScript Safely

Applications should connect through a bounded database adapter that owns pooling, pgvector type registration, parameterized SQL, transactions, timeouts, and result mapping. Python's psycopg and TypeScript's node-postgres can both issue the same reviewed SQL. Keep tenant identity server-derived, validate vector dimensions, and never let framework convenience obscure transaction or query-plan behavior.

What will you be able to do?

Connect with a pool and a secret environment variable.
Register vector types where the client library requires it.
Pass vectors as parameters.
Set per-transaction timeouts.
Test the adapter with deterministic vectors.

What does a minimal Python adapter look like?

python

import os
from psycopg_pool import ConnectionPool
from pgvector.psycopg import register_vector

pool = ConnectionPool(os.environ["DATABASE_URL"], min_size=1, max_size=10)

def search(tenant_id: str, query_vector: list[float], limit: int = 8):
    safe_limit = max(1, min(limit, 50))
    with pool.connection() as conn:
        register_vector(conn)
        with conn.transaction():
            conn.execute("SET LOCAL statement_timeout = '1500ms'")
            return conn.execute(
                """
                SELECT c.id, c.content, c.embedding <=> %s AS distance
                FROM app.chunks c
                JOIN app.documents d ON d.id = c.document_id
                WHERE d.tenant_id = %s AND d.deleted_at IS NULL
                ORDER BY c.embedding <=> %s
                LIMIT %s
                """,
                (query_vector, tenant_id, query_vector, safe_limit),
            ).fetchall()

Add psycopg_pool to dependencies. In a real adapter, derive the model ID and expected dimensions, set role or session identity for RLS, map results to typed objects, and emit safe metrics.

What does TypeScript add?

With pg, vectors can be passed in the textual form supported by pgvector or through a library codec. Keep conversion in one function and validate finite numbers and length.

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });
const vectorLiteral = (values: readonly number[]) =>
  `[${values.map((value) => {
    if (!Number.isFinite(value)) throw new Error("Vector contains a non-finite value");
    return value.toString();
  }).join(",")}]`;

export async function search(tenantId: string, embedding: readonly number[]) {
  const result = await pool.query(
    `SELECT id, content, embedding <=> $1::vector AS distance
     FROM app.chunks
     WHERE tenant_id = $2
     ORDER BY embedding <=> $1::vector
     LIMIT 8`,
    [vectorLiteral(embedding), tenantId],
  );
  return result.rows;
}

The example is intentionally SQL-forward. Adapt column names to the canonical schema and use transactions when setting local policy context.

How do you verify it?

Test missing DATABASE_URL, wrong dimensions, NaN/Infinity, connection failure, timeout, cross-tenant fixtures, rollback, and pool exhaustion. Compare adapter output with the same SQL in psql. Log query fingerprints and duration—not vectors, raw prompts, credentials, or sensitive content.

What breaks in production?

A new pool is created per request.
Idle transactions retain connections and old snapshots.
Tenant ID comes from a request body.
Query vectors are logged.
Driver timeouts and server statement timeouts disagree.
Error handling retries non-idempotent transactions blindly.

AI pair-work prompt

Review this database adapter [paste]. Check pool lifecycle, parameterization, pgvector encoding, transaction boundaries, statement timeouts, tenant provenance, retries, typed results, and sensitive logging. Produce tests before refactoring code.

Verification contract: Run the adapter against the same deterministic fixture as psql; assert ordered IDs, denial behavior, rollback, timeouts, and no leaked connections.

Check your understanding

Why should one process reuse a pool?
What must vector validation check?
Add model ID and language parameters to one adapter.

Official references