How to Use PostgreSQL With Node.js (pg Library Tutorial 2026)

ORMs are great until they're not. The moment you need a complex window function, a lateral join, or a custom aggregate, you find yourself fighting against the abstraction rather than working with it. That's when you want pg — the PostgreSQL client for Node.js.

The pg library is what ORMs like Prisma use under the hood. It's fast, well-maintained, and gives you direct control over your queries. This tutorial covers everything from initial setup through transactions and error handling — the things you actually need for production code.

Setup and Installation

npm install pg
npm install --save-dev @types/pg  # if using TypeScript

You'll need a PostgreSQL instance. If you're on macOS:

brew install postgresql@16
brew services start postgresql@16
createdb myapp_dev

Or spin up a Docker container:

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_DB=myapp_dev \
  -p 5432:5432 \
  postgres:16

For cloud-based PostgreSQL with a free tier, the MongoDB Atlas equivalent for Postgres is Supabase — the article covers free cloud database options including PostgreSQL-compatible services.

Connection Pool Setup

For applications, you almost always want a connection pool rather than individual connections. Pools reuse existing connections instead of opening and closing one per query — which is expensive:

// src/db/pool.js
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.PGHOST || 'localhost',
  port: parseInt(process.env.PGPORT) || 5432,
  database: process.env.PGDATABASE || 'myapp_dev',
  user: process.env.PGUSER || 'postgres',
  password: process.env.PGPASSWORD,
  max: 20,                    // Maximum pool size
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Fail if can't connect within 2s
  maxUses: 7500,              // Recycle connections after 7500 queries
});

// Test connection on startup
pool.on('connect', (client) => {
  console.log('New database connection established');
});

pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

// Graceful shutdown
process.on('SIGINT', async () => {
  await pool.end();
  console.log('Pool has ended');
  process.exit(0);
});

module.exports = pool;

Alternatively, use a connection string (more common with cloud providers):

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.NODE_ENV === 'production' 
    ? { rejectUnauthorized: false }  // For cloud providers like Heroku
    : false
});

Creating the Table Schema

-- migrations/001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
  id          SERIAL PRIMARY KEY,
  email       VARCHAR(255) UNIQUE NOT NULL,
  name        VARCHAR(255) NOT NULL,
  role        VARCHAR(50) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Auto-update updated_at on row changes
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Index for common queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);

Run it with:

psql -d myapp_dev -f migrations/001_create_users.sql

For more on SQL performance, the SQL query optimization guide covers indexes and EXPLAIN output in depth.

CRUD Queries with Parameterized Inputs

Parameterized queries are non-negotiable. They prevent SQL injection and allow PostgreSQL to cache query plans:

// src/db/users.js
const pool = require('./pool');

// CREATE
const createUser = async ({ email, name, role = 'user' }) => {
  const query = `
    INSERT INTO users (email, name, role)
    VALUES ($1, $2, $3)
    RETURNING *
  `;
  
  const { rows } = await pool.query(query, [email, name, role]);
  return rows[0];
};

// READ — get by ID
const getUserById = async (id) => {
  const { rows } = await pool.query(
    'SELECT * FROM users WHERE id = $1',
    [id]
  );
  return rows[0] || null;
};

// READ — list with pagination and filtering
const getUsers = async ({ role, limit = 20, offset = 0 } = {}) => {
  const conditions = [];
  const params = [];
  
  if (role) {
    params.push(role);
    conditions.push(`role = $${params.length}`);
  }
  
  const whereClause = conditions.length > 0 
    ? `WHERE ${conditions.join(' AND ')}` 
    : '';
  
  // Total count (separate query for accuracy with filters)
  const countQuery = `SELECT COUNT(*) FROM users ${whereClause}`;
  const dataQuery = `
    SELECT id, email, name, role, created_at
    FROM users
    ${whereClause}
    ORDER BY created_at DESC
    LIMIT $${params.length + 1} OFFSET $${params.length + 2}
  `;
  
  const [countResult, dataResult] = await Promise.all([
    pool.query(countQuery, params),
    pool.query(dataQuery, [...params, limit, offset])
  ]);
  
  return {
    users: dataResult.rows,
    total: parseInt(countResult.rows[0].count)
  };
};

