Module 4

Topic 3

Database (PostgreSQL)

Working with PostgreSQL in Supabase — tables, CRUD operations, queries, and relationships.

Supabase Database Overview Querying Data Insert Data

PostgreSQL in Supabase

Supabase uses PostgreSQL as its database — the world's most advanced open-source relational database. Unlike Firestore (NoSQL), PostgreSQL is a relational database with:

Tables – Structured data with rows and columns
Relationships – Foreign keys and JOINs
SQL – Powerful query language
Transactions – ACID compliance
Indexes – Fast queries
Constraints – Data integrity

✅ PostgreSQL vs Firestore (NoSQL)

PostgreSQL – Relational, complex queries, transactions, strong consistency
Firestore – Document-based, simpler queries, eventual consistency
PostgreSQL – Better for complex data relationships
Firestore – Better for simple, hierarchical data

Database Schema

A database schema defines the structure of your data. Here's a common schema for a blog app:

users

id (UUID), email, username, created_at

posts

id, user_id (FK), title, content, created_at

comments

id, post_id (FK), user_id (FK), content, created_at

likes

id, post_id (FK), user_id (FK), created_at

SQL – Create Tables



         -- Users table (extends Supabase auth.users)
        
        CREATE TABLE public.profiles (
    id UUID REFERENCES auth.users(id) PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    full_name TEXT,
    avatar_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
  );
        
         -- Posts table
        
        CREATE TABLE public.posts (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
  );
        
         -- Comments table
        
        CREATE TABLE public.comments (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    post_id UUID REFERENCES public.posts(id) ON DELETE CASCADE,
    user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
  );
        
         -- Likes table
        
        CREATE TABLE public.likes (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    post_id UUID REFERENCES public.posts(id) ON DELETE CASCADE,
    user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(post_id, user_id)
  );

CRUD Operations

Supabase provides a fluent API for CRUD operations. Here's how to perform them in Flutter.

        lib/services/post_service.dart – CRUD Operations
       
import 'package:supabase_flutter/supabase_flutter.dart';

class PostService {
  final SupabaseClient client = Supabase.instance.client;

  // ----- CREATE (INSERT) -----

  Future<Map<String, dynamic>> createPost({
    required String userId,

    required String title,

    required String content,
  }) async {
    final response = await client
        .from('posts')
        .insert({'user_id': userId, 'title': title, 'content': content})
        .select()
        .single();

    return response;
  }

  // ----- READ (SELECT) -----

  Future<List<Map<String, dynamic>>> getAllPosts() async {
    final response = await client
        .from('posts')
        .select('*, profiles(username, full_name)')
        .order('created_at', ascending: false);

    return response;
  }

  Future<Map<String, dynamic>> getPostById(String id) async {
    final response = await client
        .from('posts')
        .select(
          '*, profiles(username, full_name), comments(*, profiles(username))',
        )
        .eq('id', id)
        .single();

    return response;
  }

  // ----- UPDATE -----

  Future<Map<String, dynamic>> updatePost({
    required String id,

    required String title,

    required String content,
  }) async {
    final response = await client
        .from('posts')
        .update({
          'title': title,

          'content': content,

          'updated_at': DateTime.now().toIso8601String(),
        })
        .eq('id', id)
        .select()
        .single();

    return response;
  }

  // ----- DELETE -----

  Future<void> deletePost(String id) async {
    await client.from('posts').delete().eq('id', id);
  }
}

Querying Data

Supabase supports powerful query operations including filtering, sorting, and limiting.

        lib/services/query_service.dart – Advanced Queries
       
class QueryService {
  final SupabaseClient client = Supabase.instance.client;

  // ----- FILTERING -----

  Future<List<Map<String, dynamic>>> filterPosts({
    String? userId,
    String? searchQuery,
    DateTime? fromDate,
    DateTime? toDate,
  }) async {
    var query = client.from('posts').select('*, profiles(username)');

    if (userId != null) {
      query = query.eq('user_id', userId);
    }

    if (searchQuery != null && searchQuery.isNotEmpty) {
      query = query.ilike('title', '%$searchQuery%');
    }

    if (fromDate != null) {
      query = query.gte('created_at', fromDate.toIso8601String());
    }

    if (toDate != null) {
      query = query.lte('created_at', toDate.toIso8601String());
    }

    return await query.order('created_at', ascending: false);
  }

