Skip to main content

SQLite Schema

schema

The schema block describes a database schema.

schema "public" {
...
}

schema attributes

NameRequiredValue
commentfalsestring
namefalsestring

schema constraints

ConstraintValue
Requiredfalse
Require Name (e.g., schema "name" )true

table

The table block describes a table in a database schema.

table "users" {
schema = schema.public
column "id" {
type = int
}
...
}

table attributes

NameRequiredValue
commentfalsestring
schematrue

Object reference to schema

strictfalsebool
without_rowidfalsebool

table blocks

table.check

table.check attributes
NameRequiredValue
commentfalsestring
exprtruestring
table.check constraints
ConstraintValue
Requiredfalse
Require Namefalse
Repeatabletrue

table.column

table.column attributes
NameRequiredValue
asfalsestring
auto_incrementfalsebool
commentfalsestring
defaultfalse

Column default value can be one of:

  1. bool
  2. string
  3. number
  4. Raw expression defined with sql("expr")
nullfalsebool
typetrue

Column type can be one of:

  1. Schema type
  2. Raw expression defined with sql("expr")
unsignedfalsebool
table.column blocks

table.column.as

table.column.as attributes
NameRequiredValue
exprtruestring
typefalse

enum (VIRTUAL, STORED)

table.column constraints
ConstraintValue
Requiredfalse
Require Name (e.g., table.column "name" )true
Mutually exclusive sets[as (attribute), as (block)]

table.foreign_key

table.foreign_key attributes
NameRequiredValue
columnstrue

Foreign key columns can be one of:

  1. List of object reference to column
  2. List of object reference to table.column
commentfalsestring
on_deletefalse

enum (NO_ACTION, RESTRICT, CASCADE, SET_NULL, SET_DEFAULT)

on_updatefalse

enum (NO_ACTION, RESTRICT, CASCADE, SET_NULL, SET_DEFAULT)

ref_columnstrue

Foreign key reference columns can be one of:

  1. List of object reference to column
  2. List of object reference to table.column
table.foreign_key constraints
ConstraintValue
Requiredfalse
Require Name (e.g., table.foreign_key "name" )true

table.index

table.index attributes
NameRequiredValue
columnsfalse

Index columns can be one of:

  1. List of object reference to column
  2. List of object reference to table.column
commentfalsestring
uniquefalsebool
wherefalsestring
table.index blocks

table.index.on

table.index.on attributes
NameRequiredValue
columnfalse

Index columns can be one of:

  1. Object reference to column
  2. Object reference to table.column
descfalsebool
exprfalsestring
table.index.on constraints
ConstraintValue
Requiredfalse
Require Namefalse
Repeatabletrue
Mutually exclusive sets[column, expr]
table.index constraints
ConstraintValue
Requiredfalse
Require Name (e.g., table.index "name" )true
Mutually exclusive sets[columns, on]
One of required sets[columns, on]

table.primary_key

table.primary_key attributes
NameRequiredValue
columnstrue

Primary key columns can be one of:

  1. List of object reference to column
  2. List of object reference to table.column
commentfalsestring

table constraints

ConstraintValue
Requiredfalse
Require Name (e.g., table "name" )true
Allow Qualifier (e.g., table "schema" "name" )true

trigger

The trigger block describes a trigger on a table in a database schema.

trigger "trigger_orders_audit" {
on = table.orders
...
}

trigger attributes

NameRequiredValue
astruestring
forfalse

enum (ROW)

foreachfalse

enum (ROW)

ontrue

Trigger on can be one of:

  1. Object reference to table
  2. Object reference to view
whenfalsestring

trigger blocks

trigger.after

trigger.after attributes
NameRequiredValue
deletefalsebool
insertfalsebool
updatefalsebool
update_offalse

Trigger update_of columns can be one of:

  1. List of object reference to view.column
  2. List of object reference to table.column
trigger.after constraints
ConstraintValue
Requiredfalse
Require Namefalse
Mutually exclusive sets[insert, delete, update, update_of]
One of required sets[insert, delete, update, update_of]

trigger.before

trigger.before attributes
NameRequiredValue
deletefalsebool
insertfalsebool
updatefalsebool
update_offalse

Trigger update_of columns can be one of:

  1. List of object reference to view.column
  2. List of object reference to table.column
trigger.before constraints
ConstraintValue
Requiredfalse
Require Namefalse
Mutually exclusive sets[insert, delete, update, update_of]
One of required sets[insert, delete, update, update_of]

trigger.instead_of

trigger.instead_of attributes
NameRequiredValue
deletefalsebool
insertfalsebool
updatefalsebool
update_offalse

Trigger update_of columns can be one of:

  1. List of object reference to view.column
  2. List of object reference to table.column
trigger.instead_of constraints
ConstraintValue
Requiredfalse
Require Namefalse
Mutually exclusive sets[insert, delete, update, update_of]
One of required sets[insert, delete, update, update_of]

trigger constraints

ConstraintValue
Requiredfalse
Require Name (e.g., trigger "name" )true
Repeatabletrue
Mutually exclusive sets[foreach, for], [before, after, instead_of]
One of required sets[before, after, instead_of]

view

The view block describes a view in a database schema.

view "clean_users" {
schema = schema.public
column "id" {
type = int
}
...
}

view attributes

NameRequiredValue
astruestring
commentfalsestring
depends_onfalse

List of object references

schematrue

Object reference to schema

view blocks

view.column

view.column attributes
NameRequiredValue
commentfalsestring
nullfalsebool
typetrue

Column type can be one of:

  1. Schema type
  2. Raw expression defined with sql("expr")
view.column constraints
ConstraintValue
Requiredfalse
Require Name (e.g., view.column "name" )true

view constraints

ConstraintValue
Requiredfalse
Require Name (e.g., view "name" )true
Allow Qualifier (e.g., view "schema" "name" )true