### Install SQLCoder with Pip

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Installs the SQLCoder package using pip. Different commands are provided for basic installation, NVIDIA GPU support, and CPU or Apple Silicon support using quantized GGUF models.

```bash
# Basic installation
pip install sqlcoder

# For NVIDIA GPU support (recommended for best performance)
pip install sqlcoder[transformers]

# For CPU or Apple Silicon (uses quantized GGUF model)
pip install sqlcoder[llama-cpp]

# Install from source
git clone https://github.com/defog-ai/sqlcoder.git
cd sqlcoder
pip install -e .
```

--------------------------------

### Example SQL Query Generation

Source: https://github.com/defog-ai/sqlcoder/blob/main/defog_sqlcoder_colab.ipynb

Demonstrates how to use the `generate_query` function with a sample question and prints the resulting formatted SQL query.

```python
question = "What was our revenue by product in the New York region last month?"
generated_sql = generate_query(question)

print(generated_sql)
```

--------------------------------

### Example Generated SQL Query

Source: https://github.com/defog-ai/sqlcoder/blob/main/defog_sqlcoder_colab.ipynb

The output SQL query generated by the `generate_query` function for the example question about revenue by product in the New York region last month.

```sql
SELECT p.product_id,
       SUM(s.quantity * p.price) AS revenue
FROM sales s
JOIN salespeople sp ON s.salesperson_id = sp.salesperson_id
JOIN products p ON s.product_id = p.product_id
WHERE sp.region = 'New York'
  AND s.sale_date >= (CURRENT_DATE - INTERVAL '1 month')
GROUP BY p.product_id
ORDER BY revenue DESC NULLS LAST;
```

--------------------------------

### SQLCoder Hardware-Specific Setup (Python)

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Illustrates how SQLCoder automatically detects and configures for different hardware environments. The model selects the appropriate backend (NVIDIA GPU, Apple Silicon, or CPU-only) based on available resources, influencing performance and model type.

```python
# The model automatically selects the appropriate backend:

# On NVIDIA GPU (requires ~14GB VRAM):
# - Downloads full fp16 model from HuggingFace
# - Uses transformers pipeline with beam search
# - Best quality results

# On Apple Silicon (M1/M2/M3):
# - Downloads quantized GGUF model (~5GB)
# - Uses llama.cpp with Metal GPU acceleration
# - Good balance of quality and speed

# On CPU-only:
# - Downloads quantized GGUF model (~5GB)
# - Uses llama.cpp without GPU acceleration
# - Slower but functional on any machine
```

--------------------------------

### Install Dependencies for SQLCoder

Source: https://github.com/defog-ai/sqlcoder/blob/main/defog_sqlcoder_colab.ipynb

Installs the required Python libraries including torch, transformers, bitsandbytes, accelerate, and sqlparse to support model inference.

```bash
!pip install torch transformers bitsandbytes accelerate sqlparse
```

--------------------------------

### Serve SQLCoder API Server (CLI)

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Starts only the FastAPI backend server without the web interface. This is useful for headless deployments or when integrating with custom frontends.

```bash
# Start only the API server
sqlcoder serve-webserver

# Server runs on http://localhost:1235
# Available endpoints:
#   GET  /         - Version info
#   GET  /health   - Health check
#   POST /query    - Generate and execute SQL
#   POST /integration/* - Database integration endpoints
```

--------------------------------

### GET /

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Retrieves the current version information of the SQLCoder service.

```APIDOC
## GET /

### Description
Returns the current SQLCoder version information.

### Method
GET

### Endpoint
/

### Response
#### Success Response (200)
- **SQLCoder** (string) - The current version string.

#### Response Example
{
  "SQLCoder": "0.2.5"
}
```

--------------------------------

### Get Hardware Device Information

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Queries the server to determine the hardware type being used for model inference, such as gpu, apple_silicon, or cpu.

```bash
curl -X POST http://localhost:1235/get_device_type
```

--------------------------------

### GET /health

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Checks the operational status of the API server.

