How it Works
flAPI sits between high-throughput API consumers — AI agents, dashboards, mobile apps — and traditional analytical backends like BigQuery, Snowflake, and SAP. Every read is served from a local DuckDB snapshot refreshed on schedule, so consumers get sub-50 ms responses while the warehouse only sees one query per cache cycle. Teams routinely cut backend egress and query spend by 90% or more without changing a line of warehouse code.
- What is flAPI?
- An open-source SQL-to-API framework, written in C++ and powered by DuckDB 1.5.2, that turns SQL queries into REST endpoints and MCP tools defined in YAML.
- Who builds it?
- DataZoo GmbH (Stuttgart, Germany) maintains flAPI under the Business Source License with a community-contributable open-source repository at github.com/datazoode/flapi.
- What is MCP?
- Model Context Protocol — the JSON-RPC 2.0 standard for exposing tools, resources, and prompts to AI agents like Claude, ChatGPT, and Cursor.
Connect Your Data
Point flAPI at one of ten documented backends: BigQuery, Snowflake, Postgres, Parquet, ODBC, SAP ERP, SAP BW, Google Sheets, Power BI, or vector search. Connections are plain YAML — a few lines for the connection string and any DuckDB extension your source needs. Arbitrary DuckDB community extensions plug in through a connection init: block.
Define APIs with SQL
Write a SQL template, declare the URL path and request fields in YAML, and flAPI handles the rest: Mustache parameter substitution, seven built-in validators (int, string, enum, email, uuid, date, time), automatic OpenAPI generation, optional DuckLake snapshot caching with three refresh modes, and pluggable authentication via Basic, JWT, Bearer, OIDC, or AWS Secrets Manager.
Read & Write Instantly
Launch a single statically-linked binary and your endpoints are live in seconds — secure, documented, paginated, rate-limited. The same YAML simultaneously exposes the data as Model Context Protocol tools, resources, and prompts so AI agents can read and write through the exact same auth and validation pipeline used by your REST clients.
Packed with Power
flAPI ships every piece you need to take a SQL query from a developer laptop to a production REST and MCP surface — declarative YAML configuration, snapshot caching, request validation, five authentication schemes, and an admin REST API for runtime mutation — without depending on a managed cloud, a sidecar service, or a build pipeline.
Instant API Generation
Define an endpoint in a 10-line YAML file and flAPI immediately serves it as a documented REST API. Every endpoint gets automatic OpenAPI 3.1 generation, request validation via seven built-in types (int, string, enum, email, uuid, date, time), Mustache-templated SQL, and pagination — no Python, no Node, no compile step.
Local-First Development
flAPI ships as a single statically-linked binary distributed via PyPI (`pip install flapi-io`) or the official Docker image (`ghcr.io/datazoode/flapi:latest`). The same binary you run on a laptop is the one you ship to production — no Docker base-image roulette, no managed-cloud lock-in, and your editor gets first-class support through the flAPI VS Code extension.
High-Performance Serving Layer
flAPI uses DuckLake snapshot caching on top of DuckDB to absorb high-frequency reads. Three refresh modes (full, incremental append, incremental merge) cover everything from event streams to mutable master data. Customers routinely report 90% lower warehouse spend and sub-50 ms median response times with the warehouse left untouched between cache cycles.
Built for AI-Native Enterprise
Model Context Protocol (MCP) is a first-class citizen, not a bolt-on. Every endpoint can expose itself as an MCP tool, resource, or prompt to Claude, Cursor, or any MCP-compatible agent — and 19 admin tools let agents inspect schema, create endpoints, and reload templates at runtime, all behind the same auth pipeline that protects your REST surface.
Built for Any Use Case
Internal Analytics API
BI dashboards on BigQuery or Snowflake re-query the warehouse on every filter change, billing per scan and pinning a slot for seconds. flAPI fronts the warehouse with a DuckLake snapshot refreshed on a schedule, so each dashboard panel resolves in single-digit milliseconds against local Parquet while the warehouse handles one query per refresh cycle.
Learn how →Customer Data Endpoints
Support and operations teams need real-time visibility into SAP and ERP data, but direct RFC connections are slow, license-bound, and risky to expose to consumer-grade apps. flAPI ATTACHes SAP through the ERPL extension, caches what teams query most, and exposes a secure REST and MCP surface with row-level filtering driven by the authenticated user.
Learn how →Real-Time Reporting
AI agents that report against production databases create unpredictable load, missing indexes, and noisy-neighbour problems. flAPI gives agents a safe, rate-limited, snapshot-backed endpoint with explicit auth and per-method controls — the agent reads from the cache, never the live primary, and the operator can revoke or throttle without touching the database.
Learn how →AI Agents as Data Actors
Most agents can read data but can't act on it — they can't create orders, update inventory, or correct records. flAPI exposes write endpoints as MCP tools with the same validators, transactions, and audit hooks used by REST clients, so an agent calling create_order goes through int and email validation, runs inside a database transaction, and returns the inserted row with full referential integrity.
Learn how →Operational / Transactional APIs
Form submissions, order processing, and inventory updates still get written as bespoke Python or Node services that wrap a database driver. flAPI replaces them with a YAML file: the same engine that serves your analytics also runs INSERT, UPDATE, and DELETE statements, with declarative validation, optional transaction wrapping, and millisecond-latency writes back to your warehouse.
Learn how →Bidirectional Data Sync
Data normally flows one way from your warehouse to your apps, and any change has to be reconciled through a separate batch job. flAPI's write endpoints close the loop — a mobile app or AI agent can update the local DuckLake snapshot, flAPI propagates the change back into the warehouse on the next sync, and cache-invalidation hooks keep downstream readers consistent.
Learn how →Built for Everyone, Supported for Enterprises
Enterprise Support & Consulting
Run flAPI in production with confidence. Our engineers help you design caching strategies for billion-row workloads, harden authentication against your IdP, tune DuckDB memory and threading for your hardware profile, and stay reachable through a 4-hour SLA when something breaks at 2 a.m. Pricing starts at $2,000/month for a dedicated Slack channel and quarterly architecture reviews.
What's Included:
- ✓Architecture & Design
Expert consultation on data modeling, caching strategies, and API design
- ✓Implementation Support
Hands-on help deploying flAPI for complex enterprise use cases
- ✓Training & Enablement
Custom workshops and training for your development team
- ✓Priority Support
Direct access to engineering team with guaranteed response SLAs
Commercial Licensing for Product Builders
flAPI is permissively free for internal and open-source use, but any commercial product that embeds, redistributes, or rebrands the binary needs a license under our Business Source License terms. We work on a fair-share model — revenue share, per-seat, or a flat enterprise fee depending on what fits your business — and the conversation usually wraps up in under a week.
Our Approach:
- ✓Fair Share Model
We believe in mutual success—get the licensing terms that work for your business
- ✓Flexible Licensing
Revenue share, per-seat, or custom arrangements—we adapt to your model
- ✓Partnership Benefits
Priority feature requests, dedicated support, and go-to-market collaboration
- ✓Legal Clarity
Clear licensing terms under BSL, with straightforward commercial agreements
Questions about licensing? If you're using flAPI internally (even at enterprise scale), building open source projects, or creating free tools—you don't need a commercial license. Only companies building commercial products on top of flAPI need licensing terms.
Start Building Your First API Today
Install flAPI in one command with pip install flapi-io or pull the official Docker image from ghcr.io/datazoode/flapi:latest. The Quickstart walks you through wiring up a Parquet file, defining a SQL endpoint, and serving it as REST and MCP in under five minutes. The same binary scales from a developer laptop to a serverless container without a managed cloud in the loop.
Last updated • flAPI v0.3.0 on DuckDB 1.5.2