API 27 september 2023 · door Stefan van der Weide · 8 min

Een database ontsluiten? Een verkenning van geautomatiseerde API-generatie

Het scenario

Stel je voor dat je een database hebt met een groot aantal schema’s, en elk van die schema’s heeft een groot aantal tabellen. Op een dag komt je baas langs en vraagt je om een CRUD-API te maken voor de hele database, wat doe je dan? Je wilt al deze routes niet met de hand schrijven, er is immers veel overlap.

Dit was precies de premisse van de laatste (publieke) Hackathon die we op ons kantoor organiseerden. Hoe kunnen we een “zo compleet mogelijke” API genereren met alleen een PostgreSQL-database als gegeven. Dit is een verslag van de verschillende oplossingen die de deelnemers bedachten.

De opzet

Voor de hackathon hebben we de Microsoft AdventureWorks-dataset uit 2014 gebruikt, die 68 tabellen en 20 views bevat. We vonden dit een goede weergave van een realistische database die je via een API zou willen ontsluiten voor intern of extern gebruik. Een handige visuele weergave van de tabellen vind je hier. Alles draaide in Docker en we hadden toegang tot alle bestanden die werden gebruikt om de database aan te maken en te vullen.

We werden ook aangemoedigd om verder te kijken dan waar we ons comfortabel bij voelden en methoden te proberen zoals AI-codeaanvulling of -generatie. Daarover later meer.

Verschillende aanpakken

De verschillende aanpakken kunnen grofweg in twee hoofdcategorieën worden opgedeeld:

Een API automatisch genereren
Een API automagisch genereren

Laten we ze afzonderlijk bekijken!

Automatisch

Wanneer ik het in deze context heb over het automatisch genereren van een API op basis van de PostgreSQL-database, bedoel ik het gebruik van een tool of library die (meta)data uit de database haalt en deze gebruikt om een API op te zetten.

Tijdens de hackathon werden er door verschillende mensen drie onderscheidende tools gebruikt:

FastAPI Code Generator

Aangezien wij grotendeels een Python-shop zijn, was dit een van de meer voor de hand liggende oplossingen. Deze library neemt een of meer OpenAPI-bestanden als input en genereert zowel Pydantic-modellen als een set bijbehorende routes. De library bevindt zich nog in een experimentele fase, maar presteert goed als basis voor verdere ontwikkeling.

Laten we een voorbeeld uit hun documentatie bekijken om beter te begrijpen hoe het werkt. We beginnen met een OpenAPI-inputbestand dat er zo uitziet (ingekort voor de leesbaarheid, kijk hier voor het volledige bestand)

openapi: "3.0.0"
info:
  version: 1.0.0
  title: Swagger Petstore
  license:
    name: MIT
servers:
  - url: http://petstore.swagger.io/v1
paths:
  /pets:
    get:
      summary: List all pets
      operationId: listPets
      tags:
        - pets
      parameters:
        - name: limit
          in: query
          description: How many items to return at one time (max 100)
          required: false
          schema:
            type: integer
            format: int32
      responses:
        '200':
          description: A paged array of pets
          headers:
            x-next:
              description: A link to the next page of responses
              schema:
                type: string
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Pets"
        %% shortened for brevity %%
    post:
      summary: Create a pet
      operationId: createPets
      tags:
        - pets
      responses:
        '201':
          description: Null response
        %% shortened for brevity %%

  /pets/{petId}:
    get:
      summary: Info for a specific pet
      operationId: showPetById
      tags:
        - pets
      parameters:
        - name: petId
          in: path
          required: true
          description: The id of the pet to retrieve
          schema:
            type: string
      responses:
        '200':
          description: Expected response to a valid request
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Pets"
        %% shortened for brevity %%
