Multi-Domain Database Support

Domains

Domains allow you to organize your database schemas by business domain or tenant type:

database/schemas/
├── public/          # System-wide schemas (users, organizations, etc.)
├── tenant/          # Tenant-specific schemas (orders, products, etc.)
├── factory/         # Factory-specific schemas (machines, production, etc.)
└── marketing/       # Marketing-specific schemas (campaigns, analytics, etc.)

database/migrations/
├── public/          # Migrations for public domain
├── tenant/          # Migrations for tenant domain
├── factory/         # Migrations for factory domain
└── marketing/       # Migrations for marketing domain

Model Generation

By default, each domain gets a model prefix:

• public → no prefix (User, Organization)
• tenant → Tenant prefix (TenantOrder, TenantProduct)
• factory → Factory prefix (FactoryMachine, FactoryProduction)
• marketing → Marketing prefix (MarketingCampaign)

export default {
  modelNaming: {
    public: '',           // No prefix for public models
    tenant: 'Tenant',     // Custom prefix for tenant models
    factory: 'Plant',     // Custom prefix for factory models
    marketing: null,      // No prefix for marketing models
  }
}

Overview

• Complete data isolation between tenants
• Transparent operation - existing queries work without modification
• Shared connection pooling for efficiency
• Schema-per-tenant architecture
• AsyncLocalStorage for automatic context propagation

Configuration

Enable multi-tenant mode in your database configuration:

// config/database.ts
export default {
  // Standard database configuration
  host: env('DB_HOST', 'localhost'),
  port: env('DB_PORT', 5432),
  database: env('DB_DATABASE', 'myapp'),
  username: env('DB_USER', 'postgres'),
  password: env('DB_PASSWORD', ''),

  // Multi-tenant configuration
  multiTenant: {
    enabled: true,
  },
}

Basic Usage

Setting Tenant Context

import { withSchema } from '@strav/database'
import { User } from '../models/User'

// All database operations within this block use company_123 schema
await withSchema('company_123', async () => {
  // ORM queries
  const users = await User.all()
  const admin = await User.findBy('role', 'admin')

  // Query builder
  const orders = await query(Order)
    .where('status', 'pending')
    .orderBy('created_at', 'DESC')
    .all()

  // Raw SQL
  const stats = await sql`
    SELECT COUNT(*) as total
    FROM users
    WHERE active = true
  `
})

HTTP Integration

Create your own middleware in your app layer to extract and set tenant context:

// app/middleware/tenant.ts
import { withSchema, withoutSchema } from '@strav/database'
import { SchemaManager } from '@strav/database/database/domain/manager'
import type { Middleware } from '@strav/http'

export const tenantMiddleware: Middleware = async (ctx, next) => {
  // Extract tenant from subdomain
  const host = ctx.request.headers.get('host')
  const tenant = host?.split('.')[0] // tenant123.example.com -> tenant123

  if (tenant && tenant !== 'www') {
    // Map to schema name
    const schema = `tenant_${tenant}`

    // Optionally validate tenant exists
    const manager = container.resolve(SchemaManager)
    if (await manager.tenantExists(schema)) {
      return withSchema(schema, () => next())
    }
  }

  // Continue without tenant context
  return next()
}

// Alternative: Extract from header
export const headerTenantMiddleware: Middleware = async (ctx, next) => {
  const tenantId = ctx.request.headers.get('X-Tenant-ID')

  if (tenantId) {
    return withSchema(tenantId, () => next())
  }

  return next()
}

// For admin routes that need to bypass schema isolation
export const bypassSchema: Middleware = async (ctx, next) => {
  return withoutSchema(() => next())
}

Then use in your routes:

// app/routes.ts
import { router } from '@strav/http'
import { tenantMiddleware, bypassSchema } from './middleware/tenant'

// Apply tenant context to all routes
router.use(tenantMiddleware)

// Regular routes use tenant context automatically
router.get('/users', async (ctx) => {
  const users = await User.all() // Uses tenant schema
  return ctx.json(users)
})

