Skip to main content

Inspecting existing schemas with Atlas

Automatic Schema Inspection​

Many projects begin with an existing database that users wish to start managing with Atlas. In this case, instead of having developers learn the Atlas Language and reverse engineer a schema definition file that precisely describes the existing database, Atlas supports automatic schema inspection.

With automatic schema inspection, users simply provide Atlas with a connection string to their target database and Atlas prints out a schema definition file in the Atlas language that they can use as the starting point for working with this database.

Login to Atlas

By default, running atlas schema inspect inspects only schemas, tables, and their associated indexes and constraints such as foreign keys and checks.

Views, materialized views, functions, procedures, triggers, sequences, domains, extensions, and additional database features are available to Atlas Pro users. To include these resources in the inspection, use the following command:

atlas login

Flags​

When using schema inspect to inspect an existing database, users may supply multiple parameters:

  • --url (-u accepted as well) - the URL of database to be inspected.
  • --schema (optional, may be supplied multiple times) - schemas to inspect within the target database.
  • --exclude (optional) - filter out resources matching the given glob pattern.
  • --include (optional) - include resources matching the given glob pattern.
  • --format (optional) - Go template to use to format the output.
  • --web (-w accepted as well) - visualize the schema as an ERD on Atlas Cloud. See an example here.

Examples​

Inspect a database​

The following commands demonstrate how to inspect the entire database, including all its schemas:

atlas schema inspect -u "mysql://localhost"

atlas schema inspect -u "mysql://user:pass@localhost:3306"

Inspect a schema​

The following commands show how to inspect a single schema:

atlas schema inspect -u "mysql://localhost/schema"

atlas schema inspect -u "mysql://user:pass@localhost:3306/schema"

Inspect multiple schemas​

The following commands show how to inspect multiple schemas:

atlas schema inspect -u "mysql://localhost" --schema schema1 --schema schema2

atlas schema inspect -u "mysql://user:pass@localhost:3306" -s schema1,schema2

Exclude Schemas​

The following commands show how to exclude schemas that match a glob pattern from the inspection:

atlas schema inspect -u "mysql://localhost" --exclude "internal"

atlas schema inspect -u "mysql://localhost" --exclude "schema_*"

Exclude Database Objects​

The following commands show how to exclude database objects that match a glob pattern from the inspection:

# Skip extensions management.
atlas schema inspect -u "postgres://localhost" --exclude "*[type=extension]"

# Exclude extension versions only.
atlas schema inspect -u "postgres://localhost" --exclude "*[type=extension].version"

Exclude Schema Resources​

The following commands show how to exclude schema resources (objects) that match a glob pattern from the inspection:

When inspecting a database (multiple schemas), the first glob pattern matches the schema name, and the second matches the object name:

# Exclude all policies and functions from the public schema.
atlas schema inspect -u "postgres://localhost:5432/database" --exclude 'public.*[type=policy|function]'

# Exclude all policies and functions that match the pattern from all schemas.
atlas schema inspect -u "postgres://localhost:5432/database" --exclude '*.*[type=policy|function]'

Exclude Tables​

The following commands show how to exclude tables that match a glob pattern from the inspection:

When inspecting a database (multiple schemas), the first glob pattern matches the schema name, and the second matches the table name:

atlas schema inspect -u "mysql://localhost" --exclude "*.prefix_*"

atlas schema inspect -u "mysql://localhost" --exclude "schema.table"

atlas schema inspect -u "mysql://localhost" --exclude "schema.t*[type=table]" --exclude "schema.e*[type=enum]"

Exclude Table Resources​

The following commands show how to exclude columns, indexes or foreign-keys that match a glob pattern from the inspection:

When inspecting a database (multiple schemas), the first glob pattern matches the schema name, and the second matches the table name:

atlas schema inspect -u "mysql://localhost" --exclude "*.*.prefix_*"

atlas schema inspect -u "mysql://localhost" --exclude "public.*.c1"

atlas schema inspect -u "mysql://localhost" --exclude "public.*.c*[type=column|index]"

Include Schemas ​

The following commands show how to include only schemas that match a glob pattern during inspection:

atlas schema inspect -u "mysql://localhost" --include "internal"

atlas schema inspect -u "mysql://localhost" --include "schema_*"

Include Schema Resources ​

The following commands show how to include only schema resources (objects) that match a glob pattern from the inspection:

When inspecting a database (multiple schemas), the first glob pattern matches the schema name, and the second matches the object name:

# Include only the policies and functions from the public schema.
atlas schema inspect -u "postgres://localhost:5432/database" --include 'public.*[type=policy|function]'

# Include only policies and functions matching the pattern from all schemas.
atlas schema inspect -u "postgres://localhost:5432/database" --include '*.*[type=policy|function]'

Include Tables Only ​

The following commands show how to include only tables that match a glob pattern from the inspection. All other resources will be excluded:

When inspecting a database (multiple schemas), the first glob pattern matches the schema name, and the second matches the table name:

atlas schema inspect -u "mysql://localhost" --include "*.prefix_*"

atlas schema inspect -u "mysql://localhost" --include "schema.table"

atlas schema inspect -u "mysql://localhost" --include "schema.t*[type=table]" --include "schema.e*[type=enum]"

Include Table Resources (Table Triggers Only) ​

The following commands show how to include only triggers that match a glob pattern from the inspection. All other resources will be excluded:

When inspecting a database (multiple schemas), the first glob pattern matches the schema name, and the second matches the table name:

atlas schema inspect -u "mysql://localhost" --include "*.*.prefix_*"

atlas schema inspect -u "mysql://localhost" --include "public.*.tg"

atlas schema inspect -u "mysql://localhost" --include "public.*.c*[type=trigger]"

SQL Format​

By default, the output of schema inspect is in the Atlas DDL. However, you can use SQL to describe the desired schema in all commands that are supported by Atlas DDL. To output the schema in SQL format, use the --format flag as follows:

atlas schema inspect -u "mysql://localhost" --format "{{ sql . }}"

JSON Format​

Atlas can output a JSON document that represents the database schema. This representation allows users to use tools like jq to analyze the schema programmatically.

atlas schema inspect -u '<url>' --format '{{ json . }}'

Visualize schemas​

Atlas can generate an Entity Relationship Diagram (ERD) for the inspected schemas. The following command shows how to generate an ERD for inspected schemas:

atlas schema inspect -u '<url>' -w

Schema ERD

Split the output into multiple files​

The schema inspect command can be used to bootstrap a new project. This is useful when you want to start managing an existing database with Atlas. Using the split and write format functions, you can split the output into multiple files and write them to a directory:

atlas schema inspect -u '<url>' --format '{{ hcl . | split | write }'

The split function splits the output into multiple files (one for each database object) and the write function writes the output to the current directory. The output files will be named according to the object name and type. This might look like:

`-- schema_main
    |-- schema.hcl
    `-- tables
        `-- t1.hcl

split​

The split function takes two optional arguments: strategy and suffix. The strategy argument controls how the output is split. The following strategies are supported:

  • object (default) - Each schema gets its own directory, a subdirectory for each object type, and a file for each object.
  • schema - Each schema gets its own file.
  • type - Each object type gets its own file.

The suffix argument controls the suffix of the output files. The default suffix is .hcl but it is recommended to use a database specific suffix for better editor plugin support, for example:

DatabaseFile Suffix
MySQL.my.hcl
MariaDB.ma.hcl
PostgreSQL.pg.hcl
SQLite.lt.hcl
ClickHouse.ch.hcl
SQL Server.ms.hcl
Redshift.rs.hcl

split outputs a txtar formatted string.

write​

The write function takes one argument: path. The path argument controls the directory where the output files will be written. The path can be a relative or absolute path. If no path is specified, the output files will be written to the current directory. The write function creates the directory if it does not exist.

Examples​

Default split (using "object" strategy) and write to the current directory:

atlas schema inspect -u '<url>' --format '{{ hcl . | split | write }}'

Split by object type and write to the "schema/" directory for PostgreSQL:

atlas schema inspect -u '<url>' --format '{{ hcl . | split "type" ".pg.hcl" | write "schema/" }}'

Split by schema and write to the schema/ directory for MySQL:

atlas schema inspect -u '<url>' --format '{{ hcl . | split "schema" ".my.hcl" | write "schema/" }}'

Loading the output files​

To work with such a directory structure, you can use the hcl_schema data source in your atlas.hcl project configuration:

data "hcl_schema" "app" {
  paths = fileset("schema/**/*.hcl")
}

env "app" {
  src = data.hcl_schema.app.url
  dev = "docker://mysql/8/example"
}

Video Tutorial​

Reference​

CLI Command Reference