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.