Vespertide

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 (variable-length string with limit)

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

CHAR (fixed-length string)

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

NUMERIC/DECIMAL (exact precision)

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

ENUM (STRONGLY RECOMMENDED)

Use enums instead of text columns with CHECK constraints for status fields, categories, and any fixed set of values.

String Enum (PostgreSQL native enum):

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

Integer Enum (stored as INTEGER, no DB migration needed for new values):

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

Why Integer Enums?

• Adding new values requires NO database migration
• Application-level enum mapping only
• Better for frequently-changing value sets
• Works identically across PostgreSQL, MySQL, SQLite

Custom Type (AVOID - last resort only)

WARNING: Avoid custom types. They break cross-database compatibility. Use built-in types or redesign your schema.

{ "kind": "custom", "custom_type": "POINT" }
{ "kind": "custom", "custom_type": "TSVECTOR" }

Enum Best Practices (RECOMMENDED)

When to Use Enums

String Enum Example

{
  "name": "status",
  "type": {
    "kind": "enum",
    "name": "article_status",
    "values": ["draft", "review", "published", "archived"]
  },
  "nullable": false,
  "default": "'draft'"
}

Integer Enum Example

{
  "name": "role",
  "type": {
    "kind": "enum",
    "name": "user_role",
    "values": [
      { "name": "guest", "value": 0 },
      { "name": "user", "value": 10 },
      { "name": "moderator", "value": 50 },
      { "name": "admin", "value": 100 }
    ]
  },
  "nullable": false,
  "default": 0
}

Tip: Leave gaps in integer values (0, 10, 50, 100) to allow inserting new values in between without renumbering.

Inline Constraints (PREFERRED)

Always define constraints directly on columns. This is cleaner, more readable, and the recommended pattern. Use table-level constraints array ONLY for composite keys or CHECK expressions.

Primary Key

Simple:

{ "name": "id", "type": "integer", "nullable": false, "primary_key": true }

With auto-increment:

{ "name": "id", "type": "integer", "nullable": false, "primary_key": { "auto_increment": true } }

Unique

Simple unique:

{ "name": "email", "type": "text", "nullable": false, "unique": true }

Named unique (for composite):

{ "name": "tenant_id", "type": "integer", "nullable": false, "unique": ["uq_tenant_user"] },
{ "name": "username", "type": "text", "nullable": false, "unique": ["uq_tenant_user"] }

Index

Simple index:

{ "name": "email", "type": "text", "nullable": false, "index": true }

Composite index:

{ "name": "user_id", "type": "integer", "nullable": false, "index": ["idx_user_created"] },
{ "name": "created_at", "type": "timestamptz", "nullable": false, "index": ["idx_user_created"] }

Foreign Key

Object syntax (recommended):

{
  "name": "user_id",
  "type": "integer",
  "nullable": false,
  "foreign_key": {
    "ref_table": "user",
    "ref_columns": ["id"],
    "on_delete": "cascade",
    "on_update": null
  },
  "index": true
}

Shorthand syntax:

{
  "name": "user_id",
  "type": "integer",
  "nullable": false,
  "foreign_key": "user.id",
  "index": true
}

Reference Actions (snake_case):

• "cascade" - Delete/update child rows
• "restrict" - Prevent if children exist
• "set_null" - Set to NULL
• "set_default" - Set to default value
• "no_action" - Defer check (PostgreSQL)

Always add "index": true on foreign key columns for query performance.

Table-Level Constraints

IMPORTANT: Always prefer inline constraints (primary_key, unique, index, foreign_key on columns). Table-level constraints is ONLY needed for CHECK expressions.

When Table-Level is Required

Inline Works for Everything Else

Syntax (CHECK only)

"constraints": [
  { "type": "check", "name": "check_positive_amount", "expr": "amount > 0" },
  { "type": "check", "name": "check_dates", "expr": "end_date > start_date" }
]

What NOT to Put in Table-Level

// BAD - Use inline instead
"constraints": [
  { "type": "primary_key", "columns": ["tenant_id", "user_id"] },  // Use: "primary_key": true on each column
  { "type": "unique", "columns": ["email"] },                       // Use: "unique": true on column
  { "type": "foreign_key", "columns": ["user_id"], ... },           // Use: "foreign_key": {...} on column
  { "type": "index", "columns": ["created_at"] }                    // Use: "index": true on column
]

// GOOD - Only CHECK constraints
"constraints": [
  { "type": "check", "name": "check_amount", "expr": "amount >= 0" }
]

Composite Primary Key Example (Inline)

{
  "name": "user_role",
  "columns": [
    { "name": "user_id", "type": "integer", "nullable": false, "primary_key": true },
    { "name": "role_id", "type": "integer", "nullable": false, "primary_key": true }
  ]
}

Both columns with "primary_key": true creates a single composite primary key (user_id, role_id).

Default Values

{ "name": "status", "type": "text", "nullable": false, "default": "'active'" },
{ "name": "count", "type": "integer", "nullable": false, "default": 0 },
{ "name": "enabled", "type": "boolean", "nullable": false, "default": true },
{ "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" }

Complete Examples

User Table with Enum Status

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "user",
  "columns": [
    { "name": "id", "type": "integer", "nullable": false, "primary_key": { "auto_increment": true } },
    { "name": "email", "type": "text", "nullable": false, "unique": true, "index": true },
    { "name": "name", "type": { "kind": "varchar", "length": 100 }, "nullable": false },
    { 
      "name": "status", 
      "type": { 
        "kind": "enum", 
        "name": "user_status", 
        "values": ["pending", "active", "suspended", "deleted"] 
      }, 
      "nullable": false, 
      "default": "'pending'" 
    },
    { "name": "metadata", "type": "json", "nullable": true },
    { "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" },
    { "name": "updated_at", "type": "timestamptz", "nullable": true }
  ]
}

