# NoSQL Database Examples
# 1. Document Store (MongoDB-style)
mongodb_example = {
"_id": "507f1f77bcf86cd799439011",
"name": "John Doe",
"email": "john@example.com",
"age": 30,
"address": {
"street": "123 Main St",
"city": "Yerevan"
},
"hobbies": ["coding", "chess"],
"posts": [
{"title": "My First Post", "date": "2024-01-01"},
{"title": "Learning NoSQL", "date": "2024-01-02"}
]
}
# 2. Key-Value Store (Redis-style)
redis_example = {
"user:1001": "{'name':'John','email':'john@example.com'}",
"session:abc123": "{'user_id':1001,'expires':'2024-12-31'}",
"cache:trending": "['post1','post2','post3']"
}
# 3. Graph Database (Neo4j-style)
graph_example = """
(john:Person {name:'John', age:30})-[:FRIENDS_WITH]->(jane:Person {name:'Jane'})
(john)-[:WORKS_AT]->(company:Company {name:'TechCorp'})
(jane)-[:LIVES_IN]->(city:City {name:'Yerevan'})
"""16 DBs (Supabase)
ีฌีธึีฝีกีถีฏีกึีซ ีฐีฒีธึีดีจ, ีีฅีฒีซีถีกีฏี Karine Avetisyan
(ToDo)
Song reference - ToDo
๐ ีีฏีกึีกีฃีซึ
๐ ิฑีดีขีธีฒีปีกีฏีกีถ ีถีตีธึีฉีจ
๐บ ีีฅีฝีกีถีตีธึีฉีฅึ
๐ก ีีถีกีตีซีถ
๐ ีีตีธึีฉีจ
๐๏ธ Database Types Overview
๐ SQL Databases (Relational)
- Structure: Tables with rows and columns, strict schema
- Query Language: SQL (Structured Query Language)
- ACID Properties: Atomicity, Consistency, Isolation, Durability
๐ ACID Properties Explained
ACID guarantees reliable database transactions:
A - Atomicity โ๏ธ
- โAll or Nothingโ - A transaction either completes fully or not at all
- If any part fails, the entire transaction is rolled back
- Example: Bank transfer - both debit and credit must succeed, or neither happens
C - Consistency ๐ฏ
- โData Integrityโ - Database stays in a valid state before and after transactions
- All rules, constraints, and relationships are maintained
- Example: Account balance canโt be negative if thereโs a constraint
I - Isolation ๐
- โConcurrent Safetyโ - Multiple transactions donโt interfere with each other
- Each transaction sees a consistent view of the database
- Changes made by one transaction are not visible to others until they are committed.
- It ensures that the result of concurrent transactions is the same as if they were run one after another.
- Example: Two people trying to buy the last item - only one transaction succeeds
D - Durability ๐พ
- โPermanent Changesโ - Once committed, data survives system crashes
- Changes are written to persistent storage
- Example: Your bank deposit is safe even if the server crashes
Popular SQL Databases:
- PostgreSQL - Advanced, open-source
- MySQL - Fast, widely used
- SQLite - Lightweight, serverless
- Oracle - Enterprise-grade
- SQL Server - Microsoftโs solution
๐ NoSQL Databases (Non-Relational)
- Structure: Flexible, schema-less documents/key-value pairs
- Query Language: Varies by type
- Scalability: Horizontal scaling, eventual consistency
NoSQL Types:
1. Document Stores
- MongoDB, CouchDB
- Store JSON-like documents
- Flexible schema
2. Key-Value Stores
- Redis, DynamoDB
- Simple key-value pairs
- High performance
3. Graph Databases
- Neo4j, ArangoDB
- Nodes and relationships
- Complex connections
โ๏ธ SQL vs NoSQL Comparison
| Feature | SQL | NoSQL |
|---|---|---|
| Schema | Fixed, predefined | Flexible, dynamic |
| Scaling | Vertical (upgrade hardware) | Horizontal (add servers) |
| Consistency | ACID guaranteed | Eventual consistency |
| Queries | Complex SQL joins | Simple key lookups |
| Transactions | Full ACID support | Limited transactions |
| Data Structure | Structured, relational | Semi/unstructured |
| Learning Curve | Steeper (SQL syntax) | Easier (JSON-like) |
๐ฏ When to Use Each?
Choose SQL When:
- โ Complex relationships between data
- โ
Need ACID transactions
- โ Structured, well-defined data
- โ Complex reporting queries
- โ Mature ecosystem needed
Choose NoSQL When:
- โ Rapid development needed
- โ Flexible/changing data structure
- โ Massive scale required
- โ Simple read/write operations
- โ Document-based data
๐ง Specialized Databases
๐ Search Engines
- Elasticsearch - Full-text search, analytics
- Solr - Enterprise search platform
๐ Time Series
- InfluxDB - IoT, monitoring data
- TimescaleDB - PostgreSQL extension for time series
๐ง Vector Databases
- Pinecone - ML embeddings, AI applications
- Weaviate - Vector search with ML
๐ In-Memory
- Redis - Caching, real-time analytics
- Memcached - Simple key-value caching
๐ Multi-Model
- ArangoDB - Document + Graph + Key-Value
- Azure Cosmos DB - Multiple APIs in one service
๐ Database Keys & Constraints
๐๏ธ Types of Database Keys
Primary Key ๐
- Unique identifier for each record in a table
- Cannot be NULL or duplicate
- Automatically indexed for fast lookups
Examples:
-- Auto-incrementing primary key
id BIGSERIAL PRIMARY KEY
-- Composite primary key (multiple columns)
PRIMARY KEY (user_id, product_id)
-- Natural primary key
email VARCHAR(255) PRIMARY KEYForeign Key ๐
- Links two tables together
- References primary key of another table
- Maintains referential integrity
- Can be NULL (optional relationship)
Examples:
-- Simple foreign key
supplier_id BIGINT REFERENCES supplier(id)
-- Foreign key with actions
supplier_id BIGINT REFERENCES supplier(id)
ON DELETE CASCADE -- Delete related records
ON UPDATE SET NULL -- Set to NULL on updateUnique Key โจ
- Must be unique across all records
- Can have multiple unique keys per table
- Can be NULL (unlike primary key)
Examples:
email VARCHAR(255) UNIQUE
username VARCHAR(50) UNIQUE
phone_number VARCHAR(20) UNIQUE๐ก๏ธ Database Constraints
NOT NULL Constraint โ
- Prevents empty values in required fields
- Ensures data completeness
name TEXT NOT NULL -- Required field
email VARCHAR(255) NOT NULL -- Must have emailCHECK Constraint โ
- Custom validation rules
- Business logic enforcement
age INTEGER CHECK (age >= 0 AND age <= 150)
price INTEGER CHECK (price > 0)
status TEXT CHECK (status IN ('active', 'inactive', 'pending'))DEFAULT Constraint ๐ฏ
- Automatic value assignment
- Reduces data entry errors
created_at TIMESTAMPTZ DEFAULT NOW()
status TEXT DEFAULT 'active'
country TEXT DEFAULT 'Unknown'CASCADE Actions ๐
- Automatic relationship management
- Maintains data integrity
-- When supplier is deleted, set cheese supplier_id to NULL
ON DELETE SET NULL
-- When supplier is deleted, delete all related cheeses
ON DELETE CASCADE
-- When supplier ID changes, update all references
ON UPDATE CASCADE๐ Supabase Tutorial
Supabase is an open-source Backend-as-a-Service (BaaS) that provides: - PostgreSQL database with real-time subscriptions - Authentication & user management - Auto-generated APIs (REST & GraphQL) - File storage - Edge functions
๐ Resources
1๏ธโฃ Setup & Installation
Prerequisites
- Create Supabase Account at supabase.com
- Create New Project in Supabase dashboard
- Get API Keys from Project Settings > API
Environment Setup
Create a .env file in your project root:
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_KEY=your-anon-public-keyInstall Dependencies
# Install Supabase Python SDK
!uv pip install supabase python-dotenvUsing Python 3.10.18 environment at: C:\Users\hayk_\.conda\envs\lectures
Resolved 30 packages in 220ms
Uninstalled 2 packages in 41ms
Installed 14 packages in 296ms
+ deprecation==2.1.0
+ h2==4.2.0
+ hpack==4.1.0
+ hyperframe==6.1.0
+ postgrest==1.1.1
+ pyjwt==2.10.1
+ realtime==2.7.0
+ storage3==0.12.1
+ strenum==0.4.15
+ supabase==2.18.1
+ supabase-auth==2.12.3
+ supabase-functions==0.10.1
- typing-extensions==4.12.2 (from file:///C:/b/abs_0ffjxtihug/croot/typing_extensions_1734714875646/work)
~ typing-extensions==4.14.1
+ websockets==15.0.1# ๐ Connect to Supabase
from dotenv import load_dotenv
load_dotenv(override=True)
import os
from supabase import create_client, Client
# Get credentials from environment variables
supabase_url = os.getenv("SUPABASE_URL")
supabase_key = os.getenv("SUPABASE_KEY")
supabase: Client = create_client(supabase_url, supabase_key)2๏ธโฃ Database Schema & Table Creation
๐ Our Example Schema
Weโll create a cheese marketplace database with: - Suppliers - Companies that provide cheese - Cheese - Products with prices and supplier relationships - Users - Customers (for authentication examples)
๐ฏ Database Design Principles
- Primary Keys: Unique identifiers for each record
- Foreign Keys: Link tables together (relationships)
- Constraints: Ensure data quality (NOT NULL, CHECK, UNIQUE)
- Indexes: Speed up queries on frequently searched columns
๐ ๏ธ Table Creation SQL
Option 1: Run these queries in Supabase SQL Editor (Dashboard > SQL Editor)
Option 2: Execute via Python (shown in next cell)
-- Recommended: run in the Supabase SQL editor
-- SUPPLIERS
create table if not exists public.supplier (
id bigserial primary key,
created_at timestamptz not null default now(),
name text not null unique
);
-- CHEESES
create table if not exists public.cheese (
id bigserial primary key,
created_at timestamptz not null default now(),
-- store prices in integer cents (e.g., 550 = โฌ5.50)
price integer not null check (price >= 0),
name text not null,
supplier_id bigint,
constraint cheese_supplier_id_fkey
foreign key (supplier_id)
references public.supplier(id)
on delete set null
);
-- Helpful indexes
create index if not exists idx_cheese_supplier_id on public.cheese(supplier_id);
create index if not exists idx_cheese_price on public.cheese(price);import pandas as pdresponse = supabase.table("testing").select("*").execute()
print(pd.DataFrame(response.data)) name
0 barevresponse = (
supabase.table("cheese")
.insert({"id":509, "name": "Cheddar", "price": 509})
.execute()
)response = (
supabase.table("cheese")
.insert({"id": 2, "name": "Cheddar"})
.execute()
)response = supabase.table("supplier").select("*").execute()
# insert
response = (
supabase.table("supplier")
.insert({"id": 1, "name": "Supplier A"})
.execute()
)response = supabase.table("supplier").select("*").execute()
responseAPIResponse[~_ReturnT](data=[{'id': 1, 'created_at': '2025-08-19T00:36:25.140416+00:00', 'name': 'Supplier A'}], count=None)# ๐ง Create Artificial Data for Testing
# Insert cheeses
print("๐ง Inserting cheeses...")
cheeses_data = [
{"id": 3, "name": "Brie", "price": 1875, "supplier_id": 2},
{"id": 4, "name": "Camembert", "price": 1625, "supplier_id": 2},
{"id": 5, "name": "Emmental", "price": 2200, "supplier_id": 3},
{"id": 6, "name": "Gruyere", "price": 2850, "supplier_id": 3},
{"id": 7, "name": "Cheddar", "price": 1500, "supplier_id": 4},
{"id": 8, "name": "Gouda", "price": 1975, "supplier_id": 4}
]
supabase.table("cheese").insert(cheeses_data).execute()
print("โ
Cheeses inserted!")
print("๐ Artificial data ready for testing!")๐ง Inserting cheeses...
โ
Cheeses inserted!
๐ Artificial data ready for testing!print("๐ฆ Inserting suppliers...")
suppliers_data = [
{"id": 2, "name": "ิฟีธีพีซีฏีถีฅึ ีีิธ"},
{"id": 3, "name": "Swiss Dairy"},
{"id": 4, "name": "Local Farm"}
]
for supplier in suppliers_data:
supabase.table("supplier").insert(supplier).execute()
print("โ
Suppliers inserted!")๐ฆ Inserting suppliers...
โ
Suppliers inserted!
โ
Suppliers inserted!# ๐ FILTERING Examples
print("๐ FILTERING & SORTING EXAMPLES")
print("=" * 50)
# Filter 1: Price-based filtering
print("๐ฐ Premium cheeses (price > $7.00):")
expensive = supabase.table("cheese").select("name, price, category").gt("price", 700).execute()
for cheese in expensive.data:
print(f" โข {cheese['name']}: ${cheese['price']/100:.2f} ({cheese['category']})")
print()
# Filter 2: Category filtering
print("๐ง Hard cheeses:")
hard_cheese = supabase.table("cheese").select("name, price").eq("category", "Hard").execute()
for cheese in hard_cheese.data:
print(f" โข {cheese['name']}: ${cheese['price']/100:.2f}")
print()
# Filter 3: Price range filtering
print("๐ต Mid-range cheeses ($6.00 - $8.00):")
midrange = (supabase.table("cheese")
.select("name, price, category")
.gte("price", 600)
.lte("price", 800)
.execute())
for cheese in midrange.data:
print(f" โข {cheese['name']}: ${cheese['price']/100:.2f} ({cheese['category']})")
print()
# Filter 4: Multiple values with IN
print("๐ซ๐ท French cheese categories (Soft OR Blue):")
french_types = (supabase.table("cheese")
.select("name, price, category")
.in_("category", ["Soft", "Blue"])
.execute())
for cheese in french_types.data:
print(f" โข {cheese['name']}: ${cheese['price']/100:.2f} ({cheese['category']})")
print()
# Filter 5: Text search (case-insensitive)
print("๐ค Cheeses with 'em' in name:")
text_search = (supabase.table("cheese")
.select("name, price")
.ilike("name", "%em%") # Case-insensitive LIKE
.execute())
for cheese in text_search.data:
print(f" โข {cheese['name']}: ${cheese['price']/100:.2f}")
print()
# Filter 6: Sorting
print("๐ All cheeses sorted by price (ascending):")
sorted_cheese = (supabase.table("cheese")
.select("name, price, category")
.order("price", desc=False)
.execute())
for cheese in sorted_cheese.data:
print(f" โข {cheese['name']}: ${cheese['price']/100:.2f} ({cheese['category']})")
print("\n" + "=" * 50)# ๐ 2. GROUP BY Examples (using PostgreSQL functions)
print("๐ GROUP BY EXAMPLES")
print("=" * 50)
# Note: Supabase Python client doesn't have direct GROUP BY
# But we can use PostgreSQL functions with .rpc() or manual SQL
# Group By 1: Count cheeses per supplier
print("๐ญ Cheeses count per supplier:")
# We'll do this manually by getting data and processing
all_cheeses = supabase.table("cheese").select("supplier_id").execute()
supplier_counts = {}
for cheese in all_cheeses.data:
sid = cheese['supplier_id']
supplier_counts[sid] = supplier_counts.get(sid, 0) + 1
# Get supplier names
for supplier_id, count in supplier_counts.items():
supplier = supabase.table("supplier").select("name").eq("id", supplier_id).execute()
supplier_name = supplier.data[0]['name'] if supplier.data else f"Supplier {supplier_id}"
print(f" - {supplier_name}: {count} cheeses")
print()
# Group By 2: Average price per supplier
print("๐ฐ Average price per supplier:")
all_data = supabase.table("cheese").select("supplier_id, price").execute()
supplier_prices = {}
for cheese in all_data.data:
sid = cheese['supplier_id']
if sid not in supplier_prices:
supplier_prices[sid] = []
supplier_prices[sid].append(cheese['price'])
for supplier_id, prices in supplier_prices.items():
avg_price = sum(prices) / len(prices)
supplier = supabase.table("supplier").select("name").eq("id", supplier_id).execute()
supplier_name = supplier.data[0]['name'] if supplier.data else f"Supplier {supplier_id}"
print(f" - {supplier_name}: ${avg_price:.2f}")
print()
# Group By 3: Price ranges
print("๐ Cheese count by price range:")
price_ranges = {"$0-15": 0, "$15-20": 0, "$20-25": 0, "$25+": 0}
for cheese in all_data.data:
price = cheese['price']
if price < 15:
price_ranges["$0-15"] += 1
elif price < 20:
price_ranges["$15-20"] += 1
elif price < 25:
price_ranges["$20-25"] += 1
else:
price_ranges["$25+"] += 1
for range_name, count in price_ranges.items():
print(f" - {range_name}: {count} cheeses")
print("\n" + "=" * 50)# ๐ 3. JOIN Examples
print("๐ JOIN EXAMPLES")
print("=" * 50)
# Join 1: Get all cheeses with their supplier names
print("๐ง All cheeses with supplier information:")
# In Supabase, we use foreign table syntax for joins
cheese_with_suppliers = (supabase.table("cheese")
.select("id, name, price, supplier:supplier_id(name)")
.execute())
for item in cheese_with_suppliers.data:
supplier_name = item['supplier']['name'] if item['supplier'] else 'Unknown'
print(f" - {item['name']} (${item['price']}) from {supplier_name}")
print()
# Join 2: Get suppliers with their cheese count and total value
print("๐ญ Suppliers with cheese inventory:")
suppliers_with_cheeses = (supabase.table("supplier")
.select("id, name, cheese:cheese(name, price)")
.execute())
for supplier in suppliers_with_cheeses.data:
cheese_list = supplier['cheese']
cheese_count = len(cheese_list)
total_value = sum(cheese['price'] for cheese in cheese_list)
cheese_names = [cheese['name'] for cheese in cheese_list]
print(f" - {supplier['name']}:")
print(f" โข {cheese_count} cheeses")
print(f" โข Total value: ${total_value:.2f}")
print(f" โข Products: {', '.join(cheese_names)}")
print()
# Join 3: Get expensive cheeses (>$20) with supplier info
print("๐ Premium cheeses (>$20) with suppliers:")
premium_cheeses = (supabase.table("cheese")
.select("name, price, supplier:supplier_id(name)")
.gt("price", 20)
.execute())
for cheese in premium_cheeses.data:
supplier_name = cheese['supplier']['name'] if cheese['supplier'] else 'Unknown'
print(f" - {cheese['name']}: ${cheese['price']} from {supplier_name}")
print()
# Join 4: Filter by supplier and show their cheeses
print("๐ซ๐ท French supplier cheeses:")
french_cheeses = (supabase.table("cheese")
.select("name, price, supplier:supplier_id(name)")
.eq("supplier_id", 2) # French Fromagerie
.execute())
for cheese in french_cheeses.data:
print(f" - {cheese['name']}: ${cheese['price']}")
print("\n" + "=" * 50)
print("๐ All examples completed!")GPT
-- ๐ญ SUPPLIERS TABLE
-- Stores information about cheese suppliers/manufacturers
CREATE TABLE IF NOT EXISTS public.supplier (
id BIGSERIAL PRIMARY KEY, -- Auto-incrementing ID
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- Automatic timestamp
name TEXT NOT NULL UNIQUE, -- Company name (must be unique)
country TEXT DEFAULT 'Unknown', -- Country of origin
website TEXT, -- Optional website URL
contact_email TEXT -- Contact information
);
-- ๐ง CHEESE TABLE
-- Stores cheese products with pricing and supplier relationships
CREATE TABLE IF NOT EXISTS public.cheese (
id BIGSERIAL PRIMARY KEY, -- Auto-incrementing ID
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- Automatic timestamp
name TEXT NOT NULL, -- Cheese name
price INTEGER NOT NULL CHECK (price >= 0), -- Price in cents (e.g., 550 = $5.50)
description TEXT, -- Optional description
category TEXT DEFAULT 'Other', -- Cheese category
supplier_id BIGINT, -- Foreign key to supplier
-- Foreign key constraint with cascade options
CONSTRAINT cheese_supplier_fkey
FOREIGN KEY (supplier_id)
REFERENCES public.supplier(id)
ON DELETE SET NULL -- If supplier deleted, set to NULL
);
-- ๐ PERFORMANCE INDEXES
-- Speed up common queries
CREATE INDEX IF NOT EXISTS idx_cheese_supplier_id ON public.cheese(supplier_id);
CREATE INDEX IF NOT EXISTS idx_cheese_price ON public.cheese(price);
CREATE INDEX IF NOT EXISTS idx_cheese_category ON public.cheese(category);
CREATE INDEX IF NOT EXISTS idx_supplier_country ON public.supplier(country);
-- ๐ก COMMENTS FOR DOCUMENTATION
COMMENT ON TABLE public.supplier IS 'Cheese suppliers and manufacturers';
COMMENT ON TABLE public.cheese IS 'Cheese products with pricing and supplier info';
COMMENT ON COLUMN public.cheese.price IS 'Price stored in cents to avoid decimal issues';# ๐ ๏ธ Create Tables via Python (Alternative to SQL Editor)
def create_database_schema():
"""
Create database tables programmatically using Supabase
This is an alternative to running SQL in the Supabase dashboard
"""
# SQL for creating suppliers table
suppliers_sql = """
CREATE TABLE IF NOT EXISTS public.supplier (
id BIGSERIAL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
name TEXT NOT NULL UNIQUE,
country TEXT DEFAULT 'Unknown',
website TEXT,
contact_email TEXT
);
"""
# SQL for creating cheese table
cheese_sql = """
CREATE TABLE IF NOT EXISTS public.cheese (
id BIGSERIAL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
name TEXT NOT NULL,
price INTEGER NOT NULL CHECK (price >= 0),
description TEXT,
category TEXT DEFAULT 'Other',
supplier_id BIGINT,
CONSTRAINT cheese_supplier_fkey
FOREIGN KEY (supplier_id)
REFERENCES public.supplier(id)
ON DELETE SET NULL
);
"""
# SQL for creating indexes
indexes_sql = """
CREATE INDEX IF NOT EXISTS idx_cheese_supplier_id ON public.cheese(supplier_id);
CREATE INDEX IF NOT EXISTS idx_cheese_price ON public.cheese(price);
CREATE INDEX IF NOT EXISTS idx_cheese_category ON public.cheese(category);
CREATE INDEX IF NOT EXISTS idx_supplier_country ON public.supplier(country);
"""
try:
print("๐๏ธ Creating database schema...")
# Execute table creation SQL
supabase.postgrest.schema("public").rpc("sql", {"query": suppliers_sql}).execute()
print("โ
Suppliers table created")
supabase.postgrest.schema("public").rpc("sql", {"query": cheese_sql}).execute()
print("โ
Cheese table created")
supabase.postgrest.schema("public").rpc("sql", {"query": indexes_sql}).execute()
print("โ
Indexes created")
print("๐ Database schema setup complete!")
except Exception as e:
print(f"โ Error creating schema: {e}")
print("๐ก Try running the SQL directly in Supabase SQL Editor instead")
# Uncomment the line below to create tables
# create_database_schema()
print("๐ Table creation function defined!")
print("๐ก Uncomment the last line to create tables programmatically")
print("๐ง Or copy the SQL above to Supabase SQL Editor (recommended)")3๏ธโฃ CRUD Operations (Create, Read, Update, Delete)
๐ Basic Database Operations
- CREATE - Insert new records
- READ - Query and retrieve data
- UPDATE - Modify existing records
- DELETE - Remove records
๐ง Sample Data Setup
Letโs start by adding some sample suppliers and cheese products to work with.
from supabase import create_client, Client
import os
url = os.environ["SUPABASE_URL"]
key = os.environ["SUPABASE_KEY"] # or anon for read-only
supabase: Client = create_client(url, key)# ๐ญ CREATE: Insert Suppliers
# Sample supplier data with more details
suppliers_data = [
{
"id": 1,
"name": "ิฟีธีพีซีฏีถีฅึ ีีิธ",
"country": "Armenia",
"website": "https://kovikner.am",
"contact_email": "info@kovikner.am"
},
{
"id": 2,
"name": "French Fromagerie",
"country": "France",
"website": "https://fromagerie.fr",
"contact_email": "contact@fromagerie.fr"
},
{
"id": 3,
"name": "Swiss Alpine Dairy",
"country": "Switzerland",
"website": "https://alpinedairy.ch",
"contact_email": "orders@alpinedairy.ch"
}
]
try:
# Insert suppliers with error handling
print("๐ญ Inserting suppliers...")
response = supabase.table("supplier").insert(suppliers_data).execute()
print(f"โ
Successfully inserted {len(response.data)} suppliers!")
for supplier in response.data:
print(f" โข {supplier['name']} (ID: {supplier['id']})")
except Exception as e:
print(f"โ Error inserting suppliers: {e}")
print("๐ก Suppliers might already exist, or check your table schema")APIResponse[~_ReturnT](data=[{'id': 1, 'created_at': '2025-08-19T01:02:56.083173+00:00', 'name': 'ิฟีธีพีซีฏีถีฅึ ีีิธ'}, {'id': 2, 'created_at': '2025-08-19T01:02:56.083173+00:00', 'name': 'ีีีีีีีี ีิฒิธ'}, {'id': 3, 'created_at': '2025-08-19T01:02:56.083173+00:00', 'name': 'ีีธึีธึีฅึีซ ีฐีกีดีกีฏีธีพีกีฏีกีถ ีกีฝีธึีซึีกึีซีก'}], count=None)# ๐ง CREATE: Insert Cheese Products
# Diverse cheese data with categories and descriptions
cheese_data = [
{
"name": "Gouda",
"price": 550,
"supplier_id": 3,
"category": "Hard",
"description": "Classic Dutch cheese with a rich, nutty flavor"
},
{
"name": "ิผีธีผีซ",
"price": 850,
"supplier_id": 1,
"category": "Fresh",
"description": "Traditional Armenian cheese, perfect for breakfast"
},
{
"name": "Roquefort",
"price": 920,
"supplier_id": 2,
"category": "Blue",
"description": "Famous French blue cheese with sharp, tangy flavor"
},
{
"name": "Emmental",
"price": 690,
"supplier_id": 3,
"category": "Hard",
"description": "Swiss cheese with characteristic holes and mild taste"
},
{
"name": "Camembert",
"price": 780,
"supplier_id": 2,
"category": "Soft",
"description": "Creamy French cheese with white rind"
},
{
"name": "Chanakh",
"price": 750,
"supplier_id": 1,
"category": "Brined",
"description": "Traditional Armenian brined cheese"
}
]
try:
print("๐ง Inserting cheese products...")
response = supabase.table("cheese").insert(cheese_data).execute()
print(f"โ
Successfully inserted {len(response.data)} cheese products!")
for cheese in response.data:
price_dollars = cheese['price'] / 100
print(f" โข {cheese['name']}: ${price_dollars:.2f} ({cheese['category']})")
except Exception as e:
print(f"โ Error inserting cheeses: {e}")
print("๐ก Check if suppliers exist first, or table constraints")APIResponse[~_ReturnT](data=[{'id': 1, 'created_at': '2025-08-19T01:04:33.868746+00:00', 'price': 550, 'name': 'Gouda', 'supplier_id': 1}, {'id': 2, 'created_at': '2025-08-19T01:04:33.868746+00:00', 'price': 850, 'name': 'ิผีธีผีซ', 'supplier_id': 1}, {'id': 3, 'created_at': '2025-08-19T01:04:33.868746+00:00', 'price': 920, 'name': 'ีีกีถีกีญ', 'supplier_id': 2}, {'id': 4, 'created_at': '2025-08-19T01:04:33.868746+00:00', 'price': 890, 'name': 'ิฒีธึีขีธีฝีธีพ', 'supplier_id': 3}], count=None)# ๐ READ: Query Data
import pandas as pd
print("๐ READING DATA FROM TABLES")
print("=" * 50)
# 1. Get all cheeses
print("๐ง All cheese products:")
response = supabase.table("cheese").select("*").execute()
cheese_df = pd.DataFrame(response.data)
if not cheese_df.empty:
# Convert price from cents to dollars for display
cheese_df['price_dollars'] = cheese_df['price'] / 100
print(cheese_df[['id', 'name', 'price_dollars', 'category', 'supplier_id']].to_string(index=False))
else:
print("No cheese products found")
print("\n" + "-" * 30)
# 2. Get all suppliers
print("๐ญ All suppliers:")
response = supabase.table("supplier").select("*").execute()
supplier_df = pd.DataFrame(response.data)
if not supplier_df.empty:
print(supplier_df[['id', 'name', 'country', 'website']].to_string(index=False))
else:
print("No suppliers found")
print("\n" + "=" * 50) id created_at price name supplier_id
0 1 2025-08-19T01:04:33.868746+00:00 550 Gouda 1
1 2 2025-08-19T01:04:33.868746+00:00 850 ิผีธีผีซ 1
2 3 2025-08-19T01:04:33.868746+00:00 920 ีีกีถีกีญ 2
3 4 2025-08-19T01:04:33.868746+00:00 890 ิฒีธึีขีธีฝีธีพ 3
# Top 10 most expensive
r = (supabase.table("cheese")
.select("id,name,price")
.gt("price", 1000)
.in_("name", ["Gouda"])
.order("price", desc=True)
.range(0, 10)
.execute())
df = pd.DataFrame(r.data)
print(df) id name price
0 8 Gouda 1975# Alias "supplier" from the FK column supplier_id
r = (supabase.table("cheese")
.select("id,name,price,supplier:supplier_id(id,name)")
.execute())
print(pd.DataFrame(r.data)) id name price supplier
0 1 Gouda 550 {'id': 1, 'name': 'ิฟีธีพีซีฏีถีฅึ ีีิธ'}
1 2 ิผีธีผีซ 850 {'id': 1, 'name': 'ิฟีธีพีซีฏีถีฅึ ีีิธ'}
2 3 ีีกีถีกีญ 920 {'id': 2, 'name': 'ีีีีีีีี ีิฒิธ'}
3 4 ิฒีธึีขีธีฝีธีพ 890 {'id': 3, 'name': 'ีีธึีธึีฅึีซ ีฐีกีดีกีฏีธีพีกีฏีกีถ ีกีฝีธึีซึ...# Alias "supplier" from the FK column supplier_id
r = (supabase.table("cheese")
.select("id,name,price,supplier:supplier_id(name)")
.execute())
print(pd.DataFrame(r.data)) id name price supplier
0 1 Gouda 550 {'name': 'ิฟีธีพีซีฏีถีฅึ ีีิธ'}
1 2 ิผีธีผีซ 850 {'name': 'ิฟีธีพีซีฏีถีฅึ ีีิธ'}
2 3 ีีกีถีกีญ 920 {'name': 'ีีีีีีีี ีิฒิธ'}
3 4 ิฒีธึีขีธีฝีธีพ 890 {'name': 'ีีธึีธึีฅึีซ ีฐีกีดีกีฏีธีพีกีฏีกีถ ีกีฝีธึีซึีกึีซีก'}# Require a match using !inner
r = (supabase.table("cheese")
.select("id,name,price,supplier!inner(id,name)")
.execute())
print(pd.DataFrame(r.data)) id name price supplier
0 1 Gouda 550 {'id': 1, 'name': 'ิฟีธีพีซีฏีถีฅึ ีีิธ'}
1 2 ิผีธีผีซ 850 {'id': 1, 'name': 'ิฟีธีพีซีฏีถีฅึ ีีิธ'}
2 3 ีีกีถีกีญ 920 {'id': 2, 'name': 'ีีีีีีีี ีิฒิธ'}
3 4 ิฒีธึีขีธีฝีธีพ 890 {'id': 3, 'name': 'ีีธึีธึีฅึีซ ีฐีกีดีกีฏีธีพีกีฏีกีถ ีกีฝีธึีซึ...r = (supabase.table("cheese")
.select("id,name,price,supplier!inner(id,name)")
.eq("supplier.name", "ีีีีีีีี ีิฒิธ")
.execute())
print(pd.DataFrame(r.data)) id name price supplier
0 3 ีีกีถีกีญ 920 {'id': 2, 'name': 'ีีีีีีีี ีิฒิธ'}# โ๏ธ UPDATE & ๐๏ธ DELETE Operations
print("โ๏ธ UPDATE EXAMPLES")
print("=" * 30)
# Update a cheese price
try:
print("๐ฐ Updating Gouda price from $5.50 to $6.25...")
response = (supabase.table("cheese")
.update({"price": 625}) # $6.25 in cents
.eq("name", "Gouda")
.execute())
if response.data:
updated_cheese = response.data[0]
print(f"โ
Updated: {updated_cheese['name']} now costs ${updated_cheese['price']/100:.2f}")
else:
print("โ No cheese updated - check if 'Gouda' exists")
except Exception as e:
print(f"โ Update error: {e}")
print()
# Update supplier website
try:
print("๐ Updating supplier website...")
response = (supabase.table("supplier")
.update({"website": "https://kovikner-updated.am"})
.eq("name", "ิฟีธีพีซีฏีถีฅึ ีีิธ")
.execute())
if response.data:
updated_supplier = response.data[0]
print(f"โ
Updated: {updated_supplier['name']} website")
else:
print("โ No supplier updated")
except Exception as e:
print(f"โ Update error: {e}")
print("\n" + "๐๏ธ DELETE EXAMPLES")
print("=" * 30)
# Note: Be careful with DELETE operations!
print("โ ๏ธ DELETE operations are permanent!")
print("๐ก In production, consider 'soft deletes' (marking as inactive)")
print("๐ For this tutorial, we'll skip actual deletions to preserve data")
# Example of how you would delete (commented out):
# Delete a specific cheese
# response = supabase.table("cheese").delete().eq("name", "SomeCheeseToDelete").execute()
# Delete all cheeses from a supplier
# response = supabase.table("cheese").delete().eq("supplier_id", 999).execute()
print("๐ DELETE syntax examples shown above (commented out for safety)")APIResponse[~_ReturnT](data=[{'id': 2, 'created_at': '2025-08-19T01:02:56.083173+00:00', 'name': 'ีีีีีีีีีีีีีีีีีีีีีีีี ีิฒิธ'}], count=None)4๏ธโฃ Advanced Queries
๐ Filtering, Sorting & Joins
Master advanced database operations to build powerful applications.
Query Types Weโll Cover:
- Filtering - Find specific records with conditions
- Sorting - Order results by price, name, etc.
- Joining - Combine data from multiple tables
- Aggregation - Count, sum, average operations
- Pagination - Handle large datasets efficiently
๐ Supabase Authentication & Authorization
Supabase provides built-in authentication with email/password, OAuth providers, and Row Level Security (RLS).
response = supabase.auth.sign_up({
"email": "tarkhanyan02@gmail.com",
"password": "hndkahaver"
})
print(f"โ
User registered: {response.user.email}")
print(f"๐ User ID: {response.user.id}")
โ
User registered: tarkhanyan02@gmail.com
๐ User ID: 142df57a-fa4d-4c61-a760-403e29a04fa0response = supabase.auth.sign_in_with_password({
"email": "tarkhanyan02@gmail.com",
"password": "hndkahaver"
})
print(f"โ
User logged in: {response.user.email}")
print(f"๐ซ Access Token: {response.session.access_token[:20]}...")--------------------------------------------------------------------------- ConnectError Traceback (most recent call last) File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpx\_transports\default.py:101, in map_httpcore_exceptions() 100 try: --> 101 yield 102 except Exception as exc: File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpx\_transports\default.py:250, in HTTPTransport.handle_request(self, request) 249 with map_httpcore_exceptions(): --> 250 resp = self._pool.handle_request(req) 252 assert isinstance(resp.stream, typing.Iterable) File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpcore\_sync\connection_pool.py:256, in ConnectionPool.handle_request(self, request) 255 self._close_connections(closing) --> 256 raise exc from None 258 # Return the response. Note that in this case we still have to manage 259 # the point at which the response is closed. File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpcore\_sync\connection_pool.py:236, in ConnectionPool.handle_request(self, request) 234 try: 235 # Send the request on the assigned connection. --> 236 response = connection.handle_request( 237 pool_request.request 238 ) 239 except ConnectionNotAvailable: 240 # In some cases a connection may initially be available to 241 # handle a request, but then become unavailable. 242 # 243 # In this case we clear the connection and try again. File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpcore\_sync\connection.py:101, in HTTPConnection.handle_request(self, request) 100 self._connect_failed = True --> 101 raise exc 103 return self._connection.handle_request(request) File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpcore\_sync\connection.py:78, in HTTPConnection.handle_request(self, request) 77 if self._connection is None: ---> 78 stream = self._connect(request) 80 ssl_object = stream.get_extra_info("ssl_object") File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpcore\_sync\connection.py:124, in HTTPConnection._connect(self, request) 123 with Trace("connect_tcp", logger, request, kwargs) as trace: --> 124 stream = self._network_backend.connect_tcp(**kwargs) 125 trace.return_value = stream File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpcore\_backends\sync.py:207, in SyncBackend.connect_tcp(self, host, port, timeout, local_address, socket_options) 202 exc_map: ExceptionMapping = { 203 socket.timeout: ConnectTimeout, 204 OSError: ConnectError, 205 } --> 207 with map_exceptions(exc_map): 208 sock = socket.create_connection( 209 address, 210 timeout, 211 source_address=source_address, 212 ) File c:\Users\hayk_\.conda\envs\lectures\lib\contextlib.py:153, in _GeneratorContextManager.__exit__(self, typ, value, traceback) 152 try: --> 153 self.gen.throw(typ, value, traceback) 154 except StopIteration as exc: 155 # Suppress StopIteration *unless* it's the same exception that 156 # was passed to throw(). This prevents a StopIteration 157 # raised inside the "with" statement from being suppressed. File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpcore\_exceptions.py:14, in map_exceptions(map) 13 if isinstance(exc, from_exc): ---> 14 raise to_exc(exc) from exc 15 raise ConnectError: [Errno 11001] getaddrinfo failed The above exception was the direct cause of the following exception: ConnectError Traceback (most recent call last) File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\supabase_auth\_sync\gotrue_base_api.py:113, in SyncGoTrueBaseAPI._request(self, method, path, jwt, redirect_to, headers, query, body, no_resolve_json, xform) 112 try: --> 113 response = self._http_client.request( 114 method, 115 url, 116 headers=headers, 117 params=query, 118 json=model_dump(body) if isinstance(body, BaseModel) else body, 119 ) 120 response.raise_for_status() File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpx\_client.py:825, in Client.request(self, method, url, content, data, files, json, params, headers, cookies, auth, follow_redirects, timeout, extensions) 812 request = self.build_request( 813 method=method, 814 url=url, (...) 823 extensions=extensions, 824 ) --> 825 return self.send(request, auth=auth, follow_redirects=follow_redirects) File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpx\_client.py:914, in Client.send(self, request, stream, auth, follow_redirects) 912 auth = self._build_request_auth(request, auth) --> 914 response = self._send_handling_auth( 915 request, 916 auth=auth, 917 follow_redirects=follow_redirects, 918 history=[], 919 ) 920 try: File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpx\_client.py:942, in Client._send_handling_auth(self, request, auth, follow_redirects, history) 941 while True: --> 942 response = self._send_handling_redirects( 943 request, 944 follow_redirects=follow_redirects, 945 history=history, 946 ) 947 try: File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpx\_client.py:979, in Client._send_handling_redirects(self, request, follow_redirects, history) 977 hook(request) --> 979 response = self._send_single_request(request) 980 try: File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpx\_client.py:1014, in Client._send_single_request(self, request) 1013 with request_context(request=request): -> 1014 response = transport.handle_request(request) 1016 assert isinstance(response.stream, SyncByteStream) File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpx\_transports\default.py:249, in HTTPTransport.handle_request(self, request) 237 req = httpcore.Request( 238 method=request.method, 239 url=httpcore.URL( (...) 247 extensions=request.extensions, 248 ) --> 249 with map_httpcore_exceptions(): 250 resp = self._pool.handle_request(req) File c:\Users\hayk_\.conda\envs\lectures\lib\contextlib.py:153, in _GeneratorContextManager.__exit__(self, typ, value, traceback) 152 try: --> 153 self.gen.throw(typ, value, traceback) 154 except StopIteration as exc: 155 # Suppress StopIteration *unless* it's the same exception that 156 # was passed to throw(). This prevents a StopIteration 157 # raised inside the "with" statement from being suppressed. File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\httpx\_transports\default.py:118, in map_httpcore_exceptions() 117 message = str(exc) --> 118 raise mapped_exc(message) from exc ConnectError: [Errno 11001] getaddrinfo failed During handling of the above exception, another exception occurred: AuthRetryableError Traceback (most recent call last) Cell In[34], line 1 ----> 1 response = supabase.auth.sign_in_with_password({ 2 "email": "tarkhanyan02@gmail.com", 3 "password": "hndkahaver" 4 }) 6 print(f"โ User logged in: {response.user.email}") 7 print(f"๐ซ Access Token: {response.session.access_token[:20]}...") File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\supabase_auth\_sync\gotrue_client.py:271, in SyncGoTrueClient.sign_in_with_password(self, credentials) 269 captcha_token = options.get("captcha_token") 270 if email: --> 271 response = self._request( 272 "POST", 273 "token", 274 body={ 275 "email": email, 276 "password": password, 277 "data": data, 278 "gotrue_meta_security": { 279 "captcha_token": captcha_token, 280 }, 281 }, 282 query={ 283 "grant_type": "password", 284 }, 285 xform=parse_auth_response, 286 ) 287 elif phone: 288 response = self._request( 289 "POST", 290 "token", (...) 302 xform=parse_auth_response, 303 ) File c:\Users\hayk_\.conda\envs\lectures\lib\site-packages\supabase_auth\_sync\gotrue_base_api.py:125, in SyncGoTrueBaseAPI._request(self, method, path, jwt, redirect_to, headers, query, body, no_resolve_json, xform) 123 return xform(result) 124 except Exception as e: --> 125 raise handle_exception(e) AuthRetryableError: [Errno 11001] getaddrinfo failed
# ๐ 1. User Registration and Authentication
# User sign up with email and password
def register_user(email, password):
try:
response = supabase.auth.sign_up({
"email": email,
"password": password
})
if response.user:
print(f"โ
User registered: {response.user.email}")
print(f"๐ User ID: {response.user.id}")
return response.user
else:
print("โ Registration failed")
return None
except Exception as e:
print(f"โ Registration error: {e}")
return None
# User sign in
def login_user(email, password):
try:
response = supabase.auth.sign_in_with_password({
"email": email,
"password": password
})
if response.user:
print(f"โ
User logged in: {response.user.email}")
print(f"๐ซ Access Token: {response.session.access_token[:20]}...")
return response.user
else:
print("โ Login failed")
return None
except Exception as e:
print(f"โ Login error: {e}")
return None
# Example usage (commented out to avoid actual registration)
# register_user("test@example.com", "secure_password123")
# login_user("test@example.com", "secure_password123")
print("๐ Authentication functions defined!")
print("๐ก Uncomment the lines above to test registration/login")# ๐ซ 2. Session Management
# Get current user
def get_current_user():
try:
user = supabase.auth.get_user()
if user.user:
print(f"๐ค Current user: {user.user.email}")
print(f"๐ User ID: {user.user.id}")
print(f"๐
Created: {user.user.created_at}")
return user.user
else:
print("โ No authenticated user")
return None
except Exception as e:
print(f"โ Error getting user: {e}")
return None
# Get current session
def get_current_session():
try:
session = supabase.auth.get_session()
if session.session:
print(f"๐ซ Session active")
print(f"๐ Access token: {session.session.access_token[:20]}...")
print(f"โฐ Expires at: {session.session.expires_at}")
return session.session
else:
print("โ No active session")
return None
except Exception as e:
print(f"โ Error getting session: {e}")
return None
# Sign out
def logout_user():
try:
supabase.auth.sign_out()
print("โ
User logged out successfully")
except Exception as e:
print(f"โ Logout error: {e}")
# Check authentication status
print("๐ Checking authentication status...")
current_user = get_current_user()
current_session = get_current_session()# ๐ 3. Authenticated Requests
# Function to make authenticated requests
def authenticated_request_example():
"""
When a user is logged in, their session token is automatically
included in all Supabase requests
"""
# Check if user is authenticated
current_user = supabase.auth.get_user()
if not current_user.user:
print("โ User not authenticated!")
print("๐ก Please log in first to make authenticated requests")
return
print(f"๐ค Making request as: {current_user.user.email}")
# Example: Insert cheese with user context
try:
# This will respect RLS policies
response = supabase.table("cheese").insert({
"name": "User's Special Cheese",
"price": 1999,
"supplier_id": 1
}).execute()
print("โ
Authenticated insert successful")
print(f"๐ Inserted: {response.data}")
except Exception as e:
print(f"โ Authenticated request failed: {e}")
print("๐ก This might be due to RLS policies")
# Example: Conditional queries based on auth
def user_specific_data():
current_user = supabase.auth.get_user()
if current_user.user:
# User is authenticated - can see more data
response = supabase.table("cheese").select("*").execute()
print(f"๐ Authenticated user sees {len(response.data)} cheeses")
else:
# Anonymous user - limited access
response = supabase.table("cheese").select("name, price").limit(3).execute()
print(f"๐ค Anonymous user sees {len(response.data)} cheeses (limited)")
return response.data
# Test authenticated requests
print("๐งช Testing authenticated requests...")
authenticated_request_example()
user_data = user_specific_data()6๏ธโฃ Real-time Features
โก Live Data Updates
Supabase provides real-time subscriptions to database changes:
# Subscribe to cheese table changes
def handle_changes(payload):
print(f"Change detected: {payload}")
# Listen for all changes
supabase.table("cheese").on("*", handle_changes).subscribe()
# Listen for specific events
supabase.table("cheese").on("INSERT", handle_changes).subscribe()
supabase.table("cheese").on("UPDATE", handle_changes).subscribe()
supabase.table("cheese").on("DELETE", handle_changes).subscribe()๐ Use Cases for Real-time:
- Live dashboards - Stock levels, sales metrics
- Collaborative apps - Multiple users editing data
- Notifications - Alert users when data changes
- Chat applications - Real-time messaging
- Gaming - Live scoreboards and updates
๐ก Real-time Architecture:
- Uses WebSocket connections
- PostgreSQL triggers detect changes
- Pub/Sub system broadcasts updates
- Automatic reconnection handling
๐ฏ Next Steps & Best Practices
๐ Production Considerations
Security
- Enable Row Level Security (RLS) policies
- Use environment variables for API keys
- Implement proper authentication flows
- Set up database backups
Performance
- Add indexes for frequently queried columns
- Use connection pooling for high-traffic apps
- Implement caching strategies (Redis, etc.)
- Monitor query performance and optimize
Scaling
- Use edge functions for serverless logic
- Implement horizontal scaling strategies
- Consider read replicas for read-heavy workloads
- Plan data archiving for historical data
๐ Additional Resources
Advanced Features to Explore:
- Edge Functions - Serverless functions at the edge
- Storage - File uploads and management
- PostgREST - Auto-generated REST APIs
- GraphQL - Alternative API interface
- Webhooks - External system integrations
Supabase Ecosystem:
- Dashboard - Visual database management
- CLI tools - Command-line development
- Migration system - Database schema versioning
- Extensions - PostgreSQL extensions support
๐ Congratulations!
Youโve completed the Supabase tutorial! You now know how to: - โ
Set up Supabase projects and connections - โ
Create database schemas and tables - โ
Perform CRUD operations - โ
Write advanced queries with filtering and joins
- โ
Implement authentication and authorization - โ
Understand real-time capabilities
Keep building and exploring! ๐
๐ฒ 34 (16)
- โถ๏ธีีฝีฏีฅ ึีฌีซีฏีจ
- ๐Random link
- ๐ฆ๐ฒ๐ถHigh (ีึีจี ีฟีกึีซ)
- ๐๐ถGilbert OโSullivan - Alone Again
- ๐คิฟีกึีฃีซีถ