3.4.7: pg Promises

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));

3.4.6: Forms for Many-to-Many Relationships

3.4.8: SQL Clients

Last modified 3mo ago

Copy link

Outline

Introduction

Sample SELECT Query with Promises

Sample INSERT Query with Promises

Sequential SELECT Queries Using Promises