How wisenheim/nl2sql works
Project type and purpose
• This is a full-stack web application named “NL2SQL” whose goal is to translate natural-language queries into executable SQL, run them against a demo database, and show results.
• Key indications: a Next.js 13 “app” folder (layout.tsx, page.tsx), a REST API route (app/api/sql/route.ts), a SQL schema file (create_tables.sql), and a docker-compose.yml spinning up Postgres.
File structure
• Top-level:
• README.md & tracking.md: project overview and notes
• create_tables.sql: DDL for sample tables
• docker-compose.yml: orchestration for database (and possibly the Next.js app)
• my_app/: the main Next.js 13 application
• Inside my_app:
• app/ folder:
– layout.tsx, globals.css, page.tsx: root UI shell
– api/sql/route.ts: serverless endpoint handling NL→SQL translation & execution
• components/:
– SQLPlayground.tsx: the interactive UI for entering natural-language queries
– ui/: primitive UI building blocks (Button, Card, Input)
• lib/:
– sqlSchema.ts: representation of the database schema passed to the translator
– utils.ts: helper functions (e.g., calling an LLM API, sanitizing input)
• next.config.ts, tsconfig.json, postcss.config.mjs, eslint.config.mjs: build & lint configuration
README for additional insights
• README simply states “Natural language to SQL.”
• We infer that the API route invokes an external LLM (e.g., OpenAI) to generate SQL based on the schema defined in sqlSchema.ts, then executes the SQL against the Postgres instance defined in docker-compose.yml.
a. Main components:
• Frontend (Browser & Next.js app)
• Backend API (Next.js serverless route)
• External AI Service (e.g., OpenAI GPT)
• Database (PostgreSQL container)
• Dev/Deployment (Docker Compose orchestration)
b. Relationships & interactions:
• User’s browser sends HTTP requests to Next.js front end
• Front end calls the /api/sql endpoint with NL query
• API route uses lib/utils to:
– Load schema from lib/sqlSchema.ts
– Call the external AI service to translate NL → SQL
– Connect to Postgres and run the generated SQL
• API returns query results or error back to front end
• Front end renders results in SQLPlayground component
c. Architectural patterns & principles:
• Layered separation: presentation (React components), application logic (API route + utils), infrastructure (Postgres, Docker)
• Single-repository modular structure using Next.js “app” conventions
• Serverless endpoint pattern for backend logic
d. Key technologies/frameworks:
• Next.js 13 (app directory, serverless API routes)
• React & TypeScript for UI components
• Docker & docker-compose for local Postgres
• External LLM API (e.g., OpenAI)
• ESLint, PostCSS for code quality & styling