Connecting to a PostgreSQL database from Node.js using the pg module

The following example shows how you can connect to a PostgreSQL database from Node.js using the pg module.

First, create a new JavaScript file, app.js, and add the following code:

#!/usr/bin/env node

var pg = require("pg");

var conString = "pg://admin:guest@localhost:5432/Employees";

var client = new pg.Client(conString);
client.connect();

// client.query("CREATE TABLE IF NOT EXISTS emps(firstname varchar(64), lastname varchar(64))");
// client.query("INSERT INTO emps(firstname, lastname) values($1, $2)", ['Ronald', 'McDonald']);
// client.query("INSERT INTO emps(firstname, lastname) values($1, $2)", ['Mayor', 'McCheese']);

var query = client.query("SELECT firstname, lastname FROM emps ORDER BY lastname, firstname");
query.on("row", function (row, result) {
    result.addRow(row);
});
query.on("end", function (result) {
    console.log(JSON.stringify(result.rows, null, "    "));
    client.end();
});

The previous code tries to connect to a PostgreSQL server using the following information (from the supplied connection string above):

Config Value
username: 'admin'
password: 'guest'
server: 'localhost'
port: 5432
database: 'Employees'

In the previous example we create a new Query object (using client.query()) and attaches two event listeners: row, and end.

The row event is dispatched whenever a row is received from PostgreSQL and we use it to append all the individual rows in to an array of rows which are passed to a rows property in the end event’s result object. If we did not call the result.addRow() method, the rows array would be empty in the end event.

The end event is dispatched when all rows have been returned by the query or when an error has been encountered (which would cause the error event to be dispatched). In this example we’re dumping all the rows from PostgreSQL to the console and then closing the client’s connection using the end() method on the Client object.

Next, using the Terminal, change to the same directory as the app.js file created in the previous step and install the pg module using the following command:

npm install pg

Finally, run the Node.js script using the following command: node app.js and you should see the following information displayed to the console (assuming you have an emps table with some sample data — see the commented out lines in the JavaScript code above for creating a table and adding some records using the client.query() method):

[
    {
        "firstname": "Mayor",
        "lastname": "McCheese"
    },
    {
        "firstname": "Ronald",
        "lastname": "McDonald"
    }
]

Leave a Reply

Your email address will not be published.