[supabase-js v2] Improve Types Implementation for Nested Queries

[kryptovergleichde]

Feature request

Is your feature request related to a problem? Please describe.

Hi there, I tried implementing supabase-js v2, and first was happy to see the type generation out of select queries. But the real issues I have are still not solved with the new approach, these are nested tables types.

// I saw this example on the new docs: https://supabase.com/docs/reference/javascript/next/typescript-support.
import supabase from '~/lib/supabase'
import type { Database } from '~/lib/database.types'

async function getMovies() {
  return await supabase.from('movies').select('id, title, actors(*)')
}

type actors = Database['public']['Tables']['actors']['Row']
type MoviesResponse = Awaited<ReturnType<typeof getMovies>>
type MoviesResponseSuccess = MoviesResponse['data'] & {
  actors: actors[]
}

So let's imagine you have this data structure id, title, actors(name, birth_date), typescript will catch the id and title, of the movie, but not the name and birth_date of the nested relation. If I am now going to add this info manually like this:

// ...

async function getMovies() {
  return await supabase.from('movies').select('id, title, actors(name, birth_date)')
}

type actors = Database['public']['Tables']['actors']['Row']
type MoviesResponse = Awaited<ReturnType<typeof getMovies>>
type MoviesResponseSuccess = MoviesResponse['data'] & {
  actors: Pick<actors, 'name' | 'birth_date'>[]
}

there will be a lot of redunancy in the implementation. Since we have to define the table cols twice.

Describe the solution you'd like

Of course it would be perfect if nested relations would work out of the box. I see that this would be very difficult to do, and that's maybe why you didn't.

I would essentially suggest to export this type, to create something like this:

// (!) Pseudocode
// ...

import { ResolveRelationQuery } from '@supabase/supabase-js'

const actorsRelation = 'actors(name, birth_date)'
async function getMovies() {
  return await supabase.from('movies').select(`id, title, ${actorsRelation}`)
}

type MoviesResponse = Awaited<ReturnType<typeof getMovies>>
type MoviesResponseSuccess = MoviesResponse['data'] & {
  actors: ResolveRelationQuery<typeof actorsRelation>[]
}

Describe alternatives you've considered

I peeked into the type definitions of the new supabase-js v2 library, and saw a GetResult type coming from @supabase/postgrest-js/dist/module/select-query-parser.

I have implemented something with this, which technically works, but is not very developer friendly since everyone have to define lots of types in the repo. See https://github.com/supabase/postgrest-js/issues/303

[kryptovergleichde]

So I successfully created some Types, which could be implemented by supabase-js.

What works:

• it infers the table names and column names completely automatic, so no duplicate definitions.
• table(*) works
• It even errors if for example you do define the relation string in a wrong format. Correct is table(col, col), but if you do this with a table that doesn't exist or you do it with a wrong syntax like table[col|col] it will error.
• Determined by the second argument of ResolveRelationQuery, you can change the relation type between one (which leads to an object) and many (which leads to an array of objects). This is unfortunately impossible to detect via the types, as it is dependent on the database config (as far as I know)

What currently not works:

• currently, only the table(col, col) format is supported. Missing support for
  • table ( col, col ) (multiline)
  • supplier:supplier_id ( name )
  • users!inner(*)

So these types would need some fine-tuning for production.

You can try it out in any project: First implement this somewhere globally:

// lib/supabase.ts
import { createClient } from '@supabase/supabase-js'
import type { GetResult } from '@supabase/postgrest-js/dist/module/select-query-parser'
import type { Database } from '@/lib/database.types'

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL as string
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY as string

export const supabase = createClient<Database>(supabaseUrl, supabaseAnonKey)

type Tables = Database['public']['Tables']
type SplitRelStr_SuccessProps<T = keyof Tables, Q = string> = { table: T; query: Q }
type SplitRelStr<RelStr extends string> = RelStr extends `${infer T}(${infer Q})`
  ? T extends keyof Tables
    ? SplitRelStr_SuccessProps<T, Q>
    : { error: 'Parsed `RelationString`, but the table name does not exist.' }
  : { error: 'Cannot parse `RelationString`' }
