Skip to main content

· 12 min read
Rotem Tamir

Adapted from a talk given at Kube Native 2024:

.

Introduction

Database schema migrations are a critical part of the software development lifecycle. They allow us to evolve our data model as our application grows and changes. However, migrations can also be a significant source of risk and downtime if not handled carefully.

Despite teams applying all the common best practices, such as using a migration tool to automate changes and carefully examining each migration before approving it during code review, issues still slip through the cracks, making migrations a source of significant headaches for many engineering teams.

This post explores five strategies for making database schema migrations more reliable by introducing Atlas, a database schema-as-code tool that helps teams make their database schema changes both safer and easier to manage.

Strategies for Reliable Schema Migrations with atlas

Prerequisite: Automate

Having interviewed over a hundred engineering teams about their database migration practices, we found that a surprisingly large number of teams perform database migrations manually. This involves running SQL statements via scripts or a point-and-click interface directly against the database, often without proper testing or version control.

Manual migrations are error-prone and difficult to reproduce, leading to inconsistencies between environments and increased risk of downtime. Additionally, manual changes to the database are amongst the most stressful and dreaded tasks for developers, as they can lead to nasty outages if not done correctly.

Much has been written about the importance of automating database migrations, but it's worth reiterating here. Even if you do nothing else, please automate your schema changes!

Strategy 1: Schema-as-Code

Classic database migration tools like Flyway or Liquibase are great for automating the process of executing SQL scripts against a database. However, despite being categorized as "automation tools," they still require plenty of manual work to write, test and review the SQL scripts that define the schema changes.

Atlas takes a different approach by treating the database schema as code. Instead of manually defining the schema changes in SQL scripts, developers define the desired state of the database schema in a declarative format using code and let the tool handle the rest.

For instance, a developer may define the following schema in Atlas:

schema.sql
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);

Next, to apply this schema to a database, the developer runs the following command:

atlas schema apply --env local --to file://schema.sql

Atlas will connect to the database, inspect its current schema, calculate the diff between the current and desired state, and propose a migration plan to bring the database to the desired state. The developer can then review and approve the migration before applying it.

By automating the process of defining and applying schema changes, Atlas makes it easier to manage database migrations and reduces the risk of human error.

Strategy 2: Test DB logic like any other code

Modern databases are a lot more than just containers for data. They also embody complex business logic in the form of constraints, triggers, stored procedures, and functions.

To make sure that these database objects work as expected, and keep doing so after changes are made, it's important to test them like any other code.

Atlas provides a testing framework that allows developers to write tests with a simple syntax. For example, to test a function that returns true if the input is positive and false otherwise, a developer might write the following test:

schema.test.hcl
test "schema" "positive_func" {
parallel = true
assert {
sql = "SELECT positive(1)"
}
log {
message = "First assertion passed"
}
assert {
sql = <<SQL
SELECT NOT positive(0);
SELECT NOT positive(-1);
SQL
}
log {
message = "Second assertion passed"
}
}

By treating database objects as code and writing tests for them, developers can catch make sure that their database code works reliably and consistently and prevent regressions when making changes.

Strategy 3: Test data migrations

Most commonly, migrations deal with schema changes, such as adding or removing columns, creating tables, or altering constraints. However, as your application evolves, you may need to add or refactor data within the database, which is where data migrations come in. For instance, you may need to seed data in a table, backfill data for existing records in new columns, or somehow transform existing data to accommodate changes in your application.

Data migrations can be especially tricky to get right, and mistakes can be problematic and irreversible. For this reason testing data migrations is crucial. Testing data migrations typically involves the following steps:

  1. Setting up an empty database.
  2. Applying migrations up to the one before the test.
  3. Seeding test data.
  4. Running the migration under test.
  5. Making assertions to verify the results.

This process can be cumbersome to set up and buggy as it often involves writing an ad-hoc program to automate the steps mentioned above or manually testing the migration.

Atlas's migrate test command simplifies this by allowing you to define test cases in a concise syntax and acts as a harness to run these tests during local development and in CI.

Using Atlas, developers can write tests for data migrations in a simple format, making it easier to catch issues early and ensure that data migrations work as expected. For example, to test a data migration that backfills a new column with values from an existing column, a developer might write the following test:

migrate.test.hcl
test "migrate" "check_latest_post" {
migrate {
to = "20240807192632"
}
exec {
sql = <<-SQL
INSERT INTO users (id, email) VALUES (1, 'user1@example.com'), (2, 'user2@example.com');
INSERT INTO posts (id, title, created_at, user_id) VALUES (1, 'My First Post', '2024-01-23 00:51:54', 1), (2, 'Another Interesting Post', '2024-02-24 02:14:09', 2);
SQL
}
migrate {
to = "20240807192934"
}
exec {
sql = "select * from users"
format = table
output = <<TAB
id | email | latest_post_ts
----+-------------------+---------------------
1 | user1@example.com | 2024-01-23 00:51:54
2 | user2@example.com | 2024-02-24 02:14:09
TAB
}
log {
message = "Data migrated successfully"
}
}

In this test, the developer migrates the database to a specific version, seeds test data, runs the migration under test, and verifies the results. By automating this process, Atlas makes it easier to test data migrations and catch issues early.

Strategy 4: Automate Safety Checks

Even with the best intentions, mistakes can still happen during the development and review of migrations leading to downtime and data loss. To mitigate this risk, it's important to automate safety checks that catch common mistakes before applying the migration to the database.

Before we dive into strategies for reliable migrations, let's take a look at some common ways migrations can go wrong.

Destructive Changes

mysql> select * from dropped;

ERROR 1146 (42S02): Table 'default.dropped' doesn't exist

Migrations often involve DROP DDL statements that can lead to data loss if executed against a table or column that still contains valuable data. Unfortunately, modern databases do not have a built-in undo button, so once a destructive change is applied, it can be challenging (if not impossible) to recover the lost data.

This might sound like an obvious mistake to avoid, but it's surprisingly common in practice. For example, consider this published incident report from Resend, which states:

On February 21st, 2024, Resend experienced an outage that affected all users due to a database migration that went wrong. This prevented users from using the API (including sending emails) and accessing the dashboard from 05:01 to 17:05 UTC (about 12 hours).

The database migration accidentally deleted data from production servers. We immediately began the restoration process from a backup, which completed 6 hours later.

Constraint Violations

mysql> alter table candy ADD UNIQUE (name);

ERROR 1062 (23000): Duplicate entry 'm&m' for key 'candy.name'

Migrations that involve adding or modifying constraints can fail if the existing data does not meet the new constraints. For example, adding a NOT NULL constraint to a column that already contains NULL values will cause the migration to fail.

What makes this even more confusing is that such migrations will often succeed in a development or testing environment where the data is different from production. This can lead to a false sense of confidence that the migration is safe to apply in production.

Breaking Changes

mysql> select id, renamed_column from candy limit 1;

ERROR 1054 (42S22): Unknown column 'renamed_column' in 'field list'

A popular topic amongst data teams today is "data contracts" - the (sometimes implicit) agreement between the applications and their downstream consumers about the structure and semantics of the data. This is often mentioned in the context of data engineering teams building data pipelines, but the same concerns apply to the interface between your application backend and the database.

When a migration changes the structure of the database, it can break the contract between the application and the database, leading to runtime errors and potentially to data corruption. For example, renaming a column that is used by the application will cause queries to fail, leading to downtime and unhappy users.

Table locks

mysql> INSERT INTO candy (name, kind) VALUES ('kif-kef', 'chocolate');

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Migrations that involve large tables or complex operations can cause table locks that prevent other queries from executing. This can lead to timeouts, deadlocks, and other performance issues that affect the availability of the application.

For instance, suppose your MySQL table has an enum column with a million rows, and you want to add a new value to the enum. If you add the new value, not at the end of the enum, but in the middle (for example from ('a', 'b', 'c') to ('a', 'b', 'd', 'c')), MySQL will lock the table for the duration of the migration, while it is rewriting the column on disk.

Preventing Risky Migrations

To prevent these common mistakes, Atlas provides a set of safety checks that run automatically before applying a migration. These checks analyze the migration and the database schema to identify potential issues and warn the developer before applying the migration.

Such checks can be run either locally during development or in CI before approving the migration and merging it into the main branch. By catching issues early, these safety checks help prevent downtime and data loss caused by risky migrations.

For instance, Atlas might warn the developer about a potentially destructive change like dropping a table:

atlas migrate lint --env local --latest 1

Might produce the following output:

Analyzing changes from version 20240929125035 to 20240929125127 (1 migration in total):

-- analyzing version 20240929125127
-- destructive changes detected:
-- L2: Dropping non-virtual column "email"
https://atlasgo.io/lint/analyzers#DS103
-- suggested fix:
-> Add a pre-migration check to ensure column "email" is NULL before dropping it
-- ok (225.916µs)

-------------------------
-- 98.996916ms
-- 1 version with warnings
-- 1 schema change
-- 1 diagnostic

Being able to identify and fix these issues while working locally can save a lot of time and headaches down the road, but this is further amplified when running these checks in CI, where teams can ensure that no risky migrations are merged into the main branch.

Strategy 5: Pre-migration checks

As we mentioned above, safety checks can catch common mistakes before applying a migration, but they are not foolproof. Some changes depend on the state of the data in the database, which cannot be determined statically by analyzing the migration script.

For example, consider a migration that adds a NOT NULL constraint to a column that already contains NULL values. A safety check can warn the developer about this potential issue, but it cannot guarantee that the migration will succeed in all cases. Similarly, a migration that drops a column might be safe and reversible if the column is empty, but risky if it contains valuable data.

To handle these cases, Atlas provides a mechanism for defining pre-migration checks that run before applying the migration. These checks can analyze the state of the database and the data to determine if the migration is safe to apply. In case of an issue, the check can prevent the migration from running and provide guidance on how to resolve the issue.

For instance, the code below defines a pre-migration check that ensures the table users is empty before dropping it:

20240201131900_drop_users.sql
-- atlas:txtar

-- checks.sql --
-- The assertion below must be evaluated to true. Hence, table users must be empty.
SELECT NOT EXISTS(SELECT * FROM users);

-- migration.sql --
-- The statement below will be executed only if the assertion above is evaluated to true.
DROP TABLE users;

Summary

Database schema migrations are a critical part of the software development lifecycle, but they can also be a significant source of risk and downtime if not handled carefully. By following the strategies outlined in this post and using tools like Atlas, teams can make their database schema migrations more reliable and reduce the risk of downtime and data loss.

To summarize, here are the five strategies for reliable schema migrations:

StrategyImpact
Schema-as-codeAutomated migration planning
Test schema logicPrevent regressions in DB logic
Test data migrationsPrevent data corruption
Automated quality checksCatch risky changes in Dev/CI
Pre-migration checksVerify data dependent changes

We hope you enjoyed this post and found it useful. As always, we welcome your feedback and suggestions on our Discord server.

· 9 min read
Ariel Mashraki

Hello everyone,

Today, we're excited to release the new schema plan command, which many of you have been eagerly awaiting.

Taking the declarative workflow to the next level, the schema plan command lets you review, analyze and even edit declarative migration plans at pull-request stage, making schema apply much safer and predictable. Additionally, several new features have been added to Atlas in this release, and we'll cover them in this blog post as well.

What is Atlas?

For those visiting us for the first time, Atlas is a language-agnostic tool for managing and migrating database schemas using modern DevOps principles. Users define their desired database schema state declaratively, and Atlas handles the rest. The "state" can be defined using SQL, HCL (Atlas flavor), your preferred ORM, another database, or a combination of all. To get started, visit the getting-started doc.

Why schema plan?

Since the first release, Atlas supports declarative migrations. Using the schema apply command, users provide the desired schema, and a URL (connection string) to the target database, and Atlas computes the migration plan, and applies it to the database after the user approves it. This workflow is very similar to Terraform, but for databases schemas.

Although the declarative workflow feels magical, and works well for most cases, it had some inherent limitations:

  1. Since changes are computed at runtime, reviews also happen at runtime, either by policy (explained below) or manually. This creates a less predictable and streamlined deployment process compared to applications development, where code reviews occur during the pull request (PR) stage. Since Atlas promotes the "Schema as Code" approach, we aim to bring the same experience to database schema changes.
  2. Another limitation of this workflow is that users can define the desired state but have no control on the exact steps to reach it. Although Atlas provides a set of diff policies to fine-tune migration planning, users sometimes need more control over how the migrations are applied.
  3. Data changes, like back-filling columns with custom UPDATE statements, are difficult to express declaratively.

Fortunately, since Atlas provides also a versioned workflow, companies faced these limitations have been able to fall back to it. While versioned migration has its own limitations (like history linearity), it still works well for most cases. Combined with Atlas's automatic migration planning, the overall experience is closely to the declarative migration, but not the same.

We believe that declarative migration is the future for most cases. It lets engineers focus on feature development, not migrations. Additionally, this workflow allows schema transitions between any states, generating the most efficient plan, unlike versioned migration, which relies on a linear history of changes.

We address these limitations by introducing the schema plan command. Let's dive in.

What is schema plan?

The atlas schema plan command allows users to pre-plan, review, and approve declarative migrations before executing them on the database. It lets users modify the SQL migration plan (if necessary), involve team members in the review, and ensure the approval is done at development stage, and no human intervention is needed during deployment (atlas schema apply) stage.

How does it work? Users modify their schema code (e.g., ORM models, SQL or HCL) and open a PR with the changes. Then, Atlas computes the migration plan, runs analysis, and simulates it on a dev-database. Lastly, it comments on the PR with the results:

GitHub Action for schema plan command

Plan Generated by atlas schema plan

Once the PR is approved and merged, the plan is saved in the Atlas Registry in a "ready to be applied" state. During deployment (schema apply), Atlas checks for any pre-planned migration for the given schema transition (State1 -> State2) and uses it if available, otherwise falling back to other approval policies.

This process can also be done locally, allowing users to plan and approve locally, then apply remotely.

If you follow our blog, you know we love practical examples. To maintain this tradition and demonstrate the new command, let’s dive into an example.

Example

Before running atlas schema plan, let's ensure that a schema repository named app exists in Atlas Registry and there is a database containing the previous schema state (before our changes):

schema.sql
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);

We run atlas schema push to create the schema in Atlas Registry:

atlas schema push --env local

Schema: app
-- Atlas URL: atlas://app
-- Cloud URL: https://a8m.atlasgo.cloud/schemas/141733920781

Then, we run atlas schema apply to align the database with the schema state:

atlas schema apply --env local --auto-approve

At this stage, our database main.db contains the users table with the id and name columns.

Changing the Schema

Suppose we want to add a non-nullable email column to the users table. Let's update the schema.sql file and then run atlas schema plan to generate a migration plan.

schema.sql
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
email TEXT NOT NULL
);

We run atlas schema plan to generate a migration plan for adding the email column to the users table:

atlas schema plan --env local

The output looks like this:

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

-- add column "email" to table: "users":
-> ALTER TABLE `users` ADD COLUMN `email` text NOT NULL;

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

Analyzing planned statements (1 in total):

-- data dependent changes detected:
-- L2: Adding a non-nullable "text" column "email" will fail in case table "users"
is not empty https://atlasgo.io/lint/analyzers#MF103
-- ok (346.192µs)

-------------------------
-- 5.038728ms
-- 1 schema change
-- 1 diagnostic
? Approve or abort the plan:
▸ Approve and push
Abort

Data-Dependent Changes

Atlas detects data-dependent changes in the migration plan and provides a diagnostic message. In this case, it warns that adding the non-nullable email column, will fail if the users table is not empty. The recommended solution is to provide a default value for the new column. Let's fix this by adding a default value to the email column and re-run the atlas schema plan command.

schema.sql
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
email TEXT NOT NULL DEFAULT 'unknown'
);

Then, we run atlas schema plan again to generate a new migration plan, but this time, we approve it:

atlas schema plan --env local
Planning migration from local database to file://schema.sql (1 statement in total):

-- add column "email" to table: "users":
-> ALTER TABLE `users` ADD COLUMN `email` text NOT NULL DEFAULT 'unknown';

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

Analyzing planned statements (1 in total):

-- no diagnostics found

-------------------------
-- 6.393773ms
-- 1 schema change
? Approve or abort the plan:
▸ Approve and push
Abort

Once approved, the migration plan will be pushed to the Atlas Registry, and can be applied using atlas schema apply.

Plan Status: APPROVED
-- Atlas URL: atlas://app/plans/20240923085308
-- Cloud URL: https://a8m.atlasgo.cloud/schemas/141733920769/plans/210453397504

At this stage, we can run atlas schema apply to apply the changes to the database, on any environment, without re-calculating the SQL changes at runtime or requiring human intervention.

Applying approved migration using pre-planned file 20240923085308 (1 statement in total):

-- add column "email" to table: "users"
-> ALTER TABLE `users` ADD COLUMN `email` text NOT NULL DEFAULT 'unknown';
-- ok (749.815µs)

-------------------------
-- 802.902µs
-- 1 migration
-- 1 sql statement

Atlas Registry

Starting with this release, Atlas Registry supports the declarative workflow. It allows you to store, version, and maintain a single source of truth for your database schemas and their migration plans.

It is similar to DockerHub, but for your schemas and migrations. In addition to functioning as storage and Atlas state management, it is schema-aware and provides extra capabilities such as ER diagrams, SQL diffing, schema docs, and more.

atlas migrate push

Schema pushed with atlas schema push

What else is new?

In addition to the schema plan command, we have added several new features and improvements to Atlas. Here are some highlights:

  1. Users running atlas schema apply with a Pro license will now receive a detailed migration linting report and can control the approval based on it. Read more about the Review and Approval Policies.
  2. The schema apply command now supports the --edit flag, allowing users to safely edit the migration plan before applying it. Note that if your manual changes are not in sync with the desired state, Atlas will detect schema drift and reject the changes.
  3. The GitHub Action and gh extension for Atlas have been updated to support the new declarative workflow.
  4. The ClickHouse driver now supports Dictionaries.
  5. The docker block in Atlas config now supports build blocks, allowing users to use custom Docker images for their dev-databases.
  6. The PostgreSQL driver now supports configuring DEFERRABLE constraints on primary keys, foreign keys, unique, and exclusion constraints.
  7. The external command was added to the Atlas testing framework, allowing users to run custom commands during the testing phase.

Wrapping Up

That's all for this release! But, we are already working on several features and improvements in the pipeline. To be transparent with our community, here is a look at what's coming next:

  1. Partition support for the PostgreSQL driver.
  2. CircleCI, GitLab CI, Kubernetes Operator, and Terraform Provider will support the new declarative workflow.
  3. A new schema lint command, allowing users to lint their schemas with built-in and custom analyzers.
  4. A Prisma provider for Atlas, enabling Prisma users to import their Prisma schema into Atlas schema state.

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

· 2 min read
Rotem Tamir

Hey Atlas Users,

We wanted to share some important updates regarding changes to our Atlas Pro plan that will come into effect soon.

What's Changing?

Starting September 27th, 2024, Atlas Pro seats will no longer be available for free. New users will still have access to a 30-day free trial, but after that, a license will be required to continue using Atlas Pro.

For our existing users, don't worry! We've got you covered with a 30-day grace period to upgrade your account. Plus, we're offering each company credits that should cover their bill for an additional month.

For our existing paying users, as a token of our appreciation, you'll continue to receive 3 free seats on top of what you've already paid for, which means you are not impacted by this change. We value your continued support and want to make sure you're getting even more out of Atlas Pro.

We also recognize that many of you are using Atlas Pro for non-commercial projects, so we're excited to introduce the Atlas Pro Hacker License! This license offers full access to Atlas Pro features for students, maintainers of open-source projects, and hobbyists working on non-commercial software.

Why Are We Doing This?

At its core, Atlas has always been an open-source project available for anyone to use. However, to ensure the long-term sustainability and success of Atlas, we believe it's important to have a strong, profitable business model. This allows us to continue building and maintaining Atlas for the future, while fostering a vibrant community around it.

What You Need to Do

To continue using Atlas Pro, you'll need to upgrade your organization's account by October 27th, 2024. You can do this by heading over to the Settings page for your organization, where you'll be able to purchase Atlas Pro through Stripe or directly via AWS Marketplace.

  • Apply for company credits here.
  • Apply for the free Atlas Pro Hacker License here.

We appreciate your support and understanding as we make these changes. If you have any questions or need assistance, don't hesitate to reach out to us via our Discord server or at hello@ariga.io!

All the best, Ariel and Rotem

· 21 min read
Rotem Tamir

Adapted from a talk given at GopherCon Israel 2024

Introduction

How does Go, the project, and team behind it, test go test, the Go tool's command for running tests? Does Go test go test using the go test command? In this article, we explore the evolution of how the Go team tests the Go tool (go) and discuss strategies for testing command-line tools written in Go in general.

CLIs and Go

