kysera schema

PostgreSQL schema management commands for multi-tenant and modular database architectures.

PostgreSQL Only

Schema management commands are only available for PostgreSQL databases. SQLite has no schema support, and MySQL uses databases as the equivalent concept.

Commands

Command	Description
`list`	List all database schemas
`create`	Create a new database schema
`drop`	Drop a database schema
`info`	Show detailed information about a schema
`clone`	Clone a schema structure to a new schema
`compare`	Compare two schemas and show differences

list

List all database schemas.

kysera schema list

Options:

--json               Output as JSON
--tenant             Only show tenant schemas (tenant_* pattern)
-v, --verbose        Show detailed information (tables, owner, size)
-c, --config <path>  Path to configuration file

Examples:

# List all schemas
kysera schema list

# Show detailed information
kysera schema list --verbose

# Only tenant schemas
kysera schema list --tenant

# JSON output
kysera schema list --json

Output:

Database Schemas

  [tenant] tenant_acme
  [tenant] tenant_globex
           public
           auth
           admin

Use --verbose for detailed information

Verbose Output:

Database Schemas

name           tables  owner      size      tenant
─────────────────────────────────────────────────────
public         12      postgres   4.25 MB   -
auth           5       postgres   1.12 MB   -
tenant_acme    15      app_user   2.34 MB   acme
tenant_globex  15      app_user   1.87 MB   globex

create

Create a new database schema.

kysera schema create <name>

Arguments:

name - Schema name to create

Options:

--tenant <id>        Create as tenant schema with specified ID
--if-not-exists      Do not error if schema already exists
--force              Skip confirmation prompt
-v, --verbose        Show detailed output
-c, --config <path>  Path to configuration file

Examples:

# Create a schema
kysera schema create auth

# Create without confirmation
kysera schema create auth --force

# Create tenant schema (uses tenant_<id> naming convention)
kysera schema create --tenant acme
# Creates schema: tenant_acme

# Create if not exists
kysera schema create auth --if-not-exists

Tenant Schema Creation:

When using --tenant, the schema name follows the tenant_<id> naming convention:

kysera schema create --tenant 123
# Output:
# Schema 'tenant_123' created successfully
#
# Tenant schema created with naming convention:
#   Schema: tenant_123
#   Tenant ID: 123
#
# Next steps:
#   1. Run migrations in the new schema:
#      kysera migrate up --schema tenant_123
#   2. Or clone from a template schema:
#      kysera schema clone template tenant_123

drop

Drop a database schema.

kysera schema drop <name>

Arguments:

name - Schema name to drop

Options:

--cascade            Drop all objects in the schema (CASCADE)
--if-exists          Do not error if schema does not exist
--force              Skip confirmation prompt
-v, --verbose        Show detailed output
-c, --config <path>  Path to configuration file

Protected Schemas:

The following schemas cannot be dropped:

public
pg_catalog
information_schema

Examples:

# Drop a schema (requires confirmation)
kysera schema drop old_tenant

# Drop with CASCADE (drops all contained objects)
kysera schema drop old_tenant --cascade

# Drop without confirmation
kysera schema drop old_tenant --cascade --force

# Drop if exists (no error if not found)
kysera schema drop old_tenant --if-exists

Confirmation Output:

Warning: You are about to drop schema 'tenant_old'
  Tables: 15
  Size: 2.34 MB
  CASCADE: All objects in the schema will be dropped!

? Are you sure you want to drop schema 'tenant_old'? (y/N)

info

Show detailed information about a schema.

kysera schema info <name>

Arguments:

name - Schema name

Options:

--json               Output as JSON
--indexes            Show index information
--foreign-keys       Show foreign key relationships
-v, --verbose        Show all details (indexes, foreign keys)
-c, --config <path>  Path to configuration file

Examples:

# Basic info
kysera schema info public

# With indexes
kysera schema info public --indexes

# With foreign keys
kysera schema info public --foreign-keys

# Full details
kysera schema info public --verbose

# JSON output
kysera schema info public --json

Output:

Schema: public
──────────────────────────────────────────────────

General Information:
  Owner: postgres
  Tables: 12
  Size: 4.25 MB

Tables:
  - users
  - posts
  - comments
  - categories
  - tags
  - post_tags

Commands:
  Clone: kysera schema clone public <target>
  Drop: kysera schema drop public --cascade
  Compare: kysera schema compare public <other>

Verbose Output (with --verbose):

Schema: tenant_acme
──────────────────────────────────────────────────

General Information:
  Owner: app_user
  Tables: 15
  Size: 2.34 MB

Tenant Information:
  Tenant ID: acme
  Schema Pattern: tenant_<id>

Tables:
  - users
  - posts
  - comments
  ...

Indexes:
table       index              type    unique  primary  columns
────────────────────────────────────────────────────────────────
users       users_pkey         btree   Yes     Yes      id
users       users_email_idx    btree   Yes     No       email
posts       posts_pkey         btree   Yes     Yes      id
posts       posts_user_id_idx  btree   No      No       user_id

Foreign Keys:
constraint          table    column    references                 onDelete  onUpdate
──────────────────────────────────────────────────────────────────────────────────────
posts_user_id_fkey  posts    user_id   public.users.id            CASCADE   CASCADE
comments_post_fkey  comments post_id   public.posts.id            CASCADE   CASCADE

clone

Clone a schema's structure (and optionally data) to a new schema.

