Automatic ER Diagrams and Docs for ClickHouse​

When working with a relational database like ClickHouse, understanding the database schema becomes essential for many functions in the organization. Who cares about the schema? Almost everyone who interacts with your data:

• Software engineers and architects use knowledge about the schema to make design decisions when building software.
• Data engineers need to have an accurate understanding of schemas to build correct and efficient data pipelines.
• Data analysts rely on familiarity with the schema to write accurate queries and derive meaningful insights.
• DevOps, SREs, and Production Engineers use schema information (especially recent changes to it) to triage database-related production issues.

Having clear, centralized documentation of your database's schema and its changes can be a valuable asset to foster efficient work and collaboration. Knowing this, many teams have developed some form of strategy to provide this kind of documentation:

• Diagramming tools. Teams use generic diagramming tools like Miro or Draw.io to maintain ER (Entity-Relation) Diagrams representing their database schema. While this is easy to set up, it requires manually updating the documents whenever something changes, often causing documents to go stale and become obsolete.
• Data modeling tools. Alternatively, teams use database modeling software like DataGrip or DBeaver. While these tools automatically inspect your database, understand its schema, and provide interactive diagrams, they have two main downsides: 1) Since they run locally, they require a direct connection and credentials introducing a potential security risk; 2) They do not enable any collaboration, discussion, or sharing of information.
• Enterprise Data Catalogs like Atlan or Alation, provide extensive schema documentation and monitoring; however, they can be quite pricey and difficult to set up.

Enter: Atlas Schema Monitoring​

Atlas offers an automated, secure, and cost-effective solution for monitoring and documenting your ClickHouse schema.

With Atlas, you can:

• Generate ER Diagrams: Visualize your database schema with up-to-date, easy-to-read diagrams.
• Create Searchable Code Docs: Enable your team to quickly find schema details and usage examples.
• Track Schema Changes: Keep a detailed changelog to understand what's changed and why.
• Receive Alerts: Get notified about unexpected or breaking changes to your schema.

All without granting everyone on your team direct access to your production database.

Getting Started​

Let's see how to set up Schema Monitoring for your ClickHouse database with Atlas. In this guide, we demonstrate how to run schema monitoring using a GitHub Action, but this can easily be achieved from other CI platforms (such as BitBucket or GitLab).

Prerequisites

1. A ClickHouse Cloud account with a running service.
2. An Atlas account (start your 30-day free trial here).
3. A GitHub repository with permissions to configure GitHub Actions workflows.

1. Create a bot token in Atlas Cloud​

Head over to your Atlas Cloud account and click on the top-level Monitoring navigation entry. Choose the GitHub Action card, and click on the Generate Token button. Copy the token.

Next, go to your GitHub repository and go to Settings -> Secrets and add a new secret called ATLAS_CLOUD_TOKEN with the value of the token you just copied.

2. Create a new GitHub Actions Workflow for schema monitoring​

Store your database URL as a repository secret named DB_URL with the value of your database url.

Next, save the workflow file below as .github/workflows/monitor-schema.yaml in your repository.

Replace the slug with the name you want to give to your database. The slug is used to uniquely identify the database in Atlas Cloud, even when the database URL changes.

name: Atlas Schema Monitoring
on:
 workflow_dispatch:
 schedule:
   - cron: '0 */4 * * *'  # every 4 hours
jobs:
 monitor:
   runs-on: ubuntu-latest
   steps:
     - uses: ariga/setup-atlas@v0
     - uses: ariga/atlas-action/monitor/schema@v1
       with:
         cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
         url: ${{ secrets.DB_URL }}
         slug: my-database

Then, commit and push the changes to your repository.

3. Run the GitHub Action​

Once committed, let's run the workflow:

1. Go to the Actions tab in your repository
2. Choose the Atlas Schema Monitoring workflow
3. Click on Run Workflow on the top right corner.

After the workflow finishes, you should see a link to Atlas Cloud where you can view the schema of your database:

4. View the schema in the Atlas UI​

Click on the link provided in the logs to view the schema in the Atlas UI.

Amazing! We have set up continuous schema monitoring for our ClickHouse database using Atlas and GitHub Actions. The GitHub Action will run every 4 hours, ensuring that the schema documentation is always up-to-date, you can adjust the schedule to fit your needs or run the workflow manually on-demand.

Wrapping Up​

We hope you find this new integration useful! As always, we would love to hear your feedback and suggestions on our Discord server.

Additional Reading​

• Learn about using Drift Detection in tandem with Schema Monitoring.
• Run Schema Monitoring from a long-running Agent
• Schema Monitoring Documentation

Python has been a top programming language for the past decade, known for its simplicity and rich ecosystem. Many companies use it to build web apps and server software, thanks to frameworks like Django and SQLAlchemy.

One of the most common (and often loathed) tasks when building backend applications is managing the database schema. As the app's data model evolves, developers need to modify the database schema to reflect those changes. This is where database schema migration tools come into play.

Why devs ❤️ Django Migrations and Alembic​

As far as migration tools go, SQLAlchemy and Django have both built out robust solutions for managing database schema through Alembic and Django Migrations, which stand out as some of the best in the field. They have both been around for a while, becoming popular due to their code-first approach:

1. First, developers define their database schema as code through Python classes, which are also used at runtime to interact with the database.
2. Next, the migration tool automatically generates the necessary SQL to apply those changes to the database.

For most projects, these tools work well and are a great fit, but there are some cases where you should consider looking at a specialized schema management tool. In this article, we'll explore some of the limitations of ORM-based migration tools and present Atlas, a database schema-as-code tool that integrates natively with both Django and SQLAlchemy.

The bias of ORM-based migrations​

ORMs are commonly distributed with schema management tools. Without a way to set up the database schema, the ORM cannot function, so each ORM must include something that provides a viable developer experience.

The main purpose of ORMs is to abstract the database layer and deliver a roughly uniform experience across different systems (e.g., PostgreSQL, MySQL, SQL Server, etc.). As an abstraction layer, they tend to concentrate on the shared database features (such as tables, indexes, and columns) rather than on more advanced, database-specific capabilities.

Being ORM maintainers ourselves (the team behind Atlas maintains Ent), we can attest that in our capacity as ORM authors, migrations are seen as a "necessary evil", something we have to ship, but really is just an annoying requirement. ORMs exist to bridge code and DB - they are a runtime effort, not a CI/CD or resource management concern.

As such, ORM migration tools tend to be basic, suitable just for the common cases of reading and writing data from tables. In projects that require a more involved schema management process, you might want to consider using a specialized tool like Atlas.

Advanced database features​

In many systems, the database is viewed simply as a persistence layer, but databases are capable of much more than just storing data. In recent years, there is a growing trend of utilizing databases for more than just CRUD operations. For example, you might want to use your database for:

• Stored Procedures, Functions, and Triggers: Logic can be encapsulated in stored procedures or triggers that automatically execute on certain events (e.g., inserts, updates, or deletes). This ensures consistent data validation, auditing, or transformation at the database level.
• Views and Materialized Views: Views are virtual tables generated from a SELECT query, while materialized views store the query results in a physical table that can be indexed. Materialized views can boost performance for frequently accessed or computationally expensive queries.
• Custom Data Types: Some databases (e.g., PostgreSQL) allow defining custom data types for domain-specific constraints or storing complex structures that exceed standard built-in types.
• Extensions: Many databases support extensions that add specialized capabilities. For example, PostGIS (an extension for PostgreSQL) provides advanced geospatial data handling and queries.
• Row-Level Security (RLS): RLS lets you define policies to control which rows a user can see or modify. This is particularly useful for multi-tenant systems or sensitive data scenarios where granular, row-level permissions are required.
• Sharding: Sharding distributes data across multiple database nodes (or clusters). This approach can enhance performance, fault tolerance, and scalability, especially in high-traffic, large-volume applications.
• Enumerated Types (ENUM): ENUM types allow you to define a constrained list of valid values for a column (e.g., "small", "medium", "large"). This can help enforce data consistency and prevent invalid entries.

Where Atlas comes in​

ORMs typically do not provide a way to manage these advanced database features.

Using Atlas, ORM users can keep using their favorite ORM (e.g SQLAlchemy) but also extend their data model with advanced database features. This is done by utilizing composite_schema, a feature that allows you to define your schema in multiple parts, each part using a different schema source. For example:

data "external_schema" "sqlalchemy" {
    program = [
        "atlas-provider-sqlalchemy",
        "--path", "./models",
        "--dialect", "postgresql"
    ]
}

data "composite_schema" "example" {
  // First, load the schema with the SQLAlchemy provider
  schema "public" {
    url = data.external_schema.sqlalchemy.url
  }
  // Next, load the additional schema objects from a SQL file
  schema "public" {
    url = "file://extra_resources.sql"
  }
}

