Atlas supports defining database schemas in various ways, from its ownHCL 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, orGo template-based directory. All of these methods are covered below.

Dev Database​

When working with SQL schemas, Atlas requires a URL to aDev Database, specified via the--dev-url flag (or thedev attribute inatlas.hcl). Typically, this is a temporary database running locally, usedto 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 Dockercontainer using the specialdocker driver, and clean it up at the end of the process. Here are afew 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"

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

# When working on multiple database schemas.
--dev-url"docker://maria/latest"

# When working on a single database schema, use the auth-created
# "public" schema as the search path.
--dev-url"docker://postgres/15/dev?search_path=public"

# When working on multiple database schemas.
--dev-url"docker://postgres/15/dev"

# Atlas supports working with an in-memory database in SQLite.
--dev-url"sqlite://dev?mode=memory"

# When working on a single database schema.
--dev-url"docker://sqlserver/2022-latest/dev?mode=schema"

# When working on multiple database schemas.
--dev-url"docker://sqlserver/2022-latest/dev?mode=database"

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

# When working on multiple database schemas.
--dev-url"docker://clickhouse/23.11"

# When working on a single database schema, use the auth-created
# "public" schema as the search path.
--dev-url"redshift://user:pass@redshift-cluster:5439/dev?search_path=public"

# When working on multiple database schemas.
--dev-url"redshift://user:pass@redshift-cluster:5439/dev"

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 todiff,plan, andapply changes onto thetarget database. It's important to note that Atlas loads the raw SQL schema by executing the statements defined in the filesone by one. As such, it is expected that files and statements are ordered according to their dependencies. For example,if aVIEW namedv1 depends onTABLE namedt1,v1 must be defined aftert1, either in the same file or in aseparate one.

Schema File​

An SQL schema defined in a single file is typically namedschema.sql and composed of multiple DDL statements separatedby a semicolon (;) or acustom delimiter, which can be validlyexecuted onto a database one after the other.

-- create "users" table
CREATETABLE`users`(
`id`intNOTNULL,
`name`varchar(100)NULL,
PRIMARYKEY(`id`)
);

-- create "blog_posts" table
CREATETABLE`blog_posts`(
`id`intNOTNULL,
`title`varchar(100)NULL,
`body`textNULL,
`author_id`intNULL,
PRIMARYKEY(`id`),
CONSTRAINT`author_fk`FOREIGNKEY(`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 alsobe 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 fileat 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

CREATEINDEX CONCURRENTLY name_idxON 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 bothmigration 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 toinclude 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,usingatlas:assert oneof indicates that at least one of the assertions must pass.
• atlas:import – 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 thestatement comment(s). For example:

-- atlas:nolint DS103 MY101
ALTERTABLE`t1`DROPCOLUMN`c1`,ADDCOLUMN`d1`varchar(255)NOTNULL;

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 checkfiles to specify that they cover a particular lint check or a set of checks that should not be reported as warnings or errors.

Importing System​

Atlas supports importing SQL files and directories into other SQL files using theatlas:import directive.Such directives must be placed at the top of the file and separated from the rest of the content by two blank lines (\n\n).If a file is imported more than once, Atlas will include it only the first time it appears. If an import cycle is detected,Atlas will raise an error.

This feature makes it easy to break large SQL schema definitions into smaller parts while keeping them correct andordered. Each schema chunk (file) explicitly declares the other schema resources it depends on to function properly.For example:

-- atlas:import groups.sql
-- atlas:import tenant/functions

CREATETABLE users(
    idserialPRIMARYKEY,
    tenant_idbigintNOTNULL,
    usernametextUNIQUENOTNULL,
    emailtextUNIQUENOTNULL,
    group_idintREFERENCES groups(id)
);
ALTERTABLE usersENABLEROWLEVEL SECURITY;

CREATE POLICY user_tenant_policyON users
FORSELECT
USING(tenant_id= current_tenant_id());

📺 For a step-by-step example walk-through, watch our 5-minute tutorial:Modular SQL Schema Definitions

Template Directory​

Atlas supports computing the desired schemas dynamically usingGo templates andinjected variables. To set it up for a project, create anatlas.hclconfig file, if you don't alreadyhave one. Then, declare a new data source of typetemplate_dir that can beused later as an Atlas schema.

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
}

{{-if eq.env"dev" }}
createtable dev2(ctext);
    {{ template"shared/users""dev2" }}
{{-else }}
createtable prod2(ctext);
    {{ template"shared/users""prod2" }}
{{-end }}