PG Promises

Learning Objectives

After completing this lesson, you will be able to:

• Install the pg-promise package into an Express app
• Connect a PostgreSQL database to an Express app
• Make queries against a database using SQL statements
• Inject JavaScript data into SQL statements

Lesson

Overview

pg-promise is a PostgreSQL interface for Node.js. It allows you to connect to a database and make SQL queries and handle the results in a JavaScript friendly way.

How to Install

The first step is to install the package. At the root of your Express project, run the following command:

npm install pg-promise

Next, you have to initialize the project in your main Express file (main.js)

const pgp = require("pg-promise")();

Note: An extra set of parenthesis is appended to the require statement to invoke the package.

Finally, you create a database using the pgp function:

const db = pgp("postgres://username:password@host:port/database");

The argument is credentials for connecting to an existing database using a connection string but can also take a configuration object.

Environmental Variables

While building your project, you can use a local database from a application like Postgres.app. In order to publish your application for production, you will have to use a separate database connection. You can use an environmental variable to set a POSTGRES_CONNECTION_STRING variable and set different values on the local and production environments.

How to Use

Here are some of the most common functions to use.

Function	Description
db.any()	Executes a query that can return any number of rows
db.one()	Executes a query that expects exactly 1 row to be returned
db.none()	Executes a query that expects no data to be returned

All of these functions return a promise and take two arguments. The first argument is a SQL query string. It is the only required argument so if you want to make a simple query you call it like this:

db.any("SELECT * from USERS").then((users) => console.log(users));

The query is expected to return all of the users so the db.any() function was used. If you wanted to return just one user, the db.one() function would be used instead.

db.one("SELECT * from USERS WHERE id = 23").then((user) => console.log(user));

The second argument is an array for data to be injected into the query from the first argument. To do this you put $1, $2, in place of where you want the data to be injected.

db.any("SELECT * from USERS WHERE id = $1", [23]).then((user) =>
  console.log(user)
);

No Results

The db.any() and db.one() are functions used to read the database and return results. If you want to write data to the system and aren't expecting anything to be returned you use the db.none() function.

db.none("INSERT INTO users (email, isActive) VALUES ($1, $2);", [
  "john@email.com",
  true,
]);

The other times you may not have results is if a query does not match anything in the database. The db.any() function will throw an error if there are no results. To allow empty results, you can use the db.anyOrNone() function.

db.anyOrNone("SELECT * from USERS WHERE id = $1", [1000]).then((user) =>
  console.log(user)
);

This convention is also available with db.oneOrNone() and db.manyOrNone().

Summary

pg-promise is a powerful tool for working with a PostgreSQL database in your Express app.

Learning More

This is a starting point to how to use the interface, there is more functionality and utilities to understood by reading the documentation.

Training Exercises

To solidify your knowledge, here are a set of exercises that will require you to use the techniques you've just learned in the lesson above.

They are organized into small, medium, and large sized problems. The small exercises will be very similar to the examples in the lesson. If you get stuck, refer to the relevant section above. The medium exercises will require you to combine concepts. The lesson may not have a single, specific example for you to reference. The large exercises are more open-ended and may require you to search the web for additional material.

Setup Database

Before getting started with the exercises, you have to set up a database so you can work with the pg-promise library.

Save the following SQL statements to a todo-app-db-setup.sql file:

CREATE DATABASE todo_app;
\c todo_app;

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  is_completed BOOLEAN DEFAULT FALSE
);

INSERT INTO tasks (title) VALUES ('Buy milk');
INSERT INTO tasks (title) VALUES ('Sweep the house');
INSERT INTO tasks (title) VALUES ('Walk the dog');

Run the pgsql command in the same directly as where you saved the todo-app-db-setup.sql file and run the following command in the PostgreSQL command-line:

username=# \i todo-app-db-setup.sql

You will see the following output in the terminal:

CREATE DATABASE
You are now connected to database "todo_app" as user "username".
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1

You're done setting up your database. You can quit the pgsql process (\q) and can continue to the exercises using the task_app database and tasks table.

Small

Simple Query

