Typescript error on CRUD API functions with Supabase. Inferring from Supabase database types error? (Tables | Row)

[eybel]

Hello I have been struggling with my API query functions where I created my CRUD operations and typed them using different options.

SUMMARY:
I have 4 entities (services, articles, pets and objects) I wanted to re-use my CRUD so I infer types (data returned types and paylods) from "database.types.ts" but I can't make it work. It always complains about either my "payload" or my "return data" type.

import { supabase } from "@/lib/supabaseClient";
import { Database } from "@/types/database.types";

type TableSchema<T extends keyof Database["public"]["Tables"]> = Database["public"]["Tables"][T];
type Row<T extends keyof Database["public"]["Tables"]> = TableSchema<T>["Row"];
type Insert<T extends keyof Database["public"]["Tables"]> = TableSchema<T>["Insert"];
type Update<T extends keyof Database["public"]["Tables"]> = TableSchema<T>["Update"];

export const crudFactory = <
  T extends keyof Database["public"]["Tables"]
>(
  tableName: T
) => ({
  fetchOne: async (id: number): Promise<Row<T>> => {
    const { data, error } = await supabase
      .from(tableName)
      .select("*")
      .eq("id", id)
      .single();

    if (error) throw new Error(error.message);

    return data as Row<T>;
  },

  fetchAll: async (): Promise<Row<T>[]> => {
    const { data, error } = await supabase.from(tableName).select("*");

    if (error) throw new Error(error.message);

    return data as Row<T>[];
  },

  create: async (payload: Insert<T>): Promise<Row<T>> => {
    const { data, error } = await supabase
      .from(tableName)
      .insert(payload)
      .select()
      .single();

    if (error || !data) throw new Error(error?.message || "Failed to create");

    return data as Row<T>;
  },

  update: async (id: number, payload: Update<T>): Promise<Row<T>> => {
    const { data, error } = await supabase
      .from(tableName)
      .update(payload)
      .eq("id", id)
      .select()
      .single();

    if (error || !data) throw new Error(error?.message || "Failed to update");

    return data as Row<T>;
  },

  delete: async (id: number): Promise<boolean> => {
    const { error } = await supabase.from(tableName).delete().eq("id", id);

    if (error) throw new Error(error.message);

    return true;
  },
});

### ERRORS :

