6 posts tagged with "release"

Hi everyone,

It's been a few weeks since our last version announcement and today I'm happy to share with you v0.20, which includes some big changes and exciting features:

• New Pricing Model - As we announced earlier this month, beginning March 15th the new pricing model took effect. The new pricing is usage-based, offering you more flexibility and cost efficiency. Read about what prompted this change and view the new pricing plans here.
• Django ORM Integration - Atlas now supports Django! Django is a popular ORM for Python. Developers using Django can now use Atlas to automatically plan schema migrations based on the desired state of their schema, instead of crafting them by hand.
• Support for PostgreSQL Extensions - Atlas now supports installing and managing PostgreSQL extensions.
• Dashboards in the Cloud - The dashboard (previously the 'Projects' page) got a whole new look in Atlas Cloud. Now you can view the state of your projects and environments at a glance.
• SQL Server is out of Beta - SQL Server is officially out of Beta! Along with this official support, we have included some new features:
  • User-Defined Types support for SQL Server - Atlas now supports two User-Defined Types: alias types and table types.
  • Azure Active Directory (AAD) Authentication for SQL Server - Connect to your SQL Server database using AAD Authentication.

Let’s dive in!

New Pricing Model​

As of March 15th, there is a new pricing model for Atlas users. This change is a result of feedback we received from many teams that the previous $295/month minimum was prohibitive, and a gradual, usage-based pricing model would help them adopt Atlas in their organizations.

You can read the full reasoning for the change and a breakdown of the new pricing in this blog post.

Django ORM Integration​

Django is the most popular web framework in the Python community. It includes a built-in ORM which allows users to describe their data model using Python classes. Migrations are then created using the makemigrations command, which can be applied to the database using migrate command.

Among the many ORMs available in our industry, Django's automatic migration tool is one of the most powerful and robust. It can handle a wide range of schema changes, however, having been created in 2014, a very different era in software engineering, it naturally has some limitations.

Some of the limitations of Django's migration system include:

1. Database Features - Because it was created to provide interoperability across database engines, Django's migration system is centered around the "lowest common denominator" of database features.

2. Ensuring Migration Safety - Migrations are a risky business. If you're not careful, you can easily cause data loss or a production outage. Django's migration system does not provide a native way to ensure that a migration is safe to apply.

3. Modern Deployments - Django does not provide native integration with modern deployment practices such as GitOps or Infrastructure-as-Code.

Atlas, on the other hand, lets you manage your Django applications using the Database Schema-as-Code paradigm. This means that you can use Atlas to automatically plan schema migrations for your Django project, and then apply them to your database.

Read the full guide to set up Atlas for your Django project.

Support for PostgreSQL Extensions​

Postgres extensions are add-on modules that enhance the functionality of the database by introducing new objects, such as functions, data types, operators, and more.

The support for extensions has been highly requested, so we are excited to announce that they are finally available!

To load an extension, add the extension block to your schema file. For example, adding PostGIS would look similar to:

extension "postgis" {
 schema  = schema.public
 version = "3.4.1"
 comment = "PostGIS geometry and geography spatial types and functions"
}

Read more about configuring extensions in your schema here.

Dashboards in the Cloud​

Atlas Cloud has a new and improved dashboard view!

When working with multiple databases, environments, or even projects - it becomes increasingly difficult to track and manage the state of each of these components. With Atlas Cloud, we aim to provide a single source of truth, allowing you to get a clear overview of each schema, database, environment, deployment and their respective statuses.

Once you push your migration directory to the schema registry, you will be able to see a detailed dashboard like the one shown above.

Let’s break down what we see:

• The usage calendar shows when changes are made to your migration directory via the migrate push command in CI.

• The databases show the state of your target databases. This list will be populated once you have set up deployments for your migration directory. The state of the database can be one of the following:

  • Synced - the database is at the same version as the latest version of your migration directory schema.
  • Failed - the last deployment has failed on this database.
  • Pending - the database is not up to date with the latest version of your migration directory schema.

An alternate view to this page is viewing it per environment. This way, you can see a comprehensive list of the status of each database in each environment.

SQL Server out of Beta​

We are proud to announce that SQL Server is officially supported by Atlas! Since our release of SQL Server in Beta last August, our team has been working hard to refine and stabilize its performance.

In addition, we have added two new capabilities to the SQL Server driver.

User-Defined Types Support​

In SQL Server, user-defined types (UDTs) are a way to create custom data types that group together existing data types. Atlas now supports alias types and table types.

Alias Types​

Alias types allow you to create a custom data type, which can then make your code more readable and maintainable.

For example, you might want to create an alias type email_address for the VARCHAR(100) data type. Instead of rewriting this throughout the code, and in order to maintain consistency, you can simply use email_address for clarity.

In the schema.hcl file, you would define this like so:

type_alias "email_address" {
 schema = schema.dbo
 type   = varchar(100)
 null   = false
}
table "users" {
 schema = schema.dbo
 column "email_address" {
   type = type_alias.email_address
 }
}

Table Types​

Table types allow you to define a structured data type that represents a table structure. These are particularly useful for passing sets of data between stored procedures and functions. They can also be used as parameters in stored procedures or functions, allowing you to pass multiple rows of data with a single parameter.

For example, we have a type_table to describe the structure of an address. We can declare this table and later use it in a function:

