How to Connect Express to PostgreSQL: Simple Guide

To connect Express to PostgreSQL, use the pg library to create a client or pool with your database credentials, then query the database inside your Express routes. Initialize the connection once and reuse it for efficient database operations.

Syntax

Use the pg library to connect Express to PostgreSQL. First, import Pool from pg. Then create a new Pool instance with your database connection details like user, host, database, password, and port. Use pool.query() to run SQL commands inside your Express route handlers.

javascript

import express from 'express';
import pkg from 'pg';
const { Pool } = pkg;

const pool = new Pool({
  user: 'your_user',
  host: 'localhost',
  database: 'your_db',
  password: 'your_password',
  port: 5432
});

const app = express();

app.get('/data', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM your_table');
    res.json(result.rows);
  } catch (err) {
    res.status(500).send('Database error');
  }
});

app.listen(3000);

Example

This example shows a complete Express server connecting to PostgreSQL using pg. It queries all rows from a table and sends them as JSON in the response.

javascript

import express from 'express';
import pkg from 'pg';
const { Pool } = pkg;

const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'testdb',
  password: 'mypassword',
  port: 5432
});

const app = express();

app.get('/users', async (req, res) => {
  try {
    const result = await pool.query('SELECT id, name FROM users');
    res.json(result.rows);
  } catch (error) {
    console.error(error);
    res.status(500).send('Error fetching users');
  }
});

app.listen(3000, () => {
  console.log('Server running on http://localhost:3000');
});

Output

Server running on http://localhost:3000

// When visiting http://localhost:3000/users
// Response example:
// [
//   {"id":1,"name":"Alice"},
//   {"id":2,"name":"Bob"}
// ]

Common Pitfalls

• Not installing the pg package before use (npm install pg).
• Forgetting to use async/await or proper promise handling with pool.query().
• Hardcoding credentials in code instead of using environment variables.
• Not handling errors inside async route handlers, causing server crashes.
• Creating a new database connection on every request instead of reusing a Pool.

javascript

/* Wrong: Creating new client on every request */
app.get('/data', (req, res) => {
  const { Client } = require('pg');
  const client = new Client({ /* config */ });
  client.connect();
  client.query('SELECT * FROM table', (err, result) => {
    client.end();
    if (err) return res.status(500).send('Error');
    res.json(result.rows);
  });
});

/* Right: Use a single Pool instance reused across requests */
const pool = new Pool({ /* config */ });
app.get('/data', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM table');
    res.json(result.rows);
  } catch {
    res.status(500).send('Error');
  }
});

Quick Reference

Steps to connect Express to PostgreSQL:

• Install pg with npm install pg.
• Import and create a Pool with your DB credentials.
• Use pool.query() inside async Express routes.
• Handle errors with try/catch.
• Reuse the Pool instance for all queries.

Key Takeaways

Use the 'pg' library's Pool to connect Express to PostgreSQL efficiently.

Always reuse a single Pool instance instead of creating new connections per request.

Handle database query errors inside async route handlers to avoid crashes.

Store database credentials securely, preferably in environment variables.

Use async/await syntax for clean and readable database queries.