  // ----- PAGINATION -----

  Future<List<Map<String, dynamic>>> getPostsPaginated({
    required int page,

    required int pageSize,
  }) async {
    final from = (page - 1) * pageSize;

    final to = from + pageSize - 1;

    return await client
        .from('posts')
        .select('*, profiles(username)')
        .order('created_at', ascending: false)
        .range(from, to);
  }

  // ----- AGGREGATION -----

  Future<Map<String, dynamic>> getPostStats() async {
    // Get total posts

    final countResponse = await client
        .from('posts')
        .select('*', count: CountOption.exact)
        .limit(0);

    // Get posts by user

    final userPosts = await client
        .from('posts')
        .select('user_id, count')
        .groupBy('user_id');

    return {'totalPosts': countResponse.count, 'postsByUser': userPosts};
  }

  // ----- COMPLEX QUERY WITH JOIN -----

  Future<List<Map<String, dynamic>>> getPostsWithCommentsCount() async {
    return await client
        .from('posts')
        .select('*, profiles(username), comments(count)')
        .order('created_at', ascending: false);
  }
}

Relationships (Foreign Keys)

PostgreSQL supports powerful relationships between tables using foreign keys.

        lib/models/relationship_example.dart – Relationships
       
// One-to-Many: User → Posts

final userWithPosts = await client
    .from('profiles')
    .select('*, posts(*)')
    .eq('id', userId)
    .single();

// Many-to-One: Post → User (with foreign key)

final postWithUser = await client
    .from('posts')
    .select('*, profiles(username, full_name)')
    .eq('id', postId)
    .single();

// Many-to-Many: Posts ↔ Users (through likes table)

final likedPosts = await client
    .from('likes')
    .select('posts(*, profiles(username))')
    .eq('user_id', userId)
    .order('created_at', ascending: false);

🔑 Foreign Key Types

ON DELETE CASCADE – Delete child rows when parent is deleted
ON DELETE SET NULL – Set foreign key to NULL when parent is deleted
ON DELETE RESTRICT – Prevent deletion if child rows exist

Complete Example: Blog App

Here's a complete blog app using Supabase PostgreSQL with all CRUD operations.

        lib/main.dart – Complete Blog App
       
import 'package:flutter/material.dart';

import 'package:supabase_flutter/supabase_flutter.dart';

import 'package:intl/intl.dart';

// ==========================================

// 1. MAIN

// ==========================================

void main() async {
  await Supabase.initialize(
    url: 'https://your-project.supabase.co',
    anonKey: 'your-anon-key',
  );
  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Blog App',
      theme: ThemeData(
        colorScheme: ColorScheme.fromSeed(seedColor: Colors.deepPurple),
        useMaterial3: true,
      ),
      home: BlogListScreen(),
    );
  }
}

// ==========================================

// 2. SERVICES

// ==========================================

class BlogService {
  final SupabaseClient client = Supabase.instance.client;

  Future<List<Map<String, dynamic>>> getPosts() async {
    final response = await client
        .from('posts')
        .select('*, profiles(username, full_name)')
        .order('created_at', ascending: false);

    return response;
  }

  Future<Map<String, dynamic>> createPost({
    required String userId,

    required String title,

    required String content,
  }) async {
    return await client
        .from('posts')
        .insert({'user_id': userId, 'title': title, 'content': content})
        .select()
        .single();
  }

  Future<void> deletePost(String id) async {
    await client.from('posts').delete().eq('id', id);
  }

  Future<Map<String, dynamic>> toggleLike({
    required String postId,

    required String userId,
  }) async {
    // Check if like exists

    final existing = await client
        .from('likes')
        .select()
        .eq('post_id', postId)
        .eq('user_id', userId)
        .maybeSingle();

    if (existing != null) {
      // Unlike

      await client
          .from('likes')
          .delete()
          .eq('post_id', postId)
          .eq('user_id', userId);

      return {'liked': false};
    } else {
      // Like

      await client.from('likes').insert({'post_id': postId, 'user_id': userId});

      return {'liked': true};
    }
  }
}

// ==========================================

// 3. UI

// ==========================================

