Skip to main content

Dev Database

Introduction​

Some commands require a URL pointing to a "Dev Database", a temporary, isolated database instance that Atlas uses as a sandbox to simulate the real environment. Parsing schemas and migrations is not enough: every expression, constraint, default, function call, and DDL/DML statement must be semantically valid and accepted by a real database of the same type and version. That's what the dev-database is for: validating your code against your real environment (e.g., Postgres 18 with PostGIS and pgvector).

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

The docker driver also supports the postgis and pgvector images in its URL format:

--dev-url "docker://postgis/latest/dev"
--dev-url "docker://pgvector/pg17/dev"

To work with a custom Docker image, use one of the following formats:

# When working on a single database schema.
docker+postgres://org/image/dev?search_path=public
docker+postgres://ghcr.io/namespace/image:tag/dev?search_path=public
# For local/official images, leave host empty or use "_".
docker+postgres://_/local/dev?search_path=public
docker+postgres://_/official:latest/dev?search_path=public

# When working on multiple database schemas.
docker+postgres://org/image/dev
# Default database is "postgres".
docker+postgres://org/image:tag

Baseline Schema​

Atlas Pro Feature

The docker and the dev blocks are available for Atlas Pro users. To use these features, run:

atlas login

In some cases, you may need to configure a dev-database with a predefined schema state to ensure that any Atlas computation using the dev-database starts from this state. For example, this might be necessary if your schemas or migrations depend on objects not managed by you, such as PostgreSQL extensions or external functions used in your schema.

To configure a dev-database with a "baseline schema," you can use either the docker or dev blocks. Below are examples of both options.

Docker with baseline schema​

The docker block allows you to run an ephemeral Docker container with the relevant image and set up a baseline schema state to apply after the container is created.

docker "postgres" "dev" {
  image  = "postgres:15"
  schema = "public"
  baseline = <<SQL
   CREATE SCHEMA "auth";
   CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA "auth";
   CREATE TABLE "auth"."users" ("id" uuid NOT NULL DEFAULT auth.uuid_generate_v4(), PRIMARY KEY ("id"));
  SQL
}

env "local" {
  src = "file://schema.pg.hcl"
  dev = docker.postgres.dev.url
}

When managing multiple schemas or there is a need to generate migrations with table qualifiers, the schema argument should not be set:

docker "postgres" "dev" {
  image  = "postgres:15"
  // Use the file() function to read
  // the contents of the baseline script.
  baseline = file("baseline.sql")
}

Docker with build configurations​

When needed, the build block can be used to build the docker image with custom configurations:

docker "postgres" "dev" {
  image = "postgres:16-aws"
  // The built image will be tagged as "postgres:16-aws".
  build {
    context    = "path/to/context"
    dockerfile = "Dockerfile"
    target     = "build-target"
    args       = {
      key = "value"
    }
  }
}

MySQL, MariaDB, SQL Server, and ClickHouse users can use the docker "mysql" / docker "mariadb" / docker "sqlserver" / docker "clickhouse" to configure such block.

The code for the above examples is available in the ariga/atlas-examples repository, within the migrate-diff-hcl and schema-apply-sql directories.

Docker init-script​

The init attribute in the docker block specifies the initialization script for the container database created by Atlas. You can use this to configure global settings and variables before Atlas runs any commands on the database:

docker "mysql" "dev" {
  image = "mysql:8.4"
  init  = <<-SQL
    SET GLOBAL log_bin_trust_function_creators=true;
    SET GLOBAL restrict_fk_on_non_standard_key=false;
    SET GLOBAL sql_mode := REPLACE(@@sql_mode, 'NO_ZERO_DATE', '');
  SQL
}

env "dev" {
  src = "file://schema.my.hcl"
  dev = docker.mysql.dev.url
}

Dev-database with baseline schema​

Sometimes, you might want to use a running database as a dev-database, like AWS RDS or another managed service, but still need to set up a baseline state that your schema or migrations depend on. In such cases, you can use the dev block to configure Atlas to connect to an empty database provided by the url argument, apply the baseline schema state to it, and use it for further computations. On exit, Atlas restores the database to its original state.

dev "postgres" "rds" {
  url      = var.dev_url
  baseline = file("baseline.sql")
}

