Skip to main content

SQL Server to PostgreSQL: The SqlPackage Workflow with Atlas

SqlPackage deploys a DACPAC: it compares the schema in the package to a database and runs the script that brings the database to it. It works only with the SQL Server family, such as SQL Server, Azure SQL Database, and Azure SQL Managed Instance.

Atlas works the same way for PostgreSQL. You keep the desired schema as code, and Atlas plans and applies the changes that bring each database to it. Atlas also manages SQL Server, MySQL, Oracle, Snowflake, and other databases, so one tool covers both engines while you move.

note

The SQL Server, Aurora DSQL, and Azure HorizonDB drivers, the databricks_lakebase_token data source, and PostgreSQL views, functions, procedures, and triggers, are Atlas Pro features. Run atlas login to start a free trial.

How SqlPackage Maps to Atlas​

Each SqlPackage action and publish property you rely on has a counterpart in Atlas:

SqlPackageAtlas
SQL projectSchema as code in SQL, HCL, C#, or other formats
/Action:Extractatlas schema inspect --export
/Action:Script, /Action:DeployReportatlas schema apply --dry-run, atlas schema plan, or versioned migrations
/Action:Publishatlas schema apply
BlockOnPossibleDataLossreview = ERROR, which asks for approval before a change that loses data
DropObjectsNotInSourceA diff policy skip block, which leaves drops out of the plan by object kind, such as drop_table = true
Pre- and post-deployment scriptsDeclarative data management, pre and post-deployment hooks, and Data Scripts
.dacpacThe schema or a migration directory, pushed to the Atlas Registry with atlas schema push or atlas migrate push

One default differs: SqlPackage keeps objects that are missing from the .dacpac, while Atlas plans drops for them unless the diff policy skips them.

Export the Schema as Code​

A SQL project keeps one file per object. Atlas exports a database the same way, either a single schema or the whole database with all its schemas. The URL decides which: DATABASE_URL and the dev database must have the same scope. The dev database also needs every extension the schema uses: for vector, use the pgvector image, as in docker://pgvector/pg18/dev.

DATABASE_URL names the schema with search_path=public, and so does the dev database. Configure an exporter, and point the env's desired state at the files it writes:

atlas.hcl
exporter "sql" "schema" {
  path     = "schema"
  split_by = object
}

env "postgres" {
  url = getenv("DATABASE_URL")
  dev = "docker://postgres/18/dev?search_path=public"
  schema {
    src = "file://schema/main.sql"
  }
  export {
    schema {
      inspect = exporter.sql.schema
    }
  }
  lint {
    review = ERROR
  }
}

Export the schema:

atlas schema inspect --env postgres --export

Each object gets its own file, in a directory for its type. A typical layout:

schema
├── tables
│   ├── profiles.sql
│   └── users.sql
├── views
│   └── active_users.sql
├── functions
│   └── user_age.sql
├── types
└── main.sql

Procedures, triggers, and the other object types get a directory of their own the same way. Each file imports the files it depends on with atlas:import directives, and main.sql imports all of them, so the env reads the whole schema from it. For a new database, write the schema in schema/main.sql yourself. See Export Database Schema to Code for the other exporter options.

Plan and Apply Changes​

Edit the files, then review the plan and apply it:

atlas schema apply --env postgres --dry-run
atlas schema apply --env postgres

--dry-run prints the planned statements without running them. With review = ERROR in the env's lint block, schema apply applies changes that pass linting, and asks for approval when the destructive analyzer reports an error.

Dev, QA, and production

In CI, the same desired state is applied to the database of each environment. To promote to production exactly the schema version that ran in QA, see Environment Promotion.

Manage Both Engines from One Project​

Add an exporter and an env for SQL Server next to the PostgreSQL ones, so both databases deploy through Atlas during the move:

atlas.hcl
exporter "sql" "sqlserver" {
  path     = "sqlserver"
  split_by = object
}

env "sqlserver" {
  url = getenv("SQLSERVER_URL")
  dev = "docker://sqlserver/2022-latest/dev?mode=schema"
  schema {
    src = "file://sqlserver/main.sql"
  }
  export {
    schema {
      inspect = exporter.sql.sqlserver
    }
  }
}

SQLSERVER_URL names one schema with mode=schema, the scope of its dev database. Run atlas schema inspect --env sqlserver --export to write the SQL Server schema to sqlserver/. See the SQL Server guide to get started.

Stored Procedures and Functions​

Atlas manages procedures, functions, and triggers on both engines, and it does not translate T-SQL to PL/pgSQL. Each engine keeps its own definitions: the sqlserver env reads sqlserver/, and the postgres env reads schema/.