type_table "address" {
 schema = schema.dbo
 column "street" {
   type = varchar(255)
 }
 column "city" {
   type = varchar(255)
 }
 column "state" {
   type = varchar(2)
 }
 column "zip" {
   type = type_alias.zip
 }
 index {
   unique  = true
   columns = [column.ssn]
 }
 check "zip_check" {
   expr = "len(zip) = 5"
 }
}
function "insert_address" {
 schema = schema.dbo
 lang   = SQL
 arg "@address_table" {
   type     = type_table.address
   readonly = true // The table type is readonly argument.
 }
 arg "@zip" {
   type = type_alias.zip
 }
 return = int
 as     = <<-SQL
 BEGIN
   DECLARE @RowCount INT;
   INSERT INTO address_table (street, city, state, zip)
   SELECT street, city, state, zip
   FROM @address_table;

   SELECT @RowCount = @ROWCOUNT;

  RETURN @RowCount;
 END
 SQL
}
type_alias "zip" {
 schema = schema.dbo
 type   = varchar(5)
 null   = false
}

Read the documentation to learn how to use these types in Atlas.

Azure Active Directory (AAD) Authentication​

Now when using SQL Server with Atlas, instead of providing your regular database URL, you can connect to your Azure instance with Azure Active Directory Authentication.

Use the fedauth parameter to specify the AAD authentication method. For more information, see the document on the underlying driver.

To connect to your Azure instance using AAD, the URL will look similar to:

azuresql://<instance>.database.windows.net?fedauth=ActiveDirectoryDefault&database=master

Wrapping up​

That's it! I 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.

Hi everyone,

We are excited to share our latest release with you! Here's what's new:

• Pre-migration Checks: Before migrating your schema, you can now add SQL checks that will be verified to help avoid risky migrations.
• Schema Docs: Atlas lets you manage your database schema as code. One of the things we love most about code, is that because of its formal structure, it's possible to automatically generate documentation from it. With this release, we're introducing a new feature that lets you generate code-grade documentation for your database schema.
• SQL Server Trigger Support: Atlas now supports managing triggers in SQL Server.
• ClickHouse Materialized View Support: Atlas now supports managing materialized views in ClickHouse.

Let's dive in.

Pre-migration Checks​

Atlas now supports the concept of pre-migration checks, where each migration version can include a list of assertions (predicates) that must evaluate to true before the migration is applied.

For example, before dropping a table, we aim to ensure that no data is deleted and the table must be empty, or we check for the absence of duplicate values before adding a unique constraint to a table.

This is especially useful if we want to add our own specific logic to migration versions, and it helps to ensure that our database changes are safe.

To add these checks, Atlas supports a text-based file archive to describe "migration plans". Unlike regular migration files, which mainly contain a list of DDL statements (with optional directives), Atlas txtar files (currently) support two file types: migration files and pre-execution check files.

The code below presents a simple example of a pre-migration check. The default checks file is named checks.sql, and the migration.sql file contains the actual DDLs to be executed on the database in case the assertions are passed.

-- atlas:txtar

-- checks.sql --
-- The assertion below must be evaluated to true. Hence, the "users" table must not contain any rows.
SELECT NOT EXISTS(SELECT * FROM users);

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

If the pre-execution checks pass, the migration will be applied, and Atlas will report the results.

atlas migrate --dir atlas://app --env prod

Migrating to version 20240201131900 from 20240201131800 (1 migrations in total):
  -- checks before migrating version 20240201131900
    -> SELECT NOT EXISTS(SELECT * FROM users);
  -- ok (624.004µs)
  -- migrating version 20240201131900
    -> DROP TABLE users;
  -- ok (5.412737ms)
  -------------------------
  -- 22.138088ms
  -- 1 migration
  -- 1 check
  -- 1 sql statement

If the pre-execution checks fail, the migration will not be applied, and Atlas will exit with an error.

atlas migrate --dir atlas://app --env prod

Migrating to version 20240201131900 from 20240201131800 (1 migrations in total):
  -- checks before migrating version 20240201131900
    -> SELECT NOT EXISTS(SELECT * FROM internal_users);
    -> SELECT NOT EXISTS(SELECT * FROM external_users);
  -- ok (1.322842ms)
  -- checks before migrating version 20240201131900
    -> SELECT NOT EXISTS(SELECT * FROM roles);
    -> SELECT NOT EXISTS(SELECT * FROM user_roles);
    2 of 2 assertions failed: check assertion "SELECT NOT EXISTS(SELECT * FROM user_roles);" returned false
  -------------------------
  -- 19.396779ms
  -- 1 migration with errors
  -- 2 checks ok, 2 failures
Error: 2 of 2 assertions failed: check assertion "SELECT NOT EXISTS(SELECT * FROM user_roles);" returned false

To learn more about how to use pre-migration checks, read the documentation here.

Schema Docs​

One of the most surprising things we learned from working with teams on their Atlas journey, is that many teams do not have a single source of truth for their database schema. As a result, it's impossible to maintain up-to-date documentation for the database schema, which is crucial for disseminating knowledge about the database across the team.

