This guide is a work in progress. If you have ideas for best practices, please use the "Suggest improvements to this page" link above to submit them, or discuss them in #documentation on the PostGraphile Discord chat.
PostgreSQL does NOT add indexes to foreign keys by default. This isn't an
issue for the forward relation (
user_id → user), but for the reverse
relation (user → things by
user_id) it can make the lookup very expensive.
Always add indexes to your foreign keys.
If you're using RLS, it's best to enable it on every table in your database. You should at least enable it on every table in your exposed schemas.
The following are fine:
-- ✅ Fine GRANT SELECT ON users TO graphql_role; -- ✅ Fine GRANT INSERT (column_1, column_2, ...) ON users TO graphql_role; -- ✅ Fine GRANT UPDATE (column_a, column_b, ...) ON users TO graphql_role; -- ✅ Fine GRANT DELETE ON users TO graphql_role;
The following should be avoided:
-- 🛑 DO NOT USE GRANT SELECT (column_a, column_b) ON users TO graphql_role; -- 🛑 DO NOT USE GRANT INSERT ON users TO graphql_role; -- 🛑 DO NOT USE GRANT UPDATE ON users TO graphql_role;
Column-level SELECT grants cause a lot of issues:
- Reduces the number of optimisations we can do
SELECT * FROM
- Cannot use
RETURNING *(e.g. what we do in CRUD operations currently)
- Cannot pass a row type into a function (like we do for computed columns)
Table-level INSERT/UPDATE grants are not advisable because they lack the explicitness that should come from such operations.
You can get a leg up on this using
The long names PostGraphile uses by default are to try and avoid people
getting naming conflicts when they run PostGraphile for the first time. Once
you're more comfortable you should move to using shorter names as it's a
GraphQL best practice.
(Scan the docs for
[RECOMMENDED], but take a moment to understand why
they're recommended and yet not enabled by default - they often require
greater knowledge of PostGraphile and your database.)
Reason: triggers in PostgreSQL run in lexicographical order.