How do you connect to PostgreSQL from Node.js?

Use the 'pg' (node-postgres) library. Create a Pool for connection pooling, which manages multiple connections efficiently. Never create a new Client for each query - use pool.query() which automatically acquires and releases connections. Configure the pool with host, port, database, user, password, and max connections. For production, use environment variables and SSL.

What is connection pooling and why is it important?

Connection pooling maintains a cache of database connections that can be reused. Creating a new PostgreSQL connection takes 20-50ms due to TCP handshake and authentication. With pooling, you reuse existing connections, reducing latency to sub-millisecond. The pg Pool manages this automatically - it creates connections as needed up to a max limit, queues requests when all connections are busy, and handles connection errors and timeouts.

How do you prevent SQL injection in Node.js?

Use parameterized queries, never string concatenation. With pg library, use $1, $2 placeholders and pass values as an array: pool.query('SELECT * FROM users WHERE id = $1', [userId]). The driver escapes values automatically. ORMs like Prisma and Knex also use parameterized queries internally. Never use template literals or string concatenation to build SQL queries with user input.

How do you handle transactions in Node.js with PostgreSQL?

Get a dedicated client from the pool, run BEGIN, execute queries, then COMMIT or ROLLBACK. Use try/catch/finally to ensure the client is released back to the pool. For complex transactions, consider using a transaction helper function that handles BEGIN/COMMIT/ROLLBACK automatically. Always release the client in a finally block to prevent connection leaks.

What are database migrations and how do you manage them?

Migrations are versioned scripts that modify database schema. They allow teams to track schema changes in version control and apply them consistently across environments. Tools like Knex, Prisma, and node-pg-migrate create timestamped migration files with up() and down() functions. Run migrations during deployment, never manually modify production schemas. Migrations should be idempotent and reversible when possible.

How do you optimize slow PostgreSQL queries?

First, use EXPLAIN ANALYZE to see the query plan and actual execution time. Look for sequential scans on large tables - these often need indexes. Check for missing indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Consider composite indexes for multi-column queries. Monitor slow queries with pg_stat_statements. Common fixes: add indexes, rewrite subqueries as JOINs, limit result sets, and use connection pooling.

How to Explain PostgreSQL with Node.js in Your Interview

PostgreSQL powers some of the world's most demanding applications—Instagram, Spotify, Reddit. When interviewers ask about PostgreSQL in a Node.js context, they're testing whether you understand database fundamentals beyond basic CRUD. Connection pooling, transaction isolation, query optimization—these separate developers who've built production systems from those who've only followed tutorials.

The 30-Second Answer

When the interviewer asks "How do you work with PostgreSQL in Node.js?", here's your concise answer:

"I use the pg library with connection pooling. The Pool manages database connections efficiently—creating them as needed, reusing them across requests, and handling failures. For queries, I always use parameterized queries with $1, $2 placeholders to prevent SQL injection. For transactions, I get a dedicated client from the pool, wrap operations in BEGIN/COMMIT, and ensure the client is released in a finally block."

Short, practical, and shows you understand the key patterns.

The 2-Minute Answer (If They Want More)

If they ask for more detail:

"The pg library is the standard PostgreSQL driver for Node.js. For production apps, I create a connection pool at startup rather than connecting per-request—each new connection has ~30ms overhead from TCP and authentication. The pool maintains idle connections ready to use.

Security is critical with databases. I never concatenate user input into SQL strings. Parameterized queries separate the query structure from data, so even malicious input can't alter the query logic.

For schema management, I use migrations through tools like Prisma or Knex. Every schema change is a versioned file in source control, applied automatically during deployment. This ensures consistency across dev, staging, and production.

When performance matters, I analyze queries with EXPLAIN ANALYZE to find slow operations. Usually it's missing indexes—PostgreSQL defaults to sequential scans which get slow on large tables. Adding the right indexes can turn a 2-second query into 2 milliseconds."

The Code Example That Impresses

Draw this on the whiteboard and walk through it:

// db.js - Database connection setup
const { Pool } = require('pg');
 
const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,                // Maximum connections in pool
  idleTimeoutMillis: 30000,  // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Fail if can't connect in 2s
  ssl: process.env.NODE_ENV === 'production'
    ? { rejectUnauthorized: false }
    : false
});
 
// Verify connection on startup
pool.on('error', (err) => {
  console.error('Unexpected pool error', err);
  process.exit(-1);
});
 
