fastapi-from-frictionless

Status: Work in Progress — API and generated output are subject to change.

Overview

fastapifromfrictionless is a Python scaffolding tool that reads Frictionless Data Package schema files and generates a fully-functional FastAPI + SQLModel application — including models, CRUD endpoints, and dynamic query support — with no hand-written boilerplate.

The driving goal is to bridge the gap between familiar flat-file workflows (Excel workbooks, CSV files, Frictionless packages) and a production-grade relational database with a queryable REST API. Data stewards continue working in Excel; the package handles ingestion, validation, and API synchronization behind the scenes.

Core capabilities:

• Generate models.py, app.py, and database.py from *.schema.yaml files
• Automatic SQLModel class hierarchy per schema (base, table, create, update, public, public-with-relations)
• Full CRUD + pagination + recent-records endpoints per resource
• Excel workbook as a data-entry interface — create or update API records from a .xlsx file
• Frictionless package wraps the workbook for field-level validation before ingestion
• CLI entry point: fastapifromfrictionless generate <schema-folder>

Requirements

• Python >= 3.10
• frictionless
• FastAPI
• SQLModel
• SQLAlchemy
• fastapi-querybuilder
• jinja2
• requests
• pandas
• xlsxwriter

An ASGI server such as uvicorn is required to run the generated application.

Installation

pip install fastapifromfrictionless

For environments that run the generated app (FK query endpoints, geo field types):

pip install "fastapifromfrictionless[app]"

Windows

The base install works on Windows without any prerequisites.

The [app] extra includes geoalchemy2, which requires GDAL. If your schemas use geographic field types, install GDAL first:

GDAL installation guide for Windows

Then install the extra:

pip install "fastapifromfrictionless[app]"

If your schemas do not use geo fields, pip install fastapifromfrictionless is sufficient and no GDAL install is needed.

Unique constraints

Frictionless constraints.unique: true emits a unique constraint on the column:

name: str = Field(unique=True)

The flag is merged into whatever the field already carries, so a foreign key keeps its settings:

sensor_name: str | None = Field(foreign_key="sensor.name", index=True, unique=True)

Primary keys are skipped — already unique, so a second constraint would only add a duplicate index.

This matters for foreign keys. PostgreSQL requires a FK to reference a column with a unique or primary key constraint. If a schema's foreignKeys targets a non-PK column, mark that column unique: true or table creation fails with:

psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys
for referenced table "sensor"

SQLite does not enforce this, so such a schema works locally and fails only on a real deployment.

Geo field types

Frictionless geopoint and geojson fields generate a geoalchemy2 column rather than a plain annotation:

location: Any | None = Field(default=None, sa_column=Column(Geometry("POINT")))
footprint: Any = Field(sa_column=Column(Geometry("GEOMETRY"), nullable=False))

These columns require a spatial database. create_db_and_tables() fails on plain SQLite with no such function: RecoverGeometryColumn, because geoalchemy2 emits SpatiaLite calls. The generated database.py defaults to SQLite, so a schema with a geo field needs DATABASE_URL pointed at PostGIS (as in the podman/ stack) or SpatiaLite loaded into SQLite.

Quick Start

1. Generate application files from schemas

Place *.schema.yaml files in a folder (see doc/data/ for examples), then run:

fastapifromfrictionless generate path/to/schemas --output path/to/output

Or from Python:

from fastapifromfrictionless.scaffolding import build_database

build_database(schema_folder="path/to/schemas", db_filename="app.db")

2. Start the generated API

cd path/to/output
uvicorn app:app --reload

3. Generated endpoints

For each schema resource, the generated app exposes the following endpoints (replace {resource} with the schema name, e.g. sensor, location, permit):

4. Configuration via environment variables

5. Excel data workflow

from fastapifromfrictionless.runtime import (
    empty_excel,
    create_package,
    update_api_from_package,
    dump_to_excel,
)

# Create a blank workbook with one sheet per schema
empty_excel(schema_folder="path/to/schemas", output_filepath="data.xlsx")

# Fill in data, then validate and sync to the API
create_package(folder="path/to/schemas", filename="data.xlsx")
update_api_from_package(api_url="http://localhost:8000", package_file="data.package.yaml")

# Or export all current API data to Excel
dump_to_excel(
    api_url="http://localhost:8000", schema_folder="path/to/schemas", output_filepath="export.xlsx"
)

6. CLI reference

fastapifromfrictionless generate <schema_folder> [options]

Options:
  --output DIR      Output directory (default: current directory)
  --db FILENAME     SQLite database filename (default: database.db)
  --dry-run         Print generated code to stdout without writing files
  --no-models       Skip generating models.py
  --no-app          Skip generating app.py
  --no-db           Skip generating database.py

See doc/quickstart.ipynb for a step-by-step walkthrough of the full deployment workflow.

Deployment

A ready-to-use container deployment lives in podman/. It spins up three containers — PostGIS database, pgAdmin web UI, and the generated FastAPI app — and works with Docker Desktop, Podman, or any docker compose-compatible tool on Linux, macOS, and Windows.

Multiple deployments can run simultaneously on the same machine by assigning each a unique SUBNET_BASE and port set (API_PORT, PGADMIN_PORT, DB_PORT).

The API image uses a two-stage build: Stage 1 installs the code-generator and produces FastAPI source from your schemas; Stage 2 installs only the runtime dependencies and serves the generated app. The pip install layer is cached, so schema-only rebuilds are fast.

cd podman/
cp .env.example .env          # set ports, passwords, and settings
# add *.schema.yaml files to schemas/
docker compose up -d          # or: podman-compose up -d

After schema changes, re-run docker compose build api then docker compose up -d api. To stop and clean up, run docker compose down.

See podman/README.md for full instructions.