11 posts tagged with "migration"

Introduction​

Hibernate-ORM is one of the most popular ORMs for Java, so much so that parts of it have evolved into the JPA standard and the Jakarta APIs.

Today, we are excited to announce that Atlas now supports loading and managing Hibernate schemas.

Atlas is a modern tool for managing your database schema. It allows you to inspect, plan, lint and apply schema changes to your database. It is designed to be used by developers, DBAs and DevOps engineers alike.

By using Atlas, Hibernate users can now enjoy these benefits:

• A declarative migration flow - Atlas can operate like a "Terraform for databases", where by running atlas schema apply the application schema is applied on a target database.
• Automatic schema migration planning - Alternatively, Atlas can operate using a more traditional versioned migration flow. However, contrary to most tools, Atlas will automatically analyze the diff between the migration directory and the current application data model, and will produce correct and safe SQL migration files.
• CI for schema changes - Atlas can be used during CI to make sure you never merge a pull request that will break your database schema.
• Modern CD integrations - Atlas integrates seamlessly with modern deployment tools such as Kubernetes, Terraform, Helm, Flux, and ArgoCD. This allows you to deploy changes to your database schema as part of your existing deployment pipelines.
• Visualization - Atlas users can create beautiful, shareable ERDs of their application data model with a single command.
• .. and much more (read more about Atlas features).

Integrating Atlas into your Hibernate project​

Hibernate ships with an automatic schema management tool called hbm2ddl. Similarly to Atlas, this tool can inspect a target database and automatically migrate the schema to the desired one. However, the Hibernate team has been advising for years not to use this tool in production:

Although the automatic schema generation is very useful for testing and prototyping purposes, in a production environment, it’s much more flexible to manage the schema using incremental migration scripts.

This is where Atlas comes in. Atlas can read Hibernate schema and plan database schema migrations.

How does it work?​

Atlas compares two database schema states and plans a migration to get from ones state to the other. The database schema can be read directly from Hibernate, a migration directory, a database connection, or another ORM.

To read the Hibernate schema, Atlas utilizes the concept of an external_schema datasource.

Demo Time​

For this demo, we are going to use Gradle, PostgreSQL and this example project.

Installation​

If you haven't already, install the latest version of Atlas:

curl -sSf https://atlasgo.sh | sh

brew install ariga/tap/atlas

docker pull arigaio/atlas
docker run --rm arigaio/atlas --help

docker run --rm --net=host \
  -v $(pwd)/migrations:/migrations \
  arigaio/atlas migrate apply
  --url "mysql://root:pass@:3306/test"

Add the hibernate-provider to your project via Gradle or Maven:

plugins {
    id("io.atlasgo.hibernate-provider-gradle-plugin") version "0.1"
}

plugins {
    id "io.atlasgo.hibernate-provider-gradle-plugin" version "0.1"
}

<build>
    <pluginManagement>
        <plugins>
            <plugin>
                <groupId>io.atlasgo</groupId>
                <artifactId>hibernate-provider-maven-plugin</artifactId>
                <version>0.1</version>
            </plugin>
        </plugins>
    </pluginManagement>
</build>

Configuration​

The plugin adds a configurable Gradle task (or a Maven goal) that prints the Hibernate schema without requiring a database connection. However, the task needs to be configured with the database dialect. We can do this by creating a schema-export.properties file in the resource directory. For example, for MySQL / PostgreSQL:

jakarta.persistence.database-product-name=MySQL
jakarta.persistence.database-major-version=8

jakarta.persistence.database-product-name=PostgreSQL
jakarta.persistence.database-major-version=15

Lastly, we need to configure Atlas to use this configuration by creating an atlas.hcl file and adding the definition of the Hibernate schema:

data "external_schema" "hibernate" {
  program = [
    "./gradlew",
    "-q",
    "schema",
    "--properties", "schema-export.properties"
  ]
}

data "external_schema" "hibernate" {
  program = [
    "./mvn",
    "-q",
    "hibernate-provider:schema",
    "-Dproperties", "schema-export.properties"
  ]
}

And the Atlas configuration:

env "hibernate" {
  src = data.external_schema.hibernate.url
  dev = "docker://mysql/8/dev"
  migration {
    dir = "file://migrations"
  }
  format {
    migrate {
      diff = "{{ sql . \"  \" }}"
    }
  }
}

env "hibernate" {
  src = data.external_schema.hibernate.url
  dev = "docker://postgres/15/dev?search_path=public"
  migration {
    dir = "file://migrations"
  }
  format {
    migrate {
      diff = "{{ sql . \"  \" }}"
    }
  }
}

Running Atlas​

We should now be able to view our schema using Atlas:

atlas schema inspect -w --env hibernate --url env://src

The -w flag allows us to inspect the schema in atlas cloud:

Atlas has many more features we can explore, let's create a migration directory from our schema:

atlas migrate diff --env hibernate

By running atlas migrate diff, Atlas compares the state of our Hibernate schema and the state of the schema in the migration directory. Atlas sees that the migration directory does not exist and initializes it with the current Hibernate schema. Observe the migration directory, it should contain similar files:

-- Create "movies" table
-- Create "movies" table
CREATE TABLE "movies" (
  "id" bigserial NOT NULL,
  "numberinseries" integer NULL,
  "title" character varying(255) NULL,
  PRIMARY KEY ("id")
);
-- Create "actors" table
CREATE TABLE "actors" (
  "name" character varying(255) NOT NULL,
  PRIMARY KEY ("name")
);
-- Create "movieparticipation" table
CREATE TABLE "movieparticipation" (
  "actorname" character varying(255) NOT NULL,
  "movieid" bigint NOT NULL,
  PRIMARY KEY ("actorname", "movieid"),
  CONSTRAINT "fkaq2kkwvh9870847sm35vtjtiy" FOREIGN KEY ("movieid") REFERENCES "movies" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "fktm8fbwa577lnbvwdjegwxvget" FOREIGN KEY ("actorname") REFERENCES "actors" ("name") ON UPDATE NO ACTION ON DELETE NO ACTION
);

h1:Ezh6r7A0pU4XS7PztE87h+aq5PoGGVTd6kaprtpXxas=
20231211114848.sql h1:9ECs2QsjHE8S4PxXxFcoPYaOVUWRjpxHuF8MGFRd3dE=

Atlas uses the atlas.sum file to protect against conflicting schema changes, you can read about it here.