module.exports = pool;

// userRepository.js - Using the pool
const pool = require('./db');
 
// Simple query - pool handles connection automatically
async function getUserById(id) {
  const result = await pool.query(
    'SELECT id, name, email FROM users WHERE id = $1',
    [id]
  );
  return result.rows[0];
}
 
// Insert with returning
async function createUser(name, email, passwordHash) {
  const result = await pool.query(
    `INSERT INTO users (name, email, password_hash, created_at)
     VALUES ($1, $2, $3, NOW())
     RETURNING id, name, email, created_at`,
    [name, email, passwordHash]
  );
  return result.rows[0];
}
 
// Search with multiple conditions
async function searchUsers(searchTerm, limit = 20, offset = 0) {
  const result = await pool.query(
    `SELECT id, name, email
     FROM users
     WHERE name ILIKE $1 OR email ILIKE $1
     ORDER BY created_at DESC
     LIMIT $2 OFFSET $3`,
    [`%${searchTerm}%`, limit, offset]
  );
  return result.rows;
}

Walk through the key points:

Pool is created once at app startup, not per request
max: 20 limits concurrent connections (tune based on your PostgreSQL max_connections)
pool.query() automatically acquires and releases connections
Parameterized queries with $1, $2 prevent SQL injection
RETURNING clause gets inserted data without a second query

Connection Pooling Deep Dive

This is where interviews get technical. Understanding pooling shows production experience:

// What happens under the hood
const pool = new Pool({ max: 10 });
 
// Request 1: No idle connections, creates new one (~30ms)
await pool.query('SELECT 1');
 
// Request 2: Reuses connection from Request 1 (~0.1ms)
await pool.query('SELECT 2');
 
// 11 concurrent requests: 10 run immediately, 1 waits in queue
const promises = Array(11).fill().map(() =>
  pool.query('SELECT pg_sleep(1)')  // 1 second each
);
await Promise.all(promises);  // Takes ~2 seconds total
 
// Check pool status
console.log({
  total: pool.totalCount,      // Total connections created
  idle: pool.idleCount,        // Available connections
  waiting: pool.waitingCount   // Queued requests
});

Common interview question: "What happens when all pool connections are busy?"

"New queries wait in a queue until a connection is released. If connectionTimeoutMillis is set, queued requests will fail after that timeout. This prevents indefinite waiting but can cause errors under heavy load. The solution is either increasing max connections (up to PostgreSQL's limit, typically 100), or using a connection pooler like PgBouncer for very high throughput."

Pool vs Client

Know when to use each:

// Pool - for most operations (automatic connection management)
await pool.query('SELECT * FROM users');
 
// Client - for transactions (need same connection throughout)
const client = await pool.connect();
try {
  await client.query('BEGIN');
  // ... multiple queries on same connection
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();  // CRITICAL: always release
}

Interview trap: Forgetting client.release() causes connection leaks. The pool eventually exhausts, and all new queries hang waiting for connections.

Transactions: The Critical Pattern

Transactions ensure multiple operations succeed or fail together:

async function transferMoney(fromAccount, toAccount, amount) {
  const client = await pool.connect();
 
  try {
    await client.query('BEGIN');
 
    // Debit from source account
    const debitResult = await client.query(
      `UPDATE accounts
       SET balance = balance - $1
       WHERE id = $2 AND balance >= $1
       RETURNING balance`,
      [amount, fromAccount]
    );
 
    if (debitResult.rowCount === 0) {
      throw new Error('Insufficient funds or account not found');
    }
 
    // Credit to destination account
    await client.query(
      `UPDATE accounts
       SET balance = balance + $1
       WHERE id = $2`,
      [amount, toAccount]
    );
 
    // Record the transfer
    await client.query(
      `INSERT INTO transfers (from_account, to_account, amount, created_at)
       VALUES ($1, $2, $3, NOW())`,
      [fromAccount, toAccount, amount]
    );
 
    await client.query('COMMIT');
    return { success: true };
 
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Key insight: The WHERE balance >= $1 in the debit query prevents overdrafts at the database level—even if application logic has a bug, the transaction fails safely.

Transaction Helper Pattern

For cleaner code, create a reusable transaction wrapper:

async function withTransaction(callback) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}
 
// Usage
await withTransaction(async (client) => {
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2]);
  return { transferred: 100 };
});

Migrations: Schema Version Control

