Ben Vogt

PSQL functions for simpler queries

This is a workspace post. It's a working document of my thoughts as I built something or pieced together an idea. It might be unstructured and contain spelling errors. It also might not look good on a small screen. But it could be useful to someone or related to another post, so I'm publishing it as-is.

Multiple round trips to a postgres database can slow your application down. Adding just a couple of milliseconds each time is a pain. It’s also cumbersome to chain multiple SQL queries together in your services.

As long as you’re not married to the idea of separating your database from your service, there’s a better solution. You can use PG functions, and a WITH clause to clean up your logic and reduce round trips.

Here’s the function.

CREATE FUNCTION pt_raise_permissions_error() RETURNS integer
    LANGUAGE plpgsql AS
$$
BEGIN
    RAISE 'permission denied' USING ERRCODE = 'D3GA2';
RETURN 1;
END;
$$;

And here’s what an insertion query looks like.

WITH user_check AS (
	SELECT pt_raise_permissions_error()
	FROM (
		SELECT COUNT("id")
		FROM "Users"
		WHERE "id" = 10
	) AS found
	WHERE found.count = 0
	UNION SELECT 1
), file_insertion AS (
	INSERT INTO "Files"
	("id", "title", "archived", "createdAt")
	VALUES (99, 'untitled.txt', false, NOW())
	RETURNING 1
), resource_insertion AS (
	INSERT INTO "Resources" ("id", "resource_type")
	VALUES (99, 'file')
	RETURNING 1
), permissions_insertion AS (
	INSERT INTO "Permissions"
	("id", "subject_type", "subject_id", "resource_type", "resource_id", "role", "createdAt")
	VALUES
	(199, 'user', 10, 'file', 99, 'file_owner', NOW())
	RETURNING 1
) SELECT * FROM user_check, file_insertion, resource_insertion, permissions_insertion;

Two things to remember:

  1. Map your error codes (in this case D3GA2) to something in your service.
  2. Make sure you’re not colliding with existing PG error codes. See this stackoverflow and the pg docs for more info. I wouldn’t write a function for every error case, but just for not-founds and permissions.

They say you’re not supposed to tightly couple your database and services, but there are already a couple of ways you’re doing this if you’re using a relational DB. You have migrations you need to run, and you’re probably using an ORM or writing SQL in strings somewhere. Writing one more migration to add the PG function and using the WITH clause doesn’t add any significant amount of complexity, and if you standardize its use throughout your service, it contains and limits complexity.

There is a performance trade off though. Your mileage may vary depending on your read and write patterns.

psql | workspace | programming
2021-08-09