Vespertide Database Schema Definition

Declarative database schema management. Define tables in JSON, generate typed migrations and SQL.

CRITICAL: Always validate your model against the JSON Schema before committing. Use $schema in every model file for IDE validation.

Schema Validation (MANDATORY)

Every model file MUST include the $schema field:

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "table_name",
  "columns": []
}

Before saving any model:

1. •Ensure $schema URL is present
2. •Verify IDE shows no validation errors
3. •Run vespertide diff to check for parsing errors

The schema URL provides:

• •Real-time validation in VS Code, WebStorm, etc.
• •Autocompletion for all fields
• •Type checking for column types and constraints

Post-Edit Validation (MANDATORY)

After EVERY edit to a model file, ALWAYS run these checks:

# 1. Check for parsing errors and schema violations
vespertide diff

# 2. Preview generated SQL to verify correctness
vespertide sql

Verify the output:

• •vespertide diff shows expected changes (no unexpected additions/removals)
• •vespertide sql generates valid SQL for your target database
• •IDE shows no red squiggles (schema validation errors)
• •All required fields (name, type, nullable) are present

Only proceed to vespertide revision after verification passes.

Installation

cargo install vespertide-cli

CLI Commands

Exported ORM Files (DO NOT EDIT)

CRITICAL: Files generated by vespertide export are AUTO-GENERATED. Never modify them manually.

Rules

1. •Never manually edit exported files (SeaORM entities, SQLAlchemy models, etc.)
2. •Always regenerate by running vespertide export --orm <orm_name>
3. •Edit source models in models/*.json instead, then re-export

Workflow

# 1. Edit your model files (models/*.json)
# 2. Regenerate ORM code
vespertide export --orm seaorm

# 3. Never touch the generated files after this

Migration Files (DO NOT EDIT)

CRITICAL: Migration files are AUTO-GENERATED. Never create or modify them manually.

Rules

1. •Always use vespertide revision -m "message" to create migrations
2. •Never manually create migration JSON files
3. •Never manually edit migration JSON files
4. •Only exception: Adding fill_with values when prompted

When fill_with is Required

When adding a NOT NULL column to an existing table without a default value, the CLI will prompt for a fill_with value. This is the ONLY case where you may need to edit the migration:

{
  "type": "add_column",
  "table": "user",
  "column": {
    "name": "status",
    "type": "text",
    "nullable": false
  },
  "fill_with": "'active'"
}

The fill_with value is used to backfill existing rows during migration.

Workflow

# 1. Edit your model files (models/*.json)
# 2. Check what changed
vespertide diff

# 3. Preview SQL
vespertide sql

# 4. Create migration (auto-generated)
vespertide revision -m "add status column"

# 5. If prompted for fill_with, provide a value
# 6. Never touch migration files after this

Model Structure

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "table_name",
  "description": "Optional table description",
  "columns": [
    /* ColumnDef[] */
  ]
}

Note: constraints field is optional. Only add it when you need CHECK constraints.

Required Fields

Optional Fields

Column Definition

Required Fields

{
  "name": "column_name",
  "type": "ColumnType",
  "nullable": false
}

Optional Fields

Column Types

Simple Types (string values)

Complex Types (object values)

VARCHAR

{ "kind": "varchar", "length": 255 }

CHAR

{ "kind": "char", "length": 2 }

NUMERIC/DECIMAL

{ "kind": "numeric", "precision": 10, "scale": 2 }

ENUM (STRONGLY RECOMMENDED)

String Enum:

{
  "kind": "enum",
  "name": "order_status",
  "values": ["pending", "processing", "shipped", "delivered", "cancelled"]
}

Integer Enum:

{
  "kind": "enum",
  "name": "priority_level",
  "values": [
    { "name": "low", "value": 0 },
    { "name": "medium", "value": 1 },
    { "name": "high", "value": 2 },
    { "name": "critical", "value": 3 }
  ]
}

Inline Constraints (PREFERRED)

Always define constraints directly on columns.

Guidelines Summary

MUST DO

1. •Always include $schema
2. •Always specify nullable
3. •Index foreign key columns - Add "index": true
4. •Use inline constraints

MUST NOT DO

1. •Never use PascalCase for reference actions - Use "cascade" not "Cascade"
2. •Never skip schema validation
3. •Never add NOT NULL columns without default
4. •Never manually edit exported ORM files
5. •Never use jsonb type - Use json instead