Skip to content

Draft: Generate the SQL function signatures and data types from the OpenAPI

Eric Frias requested to merge generate-sql-from-openapi into develop

We have been thinking about how the rest API can be documented. PostgREST's built-in OpenAPI generator isn't powerful enough to generate good docs from the comments attached to tables & functions, plus PostgREST doesn't allow the path parameters we want in the API. It seems clear that we're going to have to write the OpenAPI spec by hand. That leaves us with the problem of keeping the API docs in sync with the SQL. The easiest way we could think of was to have a script generate the SQL code by reading the OpenAPI spec.

I have been experimenting in this branch, where I hand-wrote the OpenAPI spec for only the witness-related endpoints. It looks like we chose to rename the endpoints in pretty much the same way.

The approach here is that you would hand-write the OpenAPI spec fragments for each rest endpoint or data structure that needs documenting and place each fragment in a block comment in the SQL file just above where it's implemented. Then you can run a python script over the SQL sources that:

  • reads the OpenAPI comments and generates the code fragment that the fragment is documenting
  • combines the fragments into a single spec that can be served up to a SwaggerUI instance
  • generates a list of nginx rewrite rules that convert the rest-style calls with path parameters into the form PostgREST expects

As a quick example, to add a new enum, you would insert a block comment that has the OpenAPI YAML like:

 /** openapi:components:schemas
 hafbe_types.sort_direction:
   type: string
   enum:
     - asc
     - desc
  */

but you don't add the SQL code. Instead, you run the script which will insert a code block immediately below the comment:

 -- openapi-generated-code-begin
 DROP TYPE IF EXISTS hafbe_types.sort_direction CASCADE;
 CREATE TYPE hafbe_types.sort_direction AS ENUM (
     'asc',
     'desc'
 );
 -- openapi-generated-code-end

You shouldn't ever edit code between the comment guards, because the next time you run the script it will be overwritten.

The same applies to the endpoints, where you write the documentation, then the script writes the function signature and return type inside comment guards, then you add the body of the function.

Note: to run the full stack, you'll need this branch of haf_api_node that routes your main URL to a SwaggerUI instance and interposes an nginx container to rewrite API calls.

Merge request reports