type GetResolvedResult<SP extends SplitRelStr_SuccessProps> = GetResult<Tables[SP['table']]['Row'], SP['query']>

/**
 * @template RelStr Supabase relation string, e.g. `tablename(colum, another_colum)`.
 * @template RelType The type of relation. e.g. `one` or `many`. This decides if the returned relation is an array of objects/single object.
 */
export type ResolveRelationQuery<
  RelStr extends string,
  RelType extends 'one' | 'many' = 'many'
> = SplitRelStr<RelStr> extends SplitRelStr_SuccessProps
  ? {
      [K in SplitRelStr<RelStr>['table']]: RelType extends 'one'
        ? GetResolvedResult<SplitRelStr<RelStr>> | null
        : GetResolvedResult<SplitRelStr<RelStr>>[]
    }
  : { error: SplitRelStr<RelStr>['error'] }

/**
 * @template R { data } response you get from `const { data, error } = await supabase.from('tablename').select(...)`
 * @template RelObj Object that contains the table name and the resolved relation.
 */
export type ResolveResponse<R extends any[], RelObj extends object> = (R[0] & RelObj)[]

And then when you want to use it:

import { supabase, ResolveRelationQuery, ResolveResponse } from '@/lib/supabase'

const loadData = async () => {
  const actorsRelation = `actors(name, birth_date)`
  const companiesRelation = `companies(*)`

  const { data, error } = await supabase
    .from('movies')
    .select(`
      name, id,
      ${actorsRelation},
      ${companiesRelation}
    `)
 
  if (!data?.length || error) return

  type Actors = ResolveRelationQuery<typeof actorsRelation, 'many'>
  type Companies = ResolveRelationQuery<typeof companiesRelation, 'many'>
  type Response = ResolveResponse<typeof loadData, Actors & Companies>

  return data as Response
}

const main = async () => {
  const data = await loadData()

  data?.map(entry => {
    console.log(entry.id)
    console.log(entry.actors) // -> now has resolved type, object or array of objects looking like this: `{ name: string | null, birth_date: string | null }`
  })
}

main()

[lorenzodejong]

I think it's very much needed to find a solution for this, especially since querying foreign tables this way is documented as the recommended solution (https://supabase.com/docs/reference/javascript/select#query-foreign-tables).

Perhaps some insights could be gained from the way Prisma has implemented this. They have solved nested querying in their type resolving, you can simply run a query similar to the one below. This will return a type including all nested relations.

await prisma.movies.findMany({
    select: {
        name: true,
        id: true,
        actors: {
            name: true,
            birth_date: true
        },
        companies: true
    }
});

[soedirgo]

Thanks for looking into this! Yes, the nested types is intentionally omitted for now - the difficulty here is determining the cardinality of the nested tables, e.g. using the first snippet actors can either be { name, birth_date } or { name, birth_date }[] depending on how the foreign key relationships look like.

[kryptovergleichde]

@soedirgo Thanks for your answer, yes. Do you know if there is a possibility to get this information about the foreign key relationship out of the data returned by supabase-cli? Otherwise I think there has to be a manual way to define if it's a one or many relation, like I did in my examples.

[soedirgo]

Yup, we're looking into this right now (PostgREST itself definitely does this) - ideally the typegen should output this information and postgrest-js would handle the disambiguation OOtB.

[johanneskares]

I'm doing this temporary hack until it gets solved from supabase-js. Any update on when this will be done automatically?

const { data, error } = await supabase.from("items")
    .select(`*, talent:talent_id( username, avatar )`,)
    .eq("content_id", "1");

if (error) {
    return null
}

// TODO: In future versions of supabase-js this should no longer be necessary
type supabaseType = typeof data & { 
    talent: Pick<Database['public']['Tables']['users']['Row'], "username" | "avatar">
}[]
const supabaseData =  data as supabaseType

const username = supabaseData[0].talent.username

[johanneskares]

