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
:
- MySQL
- MariaDB
- PostgreSQL
- SQLite
atlas schema inspect -u "mysql://root:pass@localhost:3306/example" > schema.hcl
Then, view the contents of the file:
schema "example" {
charset = "utf8mb4"
collate = "utf8mb4_0900_ai_ci"
}
atlas schema inspect -u "maria://root:pass@localhost:3306/example" > schema.hcl
Then, view the contents of the file:
schema "example" {
charset = "utf8mb4"
collate = "utf8mb4_0900_ai_ci"
}
atlas schema inspect -u "postgres://postgres:pass@localhost:5432/example?sslmode=disable" > schema.hcl
Then, view the contents of the file:
schema "public" {
}
atlas schema inspect -u "sqlite://file.db" > schema.hcl
Then, view the contents of the file:
schema "main" {
}
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:
Inspecting table schemas with Atlas CLI
Next, let's re-run our inspection command:
- MySQL
- MariaDB
- PostgreSQL
- SQLite
atlas schema inspect -u "mysql://root:pass@localhost:3306/example" > schema.hcl
atlas schema inspect -u "maria://root:pass@localhost:3306/example" > schema.hcl
atlas schema inspect -u "postgres://postgres:pass@localhost:5432/example?sslmode=disable" > schema.hcl
atlas schema inspect -u "sqlite://file.db" > 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.