Skip to content
/ strata Public

Define schemas in YAML, auto-generate migrations for PostgreSQL, MySQL, and SQLite

License

MIT license
1 star 0 forks Branches Tags Activity
Star
Notifications You must be signed in to change notification settings

Lazialize/strata

BranchesTags

Repository files navigation

Strata

Database schema management CLI tool - Infrastructure as Code for database schemas

License Rust Version

Strata is a modern database schema management tool that treats your database schema as code. Define your schemas in declarative YAML files, automatically generate migrations, and apply them with confidence across multiple environments.

Features

  • 📝 Schema as Code: Define database schemas in declarative YAML files
  • 🔄 Automatic Migration Generation: Generate migration files from schema changes
  • ✅ Schema Validation: Validate integrity, foreign keys, and naming conventions
  • 🔍 Migration Status Tracking: Track applied and pending migrations
  • ⬆️ Apply & Rollback: Apply migrations or rollback with confidence
  • 📤 Schema Export: Export existing database schemas to code
  • 🛡️ Destructive Change Guard: Prevent accidental data loss with explicit confirmation
  • 🗄️ Multi-Database Support: PostgreSQL, MySQL, and SQLite
  • 📊 Structured Output: --format json for CI/CD integration
  • 🔒 SSL/TLS Support: Configurable SSL modes for secure database connections

Installation

Using Install Script (Recommended)

The easiest way to install Strata. Automatically downloads and installs the latest version.

# Install the latest version
curl -fsSL https://raw.githubusercontent.com/Lazialize/strata/main/install.sh | bash

# Install a specific version
curl -fsSL https://raw.githubusercontent.com/Lazialize/strata/main/install.sh | VERSION=v1.0.0 bash

# Specify custom installation directory
curl -fsSL https://raw.githubusercontent.com/Lazialize/strata/main/install.sh | INSTALL_DIR=/usr/local/bin bash

From Source

# Clone the repository
git clone https://github.com/Lazialize/strata.git
cd strata

# Build and install
cargo build --release
cargo install --path .

For detailed build instructions, cross-compilation, and platform-specific guides, see BUILDING.md.

Quick Start

1. Initialize a New Project

# Initialize with SQLite
strata init --dialect sqlite

# Or with PostgreSQL
strata init --dialect postgresql

# Or with MySQL
strata init --dialect mysql

This creates:

  • .strata.yaml - Configuration file with development, staging, and production environments
  • schema/ - Directory for schema definitions
  • migrations/ - Directory for generated migrations

2. Define Your Schema

Create a schema file in the schema/ directory (e.g., schema/users.yaml):

version: "1.0"
tables:
  users:
    columns:
      - name: id
        type:
          kind: INTEGER
        nullable: false
        auto_increment: true
      - name: email
        type:
          kind: VARCHAR
          length: 255
        nullable: false
      - name: created_at
        type:
          kind: TIMESTAMP
          with_time_zone: true
        nullable: false
    primary_key:
      - id
    indexes:
      - name: idx_users_email
        columns:
          - email
        unique: true

3. Generate Migration

# Generate migration with description
strata generate --description "create users table"

# Or with auto-generated description
strata generate

This creates a migration in migrations/ directory with:

  • up.sql - SQL to apply the migration
  • down.sql - SQL to rollback the migration
  • .meta.yaml - Migration metadata (version, checksum)

4. Apply Migration

# Apply to development environment
strata apply

# Dry run to preview SQL
strata apply --dry-run

# Apply to production
strata apply --env production

5. Check Status

# Show migration status
strata status

# Check production status
strata status --env production

Global Options

These options apply to all commands:

  • -c, --config <PATH> - Path to configuration file (default: .strata.yaml)
  • -v, --verbose - Enable verbose debug output
  • --no-color - Disable colored output
  • --format <FORMAT> - Output format: text (default) or json

Commands

init - Initialize Project

Initialize a new schema management project.

# Basic initialization
strata init --dialect sqlite

# Force re-initialization
strata init --force

