PostgreSQL query (pool &client)

great tutorial to check pg's client: https://www.youtube.com/watch?v=ufdHsFClAk0&t=723s
great tutorial to check pg's pool: https://www.youtube.com/watch?v=GTeCtIoV2Tw

Client & Pool makes us easy to retrieve, update or write data into PostgreSQL database.

When using it

call pg into file:

const { Pool, Client } = require("pg");

create client or pool:

const pool = new Pool({
  user: "postgres",
  host: "localhost",
  database: "productDB",
  password: "password",
  port: 5432,
  max: 20,
});

if API is connected, create query (I'm trying to get {count} number of rows in {page}. OFFSET allows me to start from specific row, so I can multiply my page & count to start from that row. **REMEMBER TO: ORDER BY (sort) because OFFSET needs to have static rows of data to track it's position.

const getProducts = async (page, count, callback) => {
  const queryString = `SELECT id, slogan, description, category, default_price FROM products ORDER BY id ASC LIMIT ${count} OFFSET ${count * page};`
  await pool.query(queryString, (err, res) => {
    if (err) {
      console.log(err);
    }
    callback(null, res.rows);
  });
};

** If I want to use join table method, here is cool link to check out: http://sqlfiddle.com/#!15/70cd7/12

if I want to set up value as objects inside of an array:

ex.

{
    "id": 11,
    "name": "Air Minis 250",
    "slogan": "Full court support",
    "description": "This optimized air cushion pocket reduces impact but keeps a perfect balance underfoot.",
    "category": "Basketball Shoes",
    "default_price": "0",
    "features": [
    {
            "feature": "Sole",
            "value": "Rubber"
        },
    {
            "feature": "Material",
            "value": "FullControlSkin"
        },
    // ...
    ],
}

I can write it like this:

SELECT products.id, products.name, products.slogan, products.description, products.category, products.default_price, json_agg(json_build_object('feature', features.feature, 'value', features.value))
  AS features
  FROM products
  LEFT JOIN features
  ON products.id = features.product_id
  WHERE products.id = ${product_id}
  GROUP BY products.id, products.name, products.slogan, products.description, products.category, products.default_price`

Blog

PostgreSQL query (pool &client)

Edrick Ee

Join Our Newsletter. No Spam, Only the good stuff.

Related