Environment-Specific Schema Objects: Shared Base and Overlays

Dev, UAT, and production run almost the same schema in many projects. Most tables, views, and procedures are shared, but a few objects differ: a procedure in production calls a linked server that UAT does not have, dev keeps helper procedures that never reach production, or a procedure hardcodes an ID or a code that is different in each environment.

Keep the shared objects in one base, and put the objects that differ in a folder per environment. Atlas builds the desired schema of an environment from the base and the folder of the environment passed to --env, and each environment gets its own Schema Registry repository. When only hardcoded values differ, keep one definition and inject the values per environment instead, as shown in Per-Environment Values.

When every environment runs the same schema, you do not need this setup: use one Schema Registry repository and environment promotion. For rows that differ between environments in a versioned workflow, see Seed Data per Environment.

This guide uses the declarative workflow with SQL Server.

Atlas Pro Feature

SQL Server, the composite_schema data source, and the Schema Registry are available only to Atlas Pro users. To use them, run the following command, which starts a free trial:

atlas login

Project Layout

Put the shared tables, views, and procedures in schema/, and each environment's versions of the objects that differ in envs/<env>/:

.
├── atlas.hcl
├── schema/
│   ├── main.sql
│   └── tables/
│       └── orders.sql
├── envs/
│   ├── dev/
│   │   └── charge_order.sql
│   ├── uat/
│   │   └── charge_order.sql
│   └── prod/
│       └── charge_order.sql
└── tests/
    ├── dev/
    │   └── charge_order.test.hcl
    ├── uat/
    │   └── charge_order.test.hcl
    └── prod/
        └── charge_order.test.hcl

schema/main.sql imports the base files with atlas:import directives, such as -- atlas:import tables/orders.sql.

An object is defined either in the base or in the environment folders, never in both. An object that exists in only some environments goes only in their folders.

Keep test files out of envs/: a folder with both .sql and .hcl files fails with ambiguous schema: both SQL and HCL files found.

schema/tables/orders.sql
CREATE TABLE [orders] (
  [id] int NOT NULL,
  [gateway] nvarchar(10) NULL,
  CONSTRAINT [PK_orders] PRIMARY KEY ([id])
);

envs/prod/charge_order.sql
envs/uat/charge_order.sql

envs/prod/charge_order.sql
CREATE PROCEDURE [charge_order] (
  @order_id int
)
AS
BEGIN
  UPDATE [orders] SET [gateway] = 'live' WHERE [id] = @order_id;
END;

envs/uat/charge_order.sql
CREATE PROCEDURE [charge_order] (
  @order_id int
)
AS
BEGIN
  UPDATE [orders] SET [gateway] = 'sandbox' WHERE [id] = @order_id;
END;

Objects in an environment folder can reference objects in the base, such as the orders table above. The reverse does not work: the base loads first, so an object in the base cannot reference an object in envs/. Move such an object to the environment folders too.

Configure the Project

atlas.hcl
data "composite_schema" "app" {
  schema "dbo" {
    url = "file://schema/main.sql"
  }
  schema "dbo" {
    url = "file://envs/${atlas.env}"
  }
}

env {
  name = atlas.env
  url  = getenv("DATABASE_URL")
  dev  = "docker://sqlserver/2022-latest/dev?mode=schema"
  schema {
    src = data.composite_schema.app.url
    repo {
      name = "app-${atlas.env}"
    }
  }
}

The second schema "dbo" block extends the first with the objects of the environment passed to --env. The unlabeled env block serves every environment: --env uat reads envs/uat/ and pushes to the app-uat repository. DATABASE_URL must use mode=schema, the same scope as the dev database.

The composition fails before anything is planned when an object is defined in both the base and the environment folder, or when the environment has no folder. See Troubleshooting for the errors.

Each environment pushes to its own Schema Registry repository because its desired schema differs by design. A deployment to prod plans against the app-prod history only, so another environment's version of a procedure cannot reach it. When environments share the same schema, keep them in one repository instead, so they share one history for environment promotion.

Adopt Existing Environments

When each environment already has a database, build the project from them. In the commands below, PROD_URL, UAT_URL, and DEV_URL hold the URLs of the prod, uat, and dev databases, all with mode=schema. --dev-url points to a temporary SQL Server container, the dev database Atlas uses to compute the diff.

Export the base

Export one environment, for example prod, into schema/ as one file per object. The export writes main.sql with an atlas:import line per file:

atlas schema inspect -u "$PROD_URL" --format '{{ sql . | split | write "schema" }}'

Find the objects that differ

List the objects that differ between prod and each other environment:

atlas schema diff --from "$UAT_URL" --to "$PROD_URL" \
  --dev-url "docker://sqlserver/2022-latest/dev?mode=schema"
atlas schema diff --from "$DEV_URL" --to "$PROD_URL" \
  --dev-url "docker://sqlserver/2022-latest/dev?mode=schema"

Each statement in the output is an object that differs between the two environments. An ALTER is an object with a different definition, and a CREATE or DROP is an object that exists in only one of them. An empty diff prints Schemas are synced, no changes to be made.

Move them to the environment folders

Move each differing object's file from schema/ to envs/prod/, and delete its -- atlas:import line from schema/main.sql. A base file that imports a moved object depends on it, so move that file too.
Export each other environment into a temporary directory the same way, and copy its version of the same objects into envs/<env>/. An object that an environment does not have stays out of its folder.
Delete the -- atlas:import lines from every file in envs/. The base already loads the objects they point to, and a relative path such as ../tables/orders.sql does not resolve from envs/<env>/.