Move one routine at a time, and check it before the next:

  1. Write its PostgreSQL version in a new file under schema/, and import it from schema/main.sql with -- atlas:import functions/tier_discount.sql. A coding agent can draft it from the T-SQL file:

    schema/functions/tier_discount.sql
    CREATE FUNCTION tier_discount(amount numeric, tier text) RETURNS numeric
    LANGUAGE sql IMMUTABLE
    AS $$
      SELECT round(amount * (1 - CASE tier WHEN 'pro' THEN 0.10
                                           WHEN 'enterprise' THEN 0.15
                                           ELSE 0 END), 2)
    $$;

  2. Run atlas schema validate --env postgres. It creates the schema on the dev database, so a routine that PostgreSQL rejects fails here. See schema health check.

  3. Check that the routine returns what the T-SQL one returns. Call the T-SQL routine on SQL Server with a few inputs, and turn its results into a schema test that calls the PostgreSQL routine with the same inputs:

    tests/tier_discount.test.hcl
    # The inputs, and what dbo.tier_discount returns for them on SQL Server.
    test "schema" "tier_discount" {
      for_each = [
        {amount: 100, tier: "free", want: 100},
        {amount: 100, tier: "pro", want: 90},
        {amount: 19.99, tier: "enterprise", want: 16.99},
      ]
      assert {
        sql = "SELECT tier_discount(${each.value.amount}, '${each.value.tier}') = ${each.value.want}"
      }
    }

    Run the tests with atlas schema test --env postgres tests/. Comparing with = in an assert ignores formatting differences between the engines, such as trailing zeros. For a procedure or a trigger, run the same statements on both engines, and assert on the rows they leave.

A coding agent can run this loop for every routine: convert, validate, and test, fix until the test passes, then move to the next. See Get Started with a Coding Agent.

Managed PostgreSQL​

Atlas connects to managed PostgreSQL services with a postgres:// URL, and to Azure HorizonDB with a horizondb:// URL. On Azure Database for PostgreSQL, Cloud SQL, Amazon RDS, and Aurora, a data source generates a short-lived token, and the URL uses it as the password. The HorizonDB, Neon, and Supabase examples read the password from an environment variable:

atlas.hcl
data "azure_db_token" "db" {}

env "azure" {
  url = urluserinfo(
    "postgres://myserver.postgres.database.azure.com:5432/postgres?sslmode=require",
    "app@contoso.onmicrosoft.com",
    data.azure_db_token.db,
  )
}

The azure_db_token data source requests a Microsoft Entra token for the user.

Except for Supabase, these URLs cover the whole database. To manage a single schema, add search_path=public, as in Export the Schema as Code. AlloyDB for PostgreSQL connects through the AlloyDB Auth Proxy, which accepts standard PostgreSQL connections. Databricks Lakebase and Aurora DSQL have their own sections: Databricks Lakebase and Aurora DSQL.

Databricks Lakebase​

Databricks Lakebase is managed PostgreSQL in a Databricks workspace. Atlas connects to a Lakebase endpoint with a postgres:// URL, and the databricks_lakebase_token data source generates a one-hour OAuth token for the password. Add a lakebase env next to the sqlserver env:

atlas.hcl
exporter "sql" "sqlserver" {
  path     = "sqlserver"
  split_by = object
}

exporter "sql" "schema" {
  path     = "schema"
  split_by = object
}

locals {
  host = "ep-example.database.us-west-2.cloud.databricks.com"
  user = "user@example.com"
}

data "databricks_lakebase_token" "db" {
  endpoint = "projects/my-app/branches/production/endpoints/primary"
}

env "sqlserver" {
  url = getenv("SQLSERVER_URL")
  dev = "docker://sqlserver/2022-latest/dev?mode=schema"
  schema {
    src = "file://sqlserver/main.sql"
  }
  export {
    schema {
      inspect = exporter.sql.sqlserver
    }
  }
}

env "lakebase" {
  url = urluserinfo(
    "postgres://${local.host}:5432/databricks_postgres?sslmode=require&search_path=public",
    local.user,
    data.databricks_lakebase_token.db,
  )
  dev = "docker://postgres/17/dev?search_path=public"
  // Functions that Lakebase manages in "public".
  exclude = [
    "grant_usage_on_new_schema[type=function]",
    "grant_all_on_new_sequences[type=function]",
    "grant_select_on_new_objects[type=function]",
  ]
  schema {
    src = "file://schema/main.sql"
  }
  export {
    schema {
      inspect = exporter.sql.schema
    }
  }
  lint {
    review = ERROR
  }
}
  • endpoint is the endpoint path, and local.host is the endpoint's host. Use the host without -pooler: the pooler rejects OAuth tokens. databricks postgres list-endpoints prints both (read more).
  • local.user is the Postgres role of the identity the token is issued to: the email of a Databricks user, or the application ID of a service principal. The data source signs in to Databricks with a CLI profile or service principal variables (read more).
  • databricks_postgres is the database that Lakebase creates on every branch.
  • Each env's dev database has the scope of its target: mode=schema for SQL Server, and search_path=public for Lakebase. The dev image runs the project's Postgres version.

Run atlas schema inspect --env lakebase --export to write the Lakebase schema to schema/, and atlas schema apply --env lakebase to deploy it. Atlas generates a new token each time it loads atlas.hcl, so each command starts with a token that has not expired.

Lakebase creates a __db_system schema, three functions in public, and three event triggers that call them, in every database. The exclude list keeps the functions out of the plan and the export: without it, schema apply plans to drop them and fails with 2BP01. For the full list, and the excludes for an env that covers more than one schema, see Objects Lakebase Manages.

