Intermediate18 min read

Application Development

Connect applications to PostgreSQL with pooling, drivers, ORMs, and error handling.

Connection Pools

PgBouncer or built-in poolers multiplex many client connections to fewer server connections. Size pools per instance × instances below max_connections minus admin headroom.

Use prepared statements carefully with transaction pooling mode—session pooling safer for prepared statements and temp tables.

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
});

Drivers

node-postgres (pg), psycopg3, JDBC, and async drivers support PostgreSQL features including COPY, LISTEN/NOTIFY, and array types. Use parameterized queries exclusively.

COPY FROM STDIN bulk loads CSV faster than INSERT batches for migrations.

Set application_name in connection for pg_stat_activity tracing
Use SSL in production connection strings
Handle connection errors with retry for transient failures

ORMs

SQLAlchemy, TypeORM, Prisma, Drizzle, and ActiveRecord map objects to tables. Leverage PostgreSQL-specific types (JSONB, arrays) in schema definitions.

Review generated SQL for N+1 queries and missing indexes. Use migrations as canonical schema history.

// Prisma schema excerpt
model User {
  id    BigInt @id @default(autoincrement())
  email String @unique
  meta  Json?
}

Error Handling

Map SQLSTATE codes—23505 unique violation, 40001 serialization failure, 40P01 deadlock. Retry serialization failures and deadlocks with backoff.

Use transactions for multi-step operations; rollback on any failure before releasing connection.

catch (error) {
  if (error.code === "23505") throw new ConflictError("Duplicate");
  if (error.code === "40001") return retry(fn);
  throw error;
}

Testing

Testcontainers or Docker Compose PostgreSQL for integration tests. Use transactions rolled back after each test or template database cloning for speed.

Match PostgreSQL major version in CI to production. Test migrations up and down.