Confirm each environment matches its database

Add the atlas.hcl from Configure the Project. Then, point DATABASE_URL at each environment's database and confirm that the environment's files match it:

export DATABASE_URL="$DEV_URL"
atlas schema diff --env dev --from env://url --to env://schema.src

export DATABASE_URL="$UAT_URL"
atlas schema diff --env uat --from env://url --to env://schema.src

export DATABASE_URL="$PROD_URL"
atlas schema diff --env prod --from env://url --to env://schema.src

Every environment must print Schemas are synced, no changes to be made. A statement in the output is an object in the wrong folder, or one missing from envs/<env>/.

Validate Each Environment

Validate the desired schema of each environment on the dev database:

atlas schema validate --env dev
atlas schema validate --env uat
atlas schema validate --env prod

Each command exits without output when the environment's schema loads, and prints the error otherwise. Run all three after changing the base: a base change can break one environment's objects and not the others.

Test the objects that differ with a schema test per environment that asserts the environment's own values:

tests/prod/charge_order.test.hcl
test "schema" "charge_order" {
  exec {
    sql = "INSERT INTO [orders] ([id]) VALUES (1)"
  }
  exec {
    sql = "EXEC [charge_order] 1"
  }
  exec {
    sql    = "SELECT [gateway] FROM [orders] WHERE [id] = 1"
    output = "live"
  }
}

Run each environment's tests against its own schema:

atlas schema test --env dev tests/dev
atlas schema test --env uat tests/uat
atlas schema test --env prod tests/prod

When an environment builds another environment's version of the object, its test fails with output not equal and prints the expected and actual values.

A change to the base or to any environment folder is ready for review when atlas schema validate and the tests pass for every environment, not only for the one that changed.

Run CI/CD per Environment

Set up the pipeline from the declarative CI guide for your platform:

GitHub Actions

Declarative workflow with the Atlas GitHub Actions

GitLab CI

Declarative workflow on GitLab CI

Bitbucket Pipelines

Declarative workflow with the Atlas Bitbucket Pipes

CircleCI

Declarative workflow with the Atlas CircleCI Orb

Azure DevOps

Declarative workflow on Azure Pipelines, with GitHub or Azure Repos

Jenkins

Declarative workflow on Jenkins with the Atlas CLI

Run each Atlas step once per environment, and pass it the environment name: the env input of the Atlas action, pipe, orb, or task, or --env when the pipeline runs the Atlas CLI.

In pull requests, plan the change for dev, uat, and prod. Reviewers get one plan per environment and see exactly what each one will run.
On the default branch, approve the plan, push the schema, and apply it for each environment, in the order you deploy them.

Give each environment's DATABASE_URL only to the steps that deploy to it, and keep it in a scope that only the default branch reads.

Per-Environment Values

When the objects are the same in every environment and only values differ, keep one definition and inject the values with a template directory. Put the templated objects in their own folder, templates/, and compose it with the base:

templates/charge_order.sql
CREATE PROCEDURE [charge_order] (
  @order_id int
)
AS
BEGIN
  UPDATE [orders] SET [gateway] = '{{ .gateway }}' WHERE [id] = @order_id;
END;

atlas.hcl
locals {
  gateway = {
    dev  = "sandbox"
    uat  = "sandbox"
    prod = "live"
  }
}

data "template_dir" "procedures" {
  path = "templates"
  vars = {
    gateway = local.gateway[atlas.env]
  }
}

data "composite_schema" "app" {
  schema "dbo" {
    url = "file://schema/main.sql"
  }
  schema "dbo" {
    url = data.template_dir.procedures.url
  }
}

env {
  name = atlas.env
  url  = getenv("DATABASE_URL")
  dev  = "docker://sqlserver/2022-latest/dev?mode=schema"
  schema {
    src = data.composite_schema.app.url
    repo {
      name = "app-${atlas.env}"
    }
  }
}

The values of every environment sit in one map, next to each other in review. An environment missing from the map fails instead of rendering an empty value.

A template directory reads only the .sql files directly under its path, and does not follow atlas:import directives. Keep the templated files flat in templates/, and leave the rest of the schema in the base.

Troubleshooting

Error	Cause	Fix
`There is already an object named '<name>' in the database`	The object is defined in both the base and an environment folder, or it was left on the dev database	Run `grep -rn "<name>" schema envs`. Two definitions: keep one. One definition: point `dev` at an empty database
`stat envs/<env>: no such file or directory`	No folder for the environment passed to `--env`	Fix the `--env` value, or add the folder with that environment's objects
`ambiguous schema: both SQL and HCL files found`	A `.hcl` file, such as a test, sits in a schema folder	Move tests to `tests/<env>/`
`stat envs/tables/<file>.sql: no such file or directory`	A file moved into `envs/<env>/` still has its `-- atlas:import ../...` line	Delete the `-- atlas:import` lines from the files in `envs/`
`Invalid index; The given key does not identify an element in this collection value.`	The environment is missing from the `locals` map of template values	Add the environment's values to the map
`login is required to use the 'sqlserver' driver`	Atlas is not logged in	Run `atlas login`

Next Steps

Composite Schema

Compose multiple schema sources into one desired state

CI/CD for Declarative Migrations

Plan, review, and apply declarative changes from your CI platform

Seed Data per Environment

Shared and environment-specific rows in one migration directory per environment

Project Layout​

Configure the Project​

Adopt Existing Environments​

Export the base​

Find the objects that differ​

Move them to the environment folders​

Confirm each environment matches its database​

Validate Each Environment​

Run CI/CD per Environment​