// UPDATE
const updateUser = async (id, updates) => {
  const allowed = ['name', 'email', 'role'];
  const fields = Object.keys(updates).filter(k => allowed.includes(k));
  
  if (fields.length === 0) return null;
  
  const setClauses = fields.map((field, i) => `${field} = $${i + 2}`);
  const values = fields.map(f => updates[f]);
  
  const query = `
    UPDATE users
    SET ${setClauses.join(', ')}
    WHERE id = $1
    RETURNING *
  `;
  
  const { rows } = await pool.query(query, [id, ...values]);
  return rows[0] || null;
};

// DELETE
const deleteUser = async (id) => {
  const { rowCount } = await pool.query(
    'DELETE FROM users WHERE id = $1',
    [id]
  );
  return rowCount > 0;
};

// EXISTS check
const emailExists = async (email) => {
  const { rows } = await pool.query(
    'SELECT 1 FROM users WHERE email = $1 LIMIT 1',
    [email]
  );
  return rows.length > 0;
};

module.exports = { createUser, getUserById, getUsers, updateUser, deleteUser, emailExists };

The RETURNING * clause is PostgreSQL-specific and extremely useful — you get the full inserted/updated row back without needing a second SELECT query.

Transactions

Transactions are essential when you need multiple queries to succeed or fail as a unit. The classic example: transferring money between accounts, or creating a user and their associated profile in the same operation.

// src/db/transactions.js
const pool = require('./pool');

// Helper for running a transaction
const withTransaction = async (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();  // Always return the client to the pool
  }
};

// Example: Create user + profile atomically
const createUserWithProfile = async ({ email, name, bio, avatarUrl }) => {
  return withTransaction(async (client) => {
    // Step 1: Create user
    const { rows: [user] } = await client.query(
      'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
      [email, name]
    );
    
    // Step 2: Create profile (linked to user)
    const { rows: [profile] } = await client.query(
      'INSERT INTO profiles (user_id, bio, avatar_url) VALUES ($1, $2, $3) RETURNING *',
      [user.id, bio, avatarUrl]
    );
    
    return { user, profile };
  });
};

// Example: Transfer points between users
const transferPoints = async (fromUserId, toUserId, amount) => {
  return withTransaction(async (client) => {
    // Lock rows to prevent concurrent modifications (SELECT FOR UPDATE)
    const { rows: [sender] } = await client.query(
      'SELECT points FROM users WHERE id = $1 FOR UPDATE',
      [fromUserId]
    );
    
    if (!sender || sender.points < amount) {
      throw new Error('Insufficient points');
    }
    
    await client.query(
      'UPDATE users SET points = points - $2 WHERE id = $1',
      [fromUserId, amount]
    );
    
    await client.query(
      'UPDATE users SET points = points + $2 WHERE id = $1',
      [toUserId, amount]
    );
    
    return { success: true, transferred: amount };
  });
};

module.exports = { withTransaction, createUserWithProfile, transferPoints };

The finally block with client.release() is critical — without it, you'll exhaust your connection pool if a transaction fails.

Advanced Queries

// Full-text search (PostgreSQL built-in)
const searchUsers = async (searchTerm) => {
  const { rows } = await pool.query(`
    SELECT id, email, name,
           ts_rank(to_tsvector('english', name || ' ' || email), plainto_tsquery($1)) AS rank
    FROM users
    WHERE to_tsvector('english', name || ' ' || email) @@ plainto_tsquery($1)
    ORDER BY rank DESC
    LIMIT 20
  `, [searchTerm]);
  
  return rows;
};

// Bulk insert with unnest (much faster than looping)
const bulkCreateUsers = async (users) => {
  const emails = users.map(u => u.email);
  const names = users.map(u => u.name);
  
  const { rows } = await pool.query(`
    INSERT INTO users (email, name)
    SELECT * FROM unnest($1::text[], $2::text[])
    ON CONFLICT (email) DO NOTHING
    RETURNING *
  `, [emails, names]);
  
  return rows;
};

