Skip to main content

2 posts tagged with "migration"

View All Tags

ยท 6 min read
Ariel Mashraki

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

Schema as Code (SaC)โ€‹

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

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

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

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

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

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

atlas schema apply \
--url "mysql://root:pass@localhost:3306/example" \
--to "file://schema.sql" \
--dev-url "docker://mysql/8/example"
FLAGS
  • --url - the database URL to apply the schema to.
  • --to - URLs describe the desired state: SQL or HCL schema definition, or a database URL.
  • --dev-url - a URL to a Dev Database that will be used to compute the diff.

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

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

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

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

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

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

Now let's make our schema more interesting by adding a column to the users table and creating a blog_posts table with a foreign key that references users:

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

-- create table "blog_posts"
CREATE TABLE blog_posts(
id int NOT NULL,
title varchar(100) NULL,
body text NULL,
author_id int NULL,
PRIMARY KEY(id),
CONSTRAINT author_fk FOREIGN KEY(author_id) REFERENCES users(id)
);

Next, executing atlas schema apply again will update the database schema with the following changes:

atlas schema apply
-- Planned Changes:
-- Add column "email" to table: "users"
ALTER TABLE `users` ADD COLUMN `email` varchar NULL;
-- Create "blog_posts" table
CREATE TABLE `blog_posts` (`title` varchar NULL, `body` text NULL, `author_id` int NULL, `id` int NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `author_fk` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION);

Boom! Atlas automatically calculates the difference between the current state of our database and the desired state defined by our schema file, and generates the necessary changes to migrate the database to the new state. We don't need to specify each individual migration โ€“ we simply tell Atlas what state we want the database to be in, and it handles the rest.

To see a full description of this generated migration plan, check out this diagram example in Atlas public playground:

Diff ERDโ€‹

Diff SQLโ€‹

Atlas Playgroundโ€‹

As part of this version, we have released the Atlas playground where users can visualize their database schemas in an interactive way. Simply provide an SQL or HCL schema, or import one from an existing database, and in return get an ERD visualizing their entire data model.

Users can also compare between two schemas with the Schema Diff button, and get the SQL statements necessary to migrate from one schema to the other - give it try!

Blog ERD

A big thanks to @solomonme, @ronenlu and @masseelch for contributing this feature to Atlas!

Schema Loadersโ€‹

What's next? In the near future, we plan to add an infrastructure for loading schemas from external sources. This will enable ORM maintainers to integrate with Atlas and provide their schema definitions as Atlas schemas. As as result, they can utilize the Atlas engine to diff schemas, plan and lint migrations, execute them on the databases, and more.

The first ORM to integrate with Atlas will be Ent. Using this integration, Ent users will be able to generate Atlas schemas or migrations for their Ent projects with a single command:

atlas migrate diff create_users \
--dir "file://migrations" \
--to "ent://path/to/schema" \
--dev-url "docker://<driver-name>"

Would you like to see other ORMs integrated with Atlas? Please, join our Discord server and let me know.

What next?โ€‹

Have questions or feedback? Feel free to reach out on our Discord server.

ยท 7 min read

Most software projects are backed by a database, that's widely accepted. The schema for this database almost always evolves over time: requirements change, features are added, and so the application's model of the world must evolve. When this model evolves, the database's schema must change as well. No one wants to (or should) connect to their production database and apply changes manually, which is why we need tools to manage schema changes. Most ORMs have basic support, but eventually projects tend to outgrow them. This is when projects reach to choose a schema migration tool.

Many such tools exist, and it's hard to know which to choose. My goal in this article is to present 3 popular choices for migration tools for Go projects to help you make this decision.

By way of introduction (and full disclosure): my name is Pedro Henrique, I'm a software engineer from Brazil, and I've been a contributing member of the Ent/Atlas community for quite a while. I really love open-source and think there's room for a diverse range of tools in our ecosystem, so I will do my best to provide you with an accurate, respectful, and fair comparison of the tools.

golang-migrate - Created: 2014 Github Stars: 10.3k
Golang migrate is one of the most famous tools for handling database migrations. Golang migrate has support for many database drivers and migration sources, it takes a simple and direct approach for handling database migrations.

Goose - Created: 2012 Github Stars: 3.2k
Goose is a solid option when choosing a migration tool. Goose has support for the main database drivers and one of its main features is support for migrations written in Go and more control of the migrations application process.

