# defog-ai/sqlcoder architecture

> Architecture diagram of the GitHub repository defog-ai/sqlcoder, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-07-03). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/defog-ai/sqlcoder
- Repository: https://github.com/defog-ai/sqlcoder

## Overview

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: [`sqlcoder/static/index.html`](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/index.html) (Static UI), [`sqlcoder/serve.py`](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/serve.py) (Serve), [`sqlcoder/query_routes.py`](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/query_routes.py) (Query API), [`sqlcoder/__init__.py`](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/__init__.py) (sqlcoder)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_repo["sqlcoder repo"]
  node_sqlcoder_pkg["sqlcoder<br/>python package<br/>[__init__.py]"]
  node_cli["CLI<br/>launcher<br/>[cli.py]"]
  node_serve["Serve<br/>app bootstrap<br/>[serve.py]"]
  node_query_routes["Query API<br/>http routes<br/>[query_routes.py]"]
  node_integration_routes["Integration API<br/>http routes"]
  node_inference_py["Inference<br/>example runner<br/>[inference.py]"]
  node_metadata_sql["Sample schema<br/>sql metadata<br/>[metadata.sql]"]
  node_static_ui["Static UI<br/>web assets<br/>[index.html]"]
  node_query_page["Query page<br/>ui page<br/>[query-data.html]"]
  node_metadata_page["Metadata page<br/>ui page"]
  node_instruct_page["Instruct page<br/>ui page"]
  node_next_assets["Next assets<br/>compiled frontend"]
end

subgraph group_external["External"]
  node_user_cli(("User / CLI<br/>entrypoint"))
  node_browser(("Browser<br/>client"))
  node_db[("User DB<br/>database")]
  node_hf_model[("HF weights<br/>model artifact")]
end

node_user_cli -->|"runs"| node_cli
node_cli -->|"launches"| node_serve
node_cli -.->|"can run"| node_inference_py
node_serve -->|"mounts"| node_query_routes
node_serve -->|"mounts"| node_integration_routes
node_serve -->|"serves"| node_static_ui
node_static_ui -->|"includes"| node_query_page
node_static_ui -->|"includes"| node_metadata_page
node_static_ui -->|"includes"| node_instruct_page
node_static_ui -->|"backed by"| node_next_assets
node_browser -->|"loads"| node_static_ui
node_browser -->|"calls"| node_query_routes
node_browser -->|"calls"| node_integration_routes
node_query_routes -->|"uses"| node_hf_model
node_integration_routes -->|"reads schema"| node_db
node_inference_py -->|"uses"| node_metadata_sql
node_inference_py -->|"loads"| node_hf_model
node_query_routes -.->|"optionally queries"| node_db
node_sqlcoder_pkg -->|"contains"| node_cli
node_sqlcoder_pkg -->|"contains"| node_serve
node_sqlcoder_pkg -->|"contains"| node_query_routes
node_sqlcoder_pkg -->|"contains"| node_integration_routes

click node_sqlcoder_pkg "https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/__init__.py"
click node_cli "https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/cli.py"
click node_serve "https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/serve.py"
click node_query_routes "https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/query_routes.py"
click node_integration_routes "https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/integration_routes.py"
click node_inference_py "https://github.com/defog-ai/sqlcoder/blob/main/inference.py"
click node_metadata_sql "https://github.com/defog-ai/sqlcoder/blob/main/metadata.sql"
click node_static_ui "https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/index.html"
click node_query_page "https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/query-data.html"
click node_metadata_page "https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/extract-metadata.html"
click node_instruct_page "https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/instruct-model.html"
click node_next_assets "https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/_next/static/chunks/pages/index-a1b2fa2d87d27d8d.js"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_sqlcoder_pkg,node_cli,node_serve,node_query_routes,node_integration_routes,node_inference_py,node_metadata_sql,node_static_ui,node_query_page,node_metadata_page,node_instruct_page,node_next_assets toneBlue
class node_user_cli,node_browser,node_db,node_hf_model toneAmber
```

## Components

### sqlcoder repo

Main Python package plus local serving and demo UI.

- [sqlcoder](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/__init__.py): python package · Primary package boundary for CLI, server, and HTTP routes. · `sqlcoder/__init__.py`
- [CLI](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/cli.py): launcher · Command-line entry that starts local workflows. · `sqlcoder/cli.py`
- [Serve](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/serve.py): app bootstrap · Orchestrates the interactive local server and binds routes. · `sqlcoder/serve.py`
- [Query API](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/query_routes.py): http routes · Backend endpoints for natural language to SQL generation. · `sqlcoder/query_routes.py`
- [Integration API](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/integration_routes.py): http routes · Backend endpoints for database integration and metadata ingestion. · `sqlcoder/integration_routes.py`
- [Inference](https://github.com/defog-ai/sqlcoder/blob/main/inference.py): example runner · Standalone question-to-SQL execution path against sample metadata. · `inference.py`
- [Sample schema](https://github.com/defog-ai/sqlcoder/blob/main/metadata.sql): sql metadata · Example schema context used by the standalone inference flow. · `metadata.sql`
- [Static UI](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/index.html): web assets · Prebuilt Next.js frontend served by the local app. · `sqlcoder/static/index.html`
- [Query page](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/query-data.html): ui page · UI flow for asking questions and receiving SQL. · `sqlcoder/static/query-data.html`
- [Metadata page](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/extract-metadata.html): ui page · UI flow for extracting schema metadata from a database. · `sqlcoder/static/extract-metadata.html`
- [Instruct page](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/instruct-model.html): ui page · UI flow for model guidance or prompt customization. · `sqlcoder/static/instruct-model.html`
- [Next assets](https://github.com/defog-ai/sqlcoder/blob/main/sqlcoder/static/_next/static/chunks/pages/index-a1b2fa2d87d27d8d.js): compiled frontend · 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**: entrypoint · Command-line and local launch entry into the package.
- **Browser**: client · Loads the packaged demo UI and talks to backend routes.
- **User DB**: database · Optional runtime database connected for metadata extraction and query generation.
- **HF weights**: model artifact · Externally hosted SQLCoder weights fetched at runtime.

## Connections

- 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.