Atlas changes this by creating a workflow that begins with a single source of truth for the database schema - the desired state of the database, as defined in code. This is what enables Atlas to automatically plan migrations, detect drift (as we'll see below), and now, generate documentation.

How it works​

Documentation is currently generated for the most recent version of your schema for migration directories that are pushed to Atlas Cloud. To generate docs for your schema, follow these steps:

1. Make sure you have the most recent version of Atlas:

   • macOS + Linux
   • Homebrew
   • Docker
   • Windows
   • Manual Installation

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

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

2. Login to Atlas Cloud using the CLI:

   atlas login
   

   If you do not already have a (free) Atlas Cloud account, follow the instructions to create one.

3. Push your migrations to Atlas Cloud:

   atlas migrate push <dir name>
   

   Be sure to replace <dir name> with the name of the directory containing your migrations. (e.g app)

4. Atlas will print a link to the overview page for your migration directory, e.g:

   https://gh.atlasgo.cloud/dirs/4294967296
   

5. Click on "Doc" in the top tabs to view the documentation for your schema.

SQL Server Trigger Support​

In version v0.17, we released trigger support for PostgreSQL, MySQL and SQLite. In this release, we have added support for SQL Server as well.

Triggers are a powerful feature of relational databases that allow you to run custom code when certain events occur on a table or a view. For example, you can use triggers to automatically update the amount of stock in your inventory when a new order is placed or to create an audit log of changes to a table. Using this event-based approach, you can implement complex business logic in your database, without having to write any additional code in your application.

Managing triggers as part of the software development lifecycle can be quite a challenge. Luckily, Atlas's database schema-as-code approach makes it easy to do!

atlas login

Let's use Atlas to build a small chunk of a simple e-commerce application:

1. Download the latest version of the Atlas CLI:

   • macOS + Linux
   • Homebrew
   • Docker
   • Windows
   • Manual Installation

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

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

2. Make sure you are logged in to Atlas:

   atlas login
   

3. Let's spin up a new SQL Server database using docker:

   docker run --rm -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=P@ssw0rd0995' -p 1433:1433 --name atlas-demo -d mcr.microsoft.com/mssql/server:latest
   

4. Next, let's define and apply the base table for our application:

   schema.hcl

   schema "dbo" {
   }
   table "grades" {
     schema = schema.dbo
     column "student_id" {
       null = false
       type = bigint
     }
     column "course_id" {
       null = false
       type = bigint
     }
     column "grade" {
       null = false
       type = int
     }
     column "grade_status" {
       null = true
       type = varchar(10)
     }
     primary_key {
       columns = [column.student_id, column.course_id]
     }
   }
   

   The grades table represents a student's grade for a specific course. The column grade_status will remain null at first, and we will use a trigger to update whether it the grade is pass or fail.

   Apply this schema on our local SQL Server instance using the Atlas CLI:

   atlas schema apply \
     --url "sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master" \
     --to "file://schema.hcl" \
     --dev-url "docker://sqlserver/2022-latest/dev?mode=schema" \
     --auto-approve
   

   This command will apply the schema defined in schema.hcl to the local SQL Server instance. Notice the --auto-approve flag, which instructs Atlas to automatically apply the schema without prompting for confirmation.

5. Now, let's define the logic to assign a grade_status using a TRIGGER. Append this definition to schema.hcl:

   schema.hcl

     trigger "after_grade_insert" {
       on = table.grades
       after {
         insert = true
       }
       as = <<-SQL
        BEGIN
          SET NOCOUNT ON;
   
         UPDATE grades
         SET grade_status = CASE
             WHEN inserted.grade >= 70 THEN 'Pass'
             ELSE 'Fail'
             END
             FROM grades
         INNER JOIN inserted ON grades.student_id = inserted.student_id and grades.course_id = inserted.course_id;
         END
       SQL
     }
   

   We defined a TRIGGER called after_grade_insert. This trigger is executed after new rows are inserted or existing rows are updated into the grades table. The trigger executes the SQL statement, which updates the grade_status column to either 'Pass' or 'Fail' based on the grade.

   Apply the updated schema using the Atlas CLI:

   atlas schema apply \
     --url "sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master" \
     --to "file://schema.hcl" \
     --dev-url "docker://sqlserver/2022-latest/dev?mode=schema" \
     --auto-approve
   

   Notice that Atlas automatically detects that we have added a new TRIGGER, and applies it to the database.

6. Finally, let's test our application to see that it actually works. We can do this by populating our database with some students' grades. To do so, connect to the SQL Server container and open a sqlcmd session.

   docker exec -it atlas-demo /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'P@ssw0rd0995'
   

   Now that a sqlcmd session is open, we can populate the items:

   INSERT INTO grades (student_id, course_id, grade, grade_status) VALUES (1, 1, 87, null);
   INSERT INTO grades (student_id, course_id, grade, grade_status) VALUES (1, 2, 99, null);
   INSERT INTO grades (student_id, course_id, grade, grade_status) VALUES (2, 2, 68, null);
   

   To exit the session write Quit.

   Now, let's check the grades table to see that the grade_status column was updated correctly:

    docker exec -it atlas-demo /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'P@ssw0rd0995' -Q "SELECT * FROM grades;"
   

   You should see the following output:

    student_id    course_id        grade   grade_status
    ---------- ------------- ----------- --------------
             1             1          87 Pass
             1             2          99 Pass
             2             2          68 Fail
    (3 rows affected)
   

   Amazing! Our trigger automatically updated the grade_status for each of the rows.

ClickHouse Materialized View Support​

A materialized view is a table-like structure that holds the results of a query. Unlike a regular view, the results of a materialized view are stored in the database and can be refreshed periodically to reflect changes in the underlying data.

atlas login

Let's see an example of how to write a materialized view in HCL for a ClickHouse database:

materialized "mat_view" {
  schema     = schema.public
  to         = table.dest
  as         = "SELECT * FROM table.src"
  depends_on = [table.src]
}

In the example above, when creating materialized views with TO [db.]table, the view will be created with the same structure as the table or view specified in the TO clause.

The engine and primary_key attributes are required if the TO clause is not specified. In this syntax, populate can be used for the first time to populate the materialized view:

materialized "mat_view" {
  schema = schema.public
  engine = MergeTree
  column "id" {
    type = UInt32
  }
  column "name" {
    type = String
  }
  primary_key {
    columns = [column.id]
  }
  as         = "SELECT * FROM table.src"
  populate   = true
  depends_on = [table.src]
}

Wrapping up​

That's it! I 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.

Hi everyone,

Thanks for joining us today for another release announcement! We have a bunch of really exciting features to share with you today, so let's get started! Here's what we'll cover:

• Drift Detection - A common source of database trouble is that the schema in your database doesn't match the schema in your code. This can happen for a variety of reasons, including manual changes to the database, or changes made by other tools. Today, we are happy to announce the availability of a new feature that lets you automatically detect these changes, and alerts you when they happen.
• SQLAlchemy Support - SQLAlchemy is a popular Python ORM. Developers using SQLAlchemy can use Atlas to automatically plan schema migrations for them, based on the desired state of their schema instead of crafting them by hand.
• VSCode ERDs - We've added a new feature to our VSCode extension that lets you visualize your database schema as an ERD diagram.
• Composite Schemas - The newly added composite_schema data source lets you combine multiple schemas into one, which is useful for managing schemas that are loaded from multiple sources or to describe applications that span multiple database schemas.

Drift Detection​

We believe, that in an ideal world, schema migrations on production databases should be done in an automated way, preferably in your CI/CD pipelines, with developers not having root access. However, we know that this is oftentimes is not the case. For this reason, it is also common to find databases which schemas differ from the ones they are supposed to have. This phenomenon, called a Schema Drift can cause a lot of trouble for a team.

Atlas now can periodically check if your deployed databases schemas match their desired state. To function correctly, this feature relies on Atlas Cloud being able to communicate to your database. As it is uncommon for databases to be directly accessible from the internet, we have added the option to run Atlas Agents in your database's network to facilitate this communication. Agents register themselves via credentials against your Atlas Cloud account and continuously poll it for work.

To learn more about how to use this feature, check out our Drift Detection Guide.

In addition, Atlas Agents enable you do use a lot more cool features, like

• Cloud mediated deployments (coming soon)
• Schema monitoring and auditing (coming soon)

SQLAlchemy Support​

Goodbye, Alembic. Hello, Atlas.​

SQLAlchemy is a popular ORM toolkit widely used in the Python community. SQLAlchemy allows users to describe their data model using its declarative-mapping feature. To actually create the underlying tables, users can use the Base.metadata.create_all method which may be sufficient during development where tables can be routinely dropped and recreated.

However, at some point, teams need more control and decide to employ the versioned migrations methodology, which is a more robust way to manage a database schema.

The native way to manage migrations with SQLAlchemy is to use the Alembic migration tool. Alembic can automatically generate migration scripts from the difference between the current state of the database and the desired state of the application.

A downside of this approach is that in order for it to work, a pre-existing database with the current version of the schema must be connected to. In many production environments, databases should generally not be reachable from developer workstations, which means this comparison is normally done against a local copy of the database which may have undergone some changes that aren't reflected in the existing migrations.

In addition, Alembic auto-generation fails to detect many kinds of changes and cannot be relied upon to generate production-ready migration scripts without routine manual intervention.

Atlas, on the other hand, can automatically plan database schema migrations for SQLAlchemy without requiring a connection to such a database and can detect almost any kind of schema change. Atlas plans migrations by calculating the diff between the current state of the database, and its desired state.

To learn how to use Atlas with SQLAlchemy, check out our SQLAlchemy Guide.

Special thanks to No'am (Miko) Tamir (who also doubles as my young brother) for his fantastic work building the prototype for this feature and to Ronen Lubin for making it production-ready.

VSCode ERDs​

Starting with v0.4.2, our VSCode Extension can now visualize your database schema as an ERD diagram. To use this feature, simply open the command palette (Ctrl+Shift+P on Windows/Linux, Cmd+Shift+P on Mac) and select Atlas: View in ERD.

Composite Schemas​

The composite_schema data source allows the composition of multiple Atlas schemas into a unified schema graph. This functionality is useful when projects schemas are split across various sources such as HCL, SQL, or application ORMs. For example, each service might have its own schema.

Referring to the url returned by this data source allows reading the entire project schemas as a single unit by any of the Atlas commands, such as migrate diff, schema apply, or schema inspect.

Usage example​

By running atlas migrate diff with the given configuration, Atlas loads the inventory schema from the SQLAlchemy schema, the graph schema from ent/schema, and the auth and internal schemas from HCL and SQL schemas defined in Atlas format. Then, the composite schema, which represents these four schemas combined, will be compared against the current state of the migration directory. In case of a difference between the two states, a new migration file will be created with the necessary SQL statements.

data "composite_schema" "project" {
  schema "inventory" {
    url = data.external_schema.sqlalchemy.url
  }
  schema "graph" {
    url = "ent://ent/schema"
  }
  schema "auth" {
    url = "file://path/to/schema.hcl"
  }
  schema "internal" {
    url = "file://path/to/schema.sql"
  }
}

env "dev" {
  src = data.composite_schema.project.url
  dev = "docker://postgres/15/dev"
  migration {
    dir = "file://migrations"
  }
}

Wrapping up​

That's it! I 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.

Hi everyone,

I hope you are enjoying the holiday season, because we are here today with the first Atlas release of 2024: v0.17. It's been only a bit over a week since our last release, but we have some exciting new features we couldn't wait to share with you:

• Trigger Support - Atlas now supports managing triggers on MySQL, PostgreSQL, MariaDB and SQLite databases.
• Improved ERDs - You can now visualize your schema's SQL views, as well as create filters to select the specific database objects you wish to see.

Without further ado, let's dive in!

Trigger Support​

atlas login

Triggers are a powerful feature of relational databases that allow you to run custom code when certain events occur on a table or a view. For example, you can use triggers to automatically update the amount of stock in your inventory when a new order is placed or to create an audit log of changes to a table. Using this event-based approach, you can implement complex business logic in your database, without having to write any additional code in your application.

Managing triggers as part of the software development lifecycle can be quite a challenge. Luckily, Atlas's database schema-as-code approach makes it easy to do!

Let's use Atlas to build a small chunk of a simple e-commerce application:

1. Download the latest version of the Atlas CLI:

   • macOS + Linux
   • Homebrew
   • Docker
   • Windows
   • Manual Installation

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

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

2. Make sure you are logged in to Atlas:

   atlas login
   

3. Let's spin up a new PostgreSQL database using docker:

   docker run --name db -e POSTGRES_PASSWORD=pass -d -p 5432:5432 postgres:16
   

4. Next, let's define and apply the base tables for our application:

   schema.hcl

    table "inventory" {
      schema = schema.public
      column "item_id" {
        null = false
        type = serial
      }
      column "item_name" {
        null = false
        type = character_varying(255)
      }
      column "quantity" {
        null = false
        type = integer
      }
      primary_key {
        columns = [column.item_id]
      }
    }
    table "orders" {
      schema = schema.public
      column "order_id" {
        null = false
        type = serial
      }
      column "item_id" {
        null = false
        type = integer
      }
      column "order_quantity" {
        null = false
        type = integer
      }
      primary_key {
        columns = [column.order_id]
      }
      foreign_key "orders_item_id_fkey" {
        columns     = [column.item_id]
        ref_columns = [table.inventory.column.item_id]
        on_update   = NO_ACTION
        on_delete   = NO_ACTION
      }
    }
   

   This defines two tables: inventory and orders. The inventory table holds information about the items in our store, and the orders table holds information about orders placed by our customers. The orders table has a foreign key constraint to the inventory table, to ensure that we can't place an order for an item that doesn't exist in our inventory.

   Apply this schema on our local Postgres instance using the Atlas CLI:

   atlas schema apply \
      --dev-url 'docker://postgres/16?search_path=public' \
      --to file://schema.hcl \
      -u 'postgres://postgres:pass@:5432/postgres?search_path=public&sslmode=disable' \
      --auto-approve
   

   This command will apply the schema defined in schema.hcl to the local Postgres instance. Notice the --auto-approve flag, which instructs Atlas to automatically apply the schema without prompting for confirmation.

5. Let's now populate our database with some inventory items. We can do this using the psql command that is installed inside the default PostgreSQL Docker image:

   docker exec -it db psql -U postgres -c "INSERT INTO inventory (item_name, quantity) VALUES ('Apple', 10);"
   docker exec -it db psql -U postgres -c "INSERT INTO inventory (item_name, quantity) VALUES ('Banana', 20);"
   docker exec -it db psql -U postgres -c "INSERT INTO inventory (item_name, quantity) VALUES ('Orange', 30);"
   

6. Now, let's define the business logic for our store using a FUNCTION and a TRIGGER. Append these definitions to schema.hcl:

   schema.hcl

    function "update_inventory" {
      schema = schema.public
      lang   = PLpgSQL
      return = trigger
      as     = <<-SQL
      BEGIN
          UPDATE inventory
          SET quantity = quantity - NEW.order_quantity
          WHERE item_id = NEW.item_id;
          RETURN NEW;
      END;
      SQL
    }
    trigger "after_order_insert" {
      on = table.orders
      after {
        insert = true
      }
      foreach = ROW
      execute {
        function = function.update_inventory
      }
    }
   

   We start by defining a FUNCTION called update_inventory. This function is written in PL/pgSQL, the procedural language for PostgreSQL. The function accepts a single argument, which is a TRIGGER object. The function updates the inventory table to reflect the new order, and then returns the NEW row, which is the row that was just inserted into the orders table.

   Next, we define a TRIGGER called after_order_insert. This trigger is executed after a new row is inserted into the orders table. The trigger executes the update_inventory function for each row that was inserted.

   Apply the updated schema using the Atlas CLI:

   atlas schema apply \
      --dev-url 'docker://postgres/16?search_path=public' \
      --to file://schema.hcl \
      -u 'postgres://postgres:pass@:5432/postgres?search_path=public&sslmode=disable' \
      --auto-approve
   

   Notice that Atlas automatically detects that we have added a new FUNCTION and a new TRIGGER, and applies them to the database.

7. Finally, let's test our application to see that it actually works. We can do this by inserting a new row into the orders table:

   docker exec -it db psql -U postgres -c "INSERT INTO orders (item_id, order_quantity) VALUES (1, 5);"
   

   This statement creates a new order for 5 Apples.

   Now, let's check the inventory table to see that the order was processed correctly:

   docker exec -it db psql -U postgres -c "SELECT quantity FROM inventory WHERE item_name='Apple';"
   

   You should see the following output:

    quantity
    ---------
            5
   (1 row)
   

   Amazing! Our trigger automatically detected the creation of a new order of apples, and updated the inventory accordingly from 10 to 5.

Improved ERDs​

One of the most frequently used capabilities in Atlas is schema visualization. Having a visual representation of your data model can be helpful as it allows for easier comprehension of complex data structures, and enables developers to better understand and collaborate on the data model of the application they are building.

Visualizing Database Views​

Until recently, the ERD showed schema's tables and the relations between them. With the most recent release, the ERD now visualizes database views!

Within each view you can find its:

• Columns - the view's columns, including their data types and nullability.
• Create Statement - the SQL CREATE statement, based on your specific database type.
• Dependencies - a list of the tables (or other views) it is connected to. Clicking on this will map edges to each connected object in the schema.

As of recently (including this release), we have added support for functions, stored procedures and triggers which are all coming soon to the ERD!

To play with a schema that contains this feature, head over to the live demo.

ERD Filters​

In cases where you have many database objects and prefer to focus in on a specific set of tables and views, you can narrow down your selection by creating a filter. Filters can be saved for future use. This can be great when working on a feature that affects a specific part of the schema, this way you can easily refer to it as needed.

Wrapping up​

That's it! I 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.

Hi everyone,

It's been a while since our last version announcement and today I'm happy to share with you v0.16, which includes some very exciting improvements for Atlas:

• ClickHouse Beta Support - ClickHouse is a high-performance, columnar database optimized for analytics and real-time query processing. Support for ClickHouse in Atlas has been one of the top requested features by our community in the past year. Today, we are happy to announce that ClickHouse is officially in Beta!
• Hibernate Provider - Atlas now supports loading the desired state of your database directly from your Hibernate code. Hibernate developers can now join developers from the GORM, Sequelize, TypeORM and more communities who can now use Atlas to manage their database schema.
• Baseline Schemas - In some cases, your migrations rely on certain database objects to exist apriori to your application schema, for example extensions or legacy tables. Atlas now supports defining a baseline schema which will be loaded before automatically planning and applying your migrations.
• Proactive conflict detection - Teams that have connected their project to Atlas Cloud will get a prompt in the CLI if their migration directory is out of sync with the latest version in Atlas Cloud. This ensures that new migration files are added in a sequential order, preventing unexpected behavior.
• Mermaid Support - Atlas now supports generating a Mermaid diagram of your database schema. This is a great way to visualize your database schema and share it with your team.
• Review Policies - Users working with declarative migrations can now define "review policies" which can define thresholds for which kinds of changes require human review and which can be auto-applied.
• Postgres Sequences - Another long awaited feature, Atlas now supports managing sequences in PostgreSQL.

I know that's quite a list, so let's dive right in!

ClickHouse Support​

ClickHouse is a high-performance, columnar database optimized for analytics and real-time query processing. Support for ClickHouse in Atlas has been one of the top requested features by our community in the past year. Our team has been working hard to bring this feature to you and today we are happy to announce that ClickHouse is now available to use in Beta!

Here's what you need to do to get started:

1. Log in to your Atlas Cloud account. If you don't have an account yet, you can sign up for free.

2. Download the latest version of the Atlas CLI:

   • macOS + Linux
   • Homebrew
   • Docker
   • Windows
   • Manual Installation

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

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

3. Log in to your Atlas Cloud account from the CLI:

   atlas login
   

4. Spin up a local ClickHouse instance:

   docker run -d --name clickhouse-sandbox -p 9000:9000 -d clickhouse/clickhouse-server:latest
   

5. Verify that you are able to connect to this instance:

   atlas schema inspect -u 'clickhouse://localhost:9000'
   

   If everything is working correctly, you should see the following output:

    schema "default" {
      engine = Atomic
    }
   

6. Create a new file named schema.hcl with the following content:

    schema "default" {
     engine = Atomic
    }
   
    table "users" {
      schema = schema.default
      engine = MergeTree
      column "id" {
         type = UInt32
      }
      column "name" {
        type = String
      }
      column "created" {
        type = DateTime
      }
      primary_key {
        columns = [column.id]
      }
    }
   

7. Run the following command to apply the schema to your local ClickHouse instance:

    atlas schema apply -u 'clickhouse://localhost:9000' -f schema.hcl
   

   Atlas will prompt you to confirm the changes:

    -- Planned Changes:
    -- Create "users" table
    CREATE TABLE `default`.`users` (
      `id` UInt32,
      `name` String,
      `created` DateTime
    ) ENGINE = MergeTree
     PRIMARY KEY (`id`) SETTINGS index_granularity = 8192;
   

   Hit "Enter" to apply the changes.

8. Amazing! Our schema has been applied to the database!

Hibernate Provider​

Atlas now supports loading the desired state of your database directly from your Hibernate code. Packaged as both a Maven and Gradle plugin, the Hibernate provider allows you seamlessly integrate Atlas into your existing Hibernate project.

Hibernate ships with an automatic schema management tool called hbm2ddl. Similarly to Atlas, this tool can inspect a target database and automatically migrate the schema to the desired one. However, the Hibernate team has been advising for years not to use this tool in production:

Although the automatic schema generation is very useful for testing and prototyping purposes, in a production environment, it’s much more flexible to manage the schema using incremental migration scripts.

This is where Atlas comes in. Atlas can read Hibernate schema and plan database schema migrations.

To get started, refer to the blog post we published earlier this week.

Baseline Schemas​

atlas login

In some cases, there is a need to configure a baseline schema for the dev database so that every computation using the dev-database starts from this baseline. For example, users' schemas or migrations rely on objects, extensions, or other schema resources that are not managed by the project.

To configure such a baseline, use the docker block with the relevant image and pass to it the script for creating the base schema for the project:

docker "postgres" "dev" {
  image  = "postgres:15"
  schema = "public"
  baseline = <<SQL
   CREATE SCHEMA "auth";
   CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA "auth";
   CREATE TABLE "auth"."users" ("id" uuid NOT NULL DEFAULT auth.uuid_generate_v4(), PRIMARY KEY ("id"));
  SQL
}

env "local" {
  src = "file://schema.pg.hcl"
  dev = docker.postgres.dev.url
}

For more details refer to the documentation.

Proactive conflict detection​

Teams that have connected their project to Atlas Cloud (see setup) will get a prompt in the CLI if their migration directory is out of sync with the latest version in Atlas Cloud. This ensures that new migration files are added in a sequential order, preventing unexpected behavior. For example:

atlas migrate diff --env dev

? Your directory is outdated (2 migrations behind). Continue or Abort:
▸ Continue (Rebase later)
  Abort (Pull changes and re-run the command)

Additionally, the atlas migrate lint command helps enforce this requirement during the CI stage. Learn more on how to integrate Atlas into your GitHub Actions or GitLab CI pipelines.

Mermaid Support​

Atlas now supports generating a Mermaid diagram of your database schema. Let's demonstrate this feature using an example schema for a local SQLite database. First, we'll create a new file named sqlite.hcl with the following content:

schema "default" {
}

table "users" {
  schema = schema.default
  column "id" {
    type = int
  }
  column "name" {
    type = text
  }
  column "email" {
    type = text
  }
  primary_key {
    columns = [column.id]
  }
}

table "blog_posts" {
  schema = schema.default
  column "id" {
    type = int
  }
  column "title" {
    type = text
  }
  column "body" {
    type = text
  }
  column "author_id" {
    type = int
  }
  foreign_key "blog_author" {
    columns = [column.author_id]
    ref_columns = [table.users.column.id]
  }
}

Run the following command to inspect the schema and generate the Mermaid code:

atlas schema inspect -u file://sqlite.hcl --dev-url 'sqlite://?mode=memory' --format "{{ mermaid . }}"

The output will look like this:

erDiagram
    users {
      int id PK
      text name
      text email
    }
    blog_posts {
      int id
      text title
      text body
      int author_id
    }
    blog_posts }o--o| users : blog_author

