Database Migrations

Overview

AniDojo uses Supabase for its database, and migrations are managed through SQL files. This guide covers how to apply migrations to your production database and manage schema changes.

Database migrations must be run before deploying your application to production. The application will not function correctly without the proper database schema.

Understanding Migrations

Migrations are SQL scripts that create and modify your database schema. AniDojo’s migrations are located in:

supabase/migrations/
└── 001_initial_schema.sql

The initial schema creates:

profiles - User profile data extending Supabase Auth
anime_entries - User’s anime tracking entries
reviews - User-written anime reviews
review_votes - Helpful/unhelpful votes on reviews
comments - Comments on reviews
comment_votes - Upvotes/downvotes on comments
custom_lists - User-created anime lists
custom_list_entries - Anime entries in custom lists
Row Level Security (RLS) policies for data protection
Indexes for query performance
Triggers for automatic profile creation and timestamp updates

Prerequisites

Before running migrations, ensure you have:

A Supabase project created at supabase.com
Your project’s connection details (URL and keys)
Either the Supabase Dashboard access or Supabase CLI installed

Method 1: Using Supabase Dashboard (Recommended for Beginners)

This method is quick and doesn’t require any CLI tools.

Access SQL Editor

Go to your Supabase Dashboard
Select your project
Navigate to SQL Editor in the left sidebar

Open Migration File

Open the migration file from your project:

supabase/migrations/001_initial_schema.sql

This file contains 375 lines of SQL that creates your entire database schema.

Copy SQL Content

Copy the entire contents of 001_initial_schema.sqlThe file includes:

Table definitions
Indexes for performance
RLS policies for security
Triggers for automation
Helper functions

Run Migration

Paste the SQL into the SQL Editor
Click Run (or press Ctrl/Cmd + Enter)
Wait for execution to complete (typically 2-5 seconds)
Verify you see “Success. No rows returned” message

Verify Tables Created

Verify the migration succeeded:

Navigate to Table Editor in the left sidebar
You should see all 8 tables:
- profiles
- anime_entries
- reviews
- review_votes
- comments
- comment_votes
- custom_lists
- custom_list_entries

If you don’t see all tables, check the SQL Editor for error messages and ensure the entire script was pasted.

Method 2: Using Supabase CLI (Recommended for Production)

The CLI method is preferred for production environments and team workflows.

Install Supabase CLI

Install the Supabase CLI globally:

npm install -g supabase

Verify installation:

supabase --version

Get Project Reference ID

Go to your Supabase Dashboard
Select your project
Go to Settings → General
Copy your Reference ID (looks like: abcdefghijklmnop)

Link Project

Link your local project to your Supabase project:

supabase link --project-ref your-project-ref

You’ll be prompted to enter your database password. This is the password you set when creating your Supabase project.

This command creates a .supabase directory in your project. Add it to .gitignore to avoid committing credentials.

Push Migrations

Apply all migrations to your remote database:

supabase db push

This command:

Reads all files in supabase/migrations/
Applies them to your remote database in order
Tracks which migrations have been applied
Shows success/error messages

Expected output:

Applying migration 001_initial_schema.sql...
Finished supabase db push.

Verify Migration

Check that migrations were applied successfully:

supabase db diff

If no differences are shown, your database schema matches your migration files.

Post-Migration Setup

After running migrations, set up additional Supabase features:

Storage Buckets

Create storage buckets for file uploads:

Navigate to Storage

Go to your Supabase Dashboard
Click Storage in the left sidebar
Click Create a new bucket

Create Avatars Bucket

Create a bucket for user avatars:

Name: avatars
Public: ✅ Yes
File size limit: 5 MB
Allowed MIME types: image/jpeg, image/png, image/webp, image/gif

Create Review Images Bucket (Optional)

Create a bucket for review attachments:

Name: review-images
Public: ✅ Yes
File size limit: 10 MB
Allowed MIME types: image/jpeg, image/png, image/webp

Create Anime Images Bucket (Optional)

Create a bucket for custom anime images:

Name: anime-images
Public: ✅ Yes
File size limit: 10 MB
Allowed MIME types: image/jpeg, image/png, image/webp

Storage Policies

Set up Row Level Security policies for storage buckets:

Avatar Storage Policies

Run these SQL commands in the SQL Editor:

-- Allow public read access
CREATE POLICY "Avatar images are publicly accessible"
ON storage.objects FOR SELECT
USING (bucket_id = 'avatars');

-- Allow authenticated users to upload their own avatars
CREATE POLICY "Users can upload their own avatar"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'avatars' AND
  auth.uid()::text = (storage.foldername(name))[1]
);

-- Allow users to update their own avatars
CREATE POLICY "Users can update their own avatar"
ON storage.objects FOR UPDATE
USING (
  bucket_id = 'avatars' AND
  auth.uid()::text = (storage.foldername(name))[1]
);

-- Allow users to delete their own avatars
CREATE POLICY "Users can delete their own avatar"
ON storage.objects FOR DELETE
USING (
  bucket_id = 'avatars' AND
  auth.uid()::text = (storage.foldername(name))[1]
);

Review Images Storage Policies

-- Allow public read access
CREATE POLICY "Review images are publicly accessible"
ON storage.objects FOR SELECT
USING (bucket_id = 'review-images');

-- Allow authenticated users to upload review images
CREATE POLICY "Authenticated users can upload review images"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'review-images' AND
  auth.role() = 'authenticated'
);

Anime Images Storage Policies

-- Allow public read access
CREATE POLICY "Anime images are publicly accessible"
ON storage.objects FOR SELECT
USING (bucket_id = 'anime-images');

