Last week we released v0.3.2 of the Atlas CLI.

Atlas is an open source tool that helps developers manage their database schemas. Atlas plans database migrations for you based on your desired state. The two main commands are inspect and apply. The inspect command inspects your database and the apply command runs a migration by providing an HCL document with your desired state.

The most notable change in this version is the ability to interact with multiple schemas in both database inspection and migration (the apply command).

Some other interesting features include:

• schema apply --dry-run - running schema apply in dry-run mode connects to the target database and prints the SQL migration to bring the target database to the desired state without prompting the user to approve it.
• schema fmt - adds basic formatting capabilities to .hcl files.
• schema diff - Connects to two given databases, inspects them, calculates the difference in their schemas, and prints a plan of SQL statements needed to migrate the "from" database to the state of the "to" database.

In this post we will explore the topic of multi-schema support. We will start our discussion with a brief explanation of database schemas, next we'll present the difference between how MySQL and PostgreSQL treat "schemas". We will then show how the existing schema inspect and schema apply commands work with multi-schema support, and wrap up with some plans for future releases.

What is a database schema?​

Within the context of relational (SQL) databases, a database schema is a logical unit within a physical database instance (server/cluster) that forms a namespace of sorts. Inside each schema you can describe the structure of the tables, relations, indexes and other attributes that belong to it. In other words, the database schema is a "blueprint" of the data structure inside a logical container (Note: in Oracle databases a schema is linked to the user, so it carries a different meaning which is out of scope for this post). As you can guess from the title of this post, many popular relational databases allow users to host multiple (logical) schemas on the same (physical) database.

Where are database schemas used in practice?​

Why is this level of logical division necessary? Isn't it enough to be able physically split data into different database instances? In my career, I've seen multiple scenarios in which organizations opt to split a database into multiple schemas.

First, grouping different parts of your application into logical units makes it simpler to reason about and govern. For instance, it is possible to create multiple user accounts in our database and give each of them permission to access a subset of the schemas in the database. This way, each user can only touch the parts of the database they need, preventing the practice of creating an almighty super-user account that has no permission boundary.

An additional pattern I've seen used, is in applications with a multi-tenant architecture where each tenant has its own schema with the same exact table structure (or some might have a different structure since they use different versions of the application). This pattern is used to create a stronger boundary between the different tenants (customers) preventing the scenario where one tenant accidentally has access to another's data that is incidentally hosted on the same machine.

Another useful feature of schemas is the ability to divide the same server into different environments for different development states. For example, you can have a "dev" and "staging" schema inside the same server.

What are the differences between schemas in MySQL and PostgreSQL?​

A common source of confusion for developers (especially when switching teams or companies) is the difference between the meaning of schemas in MySQL and PostgreSQL. Both are currently supported by Atlas, and have some differences that should be clarified.

Looking at the MySQL glossary, it states:

"In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE"

As we can see, MySQL doesn't distinguish between schemas and databases in the terminology, but the underlying meaning is still the same - a logical boundary for resources and permissions.

To demonstrate this, open your favorite MySQL shell and run:

mysql> create schema atlas;
Query OK, 1 row affected (0.00 sec)

To create a table in our new schema, assuming we have the required permissions, we can switch to the context of the schema that we just created, and create a table:

USE atlas;
CREATE table some_name (
    id int not null
);

Alternatively, we can prefix the schema, by running:

CREATE TABLE atlas.cli_versions
(
    id      bigint auto_increment primary key,
    version varchar(255) not null
);

This prefix is important since, as we said, schemas are logical boundaries (unlike database servers). Therefore, we can create references between them using foreign keys from tables in SchemaA to SchemaB. Let's demonstrate this by creating another schema with a table and connect it to a table in the atlas schema:

CREATE SCHEMA atlantis;

CREATE TABLE atlantis.ui_versions
(
    id               bigint auto_increment
        primary key,
    version          varchar(255) not null,
    atlas_version_id bigint       null,
    constraint ui_versions_atlas_version_uindex
        unique (atlas_version_id)
);

