SQL Resources
The sqlspec package defines the resource types used to describe an SQL database schema. Used with the Atlas HCL syntax, it is easy to compose documents describing the desired state of a SQL database.
Schema​
The schema
object describes a database schema. A DATABASE
in MySQL and SQLite, or a SCHEMA
in PostgreSQL.
An HCL file can contain 1 or more schema objects.
- MySQL
- PostgreSQL
- SQLite
In MySQL and MariaDB, the schema
resource can contain the charset
and collate
attributes. Read more about them
in MySQL or
MariaDB websites.
# Schema with attributes.
schema "market" {
charset = "utf8mb4"
collate = "utf8mb4_0900_ai_ci"
comment = "A schema comment"
}
# Schema without attributes.
schema "orders" {}
schema "public" {
comment = "A schema comment"
}
schema "private" {}
Atlas does not support attached databases, and support only the default
database (i.e. main
).
schema "main" {}
Table​
A table
describes a table in a SQL database. A table hold its columns, indexes, constraints, and additional attributes
that are supported by the different drivers.
table "users" {
schema = schema.public
column "id" {
type = int
}
column "name" {
type = varchar(255)
}
column "manager_id" {
type = int
}
primary_key {
columns = [
column.id
]
}
index "idx_name" {
columns = [
column.name
]
unique = true
}
foreign_key "manager_fk" {
columns = [column.manager_id]
ref_columns = [column.id]
on_delete = CASCADE
on_update = NO_ACTION
}
}
Check​
A check
is a child resource of a table
that describes a CHECK
constraint.
table "products" {
column "price" {
type = float
}
check "positive price" {
expr = "price > 0"
}
}
Partitions​
Table partitioning refers to splitting logical large tables into smaller physical ones.
Atlas currently only supports PostgreSQL. Support for the remaining dialects will be added in future versions.
table "logs" {
schema = schema.public
column "date" {
type = date
}
column "text" {
type = integer
}
partition {
type = RANGE
columns = [column.date]
}
}
table "metrics" {
schema = schema.public
column "x" {
type = integer
}
column "y" {
type = integer
}
partition {
type = RANGE
by {
column = column.x
}
by {
expr = "floor(y)"
}
}
}
Table Qualification​
In some cases, an Atlas DDL document may contain multiple tables of the same name. This usually happens
when the same table name appears in two different schemas. In these cases, the table names must be
disambiguated by using resource qualifers. The following document describes a
database that contains two schemas named a
and b
, and both of them contain a table named users
.
schema "a" {}
schema "b" {}
table "a" "users" {
schema = schema.a
// .. columns
}
table "b" "users" {
schema = schema.b
// .. columns
}
Column​
A column
is a child resource of a table
.
column "name" {
type = text
null = false
}
column "age" {
type = integer
default = 42
}
column "active" {
type = tinyint(1)
default = true
}
Properties​
Name | Kind | Type | Description |
---|---|---|---|
null | attribute | bool | Defines whether the column is nullable. |
type | attribute | *schemahcl.Type | Defines the type of data that can be stored in the column. |
default | attribute | *schemahcl.LiteralValue | Defines the default value of the column. |
Generated Columns​
Generated columns are columns whose their values are computed using other columns or by deterministic expressions.
- MySQL
- PostgreSQL
- SQLite
table "users" {
schema = schema.test
column "a" {
type = int
}
column "b" {
type = int
# In MySQL, generated columns are VIRTUAL by default.
as = "a * 2"
}
column "c" {
type = int
as {
expr = "a * b"
type = STORED
}
}
}
table "users" {
schema = schema.test
column "a" {
type = int
}
column "b" {
type = int
# In PostgreSQL, generated columns are STORED by default.
as = "a * 2"
}
column "c" {
type = int
as {
expr = "a * b"
type = STORED
}
}
}
table "users" {
schema = schema.test
column "a" {
type = int
}
column "b" {
type = int
# In SQLite, generated columns are VIRTUAL by default.
as = "a * 2"
}
column "c" {
type = int
as {
expr = "a * b"
type = STORED
}
}
}
Note, it is recommended to use the --dev-url
option when generated columns are used.
Column Types​
The SQL dialects supported by Atlas (Postgres, MySQL, MariaDB, and SQLite) vary in the types they support. At this point, the Atlas DDL does not attempt to abstract away the differences between various databases. This means that the schema documents are tied to a specific database engine and version. This may change in a future version of Atlas as we plan to add "Virtual Types" support. This section lists the various types that are supported in each database.
For a full list of supported column types, click here.
Primary Key​
A primary_key
is a child resource of a table
, and it defines the table's
primary key.
Example​
primary_key {
columns = [column.id]
}
Properties​
Name | Kind | Type | Description |
---|---|---|---|
columns | resource | reference (list) | A list of references to columns that comprise the primary key. |
Foreign Key​
Foreign keys are child resources of a table
, and it defines some columns in the table
as references to columns in other tables.
Example​
table "users" {
schema = schema.public
column "id" {
type = integer
}
primary_key {
columns = [column.id]
}
}
table "orders" {
schema = schema.market
// ...
column "owner_id" {
type = integer
}
foreign_key "owner_id" {
columns = [column.owner_id]
ref_columns = [table.users.column.id]
on_update = NO_ACTION
on_delete = NO_ACTION
}
}
Referencing Qualified Tables​
If a foreign key references a column in a qualified table, it is referenced
using table.<qualifier>.<table_name>.column.<column_name>
:
table "public" "users" {
schema = schema.public
column "id" {
type = integer
}
primary_key {
columns = [column.id]
}
}
table "admin" "users" {
schema = schema.admin
// ...
column "external_id" {
type = integer
}
foreign_key "external_id" {
columns = [column.external_id]
ref_columns = [table.admin.users.column.id]
on_update = NO_ACTION
on_delete = NO_ACTION
}
}
Properties​
Name | Kind | Type | Description |
---|---|---|---|
columns | attribute | reference (list) | The columns that reference other columns. |
ref_columns | attribute | reference (list) | The referenced columns. |
on_update | attribute | schema.ReferenceOption | Defines what to do on update. |
on_delete | attribute | schema.ReferenceOption | Defines what to do on delete. |
Index​
Indexes are child resources of a table
, and it defines an index on the table.
Example​
index "idx_name" {
columns = [
column.name
]
unique = true
}
index "idx_name" {
on {
column = column.rank
}
on {
column = column.score
desc = true
}
unique = true
}
index "idx_name" {
type = GIN
columns = [column.data]
}
index "idx_range" {
type = BRIN
columns = [column.range]
page_per_range = 128
}
index "idx_include" {
columns = [column.range]
include = [column.version]
}
index "idx_operator_class" {
type = GIN
on {
column = column.j
ops = jsonb_path_ops
}
}
Properties​
Name | Kind | Type | Description |
---|---|---|---|
unique | attribute | boolean | Defines whether a uniqueness constraint is set on the index. |
type | attribute | IndexType (enum) | Defines the index type. e.g. HASH , GIN , FULLTEXT . |
columns | attribute | reference (list) | The columns that comprise the index. |
on | resource | schema.IndexPart (list) | The index parts that comprise the index. |
options | attribute | schema.Attr | Additional driver specific attributes. e.g. page_per_range |
Index Expressions​
Index expressions allow setting indexes over functions or computed expressions. Supported by PostgreSQL, SQLite and MySQL8.
table "t" {
schema = schema.test
column "c1" {
type = int
}
column "c2" {
type = int
}
index "i" {
on {
expr = "c1 - c2"
}
on {
expr = "c2 - c1"
}
}
}
Note, it is recommended to use the --dev-url
option when index expressions are used.
Partial Indexes​
Partial indexes allow setting indexes over subset of the table. Supported by PostgreSQL and SQLite.
table "t" {
schema = schema.public
column "b" {
type = bool
}
column "c" {
type = int
}
index "i" {
columns = [column.c]
where = "b AND c > 0"
}
}
Note, it is recommended to use the --dev-url
option when partial indexes are used.
Index Prefixes​
Index prefixes allow setting an index
on the first N
characters of string columns. Supported by MySQL and MariaDB.
table "users" {
schema = schema.test
column "name" {
type = varchar(255)
}
index "user_name" {
on {
column = column.name
prefix = 128
}
}
}
Comment​
The comment
attribute is an attribute of schema
, table
, column
, and index
.
schema "public" {
comment = "A schema comment"
}
table "users" {
schema = schema.public
column "name" {
type = text
comment = "A column comment"
}
index "name_idx" {
columns = [column.name]
}
comment = "A table comment"
}
Charset and Collation​
The charset
and collate
are attributes of schema
, table
and column
and supported by MySQL, MariaDB and PostgreSQL.
Read more about them in MySQL,
MariaDB and
PostgreSQL websites.
- MySQL
- PostgreSQL
schema "public" {
charset = "utf8mb4"
collate = "utf8mb4_0900_ai_ci"
}
table "products" {
column "name" {
type = text
collate = "binary"
}
collate = "utf8_general_ci"
}
schema "public" {}
table "products" {
column "name" {
type = text
collate = "es_ES"
}
}
Auto Increment​
AUTO_INCREMENT
and IDENTITY
columns are attributes of the column
and table
resource, and can be used to
generate a unique identity for new rows.
- MySQL
- PostgreSQL
- SQLite
In MySQL/MariaDB the auto_increment
attribute can be set on columns and tables.
table "users" {
schema = schema.public
column "id" {
null = false
type = bigint
auto_increment = true
}
primary_key {
columns = [column.id]
}
}
The auto_increment
column can be set on the table to configure a start value other than 1.
table "users" {
schema = schema.public
column "id" {
null = false
type = bigint
auto_increment = true
}
primary_key {
columns = [column.id]
}
auto_increment = 100
}
PostgreSQL supports serial
columns and the generated as identity
syntax for versions >= 10.
table "users" {
schema = schema.public
column "id" {
null = false
type = int
identity {
generated = ALWAYS
start = 10
increment = 10
}
}
primary_key {
columns = [column.id]
}
}
SQLite allows configuring AUTOINCREMENT
columns using the auto_increment
attribute.
table "users" {
schema = schema.main
column "id" {
null = false
type = integer
auto_increment = true
}
primary_key {
columns = [column.id]
}
}