Skip to main content

Generate OpenAPI Specs from Your Database Schema with Atlas

OpenAPI specifications (specs) serve as the source-of-truth for HTTP APIs, defining how your endpoints, requests, and responses behave. Modern software development teams rely on OpenAPI specs for streamlining collaboration between the frontend and backend, automating documentation, and generating SDKs.

Keeping your specs in-line with your database schema is highly important. Since your database dictates how your data is stored while your OpenAPI spec dictates how that same data is exposed to the world, any drift between the two can lead to trouble.

Enter: Atlas​

Atlas treats your database schema as code and can generate OpenAPI output from the same definitions you use to plan and apply migrations. You declare tables, columns, and API metadata together. Atlas validates the metadata, generates SQL migrations, exports an OpenAPI 3.x spec (or any other version), and keeps everything aligned through your normal CI/CD workflow.

To get started, install Atlas:

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

curl -sSf https://atlasgo.sh | sh

Maintianing Consistency​

When schema and spec live in separate files (or separate repos), they can easily diverge. A phone column added to users might ship in a migration but never appear in the API docs. An endpoint documented in OpenAPI might reference a field that was dropped from the database months ago.

There are several common patterns for keeping APIs and databases aligned:

ApproachHow it worksDrift risk
Spec-firstDesign the OpenAPI spec, then implement handlers and migrations manually.High: implementation can lag the spec
Code-firstGenerate OpenAPI from application code (e.g. Swagger annotations in Go).Medium: spec reflects handlers, not necessarily the DB
Schema-drivenDerive API schemas from the database. Add HTTP metadata via annotations.Low: spec reflects actual tables and columns

This guide uses the schema-driven approach. Your tables are the source of truth for data shape. Annotations on those tables describe how they surface as HTTP resources (paths, methods, summaries). Go templates turn the annotated schema into OpenAPI component schemas and path definitions.

After every edit to the schema, both the database and the API specs are updated, preventing drift at the source.

Project Setup​

This guide will follow the versioned migrations workflow on a PostgreSQL database. The versioned workflow compares the desired schema against the final state of a migrations directory using a dev database, and writes the diff as SQL statements to the migrations directory in a migration file.

To learn more about the different Atlas workflows, read our Declarative vs. Versioned Migrations guide.

Create a basic versioned workflow​

We will begin with a basic schema file (schema.pg.hcl) containing just two tables:

schema.pg.hcl
schema "public" {}

table "users" {
  schema = schema.public
  column "id" {
    null = false
    type = serial
  }
  column "email" {
    null = false
    type = text
  }
  column "created_at" {
    null = false
    type = timestamptz
    default = sql("now()")
  }
  primary_key {
    columns = [column.id]
  }
}