// Upsert pattern
const upsertUser = async ({ email, name }) => {
  const { rows } = await pool.query(`
    INSERT INTO users (email, name)
    VALUES ($1, $2)
    ON CONFLICT (email)
    DO UPDATE SET name = EXCLUDED.name, updated_at = NOW()
    RETURNING *
  `, [email, name]);
  
  return rows[0];
};

// Window functions (ranking, running totals)
const getUsersWithRank = async () => {
  const { rows } = await pool.query(`
    SELECT 
      id,
      name,
      points,
      RANK() OVER (ORDER BY points DESC) AS rank,
      SUM(points) OVER () AS total_points
    FROM users
    ORDER BY points DESC
    LIMIT 10
  `);
  
  return rows;
};

For comparing PostgreSQL against other databases, the PostgreSQL vs MySQL vs SQLite comparison covers the architectural differences.

Error Handling

The pg library throws errors with standard PostgreSQL error codes:

// src/db/errorHandler.js
const handleDbError = (err) => {
  switch (err.code) {
    case '23505':  // unique_violation
      const column = err.constraint?.replace(/_key$/, '') || 'field';
      throw Object.assign(new Error(`${column} already exists`), { statusCode: 409 });
      
    case '23503':  // foreign_key_violation
      throw Object.assign(new Error('Referenced record does not exist'), { statusCode: 400 });
      
    case '23502':  // not_null_violation
      throw Object.assign(new Error(`${err.column} is required`), { statusCode: 422 });
      
    case '22P02':  // invalid_text_representation (e.g., invalid integer)
      throw Object.assign(new Error('Invalid data format'), { statusCode: 400 });
      
    case 'ECONNREFUSED':
      throw Object.assign(new Error('Database connection failed'), { statusCode: 503 });
      
    default:
      console.error('Unhandled database error:', err);
      throw Object.assign(new Error('Database error'), { statusCode: 500 });
  }
};

// Wrap db functions to use error handler
const createUser = async (data) => {
  try {
    return await _createUser(data);
  } catch (err) {
    handleDbError(err);
  }
};

Library Comparison Table

Library	Type	Raw SQL	Schema	Type Safety	Bundle Size	Best For
pg	Driver	Full	No	Manual	Small	Raw queries, performance
pg-promise	Driver wrapper	Full	No	Manual	Small	Raw SQL + helpers
Prisma	ORM	Escape hatch	SDL	Excellent (TS)	Large	Type-safe CRUD
Sequelize	ORM	Escape hatch	JS classes	Moderate	Medium	Standard CRUD
Knex.js	Query builder	Yes	Migrations	None	Medium	Dynamic queries
Drizzle	ORM	Yes	TypeScript	Excellent (TS)	Small	Modern TS projects

The Prisma ORM PostgreSQL tutorial shows the other end of the spectrum — where you trade SQL control for developer ergonomics and type safety. For most production Node.js apps, a mix makes sense: Prisma for standard CRUD, raw pg for complex analytics queries.

You can reference PostgreSQL's official documentation for the full SQL reference, and node-postgres documentation for the full pg API.

Conclusion

The pg library keeps you close to the database, which means you write better SQL and you understand what's actually happening with your queries. Connection pooling handles the infrastructure concern, parameterized queries handle security, and transactions handle consistency.

For simple apps with standard CRUD, an ORM will serve you faster. But when your queries get complex — window functions, CTEs, custom aggregates, bulk operations — pg gives you the control you need without fighting the abstraction layer.

If you're comparing the overall Node.js API architecture, the Node.js Express MongoDB REST API tutorial shows the same patterns with a document database. For adding authentication to your PostgreSQL-backed API, the API authentication methods comparison covers JWT and session-based approaches.

FAQ

Why use pg directly instead of an ORM like Prisma or Sequelize?

ORMs add abstraction that's great for CRUD-heavy apps, but they generate SQL you don't fully control. Raw pg gives you exact queries, which matters for performance-critical paths, complex joins, and operations that ORMs handle awkwardly. Many teams use both: pg for complex queries where they need control, and an ORM for standard CRUD operations. Starting with pg also teaches you SQL properly.

How many connections should I put in my connection pool?

