Node Postgres tutorial
last modified July 7, 2020
The Node Postgres tutorial shows how to work with PostgreSQL database in JavaScript with node-postgres.
The node-postgres
The node-postgres is a collection of Node.js modules for interfacing with the PostgreSQL database. It has support for callbacks, promises, async/await, connection pooling, prepared statements, cursors, and streaming results.
In our examples we also use the Ramda library. See Ramda tutorial for more information.
Setting up node-postgres
First, we install node-postgres.
$ node -v v11.5.0
We use Node version 11.5.0.
$ npm init -y
We initiate a new Node application.
$ npm i pg
We install node-postgres with nmp i pg
.
$ npm i ramda
In addition, we install Ramda for beautiful work with data.
DROP TABLE IF EXISTS cars; CREATE TABLE cars(id SERIAL PRIMARY KEY, name VARCHAR(255), price INT); INSERT INTO cars(name, price) VALUES('Audi', 52642); INSERT INTO cars(name, price) VALUES('Mercedes', 57127); INSERT INTO cars(name, price) VALUES('Skoda', 9000); INSERT INTO cars(name, price) VALUES('Volvo', 29000); INSERT INTO cars(name, price) VALUES('Bentley', 350000); INSERT INTO cars(name, price) VALUES('Citroen', 21000); INSERT INTO cars(name, price) VALUES('Hummer', 41400); INSERT INTO cars(name, price) VALUES('Volkswagen', 21600);
In some of the examples, we use this cars
table.
The node-postgres first example
In the first example, we connect to the PostgreSQL database and return a simple SELECT query result.
const pg = require('pg'); const R = require('ramda'); const cs = 'postgres://postgres:s$cret@localhost:5432/ydb'; const client = new pg.Client(cs); client.connect(); client.query('SELECT 1 + 4').then(res => { const result = R.head(R.values(R.head(res.rows))); console.log(result); }).finally(() => client.end());
The example connects to the database and issues a SELECT statement.
const pg = require('pg'); const R = require('ramda');
We include the pg
and ramda
modules.
const cs = 'postgres://postgres:s$cret@localhost:5432/ydb';
This is the PostgreSQL connection string. It is used to build a connection to the database.
const client = new pg.Client(cs); client.connect();
A client is created. We connect to the database with connect()
.
client.query('SELECT 1 + 4').then(res => { const result = R.head(R.values(R.head(res.rows))); console.log(result); }).finally(() => client.end());
We issue a simple SELECT query. We get the result and output it to the console.
The res.rows
is an array of objects; we use Ramda to get the
returned scalar value. In the end, we close the connection with end()
.
$ node first.js 5
This is the output.
The node-postgres column names
In the following example, we get the columns names of a database.
const pg = require('pg'); const cs = 'postgres://postgres:s$cret@localhost:5432/ydb'; const client = new pg.Client(cs); client.connect(); client.query('SELECT * FROM cars').then(res => { const fields = res.fields.map(field => field.name); console.log(fields); }).catch(err => { console.log(err.stack); }).finally(() => { client.end() });
The column names are retrieved with res.fields
attribute.
We also use the catch
clause to output potential errors.
$ node column_names.js [ 'id', 'name', 'price' ]
The output shows three column names of the cars
table.
Selecting all rows
In the next example, we select all rows from the database table.
const pg = require('pg'); const R = require('ramda'); const cs = 'postgres://postgres:s$cret@localhost:5432/ydb'; const client = new pg.Client(cs); client.connect(); client.query('SELECT * FROM cars').then(res => { const data = res.rows; console.log('all data'); data.forEach(row => { console.log(`Id: ${row.id} Name: ${row.name} Price: ${row.price}`); }) console.log('Sorted prices:'); const prices = R.pluck('price', R.sortBy(R.prop('price'), data)); console.log(prices); }).finally(() => { client.end() });
The example outputs all rows from the cars
table and a sorted
list of car prices.
$ node all_rows.js all data Id: 1 Name: Audi Price: 52642 Id: 2 Name: Mercedes Price: 57127 Id: 3 Name: Skoda Price: 9000 Id: 4 Name: Volvo Price: 29000 Id: 5 Name: Bentley Price: 350000 Id: 6 Name: Citroen Price: 21000 Id: 7 Name: Hummer Price: 41400 Id: 8 Name: Volkswagen Price: 21600 Sorted prices: [ 9000, 21000, 21600, 29000, 41400, 52642, 57127, 350000 ]
This is the output.
The node-postgres parameterized query
Parameterized queries use placeholders instead of directly writing the values into the statements. Parameterized queries increase security and performance.
const pg = require('pg'); const cs = 'postgres://postgres:s$cret@localhost:5432/ydb'; const client = new pg.Client(cs); client.connect(); const sql = 'SELECT * FROM cars WHERE price > $1'; const values = [50000]; client.query(sql, values).then(res => { const data = res.rows; data.forEach(row => console.log(row)); }).finally(() => { client.end() });
The example uses a parameterized query in a simple SELECT statement.
const sql = 'SELECT * FROM cars WHERE price > $1';
This is the SELECT query. The $1
is a placeholder which is
later replaced with a value in a secure way.
const values = [50000];
These are the values to be inserted into the parameterized query.
client.query(sql, values).then(res => {
The values are passed to the query()
method as the second parameter.
$ node parameterized.js { id: 1, name: 'Audi', price: 52642 } { id: 2, name: 'Mercedes', price: 57127 } { id: 5, name: 'Bentley', price: 350000 }
This is the output.
The node-postgres with async/await
Node Postgres supports the async/await syntax.
const pg = require('pg'); const R = require('ramda'); const cs = 'postgres://postgres:s$cret@localhost:5432/ydb'; async function fetchNow() { const client = new pg.Client(cs); try { await client.connect(); let result = await client.query('SELECT now()'); return R.prop('now', R.head(result.rows)); } finally { client.end() } } fetchNow().then(now => console.log(now));
The example outputs the result of a SELECT now()
query
with async/await.
$ node async_await.js 2019-02-17T11:53:01.447Z
This is the output.
The node-postgres rowmode
By default, node-postgres returns data as an array of objects. We can tell node-postgres to return the data as an array of arrays.
const pg = require('pg'); const R = require('ramda'); const cs = 'postgres://postgres:s$cret@localhost:5432/ydb'; const client = new pg.Client(cs); client.connect(); const query = { text: 'SELECT * FROM cars', rowMode: 'array' }; client.query(query).then(res => { const data = res.rows; console.log('all data'); data.forEach(row => { console.log(`Id: ${row[0]} Name: ${row[1]} Price: ${row[2]}`); }) console.log('Sorted prices:'); const prices = data.map(x => x[2]); const sorted = R.sort(R.comparator(R.lt), prices); console.log(sorted); }).finally(() => { client.end() });
The example shows all rows from the cars
table. It enables
the array row mode.
const query = { text: 'SELECT * FROM cars', rowMode: 'array' };
We use the configuration object where we set the rowMode
to array
.
console.log('all data'); data.forEach(row => { console.log(`Id: ${row[0]} Name: ${row[1]} Price: ${row[2]}`); })
Now we loop over an array of arrays.
$ node row_mode.js all data Id: 1 Name: Audi Price: 52642 Id: 2 Name: Mercedes Price: 57127 Id: 3 Name: Skoda Price: 9000 Id: 4 Name: Volvo Price: 29000 Id: 5 Name: Bentley Price: 350000 Id: 6 Name: Citroen Price: 21000 Id: 7 Name: Hummer Price: 41400 Id: 8 Name: Volkswagen Price: 21600 Sorted prices: [ 9000, 21000, 21600, 29000, 41400, 52642, 57127, 350000 ]
This is the output.
The node-postgres pooling example
Connection pooling improves performance of a database application. It is especially useful web applications.
const pg = require('pg'); var config = { user: 'postgres', password: 's$cret', database: 'ydb' } const pool = new pg.Pool(config); pool.connect() .then(client => { return client.query('SELECT * FROM cars WHERE id = $1', [1]) .then(res => { client.release(); console.log(res.rows[0]); }) .catch(e => { client.release(); console.log(e.stack); }) }).finally(() => pool.end());
The example shows how to set up an example which uses connection pooling.
When we are done with a query, we call the client.release()
method
to return the connection to the pool.
}).finally(() => pool.end());
The pool.end()
drains the pool of all active clients, disconnect them,
and shut down any internal timers in the pool. This is used in scripts such as this example.
In web applications, we can call it when the web server shuts down or don't call it at all.
In this tutorial, we have used node-postgres
to interact with
PostgreSQL in Node.js.
List all JavaScript tutorials.