Create table

Creating tables is done with an object called createtable. Column types and some column attributes depend on the root-level engine value.

Format createtable like this. The JSON snippet below shows createtable inside a MySQL version source. Every object key in createtable represents a table. Every object key inside a table represents a column, except reserved table keys such as primary_key, foreign_key, index, and check.


{
  "engine": "mysql",
  "name": "template-name",
  "version": [
    {
      "_id": "1.0.0",
      "createtable": {
        "table1": {
          "primary_key": "id",
          "id": {
            "type": "INT",
            "auto_increment": true
          },
          "name": {
            "type": "VARCHAR",
            "length": 20,
            "default": "John"
          }
        },
        "table2": {
          "primary_key": "id",
          "id": {
            "type": "INT"
          },
          "description": {
            "type": "TEXT",
            "null": true
          }
        }
      }
    }
  ]
}

For Postgres, use Postgres column types and identity syntax. Postgres does not use auto_increment; use generated with "ALWAYS" or "BY DEFAULT" for identity columns.


{
  "engine": "postgres",
  "name": "template-name",
  "version": [
    {
      "_id": "1.0.0",
      "createtable": {
        "table1": {
          "primary_key": "id",
          "id": {
            "type": "INTEGER",
            "generated": "ALWAYS"
          },
          "name": {
            "type": "VARCHAR",
            "length": 20,
            "default": "John"
          }
        }
      }
    }
  ]
}

Foreign keys

A table may declare foreign keys with the foreign_key key. It holds an array of constraints. name, from, references and to are required; on_delete is optional.


{
  "createtable": {
    "orders": {
      "primary_key": "id",
      "id": { "type": "INT", "auto_increment": true },
      "account_id": { "type": "INT" },
      "foreign_key": [
        {
          "name": "orders_account_fk",
          "from": "account_id",
          "references": "accounts",
          "to": "id",
          "on_delete": "cascade"
        }
      ]
    }
  }
}

Indexes

Indexes are declared with the index key, an array of index definitions. name and columns are required. unique (boolean) and type (e.g. "btree") are optional. On Postgres a condition may be supplied to create a partial index — see Conditions for the condition format. MySQL does not support partial indexes.


{
  "createtable": {
    "users": {
      "primary_key": "id",
      "id": { "type": "INT", "auto_increment": true },
      "email": { "type": "VARCHAR", "length": 255 },
      "index": [
        { "name": "users_email_idx", "columns": ["email"], "unique": true }
      ]
    }
  }
}

Check constraints

The check key holds an array of check constraints. Each has a name and a condition.


{
  "createtable": {
    "events": {
      "primary_key": "id",
      "id": { "type": "INT", "auto_increment": true },
      "start_at": { "type": "DATETIME" },
      "end_at": { "type": "DATETIME" },
      "check": [
        {
          "name": "events_valid_time",
          "condition": {
            "type": "comparison",
            "op": ">",
            "left": { "type": "column", "name": "end_at" },
            "right": { "type": "column", "name": "start_at" }
          }
        }
      ]
    }
  }
}

Conditions

Conditions are objects tagged by a type field. They are used by check constraints and Postgres partial indexes.

`type`	Fields	Renders to
`comparison`	`op` (`=`, `!=`, `<`, `<=`, `>`, `>=`), `left`, `right`	`left op right`
`and`	`conditions` (array)	`(...) AND (...)`
`or`	`conditions` (array)	`(...) OR (...)`
`not`	`condition`	`NOT (...)`
`is_null`	`column`	`column IS NULL`
`is_not_null`	`column`	`column IS NOT NULL`
`in`	`column`, `values` (array)	`column IN (...)`
`not_in`	`column`, `values` (array)	`column NOT IN (...)`

left and right in a comparison are expressions: either a column ({ "type": "column", "name": "..." }) or a literal value ({ "type": "value", "value": ... }).