env "local" {
  src = "file://schema.pg.hcl"
  dev = dev.postgres.rds.url
}

Validation​

Atlas has parsers for every supported database, so syntax is covered. Correctness is a different question: only the real engine, with the right version and extensions, can compile every expression, resolve every reference, and accept the result. That's the dev-database's job.

Concretely, Atlas runs your schemas, migrations, and data blocks on a database that matches your production engine, version, and extensions (PostGIS, pgvector, etc.), catching bad expressions, logic errors, and failed migrations before they reach the target database. This is especially critical for engines without transactional DDL, like MySQL, Spanner, and Snowflake, where partial failures can't be rolled back.

A very simple example: an invalid CHECK constraint.

schema.hcl
table "t" {
  schema = schema.test
  column "c" {
    type = int
  }
  check "ck" {
    expr = "c <> d"
  }
}

After running schema apply, we get the following error because the CHECK constraint is invalid, as column d does not exist.

atlas schema apply \
  --url "mysql://root:pass@:3308/test" \
  --to "schema.hcl"
-- Planned Changes:
-- Modify "t" table
ALTER TABLE `test`.`t` ADD CONSTRAINT `ck` CHECK (c <> d), DROP COLUMN `c1`, ADD COLUMN `c` int NOT NULL
✔ Apply
Error: modify "t" table: Error 1054: Unknown column 'd' in 'check constraint ck expression'
exit status 1

Catching this needs the engine to compile the expression itself. With --dev-url, Atlas runs it on the dev-database first, so the error shows up at plan time instead of mid-apply:

atlas schema apply \
  --url "mysql://root:pass@:3308/test" \
  --to "schema.hcl" \
  --dev-url "docker://mysql/8/test"
Error: create "t" table: Error 3820: Check constraint 'ck' refers to non-existing column 'd'.
exit status 1

Diffing​

Atlas adopts the declarative approach for maintaining the schemas desired state, but provides two ways to manage and apply changes on the database: schema apply and migrate diff. In both commands, Atlas compares the "current", and the "desired" states and suggests a migration plan to migrate the "current" state to the "desired" state. For example, the "current" state can be an inspected database or a migration directory, and the "desired" state can be an inspected database, or an HCL file.

Schemas that are written in HCL files are defined in natural form by humans. However, databases store schemas in normal form (also known as canonical form). Therefore, when Atlas compares two different forms it may suggest incorrect or unnecessary schema changes, and using the --dev-url option can solve this (see the above section for more in-depth example).

Let's see it in action, by adding an index-expression to our schema.

schema.hcl
table "t" {
  schema = schema.test
  column "c" {
    type = varchar(32)
  }
  index "i" {
    on {
      expr = "upper(concat('c', c))"
    }
  }
}
atlas schema apply \
  --url "mysql://root:pass@:3308/test" \
  --to "schema.hcl"
-- Planned Changes:
-- Modify "t" table
ALTER TABLE `test`.`t` ADD INDEX `i` ((upper(concat('c', c))))
✔ Apply

We added a new index-expression to our schema, but using schema inspect will show our index in its normal form.

atlas schema inspect --url "mysql://root:pass@:3308/test"
table "t" {
  schema = schema.test
  column "c" {
    null = false
    type = varchar(32)
  }
  index "i" {
    on {
      expr = "upper(concat(_utf8mb4'c',`c`))"
    }
  }
}

Therefore, running schema apply again will suggest unnecessary schema changes.

atlas schema apply \
  --url "mysql://root:pass@:3308/test" \
  --to "schema.hcl"
-- Planned Changes:
-- Modify "t" table
ALTER TABLE `test`.`t` DROP INDEX `i`
-- Modify "t" table
ALTER TABLE `test`.`t` ADD INDEX `i` ((upper(concat('c', c))))
✔ Abort

Similarly to the previous example, we will use the --dev-url option to solve this.

atlas schema apply \
  --url "mysql://root:pass@:3308/test" \
  --to "schema.hcl" \
  --dev-url "docker://mysql/8/test"
Schema is synced, no changes to be made

Hooray! Our desired schema is synced and no changes have to be made.

info

Atlas cleans up after itself! You can use the same instance of a "Dev Database" for multiple environments, as long as they are not accessed concurrently.