Next, copy this output and paste it into the Mermaid Live Editor.

The result should look like this:

Review Policies​

Users working with declarative migrations can now define "review policies" which can define thresholds for which kinds of changes require human review and which can be auto-applied.

By default, when running atlas schema apply on a target database, if any changes to the target database are required, Atlas will prompt the user to confirm the changes. This is a safety measure to prevent accidental changes to the target database.

However, Atlas ships with an analysis engine that can detect the impact of different changes to the target database. For example, Atlas can detect irreversible destructive changes that will result in data loss or data dependent changes that may fail due to data integrity constraints.

With review policies, you can tell Atlas to first analyze the proposed changes and only prompt the user if the changes are above a certain risk threshold. For example, you can configure Atlas to only ask for review if any warnings are found and to automatically apply all changes that do not trigger any diagnostics:

lint {
  review = WARNING
}

You can see a live demonstration of this feature towards the end of our recent HashiCorp conference talk.

Postgres Sequences​

atlas login

The sequence block allows defining a sequence number generator. Supported by PostgreSQL.

Note, a sequence block is printed by Atlas on inspection, or it may be manually defined in the schema only if it represents a PostgreSQL sequence that is not implicitly created by the database for identity or serial columns.

# Simple sequence with default values.
sequence "s1" {
  schema = schema.public
}

