Supabase Database Operations Guide

Good to know

• The following documentation is not a complete guide to Supabase database operations, but rather some operation methods applied in Nexty.dev that can help you understand the functionality provided by the template more quickly.
• AI is very familiar with Supabase, so you can confidently use AI to complete various database operations.

Nexty.dev adopts a complete Supabase database operation architecture, providing type-safe database access, comprehensive permission control, and unified operation patterns. By combining TypeScript type system, Zod data validation, and RLS (Row Level Security) policies, it ensures the security and maintainability of data operations.

Basic CRUD Operations

Query Operations (SELECT)

Simple Query

const { data: users, error } = await supabase
  .from('users')
  .select('*') // or specify return fields .select('email, role')
  .eq('role', 'admin');

Relational Query

const { data: posts, error } = await supabase
  .from('posts')
  .select(`
    *,
    tags (*)
  `)
  .eq('status', 'published');

Paginated Query

const { data, error, count } = await supabase
  .from('posts')
  .select('*', { count: 'exact' }) // { count: 'exact' } -> with total count statistics
  .range(0, 9)
  .order('created_at', { ascending: false });

Insert Data

const { data: newPost, error } = await supabase
  .from('posts')
  .insert({
    title: 'New Post',
    slug: 'new-post',
    content: 'Post content...',
    language: 'en',
    status: 'draft',
    author_id: user.id
  })
  .select("id")
  .single();

Update Data

const { data: updatedPost, error } = await supabase
  .from('posts')
  .update({
    title: 'Updated Title',
    status: 'published'
  })
  .eq('id', postId)
  .select("id")
  .single();

Delete Data

const { error } = await supabase
  .from('posts')
  .delete()
  .eq('id', postId);

Advanced Query Techniques

Conditional Queries

let query = supabase
  .from('posts')
  .select('*');
 
if (filter) {
  // Perform fuzzy search on title, slug, description
  const filterValue = `%${filter}%`;
  query = query.or(
    `title.ilike.${filterValue},slug.ilike.${filterValue},description.ilike.${filterValue}`
  );
}
 
if (status) {
  query = query.eq('status', status);
}
 
const { data, error } = await query
  .order('is_pinned', { ascending: false })
  .order('created_at', { ascending: false })
  .range(from, to);

Aggregate Queries

// Count the number of records where status field is 'published'
const { count, error } = await supabase
  .from('posts')
  .select('*', { count: 'exact', head: true })
  .eq('status', 'published');

Transaction Processing & RPC

For database atomic operations and complex operations, you can utilize Supabase's RPC (Remote Procedure Call) functions.

RPC are PostgreSQL functions defined directly in the database that can be called directly through the Supabase client, providing more powerful functionality than regular SQL queries.

const { error: usageError } = await supabaseAdmin.rpc('upsert_and_set_subscription_credits', {
  p_user_id: userId,
  p_credits_to_set: creditsToGrant
});

Advantages of RPC functions:

• Atomic operations: Ensure complex multi-step operations either all succeed or all fail
• Better performance: Execute at database level, reducing network round trips
• Permission control: Achieve fine-grained permission control through SECURITY DEFINER
• Complex business logic: Support conditional judgment, loops, exception handling, etc.
• Data consistency: Avoid race conditions and concurrency issues

Use cases:

• Credit deduction systems
• Order processing workflows
• Batch data updates
• Complex calculation logic
• Operations requiring transaction guarantees

In the Nexty.dev source code, you can globally search for .rpc to see RPC functions applied in methods that add credits after user payments. Their function logic is in the corresponding data table definition files under the data folder.

Permission Control & Security

Admin Permission Check

For sensitive operations, first verify admin identity, then use Service Role Key to operate the database

import { createClient as createAdminClient } from "@supabase/supabase-js";
import { isAdmin } from '@/lib/supabase/isAdmin';
 
// Verify admin identity
if (!(await isAdmin())) {
  return actionResponse.forbidden("Admin privileges required.");
}
 
