text2sql

Text2SQL — AI-Powered Natural Language to SQL Query Tool

Transform natural language questions into SQL queries using AI, with built-in safety and visualization.

Features

• 🤖 AI-Powered SQL Generation - Convert natural language to SQL using LLM
• 🔒 Read-Only Safety - Multi-layer validation ensures only SELECT queries execute
• 🗄️ Multi-Database Support - Works with PostgreSQL and MySQL
• 📊 Schema Explorer - Automatic schema extraction and visualization
• 📝 Query History - Track all queries with execution metrics
• 🔐 Secure Credentials - AES-GCM encryption for database passwords
• 🚀 Single Binary - No runtime dependencies, easy deployment
• 🎨 Modern API - RESTful API with JSON responses

Quick Start

Prerequisites

• Go 1.25 or higher
• PostgreSQL or MySQL database (for querying)
• OpenAI API key or compatible LLM endpoint

Installation

# Clone the repository
git clone https://github.com/hungp03/text2sql.git
cd text2sql

# Install dependencies
go mod download

# Configure environment
cp .env.example .env
# Edit .env with your settings

Configuration

Edit .env file:

# LLM Configuration
LLM_BASE_URL=https://api.openai.com/v1
LLM_API_KEY=your-api-key-here
LLM_MODEL=gpt-4o
LLM_TEMPERATURE=0.1
LLM_MAX_TOKENS=2000

# Server Configuration
APP_PORT=8080
APP_HOST=0.0.0.0

# Internal SQLite Database
SQLITE_PATH=./data/text2sql.db

# Default Database Connection (Optional - auto-connect on startup)
# Leave empty to skip auto-connect
DEFAULT_DB_TYPE=postgres          # postgres or mysql
DEFAULT_DB_HOST=localhost
DEFAULT_DB_PORT=5432             # 5432 for postgres, 3306 for mysql
DEFAULT_DB_NAME=mydb
DEFAULT_DB_USER=postgres
DEFAULT_DB_PASSWORD=your_password

# Security (must be exactly 32 characters)
ENCRYPTION_KEY=your-32-byte-encryption-key-here

# Logging
LOG_LEVEL=info
LOG_FORMAT=json

# Query Limits
MAX_QUERY_ROWS=1000
QUERY_TIMEOUT_SECONDS=30

Generate a secure encryption key:

openssl rand -base64 32 | cut -c1-32

Run

# Development mode
go run ./cmd/server/main.go

# Or build and run
go build -o text2sql ./cmd/server
./text2sql

Server will start on http://localhost:8080

Note: If you configured DEFAULT_DB_* variables in .env, the server will automatically connect to that database on startup. You'll see:

{"level":"info","message":"Connecting to default database..."}
{"level":"info","message":"Default database connected successfully"}

API Usage

Health Check

curl http://localhost:8080/health

Database Connection Management

Test Connection

curl -X POST http://localhost:8080/api/connections/test \
  -H "Content-Type: application/json" \
  -d '{
    "name": "My Database",
    "db_type": "postgres",
    "host": "localhost",
    "port": 5432,
    "database": "mydb",
    "username": "postgres",
    "password": "your_password"
  }'

Create Connection

curl -X POST http://localhost:8080/api/connections \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Production DB",
    "db_type": "mysql",
    "host": "prod.example.com",
    "port": 3306,
    "database": "production",
    "username": "app_user",
    "password": "secure_password"
  }'

List Connections

curl http://localhost:8080/api/connections

Delete Connection

curl -X DELETE http://localhost:8080/api/connections/{connection_id}

Generate and Execute SQL

curl -X POST http://localhost:8080/api/v1/chat \
  -H "Content-Type: application/json" \
  -d '{
    "connection_id": "default",
    "question": "Show me all users who registered in the last 7 days",
    "history": []
  }'

Note: Use "connection_id": "default" to use the auto-connected database from .env

Response:

{
  "question": "Show me all users who registered in the last 7 days",
  "generated_sql": "SELECT * FROM users WHERE created_at >= NOW() - INTERVAL '7 days'",
  "result": {
    "columns": ["id", "name", "email", "created_at"],
    "rows": [...],
    "row_count": 42,
    "execution_time_ms": 15
  },
  "execution_time_ms": 1250,
  "tokens_used": 450
}

Architecture

text2sql/
├── cmd/server/          # Application entry point
├── internal/
│   ├── api/            # HTTP handlers and routing
│   ├── config/         # Configuration management
│   ├── db/             # Database connection and execution
│   ├── llm/            # LLM client and prompt management
│   └── store/          # Internal SQLite storage
└── frontend/           # Web UI (coming soon)

Security

Read-Only Enforcement

Text2SQL implements multiple layers of protection:

1. SQL String Parsing - Validates queries before execution
2. Keyword Blacklist - Blocks INSERT, UPDATE, DELETE, DROP, etc.
3. Comment Removal - Prevents bypass attempts via SQL comments
4. Recommended - Use read-only database users

Credential Protection

• Database passwords encrypted with AES-GCM
• Encryption key required in environment
• Credentials never logged or exposed in API responses

Development

Run Tests

# All tests
go test ./... -v

# With coverage
go test ./... -v -coverprofile=coverage.out
go tool cover -html=coverage.out

# With race detector
go test ./... -race

Linting

# Install golangci-lint
go install github.com/golangci/golangci-lint/cmd/golangci-lint@latest

# Run linter
golangci-lint run

Hot Reload

# Install air
go install github.com/air-verse/air@latest

# Run with hot reload
air

Docker

Build Image

docker build -t text2sql:latest .

Run Container

docker run -p 8080:8080 --env-file .env text2sql:latest

Docker Compose

docker-compose up

Roadmap

• Phase 1: Project Foundation
• Phase 2: Database Layer
• Phase 3: LLM Integration
• Phase 4: Complete Handler Implementations
• Phase 5: Web UI
• Phase 6: Documentation & Polish

Contributing

Contributions are welcome! Please read CONTRIBUTING.md for guidelines.

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'feat: add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

• Built with Gin web framework
• LLM integration via go-openai
• Database access with sqlx
• Logging with zerolog

Documentation

• 📖 Vietnamese Database Connection Guide - Hướng dẫn kết nối database bằng tiếng Việt
• 🧪 Testing Guide - How to run tests
• 🤝 Contributing Guide - How to contribute

Support

• 📫 Issues: GitHub Issues
• 💬 Discussions: GitHub Discussions

---

Made with ❤️ by hungp03