Declarative Data Management: Preserve Explicit IDs

Atlas can now emit the explicit values of auto-increment and identity primary-key columns in generated data INSERT statements, instead of omitting the column and letting the database assign new values. Opt in with preserve_ids = true on the data block in your atlas.hcl env.

By default, Atlas omits auto-increment and identity primary-key columns from generated data INSERT statements and lets the database assign new values. The new preserve_ids attribute on the data block emits those columns with their explicit values instead. It defaults to false and is additive: nothing changes until you opt in.

Opting In

Set preserve_ids = true on the data block. The switch is per env and applies to all managed tables, the same granularity as include, exclude, skip_diff, and max_rows:

env "prod" {
  data {
    mode         = INSERT
    include      = ["users"]
    preserve_ids = true
  }
}

What Atlas Emits

On MySQL, the AUTO_INCREMENT column is included in the INSERT:

INSERT INTO `users` (`id`, `name`) VALUES (1, 'a8m'), (5, 'nati');

On PostgreSQL, columns declared GENERATED BY DEFAULT AS IDENTITY use a plain INSERT. Columns declared GENERATED ALWAYS AS IDENTITY reject explicit values unless the statement carries the OVERRIDING SYSTEM VALUE clause, so Atlas adds it inside the single INSERT:

INSERT INTO "users" ("id", "name") OVERRIDING SYSTEM VALUE VALUES (1, 'a8m'), (5, 'nati');

Engine Support

• MySQL AUTO_INCREMENT and SQLite AUTOINCREMENT: the column is included in the INSERT.
• PostgreSQL: GENERATED BY DEFAULT AS IDENTITY uses a plain INSERT; GENERATED ALWAYS AS IDENTITY adds OVERRIDING SYSTEM VALUE. SERIAL columns are not identity columns; their values were already emitted regardless of preserve_ids.
• Oracle: GENERATED BY DEFAULT [ON NULL] AS IDENTITY columns are included (Oracle data inserts use the INSERT ALL ... SELECT * FROM DUAL form). GENERATED ALWAYS AS IDENTITY columns are still skipped: the engine cannot accept an explicit value.

preserve_ids takes effect anywhere data changes are planned: migrate diff, migrate checkpoint, and migrate down, as well as schema diff, schema apply, schema plan, and schema test. The data block is part of Atlas Pro; run atlas login to get started.