```APIDOC
## GET /health

### Description
Checks if the API server is running and healthy.

### Method
GET

### Endpoint
/health

### Response
#### Success Response (200)
- **status** (string) - The health status of the server.

#### Response Example
{
  "status": "ok"
}
```

--------------------------------

### Launch SQLCoder Application (CLI)

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Launches the complete SQLCoder application including both the API server and web interface. It automatically downloads the appropriate model based on detected hardware (full model for GPU, quantized GGUF for CPU/Apple Silicon).

```bash
# Launch the full SQLCoder application
sqlcoder launch

# Output:
# Downloading the SQLCoder-7b-2 model. This is a ~14GB file and may take a long time to download...
# Starting SQLCoder server...
# Serving static server...
# Press Ctrl+C to exit.

# The web interface opens at http://localhost:8002
# The API server runs at http://localhost:1235
```

--------------------------------

### Serve SQLCoder Web Interface (CLI)

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Serves only the static web frontend. This can be used independently when the API server is hosted elsewhere.

```bash
# Serve only the web interface
sqlcoder serve-static

# Output:
# Static folder is /path/to/sqlcoder/static
# Opens browser at http://localhost:8002
```

--------------------------------

### Manage Database Integrations and Metadata

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Endpoints for retrieving database credentials, generating table lists, and managing schema metadata required for SQL generation.

```bash
curl -X POST http://localhost:1235/integration/get_tables_db_creds -H "Content-Type: application/json"
curl -X POST http://localhost:1235/integration/generate_tables -H "Content-Type: application/json" -d '{"db_type": "postgres", "db_creds": {"host": "localhost", "port": 5432, "database": "salesdb", "user": "admin", "password": "secret"}}'
curl -X POST http://localhost:1235/integration/generate_metadata -H "Content-Type: application/json" -d '{"tables": ["products", "sales", "customers"]}'
curl -X POST http://localhost:1235/integration/get_metadata -H "Content-Type: application/json"
```

--------------------------------

### Generate Prompt for SQLCoder (Python)

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Constructs a formatted prompt for the SQLCoder model by combining a natural language question with database schema metadata. It reads template files and interpolates the user's question with table definitions.

```python
from inference import generate_prompt

# Generate a prompt using custom metadata
prompt = generate_prompt(
    question="What are the top 5 products by total sales revenue?",
    prompt_file="prompt.md",
    metadata_file="metadata.sql"
)

print(prompt)
```

--------------------------------

### Load SQLCoder Model and Tokenizer (Python)

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Loads the SQLCoder model and tokenizer from HuggingFace Hub with optimized settings for inference. The model is loaded with float16 precision and automatic device mapping for GPU utilization.

```python
from inference import get_tokenizer_model
import torch

# Load the SQLCoder-7b-2 model
tokenizer, model = get_tokenizer_model("defog/sqlcoder-7b-2")

# Model is loaded with:
# - torch_dtype=torch.float16 for memory efficiency
# - device_map="auto" for automatic GPU placement
# - use_cache=True for faster generation

print(f"Tokenizer vocab size: {tokenizer.vocab_size}")
print(f"Model device: {model.device}")
```

--------------------------------

### POST /integration/generate_tables

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Connects to a database to discover and store table information.

```APIDOC
## POST /integration/generate_tables

### Description
Connects to the database and generates a list of all available tables, storing the result in the local configuration.

### Method
POST

### Endpoint
/integration/generate_tables

### Request Body
- **db_type** (string) - Required - The type of database (e.g., postgres).
- **db_creds** (object) - Required - Database connection credentials.

### Response
#### Success Response (200)
- **tables** (array) - List of discovered table names.

#### Response Example
{
  "tables": ["products", "customers"]
}
```

--------------------------------

### Generate and Execute SQL via REST API

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Sends a natural language question to the /query endpoint to generate a SQL query and execute it against the database. Returns the generated query and the resulting data.

```bash
curl -X POST http://localhost:1235/query \
  -H "Content-Type: application/json" \
  -d '{
    "question": "What are the top 3 best-selling products?"
  }'
```

--------------------------------

### POST /query

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Generates a SQL query from a natural language question and executes it against the database.