The recent release from this PR https://github.com/supabase/postgrest-js/pull/345 broke my workaround, mentioned above.

Additionally, instead of always returning unknown for all nested relations, we instead return T | T[]

I guess it would be better to change this behavior once the feature is complete instead of forcing us to come up with new workarounds along the way ;)

[cdedreuille]

For someone relatively new to Typescript, managing types for nested tables is quite painful. I hope this could be resolved at some point.

I ended up doing this which I'm pretty sure is quite a bad solution:

type Product = Database["public"]["Tables"]["products"]["Row"];
interface ProductFull extends Omit<Product, "vendor"> {
  vendor: Database["public"]["Tables"]["vendors"]["Row"];
}

const Products: FC = () => {
  const [products, setProducts] = useState<ProductFull[]>([]);
  const supabaseClient = useSupabaseClient<Database>();

  useEffect(() => {
    async function getProducts(): Promise<PostgrestResponse<ProductFull>> {
      return await supabaseClient.from("products").select("*, vendor(*)");
    }

    async function loadData() {
      const { data } = await getProducts();
      if (data) setProducts(data);
    }
    loadData();
  }, [supabaseClient]);

  return <></>
}

[samuba]

I came up with a workaround with less moving parts, inspired by @johanneskares workaround :

const { data, error } = await supabase.from("items")
    .select(`*, talent:talent_id( username, avatar )`,)
    .eq("content_id", "1");

// TODO: workaround until this is implemented: https://github.com/supabase/postgrest-js/issues/303
type ResultRow = ArrayElement<typeof data>;
type PatchedResult = Modify<ResultRow, {
	talent: ArrayElement<ResultRow['talent']>;
}>[];

const username = (data as PatchedResult)[0].talent.username

Can also easily be adjusted to have talent as an array:

type PatchedResult = Modify<ResultRow, {
	talent: ArrayElement<ResultRow['talent']>[];
}>[];

It uses the custom ArrayElement and Modify types which can come in handy in all sorts of Situations:

export type ArrayElement<T> = T extends readonly unknown[] ? T[0] : never;
export type Modify<T, R> = Omit<T, keyof R> & R;

[Murkrage]

Thanks for the workaround @samuba! However it seems type-fest does not include those types? https://www.npmjs.com/package/type-fest

[samuba]

Whoopsie, thanks for pointing it out. 😅 I corrected my answer

[pybuche]

Hello 👋 Having the same problem with joins, whatever you do, join results always appear to be T | T[] | null.

I used the .returns<MainEntity & { relationship: Relationship }>() helper introduced in #345 but I'm really wondering if we couldn't find a way to easily infer this information.

Let's say we have 2 tables:

• movies (id, name, category_id)
• categories (id, name)

One to One

If category cannot be NULL, then:

• supabase.from("movies").select("*, category:categories(*)") cannot return any other type than Movie & { category: Category }, right?

If category is NULL, then:

• supabase.from("movies").select("*, category:categories(*)") could return something like Movie & { category?: Category }, right?

One to Many

If category cannot be NULL, then:

• supabase.from("categories").select("*, movies(*)") cannot return any other type than Category & { movies: Movie[] }, right?

Many to Many

I don't even know how it's handled honestly, but I suppose we can have the same reasoning

I'm not a huge Typescript expert, but I would gladly have a look if you think that's worth it.

[activenode]

I think it's very much needed to find a solution for this, especially since querying foreign tables this way is documented as the recommended solution (https://supabase.com/docs/reference/javascript/select#query-foreign-tables).

Perhaps some insights could be gained from the way Prisma has implemented this. They have solved nested querying in their type resolving, you can simply run a query similar to the one below. This will return a type including all nested relations.

await prisma.movies.findMany({
    select: {
        name: true,
        id: true,
        actors: {
            name: true,
            birth_date: true
        },
        companies: true
    }
});

What I don't get is anyway the hyper complex resolution of the string. From a personal perspective: I love it because it shows that you can parse strings with TypeScript.

However: I asked myself if this isn't making things overly complicated? I'm not saying one should get rid of the current solution - at all.