diff --git a/migrations/20231210140844.sql b/examples/with_local_plugin_repository/migrations/20231210140844.sql
index ad80a64..5955834 100644
--- a/migrations/20231210140844.sql
+++ b/migrations/20231210140844.sql
@@ -4,3 +4,6 @@ CREATE TABLE `Event` (`id` bigint NOT NULL AUTO_INCREMENT, `title` varchar(255)
 -- Create "Event_SEQ" table
 CREATE TABLE `Event_SEQ` (`next_val` bigint NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
+ -- Initialize "Event_SEQ" table
+ insert into Event_SEQ values ( 1 );

Testing the migrations​

Now that our migration directory is ready, let's see how to apply it to a target database. Let's start a local PostgreSQL instance:

docker run -it --rm --name mypostgres -p 5432:5432 -e 'POSTGRES_PASSWORD=password' postgres

Next, let's apply our migrations to the database:

atlas migrate apply --env hibernate --url 'postgres://postgres:password@0.0.0.0:5432/?search_path=public&sslmode=disable'

Atlas provides details on the applied migrations:

Migrating to version 20231211121102 (1 migrations in total):

  -- migrating version 20231211121102
    -> CREATE TABLE "movies" (
         "id" bigserial NOT NULL,
         "numberinseries" integer NULL,
         "title" character varying(255) NULL,
         PRIMARY KEY ("id")
       );
    -> CREATE TABLE "actors" (
         "name" character varying(255) NOT NULL,
         PRIMARY KEY ("name")
       );
    -> CREATE TABLE "movieparticipation" (
         "actorname" character varying(255) NOT NULL,
         "movieid" bigint NOT NULL,
         PRIMARY KEY ("actorname", "movieid"),
         CONSTRAINT "fkaq2kkwvh9870847sm35vtjtiy" FOREIGN KEY ("movieid") REFERENCES "movies" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION,
         CONSTRAINT "fktm8fbwa577lnbvwdjegwxvget" FOREIGN KEY ("actorname") REFERENCES "actors" ("name") ON UPDATE NO ACTION ON DELETE NO ACTION
       );
  -- ok (9.282079ms)

  -------------------------
  -- 54.100203ms
  -- 1 migrations
  -- 3 sql statements

To confirm the migrations were applied, we can use Atlas to inspect the database. Run the following command:

atlas schema inspect -w --env hibernate --url 'postgres://postgres:password@0.0.0.0:5432/?search_path=public&sslmode=disable'

Making changes with confidence​

Atlas ships with a static code analysis engine that can detect risky schema changes during development or Continuous Integration. This functionality is exposed to users via the migrate lint command. Let's demonstrate this capability with an example.

Suppose we make the following change:

--- a/src/main/java/org/example/Movie.java
+++ b/src/main/java/org/example/Movie.java
@@ -10,13 +10,10 @@ public class Movie {

     Movie(String title, Integer numberInSeries) {
         this.title = title;
-        this.numberInSeries = numberInSeries;
     }
     @Id
     @GeneratedValue(strategy = GenerationType.IDENTITY)
     public Long id;

     public String title;
-
-    public Integer numberInSeries;
 }

By removing an attribute from the Movie model, we are removing a column from the database schema. Let's see how Atlas handles this change. Run atlas migrate diff --env hibernate, and observe the new file in the migration directory:

-- Modify "movies" table
ALTER TABLE "movies" DROP COLUMN "numberinseries";

While this change may be desired, it is an irreversible operation that should be done with caution. Atlas can help us avoid dangerous schema changes by linting the migration directory and not allowing such a change to get merged.

Running the following command, we can see that Atlas will warn us about a destructive change to the database:

atlas migrate lint --env hibernate --latest 1

20231211124321.sql: destructive changes detected:
        L2: Dropping non-virtual column "numberinseries"

Running migrate lint locally during development can be very useful, but linting becomes much more powerful when you integrate into your Continuous Integration pipeline. Atlas offers a set of Github Actions designed to make setting this up a breeze.

Conclusion​

In this post, we have presented how Hibernate projects can use Atlas to automatically plan, lint and apply schema migrations based only on their data model.

If you want to explore more configuration options or dive deeper into how this works, please take a look at this repository.

How can we make Atlas better?​

We would love to hear from you on our Discord server ❤️.

Introduction​

Sequelize is one of the most popular ORMs for Node.js. It supports a variety of databases, including MySQL, PostgreSQL, SQLite, and Microsoft SQL Server.

Atlas is a modern tool for managing your database schema. It allows you to inspect, plan, lint and execute schema changes to your database. It is designed to be used by developers, DBAs and DevOps engineers alike.

Atlas supports many ways to describe database schemas: Using Schema Loaders, plain SQL, a connection to another database or using Atlas HCL.

Today, I'm happy to announce that Atlas supports loading the desired schema from Sequelize projects. This means that Sequelize users can now use Atlas instead of the existing Sequelize CLI to manage their database schema.

By using Atlas, Sequelize users can now enjoy these benefits:

• A declarative migration flow - Atlas can operate like a "Terraform for databases", where by running atlas schema apply the application schema is applied on a target database.
• Automatic schema migration planning - Alternatively, Atlas can operate using a more traditional versioned migration flow. However, contrary to most tools, Atlas will automatically analyze the diff between the migration directory and the current application data model, and will produce correct and safe SQL migration files.
• CI for schema changes - Atlas can be used during CI to make sure you never merge a pull request that will break your database schema.
• Modern CD integrations - Atlas integrates seamlessly with modern deployment tools such as Kubernetes, Terraform, Helm, Flux, and ArgoCD. This allows you to deploy changes to your database schema as part of your existing deployment pipelines.
• Visualization - Atlas users can create beautiful, shareable ERDs of their application data model with a single command.
• .. and much more (read more about Atlas features)

Evolving beyond Sequelize's native migration support​

Sequelize allows users to manage their database schemas using its sync feature, which is usually sufficient during development and in many simple cases:

await sequelize.sync({ force: true });
console.log("All models were synchronized successfully.");

However, at some point, teams need more control and decide to employ the migrations methodology, which is a more robust way to manage your database schema. The problem with creating migrations in Sequelize is that they are usually written by hand in a very specific DSL, which is error-prone and time consuming:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Person', {
      name: Sequelize.DataTypes.STRING,
      isBetaMember: {
        type: Sequelize.DataTypes.BOOLEAN,
        defaultValue: false,
        allowNull: false
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Person');
  }
};

Atlas can automatically plan database schema migrations for developers using Sequelize by calculating the diff between the current state of the migration directory and its desired state defined by the Sequelize schema.

Demo time​

Let's demonstrate how to set up Atlas to manage your Sequelize schema.

Installation​

If you haven't already, install Atlas from macOS or Linux by running:

curl -sSf https://atlasgo.sh | sh

See the documentation for more installation options.

Install the Atlas Sequelize Provider by running:

npm install @ariga/atlas-provider-sequelize

npm install @ariga/ts-atlas-provider-sequelize

Make sure all your Node dependencies are installed by running:

npm install

Standalone vs Script mode​

The provider can be used in two modes:

• Standalone - If all of your Sequelize models exist in a single Node.js module, you can use the provider directly to load your Sequelize schema into Atlas.
• Script - In other cases, you can use the provider as an npm package to write a script that loads your Sequelize schema into Atlas.

Standalone mode​

In your project directory, create a new file named atlas.hcl with the following contents:

data "external_schema" "sequelize" {
    program = [
        "npx",
        "@ariga/atlas-provider-sequelize",
        "load",
        "--path", "./path/to/models",
        "--dialect", "mysql", // mariadb | postgres | sqlite | mssql
    ]
}

env "sequelize" {
    src = data.external_schema.sequelize.url
    dev = "docker://mysql/8/dev"
    migration {
        dir = "file://migrations"
    }
    format {
        migrate {
            diff = "{{ sql . \"  \" }}"
        }
    }
}

data "external_schema" "sequelize" {
    program = [
        "npx",
        "@ariga/ts-atlas-provider-sequelize",
        "load",
        "--path", "./path/to/models",
        "--dialect", "mysql", // mariadb | postgres | sqlite | mssql
    ]
}

env "sequelize" {
    src = data.external_schema.sequelize.url
    dev = "docker://mysql/8/dev"
    migration {
        dir = "file://migrations"
    }
    format {
        migrate {
            diff = "{{ sql . \"  \" }}"
        }
    }
}

For the sake of brevity, we will not review the Script mode in this post, but you can find more information about it in the Sequelize Guide.

Load Sequelize Schema in action​

Atlas supports a versioned migrations workflow, where each change to the database is versioned and recorded in a migration file. You can use the atlas migrate diff command to automatically generate a migration file that will migrate the database from its latest revision to the current Sequelize schema.

Suppose we have the following Sequelize models directory, with two models task and user:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Task = sequelize.define('Task', {
    complete: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    }
  });

  Task.associate = (models) => {
    Task.belongsTo(models.User, {
      foreignKey: {
        name: 'userID',
        allowNull: false
      },
      as: 'tasks'
    });
  };

  return Task;
};

'use strict';
module.exports = function(sequelize, DataTypes) {
  const User = sequelize.define('User', {
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        isEmail: true
      },
    }
  });

  User.associate = (models) => {
    User.hasMany(models.Task, {
      foreignKey: {
        name: 'userID',
        allowNull: false
      },
      as: 'tasks'
    });
  };

  return User;
};

import { Table, Column, Model, PrimaryKey, ForeignKey, BelongsTo, AutoIncrement, DataType, AllowNull, Default } from "sequelize-typescript";

import User from "./user";

