Node.js | Using PostgreSQL with Express

Using PostgreSQL from Node.js

Databases are essential in many web applications. Node.js can use databases through additional packages rather than through only the standard library. In this article, PostgreSQL is used because Heroku supports it as a standard database option.

Install PostgreSQL locally first so that you can test the application and use Heroku’s PostgreSQL commands. Add PostgreSQL’s bin directory to the system Path so commands such as psql can be called from the terminal.

C:\Program Files\PostgreSQL\x.x\bin

In the Node.js application directory, install dependencies and add the PostgreSQL package.

$ npm install
$ npm install pg

The pg package provides PostgreSQL access from Node.js.

Preparing Heroku

Log in to Heroku from the application directory and add the PostgreSQL add-on.

$ heroku login
$ heroku addons:add heroku-postgresql:dev

After the add-on is attached, promote it as the application’s default database.

$ heroku pg:promote HEROKU_POSTGRESQL_NAME_URL

Create a table through Heroku’s PostgreSQL console.

$ heroku pg:psql

For this example, create a simple mydata table.

create table mydata (
  id serial primary key,
  name char(50),
  mail char(100),
  memo char(255)
);

Files for Heroku

An Express application deployed to Heroku needs application metadata and a start command. In package.json, include dependencies such as express, pg, and ejs.

{
  "name": "application-name",
  "version": "0.0.1",
  "private": true,
  "scripts": {
    "start": "node app.js"
  },
  "dependencies": {
    "express": "latest",
    "pg": "latest",
    "ejs": "*"
  }
}

Create a Procfile in the application root.

web: node app.js

Routing in app.js

Besides the home page, this example prepares /add for the input form and /create for the POST processing.

var add = require("./routes/add");
var create = require("./routes/create");

app.use("/add", add);
app.use("/create", create);

Displaying Table Records

The home page uses routes/index.js and views/index.ejs. Connect to PostgreSQL with pg.connect, execute select * from mydata, collect rows from the row event, and render the table when the end event fires.

var con = "tcp://user:password@host:port/database";
pg.connect(con, function(err, client) {
  var query = client.query("select * from mydata;");
  var rows = [];
  query.on("row", function(row) {
    rows.push(row);
  });
  query.on("end", function() {
    response.render("index", { title: "Express", data: rows });
  });
});

The template loops over data and prints each record’s name, mail, and memo fields in a table.

Executing SQL Queries

Database access is based on connecting and then calling client.query.

var query = client.query("select * from mydata;");

query returns a Query object. It does not directly return selected rows, because the work happens asynchronously. Use the row, end, and error events to handle search results, completion, and failures.

Adding Records

The add page consists of add.ejs, add.js, and create.js. The form sends name, mail, and memo to /create.

<form method="post" action="/create">
  <input type="text" name="name">
  <input type="text" name="mail">
  <input type="text" name="memo">
  <input type="submit">
</form>

The POST handler reads values from request.body, executes an INSERT statement, and redirects to the home page after completion.

var qstr = "insert into mydata (name,mail,memo) values($1, $2, $3);";
var query = client.query(qstr, [name_str, mail_str, memo_str]);

query.on("end", function() {
  response.redirect("/");
});

The $1, $2, and $3 placeholders receive values from the second argument array. With this pattern, most basic database operations can be implemented by connecting to the database, running SQL with query, and handling the resulting events.