---
title: "Generate an OpenAPI From Your Database"
description: "Learn how to use DB2OpenAPI to generate a full OpenAPI/Swagger specification from your SQL database schema."
canonicalUrl: "https://zuplo.com/learning-center/generate-openapi-from-database"
pageType: "learning-center"
authors: "adrian"
tags: "API Design, OpenAPI, Open Source, Tutorial, Node.js, TypeScript"
image: "https://cdn.zuplo.com/cdn-cgi/image/fit=crop,width=1200,height=630/www/media/posts/2024-12-12-generate-openapi-from-database/image.png"
---
I've talked a lot about the intersection of databases and APIs recently. As a
recap - I covered solutions for
[generating APIs directly from your database](/learning-center/generate-api-from-database),
formats for securely
[sending a SQL query through REST](/learning-center/sql-query-to-api-request),
and lastly - I created some samples around building PostgREST-style APIs on top
of [PostgresSQL with Neon](/learning-center/neon-postgrest-rest-api) and
[MySQL databases](/learning-center/mysql-postgrest-rest-api). The underlying
assumption of these posts is that you **don't** want to build CRUD APIs on top
of your database and would rather have them generated for you - but this comes
with some tradeoffs.

- API-autogenerators control the implementation of your API - often locking
  features behind their managed service. Many products are difficult to
  customize or improve upon once generated, and don't fit into your typical API
  development stack.
- REST entity query standards/tools like OData are nice - but adoption can be
  challenging due to limited toolset and having to educate your team on the
  standard. Additionally, users having to send queries in a complex format makes
  for a poor developer experience on public APIs.
- PostgREST-style APIs are great for internal tools, but yet again - they are
  not well suited for public APIs due to the complex syntax.

## A More Open Alternative

So - it seems like there is a tooling gap for the following desires:

- Continue using the tools my team and I are used to
- Easy to Adopt
- Public API ready

Luckily for you - there is already a format that is completely open,
interoperable, built for public APIs, and best yet - you already know it! I'm
talking about OpenAPI (fka Swagger).

If we simply generate a CRUD API definition directly from your database - you
are free to use it with
[OpenAPI compatible frameworks](/learning-center/top-20-python-api-frameworks-with-openapi)
(many of which are OpenAPI native, allowing you to write endpoints using your
spec), OpenAPI documentation generators for public consumption (ex.
[Zudoku](https://zudoku.dev/)). Of course this approach isn't as simple as an
API being generated for you, but its likely you will want to tweak the design
and implementation enough that you would have broken out of the capabilities of
those generators anyways.

## Generating OpenAPI From A Database

I was surprised to find that there was no standalone tool that generated an
OpenAPI spec directly from a database schema - so I decided to create one.
[DB2OpenAPI](https://github.com/zuplo-samples/db2openapi/) is an Open Source CLI
that converts your SQL database into an OpenAPI document, with CRUD routes,
descriptions, and JSON schema responses that match your tables' columns. It's
built using the [Sequelize](https://sequelize.org/) ORM, which supports:

- PostgreSQL
- MariaDB
- MySQL
- MSSQL
- SQLite
- Oracle

so the majority of you should be covered. Generating the OpenAPI file is very
simple - just provide the required parameters to connect to your database, and
the rest is taken care of. Here's an example of connecting to a Supabase
database:

```bash
db2openapi -t postgres -h aws-0-us-west-1.pooler.supabase.com -p 5432 -u postgres.ndizqitliqszxibppdxg -P <YOUR_DB_PASSWORD> -d postgres
```

This is especially helpful because Supabase uses PostegREST under the hood,
which
[does not support OpenAPI 3.x](https://github.com/PostgREST/postgrest/issues/932).
Note that some Postgres specific features like enums are not converted to JSON
Schema enums as Sequelize doesn't expose that information.

### Building an API From OpenAPI

This is out of scope for this blog, but here's some steps/tools I would
recommend:

1. Clean up the generated OpenAPI spec, adding in errors, parameters, etc. until
   you have a design you like.
2. Use [openapi-backend](https://openapistack.co/docs/openapi-backend/intro/) to
   build your CRUD APIs. This framework is great for ensuring your
   implementation actually matches your specification and can be layered on top
   of various NodeJS API frameworks.
3. Host your API somewhere, like Render or Digital Ocean.
4. Use [Zuplo](https://zuplo.com?utm_source=blog) as a gateway over your API -
   you can import your OpenAPI directly and it will create proxies over your
   endpoints, as well as generate a developer portal for your API.

And there you have it - you've successfully built a customizable CRUD API from
just your database!