Interviewers ask about migrations to test team workflow understanding:

// Using Knex migrations
// migrations/20240115120000_create_users.js
 
exports.up = function(knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('email').unique().notNullable();
    table.string('password_hash').notNullable();
    table.string('name');
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
 
    // Index for email lookups
    table.index('email');
  });
};
 
exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

# Run migrations
npx knex migrate:latest
 
# Rollback last migration
npx knex migrate:rollback
 
# Create new migration
npx knex migrate:make add_user_roles

Prisma Approach

Modern alternative with type safety:

// prisma/schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
 
  @@index([email])
}
 
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
 
  @@index([authorId])
}

# Generate migration from schema changes
npx prisma migrate dev --name add_posts_table
 
# Apply migrations in production
npx prisma migrate deploy

Interview question: "Why use migrations instead of manual SQL scripts?"

"Migrations provide version control for database schema. Every team member applies the same changes in the same order. CI/CD pipelines can automatically migrate staging and production. If something breaks, we can rollback. Without migrations, you're manually running scripts and hoping everyone's database matches—that doesn't scale."

Indexing and Query Optimization

This separates mid-level from senior candidates:

-- Without index: Sequential scan on 1M rows (~500ms)
SELECT * FROM users WHERE email = 'john@example.com';
 
-- With index: Index scan (~1ms)
CREATE INDEX idx_users_email ON users(email);
 
-- Composite index for common query patterns
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
 
-- This query uses the composite index efficiently
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 10;

EXPLAIN ANALYZE

The essential debugging tool:

async function analyzeQuery(query, params) {
  const result = await pool.query(
    `EXPLAIN ANALYZE ${query}`,
    params
  );
  return result.rows.map(r => r['QUERY PLAN']).join('\n');
}
 
// Usage
const plan = await analyzeQuery(
  'SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC',
  [123]
);
console.log(plan);

-- Output shows what's happening
Index Scan using idx_orders_user_date on orders
  Index Cond: (user_id = 123)
  Rows Removed by Filter: 0
  Planning Time: 0.150 ms
  Execution Time: 0.045 ms

Red flags to look for:

Seq Scan on large tables → needs index
High Rows Removed by Filter → index not selective enough
Sort operations → consider index with ORDER BY columns
Execution time >> Planning time for simple queries → usually OK

// Finding slow queries in production
async function getSlowQueries() {
  // Requires pg_stat_statements extension
  const result = await pool.query(`
    SELECT query, calls, mean_time, total_time
    FROM pg_stat_statements
    ORDER BY mean_time DESC
    LIMIT 10
  `);
  return result.rows;
}

Common Interview Questions

"How do you handle N+1 query problems?"

// N+1 Problem: 1 query for users + N queries for posts
const users = await pool.query('SELECT * FROM users LIMIT 10');
for (const user of users.rows) {
  // This runs 10 separate queries!
  const posts = await pool.query(
    'SELECT * FROM posts WHERE user_id = $1',
    [user.id]
  );
}
 
// Solution: JOIN or batch query
const result = await pool.query(`
  SELECT u.*,
         json_agg(p.*) as posts
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id
  LIMIT 10
`);

"What's the difference between PostgreSQL and MySQL?"

"PostgreSQL has stronger ACID compliance, better support for complex queries (CTEs, window functions), and advanced data types (JSONB, arrays, full-text search). MySQL is often faster for simple read-heavy workloads. PostgreSQL's MVCC handles concurrent writes better. For Node.js backends with complex data requirements, I prefer PostgreSQL. For simple CRUD with high read throughput, MySQL can be a good choice."

"How do you handle database connection errors?"

// Retry logic for transient failures
async function queryWithRetry(sql, params, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await pool.query(sql, params);
    } catch (err) {
      const isTransient =
        err.code === 'ECONNREFUSED' ||
        err.code === '57P01' ||  // admin_shutdown
        err.code === '57P02';    // crash_shutdown
 
      if (!isTransient || attempt === maxRetries) {
        throw err;
      }
 
      // Exponential backoff
      await new Promise(r => setTimeout(r, Math.pow(2, attempt) * 100));
    }
  }
}

"How do you handle database credentials securely?"

"Never hardcode credentials. Use environment variables loaded from a secrets manager in production—AWS Secrets Manager, HashiCorp Vault, or platform-specific solutions. In development, use .env files that are gitignored. For CI/CD, inject secrets at runtime. The pg library reads standard PostgreSQL environment variables (PGHOST, PGUSER, etc.) automatically, which works well with container orchestration."

