Teams and members

How you can speed up your app by returning multi-dimensional data in Postgres with Supabase

6 min read

Tom Holder

Tom Holder - Founder BoardShape

Let's reduce the amount of database queries you're running per page and make your code cleaner at the same time by returning multi-dimensional data and type it correctly.

For the purpose of explaining this. we are going to use the relationship that exists in our product between teams and the members of a team. Each team can can have one or many members. This relationship can be visually seen on a team card on the dashboard of the app where we show team members that belong to each team:

Teams and members

Approach 1 - Multiple Queries πŸ‘Ž

Typically, when developing something like this we might run following queries on our page:

SELECT * FROM teams;

And, then, for each team we would run:

SELECT * FROM team_members tm INNER JOIN users u ON tm.user_id = WHERE = {TEAM_ID}

😱 ok, so whilst this SQL is really simple to understand it creates an n+1 issue. This basically means for every team you're having to run an additional query, as the number of teams grows so does the number of queries.

Approach 2 - Single Join Query πŸ‘Ž

The other alternative, is to run something like this:

SELECT * FROM teams t INNER JOIN team_members tm ON = tm.team_id INNER JOIN users u ON tm.user_id = WHERE = {TEAM_ID}

This is pretty straight-forward SQL, the problem is you end up with results like this:

Team NameFirst NameLast NameJob Title
Boardshape IncNickHolderCMO
Boardshape IncRickSmouldersCTO
Boardshape IncTomHolderCEO
Boardshape IncMarianaFloresChairperson
Boardshape IncDavidAndersonNon Executive
Boardshape IncMoHaiderNon Executive
Boardshape IncSimonHughesSecretary
Boardshape IncSaraHatfieldContent Manager

You have one row for each team member and all the team data is replicated. This is annoying because you want to iterate teams and then iterate members within those teams. Whilst you can do this in JS the code is going to be a bit messy and you're sending more data over the wire.

Another important reason why not to take this approach is that Supabase can not automatically type this query for you. You will need to create a view and that view will be it's own type.

Approach 3 - Strongly typed multi-dimensional results πŸ‘πŸ”₯

Here is a different approach you might want to consider. Returning one row per team but with the team members embeded as json per row, and here's how to do it. Note. For the sake of brevity these aren't our actual queries, they only contain a subset of the fields and consequently their may be some minor discrepancies. We are also doing more complex joins because of our user security context.

Start with creating a postgres function:

CREATE OR REPLACE FUNCTION public.get_team_members(team_id uuid) RETURNS jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER AS $function$ DECLARE result jsonb; BEGIN SELECT json_agg(row_to_json(t)) INTO result FROM ( SELECT tm.team_id, tm.user_id, as user_id, u.first_name, u.last_name FROM public.team_members AS tm INNER JOIN public.profiles AS u ON = tm.user_id WHERE tm.team_id = get_team_members.team_id ) t; RETURN result; END; $function$ ;

The result of this function gives you a JSON array that looks like this:

[ { "team_id": "aad4f3b2-2bfa-451a-a1e7-359184c17ca4", "user_id": "5e36b5bf-a446-4d0a-b4f6-e2ed4e6ccb83", "last_name": "Holder", "first_name": "Nick", "job_title": "CMO" }, { "team_id": "aad4f3b2-2bfa-451a-a1e7-359184c17ca4", "user_id": "0e23efeb-a011-4d7c-8dbc-d32404f28f5c", "last_name": "Flores", "first_name": "Mariana", "job_title": "Chairperson" }, ...Abbreviated ]

Now, we want to return the members as above alongside our teams data. So we create a view like the following:

CREATE OR REPLACE VIEW public.teams_with_team_members AS SELECT, get_team_members( AS members FROM teams t

You then end up with results that look like this:

Boardshape Inc[ JSON ARRAY AS ABOVE ]

If it isn't obvious, you would have one row per team. You can now iterate teams and iterate members within those teams.

The magic of Supabase Typing

Wherever possible, we want to leave our typing to Supabase. We just run the following command:

supabase gen types typescript --local --schema public > src/database.types.ts

It spits out all the types for our tables and views. We maintain our own database.alias.types.ts where we shorten down the Supabase types we commonly use like this:

export type ViewTeamsWithTeamMembers = Database['public']['Views']['teams_with_team_members']['Row']

An issue with this type though is that it looks like this:

teams_with_team_members: { Row: { name: string | null members: Json | null } ...

The issue here is that members is of type JSON and isn't a strong type that will give us type checking. We don't want to adjust this type because it's automatically maintained by Supabase. What we can do to avoid this is create our own type in aliases based on the above which looks like this:

export interface TeamsWithTeamMemberProfiles extends Omit<ViewTeamsWithTeamMembers, 'members'> { members: TeamMembers[] | null }

Here we re-type members to be a strongly typed array of TeamMembers which is just another auto-generated type from Supabase:

export type TeamMembers = Database['public']['Tables']['users']['Row']

There is actually an error in the above because our get_team_members database function doesn't actually return a record from users, it's a joined hybrid record which Supabase doens't know about, so there's actually another custom type you need to put in here but I've kept this simple by way of an explanation. The important thing is, don't adjust your supabase types, use them as a starting point for your own types.

This approach can also help with RLS rules because our function on the view is setup with SECURITY DEFINER. For more on this read our post How to implement RLS for a team invite system with Supabase.

Working with Supabase and Postgres is so enjoyable, we hope this gives you some ideas of ways to adjust your traditional approach of pulling back database records.

Please follow us on twitter for updates on when we post new engineering content and give BoardShape a try if you're interested in running better organized board meetings.

Get your Board Management in Shape

Try BoardShape today. It's FREE to get started.

Get Going - Sign Up FREE