PSQL functions for simpler 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.