Options:

  • -d, --dialect <DIALECT> - Database dialect (postgresql, mysql, sqlite)
  • -f, --force - Force initialization even if config exists

generate - Generate Migrations

Generate migration files from schema changes.

# With description
strata generate --description "add user email column"

# Auto-generate description
strata generate

# Dry run to preview SQL and destructive changes
strata generate --dry-run

# Allow destructive changes (DROP, RENAME, etc.)
strata generate --allow-destructive

Options:

  • -d, --description <DESCRIPTION> - Description for the migration
  • --dry-run - Show SQL without creating files
  • --allow-destructive - Allow destructive changes (DROP TABLE, DROP COLUMN, RENAME, ENUM removal)

apply - Apply Migrations

Apply pending migrations to the database.

# Apply to development
strata apply

# Dry run (preview only)
strata apply --dry-run

# Apply to production with timeout
strata apply --env production --timeout 30

# Allow destructive changes
strata apply --allow-destructive

Options:

  • --dry-run - Show SQL without executing
  • -e, --env <ENV> - Target environment (default: development)
  • --timeout <SECONDS> - Timeout for database operations
  • --allow-destructive - Allow destructive changes (DROP TABLE, DROP COLUMN, RENAME, ENUM removal)

rollback - Rollback Migrations

Rollback applied migrations.

# Rollback last migration
strata rollback

# Rollback last 3 migrations
strata rollback --steps 3

# Dry run to preview SQL
strata rollback --dry-run

# Rollback in production
strata rollback --env production --steps 1

# Allow destructive rollback
strata rollback --allow-destructive

Options:

  • --steps <N> - Number of migrations to rollback
  • --dry-run - Show SQL without executing
  • --allow-destructive - Allow destructive changes (DROP TABLE, DROP COLUMN, etc.)
  • -e, --env <ENV> - Target environment (default: development)

check - Validate and Preview Migrations

Run validate followed by generate --dry-run in a single command. If validation fails, generation is skipped.

# Check default schema directory
strata check

# Check specific directory
strata check --schema-dir ./custom-schema

# JSON output for CI/CD
strata check --format json

Options:

  • -s, --schema-dir <DIR> - Path to schema directory

validate - Validate Schema

Validate schema definition files.

# Validate default schema directory
strata validate

# Validate specific directory
strata validate --schema-dir ./custom-schema

Options:

  • -s, --schema-dir <DIR> - Path to schema directory

status - Show Migration Status

Display migration status information.

# Show status for development
strata status

# Show status for production
strata status --env production

Options:

  • -e, --env <ENV> - Target environment (default: development)

export - Export Schema

Export existing database schema to code.

# Export to default schema directory
strata export

# Export to custom directory
strata export --output ./exported-schema

# Export from production
strata export --env production --output ./prod-schema

# Overwrite existing files
strata export --force

# Split into per-table files
strata export --split

# Export specific tables only
strata export --tables users,posts

# Exclude specific tables
strata export --exclude-tables schema_migrations

Options:

  • -o, --output <DIR> - Output directory for schema files
  • -e, --env <ENV> - Target environment (default: development)
  • --force - Overwrite existing files without confirmation
  • --split - Output one YAML file per table instead of a single file
  • --tables <TABLES> - Include only specified tables (comma-separated)
  • --exclude-tables <TABLES> - Exclude specified tables (comma-separated)

Configuration

The .strata.yaml configuration file defines database connections and project settings.

Example Configuration

version: "1.0"
dialect: postgresql  # postgresql, mysql, or sqlite
schema_dir: schema
migrations_dir: migrations

environments:
  development:
    host: localhost
    port: 5432
    database: myapp_dev
    user: developer
    password: devpass
    timeout: 30

  staging:
    host: staging-db.example.com
    port: 5432
    database: myapp_staging
    user: app_user
    timeout: 30
    ssl_mode: require

  production:
    host: db.example.com
    port: 5432
    database: myapp_prod
    user: app_user
    timeout: 60
    ssl_mode: verify_full
    max_connections: 10
    min_connections: 2
    idle_timeout: 300
    options:
      application_name: strata