# Sequence with custom configuration.
sequence "s2" {
  schema    = schema.public
  type      = smallint
  start     = 100
  increment = 2
  min_value = 100
  max_value = 1000
}

# Sequence that is owned by a column.
sequence "s3" {
  schema  = schema.public
  owner   = table.t2.column.id
  comment = "Sequence with column owner"
}

# The sequences created by this table are not printed on inspection.
table "users" {
  schema = schema.public
  column "id" {
    type = int
    identity {
        generated = ALWAYS
        start = 10000
    }
  }
  column "serial" {
    type = serial
  }
  primary_key  {
    columns = [column.id]
  }
}

table "t2" {
  schema = schema.public
  column "id" {
    type = int
  }
}

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

Wait, there's more!​

A few other notable features shipped in this release are:

• Analyzers for detecting blocking enum changes on MySQL. Certain kinds of changes to enum columns on MySQL tables change the column type and require a table copy. During this process, the table is locked for write operations which can cause application downtime.

  Atlas now ships with analyzers that can detect such changes and warn the user before applying them. For more information see the documentation for analyzers MY111, MY112 and MY113.

• The external data source - The external data source allows the execution of an external program and uses its output in the project.

  For example:

  atlas.hcl

  data "external" "dot_env" {
    program = [
      "npm",
      "run",
      "load-env.js"
    ]
  }
  
  locals {
    dot_env = jsondecode(data.external.dot_env)
  }
  
  env "local" {
    src = local.dot_env.URL
    dev = "docker://mysql/8/dev"
  }
  

