NuxtFast ⚡️
Login
Core Concepts
January 21, 2025

Database Integration

Learn how to work with Supabase PostgreSQL database in your NuxtFast application

Author

Lorenzo

Author

NuxtFast uses Supabase's PostgreSQL database to provide a powerful, scalable, and real-time database solution. This guide covers everything you need to know about working with your database.

Overview

Supabase provides:

  • PostgreSQL database with full SQL support
  • Real-time subscriptions for live data updates
  • Row Level Security (RLS) for data protection
  • Automatic API generation from your database schema
  • Database migrations for version control

Quick Start

1. Database Schema

Create your database tables using Supabase's SQL editor or migrations:

-- Create a profiles table
CREATE TABLE profiles (
  id UUID REFERENCES auth.users ON DELETE CASCADE,
  username TEXT UNIQUE,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  PRIMARY KEY (id)
);

-- Enable RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "Users can view own profile" ON profiles
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON profiles
  FOR UPDATE USING (auth.uid() = id);

2. Basic Database Operations

Here's how to perform CRUD operations with the Supabase client:

<template>
  <div>
    <div v-if="pending" class="loading loading-spinner"></div>
    <div v-else>
      <div v-for="profile in profiles" :key="profile.id" class="card">
        <div class="card-body">
          <h3 class="card-title">{{ profile.full_name }}</h3>
          <p>@{{ profile.username }}</p>
        </div>
      </div>
    </div>
  </div>
</template>

<script setup>
const supabase = useSupabaseClient()

// Fetch data
const { data: profiles, pending } = await useAsyncData('profiles', async () => {
  const { data, error } = await supabase
    .from('profiles')
    .select('*')
    .order('created_at', { ascending: false })

  if (error) throw error
  return data
})
</script>

Data Fetching

Using useAsyncData

For server-side rendering and automatic caching:

const {
  data: posts,
  pending,
  error,
} = await useAsyncData('posts', async () => {
  const { data, error } = await supabase
    .from('posts')
    .select(
      `
      *,
      profiles(username, avatar_url)
    `
    )
    .order('created_at', { ascending: false })

  if (error) throw error
  return data
})

Client-side Fetching

For interactive data that changes frequently:

const posts = ref([])
const loading = ref(true)

const fetchPosts = async () => {
  loading.value = true
  const { data, error } = await supabase.from('posts').select('*').eq('published', true).limit(10)

  if (error) {
    console.error('Error fetching posts:', error)
  } else {
    posts.value = data
  }
  loading.value = false
}

Real-time Subscriptions

Listen for database changes in real-time:

<script setup>
const supabase = useSupabaseClient()
const messages = ref([])

onMounted(() => {
  // Subscribe to changes
  const channel = supabase
    .channel('messages')
    .on(
      'postgres_changes',
      {
        event: 'INSERT',
        schema: 'public',
        table: 'messages',
      },
      payload => {
        messages.value.push(payload.new)
      }
    )
    .on(
      'postgres_changes',
      {
        event: 'DELETE',
        schema: 'public',
        table: 'messages',
      },
      payload => {
        const index = messages.value.findIndex(m => m.id === payload.old.id)
        if (index > -1) {
          messages.value.splice(index, 1)
        }
      }
    )
    .subscribe()

  // Cleanup on unmount
  onUnmounted(() => {
    supabase.removeChannel(channel)
  })
})
</script>

Database Queries

Filtering and Sorting

// Filter by status
const { data } = await supabase.from('tasks').select('*').eq('status', 'completed')

// Multiple filters
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('published', true)
  .gte('created_at', '2024-01-01')
  .order('created_at', { ascending: false })

// Text search
const { data } = await supabase.from('posts').select('*').textSearch('title', 'database')

Joins and Relations

// Join with related tables
const { data } = await supabase.from('posts').select(`
    *,
    profiles!posts_author_id_fkey(
      username,
      full_name,
      avatar_url
    ),
    categories(
      name,
      slug
    )
  `)

// Nested relations
const { data } = await supabase.from('users').select(`
    *,
    posts(
      *,
      comments(
        *,
        profiles(username)
      )
    )
  `)

Aggregations

// Count records
const { count } = await supabase
  .from('posts')
  .select('*', { count: 'exact', head: true })
  .eq('published', true)

// Group by and count
const { data } = await supabase.from('posts').select('category, count(*)').group('category')

Data Mutations

Creating Records

