Category: Databases

In my last post, I talked about using the update command and offered up this simple example:

UPDATE users
SET instances = instances - 1
WHERE id = 102
RETURNING *

Switching this to Typescript code that would work with PG (and having it update the first_name rather than the instances) would look like this:

import { Pool } from 'pg';

const pool = new Pool({
  host: 'localhost',
  user: 'database-user',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

const updateFirstName = async (
  firstName: string,
  id: number,
): Promise<void> => {
  const client = await pool.connect(); // create a connection

  try {

    const query = `
      UPDATE users
      SET first_name = '${firstName}'
      WHERE id = ${id}
      RETURNING *
    `;

    await client.query(query); // use the connection
  } catch (error) {
    // Deal with your catch error
  } finally {
    client.release(); // release the connection
  }
};

The problem with this, obviously, is that there isn’t any parameterization, which means that it’s vulnerable to SQL injection attacks.

I looked around trying to find an example of parameterization with the PG npm package as it related to an UPDATE query/command. I found plenty of examples for the INSERT INTO query/command, but nothing with the UPDATE.

So, I did some experimenting and determined that this works:

import { Pool } from 'pg';

const pool = new Pool({ // Customize all of this...
  host: 'localhost',
  user: 'database-user',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
})

const updateFirstName = async (
  firstName: string,
  lastName: string,
  id: number,
): Promise<void> => {
  const client = await pool.connect(); // create a connection

  try {

    if(typeof id !== 'number) {
      throw new Error('non-number id passed into updateFirstName');
    }
    const query = `
      UPDATE users
      SET first_name = $1, last_name = $2
      WHERE id = ${id}
      RETURNING *
    `;

    const values = [firstName, lastName];

    await client.query(query, values); // use the connection
  } catch (error) {
    // Deal with your catch error
  } finally {
    client.release(); // release the connection
  }
}

You’ll obviously have to customize that some to match your use case, but that should give you the correct approach.

I’ve been reworking my side project to use Typescript and the PG npm package (so that I can use a postgres database). In the course of doing that, started out using ‘INSERT INTO’ statements because they have the benefit of creating a new record if no record currently exists with that primary key, and updating the existing record if there currently exists a record with that primary key.

That has by and large worked well, because my initial tables that I’ve been rebuilding used a string as a primary key rather than an auto-incrementing integer (or in postgres parlance a SERIAL).

Once I started trying to do INSERT statements into a table with a more ‘normal’ primary key, I ran into problems around the primary key.

Additionally, I came across a scenario where I needed to decrement a number in the database and I didn’t want to have to do a query to get the current number, and then do an insert because I suspected that would result in more load on my database and more latency for my application.

You may not run across the same issues, but if you do run into problems with the INSERT approach, it may be worth playing around with the UPDATE command.

Here is an example of an update query:

UPDATE users
SET instances = instances - 1
WHERE id = 102
RETURNING *

That syntax is fairly easy to find, but I couldn’t find an example with parameterization for the PG npm package, so I had to do some experimenting with that. More on that next time.