Atlas - Created: 2021 Github Stars: 2.1k
Atlas is an open-source schema migration tool that supports a declarative workflow to schema migrations, making it a kind of "Terraform for databases". With Atlas, users can declare their desired schema and let Atlas automatically plan the migrations for them. In addition, Atlas supports classic versioned migration workflows, migration linting, and has a GitHub Actions integration.

Golang migrateโ€‹

Golang migrate was initially created by Matt Kadenbach. In 2018 the project was handed over to Dale Hui, and today the project resides on the golang-migrate organization and is actively maintained, having 202 contributors.

One of Golang migrate's main strengths is the support for various database drivers. If your project uses a database driver that is not very popular, chances are that Golang migrate has a driver for it. For cases where your database is not supported, Golang migrate has a simple API for defining new database drivers. Databases supported by Golang migrate include: PostgreSQL, Redshift, Ql, Cassandra, SQLite, MySQL/MariaDB, Neo4j, MongoDB, Google Cloud Spanner, and more.

Another feature of Golang migrate is the support for different migrations sources, for cases where your migration scripts resides on custom locations or even remote servers.

Gooseโ€‹

Goose has a similar approach to Golang migrate. The project was initially created by Liam Staskawicz in 2012, and in 2016 Pressly created a fork improving the usage by adding support for migrations in Go, handling cases of migrations out of order and custom schemas for migration versioning. Today Goose has 80 contributors.

Goose only provides support for 7 database drivers, so if your project uses one of the main databases in the market, Goose should be a good fit. For migration sources, Goose allows only the filesystem, it's worth pointing out that with Go embed it is possible to embed the migration files on a custom binary. Goose's main difference from Golang migrate is the support for migrations written in Go, for cases where it is necessary to query the database during the migration. Goose allows for different types of migration versioning schemas, improving one key issue with Golang migrate.

Atlasโ€‹

Atlas takes a completely different approach to Golang migrate or Goose. While both tools only focus on proving means of running and maintaining the migration directory, Atlas takes one step further and actually constructs a graph representing the different database entities from the migration directory contents, allowing for more complex scenarios and providing safety for migration operations.

Migrations in Atlas can be defined in two ways:

  • Versioned migrations are the classical style, where the migration contents are written by the developer using the database language.
  • Declarative migrations are more similar to Infrastructure-as-Code, where the schema is defined in a Terraform-like language and the migrations commands are calculated based on the current and desired state of the database. It's possible to use Atlas in a hybrid way as well, combining both styles, called Versioned Migration Authoring where the schema is defined in the Atlas language, but the Atlas engine is used to generate versioned migrations.

On top of Atlas's ability to load the migration directory as a graph of database entities, an entire infrastructure of static code analysis was built to provide warnings about dangerous or inefficient operations. This technique is called migration linting and can be integrated with the Atlas Github action during CI.

In addition, if you would like to run your migrations using Terraform, Atlas has a Terraform provider as well.

Another key point that Atlas solves is handling migration integrity, which becomes a huge problem when working with multiple branches that all make schema changes. Atlas solves this problem by using an Integrity file. While we are on the topic of integrity, one key feature of Atlas is the support for running the migrations inside a transaction, unlike Goose during the process of migration. Atlas acquires a lock ensuring that only one migration happens at a time and the migration order/integrity is respected. For cases where problems are found, Atlas makes the troubleshooting process easier, allowing schema inspections, dry runs and providing helpful links to the common problems and solutions.

Feature comparisonโ€‹

FeatureGolang migrateGooseAtlas
Drivers supportedMain SQL and NoSQL databasesMain SQL databasesMain SQL databases
Migration sourcesLocal and remote SQL filesSQL and Go filesHCL and SQL files
Migrations typeVersionedVersionedVersioned and Declarative
Support for migrations in GoNoYesYes
Integrity checksNoNoYes
Migration out of orderNoPossible with hybrid versioningPossible calculating the directory hash
Lock supportYesNoYes
Use as CLIYesYesYes
Use as packageYesYesPartial support ยน
Versioned Migration AuthoringNoNoYes
Migration lintingNoNoYes
Github actionNoNoYes
Terraform providerNoNoYes
  • 1: Atlas provides a few packages related to database operations, but the use is limited to complex cases and there is no package that provides migration usage out of the box.

Wrapping upโ€‹

In this post we saw different strengths of each migration tool. We saw how Golang migrate has a great variety of database drivers and database sources, how Goose allows use to written migration in Go for the complexes migration scenarios and how Atlas makes the migration a complete different business, improving the safety of the migration operations and bringing concepts from others fields.