kysera schema clone <source> <target>

Arguments:

source - Source schema name
target - Target schema name

Options:

--include-data         Include table data in the clone
--exclude <tables...>  Tables to exclude from cloning
--tenant <id>          Create target as tenant schema with specified ID
--force                Skip confirmation prompt
-v, --verbose          Show detailed output
-c, --config <path>    Path to configuration file

Examples:

# Clone structure only
kysera schema clone template new_tenant

# Clone with data
kysera schema clone template new_tenant --include-data

# Clone as tenant schema
kysera schema clone template --tenant acme
# Creates: tenant_acme

# Exclude certain tables
kysera schema clone template new_tenant --exclude logs sessions

# Without confirmation
kysera schema clone template new_tenant --force

Confirmation Output:

Clone Schema
──────────────────────────────────────────────────
  Source: template
  Target: tenant_acme
  Tables: 15
  Size: 1.24 MB
  Include Data: No

? Clone schema 'template' to 'tenant_acme'? (Y/n)

Success Output:

Schema 'template' cloned to 'tenant_acme' successfully

Clone Summary:
  Tables cloned: 15
  Size: 48 KB

Tenant schema created:
  Schema: tenant_acme
  Tenant ID: acme

Next steps:
  View schema: kysera schema info tenant_acme
  Run queries: kysera query --schema tenant_acme

compare

Compare two schemas and show differences.

kysera schema compare <schema1> <schema2>

Arguments:

schema1 - First schema name
schema2 - Second schema name

Options:

--json               Output as JSON
-v, --verbose        Show detailed output (list all common tables)
-c, --config <path>  Path to configuration file

Examples:

# Compare two schemas
kysera schema compare template tenant_acme

# Verbose output
kysera schema compare template tenant_acme --verbose

# JSON output
kysera schema compare template tenant_acme --json

Output:

Schema Comparison
============================================================

Schema Overview:

  template                       tenant_acme
  ------------------------------ ------------------------------
  Tables: 15                     Tables: 16
  Size: 1.24 MB                  Size: 2.34 MB

Tables only in 'template' (1):
  - archived_users

Tables only in 'tenant_acme' (2):
  + custom_settings
  + tenant_config

Common tables (14):
  users, posts, comments, categories, tags ... and 9 more

------------------------------------------------------------

Summary:
  Total tables in 'template': 15
  Total tables in 'tenant_acme': 16
  Common tables: 14
  Unique to 'template': 1
  Unique to 'tenant_acme': 2

Hints:
  To sync missing tables, run migrations on 'tenant_acme'

Identical Schemas Output:

Schema Comparison
============================================================

Schema Overview:

  template                       tenant_new
  ------------------------------ ------------------------------
  Tables: 15                     Tables: 15
  Size: 1.24 MB                  Size: 48 KB

Schemas have identical table structures

------------------------------------------------------------

Summary:
  Total tables in 'template': 15
  Total tables in 'tenant_new': 15
  Common tables: 15
  Unique to 'template': 0
  Unique to 'tenant_new': 0

Multi-Tenant Workflow

Initial Setup

Create a template schema:

# Create template schema
kysera schema create template

# Run migrations in template
kysera migrate up --schema template

Provision new tenants:

# Clone template for new tenant
kysera schema clone template --tenant acme

# Or create and migrate separately
kysera schema create --tenant acme
kysera migrate up --schema tenant_acme

Manage tenant schemas:

# List all tenant schemas
kysera schema list --tenant

# Check schema drift
kysera schema compare template tenant_acme

# Remove old tenant
kysera schema drop tenant_old --cascade --force

Schema Migration Strategy

When updating the database structure:

# 1. Update template first
kysera migrate up --schema template

# 2. Check differences with existing tenants
kysera schema compare template tenant_acme

# 3. Run migrations on each tenant
kysera migrate up --schema tenant_acme
kysera migrate up --schema tenant_globex

Automated Tenant Provisioning

#!/bin/bash
TENANT_ID=$1

# Create tenant schema from template
kysera schema clone template --tenant "$TENANT_ID" --force

# Verify schema
kysera schema info "tenant_$TENANT_ID"

echo "Tenant $TENANT_ID provisioned successfully"

Configuration

Schema in Configuration File

You can set a default schema in kysera.config.ts:

import { defineConfig } from '@kysera/cli'

export default defineConfig({
  database: {
    dialect: 'postgres',
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '5432'),
    database: process.env.DB_NAME || 'myapp',
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASSWORD,
    schema: 'public'  // Default schema
  }
})

Environment-Based Schema

export default defineConfig({
  database: {
    // ...
    schema: process.env.DB_SCHEMA || 'public'
  }
})

PostgreSQL Schema Management - Programmatic API
Multi-Tenant SaaS Example - Full example application
Migration Commands - Running migrations with --schema
Database Commands - Database utilities with schema support

Commands​

list​

create​

drop​

info​

clone​

compare​

Multi-Tenant Workflow​

Initial Setup​

Schema Migration Strategy​

Automated Tenant Provisioning​

Configuration​

Schema in Configuration File​

Environment-Based Schema​

Related Documentation​

Commands

list

create

drop

info

clone

compare

Multi-Tenant Workflow

Initial Setup

Schema Migration Strategy

Automated Tenant Provisioning

Configuration

Schema in Configuration File

Environment-Based Schema

Related Documentation