Wrapping up​

That's it! I 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.

Introduction​

Today we are very excited to announce the release of Atlas Terraform Provider v0.4.0. This release brings some exciting new features and improvements to the provider which we will describe in this post.

In addition, this release is the first to be published under our new partnership with HashiCorp as a Technology Partner. Atlas is sometimes described as a "Terraform for Databases", so we have high hopes that this partnership will help us to bring many opportunities to create better ways for integrating database schema management into IaC workflows.

What's new​

When people first hear about integrating schema management into declarative workflows, many raise the concern that because making changes to the database is a high-risk operation, they would not trust a tool to do it automatically.

This is a valid concern, and this release contains three new features that we believe will help to address it:

• SQL plan printing
• Versioned migrations support
• Migration safety verification

SQL Plan Printing​

In previous versions of the provider, we displayed the plan as a textual diff showing which resources are added, removed or modified. With this version, the provider will also print the SQL statements that will be executed as part of the plan.

For example, suppose we have the following schema:

schema "market" {
 charset = "utf8mb4"
 collate = "utf8mb4_0900_ai_ci"
 comment = "A schema comment"
}

table "users" {
 schema = schema.market
 column "id" {
   type = int
 }
 column "name" {
   type = varchar(255)
 }
 primary_key {
   columns = [
     column.id
   ]
 }
}