Configuration Fields

  • version - Configuration file version
  • dialect - Database type (postgresql, mysql, sqlite)
  • schema_dir - Directory for schema definition files
  • migrations_dir - Directory for migration files
  • environments - Database connection settings per environment
    • host - Database host (default: localhost)
    • port - Database port (default: 5432 for PostgreSQL, 3306 for MySQL, none for SQLite)
    • database - Database name (or file path for SQLite)
    • user - Database user (optional for SQLite)
    • password - Database password (optional for SQLite)
    • timeout - Connection timeout in seconds
    • ssl_mode - SSL connection mode: disable, prefer, require, verify_ca, verify_full
    • max_connections - Maximum connection pool size (default: 5)
    • min_connections - Minimum connection pool size
    • idle_timeout - Idle connection timeout in seconds
    • options - Additional connection parameters (key-value pairs appended to connection string)

Environment Variable Overrides

Database connection settings can be overridden with environment variables:

Variable Description
DB_HOST Database host
DB_PORT Database port
DB_DATABASE Database name
DB_USER Database user
DB_PASSWORD Database password
DB_TIMEOUT Connection timeout in seconds

This allows you to keep sensitive credentials out of your configuration file:

DB_PASSWORD=secret strata apply --env production

Schema Definition Format

Strata uses YAML for schema definitions. Each table is defined with its columns, indexes, and constraints.

IDE Setup for YAML Completion

For better development experience with IDE auto-completion, configure your editor to use the Strata YAML schema:

VSCode

Install the YAML extension by Red Hat, then add the following to your .vscode/settings.json:

{
  "yaml.schemas": {
    "./resources/schemas/strata-schema.json": "schema/**/*.yaml"
  }
}

This enables:

  • Auto-completion for column types (common and dialect-specific)
  • Validation of schema structure
  • Inline documentation for type properties

IntelliJ IDEA / WebStorm

  1. Open Settings → Languages & Frameworks → Schemas and DTDs → JSON Schema Mappings
  2. Add a new mapping:
    • Name: Strata Schema
    • Schema file: resources/schemas/strata-schema.json
    • Schema version: JSON Schema version 2020-12
    • File path pattern: schema/**/*.yaml

Dialect-Specific Column Types

In addition to common column types that work across all databases, Strata supports dialect-specific types that leverage database-specific features:

PostgreSQL-Specific Types

  • SERIAL - Auto-incrementing integer (equivalent to INTEGER + SEQUENCE)
  • BIGSERIAL - Auto-incrementing big integer
  • SMALLSERIAL - Auto-incrementing small integer
  • INT2 - 2-byte integer (-32768 to 32767)
  • INT4 - 4-byte integer
  • INT8 - 8-byte integer
  • VARBIT - Variable-length bit string (with optional length parameter)
  • INET - IPv4 or IPv6 network address
  • CIDR - IPv4 or IPv6 network specification
  • ARRAY - Array type (with element_type parameter)

Example:

- name: id
  type:
    kind: SERIAL
  nullable: false

- name: ip_address
  type:
    kind: INET
  nullable: true

- name: flags
  type:
    kind: VARBIT
    length: 16
  nullable: true

MySQL-Specific Types

  • TINYINT - Very small integer (-128 to 127, or 0 to 255 if unsigned)
  • MEDIUMINT - Medium-sized integer
  • ENUM - String object with a value chosen from a list (requires values parameter)
  • SET - Set of string values (requires values parameter)
  • YEAR - Year in 4-digit format

Example:

- name: age
  type:
    kind: TINYINT
    unsigned: true
  nullable: false

- name: status
  type:
    kind: ENUM
    values: ["active", "inactive", "pending"]
  nullable: false

- name: permissions
  type:
    kind: SET
    values: ["read", "write", "execute"]
  nullable: true

