How to model relationships in Supabase?

 

Step 1: Setting Up Your Supabase Project

 

• Open your browser and navigate to Supabase.
• Sign in to your account or create a new account if you do not have one.
• Once logged in, click on "New Project" to create a new Supabase project.
• Fill in the required details such as project name, database password, and region, then click "Create new project".

 

Step 2: Designing the Database Schema

 

• Plan your database schema and determine the entities (tables) you need and the type of relationships (one-to-one, one-to-many, many-to-many) between them.

Example entities and relationships:

• Users and Profiles (One-to-One)
• Authors and Books (One-to-Many)
• Students and Courses (Many-to-Many)

 

Step 3: Creating Tables for Entities

 

• In your Supabase project dashboard, navigate to the "Table Editor" in the left-hand menu.
• Click on "New Table" to create a new table for each entity identified in your schema.

Example for creating a "Users" table:

CREATE TABLE public.users (
  id serial PRIMARY KEY,
  username text UNIQUE NOT NULL,
  email text UNIQUE NOT NULL
);

Example for creating a "Profiles" table:

CREATE TABLE public.profiles (
  id serial PRIMARY KEY,
  user_id integer REFERENCES public.users(id) UNIQUE,
  bio text
);

 

Step 4: Modeling One-to-One Relationships

 

• Create a unique foreign key constraint on the child table referencing the parent table.

Example for a one-to-one relationship between "Users" and "Profiles":

ALTER TABLE public.profiles
ADD CONSTRAINT fk_user
FOREIGN KEY(user_id) REFERENCES public.users(id) UNIQUE;

 

Step 5: Modeling One-to-Many Relationships

 

• In this relationship, add a foreign key in the "many" side table referencing the "one" side table.

Example for a one-to-many relationship between "Authors" and "Books":

• Create "Authors" and "Books" tables:
  ```
  CREATE TABLE public.authors (
  id serial PRIMARY KEY,
  name text NOT NULL
  );

CREATE TABLE public.books (
id serial PRIMARY KEY,
title text NOT NULL,
author_id integer REFERENCES public.authors(id)
);

 

**Step 6: Modeling Many-to-Many Relationships**

 

- For many-to-many relationships, use a junction table with foreign keys referencing each of the two tables to define the relationship.

Example for a many-to-many relationship between "Students" and "Courses":
- Create a "Students" table and a "Courses" table:

CREATE TABLE public.students (
id serial PRIMARY KEY,
name text NOT NULL
);

CREATE TABLE public.courses (
id serial PRIMARY KEY,
title text NOT NULL
);

- Create a junction table "Enrollments":

CREATE TABLE public.enrollments (
student_id integer REFERENCES public.students(id),
course_id integer REFERENCES public.courses(id),
PRIMARY KEY(student_id, course_id)
);

 

**Step 7: Using Supabase to Interact with the Database**

 

- Use Supabase client libraries (JavaScript, Python, etc.) to interact with the database.

JavaScript Example:

import { createClient } from '@supabase/supabase-js';

const supabaseUrl = 'your-supabase-url';
const supabaseKey = 'your-supabase-key';
const supabase = createClient(supabaseUrl, supabaseKey);

async function fetchUsers() {
const { data, error } = await supabase
.from('users')
.select('*');

if (error) console.log('Error fetching users:', error);
else console.log('Users:', data);
}

 

**Step 8: Testing the Relationships**

 

- Perform insertions to ensure that relationships are established correctly.

Insert Example:

-- Inserting a user
INSERT INTO public.users (username, email) VALUES ('john_doe', 'john@example.com');

-- Inserting a profile linked to the user
INSERT INTO public.profiles (user_id, bio) VALUES (1, 'Software Developer');

- Validate the relationships using SQL JOIN queries to fetch related data.

Example of a JOIN query:

SELECT users.username, profiles.bio
FROM public.users
JOIN public.profiles ON users.id = profiles.user_id;

 

This comprehensive tutorial illustrates the steps to model relationships in Supabase, ensuring each part of the process is meticulously explained.