env "local" {
  src = data.composite_schema.example.url
  // ... other configurations
}

In this example, we define a composite schema that combines the SQLAlchemy schema with additional schema objects loaded from a SQL file. This allows you to use the full power of your database while still benefiting from the convenience of ORMs.

Using composite schemas, we can use SQLAlchemy to define a base table and then use a SQL file to define a materialized view that aggregates data from it for faster querying. For instance, let's define a SQLAlchemy model for a user account:

class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    team_name: Mapped[Optional[str]] = mapped_column(String(30))
    points: Mapped[int] = mapped_column(Integer)

Then use plain SQL to define a materialized view that aggregates the total points per team:

CREATE MATERIALIZED VIEW team_points AS
SELECT team_name, SUM(points) AS total_points
FROM user_account
GROUP BY team_name;

Atlas will read both the SQLAlchemy model and the SQL file and generate any necessary SQL migration scripts to apply the changes to the database.

CI/CD Pipelines​

Although it happens more frequently than you might hope, database schema migrations should not be executed from a developer's workstation. Running migrations in such a way is error-prone and can lead to inconsistencies between your codebase and the database schema.

Instead, migrations should be applied as part of your CI/CD pipeline. This ensures that only code that was reviewed, approved and merged is deployed to production. Additionally, it reduces the need to grant developers direct access to the production database, which can be a security and compliance risk.

Django and SQLAlchemy are unopinionated about how you run migrations in your CI/CD pipeline. They provide the basic building blocks (e.g., manage.py migrate for Django) and leave it up to you to integrate them into your pipeline.

For simple use-cases, this is fine. But as your project grows, you might find yourself needing more control over the migration process. For example, you might want to:

1. Automate code review. Automatically verify that migrations are safe to apply before running them. Integrating automatic checks into your CI/CD pipeline can help catch issues early and prevent bad migrations from being applied.

2. Integrate with CD systems. As systems evolve, organizations often adapt more complex deployment strategies that require advanced tooling (e.g GitOps, Infrastucture as Code). Integrating migrations natively into these systems requires a substantial engineering effort (e.g, writing a Kubernetes Operator or Terraform provider).

3. Monitor schema drift. As much as we'd like to believe that production environments are air-tight, and never touched by human hands, the reality is that things happen. Monitoring schema drift can help you catch unexpected changes to your database schema and take corrective action before they cause issues.

Atlas ships with native integrations for popular CI/CD systems like GitHub Actions, GitLab CI, BitBucket Pipelines, Kubernetes, Terraform, and more. This allows you to easily integrate schema management into your existing CI/CD pipelines without having to write brittle custom scripts or plugins.

One migration tool to rule them all​

If your company's tech stack is uniform and everyone is using the same ORM and database system, you might not be worried about the need to standardize on a single migration tool, but as companies grow, the tech stack can become more diverse.

This is especially true when adopting a microservices architecture as different teams might be using different ORMs, languages or database systems. While this is great for flexibility, it can make it can make it very difficult for platform, security, and compliance functions to ensure that all teams are following the same best practices. This is where choosing a single migration tool can help.

Atlas is designed to be a universal schema management tool that can be used across different ORMs, languages, and database systems. It provides a consistent experience for managing database schema, regardless of the underlying technology stack.

By providing a plugin system that can provide bindings to different ORMs and database systems, Atlas can be to be the common denominator for schema management across your organization.

Conclusion​

Django Migrations and Alembic are great tools that have served Python developers well. They make schema changes simple and work seamlessly with their respective ORMs. But ORMs focus on abstracting databases, not managing them.

For teams that need more advanced database features, better CI/CD integration, or consistency across multiple stacks — a dedicated schema management tool like Atlas can help. It works alongside ORMs, letting developers define schema as code while keeping full control over the database.

If you're running into the limits of ORM-based migrations, give Atlas a try!

Hey everyone!

Welcome to the second Atlas release of 2025, v0.31! We're excited to share the latest updates and improvements with you. In this release you will find:

• Custom schema rules: You can now define custom rules for your database schema and have Atlas enforce them for you during CI.
• pgvector support: We've added support for managing schemas for projects that use the LLM-based pgvector extension.
• Drift detection: It is now simpler to set up drift detection checks to alert you when a target database isn't in the state it's supposed to be in.
• Multi-project ER Diagrams: you can now create composite ER diagrams that stitch schema objects from multiple Atlas projects.

Enforce custom schema rules​

atlas login

Atlas now supports the definition of custom schema rules that can be enforced during local development or CI/CD pipelines. This feature is particularly useful for teams that want to ensure that their database schema adheres to specific conventions or best practices, such as:

• "Require columns to be not null or have a default value"
• "Require foreign keys to reference primary keys of the target table and use ON DELETE CASCADE"
• "Require an index on columns used by foreign keys"
• ... and many more

The linted schema can be defined in any supported Atlas schema format, such as HCL, SQL, ORM, a URL to a database, or a composition of multiple schema sources.

Here's a simple example of a schema rule file:

# A predicate that checks if a column is not null or has a default value.
predicate "column" "not_null_or_have_default" {
  or {
    default {
      ne = null
    }
    null {
      eq = false
    }
  }
}

rule "schema" "disallow-null-columns" {
  description = "require columns to be not null or have a default value"
  table {
    column {
      assert {
        predicate = predicate.column.not_null_or_have_default
        message   = "column ${self.name} must be not null or have a default value"
      }
    }
  }
}

The example above defines a rule that checks if all columns in a schema are either not null or have a default value.

To use it, you would add the rule file to you atlas.hcl configuration:

lint {
  rule "hcl" "table-policies" {
    src = ["schema.rule.hcl"]
  }
}

env "local" {
  src = "file://schema.lt.hcl"
  dev = "sqlite://?mode=memory"
}

Suppose we used the following schema:

schema "main" {
}

table "hello" {
  schema = schema.main
  column "id" {
    type = int
    null = true
  }
}

Next, Atlas offers two ways to invoke the linting process:

1. Using the migrate lint command in the Atlas CLI. This lints only new changes that are introduced in the linted changeset - typically the new migration files in the current PR.
2. Using the newly added schema lint command in the Atlas CLI. This lints the entire schema, including all tables and columns, and can be used to enforce schema rules during CI/CD pipelines.

Let's use the schema lint command to lint the schema:

atlas schema lint --env local -u env://src

The command will output the following error:

Analyzing schema objects (3 objects in total):

  rule "disallow-null-columns":
    -- schema.lt.hcl:7: column id must be not null or have a default value

  -------------------------
  -- 27.791µs
  -- 1 diagnostic

Great! Atlas has detected that the id column in the hello table is nullable and has raised an error. You can now update the schema to fix the error and rerun the linting process.

For more information on defining custom schema rules, check the docs for custom schema rules.

Manage schemas for LLM-based projects with pgvector​

pgvector has become the de-facto standard for storing high-dimensional vectors in PostgreSQL. It is widely used in LLM-based projects that require fast similarity search for large datasets. Use cases such as recommendation systems and RAG (Retrieval-Augmented Generation) pipelines rely on pgvector to store embeddings and perform similarity searches.

In this release, we've added support for managing schemas for projects that use pgvector. You can now use Atlas to automate schema migrations for your pgvector projects.

Suppose you are building a RAG pipeline that uses OpenAI embeddings to generate responses to user queries. Your schema might look like this:

schema "public" {
}

extension "vector" {
  schema  = schema.public
}

table "content" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "text" {
    type = text
  }
  column "embedding" {
    type = sql("vector(1536)")
  }
  primary_key {
    columns = [ column.id ]
  }
  index "idx_emb" {
    type = "IVFFlat"
    on {
      column = column.embedding
      ops    = "vector_l2_ops"
    }
    storage_params {
      lists = 100
    }
  }
}

Notice three key elements in the schema definition:

1. The extension block defines the vector extension, enabling pgvector support for the schema.
2. The column block defines a column named embedding with the vector(1536) type, which stores 1536-dimensional vectors.
3. The index block defines an index on the embedding column, which uses the IVFFlat method for similarity search.

For more information on defining pgvector powered indexes, check the docs for index storage parameters.

Simpler setup for Drift Detection​

Schema drift, which refers to a state where there is a difference between the intended database schema and its actual state, can lead to significant issues such as application errors, deployment failures, and even service outages.

In this release, we've simplified the process of setting up drift checks using the Atlas Cloud UI.

If you already use Atlas to run migrations, enable drift detection to stay on top of any manual schema changes that may cause a painful outage.

Watch the demo:

Multi-project ER Diagrams​