Note: Dialect-specific types are validated at database execution time. If you use a type that doesn't exist in your target database, you'll receive a clear error message from the database engine.

Example Schema Files:

For more detailed documentation and examples, see example/DIALECT_SPECIFIC_TYPES.md.

Column Types

Supported column types:

Numeric Types:

  • INTEGER - Integer numbers
    • precision: Optional bit size (e.g., 16, 32, 64)
  • DECIMAL - Fixed-point decimal numbers
    • precision: Total number of digits (required)
    • scale: Number of decimal places (required)
  • FLOAT - Single-precision floating-point numbers
  • DOUBLE - Double-precision floating-point numbers

String Types:

  • VARCHAR - Variable-length strings
    • length: Maximum length (required)
  • CHAR - Fixed-length strings
    • length: Fixed length (required, 1-255)
  • TEXT - Long text

Date/Time Types:

  • DATE - Date only (no time)
  • TIME - Time only (no date)
    • with_time_zone: Optional timezone support
  • TIMESTAMP - Date and time
    • with_time_zone: Optional timezone support

Other Types:

  • BOOLEAN - Boolean values (true/false)
  • BLOB - Binary large objects (images, files, etc.)
  • UUID - Universally unique identifier
  • JSON - JSON data
  • JSONB - Binary JSON (PostgreSQL optimized, falls back to JSON on other databases)

Constraints

Supported constraints:

  • PRIMARY_KEY - Primary key constraint
    • columns: List of column names
  • FOREIGN_KEY - Foreign key constraint
    • columns: List of column names
    • referenced_table: Referenced table name
    • referenced_columns: Referenced column names
    • on_delete: Referential action on delete (optional): NO_ACTION, CASCADE, SET_NULL, SET_DEFAULT, RESTRICT
    • on_update: Referential action on update (optional): same values as on_delete
  • UNIQUE - Unique constraint
    • columns: List of column names
  • CHECK - Check constraint
    • columns: List of columns involved
    • check_expression: SQL check expression (e.g., "price > 0")

Table and Column Renames

To rename a table or column, use the renamed_from field. Strata will generate ALTER TABLE RENAME or ALTER TABLE RENAME COLUMN instead of a destructive drop-and-create:

tables:
  # Rename table: accounts -> users
  users:
    renamed_from: accounts
    columns:
      - name: full_name
        renamed_from: name  # Rename column: name -> full_name
        type:
          kind: VARCHAR
          length: 255
        nullable: false

Note: Remove renamed_from after the migration has been applied. It is only used during migration generation.

Database Dialect Type Mapping

Strata automatically maps column types to the appropriate native type for each database:

Strata Type PostgreSQL MySQL SQLite
INTEGER INTEGER/SERIAL INT INTEGER
DECIMAL NUMERIC(p,s) DECIMAL(p,s) TEXT
FLOAT REAL FLOAT REAL
DOUBLE DOUBLE PRECISION DOUBLE REAL
VARCHAR VARCHAR(n) VARCHAR(n) TEXT
CHAR CHAR(n) CHAR(n) TEXT
TEXT TEXT TEXT TEXT
DATE DATE DATE TEXT
TIME TIME [WITH TZ] TIME TEXT
TIMESTAMP TIMESTAMP [WITH TZ] TIMESTAMP TEXT
BOOLEAN BOOLEAN BOOLEAN INTEGER
BLOB BYTEA BLOB BLOB
UUID UUID CHAR(36) TEXT
JSON JSON JSON TEXT
JSONB JSONB JSON TEXT

Note: SQLite has limited native type support. Strata stores some types as TEXT to preserve precision (e.g., DECIMAL, DATE).

Column Type Examples

Here are examples of how to use each column type in your schema:

