Skip to main content

SQL Schema

Atlas supports defining database schemas in various ways, from its own HCL language to external ORMs, programs, or the standard SQL. This guide focuses on defining schemas using SQL syntax, but also covers other methods. There are multiple ways to define schemas in SQL, such as using a single schema SQL file, a directory, or Go template-based directory. All of these methods are covered below.

Dev Database​

When working with SQL schemas, Atlas requires a URL to a Dev Database, specified via the --dev-url flag (or the dev attribute in atlas.hcl). Typically, this is a temporary database running locally, used to parse and validate the SQL definition. This requirement is necessary as Atlas cannot replicate every database type 'X' in every version 'Y'.

To simplify the process of creating temporary databases for one-time use, Atlas can spin up an ephemeral local Docker container using the special docker driver, and clean it up at the end of the process. Here are a few examples of how to use the docker driver:

# When working on a single database schema.
--dev-url "docker://mysql/8/schema"

# When working on multiple database schemas.
--dev-url "docker://mysql/8"

Schema Definition​

Once the dev-database is set, Atlas utilizes it to convert the provided raw SQL files and statements into the Atlas "schema graph", that then can be used by various layers of the engine to diff, plan, and apply changes onto the target database. It's important to note that Atlas loads the raw SQL schema by executing the statements defined in the files one by one. As such, it is expected that files and statements are ordered according to their dependencies. For example, if a VIEW named v1 depends on TABLE named t1, v1 must be defined after t1, either in the same file or in a separate one.

Ensuring the dev-database is clean

As mentioned above, Atlas uses the dev database to compute the desired state of the database schema. Therefore, before starting its work, Atlas ensures the dev database is clean and there are no leftovers from previous runs. Once done, Atlas cleans up after itself and the dev database is ready for future runs.

Schema File​

An SQL schema defined in a single file is typically named schema.sql and composed of multiple DDL statements separated by a semicolon (;) or a custom delimiter, which can be validly executed onto a database one after the other.

schema.sql
-- create "users" table
CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(100) NULL,
  PRIMARY KEY (`id`)
);

-- create "blog_posts" table
CREATE TABLE `blog_posts` (
  `id` int NOT NULL,
  `title` varchar(100) NULL,
  `body` text NULL,
  `author_id` int NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `author_fk` FOREIGN KEY (`author_id`) REFERENCES `example`.`users` (`id`)
);

In order to use an SQL schema file as an Atlas state, use the following format: file://path/to/schema.sql.

Schema Directory​

An SQL schema directory includes one or more schema files, ordered lexicographically according to their dependencies. For example, a table with foreign keys must be defined after the other tables it references, and a view should also be defined after the other tables and views it depends on.

In order to use a schema directory as an Atlas state, use the following format: file://path/to/dir.

atlas Directives​

SQL files in Atlas support directives, which are standard SQL comments that instruct Atlas on how to handle the SQL file at different stages of execution. For example, directives can control how the file is parsed when read by Atlas, disable linting warnings for specific statements or files, configure the transaction mode for a specific file, and more.

There are two types of directives:

File-Level Directives​

File-level directives appear at the top of the file and should not be associated with any specific statement. To separate file directives from the rest of the content, double new lines (\n\n) are used. For example:

-- atlas:txmode none

CREATE INDEX CONCURRENTLY name_idx ON users (name);

The following file-level directives are supported by Atlas:

  • atlas:txmode – can be set in migration files to control the transaction mode.
  • atlas:nolint – can be set in migration files to disable linting warnings for the entire file.
  • atlas:checkpoint – can be set in migration files to tag them as migration checkpoints.
  • atlas:delimiter – can be set in both migration and schema files to specify a custom delimiter for statements in the file.
  • atlas:txtar – can be set in migration files with pre-migration checks to include additional check files that should be executed before the migration.
  • atlas:assert – can be set in pre-migration check files to control assertion behavior. For example, using atlas:assert oneof indicates that at least one of the assertions must pass.
  • atlas:import (coming soon) – can be set in schema files to import other schema files or directories.

Statement-Level Directives​

Statement-level directives are attached to specific statements within the SQL file. They should be included in the statement comment(s). For example:

-- atlas:nolint DS103 MY101
ALTER TABLE `t1` DROP COLUMN `c1`, ADD COLUMN `d1` varchar(255) NOT NULL;

The following statement-level directives are supported by Atlas:

  • atlas:nolint – can be set in migration files to disable linting warnings for specific statements.
  • atlas:assert – can be set on assertion statements in pre-migration check files to specify that they cover a particular lint check or a set of checks that should not be reported as warnings or errors.

Template Directory​

Atlas supports computing the desired schemas dynamically using Go templates and injected variables. To set it up for a project, create an atlas.hcl config file, if you don't already have one. Then, declare a new data source of type template_dir that can be used later as an Atlas schema.

atlas.hcl
variable "path" {
  type        = string
  description = "A path to the template directory"
}

data "template_dir" "schema" {
  path = var.path
  vars = {
    key = "value"
    // Pass the --env value as a template variable.
    env  = atlas.env
  }
}

env "dev" {
  url = var.url
  src = data.template_dir.schema.url
}
example/schema.tmpl.sql
{{- if eq .env "dev" }}
    create table dev2 (c text);
    {{ template "shared/users" "dev2" }}
{{- else }}
    create table prod2 (c text);
    {{ template "shared/users" "prod2" }}
{{- end }}