If you are a software engineer in 2024, you are most likely using a CLI tool written a tool to perform some critical part of your work. Perhaps you're using docker, to build and run container images or kubectl to interact with a kubernetes cluster. Maybe you're using terraform to manage your infrastructure as code. Maybe you're using atlas (the project I work on) to manage your database schema as code. You could be using trivy to scan your code for vulnerabilities or gh to interact with your code on GitHub.

Go is a fantastic language for writing CLI tools, and today we're going to try and study some of the strategies that you can employ to test CLI tools by looking at how the Go team tests the go tool.

Motivation

My personal motivation for digging into this topic arose from my work on Atlas, a database schema as code tool. Atlas is a CLI written in Go (see our GitHub repo), that has sometimes been described as a "Terraform for databases." It is used by many companies big and small to streamline their database schema management process.

One of the first decisions Ariel (my co-founder) and I made when we started to work on Atlas was that we were going to be employing a continuous delivery strategy, shipping new features and bug fixes to our users as soon as they were ready, often times multiple times a day. This meant that we needed to have a robust testing strategy in place to ensure that we were shipping high-quality software to our users. After all, we were building a tool that was going to be used by other developers to manage their most critical data assets.

Testing CLI tools

Before we dive into how the Go team tests the go tool, let's take a step back and think about what CLI testing is all about. Testing CLIs has it's unique challenges, but at the end of the day, it's very similar to testing any other piece of software.

As with all automated tests, we can identify four discrete phases with CLI tests which I characterize as the "Quadruple A" of testing:

  • Arrange: We setup the environment for the test. For CLI tests this typically involves creating temporary files, and setting up environment variables.
  • Act: When testing server software we would issue a request, but when testing CLIs, this means executing the binary under test, often supplying it with command-line arguments, flags, and potentially piping data into STDIN.
  • Assert: We consume the output streams (STDOUT, STDERR) and compare them to expected values. We also check the exit code of the process, and any side effects that the command may have had on the environment.
  • And... cleanup: We clean up the environment, removing any temporary files, and resetting any environment variables that we may have changed. Failing to do this can lead to flaky tests - which debugging is arguably one of the worst things in software development.

How the Go team tests go test

With that in mind let's now explore how testing the go tool has evolved over time.

This section is mostly built upon a terrific and detailed commit message on CL #123577 by Russ Cox. I highly recommend reading the original commit message for a more detailed explanation of the evolution of the Go test suite.

2012-2015: test.bash

In the early days of Go, the Go test suite was tested using a shell script called test.bash. This script started out as a simple 30-40 line script that ran the go tool with various flags and options and checked the output. Over time, as the Go tool grew in complexity, so did the test.bash script. It eventually grew to be a 1500+ line shell script that tested the go tool in a variety of ways. The tests looked something like this:

TEST 'file:line in error messages'
# Test that error messages have file:line information at beginning of
# the line. Also test issue 4917: that the error is on stderr.
d=$(TMPDIR=/var/tmp mktemp -d -t testgoXXX)
fn=$d/err.go
echo "package main" > $fn
echo 'import "bar"' >> $fn
./testgo run $fn 2>$d/err.out || true
if ! grep -q "^$fn:" $d/err.out; then
echo "missing file:line in error message"
cat $d/err.out
ok=false
fi
rm -r $d

If you examine the test above, you will see that it is comprised of the same four phases that we discussed earlier: Arrange, Act, Assert, and Cleanup:

  • Arrange: The test creates a temporary directory and a temporary file.
  • Act: The test runs the go tool with the run subcommand and pipes the output to a file.
  • Assert: The test checks that the output contains the filename and line number of the error.
  • Cleanup: The test removes the temporary directory.

Russ writes about the test.bash script:

The original cmd/go tests were tiny shell scripts written against a library of shell functions.

They were okay to write but difficult to run: you couldn't select individual tests (with -run) they didn't run on Windows, they were slow, and so on.

The tests had always been awkward to write.

2015-2018: testgo

In June 2015, CL #10464 introduced go_test.go. This file contained a basic framework for writing Go tests for the go tool named testgo. The same test from above, written in Go, looked something like this:

func TestFileLineInErrorMessages(t *testing.T) {
tg := testgo(t)
defer tg.cleanup()
tg.parallel()
tg.tempFile("err.go", `package main; import "bar"`)
path := tg.path("err.go")
tg.runFail("run", path)
shortPath := path
if rel, err := filepath.Rel(tg.pwd(), path);
err == nil && len(rel) < len(path) {
shortPath = rel
}
tg.grepStderr(
"^"+regexp.QuoteMeta(shortPath)+":",
"missing file:line in error message",
)
}

As you can see, the test is still comprised of the same four phases: Arrange, Act, Assert, and Cleanup:

  • Arrange: The test creates a temporary file.
  • Act: The test runs the go tool with the run subcommand.
  • Assert: The test checks that the output contains the filename and line number of the error.
  • Cleanup: The test removes the temporary file. (this happens in the defer tg.cleanup() call)

Russ writes about the testgo framework:

“CL 10464 introduced go_test.go's testgo framework and later CLs translated the test shell script over to individual go tests. This let us run tests selectively, run tests on Windows, run tests in parallel, isolate different tests, and so on.

It was a big advance. It's better but still quite difficult to skim.”

2018-?: script_test.go

Most teams and projects that I know would stop here. Go's testing infrastructure, the testing package, as well as the accompanying go test tool is terrific. When coupled with some thoughtful library code, testing CLIs in Go can be a breeze. But the Go team didn't stop there. In 2018, CL #123577 introduced a new testing framework for the go tool called script_test.go.

Russ writes about it:

script_test.go brings back the style of writing tests as little scripts, but they are now scripts in a built-for-purpose shell-like language, not bash itself.

Under script_test.go, test cases are described as txt files which are txtar archives containing the test script and any accompanying files. Here's the "Hello, world" example for script_test:

# src/cmd/go/testdata/script/run_hello.txt

# this is a txtar archive

# run hello.go (defined below)
go run hello.go

# assert ‘hello world’ was printed to stderr
stderr 'hello world'

-- hello.go --
package main
func main() { println("hello world") }

As before, the test comprises the same four phases: Arrange, Act, Assert, and Cleanup:

  • Arrange: The test creates a temporary file, defined by the -- hello.go -- section.
  • Act: The test runs the go tool with the run subcommand on the temporary file.
  • Assert: The test checks that the output contains the string hello world.
  • Cleanup: Where is the cleanup code? We'll explore that in a moment.

How does script_test.go work?

script_test does a lot of cool things under the hood that makes it ideal for testing a CLI tool:

  1. Each script becomes a Go sub-test, which means from the perspective of go test, it's a normal test, that can be run in parallel, skipped, or run with the -run flag.
  2. script_test creates an isolated sandbox for each test, so that tests can't interfere with each other. Doing so enables it to run tests in parallel, which can significantly speed up the test suite.
  3. The files defined in the txtar archive are created in the sandbox, and the test is run in that directory.

After setting up, script_test runs the test script commands, line by line:

  1. Commands are run in sequence, and the output is captured into stdout and stderr buffers.
  2. Commands are expected to succeed, unless explicitly negated with ! at the beginning of the line.
  3. Many helpful assertion commands such as stdout (to check the stdout buffer), stderr (to check the stderr buffer), and cmp (to compare the contents of two files) are available.

As for cleanup, script_test automatically cleans up the sandbox after each test, removing all files and directories created during the test.

Can I use script_test.go for my CLI?

If you are writing a CLI tool in Go, I hope by now you are pretty excited about script_test.go. Wouldn't you love to have a testing framework that allows you to write tests in a shell-like language, that can be run in parallel, and that automatically cleans up after itself?

You are probably asking yourself, "Can I use script_test.go for my CLI?"

Well, surprisingly enough, the answer is:

No, you can't.

script_test.go is under an internal package in the Go repository, and it is pretty tightly coupled to the go tool.

The End.

Or is it?

Introducing testscript

In late 2018, Roger Peppe, a long time Go user, contributor and member of the Go community created a repo named rogpeppe/go-internal to factor out some useful internal packages from within the Go codebase. One of these packages is testscript, which is based on the work the Go team created for script_test.

Roger was kind enough to speak with me in preparation for this talk, so I hope that even if you've read about it before, I can share some new things you haven't heard.

script_test.go, as we mentioned, never exposed a public API, and so over the past 6 years, the package gained steam and popularity, especially among Go "insiders" - people who knew about script_test, but couldn't use it. Today, according to public GitHub data, go-internal is depended upon by over 100K repositories on GitHub.