components:
  schemas:
    Pet:
      required:
        - id
        - name
      properties:
        id:
          type: integer
          format: int64
        name:
          type: string
        tag:
          type: string
    Pets:
      type: array
      description: list of pet
      items:
        $ref: "#/components/schemas/Pet"
    Error:
      required:
        - code
        - message
      properties:
        code:
          type: integer
          format: int32
        message:
          type: string

Wanneer we dit bestand als input aan de library geven, genereert het automatisch de volgende bestanden.

Eerst een app/main.py-bestand met de daadwerkelijke routes die aangeroepen kunnen worden. Zoals je ziet genereert het alleen een geldig skelet, het is aan ons om de business logic voor het ophalen en opslaan van data te implementeren.

# generated by fastapi-codegen:
#   filename:  api.yaml
#   timestamp: 2020-06-14T10:45:22+00:00

from __future__ import annotations

from typing import Optional

from fastapi import FastAPI, Query

from .models import Pets

app = FastAPI(version="1.0.0", title="Swagger Petstore", license="{'name': 'MIT'}",)


@app.get('/pets', response_model=Pets)
def list_pets(limit: Optional[int] = None) -> Pets:
    """
    List all pets
    """
    pass


@app.post('/pets', response_model=None)
def create_pets() -> None:
    """
    Create a pet
    """
    pass


@app.get('/pets/{pet_id}', response_model=Pets)
def show_pet_by_id(pet_id: str = Query(..., alias='petId')) -> Pets:
    """
    Info for a specific pet
    """
    pass

De bijbehorende Pydantic-modellen worden ook gegenereerd in het bestand app/models.py:

# generated by datamodel-codegen:
#   filename:  api.yaml
#   timestamp: 2020-06-14T10:45:22+00:00

from typing import List, Optional

from pydantic import BaseModel, Field


class Pet(BaseModel):
    id: int
    name: str
    tag: Optional[str] = None


class Pets(BaseModel):
    __root__: List[Pet] = Field(..., description='list of pet')


class Error(BaseModel):
    code: int
    message: str

Als we naar de oplossingen kijken, komen we tot de volgende voor- en nadelen:

Voordelen

Automatische generatie van alle opgegeven routes.
Automatische generatie van alle bijbehorende Pydantic-modellen.
Veel flexibiliteit in de business logic.
Gebaseerd op OpenAPI-bestanden, die gemakkelijk te begrijpen zijn voor zowel developers als niet-developers.

Nadelen

Je moet zelf een OpenAPI-bestand genereren.
Er wordt geen business logic gegenereerd, alleen boilerplate.
Werkt alleen voor FastAPI.

Het nadeel rond de noodzaak van een OpenAPI-bestand werd tijdens de hackathon slim opgelost door de aangeleverde create table-SQL-statements aan ChatGPT 3.5 te geven en het te vragen deze te vertalen naar geldige OpenAPI-specs. Een vergelijkbare aanpak werd gebruikt voor de business logic, door ChatGPT 3.5 te vragen de CRUD-logica te genereren op basis van het FastAPI-skelet dat door de library was gegenereerd.

Een mooie combinatie van automatisch en automagisch!

Django Inspectdb

Een andere vergelijkbare oplossing is Django’s inspectdb-utility, die in feite hetzelfde doet als de fastapi-code-generator maar specifiek voor Django. Laten we een kort voorbeeld bekijken.

Stel dat we een databasetabel hebben die Pets heet en er ongeveer zo uitziet:

Name	age	color	species
Fido	3	gold	dog
Bud	6	brown	dog
Max	2	red	cat

en we geven die als input aan de inspectdb-utility. De output zou er zo uitzien:

# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Make sure each ForeignKey and OneToOneField has `on_delete` set to the desired behavior
#   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
from django.db import models


class Pets(models.Model):
    index = models.BigIntegerField(blank=True, null=True)
    name = models.CharField(blank=True, null=True)
    age = models.BigIntegerField(blank=True, null=True)
    color = models.CharField(blank=True, null=True)
    species = models.CharField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'pets'

