2.2.1. Connecting and Querying with SDKs

💡 First Principle: PostgreSQL connections are heavyweight server processes, so the driver rituals — connect once, parameterize always, encrypt by default — all exist to respect what a connection actually costs. Get the ritual right and everything downstream (2.2.5's pooling story) makes sense.

Python speaks to Flexible Server through psycopg (synchronous) or asyncpg (async — the right choice inside async AI orchestration code). Flexible Server enforces TLS, so connection strings must request encryption:

import psycopg

conn = psycopg.connect(
    host="myserver.postgres.database.azure.com",
    dbname="ragdb", user="app_user", password=pw,
    sslmode="require"
)
with conn.cursor() as cur:
    cur.execute("SELECT content FROM chunks WHERE source = %s", ("faq",))
    rows = cur.fetchall()

The %s placeholder is non-negotiable — string-formatted SQL is an injection vulnerability, and exam code snippets test whether you notice. For passwordless auth, Flexible Server supports Microsoft Entra ID: acquire a token via DefaultAzureCredential and pass it as the password — the same identity-over-secrets theme from 2.1.1 and Phase 5.

asyncpg uses $1-style parameters and connection pools (asyncpg.create_pool) — pools preview the throughput story in 2.2.5.

⚠️ Exam Trap: "Connection refused/SSL required" scenarios almost always resolve to a missing sslmode=require (psycopg) or ssl=True (asyncpg). Distractors point at firewalls or credentials; check the SSL parameter first.

Reflection Question: Why is opening a fresh connection per request more damaging in PostgreSQL than in Cosmos DB's SDK model?