(As a side-note, Roger pointed out to me that it's difficult to get the exact number of projects that use testscript itself, as the go.dev site omits any dependencies that run through test code. If you look at go.dev it shows that only 14 (!) packages import it)

Because script_test never had a public API, and was very tightly coupled to testing the Go tool codebase, testscript should be thought of as more of a conceptual "factoring out" than a 1:1 exporting.

Over time, many features that weren't available in the original implementation, such as generating coverage data, a standalone CLI, and auto-updated of Golden files was added.

As I will show later, testscript is a fantastic tool and we have been utilizing it in the Atlas codebase for a long time with great success. However, it is worth mentioning that in November 2023, Russ Cox published a similar package named rsc.io/script which is also based on the script_test codebase. I haven't used it myself, but it's worth checking out.

Our example CLI: wordwrap

To demonstrate how testscript works, I've created a simple CLI tool named wordwrap. wordwrap is a simple tool that takes a path and applies simple word wrapping to all .txt files in that path. You can find the code on GitHub. Wordwrap has a few features that we would like to test:

On the simple case, suppose our current working directory contains a file named example.txt with the following content:

This is a text file with some text in it. To demonstrate wordwrap, it has more than 40 chars.

Running wordwrap:

go run ./cmd/wordwrap -path ./dir-with-txt-files

Our example.txt file would be transformed into:

This is a text file with some text in
it. To demonstrate wordwrap, it has more
than 40 chars.

By default, wordwrap wraps lines at 40 characters, but you can specify a different line length with the -width flag:

go run ./cmd/wordwrap -path ./dir-with-txt-files -width 20

Would wrap the lines at 20 characters:

This is a text file
with some text in
it. To demonstrate
wordwrap, it has
more
than 40 chars.

To make things more interesting, we have also added a -strict flag that will cause wordwrap to fail if any line in the file is longer than the specified width. For example, suppose our example.txt file contains a word that is 34 characters long:

It's supercalifragilisticexpialidocious
Even though the sound of it is something quite atrocious
If you say it loud enough you'll always sound precocious

Running wordwrap with the -strict flag and a width of 20:

go run ./cmd/wordwrap -path ./hack -width 20 -strict

Would fail with an error message:

file hack/example.txt: line 2 exceeds specified width 20
exit status 1

Writing tests with testscript

Let's see how to write tests for wordwrap using testscript.

To set up, first create a file named wordwrap_test.go in your projects and create the following boilerplate code:

package wordwrap_test

import (
"bufio"
"os"
"testing"
"github.com/rogpeppe/go-internal/testscript"

"rotemtam.com/wordwrap"
)

func TestMain(m *testing.M) {
os.Exit(testscript.RunMain(m, map[string]func() int{
"wordwrap": wordwrap.Run,
}))
}

func TestScript(t *testing.T) {
testscript.Run(t, testscript.Params{
Dir: "testdata",
})
}

Here's what's happening in the code above:

  1. Our TestMain function is a setup function that prepares the test environment. It uses testscript.RunMain to tell testscript that it should create a custom command wordwrap that runs the wordwrap.Run function. This simulates having a binary named wordwrap that runs our program's main function.
  2. TestScript is where the actual magic happens. It uses testscript.Run to run the tests in the testdata directory. The testdata directory contains the test scripts that we will write in the next step.

Our first test script

Let's create a file named testdata/basic.txt with the following content:

wordwrap

cmp basic.txt basic.golden

-- basic.txt --
This is a text file with some text in it. To demonstrate wordwrap, it has more than 40 chars.

-- basic.golden --
This is a text file with some text in
it. To demonstrate wordwrap, it has more
than 40 chars.

As before, you will find our test script is comprised of the same four phases: Arrange, Act, Assert, and Cleanup:

  • Arrange: The test creates a temporary file, defined by the -- basic.txt -- section.
  • Act: The test runs the wordwrap command.
  • Assert: The test compares the output to the contents of the basic.golden file. This is done using the included cmp command.
  • Cleanup: There is no explicit cleanup in this test, as testscript will automatically clean up the sandbox after the test.

The awesome thing about testscript, is that from go test's perspective, basic is just a regular Go test. This means that we can execute it as we would any other test:

go test -v ./... -run TestScript/basic

This is the output you should see:

=== RUN   TestScript
=== RUN TestScript/basic
=== PAUSE TestScript/basic
=== CONT TestScript/basic
testscript.go:558: WORK=$WORK
# --- redacted for brevity ---
> wordwrap
> cmp basic.txt basic.golden
PASS

--- PASS: TestScript (0.00s)
--- PASS: TestScript/basic (0.15s)
PASS
ok rotemtam.com/wordwrap (cached)

A more involved test script

Next, let's create a more involved test script that verifies additional behavior in wordwrap. Create a file named testdata/dont-touch.txt with the following content:

wordwrap -path p1.txt

! stderr .

cmp p1.txt p1.golden

exec cat dont-touch.txt
stdout 'This file shouldn''t be modified, because we invoke wordwrap with a path argument.'

-- p1.txt --
Don't communicate by sharing memory, share memory by communicating.

-- p1.golden --
Don't communicate by sharing memory,
share memory by communicating.

-- dont-touch.txt --
This file shouldn't be modified, because we invoke wordwrap with a path argument.

This test verifies that wordwrap doesn't modify files that are not passed as arguments. The test script is comprised of the same phases.

  • Arrange: The test creates p1.txt, which is the file we are going to modify, and dont-touch.txt, which is the file we don't want to modify.
  • Act: The test runs the wordwrap command with the -path flag.
  • Assert: The test compares the output to the contents of the p1.golden file. This is done using the included cmp command. The test also verifies that the dont-touch.txt file hasn't been modified.
  • Cleanup: There is no explicit cleanup in this test, as testscript will automatically clean up the sandbox after

Testing the -width flag

In addition, we should probably verify that the -width flag works as expected. Create a file named testdata/width.txt:

skip

wordwrap -width 60

cmp effective.txt effective.golden

-- effective.txt --
This document gives tips for writing clear, idiomatic Go code. It augments the language specification, the Tour of Go, and How to Write Go Code, all of which you should read first.

Note added January, 2022: This document was written for Go's release in 2009, and has not been updated significantly since.

-- effective.golden --
This document gives tips for writing clear, idiomatic Go
code. It augments the language specification, the Tour of
Go, and How to Write Go Code, all of which you should read
first.

Note added January, 2022: This document was written for Go's
release in 2009, and has not been updated significantly
since.

This test script verifies that the -width flag works as expected. The test script is comprised of the same phases.

This works, but I didn't love writing it. Creating the .golden file by hand is a bit tedious, and it's easy to make mistakes. In this case, wouldn't it be great if we could create a custom command that verifies that the output is wrapped at 60 characters?

Thankfully, testscript allows us to create custom commands. Let's create a custom command named maxlen that verifies that the output is wrapped at a maximum of n characters. Add the following code to wordwrap_test.go:

// maxline verifies that the longest line in args[0] is shorter than args[1] chars.
// Usage: maxline <path> <maxline>
func maxline(ts *testscript.TestScript, neg bool, args []string) {
if len(args) != 2 {
ts.Fatalf("usage: maxline <path> <maxline>")
}
l, ok := strconv.Atoi(args[1])
if ok != nil {
ts.Fatalf("usage: maxline <path> <maxline>")
}
scanner := bufio.NewScanner(
strings.NewReader(
ts.ReadFile(args[0]),
),
)
tooLong := false
for scanner.Scan() {
if len(scanner.Text()) > l {
tooLong = true
break
}
}
if tooLong && !neg {
ts.Fatalf("line too long in %s", args[0])
}
if !tooLong && neg {
ts.Fatalf("no line too long in %s", args[0])
}
}

In order to use the maxline command in our test scripts, we need to register it with testscript. Update the TestScript function in wordwrap_test.go to include the following code:

func TestScript(t *testing.T) {
testscript.Run(t, testscript.Params{
Dir: "testdata",

Cmds: map[string]func(ts *testscript.TestScript, neg bool, args []string){
"maxline": maxline,
},
})
}

Now we can use the maxline command in our test scripts. Create a new test named testdata/width-custom.txt with the following content:

wordwrap -width 60

! maxline effective.txt 20
maxline effective.txt 60

wordwrap -width 40
! maxline effective.txt 20
maxline effective.txt 40

wordwrap -width 20
maxline effective.txt 20

-- effective.txt --
This document gives tips for writing clear, idiomatic Go code. It augments the language specification, the Tour of Go, and How to Write Go Code, all of which you should read first.

Note added January, 2022: This document was written for Go's release in 2009, and has not been updated significantly since.

Running this test script will verify that the output is wrapped at 60 characters, 40 characters, and 20 characters:

go test -v ./... -run TestScript/width-custom

Output:

?       rotemtam.com/wordwrap/cmd/wordwrap      [no test files]
=== RUN TestScript
=== RUN TestScript/width-custom
=== PAUSE TestScript/width-custom
=== CONT TestScript/width-custom
testscript.go:558: WORK=$WORK
# --- redacted for brevity ---
> wordwrap -width 60
> ! maxline effective.txt 20
> maxline effective.txt 60
> wordwrap -width 40
> ! maxline effective.txt 20
> maxline effective.txt 40
> wordwrap -width 20
> maxline effective.txt 20
PASS

--- PASS: TestScript (0.00s)
--- PASS: TestScript/width-custom (0.19s)
PASS
ok rotemtam.com/wordwrap 0.626s

Testing strict mode

Finally, let's create a test script that verifies that the -strict flag works as expected. Create a file named testdata/strict.txt with the following content:

! wordwrap -path poppins.txt -width 20 -strict
stderr 'line 2 exceeds specified width 20'

wordwrap -path poppins.txt -width 20
cmp poppins.txt poppins.golden
-- poppins.txt --
It's supercalifragilisticexpialidocious
Even though the sound of it is something quite atrocious
-- poppins.golden --
It's
supercalifragilisticexpialidocious
Even though the
sound of it is
something quite
atrocious

This test script verifies that wordwrap fails when a line exceeds the specified width in strict mode.

Awesome!

Personal impact

Aside from being a super cool tool for writing tests for CLI tools, testscript has had a significant impact on my team. We have been using it in the Atlas codebase for a long time, and it has been a game-changer for us.

Atlas, as a schema-as-code tool, is a bridge between code (files) and databases. Thus, being able to easily write tests to verify our tool's behavior in a way that is close to how our users interact with it has been invaluable.

Over the years, we have accumulated a set of custom testscript commands that allow us to write test scripts in a fluent and intuitive way. You can see this in action in the Atlas codebase, but just to give you a taste, here is how our testscript entrypoint looks like for MySQL integration tests:

func TestMySQL_Script(t *testing.T) {
myRun(t, func(t *myTest) {
testscript.Run(t.T, testscript.Params{
Dir: "testdata/mysql",
Setup: t.setupScript,
Cmds: map[string]func(ts *testscript.TestScript, neg bool, args []string){
"only": cmdOnly,
"apply": t.cmdApply,
"exist": t.cmdExist,
"synced": t.cmdSynced,
"cmphcl": t.cmdCmpHCL,
"cmpshow": t.cmdCmpShow,
"cmpmig": t.cmdCmpMig,
"execsql": t.cmdExec,
"atlas": t.cmdCLI,
"clearSchema": t.clearSchema,
"validJSON": validJSON,
},
})
})
}

Having these commands, allow us to write test scripts that are easy to read and understand, and that verify the behavior of our tool is correct. For example:

apply 1.hcl
cmpshow users 1.sql

-- 1.hcl --
schema "main" {}

table "users" {
schema = schema.main
column "id" {
null = false
type = integer
auto_increment = true
}
primary_key {
columns = [column.id]
}
}

-- 1.sql --
CREATE TABLE `users` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT)

This test applies an Atlas DDL schema on a SQLite database and verifies that the schema is created correctly.

Conclusion

In this article, we have explored how the Go team tests the go tool, and how you can apply similar strategies to test your CLI tools using testscript.

As a team that develops tools for other developers, we take the reliability of our tools very seriously. The key to this, we have found over the years, is to have a robust testing strategy in place. This allows us to move fast (without breaking things) and to ship high-quality software to our users.

Resources

· 4 min read
Rotem Tamir

Hi Everyone,

It's been a few weeks since our last release, and I'm very excited to share with you the news of Atlas v0.27. In this release, you will find:

  • Atlas Schema Monitoring: A new product that provides a set of tools and features to help you manage and monitor your database schema effectively.
  • Pay via AWS Marketplace: Atlas users can now pay for their Atlas subscription via the AWS Marketplace.
  • Atlas HCL Doc Portal: A new portal that contains always up to date, automatically generated documentation for the Atlas HCL language.

Introducing Schema Monitoring