Many teams using Atlas adopt microservices-oriented architectures to build their applications. This approach allows them to develop, scale, and deploy services independently, but it also introduces a unique challenge: database schemas are often spread across multiple logical (and sometimes physical) databases.

While these architectures excel at modularity (by decoupling teams from one another) they make it harder to visualize, manage, and reason about the overall data model. Traditional Entity-Relationship (ER) diagrams are designed for monolithic databases, where all relationships and constraints exist within a single system. However, in a microservices setup, relationships between entities often span across services, sometimes without explicit foreign keys or enforced constraints.

To help teams overcome this challenge, we are happy to announce today the availability of composite, multi-project ER diagrams in Atlas Cloud. Using this feature, teams can create diagrams that are composed of database objects from multiple Atlas projects.

See it in action:

Related Object Selection​

Over years of development, database schemas tend to become complex and may grow to encompass hundreds or even thousands of objects. Using the Atlas data modeling tool, users can curate smaller subgraphs of the schema, named “saved filters”, which can serve as documentation for a certain aspect of the system. To make this process easier, our team has added a new context menu that makes it easier to include connected objects (e.g tables that are connected via a Foreign Key) in saved filters.

Watch the example:

New Trust Center​

At Ariga, the company behind Atlas, we are committed to meeting the evolving security and compliance needs of our customers. As part this commitment we've consolidated our legal and compliance documentation into a single page in our new Trust Center.

Wrapping Up​

We hope you enjoy the new features and improvements. As always, we would love to hear your feedback and suggestions on our Discord server.

Happy new year everyone, and welcome to our first release of 2025, Atlas v0.30! We have some exciting new features and improvements to share with you.

In this release you will find:

1. Simplified Schema Monitoring: Previously you needed to install a long-running agent on your database VPC to monitor your schema. Schema monitoring is now even simpler with the introduction of a new agentless monitoring mode.
2. Drizzle Support: We now support Drizzle, a popular ORM for Node.js. You can now use Atlas to automate schema migrations for your Drizzle projects.
3. Bitbucket Pipelines: We have added support for Bitbucket Pipelines, making it easier to integrate Atlas into your Bitbucket CI/CD workflows.
4. Custom Kubernetes Configurations: Atlas Pro users can now provide custom atlas.hcl configurations for their Kubernetes Custom Resource Definitions (CRDs) using the Atlas Operator.
5. txtar Editor Support: The Atlas JetBrains plugin now supports editing txtar files, used by the Atlas CLI to define pre-migration checks but also useful for other purposes.

Simplified Schema Monitoring​

We released Atlas Schema Monitoring a few months ago to enabled teams to track changes to the schema of any database. Previously, this workflow required installing a long-running agent process on your infrastructure.

To simplify things further, you can now run schema monitoring workflows directly from your GitHub Actions pipelines.

Learn how in this quickstart guide, or watch the video:

Drizzle Support​

Following popular demand from the Drizzle community, we are excited to announce our official guide on using Atlas to manage database schemas for Drizzle projects.

Drizzle already provides a powerful migration tool as part of the drizzle-kit CLI, which handles many schema management needs seamlessly. However, because it is deeply integrated with the Drizzle ORM, there are scenarios where a standalone schema management solution like Atlas might be a better fit.

In collaboration with the Drizzle team, we’re thrilled to highlight a brand-new feature introduced in drizzle-kit: the drizzle-kit export command. This feature, developed in partnership with our team, allows you to easily export your existing schema for use with Atlas.

To learn more, check out our Drizzle guide.

Bitbucket Pipes​

Atlas is designed to integrate seamlessly with your CI/CD workflows. Today we are excited to announce that we natively support Bitbucket Pipes, making it easier to automate your database schema management tasks with Atlas.

Using the new Bitbucket integration, users can easily perform schema management related tasks, such as running migrations, verifying schema changes, and monitoring schema drift, directly from their Bitbucket Pipelines.

For example, you can use the arigaio/atlas-action pipe to run migrations on your target database:

image: atlassian/default-image:3
pipelines:
  branches:
    master:
      - step:
          name: "Applies a migration directory to a target database"
          script:
            - name: "Migrate Apply"
              pipe: docker://arigaio/atlas-action:master
              variables:
                ATLAS_ACTION: "migrate/apply" # Required
                ATLAS_INPUT_URL: ${DATABASE_URL}
                ATLAS_INPUT_DIR: "file://migrations"
            - source .atlas-action/outputs.sh

To learn more, check out our Bitbucket Pipes guide.

Custom Kubernetes Configurations​

The Atlas Operator now supports custom configurations for your Kubernetes CRDs. This feature is available to Atlas Pro users and enables use cases like loading credentials from an external secret store or defining custom linting rules for verifying the safety of your schema changes.

To enable this feature, install the latest version of the Atlas Operator Helm chart using the allowCustomConfig flag. Then, provide your custom atlas.hcl configuration file in the spec.config field of your Atlas CRD:

apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasSchema
metadata:
  name: sample
spec:
  envName: "test"
  schema:
    sql: |
      create table users (
        -- redacted for brevity
      );
  cloud:
    repo: atlas-operator
    tokenFrom:
      secretKeyRef:
        name: atlas-token
        key: ATLAS_TOKEN
  vars:
    - key: "db_url"
      valueFrom:
        secretKeyRef:
          name: schema-db-creds
          key: url
    - key: "dev_db_url"
      valueFrom:
        configMapKeyRef:
          name: schema-db-dev-creds
          key: url
  config: |
    variable "db_url" {
      type = string
    }
    variable "dev_db_url" {
      type = string
    }
    env "test" {
      url = var.db_url
      dev = var.dev_db_url
    }

For more information, check out the Atlas Operator documentation.

txtar Editor Support​

A few months ago, we shared our journey of building a robust testing framework for the Atlas CLI, in a blog post titled How Go Tests go test (originally created for GopherCon Israel 2024).

