Skip to main content
aiFull Stack

NT-To-SQL

Full-stack microservices application that converts natural-language questions into executable SQL using a locally hosted Ollama model, with MariaDB, FastAPI, Docker and automated testing.

Architectural Overview

NT-To-SQL is a microservices-based, full-stack application designed to translate natural-language user prompts into syntactically valid and executable SQL queries, run those queries against a relational database, and return structured datasets. The system relies on a local Large Language Model (LLM) managed by Ollama, eliminating external third-party API dependencies, ensuring privacy, and reducing operational costs.

The architecture decouples the natural language interface from database execution through an orchestration layer built with FastAPI. Pydantic enforces strict data validation and schema serialization across system boundaries, while MariaDB serves as the target relational database management system (RDBMS). The entire system is containerized using Docker, enabling reproducible multi-service deployment and local networking isolation.

+-------------------------------------------------------------------------+
|                              Docker Host                                |
|                                                                         |
|  +--------------------+      +--------------------+      +-----------+  |
|  |   FastAPI Service  | ---> |   Ollama Service   |      |  MariaDB  |  |
|  | (Orchestrator/API) | <--- |   (Local LLM)      |      |  Database |  |
|  +--------------------+      +--------------------+      +-----------+  |
|            |                                                   ^        |
|            +------------------ Query Execution ----------------+        |
+-------------------------------------------------------------------------+

Core Service Architecture & Component Interaction

The platform is structured into three primary operational tiers:

1. API Gateway & Orchestration Layer (FastAPI & Pydantic)

FastAPI functions as the central orchestration engine and entry point for external client requests. It exposes asynchronous REST endpoints that receive natural language questions.

  • Validation & Serialization: Pydantic models define the API contract. Incoming payload objects (e.g., query string, context parameters) are validated prior to execution.
  • Orchestration: Upon receiving a prompt, FastAPI constructs a tailored prompt—often incorporating database schema metadata—and issues an HTTP request to the local Ollama service endpoint. Upon receiving the LLM output, FastAPI parses the generated SQL and executes it against the MariaDB instance.
  • Response Formatting: Pydantic formats both the generated SQL string and the resulting raw MariaDB execution dataset into clean, predictable JSON responses.

2. Local Inference Layer (Ollama)

The inference component runs locally as a containerized service or dedicated daemon managed by Ollama.

  • Model Hosting: Hosts specialized open-weight models (e.g., CodeLlama, Llama 3, or SQL-focused variants) capable of code generation.
  • Context Handling: Receives structured prompt contexts containing the target database schema definitions (DDL) alongside the user's natural language question, allowing the model to produce database-specific SQL (MariaDB dialect) without requiring standard fine-tuning.

3. Relational Storage Layer (MariaDB)

MariaDB acts as the operational database housing the dataset intended for querying.

  • Schema Definition: Stores transactional or analytical data structures against which natural language queries are targeted.
  • Execution Target: Receives dynamically generated SQL queries from the FastAPI orchestration layer, executes the queries within a managed transaction context, and returns tabular record sets back to the application.

Data Flow and Pipeline Execution

  1. Request Ingestion: The client posts a JSON payload containing a natural language question (e.g., "Show total sales by product category for Q3") to the FastAPI application.
  2. Schema Ingestion & Prompt Assembly: The FastAPI app fetches or injects the relevant MariaDB table schemas into a predefined system prompt template.
  3. Inference Call: FastAPI forwards the structured prompt to the Ollama API interface via an asynchronous HTTP client.
  4. SQL Parsing: Ollama returns the generated text. FastAPI extracts the raw SQL block, stripping markdown tags or ancillary conversational text returned by the model.
  5. Query Execution: FastAPI establishes a connection to MariaDB via an async database driver, executes the parsed query, and fetches the resulting rows.
  6. Response Delivery: FastAPI packages the output into a Pydantic response schema containing the generated SQL string, execution metadata, and data records, returning it to the user.

Key Engineering Decisions

Local LLM vs. Managed Cloud API

  • Decision: Utilizing Ollama to host LLMs locally rather than relying on external providers like OpenAI or Anthropic.
  • Rationale: Ensures complete data privacy by keeping sensitive database schemas and internal queries on-premises. It eliminates per-token operational costs and guarantees application functionality in air-gapped or restricted network environments.

Decoupled Service Architecture

  • Decision: Separating the API orchestration, model inference, and database into isolated services.
  • Rationale: Allows each component to be scaled independently. The compute-heavy inference engine (Ollama) can be allocated dedicated GPU resources, while the API layer (FastAPI) remains lightweight and highly concurrent.

Strict Type Safety with Pydantic

  • Decision: Enforcing explicit data models at the input and output stages of the FastAPI backend.
  • Rationale: Minimizes runtime errors stemming from unpredictable LLM outputs and provides structured error handling if model outputs fail validation.

Deployment, Testing, and Security Considerations

Containerization & Orchestration

The application relies on Docker (and typically Docker Compose) to standardize environment configuration across development and production environments. Container definitions ensure consistent versions of Python runtime dependencies, MariaDB instances, and Ollama service configurations.

Testing Architecture

  • Unit Testing: Validates Pydantic serialization models, API route controllers, and SQL parsing utility functions.
  • Integration Testing: Automated test suites verify end-to-end flows by sending test natural language queries, verifying LLM response formatting, executing generated SQL against a test MariaDB database instance, and asserting response accuracy.

Security & Query Execution Safety

Executing dynamically generated SQL presents security risks, such as accidental data mutation or SQL injection.

  • Observed & Inferred Safety Patterns: The execution pipeline should enforce read-only database connections or transaction rollbacks when evaluating dynamically generated SQL to prevent unintended UPDATE, DELETE, or DROP statements from altering state.
  • Network isolation within Docker prevents direct external exposure of internal database ports, routing all interactions strictly through the FastAPI validation layer.