7 posts tagged with "versioned migrations"

How to detect schema drift in CI and ensure the migration directory is up to date with the desired schema state - ORM, HCL, SQL, or any other schema definition?

With Atlas, we advocate for treating migration directories as deployment artifacts resulting from a structured build process. The preferred approach is to push migration directories to the Atlas Schema Registry. In addition to its role as a migration directory storage, the Schema Registry provides a tight integration with the Atlas CLI and the Atlas Cloud UI, allowing you to deploy migrations, visualize schemas over time, review deployment logs and errors, and more.

However, some users prefer to store their migration directories in S3, typically due to internal policies or requirements.

PostGIS is a spatial database extension for PostgreSQL. It adds support for geometric objects, enabling the processing of spatial data like points, lines, and polygons. After installing PostGIS, the GEOMETRY data type can be used to define tables that capture geo-spatial data, for example:

Developers who use Atlas to run migrations using a golang-migrate directory format, may run into an errors like:

-- migrating version 20250324061649.down.sql
-> DROP TABLE `users`;
   Error 1051 (42S02): Unknown table 'public.users'

Question​

Why does atlas migrate diff fail with the following error after running atlas migrate apply?

Error: sql/migrate: connected database is not clean: found table "atlas_schema_revisions" in schema <schema_name>. baseline version or allow-dirty is required

Question​

How to generate an SQL schema file from an existing migrations directory to quickly set up a database for integration tests?

When working with multiple branches and applying hotfixes directly to production, out-of-order migration conflicts may occur if migration files are created with timestamps that don't reflect the actual merge order.

The Problem​

Consider this scenario:

Initial state:

• Master branch:
  • 001_initial.sql
• Dev branch (PR pending):
  • 001_initial.sql
  • 002_add_posts.sql
  • 004_add_index.sql

After hotfix applied directly to production:

• Master branch:
  • 001_initial.sql
  • 003_hotfix_add_email.sql ← hotfix added
• Dev branch (unchanged):
  • 001_initial.sql
  • 002_add_posts.sql
  • 004_add_index.sql

After merging master into dev - the problem:

• 001_initial.sql - Applied to production
• 002_add_posts.sql - Dev-only, not applied to production
• 003_hotfix_add_email.sql - Applied to production
• 004_add_index.sql - Dev-only, not applied to production

This creates a non-linear migration history where migration files 002 and 004 were created before and after the hotfix timestamp but haven't been applied to production.