Dynamically serving REST endpoints for database stored procedures with FastAPI

Track: Web

Type: Talk

Room: Talk Room 1

Time: Oct 06 (Fri): 09:00

Duration: 0:45

This talk covers 1 approach to making stored procedures in an internal database available as REST endpoints for easier access to internal or even external parties. Like many organisations my employer has a large set of useful stored procedures in their primary database (MS SQL Server in this case) which provide a variety of important functions to the business. It would add value to several business areas and external partners to be able to use these stored procedures but providing wide-spread direct database access has significant security and data visibility issues.

I discuss a 2 step approach to solve this - using SQLAlchemy to dynamically determine the details of the available stored procedures and then using Jinja2 to generate Pydantic models and FastAPI endpoints for each stored procedure. Jinja2 is most commonly associated with HTML templating, particularly in Django. It can however be run standalone and can generate any text, not just HTML, including in this case Python code.

I will also discuss deployment via a Docker container with the capacity to generate up-to-date endpoints when the container is spun up.

The talk should hopefully be of interest to mid-level Python developers, covering a solution to an issue that many developers have probably not needed to consider.

URLs