// Admin routes can bypass schema isolation
router.get('/admin/schemas', bypassSchema, async (ctx) => {
  const manager = container.resolve(SchemaManager)
  const schemas = await manager.listSchemas() // Accesses all schemas
  return ctx.json(schemas)
})

Schema Management

Creating Schemas

import { SchemaManager } from '@strav/database/database/domain/manager'

const manager = container.resolve(SchemaManager)

// Create a new schema
await manager.createSchema('company_123')

// Clone structure from template schema
await manager.cloneSchema('public', 'company_456')

Running Migrations

// Migrate specific schema
await manager.migrateSchema('company_123')

// Migrate all schemas
await manager.migrateAllSchemas()

Managing Schemas

// Check if schema exists
const exists = await manager.schemaExists('company_123')

// List all schemas
const schemas = await manager.listSchemas()
// ['company_123', 'tenant_456', ...]

// Get schema statistics
const stats = await manager.getSchemaStats('company_123')
console.log(stats)
// { tables: 15, totalRows: 45678, sizeInBytes: 10485760 }

// Delete tenant (CAUTION: irreversible!)
await manager.deleteSchema('old_tenant')

CLI Commands

The Strav CLI provides commands for working with domains:

Generate Migrations

# Generate migration for public domain
strav generate:migration --scope=public --message="add user table"

# Generate migration for tenant domain
strav generate:migration --scope=tenant --message="add orders table"

# Generate migration for custom domain
strav generate:migration --scope=factory --message="add machine table"

Run Migrations

# Run public migrations
strav migrate --scope=public

# Run tenant migrations
strav migrate --scope=tenant

# Run migrations for custom domain
strav migrate --scope=factory

Rollback Migrations

# Rollback public migrations
strav rollback --scope=public

# Rollback last batch for tenant domain
strav rollback --scope=tenant

# Rollback specific batch
strav rollback --scope=tenant --batch=5

Generate Models

# Generate models for all domains
strav generate:models --scope=all

# Generate models for specific domain
strav generate:models --scope=tenant
strav generate:models --scope=factory

Transactions

Transactions automatically preserve tenant context:

await withSchema('company_123', async () => {
  await transaction(async (trx) => {
    // All queries within transaction use company_123 schema
    const user = await User.create({ name: 'Alice' }, trx)
    const profile = await Profile.create({ userId: user.id }, trx)

    // Query builder with transaction
    await query(Order, trx)
      .where('user_id', user.id)
      .update({ status: 'active' })
  })
})

Background Jobs

Set tenant context in background jobs and workers:

// In your job processor
async function processJob(job: Job) {
  const { tenantId, data } = job.payload

  await withSchema(tenantId, async () => {
    // All database operations use the tenant's schema
    await processInvoices(data)
    await sendNotifications()
  })
}

Admin Operations

Bypass tenant isolation for administrative tasks:

import { withoutSchema } from '@strav/database'

// Access all schemas without tenant restriction
await withoutSchema(async () => {
  // Query across all tenant schemas
  const report = await sql`
    SELECT
      schema_name,
      (SELECT COUNT(*) FROM information_schema.tables
       WHERE table_schema = schema_name) as table_count
    FROM information_schema.schemata
    WHERE schema_name LIKE 'tenant_%'
  `
})

Direct Execution

Execute queries directly in a specific tenant:

const manager = container.resolve(SchemaManager)

// Execute query in specific schema
const users = await manager.executeSchema(
  'company_123',
  'SELECT * FROM users WHERE active = $1',
  [true]
)

Best Practices

1. Schema Naming

Use consistent schema naming conventions:

// Good: predictable and safe
const schema = `tenant_${tenantId.toLowerCase()}`

// Validate schema names to prevent injection
if (!/^[a-z0-9_]+$/.test(schema)) {
  throw new Error('Invalid schema name')
}

2. Connection Pooling

Multi-tenant mode shares the connection pool efficiently. The search_path is set per-query, not per-connection, ensuring optimal resource usage.

3. Testing

