PostgreSQL & NodeJS On Backend Best Practices

Don’t mix DB queries and View

Because mixing the code for interacting with the database (business logic) and the views on the backend side subsequently leads to the fact that it will be very difficult for you to accompany and maintain the application (technical debt).

Modern frameworks, such as ExpressJS, RectJS, NestJS (etc.), suggest using the Model-View-Controller (MVC) pattern to separate the logic of working with database and view, and the connection between these abstractions is carried out in the intermediate controller.

Don’t trust data sent by users

Don’t use data entered by users directly in the database queries without validation and sanitisation, I recommend reading what SQL injections are. In short, if you don’t clean up user-entered data, at some point the user can submit a specially formed query and gain access to your database or corrupt it.

Some JS frameworks have a few embedded mechanisms for cleaning up, more details about validation and sanitisation in this article.

Don’t Repeat Yourself (DRY)

This is a programming pattern that encourages you not to repeat yourself when developing software, but instead try to reuse existing code. For example, in your project of several similar requests, you can create a function/method that will generate a request to the database from a template, and pass the value that can be changed through a variable.

Instead of:

let user1 = "SELECT id, name FROM users WHERE id = 1 LIMIT 1";
let user2 = "SELECT id, name FROM users WHERE id = 2 LIMIT 1";

Use something like that:

function getUserById(id) {
  if (typeof id != 'number') {
    throw "Not a number";
  }
  if (id <= 0) {
    throw "ID must be greater than zero";
  }
  return "SELECT id, name FROM users WHERE id = " + id + " LIMIT 1";
}


let user1 = getUserById(1);
let user2 = getUserById(2);

Use prepared statement

Instead of forming a query using the mixing of variables and SQL directives, it is most practical to use prepared statements, such a feature will complicate the code a little, but allow you to worry a little less about the data submitted by users, because escaping in this case is done automatically.

Using node-postgres extension:

const query = {
  text: 'INSERT INTO users(name, email) VALUES($1, $2)',
  values: ['brianc', 'brian.m.carlson@gmail.com'],
}

client
  .query(query)
  .then(res => console.log(res.rows[0]))
  .catch(e => console.error(e.stack))

Don’t keep unnecessary database connections open

In order to work with a database from NodeJS, we first need to connect to DB, then create a state, in which we can describe what query needs to be executed, and then return the data. Developers sometimes forget to close the connection after the work is completed, of course NodeJS and libraries can perform such tasks, but it’s safer to close everything manually just in case.

Using node-postgres extension:

const { Client } = require('pg')
const client = new Client()
await client.connect()

const res = await client.query('SELECT $1::text as message', ['Hello world!'])
console.log(res.rows[0].message) // Hello world!
await client.end()

Try to avoid SELECT * queries

Such queries cause a large load on the DBMS, especially if you need to return a large set of data and often you don’t need data from all the columns of the table for subsequent work.

Instead of:

SELECT * FROM users;

Better to use something like that:

SELECT id, name FROM users;

Use LIMIT/OFFSET pair for pagination

Instead of displaying all the data from the table at once, it is more convenient to use pagination. You may use it on the database side with help of LIMIT and OFFSET directives.

LIMIT – allows you to limit the number of results to the specified number of rows.

OFFSET – indents the top by the specified number of rows.

For example:

SELECT id, price, created_at FROM orders LIMIT 10 OFFSET 10;

This ^ mean: return 10 rows from orders table with offset 10 rows from begin.

Sliding window – another alternative for pagination

Sometimes there are situations when the possibilities of LIMIT/OFFSET is not enough, but it is still necessary to select data in blocks. Suppose we have a Cron script (which executed every hour) to fetch all data from the database, but ignore the data that was selected during previous runs of the script.

Here is small example of script:

const { Client } = require('pg');
const fs = require('fs');

// Get last ID
let lastId = 0;
try {
  lastId = fs.readFileSync('/tmp/lastId', 'utf8');
} catch (err) {
  console.error(err);
}

