schema.json

DOCS • Concepts

---

The schema.js spec.

See also ➞ Migrations

Spec

└ database

└ table

└ column

└ constraint

└ index

Rename Rules

To rename a database or table or column or constraint or index, use a temporary attribute (called a diff tag) to specify the new name: $name, while leaving the old name in place:

{
    "name": "database_1",
    "$name": "new_database_1"
}

Old names are needed in-place as a means of identification when diffing against your active DB schema. The temporary diff tag ($name) is automatically removed after new name has been picked up by Linked QL during migration.

Example 1:

Show

[
    {
        // string - required
        "name": "database_1",
        // TableSchemaSpec[]
        "tables": [
            {
                // string - required
                "name": "users",
                // ColumnSchemaSpec[] - required
                "columns": [
                    {
                        // string - required
                        "name": "id",
                        // string or array like ["int",3] - required
                        "type": "int",
                        // boolean or PrimaryKeySchemaSpec
                        "primaryKey": true,
                        // boolean or IdentityConstraintSchemaSpec
                        "identity": true
                    },
                    {
                        // string - required
                        "name": "first_name",
                        // array or string like "varchar" - required
                        "type": ["varchar", 101]
                    },
                    {
                        // string - required
                        "name": "last_name",
                        // array or string like "varchar" - required
                        "type": ["varchar", 101]
                    },
                    {
                        // string - required
                        "name": "full_name",
                        // array or string like "varchar" - required
                        "type": ["varchar", 101],
                        // string or ExpressionConstraintSchemaSpec
                        "expression": { "join": ["first_name", " ", "last_name"]}
                    },
                    {
                        // string - required
                        "name": "email",
                        // array or string like "varchar" - required
                        "type": ["varchar", 50],
                        // boolean or UniqueKeySchemaSpec
                        "uniqueKey": true,
                        // boolean
                        "notNull": true,
                        // string or CheckConstraintSchemaSpec
                        "check": { "expr": { "matches": ["email", "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$"] } }
                    },
                    {
                        // string - required
                        "name": "parent",
                        // string or array like ["int",3] - required
                        "type": "int",
                        // boolean
                        "notNull": true,
                        // ForeignKeySchemaSpec
                        "foreignKey": {
                            // string or string[] like ["database_2", "users"] - required
                            "targetTable": "users",
                            // string[] - required
                            "targetColumns": ["id"],
                            // string
                            "matchRule": "full",
                            // string or object like { rule: "cascade", columns: ["col1"] }
                            "updateRule": "cascade",
                            // string or object like { rule: "restrict", columns: ["col1"] }
                            "deleteRule": "restrict"
                        }
                    }
                ],
                // TableConstraintSchemaType[]
                "constraints": [
                    {
                        // string - required
                        "type": "PRIMARY_KEY",
                        // string[] - required
                        "columns": ["id_2"],
                    },
                    {
                        // string - required
                        "type": "FOREIGN_KEY",
                        // string[] - required
                        "columns": ["parent_2"],
                        // string or string[] like ["database_2", "users"] - required
                        "targetTable": "users",
                        // string[] - required
                        "targetColumns": ["id"],
                        // string
                        "matchRule": "full",
                        // string or object like { rule: "cascade", columns: ["col1"] }
                        "updateRule": "cascade",
                        // string or object like { rule: "restrict", columns: ["col1"] }
                        "deleteRule": "restrict"
                    },
                    {
                        // string - required
                        "type": "UNIQUE_KEY",
                        // string
                        "name": "constraint_name",
                        // string[] - required
                        "columns": ["parent", "full_name"]
                    },
                    {
                        // string - required
                        "type": "CHECK",
                        // string - required
                        "expr": { "matches": ["email", "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$"] }
                    }
                ],
                // IndexSchemaSpec[]
                "indexes": [
                    {
                        // string - required
                        "type": "FULLTEXT",
                        // string[] - required
                        "columns": ["full_name"]
                    },
                    {
                        // string - required
                        "type": "SPATIAL",
                        // string[] - required
                        "columns": ["full_name"]
                    }
                ]
            }
        ]
    },
    {
        // string - required
        "name": "database_2",
        // TableSchemaSpec[]
        "tables": []
    }
]