```APIDOC
## POST /query

### Description
Generates a SQL query from a natural language question using the loaded database metadata, then executes it against the connected database.

### Method
POST

### Endpoint
/query

### Request Body
- **question** (string) - Required - The natural language question to convert to SQL.

### Response
#### Success Response (200)
- **query_generated** (string) - The SQL query produced by the model.
- **data** (array) - The result set from the query execution.
- **columns** (array) - The column names of the result set.
- **ran_successfully** (boolean) - Indicates if the query execution succeeded.

#### Response Example
{
  "query_generated": "SELECT p.name, SUM(s.quantity) AS total_sold FROM products p JOIN sales s ON p.product_id = s.product_id GROUP BY p.name ORDER BY total_sold DESC LIMIT 3;",
  "data": [["Widget A", 150], ["Widget B", 120]],
  "columns": ["name", "total_sold"],
  "ran_successfully": true
}
```

--------------------------------

### Run SQL Inference via CLI

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Executes the SQLCoder inference script from the command line. It accepts a natural language question as an argument and outputs the generated SQL query.

```bash
python inference.py -q "What are the total sales by region for each salesperson?"
python inference.py
```

--------------------------------

### Run End-to-End SQL Inference (Python)

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Performs end-to-end SQL generation from a natural language question. This function loads the model, generates the prompt, and uses beam search to produce high-quality SQL queries.

```python
from inference import run_inference

# Generate SQL for a business question
question = "Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two."

sql_query = run_inference(
    question=question,
    prompt_file="prompt.md",
    metadata_file="metadata.sql"
)

print(sql_query)
```

--------------------------------

### Generate SQL Query using Python

Source: https://github.com/defog-ai/sqlcoder/blob/main/defog_sqlcoder_colab.ipynb

A Python function that takes a natural language question, formats it into a prompt, and uses a pre-trained model to generate a SQL query. It includes steps for tokenization, model inference, and SQL parsing for reformatting.

```python
import sqlparse

def generate_query(question):
    updated_prompt = prompt.format(question=question)
    inputs = tokenizer(updated_prompt, return_tensors="pt").to("cuda")
    generated_ids = model.generate(
        **inputs,
        num_return_sequences=1,
        eos_token_id=tokenizer.eos_token_id,
        pad_token_id=tokenizer.eos_token_id,
        max_new_tokens=400,
        do_sample=False,
        num_beams=1,
    )
    outputs = tokenizer.batch_decode(generated_ids, skip_special_tokens=True)

    torch.cuda.empty_cache()
    torch.cuda.synchronize()
    # empty cache so that you do generate more results w/o memory crashing
    # particularly important on Colab – memory management is much more straightforward
    # when running on an inference service
    return sqlparse.format(outputs[0].split("[SQL]")[-1], reindent=True)
```

--------------------------------

### Generate SQL Query from Question and Schema

Source: https://github.com/defog-ai/sqlcoder/blob/main/prompt.md

This snippet demonstrates how SQLCoder generates a SQL query to answer a user's question based on the provided database schema. It handles cases where the question cannot be answered by returning a specific string.

```SQL
SELECT column1, column2 FROM table_name WHERE condition;
```

--------------------------------

### Load SQLCoder-7b-2 Model

Source: https://github.com/defog-ai/sqlcoder/blob/main/defog_sqlcoder_colab.ipynb

Loads the SQLCoder-7b-2 model from Hugging Face. It automatically selects between float16 precision for high-memory systems or 8-bit quantization for systems with limited VRAM.

```python
model_name = "defog/sqlcoder-7b-2"
tokenizer = AutoTokenizer.from_pretrained(model_name)

if available_memory > 15e9:
    model = AutoModelForCausalLM.from_pretrained(
        model_name,
        trust_remote_code=True,
        torch_dtype=torch.float16,
        device_map="auto",
        use_cache=True,
    )
else:
    model = AutoModelForCausalLM.from_pretrained(
        model_name,
        trust_remote_code=True,
        load_in_8bit=True,
        device_map="auto",
        use_cache=True,
    )
```

--------------------------------

### Verify GPU Availability and Memory

