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/" }}'

Customizing Output with Go Templates​

Atlas supports templating command output using Go templates, similar to tools like kubectl and docker. This lets you generate custom output at runtime. If you're new to Go templates, see the Go documentation.

Templates are evaluated against the result of a schema inspection: an object with two fields: URL (the inspected source) and Realm (the schema structure, including schemas, tables, columns, etc.). See SchemaInspect and Realm for full details.

For a full example of generating code from a schema using templates, see this GORM guide.

Template Functions​

Atlas includes several built-in template functions to help format and manipulate the output. Open the details below to see the list of available functions.

List of functions
NameDescription
fail
Stops the template execution with the given error message.
{{- if ne (len .Realm.Schemas) 1 }}
  {{- fail "expect exactly one schema" }}
{{- end }}
assert
Asserts that the condition is true, otherwise stops the template execution with the given error message.
{{- assert (eq (len .Realm.Schemas) 1) "only one schema is supported in this example" }}
lower
Converts the given string to lower case.
upper
Converts the given string to upper case.
replace
Replaces all occurrences of old in s with new.
{{- $v := replace .Realm.Name " " "_" | lower }}
trim
Trims leading and trailing whitespace from the given string.
join
Joins the elements of the given slice of strings into a single string, separated by the given separator.
splitBy
Splits the given string by the specified separator and returns a slice of strings.
trimAll
Trims leading and trailing whitespace from each string in the given slice.
hasPrefix
Checks if the given string starts with the specified prefix.
hasSuffix
Checks if the given string ends with the specified suffix.
trimPrefix
Removes the specified prefix from the given string.
trimSuffix
Removes the specified suffix from the given string.
sub
Returns the result of subtracting the second integer from the first.
add
Returns the sum of the provided integers. If no integers are provided, returns 0.
inc
Increments the given integer by 1 and returns the result.
mul
Returns the product of two integers.
div
Returns the result of dividing the first integer by the second. Returns 0 on division by zero.
mod
Returns the remainder of dividing the first integer by the second.
txtar
Parses the given string as a txtar archive and returns it as an Archive object.
{{- (include "sub-template" .) | txtar |  write }}
exec
Executes the given template with the provided context and returns the result as a trimmed string.
{{- /* A helper function-like template for generating a title-case from a database-object name. */}}
{{- define "title" }}
{{ $v := "" }}
{{- range $w := splitBy $ "_" }}
  {{- if le (len $w) 1 }}
    {{ $v = print $v (upper $w) }}
  {{- else }}
    {{ $v = print $v (upper (slice $w 0 1)) (lower (slice $w 1)) }}
  {{- end }}
{{- end }}
{{ print $v }}
{{- end }}

{{- /* Call for the title template and assign it to a variable. */}}
{{ $title := exec "title" $t.Name }}

{{- /* Call the title template and use it in the output. */}}
{{- exec "title" .Schema.Name }}
Note, unlike the include function, the exec function returns the result of the template as a trimmed strings.
include
Executes the named template with the provided context and returns the result as a string.
{{- /* A helper template for generating model definitions from an Atlas inspected schema. */}}
{{- define "models" -}}
package models

{{- range $t := (index .Realm.Schemas 0).Tables }}
{{- $name := exec "title" $t.Name }}
// {{ $name }} holds the definition of the {{ $t.Name }} table.
type {{ $name }} struct {
  {{- range $c := $t.Columns }}
  ...
  {{- end }}
}
{{- end }}
{{- end }}

{{- assert (eq (len .Realm.Schemas) 1) "only one schema is supported in this example" }}
{{- (include "models" .) | write "models.go" -}}
columnType
Returns the SQL type of the given column as a string.
{{- range $c := $t.Columns }}
  {{ - $c.Name }}: {{ columnType $c.Type }},
{{- end }}
dict
Creates a dictionary from a list of key-value pairs.
{{- $d := dict "key1" "value1" "key2" "value2" }}
{{- $value1 := get $d "key1" }}
get
Retrieves the value associated with the given key from the dictionary.
set
Sets the value for the given key in the dictionary and returns the updated dictionary.
unset
Deletes the key from the dictionary and returns the updated dictionary.
hasKey
Checks if the dictionary contains the specified key.
list
Creates a list from the provided values.
append
Appends the given values to the list and returns a new list.

Video Tutorial​

Reference​

CLI Command Reference