The common rule is (2 × CPU cores) + 1 for the server. For a typical 4-core server, that's about 9. But it depends heavily on your query duration — long-running queries tie up connections longer. Start with 10, monitor idle and waiting connections in production, and adjust. PostgreSQL itself can handle around 100 connections by default before you need PgBouncer.

How do I prevent SQL injection when using the pg library?

Always use parameterized queries — pass your values as the second argument to client.query(). Never concatenate user input into SQL strings. The pg library handles escaping and type casting automatically for parameterized queries. If you need dynamic column names or table names (which you rarely should), use a whitelist approach — check the value against an allowed list before interpolating it.

Setup and Installation

npm install pg
npm install --save-dev @types/pg  # if using TypeScript

You'll need a PostgreSQL instance. If you're on macOS:

brew install postgresql@16
brew services start postgresql@16
createdb myapp_dev

Or spin up a Docker container:

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_DB=myapp_dev \
  -p 5432:5432 \
  postgres:16

For cloud-based PostgreSQL with a free tier, the MongoDB Atlas equivalent for Postgres is Supabase — the article covers free cloud database options including PostgreSQL-compatible services.

Connection Pool Setup

For applications, you almost always want a connection pool rather than individual connections. Pools reuse existing connections instead of opening and closing one per query — which is expensive:

// src/db/pool.js
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.PGHOST || 'localhost',
  port: parseInt(process.env.PGPORT) || 5432,
  database: process.env.PGDATABASE || 'myapp_dev',
  user: process.env.PGUSER || 'postgres',
  password: process.env.PGPASSWORD,
  max: 20,                    // Maximum pool size
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Fail if can't connect within 2s
  maxUses: 7500,              // Recycle connections after 7500 queries
});

// Test connection on startup
pool.on('connect', (client) => {
  console.log('New database connection established');
});

pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

// Graceful shutdown
process.on('SIGINT', async () => {
  await pool.end();
  console.log('Pool has ended');
  process.exit(0);
});

module.exports = pool;

Alternatively, use a connection string (more common with cloud providers):

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.NODE_ENV === 'production' 
    ? { rejectUnauthorized: false }  // For cloud providers like Heroku
    : false
});

Creating the Table Schema

-- migrations/001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
  id          SERIAL PRIMARY KEY,
  email       VARCHAR(255) UNIQUE NOT NULL,
  name        VARCHAR(255) NOT NULL,
  role        VARCHAR(50) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Auto-update updated_at on row changes
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Index for common queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);

Run it with:

psql -d myapp_dev -f migrations/001_create_users.sql

For more on SQL performance, the SQL query optimization guide covers indexes and EXPLAIN output in depth.

CRUD Queries with Parameterized Inputs

Parameterized queries are non-negotiable. They prevent SQL injection and allow PostgreSQL to cache query plans:

// src/db/users.js
const pool = require('./pool');

// CREATE
const createUser = async ({ email, name, role = 'user' }) => {
  const query = `
    INSERT INTO users (email, name, role)
    VALUES ($1, $2, $3)
    RETURNING *
  `;
  
  const { rows } = await pool.query(query, [email, name, role]);
  return rows[0];
};

// READ — get by ID
const getUserById = async (id) => {
  const { rows } = await pool.query(
    'SELECT * FROM users WHERE id = $1',
    [id]
  );
  return rows[0] || null;
};

// READ — list with pagination and filtering
const getUsers = async ({ role, limit = 20, offset = 0 } = {}) => {
  const conditions = [];
  const params = [];
  
  if (role) {
    params.push(role);
    conditions.push(`role = $${params.length}`);
  }
  
  const whereClause = conditions.length > 0 
    ? `WHERE ${conditions.join(' AND ')}` 
    : '';
  
  // Total count (separate query for accuracy with filters)
  const countQuery = `SELECT COUNT(*) FROM users ${whereClause}`;
  const dataQuery = `
    SELECT id, email, name, role, created_at
    FROM users
    ${whereClause}
    ORDER BY created_at DESC
    LIMIT $${params.length + 1} OFFSET $${params.length + 2}
  `;
  
  const [countResult, dataResult] = await Promise.all([
    pool.query(countQuery, params),
    pool.query(dataQuery, [...params, limit, offset])
  ]);
  
  return {
    users: dataResult.rows,
    total: parseInt(countResult.rows[0].count)
  };
};