version: "1.0"
tables:
  products:
    columns:
      # Numeric types
      - name: id
        type:
          kind: INTEGER
        nullable: false
        auto_increment: true
      - name: price
        type:
          kind: DECIMAL
          precision: 10
          scale: 2
        nullable: false
      - name: weight
        type:
          kind: FLOAT
        nullable: true
      - name: latitude
        type:
          kind: DOUBLE
        nullable: true

      # String types
      - name: name
        type:
          kind: VARCHAR
          length: 255
        nullable: false
      - name: country_code
        type:
          kind: CHAR
          length: 2
        nullable: false
      - name: description
        type:
          kind: TEXT
        nullable: true

      # Date/Time types
      - name: manufacturing_date
        type:
          kind: DATE
        nullable: true
      - name: opening_time
        type:
          kind: TIME
          with_time_zone: false
        nullable: true
      - name: created_at
        type:
          kind: TIMESTAMP
          with_time_zone: true
        nullable: false

      # Other types
      - name: is_active
        type:
          kind: BOOLEAN
        nullable: false
        default_value: "true"
      - name: thumbnail
        type:
          kind: BLOB
        nullable: true
      - name: external_id
        type:
          kind: UUID
        nullable: false
      - name: attributes
        type:
          kind: JSON
        nullable: true
      - name: metadata
        type:
          kind: JSONB
        nullable: true

    primary_key:
      - id

Example Schema

version: "1.0"
tables:
  posts:
    columns:
      - name: id
        type:
          kind: INTEGER
        nullable: false
        auto_increment: true
      - name: title
        type:
          kind: VARCHAR
          length: 200
        nullable: false
      - name: content
        type:
          kind: TEXT
        nullable: true
      - name: user_id
        type:
          kind: INTEGER
        nullable: false
      - name: published
        type:
          kind: BOOLEAN
        nullable: false
        default_value: "false"
      - name: published_at
        type:
          kind: TIMESTAMP
          with_time_zone: true
        nullable: true
    primary_key:
      - id
    indexes:
      - name: idx_posts_user_id
        columns:
          - user_id
        unique: false
      - name: idx_posts_published_at
        columns:
          - published_at
        unique: false
    constraints:
      - type: FOREIGN_KEY
        columns:
          - user_id
        referenced_table: users
        referenced_columns:
          - id
        on_delete: CASCADE

Migration Files

Migration files are generated in the migrations/ directory with the following structure:

migrations/
  20260122120000_create_users/
    up.sql         # SQL to apply the migration
    down.sql       # SQL to rollback the migration
    .meta.yaml     # Migration metadata

Metadata File

The .meta.yaml file contains:

version: "20260122120000"
description: create_users
dialect: postgresql
checksum: "abc123def456..."  # SHA-256 hash of up.sql

Migrations with destructive changes include additional metadata:

version: "20260125120000"
description: drop_legacy_tables
dialect: postgresql
checksum: "def789..."
destructive_changes:
  tables_dropped:
    - legacy_users
  columns_dropped:
    - table: products
      columns:
        - old_field

The checksum ensures migration integrity - any modification to the migration after it's been applied will be detected.

Best Practices

1. Schema Organization

  • One table per file: Create separate YAML files for each table
  • Meaningful names: Use descriptive names for tables, columns, and constraints
  • Consistent naming: Follow naming conventions (e.g., snake_case)

2. Migration Workflow

  1. Always check before generating migrations: strata check (validates and previews in one step)
  2. Review generated SQL before applying: strata apply --dry-run
  3. Test in development before production
  4. Never modify applied migrations - create new ones instead
  5. Keep migrations small - one logical change per migration

3. Environment Management

  • Use environment-specific configurations for different databases
  • Store sensitive credentials in environment variables
  • Test migrations in staging before production
  • Maintain separate databases for each environment

4. Version Control

  • Commit schema files to version control
  • Commit migration files to version control
  • Don't commit .strata.yaml if it contains sensitive data
  • Use .gitignore for database files and credentials

Destructive Change Safety Guard

Strata includes a safety mechanism to prevent accidental data loss from destructive schema changes.

What Are Destructive Changes?

Destructive changes are schema modifications that can cause data loss or application compatibility issues:

