Multi-Schema Setup

Organize your database with multiple schemas.

Why Multiple Schemas?

Schemas help organize large databases:

Separation of concerns - auth, billing, app data
Access control - Different permissions per schema
Name collision avoidance - Same table names in different contexts
Logical grouping - Related tables together

Creating Schemas

-- Create schemas
CREATE SCHEMA auth;
CREATE SCHEMA billing;
CREATE SCHEMA app;

Migration Structure

Organize migrations by schema:

migrations/
├── public/
│   ├── 20260125_create_users.up.sql
│   └── 20260125_create_users.down.sql
├── auth/
│   ├── 20260125_create_sessions.up.sql
│   └── 20260125_create_sessions.down.sql
└── billing/
    ├── 20260125_create_subscriptions.up.sql
    └── 20260125_create_subscriptions.down.sql

Running Migrations

Per Schema

# Apply public schema
postbase migrate up --schema public
 
# Apply auth schema
postbase migrate up --schema auth
 
# Apply billing schema
postbase migrate up --schema billing

All Schemas

# Apply all schemas in order
for schema in public auth billing; do
  postbase migrate up --schema $schema
done

Example Schema: Auth

-- migrations/auth/20260125_create_sessions.up.sql
 
CREATE SCHEMA IF NOT EXISTS auth;
 
CREATE TABLE auth.users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  email_verified BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
CREATE TABLE auth.sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id INTEGER NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  token_hash VARCHAR(255) NOT NULL,
  expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
CREATE INDEX idx_sessions_user_id ON auth.sessions(user_id);
CREATE INDEX idx_sessions_expires_at ON auth.sessions(expires_at);

Example Schema: Billing

-- migrations/billing/20260125_create_subscriptions.up.sql
 
CREATE SCHEMA IF NOT EXISTS billing;
 
CREATE TABLE billing.plans (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price_cents INTEGER NOT NULL,
  features JSONB DEFAULT '[]',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
CREATE TABLE billing.subscriptions (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,  -- References auth.users
  plan_id INTEGER NOT NULL REFERENCES billing.plans(id),
  status VARCHAR(50) DEFAULT 'active',
  current_period_start TIMESTAMP WITH TIME ZONE NOT NULL,
  current_period_end TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
CREATE INDEX idx_subscriptions_user_id ON billing.subscriptions(user_id);

Querying Across Schemas

Direct Query

SELECT 
  u.email,
  s.status,
  p.name as plan_name
FROM auth.users u
JOIN billing.subscriptions s ON s.user_id = u.id
JOIN billing.plans p ON p.id = s.plan_id
WHERE u.id = 1;

SDK with Schema

// Query specific schema
const users = await db
  .from('auth.users')
  .eq('email_verified', true)
  .execute()
 
// Query billing
const subscriptions = await db
  .from('billing.subscriptions')
  .eq('user_id', 1)
  .execute()

Type Generation

Generate types for each schema:

# Generate for all schemas
postbase types generate \
  --database myapp \
  --schema public \
  --output ./src/db/types/public.ts
 
postbase types generate \
  --database myapp \
  --schema auth \
  --output ./src/db/types/auth.ts
 
postbase types generate \
  --database myapp \
  --schema billing \
  --output ./src/db/types/billing.ts

Unified Types

Create a combined type file:

// src/db/types/index.ts
import type { Database as PublicDB } from './public'
import type { Database as AuthDB } from './auth'
import type { Database as BillingDB } from './billing'
 
export interface Database {
  public: PublicDB['public']
  auth: AuthDB['auth']
  billing: BillingDB['billing']
}

Search Path

Set default schema search order:

-- Set for session
SET search_path TO app, public;
 
-- Set for role
ALTER ROLE myapp_user SET search_path TO app, public;

Schema Permissions

Create Role per Schema

-- Auth schema role
CREATE ROLE auth_admin;
GRANT ALL ON SCHEMA auth TO auth_admin;
GRANT ALL ON ALL TABLES IN SCHEMA auth TO auth_admin;
 
-- Billing schema role
CREATE ROLE billing_admin;
GRANT ALL ON SCHEMA billing TO billing_admin;
GRANT ALL ON ALL TABLES IN SCHEMA billing TO billing_admin;
 
-- App role (read-only on auth/billing)
CREATE ROLE app_user;
GRANT USAGE ON SCHEMA auth TO app_user;
GRANT SELECT ON auth.users TO app_user;
GRANT USAGE ON SCHEMA billing TO app_user;
GRANT SELECT ON billing.subscriptions TO app_user;

Best Practices

1. Schema Naming

Use lowercase: auth, billing, app
Keep names short but descriptive
Avoid generic names like data or misc

2. Cross-Schema References

Prefer soft references for flexibility:

-- Soft reference (user_id without FK)
CREATE TABLE billing.subscriptions (
  user_id INTEGER NOT NULL,  -- No FK to auth.users
  ...
);

3. Migration Order

Define clear dependency order:

# Order matters for foreign keys
SCHEMAS="public auth billing app"
 
for schema in $SCHEMAS; do
  postbase migrate up --schema $schema
done

4. Consistent Naming

Use consistent column names across schemas:

-- All tables use same pattern
user_id INTEGER NOT NULL  -- Always user_id, not uid or user
created_at TIMESTAMP      -- Always created_at, not created