class BlogListScreen extends StatefulWidget {
  @override
  State<BlogListScreen> createState() => _BlogListScreenState();
}

class _BlogListScreenState extends State<BlogListScreen> {
  final BlogService _blogService = BlogService();
  List<Map<String, dynamic>> _posts = [];

  bool _isLoading = true;

  bool _hasError = false;
  String? _errorMessage;

  final TextEditingController _titleController = TextEditingController();

  final TextEditingController _contentController = TextEditingController();

  @override
  void initState() {
    super.initState();
    _loadPosts();
  }

  Future<void> _loadPosts() async {
    setState(() {
      _isLoading = true;
      _hasError = false;
    });

    try {
      final posts = await _blogService.getPosts();
      setState(() {
        _posts = posts;
        _isLoading = false;
      });
    } catch (e) {
      setState(() {
        _isLoading = false;
        _hasError = true;
        _errorMessage = e.toString();
      });
    }
  }

  Future<void> _createPost() async {
    final title = _titleController.text.trim();

    final content = _contentController.text.trim();

    if (title.isEmpty || content.isEmpty) return;

    final user = Supabase.instance.client.auth.currentUser;

    if (user == null) return;

    try {
      await _blogService.createPost(
        userId: user.id,
        title: title,
        content: content,
      );
      _titleController.clear();
      _contentController.clear();

      await _loadPosts();
      Navigator.pop(context);
    } catch (e) {
      ScaffoldMessenger.of(context).showSnackBar(
        SnackBar(content: Text('Error: $e'), backgroundColor: Colors.red),
      );
    }
  }

  Future<void> _deletePost(String id) async {
    showDialog(
      context: context,
      builder: (context) => AlertDialog(
        title: Text('Delete Post'),
        content: Text('Are you sure you want to delete this post?'),
        actions: [
          TextButton(
            onPressed: () => Navigator.pop(context),
            child: Text('Cancel'),
          ),
          ElevatedButton(
            onPressed: () async {
              await _blogService.deletePost(id);
              Navigator.pop(context);

              await _loadPosts();
            },
            child: Text('Delete'),
            style: ElevatedButton.styleFrom(backgroundColor: Colors.red),
          ),
        ],
      ),
    );
  }

  Future<void> _toggleLike(String postId) async {
    final user = Supabase.instance.client.auth.currentUser;

    if (user == null) return;

    await _blogService.toggleLike(postId: postId, userId: user.id);

    await _loadPosts();
  }

