Skip to main content

Managing Seed & Lookup Data in MySQL

Lookup tables (also known as reference tables), configuration data, and seed data often drift when managed through ad-hoc INSERT scripts. Atlas lets you define the desired row data declaratively in HCL data blocks or SQL INSERT statements, and automatically generates the DML needed to synchronize any database. This is useful for managing lookup tables (such as country codes, currencies, and status types), configuration data (like application settings and feature flags), and seed data required for the application to function correctly.

Atlas supports three sync modes for data management:

  • INSERT: Only add missing rows, never modify or delete existing ones.
  • UPSERT: Add missing rows and update changed ones, never delete.
  • SYNC: Full synchronization. Add, update, and delete rows to match the schema exactly.

See the HCL data reference and SQL data reference for the full syntax.

Atlas Pro Feature

Data management is available only to Atlas Pro users. To use this feature, run:

atlas login

Prerequisites​

  1. Docker
  2. Atlas installed on your machine (installation guide)
  3. An Atlas Pro account (run atlas login to authenticate)

Project Setup​

Start by spinning up a local MySQL database using atlas tool docker:

export DATABASE_URL=$(atlas tool docker --url "docker://mysql/8/demo" --name my-db)
Cleaning up

When you're done, stop the container with atlas tool docker kill --name my-db.

Create an atlas.hcl project file with a data block to enable data management:

atlas.hcl
env "local" {
  url = getenv("DATABASE_URL")
  dev = "docker://mysql/8/dev"
  schema {
    src = "file://schema.my.hcl"
  }
  data {
    mode = INSERT
  }
}

The data.mode field controls how Atlas reconciles rows. We start with INSERT (the safest mode) and explore UPSERT and SYNC later.

For the full configuration reference, see data configuration.

Defining the Schema​

Define a countries table with a unique code column and four initial rows:

schema.my.hcl
schema "demo" {
}

table "countries" {
  schema = schema.demo
  column "id" {
    null = false
    type = int
  }
  column "code" {
    null = false
    type = varchar(2)
  }
  column "name" {
    null = false
    type = varchar(100)
  }
  primary_key {
    columns = [column.id]
  }
  index "countries_code_key" {
    columns = [column.code]
    unique  = true
  }
}

data {
  table = table.countries
  rows = [
    { id = 1, code = "US", name = "United States" },
    { id = 2, code = "IL", name = "Israel" },
    { id = 3, code = "DE", name = "Germany" },
    { id = 4, code = "VN", name = "Vietnam" },
  ]
}

Applying the Schema (INSERT Mode)​

Run atlas schema apply to create the table and seed it with data:

atlas schema apply --env local

Atlas produces a plan that creates the table and inserts all four rows:

Planning migration statements (2 in total):

  -- create "countries" table:
    -> CREATE TABLE `countries` (
         `id` int NOT NULL,
         `code` varchar(2) NOT NULL,
         `name` varchar(100) NOT NULL,
         PRIMARY KEY (`id`),
         UNIQUE INDEX `countries_code_key` (`code`)
       ) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
  -- seed "countries" table:
    -> INSERT INTO `countries` (`id`, `code`, `name`) VALUES
         (1, 'US', 'United States'),
         (2, 'IL', 'Israel'),
         (3, 'DE', 'Germany'),
         (4, 'VN', 'Vietnam');

After approving, run the command again to confirm idempotency:

atlas schema apply --env local
Schema is synced, no changes to be made

Adding New Rows​

Add France to the data block (or as another INSERT in SQL) and re-apply:

schema.my.hcl
data {
  table = table.countries
  rows = [
    // ...existing rows...
    { id = 5, code = "FR", name = "France" },
  ]
}
atlas schema apply --env local

Only the new row appears in the plan. Existing rows are untouched:

Planning migration statements (1 in total):

  -- insert into "countries" table:
    -> INSERT INTO `countries` (`id`, `code`, `name`) VALUES
         (5, 'FR', 'France');

In INSERT mode, Atlas never modifies or removes existing rows, making it the best choice when multiple sources (application code, other services) may add rows and you want to guarantee a baseline.

UPSERT Mode: Updating Existing Rows​

Switch to UPSERT mode when seed data evolves, but you never want to remove rows automatically:

atlas.hcl
env "local" {
  url = getenv("DATABASE_URL")
  dev = "docker://mysql/8/dev"
  schema {
    src = "file://schema.my.hcl"
  }
  data {
    mode = UPSERT
  }
}

Now, update a row (change Israel's name to "State of Israel") and add Japan:

schema.my.hcl
data {
  table = table.countries
  rows = [
    { id = 1, code = "US", name = "United States" },
    { id = 2, code = "IL", name = "State of Israel" },
    { id = 3, code = "DE", name = "Germany" },
    { id = 4, code = "VN", name = "Vietnam" },
    { id = 5, code = "FR", name = "France" },
    { id = 6, code = "JP", name = "Japan" },
  ]
}
atlas schema apply --env local

Atlas generates an UPDATE for the changed row and an INSERT for the new one:

Planning migration statements (2 in total):

  -- update "countries" table:
    -> UPDATE `countries` SET `name` = 'State of Israel' WHERE `id` = 2;
  -- insert into "countries" table:
    -> INSERT INTO `countries` (`id`, `code`, `name`) VALUES
         (6, 'JP', 'Japan');

No rows are deleted, even if a row exists in the database but not in the schema file.

SYNC Mode: Full Synchronization​

When the schema file is the single source of truth for the data, use SYNC mode. Atlas will add, update, and delete rows so the database matches exactly. This mode requires max_rows, a safety limit that prevents accidental mass deletes:

atlas.hcl
env "local" {
  url = getenv("DATABASE_URL")
  dev = "docker://mysql/8/dev"
  schema {
    src = "file://schema.my.hcl"
  }
  data {
    mode     = SYNC
    max_rows = 100
  }
}

Remove Germany from the data, and update Vietnam's name to "Socialist Republic of Vietnam":

schema.my.hcl
data {
  table = table.countries
  rows = [
    { id = 1, code = "US", name = "United States" },
    { id = 2, code = "IL", name = "State of Israel" },
    { id = 4, code = "VN", name = "Socialist Republic of Vietnam" },
    { id = 5, code = "FR", name = "France" },
    { id = 6, code = "JP", name = "Japan" },
  ]
}
atlas schema apply --env local

Atlas plans a DELETE for the removed row and an UPDATE for the changed name:

Planning migration statements (2 in total):

  -- delete from "countries" table:
    -> DELETE FROM `countries` WHERE `id` IN (3);
  -- update "countries" table:
    -> UPDATE `countries` SET `name` = 'Socialist Republic of Vietnam' WHERE `id` = 4;

If the table contains more rows than max_rows, Atlas refuses to proceed. This prevents accidentally wiping a table that has grown beyond what the schema file tracks.

Filtering Tables​

When your schema contains many tables but only some have seed data, use include and exclude to control which tables Atlas manages:

atlas.hcl
data {
  mode    = UPSERT
  include = ["countries", "currencies"]
  exclude = ["temp_*"]
}

Tables not matching include (or matching exclude) are ignored during data diffing. Both fields support glob patterns.

Next Steps​

Have questions? Feedback? Find our team on our Discord server or schedule a demo.