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:
2. Analyze the requested data operations
3. Identify tables/collections and relationships
4. Determine filters, joins, and aggregations needed
5. Consider performance implications

1. Detect Database Type:
2. PostgreSQL, MySQL, SQLite (SQL databases)
3. MongoDB, DynamoDB (NoSQL databases)
4. Check for ORM usage (Prisma, TypeORM, SQLAlchemy, Mongoose)

1. Generate Queries:
2. Write optimized, readable queries
3. Use appropriate indexes and query patterns
4. Include parameterized queries to prevent SQL injection
5. Provide both raw SQL and ORM versions when applicable

1. Explain Query:
2. Break down query execution flow
3. Highlight performance considerations
4. Suggest indexes if needed
5. 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';

Aggregations

-- Group by with aggregations
SELECT
  DATE_TRUNC('day', created_at) as date,
  COUNT(*) as order_count,
  SUM(total_amount) as daily_revenue,
  AVG(total_amount) as avg_order_value,
  MAX(total_amount) as largest_order
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY date DESC;

-- Window functions
SELECT
  id,
  user_id,
  total_amount,
  created_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as order_rank,
  AVG(total_amount) OVER (PARTITION BY user_id) as user_avg_order
FROM orders;

Subqueries

-- Subquery in WHERE clause
SELECT * FROM users
WHERE id IN (
  SELECT DISTINCT user_id
  FROM orders
  WHERE total_amount > 1000
);

-- Subquery in SELECT (scalar subquery)
SELECT
  id,
  username,
  (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count,
  (SELECT MAX(total_amount) FROM orders WHERE user_id = users.id) as max_order
FROM users;

-- Common Table Expression (CTE)
WITH recent_orders AS (
  SELECT
    user_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent
  FROM orders
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT
  u.id,
  u.username,
  u.email,
  COALESCE(ro.order_count, 0) as recent_orders,
  COALESCE(ro.total_spent, 0) as recent_spending
FROM users u
LEFT JOIN recent_orders ro ON u.id = ro.user_id
WHERE u.active = true;

Complex Queries

-- Recursive CTE for hierarchical data
WITH RECURSIVE category_tree AS (
  -- Base case: root categories
  SELECT id, name, parent_id, 0 as level
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive case: child categories
  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree
ORDER BY level, name;

-- Find top N per group
WITH ranked_products AS (
  SELECT
    p.*,
    c.name as category_name,
    ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.sales DESC) as rank
  FROM products p
  INNER JOIN categories c ON p.category_id = c.id
)
SELECT * FROM ranked_products
WHERE rank <= 3;

UPSERT (INSERT or UPDATE)

-- PostgreSQL - ON CONFLICT
INSERT INTO users (id, username, email, updated_at)
VALUES ($1, $2, $3, NOW())
ON CONFLICT (id)
DO UPDATE SET
  username = EXCLUDED.username,
  email = EXCLUDED.email,
  updated_at = NOW();

-- MySQL - ON DUPLICATE KEY UPDATE
INSERT INTO users (id, username, email, updated_at)
VALUES (?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE
  username = VALUES(username),
  email = VALUES(email),
  updated_at = NOW();

ORM Query Examples

Prisma (TypeScript)

// Basic query
const users = await prisma.user.findMany({
  where: {
    active: true,
    createdAt: {
      gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)
    }
  },
  orderBy: { createdAt: 'desc' },
  take: 100
});

// Relations
const userWithOrders = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    orders: {
      where: { status: 'completed' },
      include: {
        items: {
          include: { product: true }
        }
      }
    }
  }
});

// Aggregations
const stats = await prisma.order.groupBy({
  by: ['userId'],
  where: {
    createdAt: {
      gte: new Date('2024-01-01')
    }
  },
  _count: { id: true },
  _sum: { totalAmount: true },
  _avg: { totalAmount: true }
});

// Raw SQL when needed
const result = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE email = ${email}
  AND active = true
`;

TypeORM (TypeScript)

// Query builder
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.active = :active', { active: true })
  .andWhere('user.createdAt >= :date', {
    date: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)
  })
  .orderBy('user.createdAt', 'DESC')
  .take(100)
  .getMany();

// Relations
const userWithOrders = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.orders', 'order')
  .leftJoinAndSelect('order.items', 'item')
  .leftJoinAndSelect('item.product', 'product')
  .where('user.id = :id', { id: userId })
  .andWhere('order.status = :status', { status: 'completed' })
  .getOne();

// Aggregations
const stats = await dataSource
  .getRepository(Order)
  .createQueryBuilder('order')
  .select('order.userId', 'userId')
  .addSelect('COUNT(order.id)', 'orderCount')
  .addSelect('SUM(order.totalAmount)', 'totalSpent')
  .addSelect('AVG(order.totalAmount)', 'avgOrder')
  .where('order.createdAt >= :date', { date: new Date('2024-01-01') })
  .groupBy('order.userId')
  .getRawMany();

SQLAlchemy (Python)

from sqlalchemy import select, func, and_, or_
from datetime import datetime, timedelta

# Basic query
stmt = (
    select(User)
    .where(
        and_(
            User.active == True,
            User.created_at >= datetime.now() - timedelta(days=30)
        )
    )
    .order_by(User.created_at.desc())
    .limit(100)
)
users = session.execute(stmt).scalars().all()

# Joins
stmt = (
    select(User, Order)
    .join(Order, User.id == Order.user_id)
    .where(Order.status == 'completed')
    .order_by(Order.created_at.desc())
)
results = session.execute(stmt).all()

# Aggregations
s