Source: https://github.com/defog-ai/sqlcoder/blob/main/defog_sqlcoder_colab.ipynb

Checks if a CUDA-enabled GPU is available and retrieves the total memory capacity to determine the optimal loading strategy for the model.

```python
import torch
from transformers import AutoTokenizer, AutoModelForCausalLM

# Check if CUDA is available
torch.cuda.is_available()

# Get total memory of the first GPU
available_memory = torch.cuda.get_device_properties(0).total_memory
print(available_memory)
```

--------------------------------

### Retrieve API Version and Health Status

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Endpoints to verify the server is operational and to check the current version of the SQLCoder service.

```bash
curl -X GET http://localhost:1235/
curl -X GET http://localhost:1235/health
```

--------------------------------

### Update Metadata via REST API (Bash)

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Updates the stored metadata with custom column descriptions using a cURL command. This helps SQLCoder generate more accurate queries by understanding the semantic meaning of columns. It sends a JSON payload containing table, column, data type, and description information.

```bash
curl -X POST http://localhost:1235/integration/update_metadata \
  -H "Content-Type: application/json" \
  -d '{
    "metadata": [
      {
        "table_name": "products",
        "column_name": "product_id",
        "data_type": "INTEGER",
        "column_description": "Unique identifier for each product in the catalog"
      },
      {
        "table_name": "products",
        "column_name": "price",
        "data_type": "DECIMAL(10,2)",
        "column_description": "Retail price in USD"
      },
      {
        "table_name": "sales",
        "column_name": "quantity",
        "data_type": "INTEGER",
        "column_description": "Number of units sold in this transaction"
      }
    ]
  }'
```

--------------------------------

### Database Schema Definition

Source: https://github.com/defog-ai/sqlcoder/blob/main/defog_sqlcoder_colab.ipynb

Defines the tables and relationships for the SQLCoder database, including products, customers, salespeople, sales, and product suppliers.

```sql
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY, -- Unique ID for each product
  name VARCHAR(50), -- Name of the product
  price DECIMAL(10,2), -- Price of each unit of the product
  quantity INTEGER  -- Current quantity in stock
);

CREATE TABLE customers (
   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer
   name VARCHAR(50), -- Name of the customer
   address VARCHAR(100) -- Mailing address of the customer
);

CREATE TABLE salespeople (
  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson
  name VARCHAR(50), -- Name of the salesperson
  region VARCHAR(50) -- Geographic sales region
);

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale
  product_id INTEGER, -- ID of product sold
  customer_id INTEGER,  -- ID of customer who made purchase
  salesperson_id INTEGER, -- ID of salesperson who made the sale
  sale_date DATE, -- Date the sale occurred
  quantity INTEGER -- Quantity of product sold
);

CREATE TABLE product_suppliers (
  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier
  product_id INTEGER, -- Product ID supplied
  supply_price DECIMAL(10,2) -- Unit price charged by supplier
);

-- sales.product_id can be joined with products.product_id
-- sales.customer_id can be joined with customers.customer_id
-- sales.salesperson_id can be joined with salespeople.salesperson_id
-- product_suppliers.product_id can be joined with products.product_id
```

--------------------------------

### POST /integration/update_metadata

Source: https://context7.com/defog-ai/sqlcoder/llms.txt

Updates the stored metadata with custom column descriptions to improve SQLCoder query accuracy.

```APIDOC
## POST /integration/update_metadata

### Description
Updates the stored metadata with custom column descriptions. Adding descriptions helps SQLCoder generate more accurate queries by understanding the semantic meaning of columns.

### Method
POST

### Endpoint
/integration/update_metadata

### Parameters
#### Request Body
- **metadata** (array) - Required - A list of objects containing table_name, column_name, data_type, and column_description.

### Request Example
{
  "metadata": [
    {
      "table_name": "products",
      "column_name": "product_id",
      "data_type": "INTEGER",
      "column_description": "Unique identifier for each product"
    }
  ]
}

### Response
#### Success Response (200)
- **status** (string) - Returns "success" upon completion.

#### Response Example
{
  "status": "success"
}
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.