@Table({ tableName: "Tasks" })
class Task extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.INTEGER)
  id!: number;

 @Default(false)
 @Column(DataType.BOOLEAN)
 complete!: boolean;

  @AllowNull(false)
  @ForeignKey(() => User)
  @Column(DataType.INTEGER)
  userID!: number;

  @BelongsTo(() => User)
  user!: User;
}

export default Task;

import { Table, Column, Model, PrimaryKey, AutoIncrement, DataType, AllowNull, HasMany} from "sequelize-typescript";
import Task from "./task";

@Table({ tableName: "Users" })
class User extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.INTEGER)
  id!: number;

  @AllowNull(false)
  @Column
  name!: string;

  @AllowNull(false)
  @Column
  email!: string;

  @HasMany(() => Task)
  task!: Task[];
}

export default User;

We can now generate a migration file by running this command:

atlas migrate diff --env sequelize

Running this command will generate files similar to this in the migrations directory:

migrations
|-- 20230918143104.sql
`-- atlas.sum

0 directories, 2 files

Examining the contents of 20230918143104.sql:

-- Create "Users" table
CREATE TABLE `Users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Create "Tasks" table
CREATE TABLE `Tasks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `complete` bool NULL DEFAULT 0,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `userID` int NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `userID` (`userID`),
  CONSTRAINT `Tasks_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `Users` (`id`) ON UPDATE CASCADE ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Amazing! Atlas automatically generated a migration file that will create the Users and Tasks tables in our database!

Next, alter the User model to add a new age field:

    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
+   age: {
+     type: DataTypes.INTEGER,
+     allowNull: false
+   },

    @AllowNull(false)
    @Column
    name!: string;

+   @AllowNull(false)
+   @Column
+   age!: number;

Re-run this command:

atlas migrate diff --env sequelize

Observe a new migration file is generated:

-- Modify "Users" table
ALTER TABLE `Users` ADD COLUMN `age` int NOT NULL;

Conclusion​

In this post, we have presented how Sequelize projects can use Atlas to automatically plan schema migrations based only on their data model.

How can we make Atlas better?​

We would love to hear from you on our Discord server ❤️.

Introduction​

Today, I'm happy to share with you one of the most exciting features we've added to Atlas since its inception: "External Schemas".

Atlas is a modern tool for managing your database schema. It allows you to inspect, plan, lint and execute schema changes to your database. It is designed to be used by developers, DBAs and DevOps engineers alike.

Schema-as-Code​

Atlas is built around the concept of database "Schema-as-Code", which means that you define the desired schema of your database in a declarative way, and Atlas takes care of planning and executing the necessary migrations to get your database to the desired state. The goal of this approach is to let organizations build a single source of truth for complex data topologies, and to make it easy to collaborate on schema changes.

Schema Loaders​

To achieve this goal, Atlas provides support for "Schema Loaders" which are different mechanisms for loading the desired state of your database schema into Atlas. Until today, Atlas supported a few ways to load your schema:

• Using Atlas DDL - an HCL based configuration language for defining database schemas.
• Using Plain SQL - a simple way to define your schema using plain SQL files (CREATE TABLE statements, etc.)
• From an existing database - Atlas can connect to your database and load the schema from it.
• The Ent ORM - Atlas can load the schema of your Ent project.

Today, we are adding support for "External Schemas", which means that you can now import the desired database schema from any ORM or other tool into Atlas, and use it to automatically plan migrations and execute them for you.

How do External Schemas work?​

External Schemas are implemented using a new type of Datasource called external_schema. The external_schema data source enables the import of an SQL schema from an external program into Atlas' desired state. With this data source, users have the flexibility to represent the desired state of the database schema in any language.

To use an external_schema, create a file named atlas.hcl with the following content:

data "external_schema" "example" {
  program = [
    "echo",
    "create table users (name text)",
  ]
}

env "local" {
  src = data.external_schema.example.url
  dev = "sqlite://file?mode=memory&_fk=1"
}

In this dummy example, we use the echo command to generate a simple SQL schema. In a real-world scenario, you would use a program that understands your ORM or tool of choice to generate the desired schema. Some ORMs support this out-of-the-box, such as Laravel's Eloquent's schema:dump command, while others require some simple integrations work to extract the schema from.

In the next section we will present the GORM Atlas Provider and how it can be used to seamlessly integrate a GORM based project with Atlas.

Demo Time​

GORM is a popular ORM widely used in the Go community. GORM allows users to manage their database schemas using its AutoMigrate feature, which is usually sufficient during development and in many simple cases.

However, at some point, teams need more control and decide to employ the versioned migrations methodology. Once this happens, the responsibility for planning migration scripts and making sure they are in line with what GORM expects at runtime is moved to developers.

Atlas can automatically plan database schema migrations for developers using GORM. Atlas plans migrations by calculating the diff between the current state of the database, and its desired state.

In the context of versioned migrations, the current state can be thought of as the database schema that would have been created by applying all previous migration scripts.

Installation​

If you haven't already, install Atlas from macOS or Linux by running:

curl -sSf https://atlasgo.sh | sh

See atlasgo.io for more installation options.

Install the provider by running:

go get -u ariga.io/atlas-provider-gorm

Standalone vs Go Program mode​

The Atlas GORM Provider can be used in two modes:

• Standalone - If all of your GORM models exist in a single package, and either embed gorm.Model or contain gorm struct tags, you can use the provider directly to load your GORM schema into Atlas.
• Go Program - If your GORM models are spread across multiple packages, or do not embed gorm.Model or contain gorm struct tags, you can use the provider as a library in your Go program to load your GORM schema into Atlas.

Standalone mode​

If all of your GORM models exist in a single package, and either embed gorm.Model or contain gorm struct tags, you can use the provider directly to load your GORM schema into Atlas.

In your project directory, create a new file named atlas.hcl with the following contents:

data "external_schema" "gorm" {
  program = [
    "go",
    "run",
    "-mod=mod",
    "ariga.io/atlas-provider-gorm",
    "load",
    "--path", "./path/to/models",
    "--dialect", "mysql", // | postgres | sqlite
  ]
}

env "gorm" {
  src = data.external_schema.gorm.url
  dev = "docker://mysql/8/dev"
  migration {
    dir = "file://migrations"
  }
  format {
    migrate {
      diff = "{{ sql . \"  \" }}"
    }
  }
}

In this example, we use the go run command to run the atlas-provider-gorm program and load the schema from the ./path/to/models directory. The atlas-provider-gorm program will scan the directory for GORM models and generate the desired schema for them. The --dialect flag is used to specify the database dialect that the schema should be generated for. The atlas-provider-gorm program supports the following dialects: mysql, postgres, and sqlite.

For the sake of brevity, we will not review the Go program mode in this post, but you can find more information about it in the GORM Guide.

External schemas in action​

Atlas supports a versioned migrations workflow, where each change to the database is versioned and recorded in a migration file. You can use the atlas migrate diff command to automatically generate a migration file that will migrate the database from its latest revision to the current GORM schema.

Suppose we have the following GORM models in our models package:

package models

import "gorm.io/gorm"

type User struct {  
    gorm.Model
    Name string
    Pets []Pet
}

type Pet struct {
    gorm.Model
    Name   string
    User   User
    UserID uint
}

We can now generate a migration file by running this command:

atlas migrate diff --env gorm 

Observe that files similar to this were created in the migrations directory:

migrations
|-- 20230627123246.sql
`-- atlas.sum

0 directories, 2 files

Examining the contents of 20230625161420.sql:

-- Create "users" table
CREATE TABLE `users` (
 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
 `created_at` datetime(3) NULL,
 `updated_at` datetime(3) NULL,
 `deleted_at` datetime(3) NULL,
 `name` longtext NULL,
 PRIMARY KEY (`id`),
 INDEX `idx_users_deleted_at` (`deleted_at`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Create "pets" table
CREATE TABLE `pets` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) NULL,
  `updated_at` datetime(3) NULL,
  `deleted_at` datetime(3) NULL,
  `name` longtext NULL,
  `user_id` bigint unsigned NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_users_pets` (`user_id`),
  INDEX `idx_pets_deleted_at` (`deleted_at`),
  CONSTRAINT `fk_users_pets` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Amazing! Atlas automatically generated a migration file that will create the pets and users tables in our database!

