DEV Community

Cover image for Your Database is Your API: Auto-generate a FastAPI Backend in Seconds
Fernando Bryan Reza Campos
Fernando Bryan Reza Campos

Posted on

Your Database is Your API: Auto-generate a FastAPI Backend in Seconds

Stop writing boilerplate. Seriously.

Every time you build an API for a database, you repeat the same tedious tasks: define Pydantic models, write CRUD endpoints for each table, manually add validation... it's a soul-crushing time sink that keeps you from building actual features.

What if your database schema was your API specification?

This is the principle behind prism-py, a Python library that generates a complete, type-safe, and feature-rich FastAPI backend directly from your database.

The Magic: How It Works

prism-py isn't just mapping tables; it's a complete, three-stage architectural process that runs at startup:

  1. Deep Introspection: It connects to your database (initially PostgreSQL) and performs a deep query of the system catalogs. It learns everything: tables, views, column types, constraints, enums, and even the exact signatures of your functions and stored procedures.
  2. Dynamic Generation: This rich metadata is then used to dynamically generate Pydantic models for type validation and SQLAlchemy models for database interaction. It literally writes the models for you, in memory.
  3. Route Assembly: Finally, it constructs a full suite of FastAPI routes for every discovered object, complete with advanced features, and attaches them to your app instance.

The result? An API that is always a perfect mirror of your database schema. Add a column, restart the server, and your API is instantly updated.

Production-Ready Features Out-of-the-Box

This isn't just basic CRUD. You get a production-grade API with zero configuration:

  • Full DB Support: Generates endpoints for tables (GET, POST, PUT, DELETE), views (GET), functions (POST), and procedures (POST).
  • Advanced Filtering: Built-in support for complex queries like ?age[gte]=18&status[in]=active,pending.
  • Robust & Type-Safe: Enforces type safety based on your schema, including string lengths from VARCHAR(n).
  • Composite Key Mastery: Flawlessly handles tables with multi-column primary keys—a feature many auto-generators fail at.
  • Admin Endpoints: Includes built-in /health checks and a /dt/schemas metadata API for programmatic schema exploration.

Quick Start: 10 Lines to a Full API

Seeing is believing. Install it (pip install prism-py), create a main.py, and paste this in:


python
# main.py
from fastapi import FastAPI
from prism import ApiPrism, DbClient

# 1. Your FastAPI app
app = FastAPI(title="My Auto-Generated API")

# 2. Point it at your database
db_client = DbClient(db_url="postgresql://user:pass@host/db_name")

# 3. Initialize the generator
api_prism = ApiPrism(db_client=db_client, app=app)

# 4. Generate EVERYTHING
api_prism.generate_all_routes()

# 5. A nice welcome message with a link to the docs
api_prism.print_welcome_message(host="127.0.0.1", port=8000)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)