table "posts" {
  schema = schema.public
  column "id" {
    null = false
    type = serial
  }
  column "title" {
    null = false
    type = text
  }
  column "author_id" {
    null = false
    type = int
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "fk_author" {
    columns     = [column.author_id]
    ref_columns = [table.users.column.id]
    on_delete   = CASCADE
  }
}

Then, we create the first migration file by running the following command:

atlas migrate diff create_tables \
  --dir "file://migrations" \
  --to "file://schema.pg.hcl" \
  --dev-url "docker://postgres/16/dev?search_path=public"
  • --dir: The path for the migrations directory. Since migrations does not yet exist, the "current state" defined by the directory is considered empty and a directory is created for the new migration file.
  • --to: The path to the desired schema file.
  • --dev-url: The connection string to a dev database.

For other databases, refer to our documentation for naming the schema file and configuring the URL for the dev database.

This command creates a migrations directory containing the first migration file (<version>_create_tables.sql) and a directory integrity file (atlas.sum).

-- Create "users" table
CREATE TABLE "users" (
  "id" serial NOT NULL,
  "email" text NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);
-- Create "posts" table
CREATE TABLE "posts" (
  "id" serial NOT NULL,
  "title" text NOT NULL,
  "author_id" integer NOT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "fk_author" FOREIGN KEY ("author_id") REFERENCES "users" ("id") ON UPDATE NO ACTION ON DELETE CASCADE
);

Generate OpenAPI Spec Files​

To create a migration, all we had to do was define our schema as code and Atlas did the rest. We can use the same process to generate OpenAPI specs.

Atlas provides a schema exporter that allows you to export your Atlas schema to nearly any format you'd like. In our case, we want to generate spec files from our table definitions to ensure that both our migrations and OpenAPI specs are derived from a single source of truth: the Atlas schema.

We'll use the "template" exporter for this. It lets us apply Go templates (the same syntax used by Helm, for example) to our schema objects and generate any assets we want using programmatic templates.

The Go templates utilize annotations to populate our OpenAPI specs. Annotation types and their attributes are defined in the Atlas configuration (see below), validated by Atlas at load time, and used in your Atlas schema to add customized metadata for individual tables, columns, etc.

Let's see how this all works together.

Configure atlas.hcl​

The configuration file ties together the various pieces of our setup:

  • HCL schema source: data "hcl_schema" "app" loads schema.pg.hcl as the desired database state.
  • Annotation types: annotation "table" and annotation "column" blocks define the structure of the annotations that can be attached to tables and columns, and carry the custom OpenAPI metadata we use later in our templates.
  • Template exporter: exporter "template" "openapi" is a schema exporter that executes the Go templates that write specs/openapi.yaml and per-table files under specs/schemas/.
  • Environment: env "local" encapuslates the flags used in the CLI command to create our first migration so we can connect our schema to the exporter configuration.
atlas.hcl
data "hcl_schema" "app" {
  path = "schema.pg.hcl"

  annotation "table" {
    attr "path" {
      type = string
    }
    attr "description" {
      type = string
    }
    attr "tags" {
      type = list(string)
    }
    block "operation" {
      repeatable = true
      attr "method" {
        type = string
      }
      attr "summary" {
        type = string
      }
      attr "operation_id" {
        type = string
      }
    }
  }

  annotation "column" {
    attr "description" {
      type = string
    }
    attr "example" {
      type = string
    }
    attr "format" {
      type = string
    }
  }
}

exporter "template" "openapi" {
  template {
    src  = "templates/openapi.yaml.tmpl"
    name = "specs/openapi.yaml"
  }
  template {
    src = "templates/schema.yaml.tmpl"
    on "table" {
      name = "specs/schemas/{{ .Name }}.yaml"
    }
  }
}

env "local" {
  dev = "docker://postgres/16/dev?search_path=public"
  schema {
    src = data.hcl_schema.app.url
  }
  migration {
    dir = "file://migrations"
  }
  export {
    schema {
      inspect = exporter.template.openapi
    }
  }
}
How it works
  • Atlas loads your schema and validates the table and column annotations against the types declared in atlas.hcl.
  • Atlas then executes the exporter and runs each template configured in atlas.hcl: openapi.yaml.tmpl is applied to the entire schema and schema.yaml.tmpl is applied to each table.
  • The results are written to specs/openapi.yaml and specs/schemas/<table>.yaml (for example, specs/schemas/users.yaml) respectively.

Add OpenAPI annotations​

Annotations attach API metadata to schema objects. They live in the same file as your table definitions, so reviewers see database and API changes together in every pull request.

Extend schema.pg.hcl with annotation blocks:

table "users" {
  schema = schema.public
  annotation {
    path        = "/users"
    description = "User records"
    tags        = ["users"]
    operation {
      method       = "get"
      summary      = "List all users"
      operation_id = "listUsers"
    }
    operation {
      method       = "post"
      summary      = "Create a user"
      operation_id = "createUser"
    }
  }
  column "id" {
    null = false
    type = serial
    annotation {
      description = "User ID"
      example     = "1"
    }
  }
  column "email" {
    null = false
    type = text
    annotation {
      description = "Email address"
      example     = "user@example.com"
      format      = "email"
    }
  }
  column "created_at" {
    null = false
    type = timestamptz
    default = sql("now()")
    annotation {
      description = "Creation timestamp"
      example     = "2024-01-01T00:00:00Z"
      format      = "date-time"
    }
  }
  primary_key {
    columns = [column.id]
  }
}

Annotation types and their attributes are declared in atlas.hcl. Atlas validates every attribute at load time, so typos fail immediately instead of producing a broken spec.

AnnotationPurpose
table.pathHTTP path for the resource (e.g. /users)
table.operationHTTP method, summary, and operationId for each endpoint
column.descriptionField documentation in OpenAPI component schemas
column.exampleExample value shown in generated docs
column.formatOpenAPI format (e.g. email, date-time)

Add Go templates​

Copy the Go templates into a templates/ directory:

The global template produces one specs/openapi.yaml file, looping over every table in the schema. For tables with annotations, it emits paths entries from each operation block (HTTP method, summary, operationId, tags, and a response that $refs the table schema), and it builds components.schemas from column names plus any description, example, and format annotations.

templates/openapi.yaml.tmpl
openapi: 3.2.0
info:
  title: Generated API
  version: 1.0.0
paths:
{{- range $s := .Realm.Schemas }}
{{- range $t := $s.Tables }}
{{- with $ann := attr $t "annotation" }}
  {{ $ann.Attr "path" }}:
{{- range $ann.Block "operation" }}
    {{ .Attr "method" }}:
      summary: {{ .Attr "summary" }}
      operationId: {{ .Attr "operation_id" }}
      tags: [{{ range $i, $tag := $ann.Attr "tags" }}{{ if $i }}, {{ end }}{{ $tag }}{{ end }}]
      responses:
        '200':
          description: {{ $ann.Attr "description" }}
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/{{ $t.Name }}'
{{- end }}
{{- end }}
{{- end }}
{{- end }}
components:
  schemas:
{{- range $s := .Realm.Schemas }}
{{- range $t := $s.Tables }}
    {{ $t.Name }}:
      type: object
      properties:
{{- range $c := $t.Columns }}
        {{ $c.Name }}:
{{- with $cann := attr $c "annotation" }}
          description: {{ $cann.Attr "description" }}
          example: {{ $cann.Attr "example" }}
{{- with $cann.Attr "format" }}
          format: {{ . }}
{{- end }}
{{- end }}
{{- end }}
{{- end }}
{{- end }}

Generate OpenAPI specs​

The --export flag runs the configured exporter "template" "openapi" block. The spec files are generated using the schema definition pointed to by --url.

atlas schema inspect --env local --url "env://schema.src" --export

Your project directory should now look like this:

.
├── atlas.hcl                 # Project configuration
├── schema.pg.hcl             # Desired database state + OpenAPI annotations
├── specs/
    └── schemas/
      ├── posts.yaml          # Posts table spec file
      └── users.yaml          # Users table spec file
    └── openapi.yaml          # Global spec file
└── templates/
    ├── openapi.yaml.tmpl     # Global OpenAPI template
    └── schema.yaml.tmpl      # Per-table schema template

With the following files in your specs/ directory:

openapi: 3.0.0
info:
  title: Generated API
  version: 1.0.0
paths:
  /posts:
    get:
      summary: List all posts
      operationId: listPosts
      tags: [posts]
      responses:
        '200':
          description: Blog posts
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/posts'
  /users:
    get:
      summary: List all users
      operationId: listUsers
      tags: [users]
      responses:
        '200':
          description: User records
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/users'
    post:
      summary: Create a user
      operationId: createUser
      tags: [users]
      responses:
        '200':
          description: User records
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/users'
components:
  schemas:
    posts:
      type: object
      properties:
        id:
        title:
        author_id:
    users:
      type: object
      properties:
        id:
        email:
        created_at:

Make a Schema Change​

Now that we have everything set up, let's keep the database and API schemas in sync through a schema change using Atlas.

When you add a column, update both the table definition and any relevant annotations in the same commit. For example, add a phone column to users:

schema.pg.hcl
table "users" {
  schema = schema.public
  annotation {
    path        = "/users"
    description = "User records"
    tags        = ["users"]
    operation {
      method       = "get"
      summary      = "List all users"
      operation_id = "listUsers"
    }
    operation {
      method       = "post"
      summary      = "Create a user"
      operation_id = "createUser"
    }
  }
  column "id" {
    null = false
    type = serial
  }
  column "email" {
    null = false
    type = text
  }
  column "created_at" {
    null = false
    type = timestamptz
    default = sql("now()")
  }
  column "phone" {
    null = true
    type = text
    annotation {
      description = "Phone number"
      example     = "+1-555-0100"
    }
  }
  primary_key {
    columns = [column.id]
  }
}

Since export reads the same HCL schema used for migrations, a new column in schema.pg.hcl appears in both the next migration and the regenerated OpenAPI output (specs/).

Generate a migration:

atlas migrate diff --env local add_phone_to_users

Run export whenever schema or annotations change to sync the specs:

atlas schema inspect --env local --export

The phone property is added to schemas/users.yaml and openapi.yaml:

schemas/users.yaml
users:
  type: object
  description: User records
  properties:
    phone:
      description: Phone number
      example: +1-555-0100