Next, alter the models.Pet struct to add a Nickname field:

type Pet struct {
    gorm.Model
    Name     string
+   Nickname string
    User     User
    UserID   uint
}

Re-run this command:

atlas migrate diff --env gorm 

Observe a new migration file is generated:

-- Modify "pets" table
ALTER TABLE `pets` ADD COLUMN `nickname` longtext NULL;

Conclusion​

In this post, we have presented External Schemas and how they can be used to automatically generate database schema directly from your ORM models. We have also demonstrated how to use the GORM Atlas Provider to automatically plan migrations for your GORM models.

We believe that this is a huge step forward in making Atlas more accessible to developers who are already using ORMs in their projects. We hope that you will find this feature useful and we look forward to hearing your feedback.

How can we make Atlas better?​

We would love to hear from you on our Discord server ❤️.

It has been two months since we announced the Community Preview Plan for Atlas Cloud, and today I am thrilled to announce the next batch of features that we are releasing to open-source and to Atlas Cloud:

In summary, version v0.12 includes a few major features that are explained in detail below:

1. We have added support for importing and running migration linting on GitHub PRs for external migration formats, such as Flyway and golang-migrate.
2. Atlas now supports reading your migration directory directly from your Atlas Cloud account. This eliminates the need for users to build their Docker images with the directory content and makes running schema migrations in production much easier.
3. By connecting Atlas CLI to Atlas Cloud, migration runs will be recorded in the cloud account, making it easier to monitor and troubleshoot executed migrations.
4. A new Slack integration is now available for Community Plan accounts. Organizations that connect their migration directories to the cloud can receive notifications to Slack channels when the schemas are updated or deployed, among other events.
5. A new look has been given to the CI report page. It will be enhanced with additional features in the next version.

Remote Directory State​

One of the most common complaints we received from our users is that setting up migration deployments for real-world environments is time-consuming. For each service, users are required to build a Docker image that includes the content of the migration directory, which ensures it is available when Atlas is executed. After this, the built image must be pushed to a registry, and finally, the deployment process needs to be configured to use this newly created image.

Not only does this process add complexity to the setup, but it is also repetitive for each migration directory and involves setting up a CI/CD pipeline for each service, adding another layer of complexity.

Atlas supports the concept of Data Sources, which enables users to retrieve information stored in an external service or database. In this release, we are introducing a new data source called remote_dir. This feature allows users to configure Atlas to read the content of the migration directory directly from their cloud account, thereby eliminating the need to build Docker images with the directory content.

Here is an example of how to configure the remote_dir data source:

variable "cloud_token" {
  type = string
}

atlas {
  cloud {
    token = var.cloud_token
  }
}

data "remote_dir" "migrations" {
  // The name of the migration directory in Atlas Cloud.
  // In this example, the directory is named "graph".
  name = "graph"
}

env {
  // Set environment name dynamically based on --env value.
  name = atlas.env
  migration {
    dir = data.remote_dir.migrations.url
  }
}

atlas migrate apply \
  --url "<DATABASE_URL>" \
  --config file://path/to/atlas.hcl \
  --env prod \
  --var cloud_token="<ATLAS_TOKEN>"

Visualizing Migration Runs​

Schema migrations are an integral part of application deployments, yet the setup might vary between different applications and teams. Some teams may prefer using init-containers, while others run migrations from CD pipeline. There are also those who opt for Helm upgrade hooks or use our Kubernetes operator. The differences also apply to databases. Some applications work with one database, while others manage multiple databases, often seen in multi-tenant applications.

However, across all these scenarios, there's a shared need for a single place to view and track the progress of executed schema migrations. This includes triggering alerts and providing the means to troubleshoot and manage recovery if problems arise.

Starting from version v0.12, if the cloud configuration was set with a valid token, Atlas will log migration runs in your cloud account. Here's a demonstration of how it looks in action:

We have several new features lined up for the Community Plan in the next release. If you're interested in them earlier, don't hesitate to reach out to me in our Discord community.

Slack Webhooks​

In this release, we're making our Slack Webhooks integration available to all users, promoting better team collaboration and providing instant alerts when issues occur. This new feature allows different groups within the organization, such as the data engineering team, to receive notifications when the schema changes. Or, ping the on-call when deployment fails.

If you're interested in enabling this feature for your project, please check out the documentation.

What's next?​

There's a lot more coming in the following months. Our next releases will be focused on making other database objects such as views, triggers, and policies accessible to all Atlas users. We'll also continue to make more features from our commercial product available to both open-source and community preview users.

As always, we value community feedback and strive to be responsive to it. Please feel free to reach out and share your feedback with us on our Discord if you think something is missing or could be improved. Cheers!

Introduction​

Today, we are excited to announce the release of Atlas v0.11.0, which introduces the Atlas Kubernetes Operator. This release is a major milestone in our mission to make Atlas the most robust and modern way to manage your database schemas. With the Atlas Kubernetes Operator, you can now manage your database schemas with Atlas from within your Kubernetes cluster.

In this release, we also introduce a new concept to Atlas - "Diff Policies" - which allow you to customize the way Atlas plans database migrations for you. This concept is directly related to the Kubernetes Operator, and we will explain how below.

What are Kubernetes Operators?​

Kubernetes has taken the cloud infrastructure world by storm mostly thanks to its declarative API. When working with Kubernetes, developers provide their cluster's desired configuration to the Kubernetes API, and Kubernetes is responsible for reconciling the actual state of the cluster with the desired state. This allows developers to focus on the desired state of their cluster, and let Kubernetes handle the complexities of how to get there.

This works out incredibly well for stateless components, such as containers, network configuration and access policies. The benefit of stateless components is that they can be replaced at any time, and Kubernetes can simply create a new instance of the component with the desired configuration. For stateful resources, such as databases, this is not the case. Throwing away a running database and creating a new one with the desired configuration is not an option.

For this reason, reconciling the desired state of a database with its actual state can be a complex task that requires a lot of domain knowledge. Kubernetes Operators were introduced to the Kubernetes ecosystem to help users manage complex stateful resources by codifying this type of domain knowledge into a Kubernetes controller.

What is the Atlas Kubernetes Operator?​

The Atlas Kubernetes Operator is a Kubernetes controller that uses Atlas to manage your database schema. The Atlas Kubernetes Operator allows you to define the desired schema and apply it to your database using the Kubernetes API.

Declarative schema migrations​

The Atlas Kubernetes Operator supports declarative migrations. In declarative migrations, the desired state of the database is defined by the user and the operator is responsible for reconciling the desired state with the actual state of the database (planning and executing CREATE, ALTER and DROP statements).

Diffing policies​

One of the common objections to applying declarative workflows to databases is that there are often multiple ways to achieve the same desired state. For example, if you are running a Postgres database, you may want to add an index to a table. Depending on your circumstances, you may want to add this index with or without the CONCURRENTLY option. When using a declarative workflow, you supply where you want to go, but not how to get there.

To address this concern, we have introduced the concept of "diff policies" to Atlas. Diff policies allow you to customize the way Atlas plans database schema changes for you. For example, you can define a diff policy that will always add the CONCURRENTLY option to CREATE INDEX statements. You can also define a diff policy that will skip certain kinds of changes (for example DROP COLUMN) altogether.

Diff policies can be defined in the atlas.hcl file you use to configure Atlas. For example:

env "local" {
  diff {
    // By default, indexes are not created or dropped concurrently.
    concurrent_index {
        create = true
        drop   = true
      }
  }
}

Diff policies are especially valuable when using the Atlas Kubernetes Operator, as they allow you to customize and constrain the way the operator manages your database to account for your specific needs. We will see an example of this below.

Demo time!​

Let's see the Atlas Kubernetes Operator in action. In this demo, we will use the Atlas Kubernetes Operator to manage a MySQL database running in a Kubernetes cluster.

The Atlas Kubernetes Operator is available as a Helm Chart. To install the chart with the release name atlas-operator:

helm install atlas-operator oci://ghcr.io/ariga/charts/atlas-operator

