How to Use PostgreSQL with Node.js

PostgreSQL and Node.js are a popular combination for building fast, scalable, and data-driven applications. Node.js is widely used for backend services, APIs, and real-time applications, while PostgreSQL provides a reliable and feature-rich relational database.

In this guide, you’ll learn how to use PostgreSQL with Node.js, starting from installation and connection setup to performing CRUD operations, handling transactions, and applying best practices for production environments.

Why Use PostgreSQL with Node.js?

Node.js is well-known for its non-blocking, event-driven architecture. When paired with PostgreSQL, it offers:

• High performance for concurrent applications
• Strong data consistency with ACID transactions
• Advanced PostgreSQL features such as JSONB and indexing
• Excellent support for REST APIs and microservices

This combination is commonly used in modern web development stacks.

Choosing a PostgreSQL Client for Node.js

The most popular PostgreSQL client for Node.js is node-postgres, also known as pg.

Why pg?

• Actively maintained and widely adopted
• Supports connection pooling
• Works with promises and async/await
• Fully compatible with PostgreSQL features

Prerequisites

Before starting, ensure you have:

• Node.js 18 or newer
• PostgreSQL installed and running
• Basic JavaScript and SQL knowledge
• npm or yarn package manager

Installing the pg Package

Create a new Node.js project and install the PostgreSQL client:

npm init -y
npm install pg

Connecting to PostgreSQL from Node.js

Below is a basic example of connecting to a PostgreSQL database using pg:

const { Client } = require('pg');

const client = new Client({
  host: 'localhost',
  user: 'postgres',
  password: 'yourpassword',
  database: 'tutorialdb',
  port: 5432,
});

async function connectDB() {
  await client.connect();
  console.log('Connected to PostgreSQL');
}

connectDB();

For better performance, production applications should use connection pooling.

Using Connection Pooling

Connection pooling allows multiple requests to share database connections efficiently.

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  user: 'postgres',
  password: 'yourpassword',
  database: 'tutorialdb',
  port: 5432,
  max: 10,
});

module.exports = pool;

Creating a Table Example

const createTable = `
CREATE TABLE IF NOT EXISTS customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(150),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
`;

pool.query(createTable)
  .then(() => console.log('Table created'))
  .catch(err => console.error(err));

Inserting Data into PostgreSQL

const insertQuery = `
INSERT INTO customers (name, email)
VALUES ($1, $2)
`;

await pool.query(insertQuery, ['John Doe', 'john@example.com']);

Why Use Parameterized Queries?

• Prevents SQL injection
• Improves security
• Automatically handles data formatting

Selecting Data from PostgreSQL

const result = await pool.query(
  'SELECT id, name, email FROM customers'
);

console.log(result.rows);

Each row is returned as a JavaScript object, making it easy to work with the data.

Updating Data

const updateQuery = `
UPDATE customers
SET email = $1
WHERE id = $2
`;

await pool.query(updateQuery, ['newemail@example.com', 1]);

Always validate input data before performing updates.

Deleting Data

await pool.query(
  'DELETE FROM customers WHERE id = $1',
  [1]
);

In production systems, consider using soft deletes instead of permanent deletion.

Handling Transactions

PostgreSQL supports full transaction control in Node.js:

try {
  await pool.query('BEGIN');
  await pool.query(
    'INSERT INTO customers (name, email) VALUES ($1, $2)',
    ['Alice', 'alice@example.com']
  );
  await pool.query('COMMIT');
} catch (err) {
  await pool.query('ROLLBACK');
  console.error('Transaction failed', err);
}

Transactions ensure data consistency across multiple operations.

Error Handling Best Practices

Always handle database errors properly:

try {
  const result = await pool.query('SELECT * FROM invalid_table');
} catch (error) {
  console.error('Database error:', error.message);
}

Use structured logging to help debug production issues.

Performance Optimization Tips

• Use connection pooling
• Avoid SELECT * in large tables
• Add indexes for frequently queried columns
• Use pagination for large result sets
• Monitor slow queries using EXPLAIN ANALYZE

PostgreSQL with Node.js Frameworks

PostgreSQL works seamlessly with popular Node.js frameworks:

• Express.js for REST APIs
• NestJS for scalable enterprise apps
• Fastify for high-performance servers

ORMs such as Sequelize, Prisma, or TypeORM can also be used, but raw SQL with pg often provides better performance and control.

Common Use Cases

• RESTful APIs and backend services
• Real-time applications
• E-commerce platforms
• SaaS products
• Microservices architecture

Security Best Practices

• Never hardcode database credentials
• Use environment variables
• Enable SSL for production connections
• Apply least-privilege access for database users
• Validate and sanitize all inputs

Conclusion

Using PostgreSQL with Node.js provides a powerful foundation for modern backend applications. With the pg library, you can efficiently manage connections, execute secure queries, and leverage PostgreSQL’s advanced features.

By following best practices in connection pooling, transaction management, and security, your Node.js applications can scale reliably in production environments.