Order Table with Integer Enum Priority

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "order",
  "columns": [
    { "name": "id", "type": "uuid", "nullable": false, "primary_key": true, "default": "gen_random_uuid()" },
    { 
      "name": "customer_id", 
      "type": "integer", 
      "nullable": false, 
      "foreign_key": { "ref_table": "customer", "ref_columns": ["id"], "on_delete": "restrict" }, 
      "index": true 
    },
    { "name": "total", "type": { "kind": "numeric", "precision": 10, "scale": 2 }, "nullable": false },
    { 
      "name": "priority", 
      "type": { 
        "kind": "enum", 
        "name": "order_priority", 
        "values": [
          { "name": "low", "value": 0 },
          { "name": "normal", "value": 10 },
          { "name": "high", "value": 20 },
          { "name": "urgent", "value": 30 }
        ]
      }, 
      "nullable": false, 
      "default": 10 
    },
    { 
      "name": "status", 
      "type": { 
        "kind": "enum", 
        "name": "order_status", 
        "values": ["pending", "confirmed", "shipped", "delivered", "cancelled"] 
      }, 
      "nullable": false, 
      "default": "'pending'" 
    },
    { "name": "notes", "type": "text", "nullable": true },
    { "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" }
  ],
  "constraints": [
    { "type": "check", "name": "check_total_positive", "expr": "total >= 0" }
  ]
}

Many-to-Many Join Table

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "user_role",
  "columns": [
    { 
      "name": "user_id", 
      "type": "integer", 
      "nullable": false, 
      "primary_key": true, 
      "foreign_key": { "ref_table": "user", "ref_columns": ["id"], "on_delete": "cascade" } 
    },
    { 
      "name": "role_id", 
      "type": "integer", 
      "nullable": false, 
      "primary_key": true, 
      "foreign_key": { "ref_table": "role", "ref_columns": ["id"], "on_delete": "cascade" },
      "index": true
    },
    { "name": "granted_at", "type": "timestamptz", "nullable": false, "default": "NOW()" },
    { "name": "granted_by", "type": "integer", "nullable": true, "foreign_key": "user.id" }
  ]
}

Article with Composite Index

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "article",
  "columns": [
    { "name": "id", "type": "big_int", "nullable": false, "primary_key": { "auto_increment": true } },
    { "name": "author_id", "type": "integer", "nullable": false, "foreign_key": "user.id", "index": ["idx_author_published"] },
    { "name": "title", "type": { "kind": "varchar", "length": 200 }, "nullable": false },
    { "name": "slug", "type": { "kind": "varchar", "length": 200 }, "nullable": false, "unique": true },
    { "name": "content", "type": "text", "nullable": false },
    { 
      "name": "status", 
      "type": { "kind": "enum", "name": "article_status", "values": ["draft", "review", "published", "archived"] }, 
      "nullable": false, 
      "default": "'draft'" 
    },
    { "name": "published_at", "type": "timestamptz", "nullable": true, "index": ["idx_author_published"] },
    { "name": "view_count", "type": "integer", "nullable": false, "default": 0 },
    { "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" }
  ],
  "constraints": [
    { "type": "check", "name": "check_view_count", "expr": "view_count >= 0" }
  ]
}

Guidelines Summary

MUST DO

1. Always include $schema - No exceptions
2. Always specify nullable - Required for every column
3. Validate against schema - Before saving, check IDE errors
4. Index foreign key columns - Add "index": true
5. Use inline constraints - primary_key, unique, index, foreign_key ON the column

SHOULD DO

1. Use enums for status/category fields - Prefer over text + CHECK
2. Use integer enums for expandable sets - No migration needed for new values
3. Use timestamptz over timestamp - Timezone-aware is safer
4. Use json type for JSON data - Works across all backends (PostgreSQL, MySQL, SQLite)

MUST NOT DO

1. Never use PascalCase for reference actions - Use "cascade" not "Cascade"
2. Never skip schema validation - Prevents runtime errors
3. Never add NOT NULL columns without default - Requires fill_with in migration
4. Never use table-level constraints - Except for CHECK expressions only
5. Never manually create/edit migration files - Only fill_with exception
6. Never manually edit exported ORM files - Use vespertide export to regenerate
7. Never use jsonb type - Use json instead (JSONB not supported in SQLite)
8. Never use custom types - Use built-in types only for cross-database compatibility
9. Never use array types - Use a separate join table instead (arrays not supported in SQLite)

Naming Conventions

Quick Reference Card

COLUMN TYPES (simple)
────────────────────────────────────────
integer, big_int, small_int          Numbers
real, double_precision               Floats
text                                 Strings
boolean                              Flags
date, time, timestamp, timestamptz   Time
interval                             Duration
uuid                                 UUIDs
json                                 JSON
bytea                                Binary
inet, cidr, macaddr                  Network
xml                                  XML

COLUMN TYPES (complex)
────────────────────────────────────────
{ "kind": "varchar", "length": N }
{ "kind": "char", "length": N }
{ "kind": "numeric", "precision": P, "scale": S }
{ "kind": "enum", "name": "...", "values": [...] }
{ "kind": "custom", "custom_type": "..." }

REFERENCE ACTIONS (snake_case!)
────────────────────────────────────────
cascade, restrict, set_null, set_default, no_action

CONSTRAINT TYPES
────────────────────────────────────────
primary_key, unique, foreign_key, check, index

Troubleshooting