Writing a RESTful API with Express & MassiveJS

MassiveJS is an amazing library that lets you query your PostgreSQL database from Node in a very easy, straight-forward way. Instead of trying to map your database to objects, like an ORM would, MassiveJS lets you work directly with your tables and db functions.

In this post, I’ll show you how to write a simple API for a Todo app with Express and MassiveJS.

** Note: You can find the complete code here.
** Update: Nov 7, 2016 — updated express routes to use res.json().

Let’s start with a basic express app:

npm install -g express-generator yarn
express todoApi
cd todoApi && yarn

Now let’s create a Postgres DB for our app, and install MassiveJS.

psql -c "CREATE ROLE todo_user \
  LOGIN PASSWORD 'todo_password' SUPERUSER;" template1
psql -c "CREATE DATABASE todo WITH OWNER todo_user;" template1
yarn add massive

In order to get the db credentials into MassiveJS, I’ll use the dotenv module, which lets us read environment variables from a .env file.

yarn add dotenv
dburl="postgres://todo_user:todo_password@localhost/todo" &&
echo "DATABASE_URL=${dburl}" > .env

Now we can attach a db instance to our Express app when it’s starting:

# app.js
require('dotenv').config();
var express = require("express");
var app = express();
var massive = require("massive");
app.set('db', massive.connectSync({
  connectionString : process.env.DATABASE_URL
}));

You don’t need to worry about opening/closing connections for each query, MassiveJS will handle the connection pool for you.

To handle migrations, I’ll use a module called db-migrate, which I particularly like because it allows us to write migrations in plain SQL, instead of forcing us to learn a new syntax.

yarn add db-migrate db-migrate-pg --dev
db-migrate create add_todo_table --sql-file

Now we can edit our migration files to create a table for our todo items.

# migrations/sqls/20161029163827-add-todo-table-up.sql
CREATE TABLE "todo" (
  "id" SERIAL NOT NULL PRIMARY KEY,
  "text" TEXT,
  "completedAt" TIMESTAMPTZ,
  "deletedAt" TIMESTAMPTZ
);

# 20161029163827-add-todo-table-down.sql
DROP table "todo";

We can run the migrations with db-migrate up and db-migrate down.

With our table ready, we can proceed and write some CRUD endpoints for our todos.

var express = require('express');
var router = express.Router();
// INDEX todos
router.get('/', function(req, res, next){
  req.app.get('db').todo.find(
  function(err, result){
    if(err){ return next(err); }
    res.json(result);
  });
});
// CREATE a todo
router.post('/', function(req, res, next) {
  req.app.get('db').todo.save({
    text: req.body.text
  }, function(err, result){
    if(err){ return next(err); }
    res.status(201).json(result);
  });
});
// UPDATE a todo
router.put('/:id', function(req, res, next) {
  req.app.get('db').todo.save({
    id: req.params.id,
    text: req.body.text
  }, function(err, result){
    if(err){ return next(err); }
    else if(!result){
      return res.status(404).send('Not Found');
    }
    res.json(result);
  });
});
// DELETE a todo
router.delete('/:id', function(req, res, next) {
  req.app.get('db').todo.save({
    id: req.params.id,
    deletedAt: 'NOW()'
  }, function(err, result){
    if(err){ return next(err); }
    else if(!result){
      return res.status(404).send('Not Found');
    }
    res.json(result);
  });
});
module.exports = router;

Without having to define any models, MassiveJS automatically created a db.todo object, which represents our todo table. It has a couple of built-in functions for the most common operations: insert, update, destroy, find, count, etc. (Even full-text search is ready for us to use).

Notice the save function can either insert or update a record, depending on whether the primary key is included in the given fields.

So far so good, but we’re still not taking advantage of one of MassiveJS’ most useful features: working with SQL functions directly.

Let’s say we wanted to add users to our app. It’s important that we don’t store passwords as plain text, so we’ll use PostgreSQL’s awesome pgcrypto extension for salting and encrypting passwords (pgcrypto also allows us to use uuids, which is pretty nice).

db-migrate create add-user-table --sql-file
# 20161031162820-add-user-table-up.sql
-- enable pgcrypto
CREATE EXTENSION pgcrypto;
-- create user table
CREATE TABLE "user" (
  "id" UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  "email" TEXT NOT NULL UNIQUE,
  "password" TEXT NOT NULL
);

# 20161031162820-add-user-table-down.sql
-- remove user table
DROP TABLE "user";
-- disable pgcrypto
DROP EXTENSION pgcrypto;

By default, MassiveJS will read any .sql file inside a db folder on our project root, so let’s create the following SQL functions:

# db/createUser.sql
INSERT INTO "user"(email, password)
     VALUES (lower($1), crypt($2, gen_salt('bf', 8)))
  RETURNING id, email;
# db/findUserByCredentials
SELECT id, email
  FROM "user"
 WHERE email = lower($1)
       AND password = crypt($2, password);

And now we can add some API endpoints for users:

var express = require('express');
var router = express.Router();
// FIND a user by credentials
router.post('/sessions', function(req, res, next){
  req.app.get('db').findUserByCredentials([req.body.email, req.body.password],
  function(err, result){
    if(err){ return next(err); }
    else if(!result[0]){
      return res.status(404).send('Not Found');
    }
    // you could construct a JWT or a session here instead of
    // returning the user object
    res.status(201).json(result[0]);
  });
});
// CREATE a user
router.post('/', function(req, res, next){
  req.app.get('db').createUser([req.body.email, req.body.password],
  function(err, result){
    if(err){ return next(err); }
    res.status(201).json(result[0]);
  });
});
module.exports = router;

That’s it. MassiveJS took our SQL files and made them available as JS functions on our db object.

Note: You could have also created a migration and defined an SQL function directly on your database, MassiveJS would have read it from there as well.

I hope this post allowed you to grasp how easy it is to work with Node and Postgres through the MassiveJS library. There’s much more info available at the official MassiveJS documentation page.

You can take a look at the complete source code for this blog post here.

PS: Here are the curl commands you would use to interact with the API we just created:

# index todos
curl -v http://localhost:3000/todos
# create todo
curl -v -X POST -H "Content-Type: application/json" \
--data "{\"text\": \"pick up milk\"}" \
http://localhost:3000/todos
# update todo
curl -v -X PUT -H "Content-Type: application/json" \
--data "{\"text\": \"learn pgSQL\"}" \
http://localhost:3000/todos/1
# delete todo
curl -v -X DELETE http://localhost:3000/todos/1
# create user
curl -v -X POST -H "Content-Type: application/json" \
--data "{
  \"email\": \"john@snow.com\",
  \"password\": \"winter is coming\"
}" \
http://localhost:3000/users
# find user by credentials
curl -v -X POST -H "Content-Type: application/json" \
--data "{
  \"email\": \"john@snow.com\",
  \"password\": \"winter is coming\"
}" \
http://localhost:3000/users/sessions