On Aurora DSQL, Atlas now plans FOREIGN KEY, CHECK, and UNIQUE constraints on existing tables, identity columns, and columns added with a default, in the statement forms DSQL accepts. It also waits for each async job DSQL starts and fails the migration if the job fails.

Changes to Existing Tables

The highlighted lines add a column, a foreign key, and two constraints to tables that already exist:

schema.sql

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY (CACHE 65536),
  email text NOT NULL,
  created_at timestamptz DEFAULT now(),
  PRIMARY KEY (id)
);
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id),
  amount numeric NOT NULL
);
CREATE TABLE projects (
  id bigint PRIMARY KEY,
  tenant_id bigint NOT NULL,
  name text NOT NULL,
  CONSTRAINT projects_name_key UNIQUE (tenant_id, name),
  CONSTRAINT projects_name_check CHECK (name <> '')
);

Atlas plans each change in a form DSQL accepts:

CHECK and FOREIGN KEY constraints are added NOT VALID, then validated with ALTER TABLE ASYNC ... VALIDATE CONSTRAINT.
A UNIQUE constraint is attached from a unique index that Atlas builds first with CREATE UNIQUE INDEX ASYNC.
A column with a default is added with ADD COLUMN and SET DEFAULT in one statement, as DSQL rejects ADD COLUMN ... DEFAULT.

Planned changes

-- Modify "projects" table
ALTER TABLE "projects" ADD CONSTRAINT "projects_name_check" CHECK (name <> ''::text) NOT VALID;
-- Validate constraint "projects_name_check"
ALTER TABLE ASYNC "projects" VALIDATE CONSTRAINT "projects_name_check";
-- Create index "projects_name_key" to table: "projects"
CREATE UNIQUE INDEX ASYNC "projects_name_key" ON "projects" ("tenant_id", "name");
-- Modify "projects" table
ALTER TABLE "projects" ADD CONSTRAINT "projects_name_key" UNIQUE USING INDEX "projects_name_key";
-- Modify "users" table (existing rows are left NULL in "created_at")
ALTER TABLE "users" ADD COLUMN "created_at" timestamptz NULL, ALTER COLUMN "created_at" SET DEFAULT now();
-- Modify "orders" table
ALTER TABLE "orders" ADD CONSTRAINT "orders_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID;
-- Validate constraint "orders_user_id_fkey"
ALTER TABLE ASYNC "orders" VALIDATE CONSTRAINT "orders_user_id_fkey";

Identity Columns

DSQL requires an explicit CACHE on identity columns, so Atlas always writes it:

"id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY (CACHE 65536)

Identity columns must be bigint. The HCL identity block has no cache attribute, so an identity declared in HCL is created with CACHE 1.

Async Jobs

Atlas waits for each async job with sys.wait_for_job before it runs the next statement. A failed job stops the migration instead of passing as applied:

  -- create index "acct_email_key" to table: "acct"
    -> CREATE UNIQUE INDEX ASYNC "acct_email_key" ON "acct" ("email");
    background job cvlukubiofbsnb4tgnkauj635y did not succeed, see sys.jobs for its status and details

Notes

Existing rows stay NULL in a column added with a default, and only new rows get the default. Adding a NOT NULL or generated column to an existing table fails at planning.
A CHECK constraint added to an existing table must have a name, which the validation statement refers to.
A failed index build leaves an INVALID index, and a failed validation leaves a NOT VALID constraint. Rerunning the migration does not fix either: drop the index, or fix the rows and validate the constraint, before you retry.
Each index build takes about 15 to 20 seconds, even on an empty table, so migrations with many indexes take longer.

Getting Started

Aurora DSQL support is part of Atlas Pro. Run atlas login to get started.

See DSQL Limitations for every change DSQL supports with restrictions or Atlas rejects.

Aurora DSQL: Constraints, Identity Columns, and Async Jobs

Changes to Existing Tables

Identity Columns

Async Jobs

Notes

Getting Started