Utilizing the data layer: Conditional INSERTs in Postgres

In most applications, we create and assert guarantees in our business layer: We check that an account meets certain conditions before running another related query in the database. This leaves us with two problems: If we're querying the same data source, we've made an unnecessary roundtrip which sacrifices time we could spend on more important tasks, and by the time we're about to run the following query the state might have already changed.

This is dangerous, but luckily there's an easy way to eliminate both problems: Moving conditional checks back to the data layer, our one and only source of truth. Let's imagine we're about to insert a new transaction and need to check if the user's account balance meets the transaction amount.

const transaction = { amount: 10 };
const balance = await getAccountBalance(accountId);

if (transaction.amount > balance) {
  throw new Error('The account lacks sufficient funds');
}

await createTransaction(transaction);

This is a very simple example and obviously doesn't come close to production-grade systems, but it will help to emphasize the need for native data layer operations. Now, we're about to create a transaction, but while we made the check, our user already created another transaction on a different node, and we're about to create another transaction, which would result in double-spending. That's not good at all. We've completely lost the consistency our database provides, the consistency it was built for.

While we could theoretically fix this by blocking any further transaction creation processes, while one handler is already processing a transaction, we can also leverage the transactional foundation of our database, let's see how.

Conditional INSERTs

Our application would send something like the following query to our Postgres instance:

INSERT INTO "transaction" (
  "id",
  "amount",
  "from",
  "to"
)
VALUES (
  gen_random_uuid(),
  10.0,
  "89af7957-aacc-416c-8ecd-97c93150c223",
  "a64fb583-3533-4dca-a014-154dbf5d67a4"
);

This is a simple INSERT statement to add the transaction to our database. In the likely case that we haven't set up constraints to validate that a transaction is possible, it will go through without further problems.

Note: This post only covers conditional INSERT strategies. For use cases where locking is required, please check out resources on locks and transaction isolation levels.

Let's rewrite this INSERT statement to validate whether the balance is sufficient to persist the transaction.

INSERT INTO "transaction" (
  "id",
  "amount",
  "from",
  "to"
)
-- Look at that! We don't have to add VALUES directly;
-- instead, we can add a SELECT statement to get the data
-- we want to insert
SELECT * FROM (
  VALUES (
    gen_random_uuid(),
    10.0,
    "89af7957-aacc-416c-8ecd-97c93150c223",
    "a64fb583-3533-4dca-a014-154dbf5d67a4"
  )
-- this is just to tell Postgres that the data types between the
-- INSERT columns and VALUES match up
) AS t (uuid, numeric, uuid, uuid)
-- And we can even filter that data to make sure
-- the balance is greater or equal the transaction
-- amount we're trying to insert. If this check
-- turns out negative, we won't insert _anything_.
WHERE (
  SELECT "balance"
  FROM "account"
  WHERE "id" = '89af7957-aacc-416c-8ecd-97c93150c223'
) >= 10.0

Now we've added quite a bit of change, let's review in order:

• Postgres allows INSERT statements to contain a SELECT expression returning rows to be inserted
• The SELECT statement returns VALUES which represent the rows to be inserted
• We nest the values inside of the SELECT so we can create a temporary table with known data types, this is important for Postgres to be able to map the input data to the INSERT columns we've declared.
• The SELECT statement also includes a WHERE condition with another nested SELECT to make sure the account balance matches up to the transaction. If the account balance is insufficient, we won't insert a thing.

With the new query in place, you can restructure your code as follows:

await createTransaction({ amount: 10 });

As you can see, there's not much we have to do now, other than executing the query which will perform the necessary checks. An addition to our query might be to add a RETURNING statement to determine whether a transaction was created or not.

Of course, this doesn't replace carefully-crafted constraints to make sure our data integrity does not get broken, but I hope this post showed you a thing or two you might haven't heard of yet, I've been using this syntax for a time now and it brought a lot of benefits. In the next posts, I'll continue with additional methods to solve practical use cases with Postgres!