Bootcamp
Search…
3.4.4: PostgreSQL Express App

Introduction

For Express we'll use a pg class other than Client. Unlike Node CLI programs that run and exit immediately, Express apps run and listen for requests until stopped. We need a way to manage the database connections that Express makes and end those connections appropriately. pg's Pool class does this automatically, and enables us to accept multiple requests simultaneously. The word pool refers to an available "pool" of connections. We can think of it as a managed array of connection metadata.

Setup

Remember to install pg.
npm install pg

Sample Code

index.js
import express from 'express';
import pg from 'pg';
​
// Initialise DB connection
const { Pool } = pg;
const pgConnectionConfigs = {
user: '<MY_UNIX_USERNAME>',
host: 'localhost',
database: '<MY_UNIX_USERNAME>',
port: 5432, // Postgres server always runs on this port by default
};
const pool = new Pool(pgConnectionConfigs);
​
const app = express();
​
app.get('/', (request, response) => {
console.log('request came in');
​
const whenDoneWithQuery = (error, result) => {
if (error) {
console.log('Error executing query', error.stack);
response.status(503).send(result.rows);
return;
}
console.log(result.rows[0].name);
response.send(result.rows);
};
​
// Query using pg.Pool instead of pg.Client
pool.query('SELECT * from cats', whenDoneWithQuery);
});
​
app.listen(3004);
Note: There is no need to run pool.end like how we ran client.end with the pg Client class in our Node CLI apps. This is because in server applications we expect the DB connection to persist beyond individual requests.