const createPost = async postData => {
  const { data, error } = await supabase
    .from('posts')
    .insert({
      title: postData.title,
      content: postData.content,
      author_id: user.value.id,
      published: false,
    })
    .select()

  if (error) {
    console.error('Error creating post:', error)
    return null
  }

  return data[0]
}

Updating Records

const updatePost = async (id, updates) => {
  const { data, error } = await supabase
    .from('posts')
    .update(updates)
    .eq('id', id)
    .eq('author_id', user.value.id) // Security check
    .select()

  if (error) {
    console.error('Error updating post:', error)
    return null
  }

  return data[0]
}

Deleting Records

const deletePost = async id => {
  const { error } = await supabase
    .from('posts')
    .delete()
    .eq('id', id)
    .eq('author_id', user.value.id) // Security check

  if (error) {
    console.error('Error deleting post:', error)
    return false
  }

  return true
}

Database Functions

Using RPC Functions

Create custom functions in your database:

-- Database function
CREATE OR REPLACE FUNCTION get_user_stats(user_id UUID)
RETURNS JSON AS $$
BEGIN
  RETURN (
    SELECT json_build_object(
      'post_count', COUNT(posts.id),
      'comment_count', COUNT(comments.id),
      'total_likes', SUM(posts.likes)
    )
    FROM posts
    LEFT JOIN comments ON comments.post_id = posts.id
    WHERE posts.author_id = user_id
  );
END;
$$ LANGUAGE plpgsql;

Call from your application:

const getUserStats = async userId => {
  const { data, error } = await supabase.rpc('get_user_stats', { user_id: userId })

  if (error) {
    console.error('Error getting user stats:', error)
    return null
  }

  return data
}

Migrations

Creating Migrations

Create migration files to track database changes:

-- migrations/001_initial_schema.sql
CREATE TABLE profiles (
  id UUID REFERENCES auth.users ON DELETE CASCADE,
  username TEXT UNIQUE,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  PRIMARY KEY (id)
);

-- Enable RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Policies
CREATE POLICY "Users can view own profile" ON profiles
  FOR SELECT USING (auth.uid() = id);

Running Migrations

Use Supabase CLI to manage migrations:

# Create a new migration
supabase migration new create_posts_table

# Apply migrations
supabase db push

# Reset database (development only)
supabase db reset

Performance Optimization

Indexing

Add indexes for frequently queried columns:

-- Index on frequently filtered columns
CREATE INDEX idx_posts_published ON posts(published);
CREATE INDEX idx_posts_author_id ON posts(author_id);

-- Composite index for complex queries
CREATE INDEX idx_posts_published_created ON posts(published, created_at DESC);

-- Text search index
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('english', title || ' ' || content));

Query Optimization

// Use select() to limit returned columns
const { data } = await supabase
  .from('posts')
  .select('id, title, created_at') // Only get what you need
  .limit(10)

// Use pagination for large datasets
const { data } = await supabase
  .from('posts')
  .select('*')
  .range(0, 9) // Get first 10 records
  .order('created_at', { ascending: false })

Security Best Practices

Row Level Security

Always use RLS to protect your data:

-- Enable RLS on all tables
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Users can only see published posts or their own drafts
CREATE POLICY "Posts are viewable by everyone" ON posts
  FOR SELECT USING (
    published = true OR auth.uid() = author_id
  );

-- Users can only edit their own posts
CREATE POLICY "Users can edit own posts" ON posts
  FOR UPDATE USING (auth.uid() = author_id);

Input Validation

Validate data on both client and server:

const createPost = async postData => {
  // Client-side validation
  if (!postData.title || postData.title.length < 3) {
    throw new Error('Title must be at least 3 characters')
  }

  const { data, error } = await supabase.from('posts').insert(postData).select()

  if (error) throw error
  return data[0]
}

Error Handling

const fetchUserPosts = async userId => {
  try {
    const { data, error } = await supabase.from('posts').select('*').eq('author_id', userId)

    if (error) {
      // Handle different error types
      if (error.code === 'PGRST116') {
        console.error('No posts found for user')
        return []
      }
      throw error
    }

    return data
  } catch (error) {
    console.error('Unexpected error:', error)
    throw error
  }
}

Testing

Database Testing

Use Supabase's testing utilities:

// tests/database.test.js
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(process.env.SUPABASE_URL, process.env.SUPABASE_ANON_KEY)

describe('Database Operations', () => {
  test('should create and fetch posts', async () => {
    const { data, error } = await supabase
      .from('posts')
      .insert({ title: 'Test Post', content: 'Test content' })
      .select()

    expect(error).toBeNull()
    expect(data[0].title).toBe('Test Post')
  })
})