PostgreSQL Database, Node.js and Express: A Comprehensive Guide

Introduction

Node.js is well suited for fast, scalable network applications thanks to its event-driven, non-blocking I/O model. Pairing it with a JavaScript runtime makes Node.js approachable for front end web developers. The Express framework simplifies Node.js server development with routing, middleware and request handling.

PostgreSQL Database is an enterprise-ready, open source relational database with an emphasis on extensibility and standards compliance. It offers features like reliable transactions, complex querying and indexing, and schema management.

Connecting these technologies provides a compelling web stack. PostgreSQL’s reliability provides safe data storage for Express and Node.js to create efficient APIs and handle user interactions. By following SQL best practices and utilizing PostgreSQL client drivers, developers can build robust web apps with Node and PostgreSQL.

Setting Up a Node Project

The first step is initializing a new Node project and installing Express with npm. This outlines the basic folder structure and imports Express:

$ npm init
$ npm install express

A simple index.js file can be created that configures Express:

const express = require('express')
const app = express()
 
app.get('/', (req, res) => {
  res.send('Hello World!') 
})
 
app.listen(3000)

This will run a basic Express server on port 3000. The next step is adding PostgreSQL support.

Installing Node PostgreSQL Drivers

Node needs a PostgreSQL driver library to connect to the database. The most popular choice is pg, which supports all PostgreSQL features and has broad compatibility with Node versions.

$ npm install pg

pg provides a Client constructor that creates a connection pool to the PostgreSQL server. Queries can then be executed against the client instance.

Connecting to a PostgreSQL Database

Connections require a PostgreSQL connection string containing the database credentials:

const { Client } = require('pg')

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

This connects to a local PostgreSQL server with the specified user, password and database name on the standard port 5432.

The client must then be initialized:

client.connect(err => {
  if (err) {
    console.error('connection error', err.stack)
  } else {
    console.log('connected')
  }
})

The callback will handle any connection errors before proceeding. With the client connected, queries can be executed.

Querying a PostgreSQL Database in Node

pg provides helpful methods for sending SQL queries to the PostgreSQL server.

The simplest is client.query() which takes the SQL query string:

client.query('SELECT $1::text as message', ['Hello world!'], (err, res) => {
  if (err) {
    console.error(err)
  } else {
    console.log(res.rows[0].message) // Hello world!
  }
})

The second parameter allows passing arguments safely to avoid SQL injection. The callback receives the error and result for processing.

For more complex queries, prepared statements can be used:

const text = 'SELECT $1::text as message'
const values = ['Hello world!']
 
client.query(text, values, (err, res) => {
  if (err) {
    console.error(err) 
  } else {
    console.log(res.rows[0].message) // Hello World!
  }
})

This approach prevents SQL injection and improves performance for reused queries.

Use async/await for cleaner async query handling:

async function getMessage() {
  const text = 'SELECT $1::text as message'
  const values = ['Hello world!']
  
  try {
    const res = await client.query(text, values) 
    return res.rows[0].message
  } catch (err) {
    console.error(err)
  }
}
 
getMessage().then(message => {
  console.log(message) // Hello world!
})

These examples demonstrate the fundamental approaches for executing queries from a Node application using the PostgreSQL client.

Connecting Express to PostgreSQL

Putting this together with Express, the PostgreSQL client can be initialized on app start and passed to routes that need it:

const express = require('express')
const { Client } = require('pg')
 
const app = express()

// PostgreSQL client
const client = new Client(/* connection config */) 
client.connect()

app.get('/', async (req, res) => {
  // Use client from outer scope
  const result = await client.query('SELECT $1::text', ['Hello world!'])  
  res.send(result.rows[0]) 
})

app.listen(3000)

For a more robust solution, the client can be wrapped in a module and exported:

// Postgres client module

const { Client } = require('pg')

const client = new Client({
  // connection config 
})

client.connect()

module.exports = client

Then imported where needed:

// Express server

const express = require('express')
const client = require('./postgres') 

