Databases - Astrio

Database Integration

Astrio provides seamless integration with Supabase, the open-source Firebase alternative built on PostgreSQL. Get a powerful, scalable database with real-time features, authentication, and storage all in one platform.

Why Supabase?

PostgreSQL Backend

Built on the world’s most advanced open-source database

Real-time Features

Live data updates with subscriptions

Built-in Auth

User authentication and authorization

File Storage

Secure file uploads and management

Supabase Integration

Getting Started with Supabase

Create Supabase Project

Get API Keys

Copy your project URL and anon/public key from the API settings

Connect in Astrio

Add Supabase integration with your project details

Create Tables

Set up your database schema in Supabase dashboard

Project Configuration

Required Credentials:

// Supabase configuration
const supabaseConfig = {
  url: 'https://your-project.supabase.co',
  anonKey: 'your-anon-key',
  serviceRoleKey: 'your-service-role-key' // For admin operations
}

Environment Variables:

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_ANON_KEY=your-anon-key
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key

Database Features

PostgreSQL Database

Leverage the full power of PostgreSQL with Supabase: SQL Support:

Full SQL - Complete PostgreSQL functionality
JSON Data Types - Store and query JSON efficiently
Geographic Data - PostGIS support for location features
Extensions - Rich ecosystem of database extensions

Example Queries:

-- Create a users table
CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Insert data
INSERT INTO users (email, name) VALUES ('john@example.com', 'John Doe');

-- Query with JSON
SELECT * FROM users WHERE metadata->>'preferences' = 'dark_mode';

Real-time Subscriptions

Get live updates when data changes:

// Subscribe to table changes
const subscription = supabase
  .from('users')
  .on('INSERT', payload => {
    console.log('New user:', payload.new)
  })
  .on('UPDATE', payload => {
    console.log('User updated:', payload.new)
  })
  .on('DELETE', payload => {
    console.log('User deleted:', payload.old)
  })
  .subscribe()

Real-time Features:

Live Updates - Instant data synchronization
Filtered Subscriptions - Subscribe to specific data changes
Presence - Track user online status
Broadcasting - Send messages to connected clients

Row Level Security (RLS)

Secure your data with fine-grained access control:

-- Enable RLS on users table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Policy for users to see only their own data
CREATE POLICY "Users can view own data" ON users
  FOR SELECT USING (auth.uid() = id);

-- Policy for public read access to profiles
CREATE POLICY "Public profiles are viewable" ON users
  FOR SELECT USING (is_public = true);

Security Features:

User-Based Access - Control access by authenticated user
Role-Based Policies - Different permissions for different roles
Public/Private Data - Mix public and private data in same table
Automatic Filtering - Queries automatically filtered by policies

Authentication Integration

Built-in Authentication

Supabase provides comprehensive authentication out of the box: Supported Providers:

Email/Password - Traditional email authentication
Magic Links - Passwordless email authentication
Phone Authentication - SMS-based verification
OAuth Providers - Google, GitHub, and more

User Management:

// Sign up a new user
const { user, error } = await supabase.auth.signUp({
  email: 'user@example.com',
  password: 'secure-password'
})

// Sign in existing user
const { user, error } = await supabase.auth.signIn({
  email: 'user@example.com',
  password: 'secure-password'
})

// Get current user
const { user } = await supabase.auth.getUser()

File Storage

Supabase Storage

Store and serve files securely: Storage Features:

File Uploads - Drag-and-drop file uploads
Image Transformations - Resize, crop, and optimize images
CDN Delivery - Fast global file delivery
Access Control - Secure file access with RLS

File Operations:

// Upload a file
const { data, error } = await supabase.storage
  .from('avatars')
  .upload('user-avatar.jpg', file)

// Get public URL
const { data } = supabase.storage
  .from('avatars')
  .getPublicUrl('user-avatar.jpg')

