Skip to main content

Export Database Schema to Code

The atlas schema inspect command can read an existing schema from a live database or any supported schema format, such as HCL, SQL, or an ORM schema, and generate an equivalent representation in HCL or SQL. This lets you capture the current state of a schema as code so it can be stored, modified, or used in migration workflows in a version-controlled environment.

Split Your Schema into Multiple Files​

By default, the entire schema is written to standard output as a single file. For larger projects or cases where you want a structured layout, Atlas can automatically organize the output into multiple files and directories. This makes it easy to inspect, edit, and maintain your schema as code in version control systems.

Use the --format flag with the split and write functions to separate the schema objects into multiple files:

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

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

split​

The split function splits schema dumps into multiple files and produces a txtar formatted output. The result is then 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 Atlas will generate a directory for each schema, and subdirectories for each object type defined in that schema. Database-level objects, such as PostgreSQL extensions, will be generated in their own directory alongside the schemas directory.

Each object will be defined in its own file within the its type's directory, 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 point to the entire schema just by referencing the main.sql file (e.g., file://path/to/main.sql).

A typical output might look like:

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

Split Schema Scope​

When inspecting a specific schema, Atlas will only generate subdirectories for each object type defined in that schema.

Each object will be defined in its own file within the its type's diretory, 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 that database objects such as schemas and extensions will not be generated. Additionally, CREATE statements will not be qualified with the schema name, so you can use the generated files in a different schema set by the URL.

A typical output might look like:

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

write​

The write function takes one argument: path.

The path argument states the directory where the output files will be written. The path can be relative or absolute. 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​

Default split and write to the current directory:

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​

To see this process in action, check out our video tutorial that covers the entire process using a PostgreSQL schema and SQL-formatted output.

Configure Schema Exporters ​

Exporters provide a declarative way to configure how schema inspection results are exported. They support advanced options like custom naming conventions, file extensions, HTTP webhooks, and chaining multiple exporters.

The sql exporter writes the schema as SQL files.

ArgumentDescription
path(Required) Output path. File for single output, directory when using split_by.
indentIndentation string. Defaults to " " (two spaces). Use "\t" for tabs.
split_bySplit strategy: object creates a file per database object.
namingFile naming when splitting: lower (default), same, upper, or title.
atlas.hcl
exporter "sql" "schema" {
  path     = "schema/sql"
  split_by = object
  naming   = lower
  indent   = "  "
}

env "prod" {
  url = getenv("DB_URL")
  export {
    schema {
      inspect = exporter.sql.schema
    }
  }
}

For the complete exporter reference, see the Exporters documentation.

Next Steps​

After exporting your database schema to code, you can leverage this code in various ways to manage and evolve your database schema effectively. Here are some recommended next steps:

Versioned Migrations

Manage your schema changes through versioned migration files. Use the atlas migrate diff command to generate migrations, atlas migrate apply to apply them, and integrate Atlas into your CI/CD pipeline for safe, auditable deployments.

Declarative Migrations

Manage your schema declaratively by defining the desired state as code, and let Atlas plan and apply the changes using atlas schema apply. To review or approve changes before applying them, use atlas schema plan to pre-plan and approve migrations in advance.