🖥️ Working with Supabase

Modifying the database schema

Supabase files

Supabase files can be found in root /supabase

Configuration file for the local Supabase setup. /supabase/config.toml

Modifying the Database Schema

Most common task I found doing is modifying the schema and generating types.

1. Create a new migration with command

pnpm run supabase:generate-migration name_of_migration

This generates a migration file inside directory. /supabase/migrations/

2. Modify newly created migration

Let's say you wanted to create a new table called ai_models. Locate and edit the new file in directory e.g. 20240501120000_name_of_migration.sql

-- 20240501120000_name_of_migration.sql

CREATE TABLE ai_models (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    provider TEXT NOT NULL,
    user_id UUID NOT NULL REFERENCES auth.users
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now()
);

-- Enable Row Level Security
ALTER TABLE ai_models ENABLE ROW LEVEL SECURITY;

-- Create a policy for select (read) operations
CREATE POLICY "Allow read access for all users" ON ai_models
    FOR SELECT USING (true);

-- Create a policy for insert operations (only authenticated users can insert)
CREATE POLICY "Allow insert for authenticated users" ON ai_models
    FOR INSERT WITH CHECK (auth.role() = 'authenticated');

-- Create policies for update and delete (only allow for the user who created the record)
CREATE POLICY "Allow update for record owners" ON ai_models
    FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "Allow delete for record owners" ON ai_models
    FOR DELETE USING (auth.uid() = user_id);

3. Apply migration

pnpm run supabase:migrate-up

4. Regenerate types for Typescript

pnpm run supabase:types

Well done! You've just created a new table and generated types for it.

🔋 Setup

🔨 Useful commands