// Download a file
const { data, error } = await supabase.storage
  .from('documents')
  .download('document.pdf')

Database Management

Schema Management

Manage your database schema through Supabase: Table Creation:

-- Example: E-commerce schema
CREATE TABLE products (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  category TEXT,
  image_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  total_amount DECIMAL(10,2) NOT NULL,
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Indexes and Performance:

-- Create indexes for better performance
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

Data Migration

Import and export data easily: CSV Import:

-- Import data from CSV
COPY products(name, description, price, category)
FROM '/path/to/products.csv'
WITH (FORMAT csv, HEADER true);

Data Export:

-- Export data to CSV
COPY products TO '/path/to/export.csv'
WITH (FORMAT csv, HEADER true);

Security and Compliance

Data Protection

Enterprise-grade security for your data:

Encryption

AES-256 encryption for data at rest and in transit

Access Control

Row Level Security and role-based permissions

Audit Logs

Complete audit trail of all database operations

Backups

Automatic daily backups with point-in-time recovery

Compliance

Meet regulatory requirements:

GDPR Compliance - European data protection regulations
SOC 2 Type II - Security and availability controls
HIPAA Ready - Healthcare data protection (Enterprise)
ISO 27001 - Information security management

Performance Optimization

Query Optimization

Optimize your database performance: Best Practices:

Use Indexes - Create indexes on frequently queried columns
Limit Results - Use LIMIT to restrict result sets
Select Specific Columns - Avoid SELECT * for large tables
Use Pagination - Implement cursor-based pagination

Performance Monitoring:

-- Check slow queries
SELECT query, mean_time, calls
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Connection Pooling

Efficient database connection management:

Automatic Pooling - Supabase handles connection pooling
Connection Limits - Prevents overwhelming the database
Health Checks - Monitors connection health
Load Balancing - Distributes queries efficiently

Getting Started

Quick Setup

Get Supabase connected in minutes:

Create Project

Get Credentials

Copy your project URL and API keys

Connect in Astrio

Add Supabase integration with your credentials

Create Schema

Set up your database tables and relationships

Example Application

Build a simple user management system:

// Create a user
const { data, error } = await supabase
  .from('users')
  .insert([
    { 
      email: 'john@example.com',
      name: 'John Doe',
      metadata: { preferences: { theme: 'dark' } }
    }
  ])

// Query users with real-time updates
const { data, error } = await supabase
  .from('users')
  .select('*')
  .order('created_at', { ascending: false })

Migration Support

Need help migrating your existing database?

Our database experts can help you migrate from any database system to Supabase. Contact support@astrio.app for migration assistance.

Start with the Supabase dashboard to explore your data and test queries before integrating with Astrio.

Always test your Row Level Security policies thoroughly to ensure data access is properly restricted.

Integrations

​Database Integration

​Why Supabase?

PostgreSQL Backend

Real-time Features

Built-in Auth

File Storage

​Supabase Integration

​Getting Started with Supabase

​Project Configuration

​Database Features

​PostgreSQL Database

​Real-time Subscriptions

​Row Level Security (RLS)

​Authentication Integration

​Built-in Authentication

​File Storage

​Supabase Storage

​Database Management

​Schema Management

​Data Migration

​Security and Compliance

​Data Protection

Encryption

Access Control

Audit Logs

Backups

​Compliance

​Performance Optimization

​Query Optimization

​Connection Pooling

​Getting Started

​Quick Setup

​Example Application

​Migration Support

Database Integration

Why Supabase?

Supabase Integration

Getting Started with Supabase

Project Configuration

Database Features

PostgreSQL Database

Real-time Subscriptions

Row Level Security (RLS)

Authentication Integration

Built-in Authentication

File Storage

Supabase Storage

Database Management

Schema Management

Data Migration

Security and Compliance

Data Protection

Compliance

Performance Optimization

Query Optimization

Connection Pooling

Getting Started

Quick Setup

Example Application

Migration Support