Inner Join Types Do Not Work Correctly

[jdgamble555]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

A clear and concise description of what the bug is.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

I made a simple repository. You can run npx supabase start, but it is not necessary. Just open the file in VS Code and see the problems.

Here is the Test Types

Here is the Schema

Or if you want to manually test it:

CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE posts (
    post_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE comments (
    comment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id UUID NOT NULL REFERENCES posts(post_id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Expected behavior

A clear and concise description of what you expected to happen.

I want to do an inner join and get the correct types.

// DOES NOT WORK, is empty array
const test1 = await supabase.from('posts').select('*, user:user_id(*)')

// DOES NOT WORK, is possibly null
const test2 = await supabase.from('posts').select('*, user:users(*)');

// DOES NOT WORK, is array instead of single
const test3 = await supabase.from('posts').select('*, user:users!user_id(*)');

What I want

const test1: PostgrestSingleResponse<{
    content: string;
    created_at: string;
    post_id: string;
    title: string;
    updated_at: string | null;
    user_id: string;
    user: {
        created_at: string;
        email: string;
        password_hash: string;
        user_id: string;
        username: string;
    };
}[]>

Test 1 Returns

const test1: PostgrestSingleResponse<{
    content: string;
    created_at: string;
    post_id: string;
    title: string;
    updated_at: string | null;
    user_id: string;
    user: {}[];
}[]>

Test 2 Returns

const test2: PostgrestSingleResponse<{
    content: string;
    created_at: string;
    post_id: string;
    title: string;
    updated_at: string | null;
    user_id: string;
    user: {
        created_at: string;
        email: string;
        password_hash: string;
        user_id: string;
        username: string;
    } | null;
}[]>

Test 3 Returns

const test3: PostgrestSingleResponse<{
    content: string;
    created_at: string;
    post_id: string;
    title: string;
    updated_at: string | null;
    user_id: string;
    user: {
        created_at: string;
        email: string;
        password_hash: string;
        user_id: string;
        username: string;
    }[];
}[]>

I really just need test to return the correct typing. It is clearly a required FK with only one option, so it shouldn't be null nor an array.

System information

• Supabase-js: 2.43.2
• Supabase: 1.167.4

Additional context

I have looked at these related posts, but none quite state the same problem clearly:

• Apply "single()" to foreign keys that only one match can be achieved. Automatically or Manually as option. #223
• Database typings are not correct on table join for entity to one relationship #408 - Seems to be related though
• [supabase-js v2] Improve Types Implementation for Nested Queries #303

J

[zineanteoh]

facing the same issue, but changing the type of id of users table from uuid to text works for me.

is there a way to have the inner join query work for uuid?

[soedirgo]

Hey @jdgamble555, the right query for this is:

await supabase.from('posts').select('*, user:users!inner(*)');

This should make user non-nullable in the response type.

Querying embedded tables through columns (user:user_id(*)) and using fkey columns as hints (user:users!user_id(*)) currently has no typings support in the client lib, which explains why you're not getting the response types you expected.

[soedirgo]

@zineanteoh let me know if that works for you too! We have an example on our docs here but we might need to make it more front and center.

[jdgamble555]

@soedirgo - Thanks, that solved the problem!

The docs show how to query, but as you can see above there is more than one way to query the results. Any query that works should also have correct typing. This would definitely stream line GH issues on the subject and confusion on possible queries.

J

[soedirgo]

👍 thanks for the feedback - this should get better as we get stricter with the typings, e.g. showing a type error instead of falling back to {} or T[].

[daveycodez]

Need to address having MULTIPLE references to users. Like a Direct Message which can have user_id AND recipient_id plz