// Define database schemas in JSON and generate migration plans. Use this skill when creating or modifying database models, defining tables with columns and inline constraints (primary_key, unique, index, foreign_key) for Vespertide-based projects.
| name | vespertide |
| description | Define database schemas in JSON and generate migration plans. Use this skill when creating or modifying database models, defining tables with columns and inline constraints (primary_key, unique, index, foreign_key) for Vespertide-based projects. |
This skill helps you create and manage database models using Vespertide, a declarative schema management tool.
cargo install vespertide-cli
vespertide init # Initialize project with vespertide.json
vespertide new <name> # Create a new model template
vespertide diff # Show pending changes
vespertide sql # Preview SQL for pending migration
vespertide revision -m "message" # Create migration file
vespertide status # Show project status
vespertide log # List applied migrations
Models are JSON files in the models/ directory:
{
"$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
"name": "table_name",
"columns": [],
"constraints": []
}
| Field | Type | Description |
|---|---|---|
name | string | Table name (snake_case) |
columns | array | Column definitions |
constraints | array | Table-level constraints (can be empty []) |
Note: The indexes field has been removed. Use inline index fields on columns instead (see Inline Constraints below).
{
"name": "column_name",
"type": "ColumnType",
"nullable": false
}
| Field | Type | Description |
|---|---|---|
default | string | Default value expression (e.g., "NOW()", "'pending'") |
comment | string | Column description |
primary_key | boolean | Inline primary key |
unique | boolean | string | string[] | Inline unique constraint |
index | boolean | string | string[] | Inline index |
foreign_key | object | Inline foreign key definition |
Column types in JSON can be either simple (string) or complex (object) values.
Simple types are represented as strings in JSON (snake_case):
| Type | SQL Type | Use Cases |
|---|---|---|
"small_int" | SMALLINT | Small integers (-32768 to 32767) |
"integer" | INTEGER | IDs, counters |
"big_int" | BIGINT | Large numbers |
"real" | REAL | Single precision float |
"double_precision" | DOUBLE PRECISION | Double precision float |
"text" | TEXT | Strings |
"boolean" | BOOLEAN | Flags |
"date" | DATE | Date only |
"time" | TIME | Time only |
"timestamp" | TIMESTAMP | Date/time without timezone |
"timestamptz" | TIMESTAMPTZ | Date/time with timezone |
"bytea" | BYTEA | Binary data |
"uuid" | UUID | UUIDs |
"json" | JSON | JSON data |
"jsonb" | JSONB | Binary JSON (indexable) |
"inet" | INET | IPv4/IPv6 address |
"cidr" | CIDR | Network address |
"macaddr" | MACADDR | MAC address |
Note: In JSON, simple types are written as lowercase strings (e.g., "integer", "text"). The Rust enum uses SimpleColumnType wrapped in ColumnType::Simple().
Complex types are represented as objects with a kind field:
VARCHAR with length:
{ "kind": "varchar", "length": 255 }
Custom types:
{ "kind": "custom", "custom_type": "DECIMAL(10,2)" }
{ "kind": "custom", "custom_type": "NUMERIC(20,8)" }
{ "kind": "custom", "custom_type": "INTERVAL" }
{ "kind": "custom", "custom_type": "UUID" }
Note: In Rust code, complex types are represented as ColumnType::Complex(ComplexColumnType::Varchar { length }) or ColumnType::Complex(ComplexColumnType::Custom { custom_type }).
{
"name": "id",
"type": "integer",
"nullable": false,
"primary_key": true
}
{ "name": "email", "type": "text", "nullable": false, "unique": true }
Named or composite unique:
{ "name": "tenant_id", "type": "integer", "nullable": false, "unique": ["uq_tenant_user"] },
{ "name": "username", "type": "text", "nullable": false, "unique": ["uq_tenant_user"] }
{ "name": "email", "type": "text", "nullable": false, "index": true }
Composite index:
{ "name": "user_id", "type": "integer", "nullable": false, "index": ["idx_user_date"] },
{ "name": "created_at", "type": "timestamp", "nullable": false, "index": ["idx_user_date"] }
{
"name": "user_id",
"type": "integer",
"nullable": false,
"foreign_key": {
"ref_table": "user",
"ref_columns": ["id"],
"on_delete": "Cascade",
"on_update": null
},
"index": true
}
Reference actions: "Cascade", "Restrict", "SetNull", "SetDefault", "NoAction"
"constraints": [
{ "type": "primary_key", "columns": ["id"] },
{ "type": "unique", "name": "uq_email", "columns": ["email"] },
{ "type": "foreign_key", "name": "fk_post_user", "columns": ["user_id"], "ref_table": "user", "ref_columns": ["id"], "on_delete": "Cascade" },
{ "type": "check", "name": "check_positive", "expr": "amount > 0" }
]
Prefer inline indexes on column definitions instead of table-level indexes:
{
"name": "email",
"type": "text",
"nullable": false,
"index": true
}
For composite indexes, use the same index name on multiple columns:
{ "name": "user_id", "type": "integer", "nullable": false, "index": ["idx_user_date"] },
{ "name": "created_at", "type": "timestamp", "nullable": false, "index": ["idx_user_date"] }
{
"$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": true },
{ "name": "email", "type": "text", "nullable": false, "unique": true, "index": true },
{ "name": "name", "type": "text", "nullable": false },
{ "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" }
],
"constraints": []
}
{
"$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
"name": "post",
"columns": [
{ "name": "id", "type": "integer", "nullable": false, "primary_key": true },
{ "name": "user_id", "type": "integer", "nullable": false, "foreign_key": { "ref_table": "user", "ref_columns": ["id"], "on_delete": "Cascade" }, "index": true },
{ "name": "title", "type": "text", "nullable": false },
{ "name": "content", "type": "text", "nullable": false },
{ "name": "published", "type": "boolean", "nullable": false, "default": "false" },
{ "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" }
],
"constraints": []
}
{
"$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_amount", "type": { "kind": "custom", "custom_type": "DECIMAL(10,2)" }, "nullable": false },
{ "name": "status", "type": "text", "nullable": false, "default": "'pending'" },
{ "name": "metadata", "type": "jsonb", "nullable": true },
{ "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" }
],
"constraints": [
{ "type": "check", "name": "check_total_positive", "expr": "total_amount >= 0" }
]
}
{
"$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": "assigned_at", "type": "timestamptz", "nullable": false, "default": "NOW()" }
],
"constraints": []
}
$schema for IDE validation and autocompletionnullable on every columnconstraints even if unusedprimary_key, unique, index, foreign_key) over table-level definitionsindex on foreign key columns for query performance (e.g., "index": true)snake_case (e.g., user_role)snake_case (e.g., created_at)idx_{table}_{columns}uq_{table}_{columns}fk_{table}_{ref_table}check_{description}created_at: "default": "NOW()", nullable: falseupdated_at: nullable: true (managed by application)"true" or "false"default value or fill_with in migration