The post got a lot of attention in many Go communities (Including Golang Weekly #552) as it demonstrated how to utilize the Go team's internal tool for testing the Go toolchain itself.

Even more exciting, it seems that the post has stirred some interest in the Go community, with some fairly large projects adopting the same approach for their testing frameworks (see acceptance testing for the GitHub CLI and Cilium).

Over the past few years using this method for testing the Atlas CLI, our team has grown very fond of writing tests in txtar format. We found it to be a very expressive and concise way to define test cases, and it has become a core part of our testing strategy. Our appreciation for txtar has led us to adopt it in other parts of our tooling, including making it the way to define pre-migration checks in Atlas.

To make it easier for our users (and other teams using txtar for other purposes) to work with txtar files, we have added support for editing txtar files in the Atlas JetBrains plugin. This feature is available in the latest version of the plugin, which you can download from the JetBrains plugin repository.

In addition to txtar support, this release also includes support for HCL code formatting, making it easier than ever to write and maintain your Atlas schemas. To see both features in action, check out the video below:

More News and Updates​

• SOC2 Compliance. We have recently completed our SOC2 re-certification for the third year in a row. This certification demonstrates our commitment to providing a secure and reliable infrastructure for our users and customers. You can read more about this in our recent blogpost.
• Podcast Appearances. Catch Atlas on some recent pod cast episodes in: Kube.fm, The IaC Podcast, and Amazic.

Wrapping Up​

We hope you enjoy the new features and improvements. As always, we would love to hear your feedback and suggestions on our Discord server.

Today we are happy to announce that Atlas has achieved SOC2 compliance for the third year in a row. This is an important milestone for us, demonstrating our commitment to providing a solid infrastructure for our users and customers.

As a company that is trusted by its customers to handle mission-critical databases, we are committed to ensuring the highest standards of security, availability, and confidentiality. Achieving SOC 2 compliance demonstrates our dedication to safeguarding customer data, maintaining trust, and adhering to industry best practices.

Our commitment to process only metadata​

Control 70. Our commitment to not process records from your database. Screenshot from Ariga's full SOC2 audit report.

As anyone in the compliance domain knows, audits are about setting a high bar and then building controls to ensure they are met throughout our day-to-day operations. While these audits often address external requirements, such as regulatory mandates, they also serve as an opportunity to build trust with customers by addressing critical areas of concern within the company.

This year, we chose to use our audit process to address a common question from customers regarding how we handle their data. As a schema management tool, Atlas interacts with critical and sensitive data assets of our customers. This naturally raises concerns for compliance and security teams, as they are entrusted with protecting data on behalf of their own customers.

Atlas, and Atlas Cloud, our SaaS offering, are designed with a foundational principle: we do not store, send, or process user data—only metadata. We have consistently communicated this commitment to compliance teams, and after thorough discussions and reviews, they have been satisfied with this approach. However, this year we decided to formalize this commitment within our compliance framework.

As part of our SOC 2 audit, we introduced Control #70 which states: "The company does not process or store records from the customer's managed databases, but only handles information schema and metadata related to them."

By incorporating this control, we have established a clear, auditable process that reinforces our promise to our customers and ensures that this principle remains at the core of how we operate moving forward.

To summarize, achieving SOC 2 compliance for the third year reflects our core belief as engineers: security, privacy, and automation should drive auditable processes. SOC 2 provides the framework to solidify these principles into a trusted, transparent process for our customers.

If your compliance team, needs access to our report or other documents, drop us a line!

"Month over month, we see smaller and smaller incidents.", Luiz Casali, Sr Engineering Manager

Company Background​

Unico is a leading digital identity technology provider in Brazil, developing secure and efficient digital identity solutions for businesses and individuals. Their platform helps organizations streamline identity verification processes, delivering a seamless user experience while enhancing security and reducing fraud.

The Missing Layer: Tackling Complexity, Outages, and Risks in Database Schema Management​

At Unico, the Platform Engineering team, led by Luiz Casali, is focused on improving developer productivity. "Reducing complexity for developers is one of our top priorities," Luiz explained.

Unico's Platform team had previously built solutions to automate CI/CD workflows for code using Bazel and GitHub Actions and for infrastructure using Terraform and Atlantis. The team was missing a standardized solution for managing database schema changes.

This gap introduced several pressing issues:

1. Risky Manual Processes: Database schema changes (migrations) were performed manually, increasing the chance of human error.
2. Unreliable Deployments: Unplanned, database-related outages were common, emphasizing the need for a safer way to handle database changes.
3. Compliance Requirements: The team needed to document and review schema changes to maintain governance standards, but the lack of automation made this challenging.

Determined to bridge this gap and establish a safer, more efficient solution for developers, Unico's Platform Engineering team began researching the best database migration tools. Thiago da Silva Conceição, a Site Reliability Engineer (SRE) in the team, took the lead on this technical evaluation.

The Challenge of Managing Database Schema Migrations​

Traditional schema migration tools posed significant challenges for Unico. As Thiago noted, "Automation with our previous tool was never easy to maintain. It followed a specific pattern, and only a few team members were familiar with it." The team faced limitations that affected usability, integration, and overall productivity:

• Limited Usability and Adoption: The tool required extensive knowledge, and documentation was limited, making it difficult to adopt across the team.
• Lack of Automation Support: Automated migrations and reliable error-handling were lacking, leading to inconsistent deployments and a need for additional manual oversight.
• Compliance Difficulties: The absence of automated documentation and governance features made it challenging to maintain and provide records for audit and compliance requirements.

With these challenges in mind, Unico needed a solution that could offer usability, integration with existing tools, and comprehensive metrics to continuously monitor and improve database migrations.

Evaluating Alternatives and Choosing Atlas​

"In the end, choosing Atlas was easy. It is a simple, yet powerful tool, offering a significant impact with many ready-made features that would require customization in other tools."
Thiago Silva Conceição, SRE, Unico

During the search for a new solution, Unico's engineering team prioritized several criteria:

1. Ease of Use: The tool needed to be straightforward and accessible for all developers, not just a few specialized team members.
2. Integration and Compatibility: It had to fit naturally with Unico's technology stack, particularly with Terraform, which was already in heavy use.
3. Metrics and Alerts: Real-time insights and alerts were essential to monitor migrations effectively.

Thiago compared a few traditional solutions before selecting Atlas. Atlas's declarative schema-as-code approach, along with its HCL compatibility and robust cloud management, aligned well with Unico's needs. It allowed the team to automate migrations, eliminate manual errors, and centralize schema management, creating a unified experience across their projects. "Atlas allowed us to keep the DDL in HCL while still supporting SQL scripts for specific use cases through its versioning model," Thiago shared.

One Migration Tool to Rule Them All​

Another key priority for Unico's Platform Engineering team was standardization. With multiple teams working across diverse programming languages and databases, The Platform Engineering team needed a unified migration tool that would work for a wide array of use cases, without sacrificing ease of use or reliability. To simplify the developer experience and streamline internal operations, they aimed to find a single solution that could support all teams consistently and seamlessly.

Atlas emerged as the ideal fit by providing plugin support for various databases, ORMs and integrations, making it a flexible tool for Unico's entire tech stack. The ability to standardize migration management with Atlas allowed Unico's Platform Engineering team to enforce consistent practices across all projects. Atlas became the single source of truth for schema management, offering a centralized framework for building policies, integrating into CI/CD pipelines, and supporting developers.

By implementing Atlas as a standard, the Platform Engineering team eliminated the need to train on or maintain multiple tools, reducing complexity and operational overhead. Now, Unico's developers enjoy a unified experience, and the platform team has only one tool to integrate, support, and scale as the company grows.

Implementation: A Smooth Migration Process​

The migration to Atlas was seamless, with no need to recreate migration files or impose rigid formats. "We simply imported the schemas from the production database, keeping the process straightforward and efficient," Thiago said. The team was able to quickly onboard Atlas and start seeing results, with pre-built actions in Atlas Cloud providing essential metrics, notifications, and dashboards for tracking progress.

This success reinforced the decision to adopt Atlas:

"Month over month, we see smaller and smaller incidents.

— Luiz Casali, Senior Engineering Manager

Outcome: Faster Development Cycles, Increased Reliability, and Enhanced Compliance​

With Atlas in place, Unico's Platform Engineering team has achieved several key outcomes:

• Accelerated Development Cycles: Automation of database migrations streamlined the development process, enabling faster iterations and more rapid deployments.
• Increased Reliability: Atlas's linting and testing tools reduced migration errors and enhanced deployment stability, contributing to Unico's goal of reducing incidents.
• Enhanced Compliance: Atlas's automated documentation ensures that each migration step is recorded, simplifying compliance by providing a clear, auditable record of all schema changes.

By automating these processes, the team has successfully reduced manual work and achieved a more predictable migration workflow. Now, as Unico grows, they are assured that their migration practices will scale smoothly, maintaining operational costs without sacrificing speed or reliability.

Getting Started​

Atlas brings the declarative mindset of infrastructure-as-code to database schema management, similar to Terraform, but focused on databases. Using its unique schema-as-code approach, teams can quickly inspect existing databases and get started with minimal setup.

Like Unico, we recommend anyone looking for a schema migration solution to get started with Atlas by trying it out on one or two small projects. Dive into the documentation, join our Discord community for support, and start managing your schemas as code with ease.

Hey everyone!

We are excited to announce the release of Atlas v0.29, which continues our journey to make working with database easier, safer and more reliable. This release includes several significant updates that we are happy to share with you:

• Approval flows for the Kubernetes Operator: Moving to a declarative way of managing database schemas has plenty of advantages, but many teams want to see and approve changes before they are applied. Doing this from the CLI is straightforward, but until recently it was not easy to provide this experience in Kubernetes-based workflows.

  With the new approval flows, you can now review and approve schema migrations seamlessly, ensuring database changes are well-governed while maintaining deployment velocity.

• Prisma support: Following our integrations with some of the most popular ORMs in our industry, we are happy to announce our official guide on using Atlas to manage database schemas for Prisma projects.

• GitLab CI/CD Components: Integrating GitLab CI with Atlas just got much easier with the new GitLab CI/CD components.

• IntelliJ Plugin: Our IntelliJ plugin has been upgraded with code folding, inline SQL syntax highlighting and suggestions, and syntax highlighting within heredoc clauses.

• Timeseries Engine support for ClickHouse: ClickHouse users can now explore beta support for timeseries data in Atlas.

• Constraint Triggers support for PostgreSQL: PostgreSQL users can now manage constraint triggers with Atlas.

Approval Flows for the Kubernetes Operator​

Moving to a declarative way of managing database schemas has plenty of advantages, but many teams want to see and approve changes before they are applied.

Providing flows for keeping a human-in-the-loop from the CLI is straightforward, but until recently it was not easy to provide this experience in Kubernetes-based workflows.

Following our recent KubeCon session, the Atlas Operator now includes approval flows for declarative schema migrations, making database changes in Kubernetes safer:

1. Pre-approvals - with pre-approvals, teams enhance their CI pipelines to detect schema changes and integrate their planning and approval in the code review process. The approved policies are then applied to the database by the operator.
2. Ad-hoc Approvals - with ad-hoc approvals, the operator pauses the migration process and waits for human approval before applying the schema change. This is useful for schema changes that were not approved in advance or for projects that do not have a strict pre-approval policy.

Prisma Support​

Following popular demand from the Atlas community, we are excited to announce our official guide on using Atlas to manage database schemas for Prisma projects.

Prisma already has an excellent migration tool called prisma migrate, so why would you want to use Atlas with Prisma? In many cases, Prisma's migrate indeed checks all the boxes for managing your database schema. However, being tightly coupled with the Prisma ORM, some use cases might require a dedicated schema management tool that can be used across different ORMs and frameworks.

This guide shows how to load your existing prisma.schema file into Atlas, manage your schema changes, and apply them to your database using the Atlas CLI.

Interested in learning more? Read the guide!

Gitlab CI/CD Components​

Integrating GitLab CI with Atlas just got much easier with our new GitLab CI/CD components.

GitLab CI/CD components are reusable YAML templates that you can use in your GitLab CI/CD pipelines to automate workflows within your GitLab project. Our newly published components are designed to simplify the process of integrating Atlas with GitLab CI/CD pipelines, enabling you to automate database schema management tasks with ease.

Want to learn more? Read the tutorial.

IntelliJ Plugin​

Our IntelliJ plugin has been upgraded with code folding, inline SQL syntax highlighting and suggestions, and syntax highlighting within heredoc clauses. Our goal with these efforts is to make writing real world database applications with Atlas easier and more enjoyable.

If you use JetBrains editors, be sure to download the most recent version.

Timeseries Data Support for ClickHouse​

ClickHouse recently added support for an experimental TimeSeries engine, which is designed to optimize storage and query performance for time-series data.

Atlas now supports this experimental feature, enabling ClickHouse users to manage schemas for their time-series data tables with ease:

You can simply define a TimeSeries table in your Atlas schema.

table "example" {
  schema = schema.public
  engine = TimeSeries
}

PostgreSQL Constraint Triggers​

The CONSTRAINT TRIGGER is a PostgreSQL extension of the SQL standard, which works like a regular trigger but allows its execution time to be dynamically controlled using the SET CONSTRAINTS command.

Starting with this version, users can define constraint triggers, and Atlas will manage their lifecycles. Their definitions are also supported in the Atlas HCL syntax:

trigger "users_insert" {
  on         = table.users
  constraint = true
  before {
    insert = true
  }
  // ...
}

trigger "groups_insert" {
  on         = table.users
  constraint = true
  deferrable = INITIALLY_DEFERRED
  before {
    insert = true
  }
  // ...
}

Read more about the Storage Engine

Wrapping Up​

We hope you enjoy the new features and improvements. As always, we would love to hear your feedback and suggestions on our Discord server.

Prepared for KubeCon North America 2024

Introduction​

For two decades now, the common practice for handling rollbacks of database schema migrations has been pre-planned "down migration scripts". A closer examination of this widely accepted truth reveals critical gaps that result in teams relying on risky, manual operations to roll back schema migrations in times of crisis.

In this post, we show why our existing tools and practices cannot deliver on the GitOps promise of "declarative" and "continuously reconciled" workflows and how we can use the Operator Pattern to build a new solution for robust and safe schema rollbacks.

The Undo Button​

One of the most liberating aspects of working on digital products is the ability to roll back changes. The Undo Button, I would argue, is one of the most transformative features of modern computing.

Correcting mistakes on typewriters was arduous. You would roll the carriage back and type over any errors, leaving messy, visible corrections. For bigger changes, entire pages had to be retyped. Correction fluid like whiteout offered a temporary fix, but it was slow and imprecise, requiring careful application and drying time.

Digital tools changed everything. The Undo Button turned corrections into a simple keystroke, freeing creators to experiment without fear of permanent mistakes. This shift replaced the stress of perfection with the freedom to try, fail, and refine ideas.

Rollbacks and Software Delivery​

When it comes to software delivery, having an Undo Button is essential as well. The ability to roll back changes to a previous state is a critical safety net for teams deploying new features, updates, or bug fixes. Specifically, rollbacks impact one of the key metrics of software delivery: Mean Time to Recovery (MTTR).

MTTR is a measure of how quickly a system can recover from failures. When a deployment fails, or a bug is discovered in production, teams generally have two options: triage and fix the issue (roll forward), or roll back to a previous known stable state.

When the fix to an issue is not immediately clear, or the issue is severe, rolling back is often the fastest way to restore service. This is why having a reliable rollback mechanism is crucial for reducing MTTR and ensuring high availability of services.

How are rollbacks even possible?​

Undoing a change in a local environment like a word processor is straightforward. There are multiple ways to implement an Undo Button, but they all rely on the same basic principle: the system keeps track of every change made and can revert to a previous state.

In a distributed system like modern, cloud-native applications, things are not so simple. Changes are made across multiple components with complex dependencies and configurations.

The key capability that enables rolling back changes is described in the seminal book, "Accelerate: The Science of Lean Software and DevOps". The authors identify "Comprehensive Configuration Management" as one of the key technical practices that enables high performance in software delivery:

"It should be possible to provision our environments and build, test, and deploy our software in a fully automated fashion purely from information stored in version control.” ¹

In theory, this means that if we can store everything there is to know about our system in version control, and have an automated way to apply these changes, we should be able to roll back to a previous state by simply applying a previous commit.

GitOps and Rollbacks​

The principle of "Comprehensive Configuration Management" evolved over the years into ideas like "Infrastructure as Code" and "GitOps". These practices advocate for storing all configuration and infrastructure definitions in version control in a declarative format, and using automation to apply these changes to the system.

Projects like ArgoCD and Flux have popularized the GitOps approach to managing Kubernetes clusters. By providing a structured way to define the desired state of your system in Git (e.g., Kubernetes manifests), and automatically reconciling the actual state with it, GitOps tools provide a structured and standardized way to manage satisfy this principle.

On paper, GitOps has finally brought us a working solution for rollbacks. Revert the commit that introduced the issue, and all of your problems are gone!

Problem solved. End of talk. Right?

The Hard Truth​

Teams that have tried to fully commit to the GitOps philosophy usually find that the promise of "declarative" and "continuously reconciled" workflows is not as straightforward as it seems. Let's consider why.

Declarative resource management works exceptionally well for stateless resources like containers. The way Kubernetes handles deployments, services, and other resources is a perfect fit for GitOps. Consider how a typical deployment rollout works:

1. A new replica set is created with the new version of the application.
2. Health checks are performed to ensure the new version is healthy.
3. Traffic is gradually shifted to healthy instances of the new version.
4. As the new version proves stable, the old version is scaled down and eventually removed.

But will this work for stateful resources like databases? Suppose we want to change the schema of a database. Could we apply the same principles to roll back a schema migration? Here's what it would look like:

1. A new database is spun up with the new schema.
2. Health checks are performed to ensure the new schema is healthy.
3. Traffic is gradually shifted to the new database instance.
4. The old database is removed.

This would get the job done... but you would probably find yourself out of a job soon after.

Stateless resources are really great to manage because we can always throw out whatever isn't working for us and start fresh. But databases are different. They are stateful, and they are comprised not only of a software component (the database engine), the configuration (server parameters and schema), but also the data itself. The data itself cannot, by definition, be provisioned from version control.

Stateful resources like databases require a different approach to manage changes.

Up and Down Migrations​

The common practice for managing schema changes in databases is to use "up" and "down" migration scripts in tandem with a migration tool (like Flyway or Liquibase). The idea is simple: when you want to make a change to the schema, you write a script that describes how to apply the change (the "up" migration). Additionally, you write a script that describes how to undo the change (the "down" migration).

For example, suppose you wanted to add a column named "short_bio" to a table named "users". Your up migration script might look like this:

ALTER TABLE users
    ADD COLUMN short_bio TEXT;

And your down migration script might look like this:

ALTER TABLE users DROP COLUMN short_bio;

In theory, this concept is sound and satisfies the requirements of "Comprehensive Configuration Management". All information needed to apply and roll back the change is stored in version control.

Theory, once again, is quite different from practice.

The Myth of Down Migrations​

After interviewing hundreds of engineers on this topic, we found that despite being a widely accepted concept, down are rarely used in practice. Why?

Naive assumptions​

When you write a down migration, you are essentially writing a script that will be executed in the future to revert the changes you are about to make. By definition, this script is written before the "up" changes are applied. This means that the down migration is based on the assumption that the changes will be applied correctly.

But what if they are not?

Suppose the "up" migration was supposed to add two columns, the down file would be written to remove these two columns. But what if the migration was partially applied and only one column was added? Running the down file would fail, and we would be stuck in an unknown state.

Yes, some databases like PostgreSQL support transactional DDL, which means that if the migration fails, the changes are rolled back, and you end up with a state this consistent with a specific revision. But even for PostgreSQL, some operations cannot be run in a transaction, and the database can end up in an inconsistent state.

For MySQL, which does not support transactional DDL, the situation is even worse. If a migration fails halfway through, you are left with only a partially applied migration and no way to roll back.

Data loss​

When you are working on a local database, without real traffic, having the up/down mechanism for migrations might feel like hitting Undo and Redo in your favorite text editor. But in a real environment with real traffic, it is not the case.

If you successfully rolled out a migration that added a column to a table, and then decided to revert it, its inverse operation (DROP COLUMN) does not merely remove the column. It deletes all the data in that column. Re-applying the migration would not bring back the data, as it was lost when the column was dropped.

For this reason, teams that want to temporarily deploy a previous version of the application, usually do not revert the database changes, because doing so will result in data loss for their users. Instead, they need to assess the situation on the ground and figure out some other way to handle the situation.

Incompatibility with modern deployment practices​

Many modern deployment practices like Continuous Delivery (CD) and GitOps advocate for the software delivery process to be automated and repeatable. This means that the deployment process should be deterministic and should not require manual intervention. A common way of doing this is to have a pipeline that receives a commit, and then automatically deploys the build artifacts from that commit to the target environment.

As it is very rare to encounter a project with a 0% change failure rate, rolling back a deployment is something everyone needs to be prepared for.

In theory, rolling back a deployment should be as simple as deploying the previous version of the application. When it comes to versions of our application code, this works perfectly. We pull and deploy the container image corresponding to the previous version.

This strategy does not work for the database, for two reasons:

1. For most migration tools, down or rollback is a separate command that needs to be executed specifically. This means that the deployment machinery needs to know what the current version of the target database is in order to decide whether to migrate up or down.
2. When we pull artifacts from a previous version, they do not contain the down files that are needed to revert the database changes back to the necessary schema - they were only created in a future commit!

These gaps mean that teams are left with two options: either they need to manually intervene to roll back the database changes, or they need to develop a custom solution that can handle the rollback in an automated way.

Down Migrations and GitOps​

Going back to our main theme of exploring whether database rollbacks and GitOps can be compatible, let's expand on this last point.

The ArgoCD documentation suggests that the way to integrate schema migrations is to use a Kubernetes Job that executes your migration tool of choice, and to annotate the Job as a PreSync hook:

This image will typically be built as part of your CI/CD pipeline, and will contain the migration tool and the migration scripts for the relevant commit or release:

apiVersion: batch/v1
kind: Job
metadata:
  name: db-migration
  annotations:
    argocd.argoproj.io/hook: PreSync
    argocd.argoproj.io/hook-delete-policy: HookSucceeded
spec:
  template:
    spec:
      containers:
        - name: migrate
          image: your-migration-image:{{ .Values.image.tag }} # Example using Helm values
      restartPolicy: Never

When ArgoCD detects a new commit in the Git repository, it will create a new Job that runs the migration tool. If the migration is successful, the Job will complete successfully, and the new version of the application will be deployed.

This will work for the up migration. But what happens when you need to roll back?

Teams commonly hit the two issues we mentioned above:

1. The deployment machinery does not know what the current version of the target database is, and therefore cannot decide whether to migrate up or down.

   Unless a team has carefully thought about this and implemented a mechanism inside the image to decide what to do, the deployment machinery will always migrate up.

2. The image that is pulled for the rollback does not contain the down files that are needed to revert the database changes back to the necessary schema. Most migration tools will silently keep the database in the current state.

What are the implications?

1. The database is no longer in sync with the current Git commit, violating all GitOps principles.

2. Teams that do need to roll back the database changes are left with a manual process that requires intervention and coordination.

Operators: The GitOps Way​

The Operator Pattern is a Kubernetes-native way to extend the Kubernetes API to manage additional resources. Operators typically ship two main components: a Custom Resource Definition (CRD) that defines the new resource type, and a controller that watches for changes to these resources and takes action accordingly.

The Operator Pattern is a perfect fit for managing stateful resources like databases. By extending the Kubernetes API with a new resource type that represents a database schema, we can manage schema changes in a GitOps-friendly way. A specialized controller can watch for changes to these resources and apply the necessary changes to the database in a way that a naive Job cannot.

The Atlas Operator​

The Atlas Operator is a Kubernetes Operator that enables you to manage your database schemas natively from your Kubernetes cluster. Built on Atlas, a database schema-as-code tool (sometimes called "like Terraform for databases"), the Atlas Operator extends the Kubernetes API to support database schema management.

Atlas has two core capabilities that are helpful to building a GitOps-friendly schema management solution:

1. A sophisticated migration planner that can generates migrations by diffing the desired state of the schema with the current state of the database.
2. A migration analyzer that can analyze a migration and determine whether it is safe to apply and surface risks before the migration is applied.

Declarative vs. Versioned Flows​

Atlas supports two kinds of flows for managing database schema changes: declarative and versioned. They are reflected in the two main resources that the Atlas Operator manages:

Declarative: AtlasSchema​

The first resource type is AtlasSchema which is used to employ the declarative flow. With AtlasSchema, you define the desired state of the database schema in a declarative way, and the connection string to the target database.

The Operator is then responsible for generating the necessary migrations to bring the database schema to the desired state, and applying them to the database. Here is an example of an AtlasSchema resource:

apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasSchema
metadata:
  name: myapp
spec:
  url: mysql://root:pass@mysql:3306/example
  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)
      );