Now let's link atlantis to atlas:

alter table atlantis.ui_versions
    add constraint ui_versions_cli_versions_id_fk
        foreign key (atlas_version_id) references atlas.cli_versions (id)
            on delete cascade;

That's it! We've created 2 tables in 2 different schemas with a reference between them.

How does PostgreSQL treat schemas?​

When booting a fresh PostgreSQL server, we get a default logical schema called "public". If you wish to split your database into logical units as we've shown with MySQL, you can create a new schema:

CREATE SCHEMA atlas;

Contrary to MySQL, Postgres provides an additional level of abstraction: databases. In Postgres, a single physical server can host multiple databases. Unlike schemas (which are basically the same as in MySQL) - you can't reference a table from one PostgreSQL database to another.

In Postgres, the following statement will create an entirely new database, where we can place different schemas and tables with that may contain references between them:

create database releases;

When we run this statement, the database will be created with the default Postgres metadata tables and the default public schema.

In Postgres, you can give permissions to an entire database(s), schema(s), and/or table(s), and of course other objects in the Postgres schema.

Another distinction from MySQL is that in addition to sufficient permissions, a user must have the schema name inside their search_path in order to use it without a prefix.

To sum up, both MySQL and Postgres allow the creation of separate logical schemas within a physical database server, schemas can refer to one another via foreign-keys. PostgreSQL supports an additional level of separation by allowing users to create completely different databases on the server.

Atlas multi-schema support​

As we have shown, having multiple schemas in the same database is a common scenario with popular relational databases. Previously, the Atlas CLI only supported inspecting or applying changes to a single schema (even though this has been long supported in the Go API). With this release, we have added support for inspecting and applying multiple schemas with a single .hcl file.

Next, let's demonstrate how we can use the Atlas CLI to inspect and manage a database with multiple schemas.

Start by downloading and installing the latest version of the CLI. For the purpose of this demo, we will start with a fresh database of MySQL running in a local docker container:

docker run --name atlas-db  -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pass -e MYSQL_DATABASE=example mysql:8

By passing example in the MYSQL_DATABASE environment variable a new schema named "example" is created. Let's verify this by using the atlas schema inspect command. In previous versions of Atlas, users had to specify the schema name as part of the DSN for connecting to the database, for example:

atlas schema inspect -u "mysql://root:pass@localhost:3306/example"

Starting with v0.3.2, users can omit the schema name from the DSN to instruct Atlas to inspect the entire database. Let's try this:

$ atlas schema inspect -u "mysql://root:pass@localhost:3306/" > atlas.hcl
cat atlas.hcl
schema "example" {
  charset   = "utf8mb4"
  collation = "utf8mb4_0900_ai_ci"
}

Let's verify that this works correctly by editing the atlas.hcl that we have created above and adding a new schema:

schema "example" {
  charset   = "utf8mb4"
  collation = "utf8mb4_0900_ai_ci"
}
schema "example_2" {
  charset   = "utf8mb4"
  collation = "utf8mb4_0900_ai_ci"
}

Next, we will use the schema apply command to apply our changes to the database:

atlas schema apply -u "mysql://root:pass@localhost:3306/" -f atlas.hcl

Atlas plans a migration to add the new DATABASE (recall that in MySQL DATABASE and SCHEMA are synonymous) to the server, when prompted to approve the migration we choose "Apply":

-- Planned Changes:
-- Add new schema named "example_2"
CREATE DATABASE `example_2`
✔ Apply

To verify that schema inspect works properly with multiple schemas, lets re-run:

atlas schema inspect -u "mysql://root:pass@localhost:3306/"

Observe that both schemas are inspected:

schema "example" {
  charset   = "utf8mb4"
  collation = "utf8mb4_0900_ai_ci"
}
schema "example_2" {
  charset   = "utf8mb4"
  collation = "utf8mb4_0900_ai_ci"
}