Met deze output werden een aantal extra stappen genomen om tot de volledige CRUD-API te komen.

Er moesten nog twee custom management-commando’s geschreven worden die generieke serialisers (DTO) en viewsets (CRUD) voor elk model bouwen.
De nieuw gegenereerde viewsets werden gekoppeld aan Django-URL’s.
De ingebouwde rest framework schema-generator werd ingeschakeld op basis van de viewsets/serialisers en dit schema werd in een custom template-view geladen die een Swagger UI laadt.

En daarmee was de API klaar. Als we naar deze oplossing kijken, zijn de voor- en nadelen vergelijkbaar met de vorige:

Voordelen

Automatische generatie van modellen in de database.
Veel flexibiliteit in de business logic.
Direct gebaseerd op de database, geen tussenbestanden nodig.

Nadelen

Meerdere DB-definities nodig om meerdere schema’s te ondersteunen.
Custom commando’s nodig om van modellen naar een volledige API te komen.
Werkt alleen voor Django.

Sommige hiervan kunnen opnieuw versneld of volledig ondervangen worden door een LLM meer boilerplate of business logic te laten genereren.

PostgREST

In dit geval werd ChatGPT 4 gebruikt als een veredelde zoekmachine, waarbij de context van de situatie werd uitgelegd en gevraagd werd hoe dit probleem efficiënt aan te pakken. Het is de enige die werd gebruikt die geen op Python gebaseerde API genereerde: PostgREST.

Dit is een standalone webserver die je PostgreSQL-database direct omzet in een RESTful API. De structurele constraints en permissies in de database bepalen de API-endpoints en -operaties.

Met andere woorden, het kijkt naar alle tabellen in de database, de tabel-constraints en -types en zet meteen een server op met de CRUD-endpoints!

Het opzetten was vrij eenvoudig omdat we Docker gebruikten tijdens de hackathon. Het simpelweg toevoegen aan het docker-compose.yml-bestand en het naar de PostgreSQL-database laten wijzen was genoeg om aan de gang te gaan:

server:  
    image: postgrest/postgrest  
    ports:  
        - "3000:3000"  
    links:  
        - db:db  
    environment:  
        PGRST_DB_URI: postgres://app_user:secret@db:5432/postgres  
        PGRST_DB_SCHEMA: public  
        PGRST_DB_ANON_ROLE: app_user 
        PGRST_SERVER_PROXY_URI: "http://127.0.0.1:3000"  
    depends_on:  
        - db

Hierna maken we een nieuwe rol aan die PosttGREST kan gebruiken om met de database te interacteren, dit is ook waar je restricties op je API instelt:

CREATE ROLE web_anon NOLOGIN;
GRANT USAGE ON SCHEMA public TO web_anon;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON tables TO web_anon;

Daarna gaan we de API gebruiken!

curl -X GET http://localhost:3000/pets

[
    {
        "id": 1,
        "name": "Fido"
        ...
    },
    {
        "id": 2,
        "name": "Max"
        ...
    }
]

Voordelen

Eenvoudige setup.
Volledig gebaseerd op de tabellen, constraints en types van de database.
Geen extra werk nodig.

Nadelen

Niet flexibel wat business logic betreft.

Als je echt alleen een CRUD-API nodig hebt, was dit zonder twijfel de snelste en gemakkelijkste manier. Dat gezegd hebbende, is het ook de minst flexibele. Als je data moet transformeren voordat je opslaat of nadat je uit de database ophaalt, is dit niet de tool voor jou.

Automagisch

Wanneer we het over automagisch hebben, bedoelen we tools die zo geavanceerd zijn dat ze magisch lijken. Denk aan AI’s of one-click-oplossingen. Sommige deelnemers brachten de AI-hulp een stap verder en probeerden een LLM de hele API te laten genereren. Het proces kwam neer op het voeden van data die de tabellen beschrijft aan een LLM en het te vragen te genereren wat we nodig hebben.