I'm just saying: Have you thought about making this simpler? E.g. providing a few more helpers rather than overriding via returns<> which I personally find cumbersome because it's basically type casting.

I don't want typecasting though but I'd love to give it more hints what the foreign tables are - if needed.

I also like the object-based solution or an array-based solution. But at the end of the day it's probably just missing some recursion here and there.

[gluharry]

+1 This feature is needed in order to use typescript proper. Great work on everything else!

[kryptovergleichde]

@harryyaprakov Absolutely. And I don't know why this is open for song long. Since this is a feature other DB clients offer by default, and is actually the reason you use typescript, to have inheriance and don't think about constructuing complex types properly. Same when you use GraphQL queries, you want the response types of them automatically generated.

Always having to think about to not only write the query but also construct types (which leads to huge duplications in code for complex queries actually was one of th main reasons which made me move away from using supabase-js and going all-in with prisma + trpc. Can only recommend it, as your application grows and you have more demand for complex queries.

[amorfati254]

+1

[Murkrage]

@harryyaprakov Absolutely. And I don't know why this is open for song long. Since this is a feature other DB clients offer by default, and is actually the reason you use typescript, to have inheriance and don't think about constructuing complex types properly. Same when you use GraphQL queries, you want the response types of them automatically generated.

Always having to think about to not only write the query but also construct types (which leads to huge duplications in code for complex queries actually was one of th main reasons which made me move away from using supabase-js and going all-in with prisma + trpc. Can only recommend it, as your application grows and you have more demand for complex queries.

How was the migration to prisma? I'm considering it but it seems like it's a pretty involved thing to do.

[kryptovergleichde]

@Murkrage First it was a bit difficult to set it up to use the right postgresql connection string for DB access and migrations.

Came out to be:

DATABASE_URL="postgres://postgres:[email protected]_ID.supabase.co:6543/postgres?schema=public&pgbouncer=true&sslmode=require&connection_limit=1&trustServerCertificate=true"
MIGRATION_DATABASE_URL="postgres://postgres:[email protected]_ID.supabase.co:5432/postgres"

Also, when using it with Prisma, just forget about RLS. It's somehow possible, but super flimsy.

Other than that, once you've got the hang of it, you never want to go back doing DB requests on the client. But yeah, for an existing project, it would probably be pretty much work.

Be aware that you'll loose certain supabase features such as real-time, though maybe you can implement that server side too, haven't tried yet.

I'm still using supabase-js, but only for auth.

[gabrielsestrem]

We need a solution for this urgently. I upgraded my project to supabase-js V2, but it's impossible to build currently. We have hundreds of nested queries, impossible to use any kind of workaround... Do you guys know if there's a way to disable this type generation out of select queries?

[mihaiandrei97]

@Murkrage First it was a bit difficult to set it up to use the right postgresql connection string for DB access and migrations.

Came out to be:

DATABASE_URL="postgres://postgres:[email protected]_ID.supabase.co:6543/postgres?schema=public&pgbouncer=true&sslmode=require&connection_limit=1&trustServerCertificate=true"
MIGRATION_DATABASE_URL="postgres://postgres:[email protected]_ID.supabase.co:5432/postgres"

Also, when using it with Prisma, just forget about RLS. It's somehow possible, but super flimsy.

Other than that, once you've got the hang of it, you never want to go back doing DB requests on the client. But yeah, for an existing project, it would probably be pretty much work.

Be aware that you'll loose certain supabase features such as real-time, though maybe you can implement that server side too, haven't tried yet.

I'm still using supabase-js, but only for auth.

@kryptovergleichde are you using the shadowDb for migrations? I didn't manage to make it work

[thorwebdev]

Hey folks, thanks for all the feedback here! Just a quick note that I'm going to hide the "Migrate to Prisma" related comments as off topic to allow us to focus on the issue at hand, I hope you understand. To further discuss that part I'd recommend you utilise GitHub Discussions.

Do note that we're actively working on this and will follow up with more details soon. Thank you 💚

@gabrielsestrem regarding

Do you guys know if there's a way to disable this type generation out of select queries?

Would you be able to use https://supabase.com/docs/reference/javascript/db-returns for now?

[Murkrage]

@thorwebdev I wasn't aware of this method, thanks for the heads up! It's still a bandaid but at least we can get some type safety out of it without having to do type guards :)

[gabrielsestrem]

Thanks @thorwebdev I wasn't aware either. This .returns<MyType>() will save us for now.

[soedirgo]

Hey all, sorry this issue has been open for so long - we're aware this is one of the major pain points when using supabase-js. The crux of the matter here is a foreign table can be either T | null or T[] depending on the cardinality.

While we work on the proper solution for this, one bandaid I'd like to propose is to do what's suggested here, which is to default to T[], since preserving type correctness using T[] | T | null seems to be more trouble that it's worth. For cases where you need T | null, you can use .returns<MyType>(). What are your thoughts?

Also, the suggestion to move away from stringly-typed queries is noted - we're planning on using a more structured query format for the next major version of the library.

[emmbm]

While .returns<>() can help circumvent the current flaws stemming from the unknown cardinality of tables' relationships, it throws out any typing already inferred by the query's string and requires us to redeclare everything form scratch while, in many cases, all that is needed is a little patching of the already provided type.

Could we maybe get a way to derive the type instead of redeclaring everything form scratch? For illustration, see: https://github.com/supabase/supabase/discussions/12238. It's really not an ideal implementation, probably has many flaws as it stands, and I'm not claiming its should be taken as-is, but I feel this way of working could prove more relevant for many cases some people seem to face.

[gabrielsestrem]

For me, the feature should handle all the scenarios. Sometimes we do big queries with many nested tables. Ideally I'd love to have all properties with types even if it's in a 3rd or 4th nested table. Upgrading to supabasejs-v2 it's changing already 150+ files and we are using .returns<>() when needed and sometimes using this approach here

Just to have an idea. This is one example of some sort of complex query:

        .from('reverse_orders')
        .select(`id, organisation_id, order_id, collect_requested, invoices, client_tracking_option, retention_value, retention_type, retention_fee_value,
        client: client_id(
            name,
            email,
            phone,
            document),
        address: address_id(
            country,
            state,
            city,
            neighborhood,
            street,
            number,
            description,
            zipcode),
        payment_bill: payment_bill_id(
            id,
            is_pix,
            pix_type,
            pix_key,
            bank_code,
            bank_agency,
            bank_account,
            document,
            bank_account_type),
        analyzed_by(
            id,
            username,
            email),
        reverse_tracking: reverse_tracking_id(
            id,
            status,
            courier_company,
            posted_date,
            courier_tracking_code,
            courier_collect_number,
            delivery_date,
            expected_delivery_date,
            shipping_company: shipping_company_id(name),
            customer_posting,
            posting_address,
            price,
            locker),
        status: status_id(
            id,
            name),
        coupon: reverse_coupon_id(
            id,
            code,
            description,
            coupon_ecommerce_id,
            validity,
            value,
            active,
            was_used),
        payment: reverse_payment_id(
            id,
            value,
            action,
            created_at,
            payment_document),
        organisation: organisation_id(
            ecommerce_provider(
                id,
                name,
                order_url,
                refund_integration),
            organisation_coupon_config(
                code_prefix,
                predefined_code,
                deadline_days,
                predefined_deadline,
                sufix_code_random,
                sufix_code),
            organisation_rules(
                refund_integration,
                alias_not_allowed_refund_integration),
            subdomain,
            website),
        order: order_id(
            id,
            items,
            discount,
            shipping_cost,
            total_price,
            channel_id,
            payment_code,
            is_bank_transfer,
            sales_invoices),
        reverse_items!reverse_items_reverse_order_id_fkey(
            id,
            hash,
            sku,
            description,
            image_url,
            quantity,
            price,
            is_exchange,
            reason,
            id_item,
            is_removed,
            received,
            retained),
        reverse_items_replaced(
            id,
            description,
            quantity,
            sku),
        history:reverse_order_history(
            id,
            created_by(
                email
            ),
            action(
                id,
                description
            ),
            details,
            created_at),
            ecommerce_number, created_at, price, refund_value, exchange_value, is_exception, second_reverse, is_complete, second_request`)
        .eq('id', reverseOrderId)
        .eq('organisation_id', organisationId)
        .returns<any>()```

And it makes sense because if I break quis query and many small ones, let's say: 5 ou 7 small queries. My API get's 5x slower 

[kinolaev]

Hello! I've found a workaround for this issue:

import { PostgrestFilterBuilder } from "@supabase/postgrest-js"

declare module "@supabase/postgrest-js" {
  class PostgrestFilterBuilder<Schema, Row, Result> {
    fixToMany<K>(): PostgrestFilterBuilder<Schema, Row, FixToMany<Result, K>>
    fixToOne<K>(): PostgrestFilterBuilder<Schema, Row, FixToOne<Result, K>>
    fixToMaybeOne<K>(): PostgrestFilterBuilder<
      Schema,
      Row,
      FixToMaybeOne<Result, K>
    >
  }
}

type FixToMany<Result, K> = Result extends Array<infer T>
  ? Array<FixToManyItem<T, K>>
  : Result extends infer T | null
  ? FixToManyItem<T, K> | null
  : FixToManyItem<Result, K>

type FixToManyItem<T, Key> = Key extends keyof T
  ? Omit<T, Key> & { [K in Key]: Extract<T[K], Array<T[K]>> }
  : T

PostgrestFilterBuilder.prototype.fixToMany = function fixToMany() {
  return this
}

type FixToOne<Result, K> = Result extends Array<infer T>
  ? Array<FixToOneItem<T, K>>
  : Result extends infer T | null
  ? FixToOneItem<T, K> | null
  : FixToOneItem<Result, K>

type FixToOneItem<T, Key> = Key extends keyof T
  ? Omit<T, Key> & { [K in Key]: Exclude<T[K], Array<T[K]> | null> }
  : T

PostgrestFilterBuilder.prototype.fixToOne = function fixToOne() {
  return this
}

type FixToMaybeOne<Result, K> = Result extends Array<infer T>
  ? Array<FixToMaybeOneItem<T, K>>
  : Result extends infer T | null
  ? FixToMaybeOneItem<T, K> | null
  : FixToMaybeOneItem<Result, K>

type FixToMaybeOneItem<T, Key> = Key extends keyof T
  ? Omit<T, Key> & { [K in Key]: Exclude<T[K], Array<T[K]>> }
  : T

PostgrestFilterBuilder.prototype.fixToMaybeOne = function fixToMaybeOne() {
  return this
}

You can use it like this to get right return type:

supabase.from("books")
  .select("id, author(*), publisher:publishers(*), last_review(*), readers(*)")
  .fixToOne<"author" | "publisher">() // Omit<ResultItem, "author" | "publischer"> & { author: Database["public"]["Tables"]["users"]["Row"], publisher: Database["public"]["Tables"]["publishers"]["Row"] }
  .fixToMaybeOne<"last_review">() // Omit<ResultItem, "last_review"> & { last_review: Database["public"]["Tables"]["reviews"]["Row"] | null }
  .fixToMany<"readers">() // Omit<ResultItem, "readers"> & { readers: Array<Database["public"]["Tables"]["users"]["Row"]> }

Hope, it helps find a real solution to this problem!

[activenode]

Hello! I've found a workaround for this issue:

import { PostgrestFilterBuilder } from "@supabase/postgrest-js"

declare module "@supabase/postgrest-js" {
  class PostgrestFilterBuilder<Schema, Row, Result> {
    fixToMany<K>(): PostgrestFilterBuilder<Schema, Row, FixToMany<Result, K>>
    fixToOne<K>(): PostgrestFilterBuilder<Schema, Row, FixToOne<Result, K>>
    fixToMaybeOne<K>(): PostgrestFilterBuilder<
      Schema,
      Row,
      FixToMaybeOne<Result, K>
    >
  }
}

type FixToMany<Result, K> = Result extends Array<infer T>
  ? Array<FixToManyItem<T, K>>
  : Result extends infer T | null
  ? FixToManyItem<T, K> | null
  : FixToManyItem<Result, K>

type FixToManyItem<T, Key> = Key extends keyof T
  ? Omit<T, Key> & { [K in Key]: Extract<T[K], Array<T[K]>> }
  : T

PostgrestFilterBuilder.prototype.fixToMany = function fixToMany() {
  return this
}

type FixToOne<Result, K> = Result extends Array<infer T>
  ? Array<FixToOneItem<T, K>>
  : Result extends infer T | null
  ? FixToOneItem<T, K> | null
  : FixToOneItem<Result, K>

type FixToOneItem<T, Key> = Key extends keyof T
  ? Omit<T, Key> & { [K in Key]: Exclude<T[K], Array<T[K]> | null> }
  : T

PostgrestFilterBuilder.prototype.fixToOne = function fixToOne() {
  return this
}

type FixToMaybeOne<Result, K> = Result extends Array<infer T>
  ? Array<FixToMaybeOneItem<T, K>>
  : Result extends infer T | null
  ? FixToMaybeOneItem<T, K> | null
  : FixToMaybeOneItem<Result, K>

type FixToMaybeOneItem<T, Key> = Key extends keyof T
  ? Omit<T, Key> & { [K in Key]: Exclude<T[K], Array<T[K]>> }
  : T

PostgrestFilterBuilder.prototype.fixToMaybeOne = function fixToMaybeOne() {
  return this
}

You can use it like this to get right return type:

supabase.from("books")
  .select("id, author(*), publisher:publishers(*), last_review(*), readers(*)")
  .fixToOne<"author" | "publisher">() // Omit<ResultItem, "author" | "publischer"> & { author: Database["public"]["Tables"]["users"]["Row"], publisher: Database["public"]["Tables"]["publishers"]["Row"] }
  .fixToMaybeOne<"last_review">() // Omit<ResultItem, "last_review"> & { last_review: Database["public"]["Tables"]["reviews"]["Row"] | null }
  .fixToMany<"readers">() // Omit<ResultItem, "readers"> & { readers: Array<Database["public"]["Tables"]["users"]["Row"]> }

Hope, it helps find a real solution to this problem!

Neat idea man.

[johanneskares]

My current workaround:

export const getSingle = <T>(input: T[] | T | null) => {
    if (input === null) {
        throw new Error("Input is null");
    }

    if (Array.isArray(input)) {
        if (input.length > 0) {
            return input[0];
        } else {
            throw new Error("Array is empty");
        }
    }

    return input;
};

export const getMaybeSingle = <T>(input: T[] | T | null) => {
    if (Array.isArray(input)) {
        if (input.length > 0) {
            return input[0];
        } else {
            return null;
        }
    }

    return input;
};

export const getArray = <T>(input: T[] | T | null) => {
    if (input === null) {
        throw new Error("Input is null");
    }

    if (Array.isArray(input)) {
        return input;
    }

    return [input];
};

and then use it as

const { data } = await supabase.from("books")
  .select("id, author(*), publisher:publishers(*), last_review(*), readers(*)").single();

const book = {
    ...data,
    author: getSingle(books.author),
    last_review: getMaybeSingle(books.last_review),
    readers: getArray(books.readers)
}

If you want to use it on many items, I add an additional .map. It also works well with nested queries, using additional .maps. It has some runtime overhead, but that gives some extra safety. For me that's fine.

[samuel-bach]

Also, the suggestion to move away from stringly-typed queries is noted - we're planning on using a more structured query format for the next major version of the library.

@soedirgo this is great news! I was worried with the current approach and its scalability. I really want to use supabase also for complex operations. When you look into this I hope you get the right inspirations from queryBuilders that already do a good job of providing a typesafe powerful abstraction for postgres like e.g. drizzle. :-)