Aurora DSQL​

Aurora DSQL is a serverless, distributed database that supports a subset of PostgreSQL 16. Atlas connects to it with a dsql:// URL, and plans each change in a form DSQL accepts. Part of a SQL Server schema has no DSQL equivalent, so check the schema against the table below before the first apply.

SQL Server Objects on DSQL​

SQL ServerAurora DSQL
Stored proceduresNot supported. Move the logic to the application.
TriggersNot supported. Move the logic to the application.
FunctionsOnly LANGUAGE sql functions, such as tier_discount in Stored Procedures and Functions. PL/pgSQL is not supported.
IDENTITY columns and sequencesbigint only, so change int identity columns to bigint. Atlas writes the CACHE option that DSQL requires.
Foreign keysSupported, with all referential actions. Rows that a cascade changes count toward the DSQL limit of 3,000 rows per transaction.
User-defined typesCREATE TYPE is not supported. Use the base type of each alias type.
Indexed viewsMaterialized views are not supported.
Partitioned tablesNot supported.
Case-insensitive collationsOnly the C collation: comparisons are case-sensitive, and ORDER BY sorts by byte order.
Descending index keysDSQL indexes take no sort order and no USING clause. A table has up to 24 indexes, each with up to 8 columns.
JSON in nvarchar columnsjson and jsonb columns are supported, but cannot be indexed.
SchemasUp to 10 per cluster.

For the full list, see DSQL Limitations.

Changes DSQL Cannot Apply​

DSQL cannot change a column or a primary key once the table exists. Atlas fails while planning, before any statement runs, when a change:

  • Adds a NOT NULL column or a generated column.
  • Changes the type, collation, or generation expression of a column.
  • Sets a column to NOT NULL.
  • Adds, drops, or changes a primary key.

Settle column types, nullability, computed columns, and primary keys in schema/ before the first schema apply. A column added later with a default gets it only in new rows, and existing rows stay NULL.

A DSQL transaction holds one DDL statement, and cannot mix DDL and DML. Atlas runs each statement in its own transaction, so a failed apply keeps the statements that ran before it, and the next atlas schema apply plans the rest. In versioned migrations, Atlas adds the -- atlas:txmode none directive to the files it generates. Atlas waits for each index build to finish, and a failed build leaves an INVALID index: drop it before you retry. DSQL has no advisory locks, so run one Atlas process against a cluster at a time.

Connect Atlas to DSQL​

Add a dsql env next to the sqlserver env. The aws_dsql_token data source generates the IAM auth token that the URL uses as the password:

atlas.hcl
locals {
  endpoint = "cluster-id.dsql.us-east-1.on.aws"
}

data "aws_dsql_token" "db" {
  endpoint = local.endpoint
  region   = "us-east-1"
  admin    = true
}

env "dsql" {
  url = "dsql://admin:${urlescape(data.aws_dsql_token.db)}@${local.endpoint}:5432/postgres?sslmode=require&search_path=public"
  dev = "dsql://admin:${urlescape(data.aws_dsql_token.db)}@${local.endpoint}:5432/postgres?sslmode=require&search_path=dev"
  schema {
    src = "file://schema/main.sql"
  }
  export {
    schema {
      inspect = exporter.sql.schema
    }
  }
  lint {
    review = ERROR
  }
}
  • Both URLs name a schema with search_path, so the dev database has the scope of the target. The dev database is an empty dev schema in the same cluster, and it counts toward the limit of 10 schemas.
  • A dev schema on DSQL rejects what DSQL rejects, so atlas schema validate --env dsql fails on a trigger, a procedure, or a PL/pgSQL function. docker://dsql/16/postgres?search_path=public plans with the DSQL rules, but it runs PostgreSQL and accepts them.
  • admin = true generates a token for the admin role, which needs the dsql:DbConnectAdmin permission. To connect as another role, see Use a custom database role.

Run atlas schema apply --env dsql to deploy schema/. See Automatic Aurora DSQL Schema Migrations for versioned migrations on DSQL.

Get Started with a Coding Agent​

With a coding agent, install the atlas-onboard skill and the database skills for both engines, atlas-mssql and atlas-postgres. One command installs them, with the other Atlas skills:

Install the skills from a terminal. It installs the Atlas plugin, with all its skills, for every project on this machine.

claude plugin marketplace add ariga/atlas
claude plugin install atlas@ariga

Then start the onboarding skill in Claude Code. See Claude Code with Atlas for the other ways to use the skills.

/atlas:atlas-onboard

atlas-onboard takes your repository from a first scan to production, one stage at a time, and the database skills hold the rules of each engine. On Databricks Lakebase, the atlas-lakebase skill adds the token setup and the objects Lakebase manages.

Next Steps​

Getting Started with PostgreSQL

Inspect, plan, and apply PostgreSQL schema changes with Atlas

Declarative Plans

Review schema changes in a pull request before they are applied

Atlas vs SSDT

A full comparison of Atlas and SQL Server Data Tools