When the AtlasSchema resource is applied to the cluster, the Atlas Operator will calculate the diff between the database at url and the desired schema, and generate the necessary migrations to bring the database to the desired state.

Whenever the AtlasSchema resource is updated, the Operator will recalculate the diff and apply the necessary changes to the database.

Versioned: AtlasMigration​

The second resource type is AtlasMigration which is used to employ the versioned flow. With AtlasMigration, you define the exact migration that you want to apply to the database. The Operator is then responsible for applying any necessary migrations to bring the database schema to the desired state.

Here is an example of an AtlasMigration resource:

apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasMigration
metadata:
  name: atlasmigration-sample
spec:
  url: mysql://root:pass@mysql:3306/example
  dir:
    configMapRef:
      name: "migration-dir" # Ref to a ConfigMap containing the migration files

When the AtlasMigration resource is applied to the cluster, the Atlas Operator will apply the migrations in the directory specified in the dir field to the database at url. Similarly to classic migration tools, Atlas uses a metadata table on the target database to track which migrations have been applied.

Rollbacks with the Atlas Operator​

The Atlas Operator is designed to handle rollbacks in a GitOps-friendly way. This is where the power of the Operator Pattern really shines as it can make nuanced and intelligent decisions about how to handle changes to the managed resources.

To roll back a schema change in an ArgoCD-managed environment, you would simply revert the AtlasSchema or AtlasMigration resource to a previous version. The Atlas Operator would then analyze the changes and generate the necessary migrations to bring the database schema back to the desired state.

