Module 4
Topic 3
Database (PostgreSQL)
Working with PostgreSQL in Supabase — tables, CRUD operations, queries, and relationships.
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
1.
Define your schema
– Create tables in the Supabase SQL Editor with proper relationships and constraints.
2.
Create the service
– Build a service class that wraps all database operations.
3.
INSERT (Create)
– Use
.insert()
with
.select()
to create and return data.
4.
SELECT (Read)
– Use
.select()
with relationships using dot notation (
'*, profiles(username)'
).
5.
UPDATE
– Use
.update()
with
.eq()
and
.select()
.
6.
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.