< Previous Article Next Article >

pjs.query()

The pjs.query() API runs an SQL statement against your database. It can retrieve, insert, or update data. The results are returned as an array of objects.

Here is a simple example of retrieving some database records:

let products = pjs.query("SELECT productCode, productName FROM products");
let productCount = products.length;

SQL Parameter Markers

pjs.query() can use SQL parameters markers for dynamic data.

The following statement is bad practice because it is prone to an SQL injection attack:

// Don't do this!
let product = pjs.query("SELECT * FROM products WHERE productCode = " + code)[0];

Instead, you should use a parameter marker, designated by a ? symbol, as follows:

// Do this instead!
let product = pjs.query("SELECT * FROM products WHERE productCode = ?", code)[0];

The 2nd parameter to pjs.query() replaces the ? in your SQL statement. Note that [0] is an index to the resulting array -- it is an attempt to retrieve the first record from the result set.

But what if you have multiple pieces of dynamic data? Simply provide the 2nd parameter to pjs.query() as an array:

let product = pjs.query(
                "SELECT * FROM products WHERE productCode = ? AND stockQty > ?", 
                [code, orderQty] )[0];

Inserting and Updating

To insert or update data, the parameter marker should map to an object of fields to insert or update.

INSERT example:

pjs.query("INSERT INTO products SET ?", {
  productCode,
  productName,
  price,
  stockQty: 0
});

UPDATE example:

pjs.query("UPDATE products SET ? WHERE productCode = ?", [ 
  { 
    stockQty: stockQty - orderQty,
    lastUpdated: new Date()
  }, 
  code 
]);

Questions?

Have questions about this topic? Ask for help on our Profound.js Spaces Discussion Forum.

< Previous Article Next Article >