Supabase: Mastering Foreign Keys & JOIN Queries

1. "Why is the User Field Null?"

I'm building a social feed. I want to fetch posts list, but I also need the author's (users) details like name and avatar.

await supabase.from('posts').select('*, users(*)');

Result:

{
  "id": "...",
  "title": "Hello World",
  "users": null // 👈 Why? The user_id is definitely there!
}

The database has the data. The user_id column contains a valid UUID. Why is the SDK returning null?

---

2. The Principle: PostgREST Logic

Supabase is built on top of PostgREST, which auto-generates API endpoints from your schema. For PostgREST to "See" a connection between two tables, a Foreign Key (FK) Constraint MUST exist in the database schema.

Naming a column user_id is not enough. You must enforce the relation in SQL level. PostgREST inspects the schema, sees the foreign key, and only then allows the embedding (joining) of resources.

---

3. Problem 1: Missing Foreign Key

You created a uuid column named user_id, but you treated it as a simple text/uuid field. You forgot to link it to public.users.id (or auth.users.id).

Solution:

1. Go to Supabase Dashboard -> Table Editor.
2. Open posts table.
3. Click the arrow next to user_id and select Edit Column.
4. Click Add Foreign Key Relation.
5. Select Schema: public (or auth), Table: users, Column: id.
6. Save.
7. Now Supabase guarantees the link, and select('*, users(*)') will work instantly.

---

4. Problem 2: Ambiguous Keys

This is a classic error. What if you have a messages table with TWO references to users?

• sender_id -> users.id
• receiver_id -> users.id

If you run .select('*, users(*)'), Supabase gets confused: "Which path should I take? Should I join via sender_id or receiver_id?" It fails or returns error Could not find a relationship between 'messages' and 'users' in the schema cache.

Solution: Specify the Foreign Key column explicitly using the ! syntax.

// "Fetch users as 'sender' using the 'sender_id' foreign key"
await supabase.from('messages').select('''
  *,
  sender:users!sender_id(*), 
  receiver:users!receiver_id(*)
''');

Note that sender: is an alias. The output JSON will have sender and receiver objects instead of a generic users array. This is much cleaner for the frontend too.

---

5. Deep Dive 1: Nested Joins (The Tree Structure)

Data is rarely flat. You want: Comments -> belonging to User -> who belongs to a Team. PostgREST allows infinite nesting. Use Parentheses () to nest.

await supabase.from('comments').select('''
  id,
  content,
  users (
    full_name,
    avatar_url,
    teams (
      name,
      mascot_url
    )
  )
''');

JSON Output:

{
  "content": "Nice post!",
  "users": {
    "full_name": "John",
    "teams": {
      "name": "Red Team"
    }
  }
}

---

6. Deep Dive 2: Count & Inner Join

Sometimes you don't need the data, just the metadata.

2.1 Fetching Counts

"Get me the post and the number of comments, not the comments themselves."

// 🌟 count mechanism
await supabase.from('posts').select('*, comments(count)');

Result: {"title": "...", "comments": [{"count": 42}]}.

2.2 Inner Join (Filtering Parent by Child)

"Get me only the posts that have at least one comment." By default, joins are LEFT JOIN (shows posts even if comments are null). Use !inner to enforce INNER JOIN.

// 🌟 !inner filters the parent based on child existence
await supabase.from('posts').select('*, comments!inner(*)');

This query will exclude any posts with zero comments.

---

7. Deep Dive 3: Many-to-Many (M:N)

The most complex relationship. Posts <-> Tags. One post has many tags, one tag has many posts. You need a Junction Table (e.g., post_tags).

Schema:

• posts (id)
• tags (id, name)
• post_tags (post_id, tag_id)

Query: PostgREST doesn't support implicit M:N flattening yet. You must go through the junction table.

// posts -> post_tags -> tags
await supabase.from('posts').select('''
  title,
  post_tags (
    tags (
      name
    )
  )
''');

Flattening (Client-side): The result is nested: post.post_tags[0].tags.name. You usually need a helper to map this to post.tags on the client side:

final tags = data['post_tags'].map((e) => e['tags']['name']).toList();

---

8. Deep Dive 4: Cascade Delete

One common architecture question is "What happens when I delete a user?". If you don't configure "Action on Delete", your delete operation will fail with Foreign Key Violation because posts are referencing the user.

Best Practice: Set Cascade on Delete.

• No Action (Default): Deletion fails if children exist. Good for critical data (e.g., Invoices).
• Cascade: Deleting User automatically deletes ALL their Posts. Good for hygiene.
• Set Null: Deleting User keeps Post, but user_id becomes null. Good if content needs to survive (e.g., "Deleted User").

---

9. Deep Dive 5: Performance Tuning (Indexing)

Did you know that Foreign Keys are NOT indexed by default in Postgres? Most people assume they are. If you frequently query select * from posts where user_id = '...', and you have millions of posts, this will be a Sequential Scan (Slow).

Solution: Always create an Index on your Foreign Key columns.

CREATE INDEX idx_posts_user_id ON posts (user_id);

Supabase Studio -> SQL Editor -> Run this query. This changes the complexity from O(N) to O(logN). Your queries will be 100x faster.

---

10. Deep Dive Glossary

• Foreign Key (FK): A constraint that guarantees a column's value matches a Primary Key in another table. It prevents "Orphaned Data".
• Primary Key (PK): A unique identifier for a row. Supabase usually uses uuid or int8 (bigint).
• Junction Table (Pivot Table): A bridge table used for Many-to-Many relationships. Contains two FKs.
• N+1 Problem: Fetching related data in a loop (getting posts, then looping to get user for each). Supabase/PostgREST solves this by fetching everything in a single SQL query, saving tremendous network overhead.
• !inner: A directive in PostgREST that changes a LEFT JOIN (default) to an INNER JOIN, effective for filtering parent rows based on child conditions.
• Alias: Renaming a table in the response (e.g., sender:users!sender_id).
• Sequential Scan: The DB reading every single row to find a match. The enemy of performance.
• Index Scan: Using a B-Tree index to jump directly to the data. The goal of performance.

---

11. Summary

No FK, No Join. Define relations explicitly in DB.

1. Missing Data? Check if FK exists in Table Editor.
2. Ambiguous Error? Use !fk_column_name.
3. Need Filtering? Use !inner.
4. M:N? Query through the junction table.
5. Slow Query? Add an Index on the FK column.

Supabase makes SQL joins feel like graph queries. Master the syntax, and you can fetch your entire screen's data in a single request.