// Open connection to database
const client = new Client();
await client.connect();

// Select all rows with ID larger than $lastId
const results = await client.query('SELECT id, name, price FROM products WHERE id > ? ORDER BY id', [lastId]);
// ... some work with response ...

// Close connection
await client.end();

// Detect last ID
lastId = results[results.length - 1].id;

// Save last ID
fs.writeFileSync('/tmp/lastId', lastId, 'utf8')

In this example, at the first start, we request all rows whose ID is greater than 0, after which we read the value of the last ID (let’s say it is 100) and save it, for example, on the file system.

After one hour 100 more records have been added during this time. Our script reads the value of the last ID from the file and selects all records with a value greater than 100. And so on.

To solve such problems, the Sliding Window principle is used.

Always keep passwords encrypted

If an attacker somehow gained access to your database, you must prevent the possibility of reading the passwords used by users, with this you may help embedded password encryption and password validation functions embedded to PHP.

Instead of:

let nickname = 'user1';
let password = 'secret_password';
let query = "INSERT INTO users (nickname, password) VALUES (?, ?);"

Use something like this:

const bcrypt = require("bcrypt");

let nickname = 'user1';
let password = await bcrypt.hash('secret_password', 10);
let query = "INSERT INTO users (nickname, password) VALUES (?, ?);"

Then for verification:

const bcrypt = require("bcrypt");

let password = 'secret_password';
let hash = '$2y$10$9trAUhBsQr.rTZuRz9T...7piFrj4weTgckSWvtwKqAVXnlRqq26y'; // secret_password

let isValidPassword = await bcrypt.compare(password, hash); // true
// or
let isValidPassword = await bcrypt.compare('wrong_password', hash); // false

Read more about bcrypt library.

Try to avoid subqueries, use JOIN instead

There are few ways to get intertwined data from multiple tables in a single query, this is usually done either with a subqueries or with a JOIN.

Subqueries are much more readable (human friendly) but usually require more server resources, while those implemented with the JOIN directive are less readable but have better performance (computer friendly).

For example you have two tables: accounts and users, and you need to select all users connected with an account, balance of which is larger than 100 USD.

Using subqueries:

SELECT id, name
FROM users
WHERE account_id IN (
  SELECT id
	FROM accounts
	WHERE balance >= 100
);

Using JOINs:

SELECT u.id, u.name
FROM users AS u
LEFT JOIN accounts AS a ON (u.account_id = a.id)
WHERE a.balance >= 100;

Use Transactions for safe from Race Conditions

Let’s analyze the situation when you have an example table and two scripts (A and B) that work with this table. Each of these scripts performs small updates of individual rows in the same column, each script first makes a request to the database, after which some analysis of the received data, after which it makes a change.

Script A

SELECT * FROM example WHERE id=1; // id:1, name:demo
UPDATE example SET name='test';

Script B

SELECT * FROM example WHERE id=1; // id:1, name:demo
UPDATE example SET name='zzz';

In some case, it may turn out that both scripts took data on the same row and are trying to update, usually only the data that was updated last (let’s say it was script B) is saved, because the data entered earlier (script A) is overwritten.

To prevent such situations transactions will help us. They are performed in three stages: the beginning of the transaction, the logic of working with data, the commit (that is, the completion).

START TRANSACTION;
SELECT * FROM example WHERE id=1; // id:1, name:demo
UPDATE example SET name='zzz';
COMMIT;

If an error occurred during the execution of this block before commit (for example, because another script changed the data), we can rollback and the database will not save the changes described inside the transaction.

ROLLBACK;

Using this technique will help you avoid a lot of unpleasant situations associated with the Race Conditions problem.

Conclusion

Of course, these are not all possible recommendations, only a basic set, it is easy to follow these recommendations, and if they are used, the quality and readability of the code will be much higher, and the code itself will be safer.