To learn more about the different options for working with multiple schemas in inspect and apply commands, consult the CLI Reference Docs.

What's next for multi-schema support?​

I hope you agree that multi-schema support is a great improvement to the Atlas CLI, but there is more to come in this area. In our previous blogpost we have shared that Atlas also has a Management UI (-w option in the CLI) and multi-schema support is not present there yet - stay tuned for updates on multi-schema support for the UI in an upcoming release!

Getting involved with Atlas​

• Follow the Getting Started guide..
• Join our Discord Server.
• Follow us on Twitter.

Earlier this week we released v0.3.0 of the Atlas CLI. This version features a ton of improvements to database inspection, diffing and migration planning. You can read about those in the release notes page, but we wanted to take the time and introduce the biggest feature in this release, the Atlas Management UI.

To recap, Atlas is an open source CLI tool that helps developers manage their database schemas. Contrary to existing tools, Atlas intelligently plans schema migrations for you, based on your desired state. Atlas currently has two main commands: inspect and apply. The inspect command inspects your database, generating an Atlas HCL document. The apply command allows you to migrate your schema from its current state in the database to your desired state by providing an HCL file with the relevant schema.

In this post we will showcase the latest addition to the CLI's feature set, the Management UI. Until now, you could use Atlas to manage your schemas via your terminal. While this is the common interface for many infrastructure management workflows, we believe that a visual, integrated environment can be beneficial in many use-cases.

Inspecting our database using the Atlas UI​

Let's see how we can use the Atlas UI to inspect our database.

For the purpose of demonstration let's assume that you have a locally running MySQL database. If you want to follow along, check out the Setting Up tutorial on the Atlas website for instructions on starting up a MySQL database locally using Docker.

We will be working with a MySQL database that has the following tables:

CREATE table users (
    id int PRIMARY KEY,
    name varchar(100)
);
CREATE TABLE blog_posts (
    id int PRIMARY KEY,
    title varchar(100),
    body text,
    author_id int,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

To inspect the database, we can use the atlas schema inspect command. Starting with this version, we can add the -w flag to open the (local) web UI:

atlas schema inspect -u "mysql://root:pass@localhost:3306/example" -w

Our browser will open automatically, and we should see this output in the CLI:

Atlas UI available at: http://127.0.0.1:5800/projects/25769803777/schemas/1
Press Ctrl+C to stop

We can see that our schema has been inspected, and that it's currently synced. On the bottom-left part of the screen the UI displays an ERD (Entity-relation Diagram) showing the different tables and the connections between them (via foreign-keys). On the bottom-right, we can see the current schema, described using the Atlas DDL. In addition, on the top-right, we see the "Activity & History" panel that holds an audit history for all changes to our schema.

Migrating our database schema with the Atlas Management UI​

Visualizing the current schema of the database is great, let's now see how we can use the UI to initiate a change (migration) to our schema.

Click on the Edit Schema button in the top-right corner and add the following two tables to our schema:

table "categories" {
  schema = schema.example
  column "id" {
    null = false
    type = int
  }
  column "name" {
    null = true
    type = varchar(100)
  }
  primary_key {
    columns = [table.categories.column.id, ]
  }
}

table "post_categories" {
    schema = schema.example
    column "post_id" {
        type = int
    }
    column "category_id" {
        type = int
    }
    foreign_key "post_category_post" {
        columns     = [table.post_categories.column.post_id, ]
        ref_columns = [table.blog_posts.column.id, ]
    }
    foreign_key "post_category_category" {
        columns     = [table.post_categories.column.category_id, ]
        ref_columns = [table.categories.column.id, ]
    }
}

Click the Save button and go back to the schema page. Observe that a few things changed on the screen:

First, we can see that the UI states that our schema is "Out of Sync". This is because there is a difference between our desired schema, the one we are currently working on, and the inspected schema, which is the actual, current schema of our database.

Second, we can see that our ERD has changed reflecting the addition of the categories and post_categories tables to our schema. These two tables that have been added are now shown in green. By clicking the "expand" icon on the top-right corner of the ERD panel, we can open a more detailed view of our schema.

Going back to our schema page, click the "Migrate Schema" to initiate a migration to apply the changes we want to make to our schema. Next, Atlas will setup the migration. Click "Plan Migration" to see the migration plan to get to the desired schema:

Atlas displays the diff in the schema in HCL on the left pane, and the planned SQL statements on the right. Click "Apply Migration" to begin executing the plan.

In the final screen of the migration flow, Atlas displays informative logs about the migration process. In this case, our migration completed successfully! Let's click "Done" to return to the schema detail page.

As expected, after executing our migration plan, our database and desired schema are now synced!

Wrapping Up​

In this post, we've introduced the Atlas Management UI and showed one of the possible workflows that are supported in it. There's much more inside, and we invite you to install it today and give it a try.

What next?​

• Follow the Getting Started guide.
• Join our Discord Server.
• Follow us on Twitter.

At Ariga, we are building a new kind of platform that we call an Operational Data Graph. This platform enables software engineers to manage, maintain and access complex data architectures as if they were one database. Today, we are open-sourcing a CLI for Atlas, one of the fundamental building blocks of our platform.

During my career, the scope of what is expected of me as a software engineer has increased significantly. Developers are no longer expected just to write code, we are expected to provision infrastructure, manage databases, define deployments and monitor systems in production.

Nowadays, one of the responsibilities we have as software engineers is to manage the database schema of our applications. Once seen as falling strictly under the domain of DBAs, today developers everywhere are responsible for defining database schemas and changing them over time. Because an application's database carries its state, all clients and servers are severely impacted if it stops functioning properly. Therefore, over the years many techniques and tools were developed to deal with this process, which is called migrating the database.

In the last few years we have seen a lot of progress in the field of tools for provisioning infrastructure. From early projects such as Chef and Puppet, to more recent work such as Terraform, a lot of thought and effort has been put across the industry to build tools that simplify and standardize the process. Instead of manually installing and configuring software and services, the common thread between all of these projects is that they are based on machine-readable definition files, a concept also known as infrastructure-as-code (IaC).

Enter: Atlas​

Atlas is at the core of Ariga's platform. In this post, I would like to share with you the work we've done so far to provide a solid foundation for managing databases in a way that's akin to infrastructure-as-code practices.

• The Atlas DDL (Data-definition Language): we have created the Atlas DDL, a new configuration language designed to capture an organization's data topology - including relational database schemas. This language is currently described in an HCL syntax (similar to TerraForm), but will support more syntaxes such as JSON and TypeScript in the future. The Atlas DDL currently supports defining schemas for SQL databases such as MySQL, Postgres, SQLite and MariaDB, but in the future, we plan to add support for other types of databases. For example:

table "users" {
  schema = "default"
  column "id" {
    type = "int"
  }
  column "name" {
    type = "string"
  }
  column "manager_id" {
    type = "int"
  }
  primary_key {
    columns = [
        table.users.column.id
    ]
  }
  index "idx_name" {
    columns = [
      table.users.column.name
    ]
    unique = true
  }
  foreign_key "manager_fk" {
    columns = [table.users.column.manager_id]
    ref_columns = [table.users.column.id]
    on_delete = "CASCADE"
    on_update = "NO ACTION"
  }
}

• The Atlas CLI On top of the building blocks provided by the DDL, we started building our CLI tool to support the two most basic functions:

  • "Schema Inspect" - Create a schema specification file from a database.
  • "Schema Apply" - Migrate a database to a new desired state.

Many infrastructure-as-code projects have taken the declarative approach, in which the developer articulates the desired state of the system and the tool is responsible for figuring out a plan to get there. As we discussed above, changing database schemas safely is a delicate practice, so we had to build the Atlas CLI to be smart enough to understand the nuance of changes for each type of database.

Atlas in action​

Let's see how Atlas CLI works with real databases. Let's start a MySQL container:

docker run --name atlas-db  -p 3306:3306  -e MYSQL_ROOT_PASSWORD=pass -e MYSQL_DATABASE=example   mysql:8.0.27

Connect to our database using a native client to validate:

docker  exec -it  atlas-db  mysql --password='pass' example

mysql> show tables;
Empty set (0.00 sec)

mysql>

Let's see how Atlas inspects it:

atlas schema inspect -u "mysql://root:pass@localhost:3306/example" > atlas.hcl

As expected, an empty schema:

# cat atlas.hcl
schema "example" {
}

Let's update our schema to:

# cat atlas.hcl
table "users" {
  schema = "example"
  column "id" {
    null = false
    type = "int"
  }
  column "name" {
    null = false
    type = "string"
    size = 255
  }
  column "manager_id" {
    null = false
    type = "int"
  }
  primary_key {
    columns = [table.users.column.id, ]
  }
  foreign_key "manager_fk" {
    columns     = [table.users.column.manager_id, ]
    ref_columns = [table.users.column.id, ]
    on_update   = "NO ACTION"
    on_delete   = "CASCADE"
  }
  index "idx_name" {
    unique  = true
    columns = [table.users.column.name, ]
  }
  index "manager_fk" {
    unique  = false
    columns = [table.users.column.manager_id, ]
  }
}
schema "example" {
}

And apply our changes!

atlas schema apply -u "mysql://root:pass@localhost:3306/example" -f atlas.hcl



-- Planned Changes:
-- Add Table : users
CREATE TABLE `example`.`users` (`id` int NOT NULL, `name` varchar(255) NOT NULL, `manager_id` int NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `idx_name` (`name`), CONSTRAINT `manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `example`.`users` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE) ;
Use the arrow keys to navigate: ↓ ↑ → ←
? Are you sure?:
  ▸ Apply
    Abort

Of course we are sure !

Using CLI to examine our database:

mysql> describe users;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | NO   | PRI | NULL    |       |
| name       | varchar(255) | NO   | UNI | NULL    |       |
| manager_id | int          | NO   | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

Let's make sure that it has the FK:

mysql> show create table users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `manager_id` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`),
  KEY `manager_fk` (`manager_id`),
  CONSTRAINT `manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Now let's see that Atlas inspects this correctly:

atlas schema inspect -u "mysql://root:pass@localhost:3306/example" > atlas.hcl

# cat atlas.hcl
table "users" {
  schema = "example"
column "id" {
    null = false
    type = "int"
  }
  column "name" {
    null = false
    type = "string"
    size = 255
  }
  column "manager_id" {
    null = false
    type = "int"
  }
  primary_key {
    columns = [table.users.column.id, ]
  }
  foreign_key "manager_fk" {
    columns     = [table.users.column.manager_id, ]
    ref_columns = [table.users.column.id, ]
    on_update   = "NO ACTION"
    on_delete   = "CASCADE"
  }
  index "idx_name" {
    unique  = true
    columns = [table.users.column.name, ]
  }
  index "manager_fk" {
    unique  = false
    columns = [table.users.column.manager_id, ]
  }
}
schema "example" {
}

Let's see what happens when we try to reapply the same change:

atlas schema apply -u "mysql://root:pass@localhost:3306/example" -f atlas.hcl
Schema is synced, no changes to be made

In this example we have shown how we can inspect a MySQL database schema and apply a change.

What's Next?​

The Atlas DDL opens up a world of tools and services, and with the help of our community, we are planning to push the development ecosystem forward. A list of tools that are on our road map includes:

• Integrations with Terraform, GitHub actions and Kubernetes.
• Extended migration logics such as renaming columns, adding or dropping nullability and altering enums.
• Toolsets for examining the migration history and reproducing it.

We hope that you find Atlas CLI as exciting as we do, and we invite you to contribute your ideas and code.