Speed up you app by returning multi-dimensional data in Postgres with Supabase

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:

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 = u.id WHERE tm.id = {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 t.id = tm.team_id 
INNER JOIN users u ON tm.user_id = u.id 
WHERE tm.id = {TEAM_ID} 

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

Team Name	First Name	Last Name	Job Title
Boardshape Inc	Nick	Holder	CMO
Boardshape Inc	Rick	Smoulders	CTO
Boardshape Inc	Tom	Holder	CEO
Boardshape Inc	Mariana	Flores	Chairperson
Boardshape Inc	David	Anderson	Non Executive
Boardshape Inc	Mo	Haider	Non Executive
Boardshape Inc	Simon	Hughes	Secretary
Boardshape Inc	Sara	Hatfield	Content 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, 
            u.id as user_id, 
            u.first_name, 
            u.last_name
                u.job_title
        FROM 
            public.team_members AS tm
        INNER JOIN 
            public.profiles AS u ON u.id = 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
    t.name,
    get_team_members(t.id) AS members
FROM teams t

You then end up with results that look like this:

Name	Members
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.