The hallmark of this release is a new product we call Atlas Schema Monitoring. Atlas Schema Monitoring provides a set of tools and features to help you manage and monitor your database schema effectively. Teams install an agent (container) on their database VPC which tracks changes to the database schema and reports metadata to the Atlas Cloud control plane. Using this metadata Atlas Schema Monitoring provides:

  1. Live visibility of your database schema with automated ER diagrams and auto-generated documentation.
  2. A Changelog of schema changes, so you can see how schemas change over time, and easily triage schema change related issues.
  3. Alerts Use Webhooks or Slack notifications to inform or alert teams that need to know about schema changes or drift.

Starting today, we are providng one free monitored instance to all signed up Atlas users.

A Live Demo is available for you to try out.

How it works

Atlas Cloud never has direct access to your database, instead it uses a middleman, the Atlas agent, to connect to your database instead. In order for this to work, the agent needs to be installed somewhere with network connectivity to the database, usually within the same VPC as the database. In addition, the agent should have outbound connectivity to your cloud account (e.g.,https://your-tenant.atlasgo.cloud).

The agent then starts polling Atlas Cloud for work. Once assigned a task, it connects to the database and executes the task, e.g. "take a snapshot" and then reports back the result to Atlas Cloud. The Agent does not read or report back any user data, only meta information about the database schema.

To read more about the key concepts and features of Atlas Schema Monitoring, head over to the Overview page.

Getting started

To get started with Schema Monitoring in under 5 minutes, head over to the Quickstart guide.

Security

Atlas Schema Monitoring is designed with the principle of minimal access in mind. The Atlas agent is designed to only require read-only access to the database schema and only requires access to system information schema tables and not user data.

Additionally, to provide further security and control, database credentials are never provided or stored in the Atlas Cloud control plane. Instead, the Atlas agent is deployed in your environment and connects to the database directly using a variety of secure methods.

To learn more about how to securely provide database credentials to the Atlas agent, head over to the Security and Credentials guide.

Pay via AWS Marketplace

Atlas users can now pay for their Atlas subscription via the AWS Marketplace. This is a great option for users who prefer to consolidate their billing and payments in one place or have AWS credits they would like to use.

To purchase Atlas quota via the AWS Marketplace, visit our Product Page.

Atlas HCL Doc Portal

Atlas enables users manage their database schema as code. One of the popular ways to define the desired state of your is via the Atlas HCL data definition language. Additionally, users have a powerful configuration language to define their project configuration.

We have added a new Atlas HCL Portal to the documentation website, which contains always up to date, automatically generated documentation for the Atlas HCL language.

Wrapping Up

That's all for this release! We hope you enjoy the new features and improvements. As always, we would love to hear your feedback and suggestions on our Discord server.

· 6 min read
Rotem Tamir

Hi everyone,

It's been about a month since our last release, and we're excited to announce that Atlas v0.26 is now available! In this release we are happy to introduce a new feature that has been requested by many of you: support for Entity Framework Core. As part of our ever going effort to improve the quality and coverage of our documentation, we have published a set of guides on testing database schemas and migrations as well as a new GORM Portal.

Additionally, we have published an official "Supported Version Policy" and made some changes to our EULA, described below.

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

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

What's missing in EF Core Migrations?

EF Core is the most popular ORM used in the .NET community, supported by Microsoft. EF Core allows users to manage their database schemas using its migrations. EF Core's migrations have long been a popular and reliable choice for managing database schema changes in the C# ecosystem.

However, EF Core migrations have lacks some capabilities can make them difficult to work with:

  1. Support for advanced database features. Like many ORMs, EF Core is designed to be database-agnostic, which means it does not support all the features of every database it can connect to. This can make it difficult to use and manage database features such as triggers, stored procedures, Row-level security and custom data types.
  2. Testing migrations. Migrations are typically considered the most risky part of a deployment. Therefore, automatically verifying they are safe and correct is paramount. Like most ORMs, EF Core does not provide a way to automatically test migrations.
  3. Production Grade Declarative Flow. EF Core supports a very basic declarative flow name EnsureCreated that can be used to create the database without specifying migrations. However, as the documentation warns, this method should not be used in production. For teams that want to adapt a "Terraform-for-databases" approach, this can be a blocker.
  4. Integration with modern CI/CD pipelines. EF Core migrations are typically run using the dotnet ef command line tool. Migrations should be integrated into the software delivery pipeline to ensure that the database schema is always in sync with the application code. This can be difficult to achieve with EF Core migrations.

Atlas and EF Core Migrations

Atlas is a database schema as code tool that allows developers to inspect, plan, test, and execute schema changes to their database. Atlas can be used to replace EF Core migrations with a more modern DevOps approach.

Comparing Atlas to EF Core migrations:

  • Loading Core Models. Similarly to EF Core migrations, Atlas can load the schema of an EF Core project. EF Core users can keep using the EF Core models as the source of truth for their database schema. Using the Atlas EF Core Provider, Atlas can load the schema of an EF Core project and use it as the source of truth for the database schema.

  • Composing schemas. Atlas can compose schemas from multiple sources, including EF Core models, SQL files, and external schema datasources. This enables users to natively declare schemas that layer advanced database features (such as views, triggers) as part of the schema source of truth which is not possible with EF Core.

  • Automatic planning. Similarly to EF Core migrations, with its "versioned migrations" workflow, Atlas can automatically plan schema migrations by diffing the data model with the migration directory.

  • Declarative flow. Atlas supports a declarative flow that can be used to create the database schema from scratch without using migrations. This is useful for teams that want to adapt a "Terraform-for-databases" approach.

  • Testing migrations. Atlas can automatically lint and test migrations to ensure they are safe and correct. Using this capability teams can reduce the risk of deploying migrations to production.

  • Integration with CI/CD pipelines. Atlas can be integrated into modern CI/CD pipelines using native integrations with popular CI/CD tools like GitHub Actions, CircleCI, GitLab CI, Terraform, Kubernetes, ArgoCD, and more.

Getting Started with Atlas + EF Core

As part of this version, we are happy to release the Atlas EF Core Provider.

To get started with Atlas and EF Core, head over to the Official Guide.

New GORM Portal

Over the past year, we have seen a significant increase in the number of users using GORM with Atlas. To better support GORM users in the Atlas ecosystem, we have launched a new documentation portal that provides guides, tutorials, and examples for using GORM with Atlas.

You can find the new GORM portal here.

Testing Database Schemas and Migrations

Since starting Atlas, it has been Ariel and my belief that accurate and thorough documentation is essential for the success of any software project targeted at developers. To that end, our team has been working hard to improve the quality and coverage of our documentation covering one of the most important aspects of working with Database Schema-as-Code: Testing.

As part of this set of guides you can find:

Supported Version Policy

To ensure the best performance, security and compatibility, the Atlas team will only support the three most recent minor versions of the CLI. For example, if the latest version is v0.26, the supported versions will be v0.25 and v0.24 (in addition to any patch releases and the "canary" release which is built twice a day).

As part of our this policy, binaries for versions that were published more than 6 months ago will be removed from the CDN and Docker Hub.

EULA Changes

The standard Atlas binary is provided under the Atlas EULA. We have recently made some changes to the EULA to reflect new data privacy considerations described in our CLI Data Privacy document. As part of these changes Atlas may collect anonymous telemetry (aggregated, anonymized, non-personal) data to help us improve the product. If you wish to opt-out of telemetry, you may set the ATLAS_NO_ANON_TELEMETRY environment variable to true.

The updated EULA can be found here.

Wrapping Up

That's all for this release! We hope you try out (and enjoy) all of these new features and find them useful. Stay tuned for our next release which is going to include some exciting new features around declarative flows and database schema observability.

As always, we would love to hear your feedback and suggestions on our Discord server.

· 5 min read
Rotem Tamir

Hi everyone,

Thanks for joining us today for our v0.25 release announcement! In this version we are introducing a new feature that has been requested by many of you: support for Row-level Security Policies in PostgreSQL.

Additionally, we have made some minor changes to our pricing plans, more on that below.

What are Row-level Security Policies?

Row-level security (RLS) in PostgreSQL allows tables to have policies that restrict which rows can be accessed or modified based on the user's role, enhancing the SQL-standard privilege system available through GRANT.

When enabled, all normal access to the table must comply with these policies, defaulting to a deny-all approach if no policies are set, ensuring that no rows are visible or modifiable. Policies can be specific to commands, roles, or both, providing fine-grained control over data access and modification.

How does RLS work?

When you create and enable a row-level security (RLS) policy in PostgreSQL, the database enforces the specified access control rules on a per-row basis.

For example, you can create a policy that allows only employees to see their own records in an employees table. The policy could look like this:

CREATE POLICY employee_policy ON employees
FOR SELECT
USING (current_user = employee_role);

This SQL command creates an RLS policy named employee_policy on the employees table. The FOR SELECT clause specifies that this policy applies to SELECT queries. The USING clause contains the condition current_user = employee_role, which means that a user can only select rows where the employee_role column matches their PostgreSQL username.

Next, database administrators typically run:

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

This command enables RLS on the employees table. With RLS enabled, PostgreSQL will check the policies defined for this table whenever a user attempts to access or modify existing rows, or insert new ones.

When a user executes a SELECT query on the employees table, PostgreSQL evaluates the employee_policy. If the user's PostgreSQL role (username) matches the employee_role column value in a row, the row is included in the query result. Otherwise, the row is excluded.

For instance, if the employees table contains the following data:

idnameemployee_role
1Alicealice
2Bobbob
3Charliecharlie

When the user alice runs SELECT * FROM employees, PostgreSQL applies the policy:

SELECT * FROM employees WHERE current_user = employee_role;

This results in:

idnameemployee_role
1Alicealice

By enforcing these policies, RLS ensures that users only have access to the data they are permitted to see, enhancing the security and privacy of the database.

Manage your Row-level Security Policies as Code

With Atlas, you can now manage your RLS policies as code, just like you manage other database resources such as tables, indexes, and triggers. This allows you to version control your policies, track changes, and apply them consistently across your environments.

To get started with RLS in Atlas, first upgrade to the most recent version.

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

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

RLS is available to Atlas Pro users only. Get your free Atlas Pro account today by running:

atlas login

Next, you can define your RLS policies in your Atlas schema file (schema.hcl) using the new policy block:

policy "employee_policy" {
on = table.employees
for = SELECT
to = [PUBLIC]
using = "(current_user = employee_role)"
}

This HCL snippet defines an RLS policy named employee_policy on the employees table, allowing only users whose employee_role matches their PostgreSQL username to SELECT rows from the table.

Next, you need to enable RLS on the table:

table "employees" {
schema = schema.public
column "employee_role" {
type = text
}
row_security {
enabled = true // ENABLE ROW LEVEL SECURITY
}
}

Finally, run atlas schema apply to apply the changes to your database!

To learn more about RLS using Atlas, check out our documentation.

Introducing Atlas Pro

Since launching Atlas Cloud a little over a year ago, we have been working hard with our users and customers to make Atlas as easy and simple to use as possible.

One point of confusion we have encountered, especially around our pricing plans, was how users who currently don't want (or can't) use Atlas Cloud for their CI/CD pipelines can get access to the advanced CLI features that Atlas offers. Previously, teams needed to buy Cloud quota to get access to the CLI, which didn't make a lot of sense.

