Skip to main content

Automatic Schema Migrations for Google Cloud Spanner with Atlas

Spanner is a fully managed, horizontally scalable, globally distributed, and strongly consistent database service offered by Google Cloud. It is designed to handle large-scale applications with high availability and low latency.

However, managing a large database schema with Spanner can be challenging due to the complexity of related data structures and the need for coordinated schema changes across multiple teams and applications.

Enter: Atlas​

Atlas helps developers manage their database schema as code, abstracting away the intricacies of database schema management. With Atlas, users provide the desired state of the database schema and Atlas automatically plans the required migrations.

In this guide, we will dive into setting up Atlas for Spanner using the declarative workflow.

note

The Spanner Driver is currently in beta and should be used with caution in production environments.

Prerequisites​

  1. Docker
  2. Google Cloud Environment set up on your machine:
  3. Atlas installed on your machine:

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

curl -sSf https://atlasgo.sh | ATLAS_VERSION="beta" sh
  1. An Atlas Pro account. To use the Spanner driver, run:
$ atlas login

Creating a Spanner Instance and Database​

info

If you want to use an existing Spanner instance and database, you can skip this step.

Let's start off by spinning up a Spanner instance using the Google Cloud Console or the gcloud command line tool.

gcloud spanner instances create my-instance \
  --config=regional-us-central1 \
  --description="My Spanner Instance" \
  --nodes=1

Next, create a Spanner database within the instance:

gcloud spanner databases create my-database \
  --instance=my-instance \
  --ddl="CREATE TABLE users (id INT64 NOT NULL, email STRING(255), display_name STRING(255)) PRIMARY KEY(id);"

This command creates a new Spanner database named my-database with a users table.

Inspecting the Schema​

The atlas schema inspect command supports reading the database description provided by a URL and outputting it in different formats, including Atlas DDL (default), SQL, and JSON. In this guide, we will demonstrate the flow using both the Atlas DDL and SQL formats, as the JSON format is often used for processing the output using jq.

To inspect our Spanner database, use the -u flag and write the output to a file named schema.hcl:

atlas schema inspect -u "spanner://projects/YOUR_PROJECT_ID/instances/my-instance/databases/my-database" \
 > schema.hcl

Open the schema.hcl file to view the Atlas schema that describes our database.

schema.hcl
table "users" {
  schema = schema.default
  column "id" {
    null = false
    type = INT64
  }
  column "email" {
    null = true
    type = STRING(255)
  }
  column "display_name" {
    null = true
    type = STRING(255)
  }
  primary_key {
    columns = [column.id]
  }
}
schema "default" {
}
info

For in-depth details on the atlas schema inspect command, covering aspects like inspecting specific schemas, handling multiple schemas concurrently, excluding tables, and more, refer to our documentation here.

Declarative Migrations​

Atlas supports a workflow called declarative schema migrations. In this workflow, you first define the desired state of your database schema (in one of many supported formats and languages). Then, you let Atlas calculate the diff between the desired state and the actual state of your database. Atlas then generates the SQL commands that will bring your database to the desired state.

Let's see this in action.

First, create a new file name schema.sql. This file will contain the desired state of our database in plain SQL.

schema.sql
-- Create "users" table
CREATE TABLE `users` (
  `id` INT64 NOT NULL,
  `email` STRING(255),
  `display_name` STRING(255)
) PRIMARY KEY (`id`);
-- Create the posts table with a custom name for the FK constraint
CREATE TABLE `posts` (
  `id` INT64 NOT NULL,
  `title` STRING(255),
  `body` STRING(MAX),
  `author_id` INT64,
  CONSTRAINT `author_fk` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`)
) PRIMARY KEY (`id`);

Applying our schema​

Next, let's apply this schema to our database using the atlas schema apply command.

atlas schema apply -u "spanner://projects/YOUR_PROJECT_ID/instances/my-instance/databases/my-database" \
  --to file://schema.sql \
  --dev-url "docker://spanner/latest" \
  --tx-mode=none
note

Spanner does not support transactions for DDL statements, so we use the --tx-mode=none flag to disable transactions.

Atlas will connect to our target database to inspect its current state. Next, it will use the dev-database to normalize our schema and generate the SQL commands that will bring our database to the desired state:

Planning migration statements (1 in total):

  -- create "posts" table:
    -> CREATE TABLE `posts` (
         `id` INT64 NOT NULL,
         `title` STRING(255),
         `body` STRING(2621440),
         `author_id` INT64,
         CONSTRAINT `author_fk` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION
       ) PRIMARY KEY (`id`)

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

Analyzing planned statements (1 in total):

  -- no diagnostics found

  -------------------------
  -- 119.9685ms
  -- 1 schema change

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

? Approve or abort the plan:
  ▸ Approve and apply
    Abort

After applying the schema, Atlas confirms that the changes were applied:

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

Applying approved migration (1 statement in total):

  -- create "posts" table
    -> CREATE TABLE `posts` (
         `id` INT64 NOT NULL,
         `title` STRING(255),
         `body` STRING(2621440),
         `author_id` INT64,
         CONSTRAINT `author_fk` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION
       ) PRIMARY KEY (`id`)
  -- ok (13.408836s)

  -------------------------
  -- 13.408947041s
  -- 1 migration
  -- 1 sql statement

Next, let's re-run the atlas schema apply command. This time, Atlas will detect that the database is already in the desired state and will not generate any changes:

Schema is synced, no changes to be made

Altering our schema​

Now, let's make some changes to our schema. Open the schema.sql file and add a new column to the users table:

schema.sql
CREATE TABLE users (
  `id` INT64 NOT NULL,
  `email` STRING(255),
  `display_name` STRING(255),
+ `bio` STRING(1024) -- New column for user biography
);

Next, let's re-run the atlas schema apply command. This time, Atlas will detect that the schema has changed and will generate the needed SQL commands to bring the database to the desired state:

Planning migration statements (1 in total):

  -- add column "bio" to table "users":
    -> ALTER TABLE `users` ADD COLUMN `bio` STRING(1024)

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

Analyzing planned statements (1 in total):

  -- no diagnostics found

  -------------------------
  -- 120.611709ms
  -- 1 schema change

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

? Approve or abort the plan:
  ▸ Approve and apply
    Abort

After applying the changes, Atlas confirms once again that the changes were applied:

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

Applying approved migration (1 statement in total):

  -- add column "bio" to table "users"
    -> ALTER TABLE `users` ADD COLUMN `bio` STRING(1024)
  -- ok (17.219869042s)

  -------------------------
  -- 17.219887625s
  -- 1 migration
  -- 1 sql statement

Visualizing our schema​

One of the most useful features of Atlas is the ability to visualize your database schema. To do so, run the atlas schema inspect command with the -w (web) flag:

atlas schema inspect -w -u "spanner://projects/YOUR_PROJECT_ID/instances/my-instance/databases/my-database"

Atlas will ask whether you would like to create your visualization publicly (in a publicly accessible URL) or privately (in your Atlas Cloud account):

? Where would you like to share your schema visualization?:
  ▸ Publicly (gh.atlasgo.cloud)
    Privately (giautm.atlasgo.cloud)

For this demo, let's choose the public option. Atlas will create the visualization and open it in your default browser:

See it for yourself at: https://gh.atlasgo.cloud/explore/0256b374

Wrapping up​

In this guide we have demonstrated how to set up Atlas to manage your Spanner database schema. We have also demonstrated some of the basic capabilities of Atlas, such as declarative schema migrations, and schema visualization. These two features are just the tip of the iceberg. Atlas has many more features that can help you better manage your database! To learn more, check out the Atlas documentation.

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