// UPDATE
const updateUser = async (id, updates) => {
  const allowed = ['name', 'email', 'role'];
  const fields = Object.keys(updates).filter(k => allowed.includes(k));
  
  if (fields.length === 0) return null;
  
  const setClauses = fields.map((field, i) => `${field} = $${i + 2}`);
  const values = fields.map(f => updates[f]);
  
  const query = `
    UPDATE users
    SET ${setClauses.join(', ')}
    WHERE id = $1
    RETURNING *
  `;
  
  const { rows } = await pool.query(query, [id, ...values]);
  return rows[0] || null;
};

// DELETE
const deleteUser = async (id) => {
  const { rowCount } = await pool.query(
    'DELETE FROM users WHERE id = $1',
    [id]
  );
  return rowCount > 0;
};

// EXISTS check
const emailExists = async (email) => {
  const { rows } = await pool.query(
    'SELECT 1 FROM users WHERE email = $1 LIMIT 1',
    [email]
  );
  return rows.length > 0;
};

module.exports = { createUser, getUserById, getUsers, updateUser, deleteUser, emailExists };

The RETURNING * clause is PostgreSQL-specific and extremely useful — you get the full inserted/updated row back without needing a second SELECT query.

Transactions

// src/db/transactions.js
const pool = require('./pool');

// Helper for running a transaction
const withTransaction = async (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();  // Always return the client to the pool
  }
};

// Example: Create user + profile atomically
const createUserWithProfile = async ({ email, name, bio, avatarUrl }) => {
  return withTransaction(async (client) => {
    // Step 1: Create user
    const { rows: [user] } = await client.query(
      'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
      [email, name]
    );
    
    // Step 2: Create profile (linked to user)
    const { rows: [profile] } = await client.query(
      'INSERT INTO profiles (user_id, bio, avatar_url) VALUES ($1, $2, $3) RETURNING *',
      [user.id, bio, avatarUrl]
    );
    
    return { user, profile };
  });
};

// Example: Transfer points between users
const transferPoints = async (fromUserId, toUserId, amount) => {
  return withTransaction(async (client) => {
    // Lock rows to prevent concurrent modifications (SELECT FOR UPDATE)
    const { rows: [sender] } = await client.query(
      'SELECT points FROM users WHERE id = $1 FOR UPDATE',
      [fromUserId]
    );
    
    if (!sender || sender.points < amount) {
      throw new Error('Insufficient points');
    }
    
    await client.query(
      'UPDATE users SET points = points - $2 WHERE id = $1',
      [fromUserId, amount]
    );
    
    await client.query(
      'UPDATE users SET points = points + $2 WHERE id = $1',
      [toUserId, amount]
    );
    
    return { success: true, transferred: amount };
  });
};

module.exports = { withTransaction, createUserWithProfile, transferPoints };

The finally block with client.release() is critical — without it, you'll exhaust your connection pool if a transaction fails.

Advanced Queries

// Full-text search (PostgreSQL built-in)
const searchUsers = async (searchTerm) => {
  const { rows } = await pool.query(`
    SELECT id, email, name,
           ts_rank(to_tsvector('english', name || ' ' || email), plainto_tsquery($1)) AS rank
    FROM users
    WHERE to_tsvector('english', name || ' ' || email) @@ plainto_tsquery($1)
    ORDER BY rank DESC
    LIMIT 20
  `, [searchTerm]);
  
  return rows;
};

// Bulk insert with unnest (much faster than looping)
const bulkCreateUsers = async (users) => {
  const emails = users.map(u => u.email);
  const names = users.map(u => u.name);
  
  const { rows } = await pool.query(`
    INSERT INTO users (email, name)
    SELECT * FROM unnest($1::text[], $2::text[])
    ON CONFLICT (email) DO NOTHING
    RETURNING *
  `, [emails, names]);
  
  return rows;
};

// Upsert pattern
const upsertUser = async ({ email, name }) => {
  const { rows } = await pool.query(`
    INSERT INTO users (email, name)
    VALUES ($1, $2)
    ON CONFLICT (email)
    DO UPDATE SET name = EXCLUDED.name, updated_at = NOW()
    RETURNING *
  `, [email, name]);
  
  return rows[0];
};

