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 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

Video Tutorial​

Reference​

CLI Command Reference