PostgreSQL-Specific Features

Know these to show depth:

JSONB for Flexible Data

// Store and query JSON data
await pool.query(`
  CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50),
    data JSONB,
    created_at TIMESTAMP DEFAULT NOW()
  )
`);
 
// Insert JSON
await pool.query(
  `INSERT INTO events (type, data) VALUES ($1, $2)`,
  ['user_signup', { userId: 123, source: 'google', plan: 'pro' }]
);
 
// Query inside JSON
const result = await pool.query(`
  SELECT * FROM events
  WHERE data->>'source' = 'google'
  AND (data->>'plan')::text = 'pro'
`);
 
// Index JSON fields for performance
await pool.query(`
  CREATE INDEX idx_events_source ON events ((data->>'source'))
`);

Array Operations

// PostgreSQL arrays are powerful
await pool.query(`
  CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    tags TEXT[]
  )
`);
 
// Insert with array
await pool.query(
  `INSERT INTO articles (title, tags) VALUES ($1, $2)`,
  ['PostgreSQL Tips', ['database', 'postgresql', 'backend']]
);
 
// Query arrays
const result = await pool.query(`
  SELECT * FROM articles
  WHERE 'postgresql' = ANY(tags)
`);

What Interviewers Actually Look For

When I ask about PostgreSQL, I'm checking:

Connection management - Do you understand pooling and why it matters?
Security awareness - Do you use parameterized queries instinctively?
Transaction knowledge - Can you handle multi-step operations safely?
Performance thinking - Do you know how to diagnose slow queries?
Production experience - Have you dealt with migrations, monitoring, failures?

A candidate who can explain connection pooling, write a safe transaction, and discuss indexing strategy demonstrates real production experience.

Quick Reference

Concept	What to Remember
Connection	Use Pool, not Client for general queries
Pooling	Reuses connections, ~30ms savings per query
Parameterized	`$1, $2` placeholders, values as array
Transactions	BEGIN → queries → COMMIT/ROLLBACK, release client
Migrations	Version-controlled schema changes
EXPLAIN ANALYZE	Find slow queries, look for Seq Scan
Indexing	Add indexes on WHERE, JOIN, ORDER BY columns
N+1	Batch queries or use JOINs
JSONB	Flexible schema within relational database

Practice Questions

Test yourself before your interview:

1. What's wrong with this code?

app.get('/user/:id', async (req, res) => {
  const client = await pool.connect();
  const result = await client.query(
    `SELECT * FROM users WHERE id = ${req.params.id}`
  );
  res.json(result.rows[0]);
});

2. Why might this code cause problems under load?

async function getUser(id) {
  const client = await pool.connect();
  const result = await client.query('SELECT * FROM users WHERE id = $1', [id]);
  return result.rows[0];
}

3. A query that was fast is now taking 5 seconds. What do you check first?

Answers:

Two problems: SQL injection (string interpolation instead of parameterized query) and connection leak (client never released). Should be pool.query() with $1 parameter.
Connection leak—client.release() is never called. Under load, the pool exhausts and new requests hang. Use pool.query() for simple queries, or add try/finally with release.
Run EXPLAIN ANALYZE to see the query plan. Check for sequential scans that indicate missing indexes. Verify table hasn't grown significantly. Check for lock contention with pg_stat_activity.

If you found this helpful, check out these related guides:

Complete Node.js Backend Developer Interview Guide - comprehensive preparation guide for backend interviews
SQL JOINs Interview Guide - Master all JOIN types with visual examples
MongoDB Interview Guide - NoSQL alternative: when to choose MongoDB vs PostgreSQL
Node.js Advanced Interview Guide - Async patterns that work with database operations
System Design Interview Guide - Database scaling and architecture decisions

Ready for More Database Interview Questions?

This is just one of 50+ backend questions in our complete interview prep guide. Each question includes:

Concise answers for time-pressed interviews
Code examples you can write on a whiteboard
Follow-up questions interviewers actually ask
Insights from real interview experience

Get Full Access to All Backend Questions →

Or try our free preview to see more questions like this.

Written by the EasyInterview team, based on real interview experience from 12+ years in tech and hundreds of technical interviews conducted at companies like BNY Mellon, UBS, and leading fintech firms.