Test with multiple tenants:

// test/multi-tenant.test.ts
import { withSchema } from '@strav/database'
import { SchemaManager } from '@strav/database/database/domain/manager'

beforeEach(async () => {
  const manager = container.resolve(SchemaManager)
  await manager.createSchema('test_schema_1')
  await manager.createSchema('test_schema_2')
})

afterEach(async () => {
  const manager = container.resolve(SchemaManager)
  await manager.deleteSchema('test_schema_1')
  await manager.deleteSchema('test_schema_2')
})

test('data isolation between tenants', async () => {
  // Create user in tenant 1
  await withSchema('test_schema_1', async () => {
    await User.create({ email: 'user1@example.com' })
  })

  // Create user in tenant 2
  await withSchema('test_schema_2', async () => {
    await User.create({ email: 'user2@example.com' })
  })

  // Verify isolation
  await withSchema('test_schema_1', async () => {
    const users = await User.all()
    expect(users).toHaveLength(1)
    expect(users[0].email).toBe('user1@example.com')
  })

  await withSchema('test_schema_2', async () => {
    const users = await User.all()
    expect(users).toHaveLength(1)
    expect(users[0].email).toBe('user2@example.com')
  })
})

4. Migrations

Structure your migrations to work across all tenant schemas:

// migrations/2024_01_01_create_users_table.ts
export async function up(sql: SQL) {
  await sql`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      created_at TIMESTAMP DEFAULT NOW()  -- PostgreSQL syntax
    )
  `
}

export async function down(sql: SQL) {
  await sql`DROP TABLE IF EXISTS users`
}

Then apply to all tenants:

# Run migrations for all schemas
strav migrate:all-schemas

5. Monitoring

Monitor schema usage and performance:

async function getSchemaMetrics() {
  const manager = container.resolve(SchemaManager)
  const schemas = await manager.listSchemas()

  const metrics = await Promise.all(
    schemas.map(async (schema) => ({
      schema,
      stats: await manager.getSchemaStats(schema),
    }))
  )

  return metrics
}

Security Considerations

// Audit log with schema context
function logDatabaseOperation(operation: string) {
  const context = getCurrentSchemaContext()
  logger.info('Database operation', {
    operation,
    schema: context?.schema ?? 'public',
    timestamp: new Date(),
  })
}

Troubleshooting

Checking Current Schema

import { getCurrentSchemaContext, getCurrentSchema, hasSchemaContext } from '@strav/database'

// In your code
const context = getCurrentSchemaContext()
console.log('Current schema:', getCurrentSchema())
console.log('Has schema context:', hasSchemaContext())

Debug Mode

Enable debug logging for tenant operations:

// In development
if (process.env.NODE_ENV === 'development') {
  const original = getCurrentSchema
  getCurrentSchema = () => {
    const schema = original()
    console.log(`[TENANT] Using schema: ${schema}`)
    return schema
  }
}

Common Issues

• Check that multi-domain mode is enabled in config
• Verify schema context is set with hasSchemaContext()
• Ensure middleware is applied before route handlers

• Use withoutSchema() for operations that need public schema
• Use bypassSchema middleware for admin routes

• Schema context is preserved within transactions automatically
• Check that you're not manually manipulating search_path

API Reference

Context Functions

• withSchema(schema, callback) - Execute callback within schema context
• withoutSchema(callback) - Execute callback without schema isolation
• getCurrentSchemaContext() - Get current schema context
• getCurrentSchema() - Get current schema name
• hasSchemaContext() - Check if in schema context

SchemaManager Methods

• createSchema(schema) - Create new schema
• deleteSchema(schema) - Delete schema (irreversible)
• schemaExists(schema) - Check if schema exists
• listSchemas() - List all schemas
• migrateSchema(schema) - Run migrations for schema
• migrateAllSchemas() - Run migrations for all schemas
• cloneSchema(source, target) - Copy schema structure
• getSchemaStats(schema) - Get schema statistics
• executeSchema(schema, sql, params) - Execute query in schema