To address some of these issues we are making some small changes to our pricing plans:

Atlas now comes in three tiers:

  • Open - Our CLI, doesn't require creating an account and comes with a solid set of features (this is more than enough for many of our users).
  • Pro (previously "Business") - An enhanced version of our CLI, which includes support for advanced database features and drivers. It will cost $9/month/user, but users get their first 3 seats per company for free when they sign up. Pro users also have access to Atlas Cloud (pricing remains the same).
  • Enterprise - our enterprise tier, targeted mostly at larger organizations or teams in regulated industries with stricter compliance requirements.

To learn more about our new plans, head over to our updated pricing page.

Wrapping Up

That's all for this release! We hope you try out (and enjoy) all of these new features and find them useful. As always, we would love to hear your feedback and suggestions on our Discord server.

· 13 min read
Rotem Tamir

Hi everyone,

We are back again with a new release of Atlas, v0.24. In this release we double down on the core principle that has been guiding us from the start: enabling developers to manage their database schema as code. The features we announce today may appear like a yet another cool addition to Atlas, but I am fairly confident, that in a few years' time, they will be recognized as something foundational.

In this release we introduce:

  • schema test - a new command (and framework) for testing your database schema using familiar software testing paradigms.
  • migrate test - a new command for testing writing tests for you schema migrations.
  • Enhanced editor support - we have added support for some long awaited features in our VSCode and JetBrains plugins: multi-file schemas, jump to definition, and support for much larger schemas.

Doubling Down on Database Schema-as-Code

The core idea behind Atlas is to enable developers to manage their Database Schema-as-Code. Before we jump into the recent additions to Atlas, I would like to take a moment to reflect on why our industry seems to think that "X-as-Code" is a great idea.