After installing the operator, follow these steps to get started:

1. Create a MySQL database and a secret with an Atlas URL to the database:

   kubectl apply -f https://raw.githubusercontent.com/ariga/atlas-operator/65dce84761354d1766041c7f286b35cc24ffdddb/config/integration/databases/mysql.yaml
   

   Result:

   deployment.apps/mysql created
   service/mysql created
   secret/mysql-credentials created
   

2. Create a file named schema.yaml containing an AtlasSchema resource to define the desired schema:

   apiVersion: db.atlasgo.io/v1alpha1
   kind: AtlasSchema
   metadata:
     name: atlasschema-mysql
   spec:
     urlFrom:
       secretKeyRef:
         key: url
         name: mysql-credentials
     schema:
       sql: |
         create table users (
           id int not null auto_increment,
           name varchar(255) not null,
           email varchar(255) unique not null,
           short_bio varchar(255) not null,
           primary key (id)
         );
   

3. Apply the schema:

   kubectl apply -f schema.yaml
   

   Result:

   atlasschema.db.atlasgo.io/atlasschema-mysql created
   

4. Check that our table was created:

   kubectl exec -it $(kubectl get pods -l app=mysql -o jsonpath='{.items[0].metadata.name}') -- mysql -uroot -ppass -e "describe myapp.users"
   

   Result:

   +-----------+--------------+------+-----+---------+----------------+
   | Field     | Type         | Null | Key | Default | Extra          |
   +-----------+--------------+------+-----+---------+----------------+
   | id        | int          | NO   | PRI | NULL    | auto_increment |
   | name      | varchar(255) | NO   |     | NULL    |                |
   | email     | varchar(255) | NO   | UNI | NULL    |                |
   | short_bio | varchar(255) | NO   |     | NULL    |                |
   +-----------+--------------+------+-----+---------+----------------+
   

   Hooray! We applied our desired schema to our target database.

Diff policies in action​

Now let's see how we can use diffing policies to customize the way the operator manages our database. In this example, we will demonstrate how we can prevent the operator from dropping columns in our database. Modify the schema.yaml file:

apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasSchema
metadata:
  name: atlasschema-mysql
spec:
  urlFrom:
    secretKeyRef:
      key: url
      name: mysql-credentials
+  policy:
+    diff:
+      skip:
+        drop_column: true
  schema:
    sql: |
      create table users (
        id int not null auto_increment,
        name varchar(255) not null,
        email varchar(255) unique not null,
-        short_bio varchar(255) not null,
        primary key (id)
      );

In the example above we added a policy section to our AtlasSchema resource. In this section, we defined a diff policy that will skip DROP COLUMN statements. In addition, we dropped the short_bio column from our schema. Let's apply the updated schema:

kubectl apply -f schema.yaml

Next, wait for the operator to reconcile the desired state with the actual state of the database:

kubectl wait --for=condition=Ready atlasschema/atlasschema-mysql

Finally, let's check that the short_bio column was not dropped. Run:

kubectl exec -it $(kubectl get pods -l app=mysql -o jsonpath='{.items[0].metadata.name}') -- mysql -uroot -ppass -e "describe myapp.users"

Result:

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| name      | varchar(255) | NO   |     | NULL    |                |
| email     | varchar(255) | NO   | UNI | NULL    |                |
| short_bio | varchar(255) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

As you can see, the short_bio column was not dropped. This is because we defined a diffing policy that skips DROP COLUMN statements.

Linting policies​

An alternative way to prevent the operator from dropping columns is to use a linting policy. Linting policies allow you to define rules that will be used to validate the changes to the schema before they are applied to the database. Let's see how we can define a policy that prevents the operator from applying destructive changes to the schema. Edit the schema.yaml file:

