July 2021

Database Update Command

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.

Postgres ‘pg’ NPM Connection Pools

I’m currently in the middle of re-working my side project to use the pg npm package. As part of that, I now have to worry about managing the connection pool, which is something that was handled behind the scenes with the ORM that I was using previously, the ORM that I was having problems getting to play nicely with Typescript.

Online, I saw a recommendation to create a pool like so:

import { Pool } from 'pg';

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

And then to execute database operations like so:

const wrapperFunc = async (): Promise<void> => {
  const client = await pool.connect(); // create a connection

  try {

    const query = `
    CREATE TABLE users (
        email varchar,
        firstName varchar,
        lastName varchar,
        age int
    );
    `;

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

Releasing the connection is important because otherwise you end up with a bunch of zombie connections that your database is waiting on, but which aren’t live anymore. Eventually, if you don’t release the connections, then your database hits its max connection count and it starts refusing new connections.

I had the bright idea of changing up my code to look more like this, which I thought would work just as well:

const wrapperFunc = async (): Promise<void> => {
  const client = await pool.connect(); // create a connection

  try {

    const query = `
    CREATE TABLE users (
        email varchar,
        firstName varchar,
        lastName varchar,
        age int
    );
    `;

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

I thought that would still result in the client being released regardless of whether we threw an error or not, but quickly started getting errors while trying to run my unit tests.

I can’t explain why yet, but for some reason, a client.release() position like I’ve got above doesn’t release the clients like it should, so I’ll be sticking with putting my client.release() statements in my finally block going forward.

Thoughts on Typescript

I once worked at a company where there was an ongoing rivalry among the developers between Java and Javascript/NodeJS.

To be honest, I didn’t know enough back in those days to have anything remotely resembling an informed opinion. I’m not sure that much has changed there, but one of the things that surprised me a bit was the way that one of the Java proponents would dismiss Typescript as just being a way to try and make Javascript more like Java.

I ended up ultimately choosing to focus my learning efforts more an Javascript/NodeJS simply because I liked the idea of not having to master Java for the back end and Javascript for the front end, but I always figured that someone who was in favor of strong typing would be in favor of bringing stronger typing to Javascript.

I’ve now been working with Typescript for a few months. There is still obviously a ton that I need to get my arms around with Typescript, but my initial impression has largely been favorable for all of the reasons that people typically praise it for.

I do occasionally miss being able to do crazy things with my variables, but by and large those types of things are likely to end up bitting me at a future point. By giving that up, I get instant feedback from my code editor when I mis-remember the type of a variable and try to use it in a way that is going to get me in trouble the first time I do a test run of my new code.

Getting things setup with some of the packages I was previously using on my side project proved to be beyond by current abilities, but once the setup is done, I seem to be developing at a faster rate simply because I’m chasing down fewer typing errors.

Typescript doesn’t magically make NodeJS something it isn’t. You still have to worry about blocking the event loop, but all in all (for what it’s worth), I think it’s a helpful extension to the base language.