cockroachdb-sql

CockroachDB SQL Skill

Converts natural language questions into CockroachDB-compliant SQL queries, following CockroachDB best practices. Use it for schema design, writing queries and optimizing query.

How to Apply this Skill

1. Connection Detection — already performed on skill invocation; reuse active connection.

2. Parse Natural Language Intent

   • Identify the operation type (SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, etc.)

3. Context Gathering

   • Check for existing schema context in conversation
   • If connected to DB, query existing schema:
     • SHOW TABLES; to see existing tables
     • SHOW CREATE TABLE table_name; for existing structure
   • Ask clarifying questions if needed:
     • Table structure if not provided
     • Data types for columns
     • Index requirements
     • Multi-region needs
     • Performance characteristics

4. Apply CockroachDB Rules

   • Reference rules in references/cockroachdb-rules/
   • Ensure compliance with CockroachDB best practices
   • Determine rule category based on operation and apply the relavant rules:
     • 00-fundamental-principles.md - Always apply these first
     • 01-schema-design.md - Table creation and structure
     • 02-dml-operations.md - Data modification
     • 03-query-patterns.md - Query construction
     • 04-optimization.md - Performance, Optimization and anti-patterns
     • 05-operational.md - Admin and maintenance
   • Validate against anti-patterns in 04-optimization.md

5. Validate against DB(MANDATORY)

   • ALWAYS run EXPLAIN on every generated SQL query when connected to DB.
   • If EXPLAIN returns a parsing/syntax error, fix the query and re-run EXPLAIN until it passes.
   • Include the EXPLAIN output in the response.

Response Behavior

Initial Response

When skill is invoked:

1. Check for an available connection so queries run against the right cluster:
   • Check if a connection string is provided in the prompt (postgresql://...).
     • If provided, use cockroach sql --url "<provided-url>" -e "SQL" to run queries. Do not use psql.
   • Else check the COCKROACH_URL environment variable (echo $COCKROACH_URL).
     • If set, use cockroach sql --url $COCKROACH_URL -e "SQL" to run queries. Do not use psql.
   • Else check for cockroach-cloud MCP server availability.
   • If none of these are available or it is unclear which cluster to use, ask the user before running anything.