```diff
apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasSchema
metadata:
  name: atlasschema-mysql
spec:
  urlFrom:
    secretKeyRef:
      key: url
      name: mysql-credentials
   policy:
+     lint:
+       destructive:
+         error: true
-    diff:
-      skip:
-        drop_column: true
  schema:
    sql: |
      create table users (
        id int not null auto_increment,
        name varchar(255) not null,
        email varchar(255) unique not null,
        primary key (id)
      );

In the example above we replaced the diff policy with a lint policy. In this policy, we defined a destructive rule that will cause the operator to fail if it detects a destructive change to the schema. Notice that the short_bio is not present in the schema (we did this in our previous change).

Let's apply the updated schema:

kubectl apply -f schema.yaml

Next, let's wait for the operator to reconcile the desired state with the actual state of the database:

kubectl wait --for=condition=Ready atlasschema/atlasschema-mysql --timeout 10s

Notice that this time, the operator failed to reconcile the desired state with the actual state of the database:

error: timed out waiting for the condition on atlasschemas/atlasschema-mysql

Let's check the reason for this failure:

kubectl get atlasschema atlasschema-mysql -o jsonpath='{.status.conditions[?(@.type=="Ready")].message}'

Result:

destructive changes detected:
- Dropping non-virtual column "short_bio"

Hooray! We have successfully prevented the operator from applying destructive changes to our database.

Conclusion​

In this post, we have presented the Atlas Operator and demonstrated how you can use it to manage your database schema. We also covered diffing and linting policies and showed how you can use them to customize the way the operator manages your database.

How can we make Atlas better?​

We would love to hear from you on our Discord server ❤️.

Earlier today, we released v0.5.0 of the Atlas Terraform Provider. This release includes two changes that, in my opinion, are a great improvement to the experience of working with the Atlas Provider.

In this post, I will discuss these two changes and how they can help you to manage your database schemas with Terraform:

• Support for the docker:// driver for dev-databases.
• Support for defining the desired state of your database schema in plain SQL (and any other schema loader supported by Atlas).

Improving the dev-database experience with the docker:// driver​

Atlas relies on a connection to an empty database which it can use to perform various calculations and operations. This database is called the "dev-database", and it allows Atlas to do things like validate the correctness of user-provided code as well as normalize user-input to the way the database actually sees it.

In previous versions of the Atlas Provider, the dev-database needed to be provided by the user. This was a bit cumbersome, as the user needed to spin up a database (usually by running a local Docker container), and then provide the connection string to it in the dev_url field of the atlas_schema resource.

To improve this experience, we added support for the docker:// driver, which allows the user to only provide the database engine and version, and Atlas will spin up an ephemeral container with the correct database engine and version. In addition, starting this version, users may define the dev_url on the provider scope. For example:

provider "atlas" {
  dev_url = "docker://mysql/8/myapp"
}

Defining the desired state of the database schema in plain SQL​

In earlier versions of the Atlas Provider, the atlas_schema resource required the user to provide an Atlas HCL file which describes the desired state of the database schema. Many users found this syntax, which resembles Terraform's own, to be clean and concise. However, others disliked it and asked for a way to define the desired state in plain SQL.

To support this use-case, and many others, we have announced support for "schema loaders" - components that can be used to load the desired schema from many kinds of sources (such as plain SQL, an existing database, or the data-model of an ORM). To use this capability, users may use the atlas_schema data source, which accepts a url field that points to the desired schema. The scheme of this URL determines which schema loader will be used, for instance:

• file://schema.sql - loads the schema from a local SQL file.
• mysql://root:pass@localhost:3306/myapp - loads the schema from an existing MySQL database.
• ent://service/ent/schema - loads the schema from the schema of an Ent project.

Managing database schemas in plain SQL using Terraform​

In the following example, we will show how you can use Terraform and the Atlas provider to manage a MySQL database schema in plain SQL.

Let's start by creating a Terraform file named main.tf installing the Atlas Terraform provider:

terraform {
  required_providers {
    atlas = {
      source  = "ariga/atlas"
      version = "0.5.0"
    }
  }
}

In addition to installing the Atlas provider, we will also spin up a local MySQL database using Docker which will represent our target database that we will manage with Terraform. In a real-world scenario, you would probably use a managed database service such as AWS RDS or Google Cloud SQL, but for the purpose of brevity, a local database will suffice. Run:

docker run -d --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pass -e MYSQL_DATABASE=myapp mysql:8

Now that we have a database to manage, we can define the desired state of the database schema. Add a file named "schema.sql" with the following content:

create table users (
    id   int          not null auto_increment primary key,
    name varchar(255) not null
);

Next, we will define an atlas_schema data source that will load the schema from the schema.sql file:

data "atlas_schema" "sql" {
  src = "file://${path.module}/schema.sql"
}

Finally, we will define an atlas_schema resource that will manage the schema in the target database. In addition, we will configure the Atlas provider to use the docker:// driver to spin up a temporary database container:

provider "atlas" {
  dev_url = "docker://mysql/8/myapp"
}

resource "atlas_schema" "mysql" {
  url        = "mysql://root:pass@localhost:3306/myapp"
  hcl        = data.atlas_schema.sql.hcl
}

Now that we have defined our Terraform configuration, we can run terraform init to install the required providers:

terraform init

This should output something like:

Initializing provider plugins...
- Finding ariga/atlas versions matching "0.4.7"...
- Installing ariga/atlas v0.5.0...
- Installed ariga/atlas v0.5.0 (signed by a HashiCorp partner, key ID 45441FCEAAC3770C)

# ...

Terraform has been successfully initialized!

Finally, we can run terraform apply to create the database schema:

terraform apply

Terraform will print the following plan:

data.atlas_schema.sql: Reading...
data.atlas_schema.sql: Read complete after 4s [id=qnUvTyupgQzivof5LYWDOQ]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following
symbols:
  + create

Terraform will perform the following actions:

  # atlas_schema.myapp will be created
  + resource "atlas_schema" "myapp" {
      + hcl = <<-EOT
            table "hello" {
              schema = schema.myapp
              column "world" {
                null = true
                type = text
              }
              column "thoughts" {
                null = true
                type = varchar(100)
              }
            }
            schema "myapp" {
              charset = "utf8mb4"
              collate = "utf8mb4_0900_ai_ci"
            }
        EOT
      + id  = (known after apply)
      + url = (sensitive value)
    }

Plan: 1 to add, 0 to change, 0 to destroy.
╷
│ Warning: Atlas Plan
│ 
│   with atlas_schema.myapp,
│   on main.tf line 18, in resource "atlas_schema" "myapp":
│   18: resource "atlas_schema" "myapp" {
│ 
│ The following SQL statements will be executed:
│ 
│ 
│ CREATE TABLE `myapp`.`hello` (`world` text NULL, `thoughts` varchar(100) NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci
│ 
╵

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: 

Notice that the plan shows the SQL statements that will be executed to create the database schema as well as our loaded schema in its HCL representation - this was done by the schema loader that was used to load the schema from the schema.sql file.

If you are happy with the plan, type yes and press enter to apply the changes:

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

atlas_schema.myapp: Modifying... [id=mysql:///myapp]
atlas_schema.myapp: Modifications complete after 4s [id=mysql:///myapp]

Apply complete! Resources: 0 added, 1 changed, 0 destroyed.

Hooray! We have successfully created a database schema using Terraform and the Atlas provider.

Terraform's true power comes from its declarative nature - we feed it a desired state and it will make sure that the actual state matches the desired state. Atlas is a perfect match for this paradigm. Let's see what happens if we change the schema in the schema.sql file and run terraform apply again:

Update the contents of schema.sql to the following:

create table `groups` (
    id   int          not null auto_increment primary key,
    name varchar(255) not null
);

create table `users` (
    id       int          not null auto_increment primary key,
    name     varchar(255) not null,
    group_id int          not null,
    foreign key (group_id) references `groups` (id)
);

Re-apply the changes:

terraform apply

Observe that our plan includes the addition of the groups table as well as the foreign key constraint on the users table:

data.atlas_schema.sql: Reading...
data.atlas_schema.sql: Read complete after 4s [id=Qhci62i6CFYRQ2CmUOjMeA]
atlas_schema.myapp: Refreshing state... [id=mysql:///myapp]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following
symbols:
  ~ update in-place

Terraform will perform the following actions:

  # atlas_schema.myapp will be updated in-place
  ~ resource "atlas_schema" "myapp" {
      ~ hcl = <<-EOT
          + table "groups" {
          +   schema = schema.myapp
          +   column "id" {
          +     null           = false
          +     type           = int
          +     auto_increment = true
          +   }
          +   column "name" {
          +     null = false
          +     type = varchar(255)
          +   }
          +   primary_key {
          +     columns = [column.id]
          +   }
          + }
            table "users" {
              schema = schema.myapp
              column "id" {
                null           = false
                type           = int
                auto_increment = true
              }
              column "name" {
                null = false
                type = varchar(255)
              }
          +   column "group_id" {
          +     null = false
          +     type = int
          +   }
              primary_key {
                columns = [column.id]
              }
          +   foreign_key "users_ibfk_1" {
          +     columns     = [column.group_id]
          +     ref_columns = [table.groups.column.id]
          +     on_update   = NO_ACTION
          +     on_delete   = NO_ACTION
          +   }
          +   index "group_id" {
          +     columns = [column.group_id]
          +   }
            }
            schema "myapp" {
              charset = "utf8mb4"
              collate = "utf8mb4_0900_ai_ci"
            }
        EOT
        id  = "mysql:///myapp"
        # (1 unchanged attribute hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.
╷
│ Warning: Atlas Plan
│ 
│   with atlas_schema.myapp,
│   on main.tf line 18, in resource "atlas_schema" "myapp":
│   18: resource "atlas_schema" "myapp" {
│ 
│ The following SQL statements will be executed:
│ 
│ 
│ CREATE TABLE `myapp`.`groups` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`)) CHARSET
│ utf8mb4 COLLATE utf8mb4_0900_ai_ci
│ ALTER TABLE `myapp`.`users` ADD COLUMN `group_id` int NOT NULL, ADD INDEX `group_id` (`group_id`), ADD CONSTRAINT
│ `users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `myapp`.`groups` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
│ 
╵

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: 

After typing yes and pressing enter, Terraform will apply the changes, bringing the actual state of the database schema in line with the desired state:

atlas_schema.myapp: Modifying... [id=mysql:///myapp]
atlas_schema.myapp: Modifications complete after 4s [id=mysql:///myapp]

Apply complete! Resources: 0 added, 1 changed, 0 destroyed.

Conclusion​

In this tutorial, we have seen how to use Terraform to manage the schema of a MySQL database using the Atlas provider with plain SQL. Using this workflow, teams can bridge the gap between their database schema management flows and their Terraform workflows, allowing for simpler and more reliable software delivery.

How can we make Atlas better?​

We would love to hear from you on our Discord server ❤️.

Database schema migrations are an essential part of software development, allowing teams to evolve and refine their application's data model over time. However, with schema changes, it's not always smooth sailing, and migration failures can be disruptive and challenging to resolve.

As much as we'd like to believe that our schema migrations will be executed flawlessly, the reality is that things can and do go wrong. Whether it's due to human error, unforeseen complications, or technical constraints, migration failures can be a significant source of frustration for development teams. Anticipating and preparing for these issues is essential to minimize their impact on your project.

In this blog post, we'll explore the common causes of migration failures and demonstrate how Atlas can help you quickly recover from such failures and easily get back on track.

Atlas: Optimized for MTTR​

MTTR (mean-time-to-recovery) is a widely accepted metric for measuring the performance of teams delivering software. MTTR measures the mean time it takes to restore service when a production issue occurs. In the context of schema migrations, this would mean measuring how long it takes a team to detect, triage and resolve failures of schema migrations.

Contrary to existing tools, Atlas was designed with failure in mind and comes with some useful features to help your team get out of the mud if (and when) a schema migration fails. By utilizing these features, your team can greatly reduce MTTR for schema change related failures.

Why do migrations fail?​

Let's begin our discussion of troubleshooting schema migration failures by mentioning the common causes for migration failures.

1. Syntax errors - A surprisingly common cause for migration failures is syntax errors in the migration script: the migration tool tries to execute a statement and the database rejects it, causing the migration to fail. For example, adding an unnecessary comma at the end of a list:

   mysql> create table users (   id int,   name varchar(255), );
   
   ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
   

2. Schema dependent changes - Incorrect assumptions about the current state of the target database can lead to failed migrations when those assumptions are not met. For example, trying to create a table that was already created:

mysql> create table users (   id int,   name varchar(255) );
ERROR 1050 (42S01): Table 'users' already exists

3. Data-dependent changes - If migrations manipulate data or modify constraints, the operation may fail depending on existing data in the target database. For example, adding a NOT NULL constraint to a column may fail if that column contains null values:

   mysql> alter table users modify bio varchar(100) not null;
   ERROR 1138 (22004): Invalid use of NULL value
   

4. Lost connection - In some cases, and depending on the state of the target database and network connectivity, the client executing the migration commands against the database may lose the connection to the database, causing the migration to fail:

   mysql> create table t1 (c int);
   No connection. Trying to reconnect...
   ERROR 2003 (HY000): Can't connect to MySQL server on '0.0.0.0:3306' (61)
   ERROR:
   Can't connect to the server
   

Troubleshooting failures with Atlas​

In the next section, we review the capabilities that Atlas provides operators to troubleshoot and resolve migration failures:

• Status observability - how to understand the current state of the system after a failure.
• Statement level granularity - how to recover from partial migration failures.
• Declarative roll-forward - how to use Atlas to automatically create a recovery plan from a failure.

Status observability​

The first step to solving any failure is being able to triage the issue at hand. To assist operators in diagnosing the current status of a target database, Atlas provides the migrate status command which can be used to understand the current situation. For instance, suppose we tried to run the following migration which contains a drop table statement for a non-existing table:

create table users (
  id int,
  name varchar(255)
);

drop table non_existing;

The migration will fail with the following error:

Error 1051 (42S02): Unknown table 'test.non_existing'

In many cases, the migration will not be applied from our workstation, so we may not have access to the execution logs. To check the migration status, we can run the migrate status command:

atlas migrate status -u mysql://root:pass@/test

Atlas will print:

Migration Status: PENDING
  -- Current Version: 20230409114917 (1 statements applied)
  -- Next Version:    20230409114917 (1 statements left)
  -- Executed Files:  2 (last one partially)
  -- Pending Files:   1

Last migration attempt had errors:
  -- SQL:   drop table non_existing;
  -- ERROR: Error 1051 (42S02): Unknown table 'test.non_existing'

Observe that Atlas prints out some useful information:

• Migration Status: PENDING - There are pending migrations.
• -- Executed Files: 2 (last one partially) - the last file was partially applied.
• The last migration failed with an error: ERROR: Error 1051 (42S02): Unknown table 'test.non_existing'

Statement-level granularity​

As we saw in the example above, in cases where migrations partially fail (only some statements succeed) our database schema will be in a limbo state of sorts, it's neither in the previous nor the next version. To keep implementations simple, in the past many migration tools have opted to treat migration files as opaque blobs, meaning they cannot provide any assistance in cases of partial failures.

Atlas, on the other hand, parses the migration files prior to executing them and can therefore provide information about failures on the statement (rather than the file) level. This is great for observability, but it is even more meaningful when trying to resolve issues.

Consider a situation similar to the one we presented above, where a migration fails halfway because of a constraint violation:

CREATE TABLE biographies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

ALTER TABLE users modify bio varchar(100) not null;

In cases where the users.bio column already contains null values, this migration will partially fail:

  -- migrating version 20230409123337
    -> CREATE TABLE biographies (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      bio TEXT,
      FOREIGN KEY (user_id) REFERENCES users(id)
    );
    -> alter table users modify bio varchar(100) not null;

    Error: Error 1138 (22004): Invalid use of NULL value

This can be solved by backfilling the table with non-null values in the relevant column. To do this, we can update our migration script to contain this UPDATE statement:

CREATE TABLE biographies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

update users set bio='' where bio is null;

alter table users modify bio varchar(100) not null;

Here's the good part: because Atlas operates at the statement level and remembers that we've already successfully applied the first CREATE TABLE statement, it will resume from where it stopped. If we run:

atlas migrate apply -u mysql://root:pass@/test

Atlas runs to completion:

Migrating to version 20230409123337 from 20230409123337 (1 migrations in total):

  -- migrating version 20230409123337
    -> update users set bio='' where bio is null;
    -> alter table users modify bio varchar(100) not null;
  -- ok (48.440861ms)

  -------------------------
  -- 56.051791ms
  -- 1 migrations
  -- 2 sql statements

Declarative roll-forward​

One of the things people experienced with existing tools immediately notice when they start working with Atlas is the absence of down migrations. Many migration tools expect users to plan a down migration parallel to every migration, which contains the statements needed to roll back the schema changes for a version. In theory, this is done to allow users to seamlessly return to a previous version in case things go wrong with the new one.

Our decision to omit down migrations from Atlas and deserves its own lengthy discussion, but limited to the examples we just showed it is easy to demonstrate that attempting to execute down migrations in cases of partial failures may fail themselves, since they rely on the database being at the state where all statements executed successfully.

Instead of down migrations, Atlas provides an alternative strategy for reverting to a previous version. As you may know, one of Atlas's core features is its support for declarative migrations - the ability to automatically plan schema changes from the current state of a database to some desired state (similar to Terraform, but for databases).

Suppose we want to revert the database to a known version. This can happen in cases where the database was somehow manually modified in a way that's preventing us from making progress, or if we simply want to revert to a previous version. Using Atlas's schema apply, we can automatically plan this change:

atlas schema apply \
  --url "mysql://root:pass@localhost:3306/example" \
  --to "file://migrations?version=targetVersion" \
  --dev-url "docker://mysql/8/example" \
  --exclude "atlas_schema_revisions"

Atlas plans the change for us:

-- Planned Changes:
-- Drop "biographies" table
DROP TABLE `biographies`;
-- Modify "users" table
ALTER TABLE `users` MODIFY COLUMN `bio` varchar(100) NULL;
✔ Apply

Let's unpack this command:

• url - is the URL of the target database that we want to modify.
• to - describes the desired state, in this case the migration directory at file://migrations at version targetVersion - omitting this query parameter will set the desired state at the most recent revision.
• dev-url - Atlas requires a connection to an empty dev-database, which it uses to normalize the desired schema. Using the docker:// URL scheme tells Atlas to spin up and use a fresh Docker container for this purpose.
• exclude - tells Atlas to ignore atlas_schema_revision which is a metadata table maintained by Atlas and not described in the migration directory. Adding this argument prevents Atlas from accidentally producing a plan that drops this table.

Wrapping up​

This blog post discussed the common causes of database schema migration failures and demonstrated how Atlas is equipped to handle them. Atlas offers features such as status observability, statement-level granularity, and declarative roll-forward capabilities, which enable development teams to efficiently recover from migration failures, reduce MTTR, and minimize disruption to their services.

How can we make Atlas better?​

We would love to hear from you on our Discord server ❤️.

It's been only two weeks since the release of v0.10.0, but we are already back with more exciting features we want to share with you. Besides some more improvements and bug fixes, we added two new SQL analyzers to Atlas and the capability to have Atlas Cloud pick up a linting configuration file from your repository.

Concurrent Index Policy (Postgres)​

One of the Analyzers we added in this release is the Concurrent Index Policy Analyzer for Postgres. When creating or dropping indexes Postgres will lock the table against writes. Depending on the amount of data this lock might be in place longer than just a few moments, up to several hours. Therefore, Postgres provides the CONCURRENTLY option which will cause the index to be built without keeping a lock for the whole time it is built. While consuming more resources, this option oftentimes is preferred, and we are happy to present to you, that Atlas Linting engine is now capable of detecting statements that create or drop an index without using the CONCURRENTLY option.

Naming Conventions Policy​

Keeping consistency when naming database schema resources is a widely common practice. Atlas now has an analyzer that can detect names that don't comply with a given naming policy and will warn you in such cases. You can configure both a global or a resource specific policy. Read on to learn how to configure this analyzer or have a look at the documentation.

Cloud Linting Configuration​

In our last post, @a8m introduced the Community Preview for Atlas Cloud and how to connect a migration directory in your GitHub repository to Atlas Cloud with just a few clicks. As of then, the Atlas Cloud Linting reports that are added to your PR's used the default linting configuration. In this post, I will show you how to add configuration to the linting by making use of both the new analyzers I mentioned above.

Enable the Analyzers​

The Concurrent Index Analyzer will not report on creating or dropping indexes on tables that have been created in the same file. Therefore, lets ensure we have a table ready we can add an index to. Our first migration file can look something like this:

CREATE TABLE users
(
    id         serial PRIMARY KEY,
    email      VARCHAR(50) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL
);

To configure the Atlas Cloud linter to warn about creating or dropping indexes without the CONCURRENTLY option and ensure that all our schema resources are named with lowercase letters only, use the following configuration:

lint {
  concurrent_index {
    error = true # block PR on violations instead of warning
  }

  naming {
    error   = true
    match   = "^[a-z]+$"                  # regex to match lowercase letters
    message = "must be lowercase letters" # message to return if a violation is found
  }
}

See It In Action​

What is left to demonstrate is a migration file violating the above policies. Take the below example: the index name contains an underscore _, which is permitted by the naming analyzer and create the index non-concurrently.

CREATE INDEX email_idx ON users (email);

After adding the above atlas.hcl configuration file and the new migration, create a Pull Request on GitHub and observe Atlas Cloud doing its magic wizardry:

Wonderful! As you can see, Atlas Cloud found the two issues with this simple statement. Since the Concurrent Index Analyzer is configured to error on violations, merging the PR is blocked (if you have this policy set on GitHub).

In addition to the comment on the Pull Request, you can find more detailed reporting in the Checks tab or have a look at the file annotations Atlas adds to your changes:

What next?​

I sure hope the new analyzers will be useful to you. In the upcoming weeks, we will be rolling out several new major features that we have been working on lately, including schema drift detection, managed migration deployments, and much more. If any of these features sound interesting to you, please do not hesitate to contact us.

We would love to hear from you on our Discord server ❤️.

It's been two months since the release of v0.9.0, so we figured it's about time to release a new version and share with you what we've accomplished so far, as well as what's to come in the upcoming weeks. Besides the many improvements and bug fixes in v0.10.0, we added two major features to Atlas that I want to share with you: schema loaders and the Community Preview of Atlas Cloud.

Schema Loaders​

In our previous post, we discussed our motivation for developing an infrastructure to load desired states from external sources (not just SQL and HCL), and we highlighted the importance of schema loaders. Today, I'm happy to share that we've made significant progress on this front. We started by creating a schema loader for the Ent framework, and with the release of v0.10.0, Ent users can now use their ent/schema package as the desired state in all the different Atlas commands.

Using the new integration, users can compare an ent/schema package with any other state, apply it onto a database, generate migrations from it, and much more. Here are two examples:

atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "ent://path/to/schema" \
  --dev-url "sqlite://dev?mode=memory&_fk=1"

atlas schema diff \
  --from "file://migrations" \
  --to "ent://path/to/schema" \
  --dev-url "sqlite://dev?mode=memory&_fk=1"

I'm really eager to see this initiative come to fruition because it has proven to work well for the Ent community. We are now ready to expand support for additional frameworks and languages. In the upcoming weeks, you can expect to see additional integrations, such as GORM, Sequelize, and more. With these new superpowers, users will be able to manage all of their database schemas using a single tool - Atlas!

Atlas Cloud Community Preview​

We are also super thrilled to announce the Community Preview of Atlas Cloud! Atlas Cloud is a cloud-based service that provides teams with an end-to-end solution for managing database schema changes. As part of the Community Preview, we are offering a free "Community" plan for all users which you can use to manage up to 5 migration directories for your team or personal projects.

One important feature that was recently added to Atlas is the ability to connect remote migration directories stored in GitHub to Atlas Cloud. This new functionality empowers users to easily audit and view their migration history and get migration linting checks on their PRs, such as destructive or backwards incompatible changes detection.

Let's walk through a simple guide on how to set it up to a project with just a few clicks:

1. Login to atlasgo.cloud and create a new workspace (organization) for your projects:

2. Once created, go to /dirs/configure and connect your migration directory stored in GitHub to Atlas Cloud:

3. After connecting your directory, you'll see an extensive overview of your migration history and the schema it presents:

4. From this point on, every change made to the migration directory will be reflected in Atlas Cloud. But what about the changes themselves? Here's where the magic happens. Once a directory is connected, any pull request that modifies it will be automatically checked and reviewed by Atlas!

Let's create a sample migration change, open a pull request, and see it in action:

Wonderful! However, that's not all. There is another detailed and visualized report available in Atlas Cloud that has been specifically created for this CI run. Go to the migration directory page, click on the CI Runs button to check it out.

A big thanks to @giautm, @masseelch and @yonidavidson for building this feature for Atlas!

What next?​

Well, this is just the beginning of Atlas Cloud! In the upcoming weeks, we will be rolling out several new major features that we have been working on lately, including schema drift detection, managed migration deployments, and much more. If any of these features sound interesting to you, please do not hesitate to contact us.

We would love to hear from you on our Discord server ❤️.

For a long time, one of the most common feature requests we've been getting from our users is the ability to manage their desired "schema state" using SQL. This is understandable, using Atlas DDL (HCL) can feel unfamiliar to some users, especially those who have never worked with Terraform before. For this reason, we're excited to announce the release of Atlas v0.9.0, which now fully supports SQL.

Schema as Code (SaC)​

Atlas applies the common IaC concept of declarative resource management to database schemas. With Atlas, users do not need to plan schema changes themselves. Instead of figuring out the correct SQL statements to update their database schemas, users provide to Atlas the schema definition that describe their desired state and Atlas generates a migration plan to move from the current state to the desired state defined by the schema.

Starting from v0.9.0, users can use SQL schema files (or a directory) containing CREATE and ALTER statements to describe their desired state. To demonstrate this, let's use this schema example with a single users table:

-- create table "users
CREATE TABLE users(
    id int NOT NULL,
    name varchar(100) NULL,
    PRIMARY KEY(id)
);

Given this schema file, Atlas offers two workflows to update databases:

• Declarative: Similar to Terraform, Atlas compares the current state of the database schema with the desired state defined by the SQL schema, and generates a migration plan to reach that state.
• Versioned: Atlas compares the current state defined by the migrations directory to the desired state defined by the SQL schema, and writes a new migration script to the directory to update the database schema to the desired state.

In this blog post, we'll focus on explaining how SQL schemas can be used with the declarative workflow. For the sake of simplicity, let's assume we have an empty database that we want to apply the schema above to:

atlas schema apply \
  --url "mysql://root:pass@localhost:3306/example" \
  --to "file://schema.sql" \
  --dev-url "docker://mysql/8/example"

atlas schema apply \
  --url "maria://root:pass@:3306/example" \
  --to "file://schema.sql" \
  --dev-url "docker://maria/latest/example"

atlas schema apply \
  --url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/15"

atlas schema apply \
  --url "sqlite://file.db" \
  --to "file://schema.sql" \
  --dev-url "sqlite://file?mode=memory"

Running the command above with the --auto-approve flag will apply the following changes:

-- Planned Changes:
-- Create "users" table
CREATE TABLE `users` (`id` int NOT NULL, `name` varchar NULL, PRIMARY KEY (`id`));

Hooray! We have successfully created the users table defined in our schema file. Let's inspect our database and ensure its schema was actually updated by the command above:

atlas schema inspect \
  --url "mysql://root:pass@localhost:3306/example" \
  --format "{{ sql . }}"

atlas schema inspect \
  --url "maria://root:pass@:3306/example" \
  --format "{{ sql . }}"

atlas schema inspect \
  --url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
  --format "{{ sql . }}"

atlas schema inspect \
  --url "sqlite://file.db" \
  --format "{{ sql . }}"

Excellent! As you can see, our database schema has been updated:

-- create "users" table
CREATE TABLE `users` (`id` int NOT NULL, `name` varchar NULL, PRIMARY KEY (`id`));