Skip to main content

Inspecting Existing Database Schemas

Atlas provides a command-line tool to inspect existing database schemas and generate their Atlas HCL definitions. With automatic schema inspection, simply provide a connection string to the target database and Atlas will print out its schema definition.

This allows users to see the current state of their schema and save it as the source of truth, enabling them to manage their schema as code with Atlas.

Log in 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 or -u (required) - URL of the database to be inspected
  • --schema - Schemas to inspect within the target database
  • --exclude - Filter out resources matching the given glob pattern
  • --include (Pro) - Include resources matching the given glob pattern
  • --format - Specify your preferred format for the output (e.g., HCL, SQL, JSON)
  • --web or -w (Pro) - Visualize the schema as an ERD on Atlas Cloud (see an example here)

Inspect a Database​

atlas schema inspect -u "postgres://localhost:5432/database"

atlas schema inspect -u "postgres://postgres:pass@localhost:5432/database?sslmode=disable"

Inspect a Single Schema​

atlas schema inspect -u "postgres://localhost:5432/database?search_path=public"

atlas schema inspect -u "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable"

Write the Output to a File​

atlas schema inspect -u "postgres://postgres:pass@localhost:5432/database?sslmode=disable" > schema.pg.hcl

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​

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​

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​

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​

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]"
Table Type Selectors

By default, the type=table selector excludes all table types, including base tables, partitions, and foreign tables. If you want to exclude a specific type of table, such as partition or foreign_table, you can use more fine-grained selectors like type=partition or type=foreign_table. For example:

# Database scope excluding all partitions.
atlas schema inspect -u "mysql://localhost" --exclude "*.*[type=partition]"

# Schema scope excluding all foreign tables.
atlas schema inspect -u "mysql://localhost" --exclude "*[type=foreign_table]"

Exclude Table Resources​

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 ​

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 ​

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 ​

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

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