Announcing v0.22: Rename Detection, Table Locking Checks, and more

Hi everyone,

It's been a few weeks since our last release, and we're happy to be back with a version packed with brand new and exciting features. Here's what's inside:

• RENAME Detection - This version includes a RENAME detector that identifies ambiguous situations of potential resource renames and interactively asks the user for feedback before generating the changes.
• PostgreSQL Features
  • UNIQUE and EXCLUDE - Unique constraints and exclusion constraints were added.
  • Composite Types - Added support for composite types, which are user-defined types that represent the structure of a row.
  • Table lock checks - Eight new checks that review migration plans and alert in cases of a potential table locks in different modes.
• SQL Server Sequence Support - Atlas now supports managing sequences in SQL Server.

Let's dive in!

RENAME Detection

One of the first things we were asked when we introduced Atlas's declarative approach to schema management was, “How are you going to deal with renames?” While column and table renames are a fairly rare event in the lifecycle of a project, the question arises from the fact that it's impossible to completely disambiguate between RENAME and DROP and ADD operations. While the end schema will be the same in both cases, the actual impact of an undesired DROP operation can be disastrous.

To avoid this, Atlas now detects potential RENAME scenarios during the migration planning phase and prompts the user about their intent.

Let's see this in action.

Assume we have a users table with the column first_name, which we changed to name.

After running the atlas migrate diff command to generate a migration, we will see the following:

? Did you rename "users" column from "first_name" to "name":
▸ Yes
  No

If this was our intention, we will click "Yes" and the SQL statement will be a RENAME statement.

If we click "No", the SQL statement will drop the first_name column and create the name column instead.

PostgreSQL Features

Unique and Exclude Constraints

Now, Atlas supports declaring unique and exclude constraints in your schema.

For example, if we were to add a unique constraint on a name column, it would look similar to:

schema.hcl

# Columns only.
unique "name" {
 columns = [column.name]
}

Read more about unique constraints in Atlas here.

Exclude constraints ensure that if any two rows are compared using a specified operator, at least one of the specified conditions must hold true. This means that the constraint ensures that no two rows satisfy the specified operator at the same time.

schema.hcl

exclude "excl_speaker_during" {
  type = GIST
  on {
    column = column.speaker
    op     = "="
  }
  on {
    column = column.during
    op     = "&&"
  }
}

Composite Types

Composite Types are user-defined data types that represent a structure of a row or record. Once defined, composite types can be used to declare columns in tables or used in functions and stored procedures.

For example, let's say we have a users table where each user has an address. We can create a composite type address and add it as a column to the users table:

schema.hcl

composite "address" {
  schema = schema.public
  field "street" {
    type = text
  }
  field "city" {
    type = text
  }
}

table "users" {
  schema = schema.public
  column "address" {
    type = composite.address
  }
}

schema "public" {
  comment = "standard public schema"
}

Learn more about composite types here.

Table Locking Checks

One of the common ways in which schema migration cause production outages is when a schema change requires the database to acquire a lock on a table, immediately causing read or write operations to fail. If you are dealing with small tables, these locks might be acquired for a short time which will not be noticeable. However, if you are managing a large and busy database, these situations can lead to a full-blown system outage.

Many developers are not aware of these pitfalls only to discover them in the middle of a crisis, which is made even worse by the fact that once they happen, there's nothing you can do except quietly wait for the migration to complete.

Teams looking to improve the reliability and stability of their systems, reach out to automation to prevent human errors like these. Atlas's automatic analysis capabilities can be utilized to detect such risky changes during the CI phase of the software development lifecycle.

In this version, we have added eight new analyzers to our PostgreSQL integration that check for cases where planned migrations can lead to locking a table. Here's a short rundown of these analyzers and what they detect:

• PG104 - Adding a PRIMARY KEY constraint (with its index) acquires an ACCESS EXCLUSIVE lock on the table, blocking all access during the operation.
• PG105 - Adding a UNIQUE constraint (with its index) acquires an ACCESS EXCLUSIVE lock on the table, blocking all access during the operation.
• PG301 - A change to the column type that requires rewriting the table (and potentially its indexes) on disk.
• PG302 - Adding a column with a volatile DEFAULT value requires a rewrite of the table.
• PG303 - Modifying a column from NULL to NOT NULL requires a full table scan.
  • If the table has a CHECK constraint that ensures NULL cannot exist, such as CHECK (c > 10 AND c IS NOT NULL), the table scan is skipped, and therefore this check is not reported.
• PG304 - Adding a PRIMARY KEY on a nullable column implicitly sets them to NOT NULL, resulting in a full table scan unless there is a CHECK constraint that ensures NULL cannot exist.
• PG305 - Adding a CHECK constraint without the NOT VALID clause requires scanning the table to verify that all rows satisfy the constraint.
• PG306 - Adding a FOREIGN KEY constraint without the NOT VALID clause requires a full table scan to verify that all rows satisfy the constraint.

View a full list of all the checks here.

SQL Server Sequence Support

In SQL Server, sequences are objects that generate a sequence of numeric values according to specific properties.Sequences are often used to generate unique identifiers for rows in a table.

Atlas supports the different types of sequences. For example, a simple sequence with default values can be declared like so:

sequence "s1" {
  schema = schema.dbo
}

We can also create a sequence with a custom configuration.

sequence "s2" {
  schema    = schema.dbo
  type      = int
  start     = 1001
  increment = 1
  min_value = 1001
  max_value = 9999
  cycle     = true
}

In the example above, we have a sequence that starts at 1001, and is incremented by 1 until it reaches the maximum value of 9999. Once it reaches its maximum value, it will start over because cycle is set to true.

Another option is to create a sequence with an alias type. For example, if we were to create a sequence for Social Security Numbers, we would do the following:

sequence "s3" {
  schema    = schema.dbo
  type      = type_alias.ssn
  start     = 111111111
  increment = 1
  min_value = 111111111
}
type_alias "ssn" {
  schema = schema.dbo
  type   = dec(9, 0)
  null   = false
}

Read the docs for more information about sequences.

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.