// Window functions (ranking, running totals)
const getUsersWithRank = async () => {
  const { rows } = await pool.query(`
    SELECT 
      id,
      name,
      points,
      RANK() OVER (ORDER BY points DESC) AS rank,
      SUM(points) OVER () AS total_points
    FROM users
    ORDER BY points DESC
    LIMIT 10
  `);
  
  return rows;
};

For comparing PostgreSQL against other databases, the PostgreSQL vs MySQL vs SQLite comparison covers the architectural differences.

Error Handling

The pg library throws errors with standard PostgreSQL error codes:

// src/db/errorHandler.js
const handleDbError = (err) => {
  switch (err.code) {
    case '23505':  // unique_violation
      const column = err.constraint?.replace(/_key$/, '') || 'field';
      throw Object.assign(new Error(`${column} already exists`), { statusCode: 409 });
      
    case '23503':  // foreign_key_violation
      throw Object.assign(new Error('Referenced record does not exist'), { statusCode: 400 });
      
    case '23502':  // not_null_violation
      throw Object.assign(new Error(`${err.column} is required`), { statusCode: 422 });
      
    case '22P02':  // invalid_text_representation (e.g., invalid integer)
      throw Object.assign(new Error('Invalid data format'), { statusCode: 400 });
      
    case 'ECONNREFUSED':
      throw Object.assign(new Error('Database connection failed'), { statusCode: 503 });
      
    default:
      console.error('Unhandled database error:', err);
      throw Object.assign(new Error('Database error'), { statusCode: 500 });
  }
};

// Wrap db functions to use error handler
const createUser = async (data) => {
  try {
    return await _createUser(data);
  } catch (err) {
    handleDbError(err);
  }
};

Library Comparison Table

Library	Type	Raw SQL	Schema	Type Safety	Bundle Size	Best For
pg	Driver	Full	No	Manual	Small	Raw queries, performance
pg-promise	Driver wrapper	Full	No	Manual	Small	Raw SQL + helpers
Prisma	ORM	Escape hatch	SDL	Excellent (TS)	Large	Type-safe CRUD
Sequelize	ORM	Escape hatch	JS classes	Moderate	Medium	Standard CRUD
Knex.js	Query builder	Yes	Migrations	None	Medium	Dynamic queries
Drizzle	ORM	Yes	TypeScript	Excellent (TS)	Small	Modern TS projects

You can reference PostgreSQL's official documentation for the full SQL reference, and node-postgres documentation for the full pg API.

Conclusion

FAQ

Why use pg directly instead of an ORM like Prisma or Sequelize?

How many connections should I put in my connection pool?

How do I prevent SQL injection when using the pg library?

How to Use PostgreSQL With Node.js (pg Library Tutorial 2026)

Setup and Installation

Connection Pool Setup

Creating the Table Schema

CRUD Queries with Parameterized Inputs

Transactions

Advanced Queries

Error Handling

Library Comparison Table

Conclusion

FAQ

Frequently Asked Questions

AiTechWorlds Team

Related Articles

How to Use Docker Compose for Local Dev (Node.js + PostgreSQL)

5 GraphQL Resolver Best Practices (DataLoader, Error Handling)

10 SQL Query Optimization Techniques (Indexes, EXPLAIN, Joins)

7 Common API Security Vulnerabilities (and How to Fix Them)

Get Free AI Notes Daily

How to Use PostgreSQL With Node.js (pg Library Tutorial 2026)

Setup and Installation

Connection Pool Setup

Creating the Table Schema

CRUD Queries with Parameterized Inputs

Transactions

Advanced Queries

Error Handling

Library Comparison Table

Conclusion

FAQ

Frequently Asked Questions

AiTechWorlds Team

Related Articles

How to Use Docker Compose for Local Dev (Node.js + PostgreSQL)

5 GraphQL Resolver Best Practices (DataLoader, Error Handling)

10 SQL Query Optimization Techniques (Indexes, EXPLAIN, Joins)

7 Common API Security Vulnerabilities (and How to Fix Them)

Get Free AI Notes Daily