And our Terraform module looks like this:

terraform {
 required_providers {
   atlas = {
     source  = "ariga/atlas"
     version = "0.4.0"
   }
 }
}

provider "atlas" {}

data "atlas_schema" "market" {
 src        = file("${path.module}/schema.hcl")
 dev_db_url = "mysql://root:pass@localhost:3307"
}

resource "atlas_schema" "market" {
 hcl        = data.atlas_schema.market.hcl
 url        = "mysql://root:pass@localhost:3306"
 dev_db_url = "mysql://root:pass@localhost:3307"
}

When we run terraform plan we will see the following output:

Plan: 1 to add, 0 to change, 0 to destroy.
╷
│ Warning: Atlas Plan
│
│   with atlas_schema.market,
│   on main.tf line 17, in resource "atlas_schema" "market":
│   17: resource "atlas_schema" "market" {
│
│ The following SQL statements will be executed:
│
│
│ -- add new schema named "market"
│ CREATE DATABASE `market`
│ -- create "users" table
│ CREATE TABLE `market`.`users` (`id` int NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`)) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci
│

Versioned migrations​

Atlas supports two types of workflows: Declarative and Versioned. With declarative workflows, the plan to migrate the database is generated automatically at runtime. Versioned migrations provide teams with a more controlled workflow where changes are planned, checked-in to source control and reviewed ahead of time. Until today, the Terraform provider only supported the declarative workflow. This release adds support for versioned migrations as well.

Suppose we have the following migration directory of two files:

CREATE TABLE `users` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `age` bigint(20) NOT NULL,
 `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `age` (`age`)
);

h1:OlaV3+7xXEWc1uG/Ed2zICttHaS6ydHZmzI7Hpf2Fss=
20221101163823_create_users.sql h1:mZirkpXBoLLm+M73EbHo07muxclifb70fhWQFfqxjD4=

We can use the Terraform Atlas provider to apply this migration directory to a database:

terraform {
 required_providers {
   atlas = {
     source  = "ariga/atlas"
     version = "0.4.0"
   }
 }
}

provider "atlas" {}

// The `atlas_migration` data source loads the current state of the given database
// with regard to the migration directory.
data "atlas_migration" "hello" {
 dir = "migrations?format=atlas"
 url = "mysql://root:pass@localhost:3306/hello"
}

// The `atlas_migration` resource applies the migration directory to the database.
resource "atlas_migration" "hello" {
 dir     = "migrations?format=atlas"
 version = data.atlas_migration.hello.latest # Use latest to run all migrations
 url     = data.atlas_migration.hello.url
 dev_url = "mysql://root:pass@localhost:3307/test"
}

Running terraform plan will show the following output:

data.atlas_migration.hello: Reading...
data.atlas_migration.hello: Read complete after 0s [id=migrations?format=atlas]

Terraform used the selected providers to generate the following execution plan.
Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # atlas_migration.hello will be created
  + resource "atlas_migration" "hello" {
      + dev_url = (sensitive value)
      + dir     = "migrations?format=atlas"
      + id      = (known after apply)
      + status  = (known after apply)
      + url     = (sensitive value)
      + version = "20221101163823"
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Linting​

Atlas provides extensive support for linting database schemas. This release adds support for linting schemas as part of the Terraform plan. This means that you can now run terraform plan and see if there are any linting errors in your schema. This is especially useful when you are using the versioned migrations workflow, as you can now run terraform plan to see if there are any linting errors in your schema before you apply the changes.

Suppose we add the following migration:

ALTER TABLE users
 DROP KEY age,
 ADD CONSTRAINT NAME UNIQUE (`name`);

If we run terraform plan on the above schema, Terraform prints the following warning:

╷
│ Warning: data dependent changes detected
│
│   with atlas_migration.hello,
│   on main.tf line 20, in resource "atlas_migration" "hello":
│   20: resource "atlas_migration" "hello" {
│
│ File: 20221101165036_change_unique.sql
│
│ - MF101: Adding a unique index "NAME" on table "users" might fail in case column
│ "name" contains duplicate entries
╵

Atlas detected that the migration may fail in case the column name contains duplicate entries! This is a very useful warning that can help you avoid unpredicted failed deployments. Atlas supports many more safety checks, which you can read about here.

Wrapping up​

In this blogpost we have discussed three new features that were added to the Terraform Atlas provider that are designed to make it safer and more predictable to manage your database schemas with Terraform. We hope you will enjoy this release!

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