How defog-ai/sqlcoder works
1. What this repo is
- This is primarily a Python package for running Defog SQLCoder, an LLM that converts natural language to SQL.
- The repo contains three architectural pieces: a model inference layer, a CLI / local server wrapper, and a prebuilt web demo UI.
2. Core package boundary
- The main Python package is sqlcoder/.
- sqlcoder/__init__.py marks the package; the other modules expose user-facing execution paths:
- cli.py for command-line entry.
- serve.py for launching the interactive app.
- query_routes.py and integration_routes.py for backend HTTP endpoints.
- This suggests the package is both a library and a runnable application.
3. Model inference path
- inference.py is the standalone example for turning a question into SQL against a sample schema.
- metadata.sql provides that sample schema/metadata context used by inference.
- The README indicates the model weights are loaded from Hugging Face, so the code here is the orchestration layer around an externally hosted model artifact, not the weights themselves.
4. Serving architecture
- sqlcoder.launch is the main user-facing entry point described in the README.
- The likely flow is: CLI starts a local server, the server exposes routes for query generation and metadata handling, and the browser UI talks to those endpoints.
- serve.py appears to be the orchestration point that binds the inference logic to the HTTP layer and the static frontend.
5. HTTP/API boundary
- query_routes.py likely handles the core “natural language question → SQL query” requests.
- integration_routes.py likely handles database connection / metadata ingestion / integration setup for the demo workflow.
- These two modules form the backend boundary between the UI and the model/database context.
6. Web UI subsystem
- sqlcoder/static/ contains a fully built frontend, including index.html, query-data.html, extract-metadata.html, and instruct-model.html.
- The static/_next/ tree shows this UI is built with Next.js and checked into the repo as compiled assets, not source.
- The server likely serves these files directly as the user interface for the local app.
7. UI pages and user flows
- The page names imply three main user journeys:
- Query data: ask a question and get SQL back.
- Extract metadata: connect to a database and capture schema context.
- Instruct model: likely a page for prompt/model guidance or prompt editing.
- 404.html indicates the static UI is served as a standalone web app with client-side routing/fallback behavior.
8. Data flow
- Typical flow is: user question + schema metadata → backend route → SQLCoder model inference → SQL output → UI display.
- In the connected-database path, the flow includes a metadata extraction step before query generation.
- The repository does not appear to store production customer data; instead, it depends on whatever database the user connects to at runtime.
9. Model/runtime variants
- The README shows two runtime backends:
- transformers for GPU-friendly inference.
- llama-cpp for quantized/local CPU or Apple Silicon usage.
- This is important architecturally because the inference layer is abstracted over multiple execution engines, which affects deployment and hardware boundaries.
10. External dependencies and deployment shape
- The model itself is external to the repo, fetched from Hugging Face.
- The interactive demo is also published externally on Defog’s website, so this repo contains the local/dev/server-side packaging rather than the hosted production stack.
- The package is distributed through standard Python packaging files (setup.py, requirements.txt).
11. Non-runtime repo contents
- .github/workflows/stale.yml is maintenance automation only.
- defog_sqlcoder_colab.ipynb is a notebook for interactive experimentation.
- These support adoption and evaluation, but they are not central to the runtime architecture.
12. How to draw the architecture diagram
- Show a left-to-right layout:
- User / CLI / browser
- Python package (sqlcoder)
- Inference backend (transformers or llama-cpp + Hugging Face weights)
- Optional user database / schema metadata source
- Static Next.js frontend served from sqlcoder/static
- Emphasize the two major runtime modes:
- headless inference via inference.py
- interactive local app via sqlcoder launch
Main entry points: index.html Static UI · serve.py Serve · query_routes.py Query API · __init__.py sqlcoder
Components 16
sqlcoder repo
Main Python package plus local serving and demo UI.
- sqlcoder · Primary package boundary for CLI, server, and HTTP routes.
sqlcoder/__init__.py - CLI · Command-line entry that starts local workflows.
sqlcoder/cli.py - Serve · Orchestrates the interactive local server and binds routes.
sqlcoder/serve.py - Query API · Backend endpoints for natural language to SQL generation.
sqlcoder/query_routes.py - Integration API · Backend endpoints for database integration and metadata ingestion.
sqlcoder/integration_routes.py - Inference · Standalone question-to-SQL execution path against sample metadata.
inference.py - Sample schema · Example schema context used by the standalone inference flow.
metadata.sql - Static UI · Prebuilt Next.js frontend served by the local app.
sqlcoder/static/index.html - Query page · UI flow for asking questions and receiving SQL.
sqlcoder/static/query-data.html - Metadata page · UI flow for extracting schema metadata from a database.
sqlcoder/static/extract-metadata.html - Instruct page · UI flow for model guidance or prompt customization.
sqlcoder/static/instruct-model.html - Next assets · Checked-in Next.js build output backing the static UI.
sqlcoder/static/_next/static/chunks/pages/index-a1b2fa2d87d27d8d.js
External
Runtime dependencies and user-provided systems.
- User / CLI · Command-line and local launch entry into the package.
- Browser · Loads the packaged demo UI and talks to backend routes.
- User DB · Optional runtime database connected for metadata extraction and query generation.
- HF weights · Externally hosted SQLCoder weights fetched at runtime.
Connections 22
- User / CLI → CLI: runs · User invokes the package from the command line.
- CLI → Serve: launches · CLI starts the local interactive server.
- CLI → Inference: can run · CLI/package also supports the standalone inference path.
- Serve → Query API: mounts · Server exposes SQL generation endpoints.
- Serve → Integration API: mounts · Server exposes metadata and integration endpoints.
- Serve → Static UI: serves · Server delivers the packaged frontend.
- Static UI → Query page: includes · Main UI page for query generation.
- Static UI → Metadata page: includes · Main UI page for metadata extraction.
- Static UI → Instruct page: includes · Main UI page for instructing or configuring the model.
- Static UI → Next assets: backed by · The UI is a checked-in Next.js build.
- Browser → Static UI: loads · Browser opens the local demo interface.
- Browser → Query API: calls · Query UI sends requests to SQL generation endpoints.
- Browser → Integration API: calls · Metadata UI sends requests to integration endpoints.
- Query API → HF weights: uses · Query endpoint triggers model inference through hosted weights.
- Integration API → User DB: reads schema · Integration flow connects to the user database.
- Inference → Sample schema: uses · Standalone example uses sample schema context.
- Inference → HF weights: loads · Inference layer runs against external model weights.
- Query API → User DB: optionally queries · Query generation may use connected database context.
- sqlcoder → CLI: contains · Package includes the CLI entry point.
- sqlcoder → Serve: contains · Package includes the server bootstrap.
- sqlcoder → Query API: contains · Package includes query endpoints.
- sqlcoder → Integration API: contains · Package includes integration endpoints.