This content originally appeared on DEV Community and was authored by Fernando Bryan Reza Campos
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:
- 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.
- 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.
- 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
/healthchecks and a/dt/schemasmetadata 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)
This content originally appeared on DEV Community and was authored by Fernando Bryan Reza Campos
Fernando Bryan Reza Campos | Sciencx (2025-09-06T00:26:47+00:00) Your Database is Your API: Auto-generate a FastAPI Backend in Seconds. Retrieved from https://www.scien.cx/2025/09/06/your-database-is-your-api-auto-generate-a-fastapi-backend-in-seconds-2/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.