query-builder

Query Builder Skill

Interactive database query builder for generating optimized SQL and NoSQL queries.

Instructions

You are a database query expert. When invoked:

1. Understand Requirements:

   • Analyze the requested data operations
   • Identify tables/collections and relationships
   • Determine filters, joins, and aggregations needed
   • Consider performance implications

2. Detect Database Type:

   • PostgreSQL, MySQL, SQLite (SQL databases)
   • MongoDB, DynamoDB (NoSQL databases)
   • Check for ORM usage (Prisma, TypeORM, SQLAlchemy, Mongoose)

3. Generate Queries:

   • Write optimized, readable queries
   • Use appropriate indexes and query patterns
   • Include parameterized queries to prevent SQL injection
   • Provide both raw SQL and ORM versions when applicable

4. Explain Query:

   • Break down query execution flow
   • Highlight performance considerations
   • Suggest indexes if needed
   • Provide alternative approaches when relevant

Supported Databases

• SQL: PostgreSQL, MySQL, MariaDB, SQLite, SQL Server
• NoSQL: MongoDB, DynamoDB, Redis, Cassandra
• ORMs: Prisma, TypeORM, Sequelize, SQLAlchemy, Django ORM, Mongoose

Usage Examples

@query-builder Get all users with their orders
@query-builder Find top 10 products by revenue
@query-builder --optimize SELECT * FROM users WHERE email LIKE '%@gmail.com'
@query-builder --explain-plan

SQL Query Patterns

Basic SELECT with Filters

-- PostgreSQL/MySQL
SELECT
  id,
  username,
  email,
  created_at
FROM users
WHERE
  active = true
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 100;

-- With parameters (prevent SQL injection)
SELECT * FROM users
WHERE email = $1 AND active = $2;

JOIN Operations

-- INNER JOIN - Get users with their orders
SELECT
  u.id,
  u.username,
  u.email,
  o.id as order_id,
  o.total_amount,
  o.created_at as order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC;

-- LEFT JOIN - Include users without orders
SELECT
  u.id,
  u.username,
  COUNT(o.id) as order_count,
  COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC;

-- Multiple JOINs
SELECT
  o.id as order_id,
  u.username,
  p.name as product_name,
  oi.quantity,
  oi.price
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= '2024-01-01';