-- Allow authenticated users to upload anime images
CREATE POLICY "Authenticated users can upload anime images"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'anime-images' AND
  auth.role() = 'authenticated'
);

Verifying Your Setup

After running migrations and setting up storage, verify everything works:

Test Authentication

Deploy your application (or run locally)
Navigate to the signup page
Create a test account
Verify the user appears in Supabase Dashboard → Authentication → Users
Check that a profile was automatically created in Table Editor → profiles

Test Database Operations

Sign in to your test account
Add an anime to your list
Verify it appears in Table Editor → anime_entries
Try updating and deleting the entry

Test Storage

Upload a profile avatar
Verify the image appears in Storage → avatars
Check that the image displays correctly in your app

Managing Future Migrations

As your application evolves, you’ll need to add more migrations:

Creating New Migrations

Create Migration File

Create a new migration file with a sequential number:

supabase/migrations/002_add_favorites_feature.sql

Use descriptive names that explain what the migration does. Include a number prefix to maintain order.

Write Migration SQL

Add your schema changes:

002_add_favorites_feature.sql

-- Add new column to anime_entries
ALTER TABLE public.anime_entries
ADD COLUMN IF NOT EXISTS is_favorite BOOLEAN DEFAULT false;

-- Create index for favorites queries
CREATE INDEX IF NOT EXISTS idx_anime_entries_favorite
ON public.anime_entries(user_id, is_favorite)
WHERE is_favorite = true;

Apply Migration

Using CLI:

supabase db push

Or manually run the SQL in the Dashboard SQL Editor.

Best Practices

Always use IF NOT EXISTS/IF EXISTS

Make migrations idempotent so they can be run multiple times safely:

-- ✅ Good: Won't fail if table already exists
CREATE TABLE IF NOT EXISTS my_table (...);

-- ❌ Bad: Will fail on second run
CREATE TABLE my_table (...);

Include rollback instructions

Document how to undo migrations:

-- Migration: Add favorites feature
ALTER TABLE anime_entries ADD COLUMN is_favorite BOOLEAN;

-- Rollback (if needed):
-- ALTER TABLE anime_entries DROP COLUMN is_favorite;

Test migrations on a staging database first

Create a separate Supabase project for staging
Apply migrations there first
Test thoroughly
Only then apply to production

Never modify existing migrations

Once a migration has been applied to production:

Never edit the migration file
Create a new migration to make changes
This maintains a clear history of schema changes

Troubleshooting

”relation already exists” Error

Cause: Migration has already been run or table was created manually Solution:

Add IF NOT EXISTS to CREATE statements
Or drop existing tables before running (⚠️ data loss!)
Or skip to the next migration

”permission denied” Error

Cause: Insufficient database permissions Solution:

Ensure you’re using the correct database password
Verify your Supabase project is active (not paused)
Check that you have owner/admin access to the project

RLS Policy Errors

Cause: Conflict with existing policies or incorrect syntax Solution:

Check for duplicate policy names

Drop existing policies first:

DROP POLICY IF EXISTS "policy_name" ON table_name;

Then recreate them

Storage Bucket Issues

“Bucket already exists” error:

Check Storage dashboard for existing buckets
Use a different bucket name
Or delete the existing bucket (⚠️ deletes all files!)

Files won’t upload:

Verify storage policies are set up correctly
Check file size limits
Ensure MIME types are allowed
Verify user is authenticated

Advanced: Local Development with Supabase

For advanced workflows, you can run Supabase locally:

# Start local Supabase (requires Docker)
supabase start

# Apply migrations locally
supabase db reset

# Create a new migration
supabase migration new add_feature

# Generate types from your database
supabase gen types typescript --local > types/database.ts

See Supabase Local Development for details.

Next Steps

Deploy to Vercel

Deploy your application with the configured database

Environment Variables

Configure connection to your database

Architecture

Deployment

Contributing

Database Migrations

Overview

Understanding Migrations

Prerequisites

Method 1: Using Supabase Dashboard (Recommended for Beginners)

Method 2: Using Supabase CLI (Recommended for Production)

Post-Migration Setup

Storage Buckets

Storage Policies

Verifying Your Setup

Managing Future Migrations

Creating New Migrations

Best Practices

Troubleshooting

”relation already exists” Error

”permission denied” Error

RLS Policy Errors

Storage Bucket Issues

Advanced: Local Development with Supabase

Next Steps

Deploy to Vercel

Environment Variables

Additional Resources

Build docs developers (and LLMs) love

Architecture

Deployment

Contributing

​Overview

​Understanding Migrations

​Prerequisites

​Method 1: Using Supabase Dashboard (Recommended for Beginners)

​Method 2: Using Supabase CLI (Recommended for Production)

​Post-Migration Setup

​Storage Buckets

​Storage Policies

​Verifying Your Setup

​Managing Future Migrations

​Creating New Migrations

​Best Practices

​Troubleshooting

​”relation already exists” Error

​”permission denied” Error

​RLS Policy Errors

​Storage Bucket Issues

​Advanced: Local Development with Supabase

​Next Steps

Deploy to Vercel

Environment Variables

​Additional Resources

Build docs developers (and LLMs) love

Overview

Understanding Migrations

Prerequisites

Method 1: Using Supabase Dashboard (Recommended for Beginners)

Method 2: Using Supabase CLI (Recommended for Production)

Post-Migration Setup

Storage Buckets

Storage Policies

Verifying Your Setup

Managing Future Migrations

Creating New Migrations

Best Practices

Troubleshooting

”relation already exists” Error

”permission denied” Error

RLS Policy Errors

Storage Bucket Issues

Advanced: Local Development with Supabase

Next Steps

Additional Resources