1. Initialize a new npm package (npm init)
2. Install the pg-promise package (npm install pg-promise)
3. Create a JavaScript file to build your app (main.js)
4. Create a database connection with the todoApp database
5. Create a simple SELECT query and log all of the tasks
6. Run the file with node and view the output from the database in the logs

When you run the main file (node index.js), you will get the following results in the console:

[
  { id: 1, title: "Buy milk", is_completed: false },
  { id: 2, title: "Sweep the house", is_completed: false },
  { id: 3, title: "Walk the dog", is_completed: false },
];

View Simple Query Solution

const pgp = require("pg-promise")();
const db = pgp("postgres://localhost:5432/todo_app");
db.any("SELECT * from TASKS").then((tasks) => console.log(tasks));

Medium

Todo CRUD API

Create a simple CRUD API using Express routes that works with the pg-promise library for the following actions:

Method	Endpoint	Description
POST	/tasks	Creates a new task
GET	/tasks	Reads all of the tasks
PATCH	/tasks/:id/is_completed	Updates if the task is completed
PATCH	/tasks/:id/title	Updates the task's title
DELETE	/tasks/:id	Deletes a task

View Todo CRUD API Solution

const express = require("express");
const bodyParser = require("body-parser");
const pgp = require("pg-promise")();
const db = pgp("postgres://localhost:5432/todo_app");
const app = express();
const PORT = 3000;

app.use(bodyParser.json());

// CREATE a task
app.post("/tasks", (req, res) => {
  const newTaskTitle = req.body.title;
  db.none("INSERT INTO tasks (title) VALUES ($1)", [newTaskTitle]).then(() => {
    res.send(`Task "${newTaskTitle}" was created`);
  });
});

// READ all tasks
app.get("/tasks", (_, res) => {
  db.any("SELECT * from TASKS").then((tasks) => res.send(tasks));
});

// UPDATE if a task is completed
app.patch("/tasks/:id/is_completed", (req, res) => {
  const taskId = req.params.id;
  const taskIsCompleted = req.body.is_completed;
  db.none("UPDATE tasks SET is_completed = $1 WHERE id = $2", [
    taskIsCompleted,
    taskId,
  ]).then(() => {
    const result = taskIsCompleted ? "completed" : "not completed";
    res.send(`Task ${taskId} is ${result} (${taskIsCompleted})`);
  });
});

// UPDATE a task's title
app.patch("/tasks/:id/title", (req, res) => {
  const taskId = req.params.id;
  const taskTitle = req.body.title;
  db.none("UPDATE tasks SET title = $1 WHERE id = $2", [
    taskTitle,
    taskId,
  ]).then(() => {
    res.send(`Task ${taskId} is updated to "${taskTitle}"`);
  });
});

// DELETE a task
app.delete("/tasks/:id", (req, res) => {
  const taskId = req.params.id;
  db.none("DELETE FROM tasks WHERE id = $1", [taskId]).then(() =>
    res.send(`Task ${taskId} was deleted`)
  );
});

app.listen(PORT, () => {
  console.log(`Express application is running on port ${PORT}`);
});

Large

There is not a large exercise for this lesson. You can build upon your todo CRUD API by adding features:

• Create a view that lets you work with the tasks through a web page
• Adjust the is_completed patch endpoint to toggle the value instead of explicitly calling the new value in the body (you'll have to get the task's current value first)
• Add optional notes column to a task (you'll have to update the tasks table schema)
• Don't allow duplicate tasks (give meaningful feedback to the user if they try to add a task that already exists)

Interview Questions

Fundamentals

• What type of database is supported with the pg-promise library?
• How do you use variables as part of a query with pg-promise?
• Why should you not just use ES6 template strings?
• Where in your code should you initialize pg-promise?

Bugfix

Why would the following code be incorrect?

db.many("SELECT * from USERS WHERE id = 23").then((user) => console.log(user));

Answer

The db.many() function will expect results (plural) however a WHERE condition on the id column will only return one item, so using db.one() instead would fix this issue.

Conceptual

• If you are just passing SQL strings to pg-promise, why do you need it?

Architect

• List some of the pros and cons to using a library like pg-promise over a more structured tool like an ORM.
• How would you create a class that represents one of your database tables?

Additional Resources

• Github Repo
• Documentation