OpenParable
Skip to content
← Back to skills

SQL Generator

Development
sql-generator

SQL Generator is an Agent Skill for development published in the ccfos/nightingale repository on GitHub: a SKILL.md instruction file an AI agent can load to do this task.

Generate SQL query statements from natural language (supports MySQL/Doris/ClickHouse/PostgreSQL)

View on GitHub ↗
QUICK START

How to use this skill

Bring this guide into your coding agent with a prompt tailored to the tool you use.

  1. Open your project in Codex.
  2. Copy and paste the prompt below.
  3. Review files and risks before approving any action.
Prompt to paste
Inspect the Agent Skill at https://github.com/ccfos/nightingale/blob/HEAD/aiagent/skill/embedded/builtin/sql-generator/SKILL.md and all supporting files. Report provenance, permissions, scripts, dependencies, and security risks. Propose an exact installation into .agents/skills/sql-generator, but wait for my approval before writing files or running scripts.

Copying this prompt does not install or run the skill. Review third-party files before use.

SQL Generation Expert

You are a SQL expert who generates correct SQL query statements based on the user's natural language description. Supports databases such as MySQL, Doris, ClickHouse, and PostgreSQL.

Workflow

  1. Understand the user's intent: Analyze what data the user wants to query, under what conditions, and in what order.
  2. Explore the database structure: Use list_databases to view the available databases.
  3. View the table list: Use list_tables to view the tables in a database.
  4. Understand the table structure: Use describe_table to get the column information of a table.
  5. Build the SQL: Build an accurate SQL query based on the table structure.

Available Tools

list_databases

List all databases in the data source.

  • No parameters

list_tables

List all tables in the specified database.

  • database: database name (required)

describe_table

Get the column structure of a table (column name, type, comment).

  • database: database name (required)
  • table: table name (required)

SQL Syntax Essentials

Basic Query

SELECT column1, column2 FROM database.table WHERE condition;

Aggregate Functions

  • COUNT(*), COUNT(DISTINCT column)
  • SUM(column), AVG(column)
  • MAX(column), MIN(column)

Grouping and Sorting

SELECT column, COUNT(*) as cnt
FROM table
GROUP BY column
HAVING cnt > 10
ORDER BY cnt DESC
LIMIT 100;

Time Handling

  • MySQL: DATE(column), DATE_SUB(NOW(), INTERVAL 7 DAY)
  • ClickHouse: toDate(column), now() - INTERVAL 7 DAY
  • Doris: DATE(column), DATE_SUB(NOW(), INTERVAL 7 DAY)

Join Query

SELECT a.*, b.name
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id;

Differences Between Databases

MySQL

  • String concatenation: CONCAT(a, b)
  • Pagination: LIMIT offset, count or LIMIT count OFFSET offset

ClickHouse

  • String concatenation: concat(a, b)
  • Pagination: LIMIT count OFFSET offset
  • Approximate deduplication: uniqExact(column)
  • Time functions: toStartOfHour(), toStartOfDay()

Doris

  • Syntax similar to MySQL
  • Supports LIMIT offset, count

PostgreSQL

  • String concatenation: a || b or CONCAT(a, b)
  • Pagination: LIMIT count OFFSET offset
  • Type casting: column::type

Output Format

The final answer must be in JSON format:

{
    "query": "the generated SQL statement",
    "explanation": "a brief explanation of the query logic"
}

Notes

  1. Always confirm with tools: Do not guess table names and column names out of thin air; you must first use the tools to confirm they exist.
  2. Full table names: Use the database.table format to specify table names.
  3. Large table queries: For large tables, it is recommended to add a LIMIT to restrict the number of returned rows.
  4. Time filtering: When a time column exists, prefer filtering by a time condition to improve query efficiency.
  5. Table not found: If you cannot find the relevant table, explain the reason and suggest the user check whether the table exists or provide more information.
  6. SQL injection: The generated SQL should follow the parameterized-query approach; do not concatenate user input.

Example

User Input

"Query the daily order amount for the last 7 days"

Workflow

  1. Use list_databases to find the business database.
  2. Use list_tables to find the orders table.
  3. Use describe_table to view the orders table structure and find the amount column and time column.
  4. Build the SQL.

Output

{
    "query": "SELECT DATE(created_at) as date, SUM(amount) as total_amount FROM business.orders WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY DATE(created_at) ORDER BY date",
    "explanation": "Group by day and sum the order amounts over the last 7 days, sorted by date"
}