Advantages of the Operator Pattern​

In the discussion above we kept talking about edge cases that arise when rolling back database schema changes, and concluded that they require manual consideration and intervention. What if we could automate this process?

The Operator Pattern is all about codifying operational knowledge into software. Let's consider how the Operator Pattern can be used to address the challenges we discussed:

1. Understanding intent. The Operator can discern between up and down migrations. By comparing between the current state of the database and the desired version, the operator decides whether to go up or down.

2. Having access to the necessary information. Contrary to a Job that only has access to the image it was built with, the Operator stores metadata about the last execution as a ConfigMap via the Kubernetes API. This metadata enables the operator to migrate down even though the current image does not information about the current state.

3. Intelligent Diffing. Because the Operator is built on top of Atlas's Schema-as-Code engine, it can calculate correct migrations even if the database is in an inconsistent state.

4. Safety checks. The Operator can analyze the migration and determine whether it is safe to apply. This is a critical feature that can prevent risky migrations from being applied. Depending on your policy, it can even require manual approval for specific types of changes!

Conclusion​

In this talk, we explored the challenges of rolling back database schema changes in a GitOps environment. We discussed the limitations of the traditional up/down migration approach, and how the Operator Pattern can be used to build a more robust and automated solution.

If you have any questions or would like to learn more, please don't hesitate to reach out to us on our Discord server.

Prepared for an Atlas Community Webinar, October 2024

Introduction​

In recent years, the shift to declarative resource management has transformed modern infrastructure practices. Groundbreaking projects like Terraform, for infrastructure as code, and Kubernetes, for container orchestration, have exemplified the power of this approach. By focusing on what the end state should be rather than how to achieve it, declarative methods make systems more scalable, predictable, and easier to maintain—essential qualities for handling today's complex environments.

However, when it comes to managing database schemas, the industry has been slow to adopt declarative workflows. Atlas was created almost four years ago to address this gap.