In a nutshell, the "X-as-Code" movement is about being able to describe the desired state of a system (whether it's infrastructure, configuration, or schema) in a declarative way and then have that state enforced by a tool.

So why is having things described as code so great? Here are a few reasons:

  • Code can be versioned. This means that you can track changes to your system over time, easily compare states, and rollback as needed.
  • Code is understood by machines. As formal languages, code can be parsed, analyzed, and executed by machines.
  • Code can be tested and validated. By using software testing paradigms, you can ensure that your system behaves as expected in an automated way.
  • Code can be shared and reused. Code allows us to transfer successful ideas and implementations between projects and teams.
  • Code has a vast ecosystem of productivity tools. By using code, you can leverage the vast ecosystem of tools and practices that have been developed by software engineers over the years.

Our core goal with Atlas is to bring these benefits to the world of database schema management. We believe that by enabling developers to manage their database schema as code, we can help them build better, more reliable systems.

Today we bring one of the most important tenets of modern software development to the world of database schema management: testing.

Why test your database schema and migrations?

Testing is a fundamental part of modern software development. By writing tests, you can ensure that your code behaves as expected, catch bugs early, and prevent regressions.

When it comes to database schemas, testing is just as important. Databases are much more than just a storage layer, they can be programmed, enforce logic and constraints, and have complex relationships between tables. For example, table triggers allow you to run custom code when certain events occur, and you should be able to test that this code behaves as expected and that later changes to the schema do not break it. In a similar vein, developers can provide complex expressions in check constraints that should be tested to ensure they are working as expected.

When it comes to migrations, testing is equally important. Atlas already provides the migrate lint command to help you catch invalid migrations and common mistakes. However, migrate test takes validating your migrations a step further.

Many teams use migrations as a mechanism to apply data migrations in tandem with schema changes. As they involve data, these changes are super risky, yet it is notoriously hard to test them. By providing a way to test your migrations, we hope to make this process easier and more reliable.

Introducing schema test

The schema test command allows you to write tests for your database schema using familiar software testing paradigms.

To get started, first install the latest version of the Atlas CLI:

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

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

Next, login to your Atlas account to activate the new schema testing features:

atlas login

Let's see a brief example. We will begin our project by defining a basic Atlas project file named atlas.hcl:

atlas.hcl
env "local" {
src = "file://schema.hcl"
dev = "docker://postgres/16/dev?search_path=public"
}

Next, let's define a PostgreSQL Domain to model a data type for a us_postal_code:

schema.sql
CREATE DOMAIN "us_postal_code" AS text
CONSTRAINT "us_postal_code_check"
CHECK (
(VALUE ~ '^\d{5}$'::text) OR
(VALUE ~ '^\d{5}-\d{4}$'::text)
);

Next, let's create a file named "schema.test.hcl" with the following content:

schema.test.hcl
test "schema" "postal" {
exec {
sql = "select 'hello'::us_postal_code"
}
}

Per testing best practices, we start with a test that is going to fail, since the string "hello" is not a valid US postal code.

Now, we can run the test using the schema test command:

atlas schema test --env local

The output will be:

-- FAIL: postal (319µs)
schema.test.hcl:2:
Error: pq: value for domain us_postal_code violates check constraint "us_postal_code_check"
FAIL

As expected, the test failed, and we can now fix the test by catching that error and verifying its message:

schema.test.hcl
test "schema" "postal" {
catch {
sql = "select 'hello'::us_postal_code"
error = "value for domain us_postal_code violates check constraint"
}
}

Re-running the test:

atlas schema test --env local

The output will be:

-- PASS: postal (565µs)
PASS

Now we can expand the test to cover more cases, such as valid postal codes and more invalid cases:

schema.test.hcl
test "schema" "postal" {
exec {
sql = "select '12345'::us_postal_code"
output = "12345" // Assert the returned value is "12345"
}
exec {
sql = "select '12345-1234'::us_postal_code"
output = "12345-1234" // Assert the returned value is "12345-1234"
}
catch {
sql = "select 'hello'::us_postal_code"
error = "value for domain us_postal_code violates check constraint"
}
catch {
sql = "select '1234'::us_postal_code"
error = "value for domain us_postal_code violates check constraint"
}
assert {
sql = "select '12345'::us_postal_code::text='12345'" // Assert the query returns true.
}
log {
message = "Hooray, testing!"
}
}

Re-running the test:

atlas schema test --env local

The output will be:

-- PASS: postal (1ms)
schema.test.hcl:21: Hooray, testing!
PASS

Let's review what happens when we run atlas schema test:

  • Atlas will apply the schema for the local environment on the dev database.
  • Atlas will search the current directory for files matching the pattern *.test.hcl.
  • For each test file found, Atlas will execute a test for each test "schema" "<name>" block.
  • Here are the possible test blocks:
    • exec - Executes a SQL statement and verifies the output.
    • catch - Executes a SQL statement and verifies that an error is thrown.
    • assert - Executes a SQL statement and verifies that the output is true.
    • log - Logs a message to the test output.

Using this modest framework, you can now write tests for your database schema, ensuring that it behaves as expected. This command can be integrated into your local development workflow or even as part of your CI pipeline further ensuring the quality of your database schema changes.

Introducing migrate test

The migrate test command allows you to write tests for your schema migrations. This is a powerful feature that enables you to test logic in your migrations in a minimal and straightforward way. The command is similar to schema test but is focused on testing migrations.

Suppose we are refactoring an existing table users which has a name column that we want to split into first_name and last_name columns. The recommended way to do this kind of refactoring in a backward-compatible way. Initially, we will be adding the new columns In Atlas DDL, the schema change would look roughly like this:

schema.hcl
table "users " {
// .. redacted
+ column "first_name" {
+ type = text
+ null = true
+ }
+ column "last_name" {
+ type = text
+ null = true
+ }
}

Next, we will use Atlas to generate a migration for this change:

atlas migrate diff --env local

A new file will be created in our migrations directory:

20240613061102.sql
-- Modify "users" table
ALTER TABLE "users" ADD COLUMN "first_name" text NULL, ADD COLUMN "last_name" text NULL;

Next, let's add the backfill logic to populate the new columns with the data from the name column:

20240613061102.sql
-- Modify "users" table
ALTER TABLE "users" ADD COLUMN "first_name" text NOT NULL, ADD COLUMN "last_name" text NOT NULL;

-- Backfill data
UPDATE "users" SET "first_name" = split_part("name", ' ', 1), "last_name" = split_part("name", ' ', 2);

After changing the contents of our migration file, we must update our atlas.sum file to reflect the changes:

atlas migrate hash --env local

Next, we will create a test case to verify that our migration works correctly in different cases. Let's add the following block to a new file named migrations.test.hcl:

migrations.test.hcl
test "migrate" "name_split" {
migrate {
// Replace with the migration version before the one we just added.
to = "20240613061046"
}
exec {
sql = "insert into users (name) values ('Ada Lovelace')"
}
migrate {
to = "20240613061102"
}
exec {
sql = "select first_name,last_name from users"
output = "Ada, Lovelace"
}
}

Let's explain what this test does:

  • We start by defining a new test case named name_split.
  • The migrate block runs migrations up to a specific version. In this case, we are running all migrations up to the version before the one we just added.
  • The exec block runs a SQL statement. In this case, we are inserting a new user with the name "Ada Lovelace".
  • Next, we run our new migration, 20240613061102.
  • Finally, we run a SQL statement to verify that the first_name and last_name columns were populated correctly.

Let's run the test:

atlas migrate test --env local

The output will be:

-- PASS: name_split (33ms)
PASS

Great, our test passed! We can now be confident that our migration works as expected.

Testing Edge Cases

With our test infra all set up, it's now easy to add more test cases to cover edge cases. For example, we can add a test to verify that our splitting logic works correctly for names that include a middle name, for example, John Fitzgerald Kennedy:

migrations.test.hcl
test "migrate" "name_split_middle_name" {
migrate {
to = "20240613061046"
}
exec {
sql = "insert into users (name) values ('John Fitzgerald Kennedy')"
}
migrate {
to = "20240613061102"
}
exec {
sql = "select first_name,last_name from users"
output = "John Fitzgerald, Kennedy"
}
}

We expect to see only the family name in the last_name column, and the rest of the name in the first_name column.

Will it work? Let's run the test:

atlas migrate test --env local --run name_split_middle_name

Our test fails:

-- FAIL: name_split_middle_name (32ms)
migrations.test.hcl:27:
Error: no match for `John Fitzgerald, Kennedy` found in "John, Fitzgerald"
FAIL

Let's improve our splitting logic to be more robust:

20240613061102.sql
-- Modify "users" table
ALTER TABLE "users" ADD COLUMN "first_name" text NULL, ADD COLUMN "last_name" text NULL;

-- Backfill data
UPDATE "users"
SET "first_name" = regexp_replace("name", ' ([^ ]+)$', ''),
"last_name" = regexp_replace("name", '^.* ', '');

We changed our splitting logic to be more robust by using regular expressions:

  • The first_name column will now contain everything before the last space in the name column.
  • The last_name column will contain everything after the last space in the name column.

Before testing our new logic, we need to update our migration hash:

atlas migrate hash --env local

Now, let's run the test again:

atlas migrate test --env local --run name_split_middle_name

The output will be:

-- PASS: name_split_middle_name (31ms)
PASS

Great! Our test passed, and we can now be confident that our migration works as expected for names with middle names.

As a final check, let's also verify that our migration works correctly for names with only one word, such as Prince:

migrations.test.hcl
test "migrate" "name_split_one_word" {
migrate {
to = "20240613061046"
}
exec {
sql = "insert into users (name) values ('Prince')"
}
migrate {
to = "20240613061102"
}
exec {
sql = "select first_name,last_name from users"
output = "Prince, "
}
}

Let's run the test:

atlas migrate test --env local --run name_split_one_word

The output will be:

-- PASS: name_split_one_word (34ms)
PASS

Amazing! Our test passed, and we can move forward with confidence.

Enhanced Editor Support

In this release, we have added support for some long-awaited features in our VSCode and JetBrains plugins:

  • Multi-file schemas - Our editor plugins will now automatically detect and load all schema files in your project, allowing you to reference tables and columns across files.
  • Jump to definition - Source code can be modeled as a graph of entities where one entity can reference another. For example a Java class method invokes a method in another class, or a table's foreign key references another table's primary key. Jump to definition allows you to navigate this graph by jumping to the definition of the entity you are interested in.
  • Support for much larger schemas - We have improved the performance of our editor plugins to support much larger schemas.

To try the latest versions, head over to the VSCode Marketplace or the JetBrains Marketplace.

Wrapping Up

That's all for this release! We hope you try out (and enjoy) all of these new features and find them useful. As always, we would love to hear your feedback and suggestions on our Discord server.

· 10 min read
TL;DR

GORM, a popular ORM for Go can easily query SQL views, but managing them has traditionally been an issue. With the latest release of Atlas GORM Provider, you can now easily manage views for your GORM application.

See an example

Introduction

Making liberal use of views is a key aspect of good SQL database design.

Postgres documentation

Views are a powerful database feature: they are virtual table representing the result of a query. Many teams use them to simplify complex queries, encapsulate logic, and present a consistent interface to users, abstracting the underlying data structures.

Using Views with ORMs

Despite their numerous benefits, views are often underutilized in many applications. Specifically, many ORMs provide partial support for views.

This is also the case with GORM, one of the most popular ORMs in Go. Let's see how GORM users can integrate views into their applications today:

First, we need to define the query that will back our view, and then use the GORM Migrator interface to create the view:

query := db.Model(&User{}).Select("id, name, age").Where("age BETWEEN 18 AND 60")

db.Migrator().CreateView("working_aged_users", gorm.ViewOption{Query: query})
// CREATE VIEW working_aged_users AS SELECT id, name, age FROM users WHERE age BETWEEN 18 AND 60

In order to be able to use GORM to query our view, we need to define an additional struct:

type WorkingAgedUser struct {
ID uint
Name string
Age int
}

Finally, we can use GORM to query records from our view:

var johnFamilies []WorkingAgedUser
db.Where("name LIKE ?", "John%").Find(&johnFamilies)
// SELECT * FROM `working_aged_users` WHERE name LIKE "John%"

Notice that this works by convention, GORM uses reflection and transforms the struct type name WorkingAgedUser to working_aged_users.

I have always felt that working with views in GORM isn't the smoothest experience. Here's why:

The "GORM way" of doing things is defining struct types and using them for everything. They serve as the foundation for modeling, querying data, and migrations. However, in my eyes, the current way of using views in GORM doesn't align with this principle. Views are defined in multiple places: the backing query, the migration step, and finally the runtime query struct.

As a GORM user, I have always wished that everything would just work from the same struct definition.

To address this challenge, our team working on the Atlas GORM provider (an Atlas plugin that enhances GORM's migration and capabilities) came up with a neat solution. Here's what it looks like:

models/models.go
// WorkingAgedUsers is mapped to the VIEW definition below.
type WorkingAgedUsers struct {
Name string
Age int
}

func (WorkingAgedUsers) ViewDef(dialect string) []gormschema.ViewOption {
return []gormschema.ViewOption{
gormschema.BuildStmt(func(db *gorm.DB) *gorm.DB {
return db.Model(&User{}).Where("age BETWEEN 18 AND 60").Select("id, name, age")
}),
}
}

The migration step is now as simple as:

main.go
gormschema.New("mysql").Load(
&models.User{}, // Table-based model.
&models.WorkingAgedUsers{}, // View-based model.
)

It is also worth mentioning that querying the view is still the same:

var johnFamilies []WorkingAgedUser
db.Where("name LIKE ?", "John%").Find(&johnFamilies)
// SELECT * FROM `working_aged_users` WHERE name LIKE "John%"

The key benefits of this approach are:

  • Alignment with GORM Philosophy: It follows the GORM (and generally ORM) principle that structs model database objects, both for schema definition and querying.
  • Unified Source of Truth: It consolidates the schema source of truth for migrations and the DB Query API in a single location - the view definition structs.

This seamless integration of views with GORM's core principles results in a more organic and holistic workflow when working with database views. In the end, it's easy to think of views as read-only tables backed by a query, and this is precisely what this API is designed for.

Demo Time!

Let's walk through a step-by-step example of using GORM Atlas Provider to automatically plan schema migrations for tables and views in a GORM project.

Installation

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

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

See atlasgo.io for more installation options.

In addition, the view feature is only available for logged-in users, run the following command to login:

atlas login

Install the provider by running:

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

Step 1: Create a GORM Application

Models are defined using normal structs. For views, we define a struct and implement the ViewDefiner interface. The ViewDef(dialect string) method receives the dialect argument to determine the SQL dialect to generate the view. It is helpful for generating the view definition for different SQL dialects if needed.

Let's create a file that will contain our database models. We will call it models/models.go

models/models.go
package models

import (
"ariga.io/atlas-provider-gorm/gormschema"
"gorm.io/gorm"
)

// User is a regular gorm.Model stored in the "users" table.
type User struct {
gorm.Model
Name string
Age int
Gender string
}

// WorkingAgedUser is mapped to the VIEW definition below.
type WorkingAgedUser struct {
Name string
Age int
}

For views, our provider provides two options for defining the view:

  • BuildStmt: allows you to define a query using the GORM API. This is useful when you need to use GORM's query building capabilities.
  • CreateStmt: allows you to define a query using raw SQL. This is useful when you need to define a complex query that GORM cannot handle.
BuildStmt

This option allows you to define the view using the GORM API. The dialect is handled automatically by GORM.

models/models.go
func (WorkingAgedUser) ViewDef(dialect string) []gormschema.ViewOption {
return []gormschema.ViewOption{
// view name will adhere to GORM's convention for table name
// which is "working_aged_users" in this case
gormschema.BuildStmt(func(db *gorm.DB) *gorm.DB {
return db.Table("users").Select("name, age").Where("age BETWEEN 18 AND 60")
}),
}
}
CreateStmt

This option gives you more flexibility to define the view using raw SQL. However, it also involves a trade-off, as you need to handle the SQL dialects yourself if you want it to work across multiple databases (e.g. switching databases, writing integration tests, etc.).

models/models.go
func (WorkingAgedUser) ViewDef(dialect string) []gormschema.ViewOption {
return []gormschema.ViewOption{
gormschema.CreateStmt(`
CREATE VIEW working_aged_users AS
SELECT
name,
age
FROM
users
WHERE
age BETWEEN 18 AND 60
`),
}
}

For demonstration purposes, we will use the CreateStmt option with the default dialect.

Step 2: Setup Atlas GORM Provider

Standalone vs Go Program mode

This feature works in both Standalone and Go Program modes:

  • Standalone: If your views and models are in the same package, you can use the provider directly to load your GORM schema into Atlas.
  • Go Program: If you have them defined in different packages, you can use the provider as a library in your Go program to load your GORM schema into Atlas.

Since all of our models are in the same package, it's pretty handy to use the Standalone mode. But if you're curious, you can also try the Go Program mode with more detail in the GORM Guide.

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

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

env "gorm" {
src = data.external_schema.gorm.url
dev = "docker://mysql/8/dev" // the dev-database needs to be mapped to the same dialect above
migration {
dir = "file://migrations"
}
format {
migrate {
diff = "{{ sql . \" \" }}"
}
}
}
Using docker://

If you use the docker:// driver for spinning up your Dev Database be sure that Docker is running locally on your machine first.

Next, to prevent the Go Modules system from dropping this dependency from our go.mod file, let's follow the Go Module's official recommendation for tracking dependencies of tools and add a file named tools.go with the following contents:

tools.go
//go:build tools
package main

import _ "ariga.io/atlas-provider-gorm/gormschema"

Alternatively, you can simply add a blank import to the models.go file we created above.

Finally, to tidy things up, run:

go mod tidy

Step 3: Generate Migrations

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

atlas migrate diff --env gorm 

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

migrations
├── 20240525153051.sql
└── atlas.sum

1 directory, 2 files

Examining the contents of 20240525153051.sql:

migrations/20240525153051.sql
-- Create "users" table
CREATE TABLE `users` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`created_at` datetime(3) NULL,
`updated_at` datetime(3) NULL,
`deleted_at` datetime(3) NULL,
`name` longtext NULL,
`age` bigint NULL,
`gender` longtext NULL,
PRIMARY KEY (`id`),
INDEX `idx_users_deleted_at` (`deleted_at`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Create "working_aged_users" view
CREATE VIEW `working_aged_users` (
`name`,
`age`
) AS select `users`.`name` AS `name`,`users`.`age` AS `age` from `users` where (`users`.`age` between 18 and 60);

Amazing! Atlas automatically generated a migration file that will create the users table and working_aged_users view in our database!

Step 4: Update the View

Next, as business requirements change, the age range is now different for each gender. Let's update the WorkingAgedUser struct and its view definition.

models/models.go
type WorkingAgedUser struct {
Name string
Age int
+ Gender string
}

func (WorkingAgedUser) ViewDef(dialect string) []gormschema.ViewOption {
return []gormschema.ViewOption{
gormschema.CreateStmt(`
CREATE VIEW working_aged_users AS
SELECT
name,
age,
+ gender
FROM
users
WHERE
- age BETWEEN 18 AND 60
+ (gender = 'male' AND age BETWEEN 18 AND 65) OR
+ (gender = 'female' AND age BETWEEN 18 AND 60)
`),
}
}

Re-run this command:

atlas migrate diff --env gorm 

Observe a new migration file is generated 🎉:

migrations
├── 20240525153051.sql
├── 20240525153152.sql
└── atlas.sum

1 directory, 3 files
migrations/20240525153152.sql
-- Modify "working_aged_users" view
CREATE OR REPLACE VIEW `working_aged_users` (
`name`,
`age`,
`gender`
) AS select `users`.`name` AS `name`,`users`.`age` AS `age`,`users`.`gender` AS `gender` from `users` where (((`users`.`gender` = 'male') and (`users`.`age` between 18 and 65)) or ((`users`.`gender` = 'female') and (`users`.`age` between 18 and 60)));

Wrapping up​

In this post, we have shown how to use Atlas to manage database schema migrations for tables and views in a GORM project. This is just one of the many features that Atlas provides for working with your database schema. Checkout the Atlas documentation for more information.

Have questions? Feedback? Find our team on our Discord server.

· 9 min read
Rotem Tamir

Hi everyone,

It's been a few weeks since the release of v0.22, and we're excited to be back with the next version of Atlas, packed with some long awaited features and improvements.

  • Redshift Support - Amazon Redshift, a fully managed, petabyte-scale data warehouse service in the cloud. Starting today, you can use Atlas to manage your Redshift Schema-as-Code.
  • CircleCI Integration - Following some recent requests from our Enterprise customers, we have added a CircleCI orb to make it easier to integrate Atlas into your CircleCI pipelines.
  • Kubernetes Operator Down Migrations - The Kubernetes Operator now detects when you are moving to a previous version and will attempt to apply a down migration if configured to do so.
  • GORM View Support - We have added support for defining SQL Views in your GORM models.
  • SQLAlchemy Provider Improvements - We have added support for defining models using SQLAlchemy Core Tables in the SQLAlchemy provider.
  • ERD v2 - We have added a new navigation sidebar to the ERD to make it easier to navigate within large schemas.
  • PostgreSQL Improvements - We have added support for PostgreSQL Event Triggers, Aggregate Functions, and Function Security.

Let's dive in!

Redshift Beta Support

Atlas's "Database Schema-as-Code" is useful even for managing small schemas with a few tables, but it really shines when you have a large schema with many tables, views, and other objects. This is the common case instead of the exception when you are dealing with Data Warehouses like Redshift that aggregate data from multiple sources.

Data warehouses typically store complex and diverse datasets consisting of hundreds of tables with thousands of columns and relationships. Managing these schemas manually can be a nightmare, and that's where Atlas comes in.

Today we are happy to announce the beta support for Amazon Redshift in Atlas. You can now use Atlas to manage your Redshift schema, generate ERDs, plan and apply changes, and more.

To get started, first install the latest version of the Atlas CLI:

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

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

Next, login to your Atlas account to activate the Redshift beta feature:

atlas login

To verify Atlas is able to connect to your Redshift database, run the following command:

atlas schema inspect --url "redshift://<username>:<password>@<host>:<port>/<database>?search_path=<schema>"

If everything is working correctly, you should see the Atlas DDL representation of your Redshift schema.

To learn more about the Redshift support in Atlas, check out the documentation.

CircleCI Integration

CircleCI is a popular CI/CD platform that allows you to automate your software development process. With this version we have added a CircleCI orb to make it easier to integrate Atlas into your CircleCI pipeline. CircleCI orbs are reusable packages of YAML configuration that condense repeated pieces of config into a single line of code.

As an example, suppose you wanted to create a CircleCI pipeline that pushes your migration directory to your Atlas Cloud Schema Registry. You can use the atlas-orb to simplify the configuration:

version: '2.1'
orbs:
atlas-orb: ariga/atlas-orb@0.0.3
workflows:
postgres-example:
jobs:
- push-dir:
context: the-context-has-ATLAS_TOKEN
docker:
- image: cimg/base:current
- environment:
POSTGRES_DB: postgres
POSTGRES_PASSWORD: pass
POSTGRES_USER: postgres
image: cimg/postgres:16.2
steps:
- checkout
- atlas-orb/setup:
cloud_token_env: ATLAS_TOKEN
version: latest
- atlas-orb/migrate_push:
dev_url: >-
postgres://postgres:pass@localhost:5432/postgres?sslmode=disable
dir_name: my-cool-project

Let's break down the configuration:

  • The push-dir job uses the cimg/postgres:16.2 Docker image to run a PostgreSQL database. This database will be used as the Dev Database for different operations performed by Atlas.
  • The atlas-orb/setup step initializes the Atlas CLI with the provided ATLAS_TOKEN environment variable.
  • The atlas-orb/migrate_push step pushes the migration directory my-cool-project to the Atlas Cloud Schema Registry.

To learn more about the CircleCI integration, check out the documentation.

Kubernetes Operator Down Migrations

The Atlas Operator is a Kubernetes operator that enables you to manage your database schemas using Kubernetes Custom Resources. In one of our recent releases, we added support for the migrate down command to the CLI. Using this command, you can roll back applied migrations in a safe and controlled way, without using pre-planned down migration scripts or manual intervention.

Starting with v0.5.0, the Atlas Operator supports down migrations as well. When you change the desired version of your database for a given AtlasMigration resource, the operator will detect whether you are moving to a previous version and will attempt to apply a down migration if you configured it to do so.

Down migrations are controlled via the new protectedFlows field in the AtlasMigration resource. This field allows you to specify the policy for down migrations. The following policy, for example, allows down migrations and auto-approves them:

apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasMigration
metadata:
name: atlasmig-mysql
spec:
protectedFlows:
migrateDown:
allow: true
autoApprove: true
# ... redacted for brevity

Alternatively, Atlas Cloud users may set the autoApprove field to false to require manual approval for down migrations. In this case, the operator will pause the migration and wait for the user to approve the down migration before proceeding:

ERD v2

When you push your migration directory to the Atlas Cloud Schema Registry, Atlas generates an ERD for your schema. The ERD is a visual representation of your schema that shows the different database objects in your schema and the relationships between them.

To make it easier to navigate within large schemas we have recently added a fresh new navigation sidebar to the ERD:

GORM View Support

GORM is a popular ORM library for Go that provides a simple way to interact with databases. The Atlas GORM provider provides a seamless integration between Atlas and GORM, allowing you to generate migrations from your GORM models and apply them to your database.

SQL Views are a powerful feature in relational databases that allow you to create virtual tables based on the result of a query. Managing views with GORM (and ORMs in general) is a notoriously clunky process, as they are normally not first-class citizens in the ORM world.

With v0.4.0, we have added a new API to the GORM provider that allows you to define views in your GORM models.

Here's a glimpse of how you can define a view in GORM:

// User is a regular gorm.Model stored in the "users" table.
type User struct {
gorm.Model
Name string
Age int
}

// WorkingAgedUsers is mapped to the VIEW definition below.
type WorkingAgedUsers struct {
Name string
Age int
}

func (WorkingAgedUsers) ViewDef(dialect string) []gormschema.ViewOption {
return []gormschema.ViewOption{
gormschema.BuildStmt(func(db *gorm.DB) *gorm.DB {
return db.Model(&User{}).Where("age BETWEEN 18 AND 65").Select("name, age")
}),
}
}

By implementing the ViewDefiner interface, GORM users can now include views in their GORM models and have Atlas automatically generate the necessary SQL to create the view in the database.

To learn more about the GORM view support, check out the documentation.

Thanks to luantranminh for contributing this feature!

SQLAlchemy Provider Improvements

The Atlas SQLAlchemy provider allows you to generate migrations from your SQLAlchemy models and apply them to your database.

With v0.2.2, we have added support for defining models using SQLAlchemy Core Tables in addition to the existing support for ORM Models.

In addition, we have decoupled the provider from using a specific SQLAlchemy release, allowing users to use any version of SQLAlchemy they prefer. This should provide more flexibility and make it easier to integrate the provider into your existing projects.

Huge thanks to vshender for contributing these improvements!

Other Improvements

On our quest to support the long tail of lesser known database features we have recently added support for the following:

PostgreSQL Event Triggers

PostgreSQL Event Triggers are a special kind of trigger. Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events.

Here are some examples of how you can use event triggers in Atlas:

# Block table rewrites.
event_trigger "block_table_rewrite" {
on = table_rewrite
execute = function.no_rewrite_allowed
}

# Filter specific events.
event_trigger "record_table_creation" {
on = ddl_command_start
tags = ["CREATE TABLE"]
execute = function.record_table_creation
}

Aggregate Functions

Aggregate functions are functions that operate on a set of values and return a single value. They are commonly used in SQL queries to perform calculations on groups of rows. PostgreSQL allows users to define custom aggregate functions using the CREATE AGGREGATE statement.

Atlas now supports defining custom aggregate functions in your schema. Here's an example of how you can define an aggregate function in Atlas:

aggregate "sum_of_squares" {
schema = schema.public
arg {
type = double_precision
}
state_type = double_precision
state_func = function.sum_squares_sfunc
}

function "sum_squares_sfunc" {
schema = schema.public
lang = PLpgSQL
arg "state" {
type = double_precision
}
arg "value" {
type = double_precision
}
return = double_precision
as = <<-SQL
BEGIN
RETURN state + value * value;
END;
SQL
}

Function Security

PostgreSQL allows you to define the security level of a function using the SECURITY clause. The SECURITY clause can be set to DEFINER or INVOKER. When set to DEFINER, the function is executed with the privileges of the user that defined the function. When set to INVOKER, the function is executed with the privileges of the user that invoked the function. This is useful when you want to create functions that execute with elevated privileges.

Atlas now supports defining the security level of functions in your schema. Here's an example of how you can define a function with SECURITY DEFINER in Atlas:

function "positive" {
schema = schema.public
lang = SQL
arg "v" {
type = integer
}
return = boolean
as = "SELECT v > 0"
security = DEFINER
}

Wrapping Up

That's all for this release! We hope you try out (and enjoy) all of these new features and find them useful. As always, we would love to hear your feedback and suggestions on our Discord server.