Database Operations Guide

Good to know

This chapter is not a complete guide for Supabase database operations, but rather covers some database operation methods used in Nexty.dev, which will help you quickly understand the features provided by the boilerplate.

AI is very familiar with Supabase, so you can confidently use AI to complete various database operations.

Nexty.dev implements a comprehensive Supabase database operation architecture, providing type-safe database access, comprehensive permission control, and unified operation patterns. By combining the 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 Query

let query = supabase
  .from('posts')
  .select('*');
 
if (filter) {
  // 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 Query

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

Transaction Processing & RPC

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

RPC functions are PostgreSQL functions defined directly in the database that can be called directly through the Supabase client, offering 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 the database level, reducing network round trips
Permission control: 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
Bulk 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 used in methods that add credits after user payments. Their function logic is defined in the corresponding data table definition files under the supabase/migration folder.

Permission Control & Security

Admin Permission Check

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

import { createClient as createAdminClient } from "@supabase/supabase-js";
import { isAdmin } from '@/lib/supabase/isAdmin';
 
// Check 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 Policy

RLS (Row Level Security) is a security feature of PostgreSQL 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-side code has vulnerabilities, it cannot access other users' data.

Here's the basic usage of RLS policies:

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 owners)
ALTER TABLE public.users FORCE ROW LEVEL SECURITY;

Create Policies

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

In the supabase/migration folder provided by the boilerplate, you can see statements for creating RLS in all table designs.

Good to know

For tables with RLS policies like "Users can only update their own profiles", admins cannot directly update user profiles through the server-side client exported by lib/supabase/server. They can only first verify admin identity, then use @supabase/supabase-js to bypass RLS policies and modify the database.

Database Migration & Updates

Reference

Supabase Integration

Dev Examples

Best Practices

Selective Field Queries

For select, try to only select necessary fields 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 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 boilerplate provides methods for unified return formats:

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

lib/action-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 boilerplate also provides fallback handling methods for different error formats. In try...catch code blocks, you can use the getErrorMessage method from lib/error-utils.ts to process errors caught by catch. This prevents irregular error information from causing frontend toast messages to fail displaying real error messages.

Supabase Database Features Overview

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