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.