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​

By using the split and write functions, you can split the output of the schema inspect command into multiple files and write them to a directory. The input for these functions can be generated by using the hcl or sql functions, which transform your schema into HCL or SQL format, respectively.

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

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

Output to a specific directory:

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

# SQL.
atlas schema inspect -u '<url>' --format '{{ sql . | split | write "dump" }'

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.

split​

The split function splits schema dumps into multiple files and produces txtar formatted output. The txtar format is not that useful by itself, but the result of it can be piped to the write function to write the output to files and directories. The API for the split function depends on the input format used, either hcl or sql:

When used with the sql function, the split function splits the SQL schema dump into multiple files and subdirectories with different formats, based on the scope you inspect - either a database or a specific schema.

Split database scope​

If you inspect a database scope with more than one schema, or need access to database-level objects like PostgreSQL extensions, Atlas will generate a directory for each schema and subdirectories for each object type defined in that schema. In addition, database-level objects such as extensions will be generated in their own directory, alongside the schemas directory.

Each generated file will contain the object definition along with atlas:import directives pointing to its dependencies. A main.sql file will also be generated as an "entry point", containing import lines for all files generated by Atlas. This allows you to easily point to the entire schema by referencing the main.sql file (e.g., file://path/to/main.sql).

A typical output might look like:

├── main.sql
├── extensions
│   ├── hstore.sql
│   └── citext.sql
└── schemas
    └── public
        ├── public.sql
        ├── tables
        │   ├── profiles.sql
        │   └── users.sql
        ├── functions
        └── types

Split schema scope​

Unlike the database scope, when inspecting a specific schema, Atlas will generate a subdirectories only for each object type defined in that schema. Each generated file will contain the object definition along with atlas:import directives pointing to its dependencies. In addition, a main.sql file will be generated as an "entry point", containing import lines for all files generated by Atlas. This allows you to easily point to the entire schema by referencing the main.sql file (e.g., file://path/to/main.sql).

Note, database objects such as schemas and extensions will not be generated. In addition, the CREATE statements will not be qualified with the schema name, allowing you to use the generated files in a different schema set by the URL.

A typical output might look like:

├── main.sql
├── tables
│   ├── profiles.sql
│   └── users.sql
├── functions
└── types

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​

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

Write to the "project/" directory:

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

Customize indentation to \t and write to the "project/" directory:

atlas schema inspect -u '<url>' --format '{{ sql . "\t" | split | write "project/" }}'

Video Tutorial​

Reference​

CLI Command Reference