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.

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