Create a REST API with OpenAPI spec for your database objects

Swagger UI for my events API

Recently, I wanted to create REST API for data managed in a Db2 on Cloud database. It was needed for a chatbot project using Watson Assistant. After looking into my options, I settled on APIFlask. In this blog, I am going to share my experiences and some resources for creating a REST API with an OpenAPI specification for database objects.

Building a REST API

If you ever created your own web app, then you probably dealt with API endpoints. Typically, most requests to a web server are with the GET method ("give me that page / file"). For handling data, other methods like POST and DELETE are needed to create and delete records. All requests require defined input and output, ideally fully typed. Most web frameworks, regardless of programming language, support the implementation of a REST API. Because I had coded some previous projects in Node.js and Go, this time I looked for Python again and started with a Flask-based framework, APIFlask.

APIFlask supports the easy creation of routes, i.e., the API functions. Each can be tagged or annotated with the expected input schema (parameters and types) and the result schema. Here is the definition for a route and function to retrieve a record by its name:

# retrieve a single event record by name
@app.get('/events/name/<string:short_name>')
@app.output(EventOutSchema)
@app.auth_required(auth)
def get_event_name(short_name):
... 

It uses the GET method, the record name is passed in the URL path, it returns an event record and authentication is required. The result schema is defined like this:

# the Python output for Events
class EventOutSchema(Schema):
    eid = Integer()
    shortname = String()
    location = String()
    begindate = Date()
    enddate = Date()
    contact = String()

The function itself is short (code sample continued from above), thanks to the power of SQLAlchemy:

def get_event_name(short_name):
    """Event record by name
    Retrieve a single event record by its short name
    """
    search="%{}%".format(short_name)
    return EventModel.query.filter(EventModel.shortname.like(search)).first()

SQLAlchemy is a database toolkit for Python. It supports database systems like Db2, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, SQLite and others. Thus, to connect to Db2 on Cloud and get everything working, only the correct database URL needed to be configured.

All my other API functions have the same structure.

OpenAPI specification

One of the benefits of using APIFlask is that it has support for generating an OpenAPI specification and related documentation included. The specification can either be generated by a simple "flask spec" command or downloaded from the available API documentation pages. The latter is available with a Swagger UI (see screenshot on top) or in Redoc format. The following shows the generated documentation for the function from above:

Redoc documentation for API function

 

Conclusions

By using an API toolkit for a standard web framework I could quickly and easily build a REST API for my database objects. Connecting to Db2 required only to pass the right database URI. By containerizing the app and deploying it IBM Cloud Code Engine, I had everything ready for my custom extension for the Watson Assistant chatbot project.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.