DEV Community: Fernando Bryan Reza Campos

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

Fernando Bryan Reza Campos — Sat, 06 Sep 2025 00:26:47 +0000

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 /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)

Go from Zero to a Full-Stack, Type-Safe, Cross-Platform App in Seconds

Fernando Bryan Reza Campos — Sat, 06 Sep 2025 00:00:20 +0000

I've been working on a project that I'm really excited to share with the dev community! My goal was to create a modern, powerful, and easy-to-use foundation for building full-stack applications that are type-safe from the database all the way to the frontend.

The result is the General Web App (GWA), a comprehensive application template, and its companion tool, gwa-create, an interactive CLI for scaffolding new projects in seconds.

If you've ever felt the pain of keeping your backend API and frontend types in sync, or wanted a single codebase that can be deployed to the web, desktop, and mobile, then this might be what you're looking for!

✨ What is General Web App (GWA)?

GWA is a full-stack, production-ready application template built on a modern, high-performance technology stack:

Backend: PostgreSQL database with a Python FastAPI server. The magic here is the use of prism-py, which automatically generates a complete REST API directly from your database schema.
Frontend: A blazing-fast Svelte 5 (with Runes!) and SvelteKit frontend, powered by the Deno runtime instead of Node.js.
End-to-End Type Safety: By combining prism-py on the backend and prism-ts on the frontend, you get a "zero-friction" data pipeline. Your database schema becomes the single source of truth, with types seamlessly propagated to your client application.
Cross-Platform Ready: The template comes with Tauri integrated out-of-the-box, allowing you to bundle your SvelteKit application as a native desktop app for Windows, macOS, and Linux using a Rust backend. There's even a proof-of-concept for Android support!

🚀 `gwa-create`: The Interactive Scaffolder

To make GWA incredibly easy to start with, I built gwa-create, a command-line tool written in Rust. Instead of manually cloning a repo and replacing placeholder values, this tool gives you an interactive wizard to configure your new project.

It guides you through:

Naming your project.
Choosing which components to include (Backend, Frontend, or both).
Setting up application identifiers for Tauri.
Configuring your database credentials.

In seconds, it generates a complete, ready-to-run project structure. For power users and automation scripts, you can also use a --yes flag to skip the prompts and use sensible defaults.

🛠️ Getting Started in 3 Steps

Ready to give it a try?

1. Install the Scaffolder

You'll need Rust and Cargo installed. Then, simply run:

cargo install gwa-create

2. Create Your New Project

Run the create command and follow the interactive prompts:

gwa create my-awesome-app

3. Run Your Application!

Once the project is generated, cd into the new directory and follow the instructions in the README.md. You can typically get the backend and frontend running with just a couple of Docker and Deno commands.

# Start the backend
cd my-awesome-app
docker compose up -d

# Start the frontend
cd generic-app
deno task dev --open

And that's it! You now have a fully functional, type-safe, full-stack application ready for you to build upon.

Why I Built This

I believe that modern development should be fast, safe, and enjoyable. By combining best-in-class tools like Svelte 5, Deno, FastAPI, and Tauri, and focusing on solving the critical problem of type-safety across the stack, GWA aims to provide a superior developer experience.

This is a project I'm passionate about, and it's built for developers like you. I'd love for you to check it out, and any feedback, suggestions, or contributions are more than welcome!

GWA Template Repo: https://github.com/Yrrrrrf/gwa
gwa-create Scaffolder Repo: https://github.com/Yrrrrrf/gwa-create

Let me know what you think in the comments below!