app.get('/', async (req, res) => {
  const result = await client.query('SELECT $1::text', ['Hello world!'])
  res.send(result.rows[0])
})

This keeps the Postgres initialization centralized and connection pool reused across the app.

Connection Pooling

For efficiency, applications should use connection pooling rather than creating a new client for every query.

The pg module natively handles connection pooling. By default, it creates a pool of 10 connections that are reused.

One client can then safely handle all database access:

// Postgres client with pooling

const { Client } = require('pg')

const client = new Client({
  // config
})

// Pool is created on connect
client.connect() 

module.exports = client

The size of the pool can be customized by passing pool options:

client.connect({
  connectionString: 'postgres://user:password@host/database',
  ssl: {
    rejectUnauthorized: false
  },
  pool: {
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000, 
  },
})

This configures a pool of up to 20 connections, with 30 second idle timeout, and 2 second connection timeout.

Tuning these values allows optimizing performance for the specific database workload.

Handling PostgreSQL Errors

It’s important to properly handle errors from PostgreSQL. They provide valuable information for diagnosing issues.

The pg client passes errors to callbacks:

client.query('INVALID', (err, res) => {
  if (err) {
    console.log(err.code) // error code
    console.log(err.message) // error text
  }  
})

or can be caught on async/await:

try {
  await client.query('INVALID')
} catch (err) {
  console.log(err.code)
  console.log(err.message) 
}

Error codes like 42703 indicate Postgres error types. The text describes the issue such as syntax errors.

Make sure to log errors appropriately in production apps for debugging. Re-throwing or returning errors is useful for consistent error handling.

Connecting from a Node.js web application to PostgreSQL combines the strengths of these technologies into a robust platform. Following best practices around pooling, error handling, and queries sets your project up for success.

Conclusion

PostgreSQL’s feature set complements Node’s simple and fast web development model. With proper configuration, developers can build Express web services that efficiently interact with a PostgreSQL datastore.

Following SQL best practices maximizes PostgreSQL’s strengths for managing structured data at scale. The pg driver enables querying PostgreSQL directly from Node with protection against injections.

Connection pooling, error handling, and extracting the client into a reusable module improve application structure. Database code can be separated from the web layers, easing development and testing.

For rapidly developing web applications that require relational data storage, PostgreSQL and Node provide a compelling stack. The active communities and support for open standards make them a natural pairing for building cloud-ready apps.

Frequently Asked Questions

Here are some additional FAQs covering using PostgreSQL with Node.js:

How do I connect my Node app to PostgreSQL?

The pg module provides a PostgreSQL client for Node. Create a new client instance passing the connection credentials. Then call client.connect() to initialize the connection pool before executing queries.

How should I structure my Node app to use PostgreSQL?

Best practice is to abstract the PostgreSQL client into a separate module exported for use in routes and controllers. This centralizes the connection handling and pooling. Queries can then be made from handler functions.

Should I use a query builder or raw SQL in Node?

Raw SQL is generally recommended for queries in Node to maximize PostgreSQL’s performance and take advantage of its features. Query builders add overhead and may not support advanced query syntax.

Is PostgreSQL or MySQL better for Node.js?

PostgreSQL generally fits better with Node.js due to its JavaScript like JSON support, standard SQL handling, and robust data integrity. MySQL lacks some advanced features useful for Node apps at scale. PostgreSQL also outperforms MySQL for more complex queries.

How do I handle errors from PostgreSQL in Node?

Robust error handling is important. Log errors from failed queries for debugging purposes. Use the error codes and messages to provide user feedback and retry logic if needed. Wrap queries in try/catch blocks and handle errors appropriately.

Is pg the best PostgreSQL driver for Node.js?

The pg module is considered the most full-featured and pure JavaScript Postgres driver for Node. It supports the complete PostgreSQL API and has broad compatibility. Alternatives like node-postgres are also good but pg tends to be most popular.

Should I use pools in PostgreSQL?

Yes, connection pooling is vital for performance in PostgreSQL. The pg driver handles pooling automatically. Tune the min/max connections and idle timeout to optimize for your workload. Never create new clients for every query.