Skip to main content

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

Inspecting our database​

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

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

Then, view the contents of the file:

schema.hcl
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 your database command-line prompt, create the 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:

Blog ERD

Inspecting table schemas with Atlas CLI​

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

atlas schema inspect -u "mysql://root:pass@localhost:3306/example" > schema.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 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.