HCL Schema

Atlas schemas can be defined in SQL, external ORMs and programs, or by using the Atlas HCL language. The HCL-based language allows developers to describe database schemas in a declarative manner, and it supports all SQL features supported by Atlas. The main advantages of using HCL are that it enables developers to manage their database schemas like regular code, facilitates sharing and reusing files between projects, allows variable injection, and provides the ability to attach annotations to objects, such as PII or sensitive data.

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.

note

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)"
    }
  }
}

Storage Engine

The engine attribute allows for overriding the default storage engine of the table. Supported by MySQL and MariaDB.

table "users" {
  schema = schema.public
  engine = MyISAM
}

table "posts" {
  schema = schema.public
  engine = InnoDB
}

table "orders" {
  schema = schema.public
  engine = "MyRocks"
}

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 qualifiers. 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
}

View

A view is a virtual table in the database, defined by a statement that queries rows from one or more existing tables or views.

view "clean_users" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "name" {
    type = text
  }
  as         = <<-SQL
  SELECT u.id, u.name
    FROM ${table.users.name} AS u
    JOIN ${view.active_users.name} AS au USING (id)
  SQL
  depends_on = [table.users, view.t1]
  comment    = "A view to active users without sensitive data"
}

view "comedies" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "name" {
    type = text
  }
  as           = "SELECT id, name FROM films WHERE kind = 'Comedy'"
  depends_on   = [table.films]
  check_option = CASCADED
}

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
    }
  }
}

info

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

schema.hcl
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>:

schema.hcl
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
  }
}

index "index_parser" {
  type    = FULLTEXT
  columns = [column.text]
  parser  = ngram
}

index "index_nulls_not_distinct" {
  unique         = true
  columns        = [column.text]
  nulls_distinct = false
}

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"
    }
  }
}

info

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"
  }
}

info

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]
  }
}

Schema​

Table​

Check​

Partitions​

Storage Engine​

Table Qualification​

View​

Column​

Properties​

Generated Columns​

Column Types​

Primary Key​

Example​

Properties​

Foreign Key​

Example​

Referencing Qualified Tables​

Properties​

Index​

Example​

Properties​

Index Expressions​

Partial Indexes​

Index Prefixes​

Comment​

Charset and Collation​

Auto Increment​

Schema

Table

Check

Partitions

Storage Engine

Table Qualification

View

Column

Properties

Generated Columns

Column Types

Primary Key

Example

Properties

Foreign Key

Example

Referencing Qualified Tables

Properties

Index

Example

Properties

Index Expressions

Partial Indexes

Index Prefixes

Comment

Charset and Collation

Auto Increment