PSQL functions for simpler queries
Use custom PG functions to raise not-found, and permission-denied errors inside of Postgres to save the round-trip time for common auth-wrapped, and dependency-prefaced queries.
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:
- Map your error codes (in this case
D3GA2
) to something in your service. - 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.