1. it complains about the data returned (can't infer):
2. it complains about the payload (can't infer):

Conversion of type '{ article: { Row: { approved: boolean; area: string | null; category: string; condition: string | null; created_at: string; deleted_at: string | null; description: string | null; event_at: string | null; ... 6 more ...; title: string; }; Insert: { ...; }; Update: { ...; }; Relationships: []; }; ... 7 more ...; servi...' to type 'Row<T>' may be a mistake because neither type sufficiently overlaps with the other. If this was intentional, convert the expression to 'unknown' first.

Whats the best way to infer types for these CRUD? I tried lots of combination, it complains every single time. I have tried several ways of doing it, cant make it work. Its not detecting this. I cant continue with my app at the moment.

The only way was to cast the data frist as unknown first then the right type, but that is not recommended since it defeats the purpose of using typescript.

### NOTE. the returned data is typed like this (<SelectQueryError... ?). Is this the problem? Is it coming from postgress?:

const data: SelectQueryError<string> | { [K in keyof {
    article: {
        Row: {
            approved: boolean;
            area: string | null;
            category: string;

........

[avallete]

The only way was to cast the data frist as unknown first then the right type, but that is not recommended since it defeats the purpose of using typescript.

I think with that you're trying to achieve it'll be difficult to get away without any casts. Since postgrest-js is already doing a bunch of stuff to infer the type results based on both your database structure and your query (column selected, etc...) you can't really just type things like this: fetchOne: async (id: number): Promise<Row<T>>

Because even if in your case, Row<T> will be the result, at the select time, the type inference logic doesn't know about that and since your crud is valid for any of your table, well the result of the select queries at this point won't be a single Row<T> but it'll be the union of all possible Row<T>.

So doing this:

  const crudFactory = <
    T extends keyof Database['public']['Tables'],
  >(
    tableName: T
  ) => ({
    fetchOne: async (): Promise<Row<T>> => {
      const { data, error } = await postgrest
        .from(tableName)
        .select('*')
        .single()

      if (error) throw new Error(error.message)

      // Here, the type that typescript infer for `data` isn't `Row<T>` but it'll be:
      // Table1 | Table2 | Table3 | .... for all possible table in the union T
      // So casting the result to `Row<T>` in the prototype will fail.
      // But trying do directly cast `data` into `Row<T>` will fail as well, because data isn't "really" a direct `Row<T>`
      // but an inferred type based on both the parameters query within `select` (column selection) and the actual table it's selecting from. 
      return data
    },
})

The best way I've found to implement something similar to what you want is this:

  type TableSchema<T extends keyof Database['public']['Tables']> = Database['public']['Tables'][T]
  type Row<T extends keyof Database['public']['Tables']> = Prettify<TableSchema<T>['Row']>
  type Insert<T extends keyof Database["public"]["Tables"]> = Prettify<TableSchema<T>["Insert"]>;
  type Update<T extends keyof Database["public"]["Tables"]> = TableSchema<T>["Update"];

  const crudFactory = <
    T extends keyof Database['public']['Tables'],
    // This is a trick to get noticed if you trying to use a crud factory over a table that doesn't have an "id" field
    TRowId extends 'id' extends keyof Row<T> ? Row<T>['id'] : never
  >(
    tableName: T
  ) => ({
    fetchOne: async (id: TRowId) => {
      const { data, error } = await postgrest
        .from(tableName)
        .select('*')
        .eq('id', id as any)
        .single()

      if (error) throw new Error(error.message)

      return data;
    },

    fetchAll: async () => {
      const { data, error } = await postgrest.from(tableName).select("*");

      if (error) throw new Error(error.message);

      return data;
    },

    create: async (payload: Insert<T>) => {
      const { data, error } = await postgrest
        .from(tableName)
        .insert(payload as any)
        .select()
        .single();

      if (error || !data) throw new Error(error?.message || "Failed to create");

      return data;
    },

    update: async (id: TRowId, payload: Update<T>) => {
      const { data, error } = await postgrest
        .from(tableName)
        .update(payload as any)
        .eq("id", id as any)
        .select()
        .single();

      if (error || !data) throw new Error(error?.message || "Failed to update");

      return data;
    },

    delete: async (id: TRowId): Promise<boolean> => {
      const { error } = await postgrest.from(tableName).delete().eq("id", id as any);

      if (error) throw new Error(error.message);

      return true;
    },
  })

Here you can see that:

1. I had to use casting to force typescript to delay the type-check until I'm in a case where I actually use the factory on a single table (and the types can properly be inferred).
2. I've removed almost all of the prototypes casting results, to leave typescript infer it at the last possible time.

This allows me to use the factory like this:

Note that this isn't perfect as you loose some of the type strictness within the crudFactory code itself.

I hope this help you tough.

[eybel]

@avallete Thank you for taking the time. I will go over your code see whats the best way of achieving this. However I would like to share my final solution see if this is a "professional" way of doing it.

Is there something you wouldnt do in this code or suggest to change?? (beside the ID type and the tableName type. I know I may need to fix that)

TYPES:

interface EntityTypeMap {
  pet: Tables<"pet">;
  object: Tables<"object">;
  service: Tables<"service">;
  article: Tables<"article">;
}

type EntityNames = keyof EntityTypeMap; // "pet" | "object" | "service" | "article"

export type PartialRecordWithMultimedia<
  T extends EntityNames | undefined = undefined
> = T extends EntityNames
  ? Partial<EntityTypeMap[T]> & { multimedia?: Tables<MultimediaTables>[] }
  : Partial<Record<keyof EntityTypeMap, unknown>> & {
      multimedia?: Tables<MultimediaTables>[];
    };

export type Entities = "pet" | "object" | "service" | "article";
export type MultimediaTables = `${Entities}_multimedia`;
export type TableNames = Entities | MultimediaTables;

API CODE:

import { useQuery, useMutation, useQueryClient } from "@tanstack/react-query";
import { supabase } from "@/lib/supabaseClient";
import { Entities, MultimediaTables, PartialRecordWithMultimedia } from "@/types/generalTypes";
import { Tables, TablesInsert, TablesUpdate } from "@/types/database.types";
import { ObjectFormConfig } from "@/config/objectsFormConfig";
import { ServiceFormConfig } from "@/config/servicesFormConfig";
import { ArticleFormConfig } from "@/config/articlesFormConfig";
import { PetFormConfig } from "@/config/petFormConfig";

export const entityFactory = (entityTable: Entities) => {
  const multimediaTable = `${entityTable}_multimedia` as MultimediaTables;

  const entityConfigs = (entityTable: Entities) => {
    if (!entityTable) {
      throw new Error("entityConfigs > Entity type must be defined.");
    }
    switch (entityTable) {
      case "pet":
        return {
          singular: "Mascota",
          plural: "Mascotas",
          formConfig: PetFormConfig,
        };
      case "object":
        return {
          singular: "Objecto",
          plural: "Objectos",
          formConfig: ObjectFormConfig,
        };
      case "service":
        return {
          singular: "Servicios",
          plural: "Servicios",
          formConfig: ServiceFormConfig,
        };
      case "article":
        return {
          singular: "Articulo",
          plural: "Articulos",
          formConfig: ArticleFormConfig,
        };
      default:
        throw new Error(`Entity "${entityTable}" is not defined.`);
    }
  };

  const fetchAllFn = async ({
    selectedFields = "*",
    filterBy = {},
    startRange = 0,
    endRange = 10,
  }: {
    selectedFields?: (keyof Tables<Entities>)[] | "*";
    filterBy?: Record<string, any>;
    startRange?: number;
    endRange?: number;
  }): Promise<PartialRecordWithMultimedia[]> => {
    const fields =
      selectedFields === "*" ? "*" : selectedFields.join(", ") as keyof Tables<Entities>;

    let query = supabase
      .from(entityTable)
      .select(`${fields}, multimedia:${multimediaTable}(*)`)
      .range(startRange, endRange);

    Object.entries(filterBy).forEach(([key, value]) => {
      query = query.eq(key, value);
    });

    const { data, error } = await query;
    if (error) throw error;

    return (data ?? []).map((item) => ({
      ...item,
      multimedia: item?.multimedia || [],
    })) as PartialRecordWithMultimedia[];
  };

  const fetchSingleFn = async (id: number): Promise<PartialRecordWithMultimedia> => {
    const { data, error } = await supabase
      .from(entityTable)
      .select(`*, multimedia:${multimediaTable}(*)`)
      .eq("id", id)
      .is("deleted_at", null)
      .single();
    if (error) throw error;

    return { ...data, multimedia: data?.multimedia || [] };
  };

  const createFn = async (payload: TablesInsert<Entities>): Promise<Tables<Entities>> => {
    const { data, error } = await supabase
      .from(entityTable)
      .insert(payload)
      .select()
      .single();
    if (error || !data) throw new Error(error?.message || "Failed to create");
    return data;
  };

  const updateFn = async (id: number, payload: TablesUpdate<Entities>): Promise<Tables<Entities>> => {
    const { data, error } = await supabase
      .from(entityTable)
      .update(payload)
      .eq("id", id)
      .select()
      .single();
    if (error || !data) throw new Error(error?.message || "Failed to update");
    return data;
  };

  const deleteFn = async (id: number): Promise<boolean> => {
    const { error } = await supabase.from(entityTable).delete().eq("id", id);
    if (error) throw new Error(error.message);
    return true;
  };

  // Multimedia Functions
  const fetchAllMultimediaFn = async (postId: number): Promise<Tables<MultimediaTables>[]> => {
    const { data, error } = await supabase
      .from(multimediaTable)
      .select("*")
      .eq("post_id", postId);

    if (error) throw error;
    return data ?? [];
  };

  const createMultimediaFn = async (
    payload: TablesInsert<MultimediaTables>
  ): Promise<Tables<MultimediaTables>> => {
    const { data, error } = await supabase
      .from(multimediaTable)
      .insert(payload)
      .select()
      .single();

    if (error || !data) throw new Error(error?.message || "Failed to create multimedia");
    return data;
  };

  const deleteMultimediaFn = async (id: number): Promise<boolean> => {
    const { error } = await supabase.from(multimediaTable).delete().eq("id", id);
    if (error) throw new Error(error.message);
    return true;
  };

  // Hooks
  const useFetchAll = ({
    selectedFields = "*",
    filterBy = {},
    startRange = 0,
    endRange = 20,
  }: {
    selectedFields?: (keyof Tables<Entities>)[] | "*";
    filterBy?: Record<string, any>;
    startRange?: number;
    endRange?: number;
  }) =>
    useQuery({
      queryKey: [entityTable, "fetchAll", selectedFields, startRange, endRange],
      queryFn: () => fetchAllFn({ selectedFields, filterBy, startRange, endRange }),
      staleTime: 0,
    });

  const useFetchSingle = (id: number) =>
    useQuery({
      queryKey: [entityTable, "fetchSingle", id],
      queryFn: () => fetchSingleFn(id),
      enabled: !!id,
      staleTime: 0,
    });

  const useCreate = () => {
    const queryClient = useQueryClient();
    return useMutation({
      mutationFn: createFn,
      onSuccess: () => {
        queryClient.invalidateQueries({ queryKey: [entityTable, "fetchAll"] });
      },
    });
  };

  const useUpdate = () => {
    const queryClient = useQueryClient();
    return useMutation({
      mutationFn: ({ id, payload }: { id: number; payload: TablesUpdate<Entities> }) =>
        updateFn(id, payload),
      onSuccess: (_, { id }) => {
        queryClient.invalidateQueries({ queryKey: [entityTable, "fetchSingle", id] });
        queryClient.invalidateQueries({ queryKey: [entityTable, "fetchAll"] });
      },
    });
  };

  const useDelete = () => {
    const queryClient = useQueryClient();
    return useMutation({
      mutationFn: deleteFn,
      onSuccess: () => {
        queryClient.invalidateQueries({ queryKey: [entityTable, "fetchAll"] });
      },
    });
  };

  const useFetchMultimedia = (postId: number) =>
    useQuery({
      queryKey: [multimediaTable, "fetchAll", postId],
      queryFn: () => fetchAllMultimediaFn(postId),
      enabled: !!postId,
      staleTime: 0,
    });

  const useCreateMultimedia = () => {
    const queryClient = useQueryClient();
    return useMutation({
      mutationFn: (payload: TablesInsert<MultimediaTables>) => createMultimediaFn(payload),
      onSuccess: (_, payload) => {
        const postId = payload?.post_id; // Get the post_id from the first payload item
        if (postId) {
          queryClient.invalidateQueries({ queryKey: [multimediaTable, "fetchAll", postId] });
        }
      },
    });
  };

  const useDeleteMultimedia = () => {
    const queryClient = useQueryClient();
    return useMutation({
      mutationFn: deleteMultimediaFn,
      onSuccess: (_, id) => {
        queryClient.invalidateQueries({ queryKey: [multimediaTable, "fetchAll"] });
      },
    });
  };

  // Return Object
  return {
    formConfig: entityConfigs(entityTable)?.formConfig,
    singular: entityConfigs(entityTable)?.singular,
    plural: entityConfigs(entityTable)?.plural,
    useFetchAll,
    useFetchSingle,
    useCreate,
    useUpdate,
    useDelete,
    useFetchMultimedia,
    useCreateMultimedia,
    useDeleteMultimedia,
  };
};

[avallete]

Hey there !

Sorry for the delay, I went trough your code and it look good to me and if it work for you then it's good 👍

Happy you found a solution that suite you and thank's for sharing it with the community.

Closing the issue.