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

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

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:

Blog ERD

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.