Generate an OpenAPI From Your Database

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, formats for securely sending a SQL query through REST, and lastly - I created some samples around building PostgREST-style APIs on top of PostgresSQL with Neon and MySQL databases. 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 (many of which are OpenAPI native, allowing you to write endpoints using your spec), OpenAPI documentation generators for public consumption (ex. Zudoku). 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 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 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:

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. 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 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 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!