Complete Guide to Supabase: Creating Tables, Schemas, and Authentication with Python & JavaScript

What is Supabase?

Supabase is an open-source Backend-as-a-Service (BaaS) that provides:

PostgreSQL Database: Full-featured SQL database with ACID compliance
Auto-generated APIs: RESTful APIs and GraphQL endpoints automatically created from your schema
Real-time subscriptions: Live data updates across clients
Authentication: Built-in user management with multiple providers
Row Level Security (RLS): Fine-grained security policies
Edge Functions: Server-side logic with Deno runtime

Setting Up Your Supabase Project

1. Create a New Project

First, head to supabase.com and create a new project:

# Install Supabase CLI
npm install -g supabase
 
# Login to your account
supabase login
 
# Initialize your project
supabase init

2. Get Your Project Credentials

Navigate to your project settings and note down:

Project URL: https://your-project.supabase.co
API Key (anon): Public key for client-side usage
API Key (service): Private key for server-side operations

Creating Tables and Schema Design

Database Schema Planning

Let's create a comprehensive e-commerce-like schema with proper relationships:

-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 
-- Users profile table (extends Supabase auth.users)
CREATE TABLE profiles (
  id UUID REFERENCES auth.users(id) PRIMARY KEY,
  username TEXT UNIQUE,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
-- Products table
CREATE TABLE products (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  category_id UUID REFERENCES categories(id),
  image_url TEXT,
  in_stock BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
-- Categories table
CREATE TABLE categories (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
-- Orders table
CREATE TABLE orders (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
-- Order items table
CREATE TABLE order_items (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
  product_id UUID REFERENCES products(id),
  quantity INTEGER NOT NULL DEFAULT 1,
  price_at_time DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Setting Up Row Level Security (RLS)

RLS is crucial for securing your data. Here's how to implement it:

-- Enable RLS on all tables
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;
 
-- Profiles policies
CREATE POLICY "Users can view their own profile" ON profiles
  FOR SELECT USING (auth.uid() = id);
 
CREATE POLICY "Users can update their own profile" ON profiles
  FOR UPDATE USING (auth.uid() = id);
 
CREATE POLICY "Users can insert their own profile" ON profiles
  FOR INSERT WITH CHECK (auth.uid() = id);
 
-- Products policies (public read, admin write)
CREATE POLICY "Anyone can view products" ON products
  FOR SELECT USING (true);
 
CREATE POLICY "Authenticated users can view categories" ON categories
  FOR SELECT USING (true);
 
-- Orders policies (users can only see their own orders)
CREATE POLICY "Users can view their own orders" ON orders
  FOR SELECT USING (auth.uid() = user_id);
 
CREATE POLICY "Users can insert their own orders" ON orders
  FOR INSERT WITH CHECK (auth.uid() = user_id);
 
-- Order items policies
CREATE POLICY "Users can view their own order items" ON order_items
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM orders 
      WHERE orders.id = order_items.order_id 
      AND orders.user_id = auth.uid()
    )
  );

Authentication Setup

1. Configure Authentication Providers

In your Supabase dashboard, navigate to Authentication > Settings and configure your preferred providers:

Email/Password: Enable email confirmations
Social Providers: Google, GitHub, Discord, etc.
Magic Links: Passwordless authentication

2. Set Up Email Templates

Customize your email templates for:

Email confirmation
Password reset
Magic link

JavaScript Integration

Installation and Setup

npm install @supabase/supabase-js

Basic Configuration

// supabaseClient.js
import { createClient } from '@supabase/supabase-js'
 
const supabaseUrl = 'https://your-project.supabase.co'
const supabaseAnonKey = 'your-anon-key'
 
export const supabase = createClient(supabaseUrl, supabaseAnonKey)

Authentication with JavaScript

// auth.js
import { supabase } from './supabaseClient.js'
 
// Sign up with email and password
export async function signUp(email, password, userData = {}) {
  try {
    const { data, error } = await supabase.auth.signUp({
      email,
      password,
      options: {
        data: userData // Additional user metadata
      }
    })
    
    if (error) throw error
    return data
  } catch (error) {
    console.error('Error signing up:', error.message)
    throw error
  }
}
 
// Sign in with email and password
export async function signIn(email, password) {
  try {
    const { data, error } = await supabase.auth.signInWithPassword({
      email,
      password
    })
    
    if (error) throw error
    return data
  } catch (error) {
    console.error('Error signing in:', error.message)
    throw error
  }
}
 
// Get current user
export async function getCurrentUser() {
  try {
    const { data: { user }, error } = await supabase.auth.getUser()
    if (error) throw error
    return user
  } catch (error) {
    console.error('Error getting current user:', error.message)
    return null
  }
}

Python Integration

Installation and Setup

pip install supabase python-dotenv

Basic Configuration

# supabase_client.py
import os
from supabase import create_client, Client
from dotenv import load_dotenv
 
load_dotenv()
 
url: str = os.environ.get("SUPABASE_URL")
key: str = os.environ.get("SUPABASE_ANON_KEY")
 
supabase: Client = create_client(url, key)
 
# For server-side operations with service key
service_key: str = os.environ.get("SUPABASE_SERVICE_KEY")
admin_supabase: Client = create_client(url, service_key)

Authentication with Python

# auth.py
from supabase_client import supabase
from typing import Dict, Any, Optional
 
class AuthManager:
    def __init__(self):
        self.client = supabase
    
    def sign_up(self, email: str, password: str, user_data: Optional[Dict] = None) -> Dict[str, Any]:
        """Sign up a new user"""
        try:
            response = self.client.auth.sign_up({
                "email": email,
                "password": password,
                "options": {
                    "data": user_data or {}
                }
            })
            return response
        except Exception as e:
            print(f"Error signing up: {e}")
            raise e
    
    def sign_in(self, email: str, password: str) -> Dict[str, Any]:
        """Sign in an existing user"""
        try:
            response = self.client.auth.sign_in_with_password({
                "email": email,
                "password": password
            })
            return response
        except Exception as e:
            print(f"Error signing in: {e}")
            raise e
 
# Usage
auth = AuthManager()

Best Practices

1. Security Best Practices

Always enable RLS: Never expose tables without proper row-level security
Use service key carefully: Only use service key for admin operations on the server
Validate input: Always validate and sanitize user input
Implement rate limiting: Protect against abuse
Regular security audits: Review your RLS policies regularly

2. Performance Optimization

-- Add indexes for frequently queried columns
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_products_created_at ON products(created_at DESC);
 
-- Use composite indexes for complex queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Conclusion

Supabase provides an incredibly powerful and flexible platform for building modern applications. With its PostgreSQL foundation, automatic API generation, real-time capabilities, and robust authentication system, you can build production-ready applications quickly.

The combination of SQL flexibility, real-time subscriptions, and easy client integration makes Supabase an excellent choice for developers who want the power of a traditional database with the convenience of a modern BaaS platform.

Remember to always prioritize security with proper RLS policies, optimize for performance with appropriate indexes, and follow best practices for error handling and monitoring.

Start building with Supabase today, and experience the power of open-source backend infrastructure!