Atlas supports two kinds of database schema migration workflows:

• Versioned Migrations - each change to the database is described as a migration script, essentially a SQL file containing the SQL commands to apply the change. Migrations are versioned and applied in order.

  Contrary to most existing migration tools, Atlas relies on users defining the desired state of the database schema in code Atlas generates the necessary migration scripts to bring the database to the desired state.

• Declarative Migrations - the database schema is described in a declarative way, and changes are applied by comparing the desired schema with the current schema and generating the necessary SQL commands to bring the database to the desired state.

To date, most teams that used Atlas in production have used it's versioned migration workflow which synthesizes the simplicity and explicitness of classic migration tools with the benefit of automated migration generation.

Recent improvements to Atlas have addressed many of the challenges and concerns teams have expressed around using declarative migrations in production in the past. In this post, we'll take a deep dive into the declarative migration workflow

Declarative Migrations in Production?​

Declarative migrations are a powerful concept, and it may surprise you, but, they are not new. In previous incarnations, they were often referred to as "state based" migrations, but they were never regarded as a production-grade solution. A quick look around the documentation of popular tools will reveal that they are often discouraged:

Hibernate ORM:

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.

EF Core Docs:

EnsureCreated will create the database if it doesn't exist and initialize the database schema. If any tables exist (including tables for another DbContext class), the schema won't be initialized.

Liquibase:

State-based deployments offer a quick, simplistic way to determine change scripts and are useful for analysis and drift detection. Due to its simplicity, though, it’s unreliable and risky to use as a primary change management approach.

Challenges of Declarative Migrations​

Having interviewed many engineers and architects about their experience with schema management, we've identified several concerns that teams have with declarative migrations:

Approval Process​

Versioned migrations serve a dual purpose, they are both an explicit set of instructions for the migration tool and a source-controlled artifact that can be examined and approved by human reviewers.

For many teams, the idea of only versioning the desired state of the database schema and not the actual migration scripts means forfeiting their main mechanism for review and approval of schema changes. This is a valid concern, and any production-grade declarative migration tool should provide a way to review and approve changes before they are applied.

Safety​

Schema changes are a risky business, if you are not careful you can easily find yourself in a situation where you've accidentally deleted data, locked a table for writes for a long period of time or introduced a production outage via a breaking schema change.

By definition, migrations generated by declarative workflows are non-deterministic, as it depend as they depend on the current state of the database. This also one of their main advantages, as they can handle unexpected circumstances and reconcile drift automatically. However, many teams are uncomfortable with the idea of not knowing exactly what will be applied to their database.

As one user once wrote us on HackerNews:

I'll never be comfortable with any tool for that automatically generates schema changes, as I'm just never sure at what point it decides to delete parts of my prod db.

Customization​

Another common concern is the lack of control over the generated migrations. Migration planning can be thought of as a navigation problem, how to get from point A to point B. Declarative migrations are like using a GPS, you tell it where you want to go, and it figures out the best route. But what if you want to travel through some specific coordinates?

When it comes to database schema management, there may be multiple ways to get from the current to the desired state, some of them completely undesirable. For instance, a simple column rename can also be achieved by dropping the column and creating a new one (thereby losing all the data in the column) or with PostgreSQL index creation may lock a table if not used with the CONCURRENTLY option.

Tools like Kubernetes and Terraform don't let the user specify the exact steps to get to the desired state, or require the development of custom plugins (such as Terraform Providers or Kubernetes Controllers) to achieve this. For database schema changes, this level of customization is often necessary.

Production-grade Declarative Migrations​

Atlas was designed with these concerns in mind and in the next section, we'll explore how Atlas addresses these concerns to provide teams with a production-grade declarative migration workflow.

Pre-planning Migrations​

To address users' need for knowing the exact migrations that will be applied to their database, Atlas provides the schema plan command. Users may run a command similar to:

atlas schema plan --env local --save

Atlas will calculate the plan, analyze it and present the user with a detailed report of the migrations that will be applied and their potential impact on the database:

Planning migration from local database to file://./schema.pg.hcl (1 statement in total):

  -- create "t1" table:
    -> CREATE TABLE "t1" (
         "c1" text NOT NULL
       );

-------------------------------------------

Analyzing planned statements (1 in total):

  -- no diagnostics found

  -------------------------
  -- 19.389709ms
  -- 1 schema change
? Approve or abort the plan: 
  ▸ Approve and save
    Abort

After saving, a "plan file" file is created:

plan "20241029120559" {
  from      = "PKrr2qiovsNurI70kgT+AW3rInDu74E1PSOSHgh8CrA="
  to        = "Szdpl/ADvG8kKrXBERSxjvU/I1nprpBejpPX7fLoWmc="
  migration = <<-SQL
  -- Create "t1" table
  CREATE TABLE "t1" (
    "c1" text NOT NULL
  );
  SQL
}

The plan block contains three relevant fields:

• from - A "fingerprint" hash which encodes the "current" state of the database.
• to - A similar hash for the "desired" state of the database.
• migration - The SQL commands that will be applied to the database to bring it from the from state to the to state.

The from and to fields are significant because they allow Atlas to guarantee that the plan will only be executed if the database is in the known from state. This is a crucial safety feature that enables the deterministic execution of the plan.

The plan file can then be applied to the database using the schema apply command:

atlas schema apply --env local --plan file://20241029120559.plan.hcl

Customization​

As we previously mentioned, there are often multiple ways to get from the current to the desired state of the database. To provide users with the ability to customize the generated migrations, Atlas provides two ways to control the process.

Diff Policies​

Diff policies can be defined on the project level to control how Atlas generates migrations. For instance, a project may define a policy that indexes must be created or dropped concurrently:

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

In some cases, users may want to tell Atlas to skip DROP operations completely:

env "local" {
  diff {
    skip {
      drop_schema = true
      drop_table  = true
    }
  }
}

Editing Plans​

In some cases, users may want to edit the generated plan before applying it. By using the --edit flag on the schema plan command, Atlas will open the plan in the user's default editor for review and modification:

When the user runs:

atlas schema plan --env local --edit --save

Atlas will calculate the plan, and open it in the user's default editor:

-- Create "t1" table
CREATE TABLE "t1" (
  "c1" text NOT NULL
);

The user may edit the plan as needed, adding or removing statements, and then save the plan.

-- Create "t1" table
CREATE TABLE "t1" (
  "c1" text NOT NULL
);
+ INSERT INTO "t1" ("c1") VALUES ('hello');

Atlas verifies that the edited plan is still valid and that the resulting schema is equivalent to the desired schema.

Suppose a user makes a change that is not in line with the desired schema, for instance, adding a column that is not in the desired schema:

-- Create "t1" table
CREATE TABLE "t1" (
  "c1" text NOT NULL,
+ "c2" text NOT NULL
);

Atlas will detect the drift and present the user with an error message:

Abort: the planned state does not match the desired state after applying the file:

--- planned state
+++ desired state
@@ -1,3 +1,4 @@
 CREATE TABLE "t1" (
-  "c1" text NOT NULL
+  "c1" text NOT NULL,
+  "c2" text NOT NULL
 );

Safety Checks​

In addition to its advanced diffing capabilities, Atlas contains a migration analysis engine that can simulate changes and detect potential issues before they occur. Until recently, this engine was only available to users of the versioned migration workflow via the migrate lint command. However, in the latest release, we've included the analysis step in the schema plan and schema apply commands for declarative migrations.

When a user runs the schema plan command, Atlas will analyze the plan and present the user with a detailed report of the potential impact of the migrations.

For instance, if a user tries to drop a column or table, Atlas will present a warning:

Planning migration from local database to file://./schema.pg.hcl (1 statement in total):

  -- drop "t1" table:
    -> DROP TABLE "t1";

-------------------------------------------
Analyzing planned statements (1 in total):

  -- destructive changes detected:
    -- L2: Dropping table "t1"
       https://atlasgo.io/lint/analyzers#DS102
  -- suggested fix:
    -> Add a pre-migration check to ensure table "t1" is empty before dropping it

  -------------------------
  -- 16.281417ms
  -- 1 schema change
  -- 1 diagnostic

? Approve or abort the plan:
  ▸ Approve and save
    Abort

For a full list of diagnostics and suggested fixes, users can visit the Atlas Lint Analyzers page.

Modern CI/CD Workflows with Declartive Migrations​

Atlas was designed to be used in modern CI/CD workflows, and the declarative migration workflow is no exception. In the final section of this post, we'll explore how teams can integrate Atlas into their CI/CD pipelines to ensure that:

1. All changes to the database schema result from changes to the desired state.
2. Changes are planned automatically during the CI process.
3. Changes are analyzed automatically to detect potential issues.
4. Changes are approved by the team before being applied to the database.
5. Changes are applied to the database in a deterministic and safe manner.

Demo: CI/CD Workflow with Atlas​

Here's a quick demo video of how to integrate Atlas into a CI/CD pipeline using GitHub Actions:

.

To summarize, our demo CI/CD workflow works as follows:

1. The user changes the desired state of the database, in our case a file named schema.sql.
2. The user commits the changes to the schema.sql file and creates a pull request.
3. GitHub Actions runs the atlas schema plan command to calculate the migration plan.
4. The Action creates a comment on the pull request with the following information:
   • The migration plan.
   • An analysis of the changes, including any potential issues.
   • Instructions for manually editing the plan if needed.
5. The user reviews the plan and approves the PR.
6. Once the PR is merged, GitHub Actions run another action that pushes the new state and plan to the Schema Registry.
7. Once the desired state and plan are in the Schema Registry, the user can deploy the changes to the database using the atlas schema apply command.

A word about the Schema Registry​

The Schema Registry is a fairly new concept introduced by Atlas. The registry is a central location where schemas and migration plans can be stored and shared among team members. You can think about it as a "DockerHub for Schema Management" or an "Artifact Repository for Database Schemas".

As we saw, it is possible to simply use files to store the desired state and migration plans, but the Schema Registry provides several advantages:

• Source of Truth - the registry can be used as the source of truth for the database schema, ensuring that all team members are working with the same schema and that all changes are tracked and go through a structured review process. Similar to how teams use DockerHub to store and share container images, making sure that only code that went through a team's CI/CD pipeline makes it's way to production.
• Streamlined Deployment - as we will see below, the registry isn't a static blob storage, but a dynamic service that can be queried and interacted with. This allows the Atlas CLI to only fetch relevant and approved migration plans, during deployment, enforcing the deterministic and safe execution of the migrations.
• Visualizations and Insights - the registry can be used to generate visualizations of the schema, track changes over time, and provide insights into the state of the database. This can be useful for auditing, debugging, and planning future changes.

How everything fits together​

As we saw in the demo, the Schema Registry is a central component of the CI/CD workflow. It acts as the source of truth for the database schema and migration plans, ensuring that all changes are tracked and reviewed before being applied to the database.

Here's what happens when a user runs the atlas schema apply command:

1. Atlas inspects the target database and fetches the current state of the schema. Atlas assigns a "fingerprint hash".
2. Atlas analyzes the desired state of the database and calculates the hash for that state.
3. Atlas uses this tuple of (from, to) hashes to search the Schema Registry for a matching migration plan.
4. If a plan is found, Atlas verifies that it is approved and safe to apply.
5. If the plan is approved, Atlas applies the plan to the database.

What happens if an approved plan is not found?​

By default, Atlas will not apply any changes to the database if an approved plan is not found. This is a safety feature that ensures that only changes that have been reviewed and approved by the team are applied to the database.

However, in some cases, users may want to apply changes to the database that have not been approved. After all, most schema changes are simple and additive and don't pose any risk to the database. Additionally, some environments are mostly used for testing and development and don't require the same level of scrutiny as production.

In cases where no approved plan is found, Atlas will calculate the plan and run it through analysis ("linting").

Atlas provides a mechanism called "Review Policies" to define what should happen based on the linting results.

Review policies are defined in the project configuration file using the lint.review attribute:

lint {
  review = ERROR // ERROR | ALWAYS
}

The review policy can be set to one of the following values:

1. ERROR - Atlas will require manual review and approval only if the linting report contains errors, i.e., one of the analyzers is configured to return an error. See the destructive-changes analyzer as an example.
2. WARNING - Atlas will require manual review and approval if the linting report contains warnings (diagnostics) or errors. See the list of checks that can be detected by the analyzers.
3. ALWAYS (default) - Atlas will always require manual review and approval, regardless of the linting report.

Wrapping Up​

In this post, we've taken a deep dive into the declarative migration workflow in Atlas. Here's what we covered:

1. Until recently, declarative migrations were considered a risky and unreliable way to manage database schemas.
2. The top challenges teams have with declarative migrations are around approval, safety, and customization.
3. Atlas addresses these concerns by providing a way to review and approve changes, analyze and simulate changes, and customize the generated migrations.
4. Atlas can be integrated into modern CI/CD workflows to ensure that all changes to the database schema are tracked, reviewed, and applied in a deterministic and safe manner.
5. The Schema Registry is a central component of the CI/CD workflow, acting as the source of truth for the database schema and migration plans.
6. Atlas provides a mechanism called "Review Policies" to define what should happen based on the linting results.

We hope this post has given you a better understanding of how Atlas can be used to manage database schemas in a declarative way. If you have any questions or would like to learn more, please don't hesitate to reach out to us on our Discord server.

"Everything on Atlas is just making too much sense for us."
— Kaushik Shanadi, Chief Architect

Conceal, a cybersecurity company, creates a secure browsing experience using a browser extension. With a lean engineering team, When Conceal shifted from serving individual consumers to working with managed service providers (MSPs), their clients' security requirements drove the need for a robust, multi-tenant architecture to ensure data isolation and scalability.

Kaushik Shanadi, VP and Chief Architect, led the charge in finding that solution.

Evaluating Alternative Solutions​

To meet the growing business demands and provide sufficient isolation for each customer’s data in a scalable and secure manner, the team considered three alternatives:

1. Keep a Single Database, Isolating on the Application Layer: This option was quickly dismissed because of the team's negative experience with this solution.

   Like many others, they found that adding a tenant_id column to every table and hoping that developers remember to filter by it on every query was a burden and risk they were not willing to take. Additionally, this was not acceptable to some of their customers, who required strict data isolation.

2. Database-per-Tenant Approach: While this approach ensured both data isolation and scalability, the cost of maintaining a Redshift cluster for each customer made this alternative prohibitive.

3. Hybrid Solution (Schema-per-Tenant): Ultimately, they chose a schema-per-tenant model, which kept the data isolated and secure without the high cloud costs. This approach also offered the flexibility to switch specific customers to their own isolated database if needed.

"It was way too easy to do that with Atlas compared to any other method," Kaushik remarked.

The Challenge of Schema-per-Tenant Architecture​

Schema-per-tenant architectures present unique challenges, primarily around managing database schema migrations:

• Migration duration scales linearly with tenant count.
• Detecting inconsistencies becomes a needle in a haystack problem.
• Rollbacks are difficult to orchestrate.

Atlas overcomes these challenges with its declarative schema-as-code approach. By automating migration planning, Atlas ensures that every schema remains consistent, aligned, and easy to manage.

Read more about the challenges and how Atlas solves them here

Safety First: Managing 1,500+ Schemas with 7 Engineers Using One Tool​

Implementation​

According to Kaushik, the implementation process was easy and smooth. Amazon Redshift was a requirement for both long-term storage and machine learning (ML) training data. "Migrating with old migration tools is a nightmare," said Kaushik. After discovering that Atlas supports Redshift, he ran a few POCs locally to test Atlas.

"I was able to get everything working and saw how fast the migration process was, so we pushed it to development," he explained.

Outcome​

By leveraging Atlas' declarative Schema-as-Code capabilities and its ability to manage schema per tenant architectures, Conceal.IO successfully manages a fleet of over 1,500 identical Redshift schemas, isolating customers' data from one another as required. This unified approach ensures that all schemas are designed consistently, aligned, and kept in the same state. This alignment has resulted in:

• Faster feature deployment across all customers
• Improved analytics and ML preparation
• Faster onboarding for new engineers
• The ability to add more customers as needed without worrying about deployments becoming slower as they grow
• Fixed operational costs, ensuring scalability without an exponential rise in expenses

All of this was achieved in a safe and secure environment.

"Having a lot of database schemas but only one tool to manage them all makes our lives so much easier", Kaushik added.

Next Steps​

The Conceal.IO team plans to use the upcoming holiday season to migrate their production workloads running on PostgreSQL into Atlas as well. Tired of dealing with constant migration issues, Kaushik is confident that “using Atlas will make it so much easier.”

"Support has been awesome, and the speed has been incredible too," summarized Kaushik. "Everything I need from an enterprise solution is available out of the box. It was almost too easy to say — let’s just use Atlas."

Getting Started​

Atlas applies the declarative mindset to database schema management, like Terraform, but for databases. Using its unique, schema-as-code approach, teams can automatically inspect existing databases and get started in no time.

Like Conceal.IO, we recommend anyone looking for a schema migration solution to get started with Atlas by trying it out on one or two small projects. Read the documentation, join our Discord community for support, and start managing your schemas as code.