Als we naar de stappen kijken:

Haal de metadata uit de database op met een SQL-query
Voeg de logica toe die nodig is om verbinding te maken met de database, aangezien alleen wij de verbindingsgegevens kennen

import psycopg2  
from fastapi import FastAPI, HTTPException  
from pydantic import BaseModel# Define your PostgreSQL database connection parameters  
db_params = {  
    "dbname": "Adventureworks",  
    "user": "postgres",  
    "password": "postgres",  
    "host": "db",  # e.g., 'localhost' or your database server's IP address  
    "port": "5432",  # default is 5432  
}# Connect to the PostgreSQL database  
try:  
    conn = psycopg2.connect(**db_params)  
except psycopg2.Error as e:  
    print("Error connecting to the database:", e)  
    exit(1)

Plak de output van de SQL-query in VSCode en vraag Copilot om de benodigde code te genereren:

"""  
Metadata for table 'humanresources.shift':  
Column Name: shiftid, Data Type: integer, Max Length: None, Numeric Precision: 32, Default Value: nextval('humanresources.shift_shiftid_seq'::regclass), Is Nullable: NO  
Constraint Name: PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_Departm  
Column Name: shiftid, Data Type: integer, Max Length: None, Numeric Precision: 32, Default Value: nextval('humanresources.shift_shiftid_seq'::regclass), Is Nullable: NO  
Constraint Name: PK_Shift_ShiftID  
Column Name: shiftid, Data Type: integer, Max Length: None, Numeric Precision: 32, Default Value: nextval('humanresources.shift_shiftid_seq'::regclass), Is Nullable: NO  
Constraint Name: FK_EmployeeDepartmentHistory_Shift_ShiftID  
Column Name: name, Data Type: character varying, Max Length: 50, Numeric Precision: None, Default Value: None, Is Nullable: NO  
Column Name: starttime, Data Type: time without time zone, Max Length: None, Numeric Precision: None, Default Value: None, Is Nullable: NO  
Column Name: endtime, Data Type: time without time zone, Max Length: None, Numeric Precision: None, Default Value: None, Is Nullable: NO  
Column Name: modifieddate, Data Type: timestamp without time zone, Max Length: None, Numeric Precision: None, Default Value: now(), Is Nullable: NO  
"""

# please create a fastapi CRUD application with a pydantic model for this schema

Herhaal voor alle 64 tabellen

Zoals je je kunt voorstellen, had deze methode zijn voor- en nadelen.

Voordelen

Geen tools nodig behalve Copilot.
Geen noodzaak om de (boilerplate)code te schrijven.
Werkt volledig binnen je IDE.

Nadelen

Schaalt niet goed naar een groot aantal tabellen.
Het fijnafstellen van wat je Copilot vraagt en hoe je het vraagt is nodig om goede output te krijgen.

Als je een relatief kleine database hebt en het je niet uitmaakt om wat heen en weer te gaan met Copilot, is dit een werkbare oplossing. Verder gaan is echter lastig en vereist behoorlijk wat geknutsel.

Conclusie

Dus, wat is de beste manier om dit te doen? Zoals met de meeste dingen: het hangt ervan af. Er zijn genoeg tools beschikbaar die het proces van het genereren van een CRUD-API op basis van een bestaande database behoorlijk gemakkelijk maken. En het lijkt erop dat de opkomst van AI het proces nog verder zal blijven vereenvoudigen.

Als mijn baas me dit daadwerkelijk zou vragen, en de databasegrootte niet triviaal is, zou ik kiezen voor een combinatie van automatisch en automagisch, zoals de fastapi-code-generator. Een AI voorspelbare boilerplate laten genereren en een dedicated tool voor de noodzakelijke code lijkt de beste oplossing met de meeste flexibiliteit, tenminste op het moment van schrijven.

Wie weet wat de toekomst brengt!

Blog