How to Properly Type Supabase Responses When Using JOIN Operations in TypeScript?

Question:

I'm using Supabase with TypeScript and I'm having trouble typing the response when performing JOIN operations between tables. Before implementing the JOIN operation, I was using the type like this:

try {
  data = await fetchIngresosForMonth(firstDayOfMonth, lastDayOfMonth) as Tables<"ingresos">[];
} catch (error) {
  console.error("Error fetching data:", error);
  // TODO: Handle the error appropriately, maybe render an error message
}

After adding the JOIN operation, I extended the type and it seems to be working. However, I'm interested in knowing if this is the correct or most efficient approach. Below is how I extended the type:

Here's my current code:

//components/tablaIngresos.tsx
import { createServerComponentClient } from "@supabase/auth-helpers-nextjs";
import { cookies } from "next/headers";
import { Database, Tables } from "@/types/supabase";
import { DbResult, DbResultErr, DbResultOk } from "@/types/dBTypes";

async function fetchIngresosForMonth(
  firstDayOfMonth: string,
  lastDayOfMonth: string,
): Promise<DbResultOk<any>> {
  const supabase = createServerComponentClient<Database>({ cookies });
  const query = supabase
    .from("ingresos")
    .select(`
    id,
    fuente,
    cantidad,
    timestamp_with_time_zone,
    user_id,
    FuenteIngreso:fuente (id, fuente_name)
  `)
    .gte("timestamp_with_time_zone", firstDayOfMonth)
    .lte("timestamp_with_time_zone", lastDayOfMonth);

  const result: DbResult<typeof query> = await query;
  // ... rest of the code
}

I tried extending the type like this:

type ExtendedIngresosRow = Tables<"ingresos"> & { FuenteIngreso: { fuente_name: string } };

I also tried another approach:

type ExtendedIngresosRow = Tables<"ingresos"> & { FuenteIngreso: Pick<Tables<"FuenteIngreso">['Row'], 'fuente_name'> };

              <tbody className="text-black dark:text-white dark:bg-gray-900">
                {data?.slice(0, maxRows).map((item, index) => (
                  <tr key={index} className="border-b dark:border-neutral-500">
                    <td className="whitespace-nowrap px-6 py-4 font-medium">
                      {index + 1}
                    </td>
                    <td className="whitespace-nowrap px-6 py-4">
                      {item.FuenteIngreso.fuente_name}
                    </td>
                    <td className="whitespace-nowrap px-6 py-4">
                      {item.cantidad}
                    </td>
                    <td className="whitespace-nowrap px-6 py-4">
                      {item.timestamp_with_time_zone}
                    </td>
                  </tr>
                ))}
              </tbody>

Is my approach correct? Or does Supabase offer a better way to handle this?

The problem with your approach is that now your application is thinking of entities as database structures, which are always tables for Relational databases like PostgreSQL, which is what Supabase offers right now. These tables do not reflect real life nor how your application understands your business. What I would suggest you do is have separate types for your business entities that are independent of your tables and then use a mapper between your supabase client and the rest of the app, so nowhere in your app you would use "Tables" and "Rows": as soon as you get that from the API you map them to real business objects, and right before calling the API you map objects to database tables if needed.

Now, this doesn't mean you need to have duplicate types (or maybe it does, depends on your choice). You can try to derive your business types from your Database types, eg:

type Ingreso = Tables<"ingresos">['Row'] & { FuenteIngreso: Pick<Tables<"FuenteIngreso">['Row'], 'fuente_name'> };

(haven't tried my code, I am hadcrafting so it may fail)

The advantage of this approach is that you have less duplication and if a type changes on the database it automatically reflects in your code... this is only theory as your logic should change as well and also, how often do we change data types of a column? A disadvantage is that your entities properties now match your DB styles (you now have to use snake_case in your business object types), and also they may have properties you don't care for like date_created.

Alternatively you can create your own types with, yes, a lot of duplication in your types, but also more control and less pollution on your business objects. You do

type Ingreso = {
    id: number,
    cantidad: number,
    user: User,
    fuente: string, // if you just need the name
    fuenteIngreso: FuenteIngreso, // if you want the whole object for calculation needs
}