Bootcamp
Search…
3.4.7: pg Promises

Introduction

Instead of using nested callbacks for our database queries we can use Promises. The following examples assume we have imported pg and initialised a new PG Client as per 3.5.2: PostgreSQL Node App.

Sample SELECT Query with Promises

client
.query('SELECT * from cats')
.then((result) => console.log(result.rows[0]))
.catch((error) => console.log(error.stack));

Sample INSERT Query with Promises

const values = ['Fluffy'];
​
client
.query('INSERT INTO cats (name) VALUES ($1) RETURNING *', values)
.then((result) => {
console.log(result.rows[0]);
})
.catch((error) => console.log(error.stack));

Sequential SELECT Queries Using Promises

We can get all recipes in a category by first doing a query for the category ID based on the category name, then joining the recipe_categories table with the recipes table, filtering by the retrieved category ID. Promises mean we only need 1 level of nesting. The result of the 1st query is used in the 2nd query on line 20.
const categoryName = 'vegan'; // from request.query
​
client
.query('SELECT * from categories WHERE name=$1', [categoryName])
.then((result) => {
// TODO: check if result.rows contains a row
console.log(result.rows[0]);
const categoryId = result.rows[0].id;
return client.query(
`SELECT
recipes.id,
recipes.label,
recipes.category_id AS recipe_category_id,
categories.id AS category_id,
categories.name AS category_name
FROM recipes
INNER JOIN categories
ON categories.id=recipes.category_id
WHERE category_id=$1`,
[categoryId]
);
})
.then((result) => {
console.log(`all recipes with category ${categoryName}`);
console.log(result.rows);
})
.catch((error) => console.log(error.stack));