Inspecting Schemas

Atlas features a Data Definition Language (DDL) that has an HCL-syntax for defining the desired state of database schemas. In this section we will learn how to use the Atlas CLI to inspect an existing database and write it's schema in HCL to a file.

Inspection is done via the atlas schema inspect command. To learn about its parameters, run:

atlas schema inspect --help

View the output:

`atlas schema inspect` connects to the given database and inspects its schema.
It then prints to the screen the schema of that database in Atlas DDL syntax. This output can be
saved to a file, commonly by redirecting the output to a file named with a ".hcl" suffix:

  atlas schema inspect -u "mysql://user:pass@localhost:3306/dbname" > atlas.hcl

This file can then be edited and used with the `atlas schema apply` command to plan
and execute schema migrations against the given database. In cases where users wish to inspect
all multiple schemas in a given database (for instance a MySQL server may contain multiple named
databases), omit the relevant part from the url, e.g. "mysql://user:pass@localhost:3306/".
To select specific schemas from the databases, users may use the "--schema" (or "-s" shorthand)
flag.

Usage:
  atlas schema inspect [flags]

Examples:
  atlas schema inspect -u "mysql://user:pass@localhost:3306/dbname"
  atlas schema inspect -u "mariadb://user:pass@localhost:3306/" --schema=schemaA,schemaB -s schemaC
  atlas schema inspect --url "postgres://user:pass@host:port/dbname?sslmode=disable"
  atlas schema inspect -u "sqlite://file:ex1.db?_fk=1"

Flags:
      --addr string      Used with -w, local address to bind the server to (default ":5800")
  -h, --help             help for inspect
  -s, --schema strings   Set schema name
  -u, --url string       [driver://username:password@protocol(address)/dbname?param=value] Select data source using the url format
  -w, --web              Open in a local Atlas UI

info

If you're using PostgreSQL, there is additional documentation about building the connection string

Inspecting our database

To inspect our locally-running MySQL instance from the previous section, use the -d flag and write output to a file named atlas.hcl:

atlas schema inspect -d "mysql://root:pass@localhost:3306/example" > atlas.hcl

To view the contents of the created file:

cat atlas.hcl

View the output:

schema "example" {
  charset = "utf8mb4"
  collate = "utf8mb4_0900_ai_ci"
}

As you can see, Atlas inspected our (empty) database and wrote an Atlas HCL document containing only a Schema resource.

Next, let's create some tables in our SQL database and see how they are reflected in the inspected Atlas HCL document.

Modifying our database schema manually

In our MySQL command-line prompt, let's create two tables:

CREATE table users (
    id int PRIMARY KEY,
    name varchar(100)
);
CREATE TABLE blog_posts (
    id int PRIMARY KEY,
    title varchar(100),
    body text,
    author_id int,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

Observe that the tables are created successfully:

Query OK, 0 rows affected (0.02 sec)

Our schema represents a highly simplified blogging system with a users table for the authors and a blog_posts table for the contents:

Inspecting table schemas with Atlas CLI

Next, let's re-run our inspection command:

atlas schema inspect -d "mysql://root:pass@localhost:3306/example" > atlas.hcl

Browse through the updated contents of the file, it contains 3 blocks representing our schema as before, and two new blocks representing the users and blog_posts tables. Consider the following block:

table "users" {
  schema = schema.example
  column "id" {
    null = false
    type = int
  }
  column "name" {
    null = true
    type = varchar(100)
  }
  primary_key {
    columns = [column.id]
  }
}

This block represents a Table resource with an id, and name columns. The schema field references the example schema that is defined elsewhere in this document. In addition, the primary_key sub-block defines the id column as the primary key for the table. In SQL databases, columns usually have a type attribute that defines the kind of data that can be stored in that column. Atlas strives to mimic the syntax of the database that the user is working against. In this case, the type for the id column is int, and varchar(100) for the name column. To see the full list of supported types, click here.

Next, consider this block:

table "blog_posts" {
  schema = schema.example
  column "id" {
    null = false
    type = int
  }
  column "title" {
    null = true
    type = varchar(100)
  }
  column "body" {
    null = true
    type = text
  }
  column "author_id" {
    null = true
    type = int
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "blog_posts_ibfk_1" {
    columns     = [column.author_id]
    ref_columns = [table.users.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
  index "author_id" {
    unique  = false
    columns = [column.author_id]
  }
}

This block represents the blog_posts table. In addition to the elements we saw in the users table, here we can find a Foreign Key block, declaring that the author_id column references the id column on the users table.

To learn more about the resource types that are available to describe SQL schemas, read the SQL Syntax documentation.

In the next section, we will see how we can modify our database's schema by applying a modified Atlas HCL file using the Atlas CLI.