// Admin uses Service Role Key to operate database
const supabaseAdmin = createAdminClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // Server-side only
);

RLS Policies

RLS (Row Level Security) is a PostgreSQL security feature that allows controlling user access permissions to specific rows at the database table level. Simply put, it ensures users can only access data that belongs to them.

In multi-user SaaS applications, data between different users must be strictly isolated. RLS provides database-level security guarantees, ensuring that even if client code has vulnerabilities, other users' data cannot be accessed.

Here's the basic usage of RLS policies:

1. Enable RLS

-- Enable row level security on table
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
 
-- Force all users to be subject to RLS restrictions (including table owner)
ALTER TABLE public.users FORCE ROW LEVEL SECURITY;

2. Create Policies

-- Users can only view their own profile
CREATE POLICY "Allow user read their own profile"
ON public.users 
FOR SELECT
USING (auth.uid() = id);
 
-- Users can only update their own profile
CREATE POLICY "Allow user update their own profile"
ON public.users 
FOR UPDATE
USING (auth.uid() = id);

In the data folder provided by the template, you can see RLS creation statements in all table designs.

Good to know

For tables that have created RLS policies like "Users can only update their own profile", administrators cannot directly update user profiles through the server-side client exported from lib/supabase/server. They can only first verify the user is an administrator, then use @supabase/supabase-js to bypass RLS policies and modify the database.

Database Migration & Updates

Database Type Updates

The template uses Supabase CLI to automatically generate and update TypeScript type definitions:

# Generate type file
supabase gen types typescript --project-id <your-project-id> --schema public > lib/supabase/types.ts

You need to execute this command locally after every database definition update to ensure your local type definitions are correct.

After executing the command, the database type definitions in the lib/supabase/types.ts file will be updated.

Good to know:

• For more Supabase CLI usage methods, please check Supabase Integration
• How to find <your-project-id>? Log into the Supabase console, the string after /project/ in the address bar is it, for example https://supabase.com/dashboard/project/<your-project-id>.

Schema Changes

-- Example migration for adding new field
ALTER TABLE posts ADD COLUMN view_count INTEGER DEFAULT 0;
 
-- Create index
CREATE INDEX IF NOT EXISTS idx_posts_status_language 
ON posts(status, language);
 
-- Update RLS policy
CREATE POLICY "Users can view published posts" ON posts
FOR SELECT USING (status = 'published');

Best Practices

Selective Field Queries

Try to only select necessary fields with select instead of using .select('*'), which can improve query performance

const { data: users, error } = await supabase
  .from('users')
  .select('email, role')
  .eq('role', 'admin');

Index Optimization for Queries

Optimize query performance by creating indexes

-- Create composite index
CREATE INDEX posts_language_status_idx ON posts (language, status);
CREATE INDEX posts_published_at_idx ON posts (published_at DESC);

Unified Return Format

The template provides methods for unified return formats:

• For Server Actions, call methods encapsulated in lib/action-response.ts
• For APIs, call methods encapsulated in lib/api-response.ts

export type ActionResult<T = any> =
  | { success: true; data?: T, customCode?: string }
  | { success: false; error: string, customCode?: string };
 
export const actionResponse = {
  success: <T>(data?: T, customCode?: string): ActionResult<T> => {
    return { success: true, data, customCode };
  },
  error: <T>(message: string, customCode?: string): ActionResult<T> => {
    return { success: false, error: message, customCode };
  },
  
  unauthorized: <T>(message = "Unauthorized", customCode?: string): ActionResult<T> => {
    return actionResponse.error(message, customCode);
  },
  // ...others...
};

The template also provides fallback handling methods for different error formats. In try...catch code blocks, errors caught by catch can call the getErrorMessage method from lib/error-utils.ts for processing, which avoids situations where irregular error messages cause frontend toast to be unable to display real error messages.

Supabase Database Feature Overview

In Supabase's Database directory, you can see all feature entries related to database definitions, including: Tables (data tables), Functions (RPC functions), Triggers (triggers), Indexes (indexes), Policies (policies).