  void _showCreateDialog() {
    showDialog(
      context: context,
      builder: (context) => AlertDialog(
        title: Text('Create Post'),
        content: Column(
          mainAxisSize: MainAxisSize.min,
          children: [
            TextField(
              controller: _titleController,
              decoration: InputDecoration(
                hintText: 'Title...',
                border: OutlineInputBorder(),
              ),
            ),
            SizedBox(height: 8),
            TextField(
              controller: _contentController,
              decoration: InputDecoration(
                hintText: 'Content...',
                border: OutlineInputBorder(),
              ),
              maxLines: 5,
            ),
          ],
        ),
        actions: [
          TextButton(
            onPressed: () => Navigator.pop(context),
            child: Text('Cancel'),
          ),
          ElevatedButton(onPressed: _createPost, child: Text('Publish')),
        ],
      ),
    );
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('📝 Blog'),
        centerTitle: true,
        actions: [IconButton(icon: Icon(Icons.refresh), onPressed: _loadPosts)],
      ),
      body: _isLoading
          ? Center(child: CircularProgressIndicator())
          : _hasError
          ? Center(
              child: Column(
                mainAxisAlignment: MainAxisAlignment.center,
                children: [
                  Icon(Icons.error, size: 64, color: Colors.red),
                  SizedBox(height: 16),
                  Text(_errorMessage ?? 'Error loading posts'),
                  SizedBox(height: 16),
                  ElevatedButton(onPressed: _loadPosts, child: Text('Retry')),
                ],
              ),
            )
          : _posts.isEmpty
          ? Center(
              child: Column(
                mainAxisAlignment: MainAxisAlignment.center,
                children: [
                  Icon(Icons.post_add, size: 80, color: Colors.grey.shade400),
                  SizedBox(height: 16),
                  Text(
                    'No posts yet',
                    style: TextStyle(fontSize: 20, fontWeight: FontWeight.w600),
                  ),
                  Text(
                    'Be the first to publish!',
                    style: TextStyle(color: Colors.grey.shade400),
                  ),
                ],
              ),
            )
          : ListView.builder(
              padding: EdgeInsets.all(8),
              itemCount: _posts.length,
              itemBuilder: (context, index) {
                final post = _posts[index];

                final profile = post['profiles'] as Map<String, dynamic>?;

                final username = profile?['username'] ?? 'Unknown';

                final createdAt = DateTime.parse(post['created_at']);

                return Card(
                  margin: EdgeInsets.symmetric(horizontal: 8, vertical: 4),
                  child: Padding(
                    padding: EdgeInsets.all(12),
                    child: Column(
                      crossAxisAlignment: CrossAxisAlignment.start,
                      children: [
                        Row(
                          children: [
                            CircleAvatar(
                              radius: 16,
                              child: Text(
                                username.isNotEmpty
                                    ? username[0].toUpperCase()
                                    : '?',
                                style: TextStyle(fontSize: 12),
                              ),
                            ),
                            SizedBox(width: 8),
                            Text(
                              username,
                              style: TextStyle(fontWeight: FontWeight.w600),
                            ),
                            Spacer(),
                            Text(
                              DateFormat('MMM d, yyyy').format(createdAt),
                              style: TextStyle(
                                fontSize: 12,
                                color: Colors.grey.shade500,
                              ),
                            ),
                          ],
                        ),
                        SizedBox(height: 8),
                        Text(
                          post['title'],
                          style: TextStyle(
                            fontSize: 18,
                            fontWeight: FontWeight.bold,
                          ),
                        ),
                        SizedBox(height: 4),
                        Text(
                          post['content'],
                          style: TextStyle(color: Colors.grey.shade700),
                          maxLines: 3,
                          overflow: TextOverflow.ellipsis,
                        ),
                        SizedBox(height: 12),
                        Row(
                          children: [
                            IconButton(
                              icon: Icon(Icons.favorite_border, size: 20),
                              onPressed: () => _toggleLike(post['id']),
                            ),
                            SizedBox(width: 4),
                            Text(
                              '0',

                              // Like count would come from the database
                              style: TextStyle(fontSize: 14),
                            ),
                            Spacer(),
                            IconButton(
                              icon: Icon(
                                Icons.delete,
                                size: 20,
                                color: Colors.red,
                              ),
                              onPressed: () => _deletePost(post['id']),
                            ),
                          ],
                        ),
                      ],
                    ),
                  ),
                );
              },
            ),
      floatingActionButton: FloatingActionButton(
        onPressed: _showCreateDialog,
        child: Icon(Icons.add),
      ),
    );
  }
}

Step-by-Step Explanation

Define your schema – Create tables in the Supabase SQL Editor with proper relationships and constraints.

Create the service – Build a service class that wraps all database operations.

INSERT (Create) – Use


          .insert()

with


          .select()

to create and return data.

SELECT (Read) – Use


          .select()

with relationships using dot notation (


          '*, profiles(username)'

UPDATE – Use


          .update()

with


          .eq()

and


          .select()

DELETE – Use


          .delete()

with


          .eq()

to remove rows.

Common Mistakes

❌ Mistake 1: Not using .select() after insert/update

Without .select() , you won't get the created/updated data back with default values like created_at .

✅ Correct: Use .select() after mutations

.insert(data).select().single() returns the newly created row.

❌ Mistake 2: Not handling null relationships

When using .select('*, profiles(username)') , the profiles field may be null if no related profile exists.

✅ Correct: Handle null relationships

Use profile?['username'] ?? 'Unknown' to handle null values safely.

❌ Mistake 3: Not using RLS policies

Without Row Level Security, users can read and modify data they shouldn't have access to.

✅ Correct: Always enable RLS

Enable RLS in the Supabase dashboard and create policies for each table and operation.

🎯 Key Takeaway

Supabase uses PostgreSQL for data storage, providing powerful relational database capabilities. You can perform CRUD operations with a fluent API, complex queries with filtering and sorting, and relationships with foreign keys. Always use .select() after mutations to get the updated data.

Previous: Authentication Next: Storage