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.
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.--format
(optional) - Go template to use to format the output.
Examples​
Inspect a database​
The following commands demonstrate how to inspect the entire database, including all its schemas:
- MySQL
- MariaDB
- PostgreSQL
- SQLite
atlas schema inspect -u "mysql://localhost"
atlas schema inspect -u "mysql://user:pass@localhost:3306"
atlas schema inspect -u "maria://localhost"
atlas schema inspect -u "maria://user:pass@localhost:3306"
atlas schema inspect -u "postgres://localhost:5432/database"
atlas schema inspect -u "postgres://postgres:pass@0.0.0.0:5432/database?sslmode=disable"
atlas schema inspect -u "sqlite://file.db"
atlas schema inspect -u "sqlite://file?cache=shared&mode=memory"
Inspect a schema​
The following commands show how to inspect a single schema:
- MySQL
- MariaDB
- PostgreSQL
atlas schema inspect -u "mysql://localhost/schema"
atlas schema inspect -u "mysql://user:pass@localhost:3306/schema"
atlas schema inspect -u "maria://localhost/schema"
atlas schema inspect -u "maria://user:pass@localhost:3306/schema"
atlas schema inspect -u "postgres://localhost:5432/database?search_path=public"
atlas schema inspect -u "postgres://postgres:pass@0.0.0.0:5432/database?search_path=public&sslmode=disable"
Inspect multiple schemas​
The following commands show how to inspect multiple schemas:
- MySQL
- MariaDB
- PostgreSQL
atlas schema inspect -u "mysql://localhost" --schema schema1 --schema schema2
atlas schema inspect -u "mysql://user:pass@localhost:3306" -s schema1,schema2
atlas schema inspect -u "maria://localhost" --schema schema1 --schema schema2
atlas schema inspect -u "maria://user:pass@localhost:3306" -s schema1,schema2
atlas schema inspect -u "postgres://localhost:5432/database" --schema schema1 --schema schema2
atlas schema inspect -u "postgres://postgres:pass@0.0.0.0:5432/database?sslmode=disable" -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 Tables​
The following commands show how to exclude tables that match a glob pattern from the inspection:
- Database Scope
- Schema Scope
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"
When inspecting a specific schema, the first glob pattern matches the table name:
atlas schema inspect -u "mysql://localhost" --exclude "prefix_*"
atlas schema inspect -u "mysql://localhost" --exclude "table"
Exclude Table Resources​
The following commands show how to exclude columns, indexes or foreign-keys that match a glob pattern from the inspection:
- Database Scope
- Schema Scope
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"
When inspecting a specific schema, the first glob pattern matches the table name:
atlas schema inspect -u "mysql://localhost" --exclude "*.prefix_*"
atlas schema inspect -u "mysql://localhost" --exclude "*.c1"
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​
Ariga's Explore tool, available in a web browser, can generate an Entity-Relationship Diagram (ERD) for the database schema. Visit gh.atlasgo.cloud/explore and follow the instructions.