Change Type Example Risk
Table Drop DROP TABLE users All data in the table is lost
Column Drop ALTER TABLE users DROP COLUMN email Column data is lost
Column Rename ALTER TABLE users RENAME COLUMN name TO full_name Application code may break
ENUM Drop DROP TYPE status_enum References become invalid
ENUM Recreate Removing values from an ENUM Existing data may become invalid

Default Behavior (Deny by Default)

By default, Strata refuses to generate or apply migrations containing destructive changes:

$ strata generate
Error: Destructive changes detected

Tables to be dropped: users, posts
Columns to be dropped:
  - products: legacy_field, unused_column

To proceed, choose one of the following:
  1. Review changes: strata generate --dry-run
  2. Allow destructive changes: strata generate --allow-destructive
  3. Reconsider your schema changes

Allowing Destructive Changes

To proceed with destructive changes, use the --allow-destructive flag:

# Generate migration with destructive changes
strata generate --allow-destructive

# Apply migration with destructive changes
strata apply --allow-destructive

Recommended Workflow

  1. Preview first: Use --dry-run to see what will happen

    strata generate --dry-run

  2. Review the changes: Ensure you understand the impact

  3. Generate with explicit permission:

    strata generate --allow-destructive --description "drop legacy tables"

  4. Apply with explicit permission:

    strata apply --allow-destructive

Migration Metadata

When you generate a migration with destructive changes, the metadata is recorded in .meta.yaml:

version: "20260125120000"
description: "drop legacy tables"
dialect: postgresql
checksum: "abc123..."
destructive_changes:
  tables_dropped:
    - legacy_users
  columns_dropped:
    - table: products
      columns:
        - old_field

This metadata is used by strata apply to enforce the safety guard, even if the migration was generated on another machine.

Legacy Migrations

Migrations created before the destructive change guard feature (without destructive_changes field) are treated as potentially destructive and require --allow-destructive to apply:

$ strata apply
Error: Destructive migration detected

Migration: 20250101120000
⚠ Legacy migration format detected

To proceed, choose one of the following:
  1. Review SQL: strata apply --dry-run
  2. Allow destructive changes: strata apply --allow-destructive
  3. Regenerate migration with current version of strata

Troubleshooting

Migration Checksum Mismatch

If you see a checksum mismatch warning:

  1. Don't modify applied migrations - create a new migration instead
  2. Check if someone modified the migration file after it was applied
  3. If intentional, you may need to manually update the database migration history

Connection Timeout

If database connections timeout:

  1. Increase timeout in .strata.yaml
  2. Check network connectivity
  3. Verify database credentials
  4. Ensure database is running

Validation Errors

If schema validation fails:

  1. Check error message for specific issues
  2. Verify all referenced tables exist
  3. Ensure foreign key columns match referenced columns
  4. Check naming conventions

Development

Building from Source

# Clone repository
git clone https://github.com/Lazialize/strata.git
cd strata

# Build
cargo build

# Run tests
cargo test

# Build release version
cargo build --release

Running Tests

# Run all tests
cargo test

# Run specific test
cargo test test_name

# Run with output
cargo test -- --nocapture

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

  1. Fork the repository
  2. Create your feature branch (git checkout -b feature/amazing-feature)
  3. Commit your changes (git commit -m 'Add amazing feature')
  4. Push to the branch (git push origin feature/amazing-feature)
  5. Open a Pull Request

License

This project is licensed under either of:

at your option.

Acknowledgments

Note

🤖 This project was created with the assistance of AI (Claude, Anthropic). The code, documentation, and architecture were developed through human-AI collaboration.

Made with ❤️ by the Strata Contributors

About

Define schemas in YAML, auto-generate migrations for PostgreSQL, MySQL, and SQLite

Resources

Readme

License

MIT license

Contributing

Contributing
Activity

Stars

1 star

Watchers

0 watching

Forks

0 forks
Report repository

Releases 7

v0.1.0 Latest
Feb 1, 2026
+ 6 releases

Packages

 
 
 

Contributors

Languages