zhongwei/gh-jeremylongshore-claude-code-plugins-plus-plugins-packages-fullstack-starter-pack

Files

Zhongwei Li 10052112c1 Initial commit

2025-11-30 08:20:34 +08:00

9.8 KiB

Raw Permalink Blame History

description, shortcut, category, difficulty, estimated_time

description	shortcut	category	difficulty	estimated_time
Generate optimized SQL queries from natural language descriptions	sqb	database	beginner	2-3 minutes

SQL Query Builder

Generates optimized SQL queries from natural language descriptions, supporting SELECT, INSERT, UPDATE, DELETE with JOINs, indexes, and best practices.

What This Command Does

Generated Queries:

SELECT queries with JOINs
INSERT, UPDATE, DELETE operations
Aggregations and GROUP BY
Subqueries and CTEs
Indexes and optimization tips
PostgreSQL, MySQL, SQLite syntax

Output: Production-ready SQL queries

Time: 2-3 minutes

Usage

# Generate query from description
/sql-query-builder "Get all users with their posts"

# Shortcut
/sqb "Find top 10 products by sales"

# Complex query
/sqb "Get users who have made purchases in last 30 days, grouped by country"

# Specific database
/sqb "List orders with customer info" --database postgresql

Example Output

Input:

/sqb "Get all blog posts with author info and comment count, ordered by recent"

Generated SQL:

-- Query: Get all blog posts with author info and comment count
-- Database: PostgreSQL
-- Optimized for performance with proper indexes

-- Main query
SELECT
  p.id,
  p.title,
  p.slug,
  p.content,
  p.published,
  p.created_at,
  p.updated_at,
  -- Author information
  u.id AS author_id,
  u.name AS author_name,
  u.email AS author_email,
  -- Comment count (calculated)
  COUNT(c.id) AS comment_count
FROM posts p
INNER JOIN users u ON p.author_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.published = true
GROUP BY
  p.id,
  p.title,
  p.slug,
  p.content,
  p.published,
  p.created_at,
  p.updated_at,
  u.id,
  u.name,
  u.email
ORDER BY p.created_at DESC
LIMIT 20 OFFSET 0;

-- Recommended indexes for optimal performance
CREATE INDEX IF NOT EXISTS idx_posts_published_created ON posts(published, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_posts_author_id ON posts(author_id);
CREATE INDEX IF NOT EXISTS idx_comments_post_id ON comments(post_id);

-- Explain plan (run to verify performance)
EXPLAIN ANALYZE
SELECT /* ... query above ... */;

Query Examples

1. Simple SELECT

Request: "Get all active users"

SELECT
  id,
  email,
  name,
  created_at
FROM users
WHERE active = true
ORDER BY created_at DESC;

-- Index recommendation
CREATE INDEX idx_users_active ON users(active, created_at DESC);

2. JOIN Queries

Request: "Get orders with customer and product information"

SELECT
  o.id AS order_id,
  o.order_date,
  o.total,
  o.status,
  -- Customer info
  c.id AS customer_id,
  c.name AS customer_name,
  c.email AS customer_email,
  -- Order items
  oi.quantity,
  oi.price AS unit_price,
  -- Product info
  p.id AS product_id,
  p.name AS product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.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 >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY o.created_at DESC;

-- Indexes
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

3. Aggregations

Request: "Get total sales by product category"

SELECT
  c.name AS category,
  COUNT(DISTINCT o.id) AS order_count,
  SUM(oi.quantity) AS units_sold,
  SUM(oi.quantity * oi.price) AS total_revenue,
  AVG(oi.price) AS avg_price
FROM categories c
INNER JOIN products p ON c.id = p.category_id
INNER JOIN order_items oi ON p.id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
  AND o.created_at >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY c.id, c.name
HAVING SUM(oi.quantity * oi.price) > 1000
ORDER BY total_revenue DESC;

4. Subqueries

Request: "Get users who have never made a purchase"

SELECT
  u.id,
  u.email,
  u.name,
  u.created_at
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = u.id
)
ORDER BY u.created_at DESC;

-- Alternative using LEFT JOIN (often faster)
SELECT
  u.id,
  u.email,
  u.name,
  u.created_at
FROM users u
LEFT JOIN orders o ON u.id = o.customer_id
WHERE o.id IS NULL
ORDER BY u.created_at DESC;

5. Common Table Expressions (CTEs)

Request: "Get top customers by purchase amount with their order history"

WITH customer_totals AS (
  SELECT
    c.id,
    c.name,
    c.email,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
  FROM customers c
  INNER JOIN orders o ON c.id = o.customer_id
  WHERE o.status = 'completed'
  GROUP BY c.id, c.name, c.email
  HAVING SUM(o.total) > 500
)
SELECT
  ct.*,
  o.id AS order_id,
  o.order_date,
  o.total AS order_total
FROM customer_totals ct
INNER JOIN orders o ON ct.id = o.customer_id
ORDER BY ct.total_spent DESC, o.order_date DESC;

6. Window Functions

Request: "Rank products by sales within each category"

SELECT
  p.id,
  p.name AS product_name,
  c.name AS category_name,
  SUM(oi.quantity * oi.price) AS total_sales,
  RANK() OVER (
    PARTITION BY p.category_id
    ORDER BY SUM(oi.quantity * oi.price) DESC
  ) AS rank_in_category
FROM products p
INNER JOIN categories c ON p.category_id = c.id
INNER JOIN order_items oi ON p.id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY p.id, p.name, p.category_id, c.name
ORDER BY c.name, rank_in_category;

7. INSERT Queries

Request: "Insert new user with validation"

-- Insert single user
INSERT INTO users (id, email, name, password, created_at, updated_at)
VALUES (
  gen_random_uuid(),
  '[email protected]',
  'John Doe',
  'hashed_password_here',
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
)
ON CONFLICT (email) DO NOTHING
RETURNING id, email, name, created_at;

-- Bulk insert
INSERT INTO users (id, email, name, password, created_at, updated_at)
VALUES
  (gen_random_uuid(), '[email protected]', 'User 1', 'hash1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  (gen_random_uuid(), '[email protected]', 'User 2', 'hash2', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  (gen_random_uuid(), '[email protected]', 'User 3', 'hash3', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON CONFLICT (email) DO NOTHING;

8. UPDATE Queries

Request: "Update product stock after order"

-- Single update
UPDATE products
SET
  stock = stock - 5,
  updated_at = CURRENT_TIMESTAMP
WHERE id = 'product-uuid-here'
  AND stock >= 5 -- Safety check
RETURNING id, name, stock;

-- Batch update with JOIN
UPDATE products p
SET
  stock = p.stock - oi.quantity,
  updated_at = CURRENT_TIMESTAMP
FROM order_items oi
WHERE p.id = oi.product_id
  AND oi.order_id = 'order-uuid-here'
  AND p.stock >= oi.quantity;

9. DELETE Queries

Request: "Delete old inactive users"

-- Soft delete (recommended)
UPDATE users
SET
  deleted_at = CURRENT_TIMESTAMP,
  updated_at = CURRENT_TIMESTAMP
WHERE active = false
  AND last_login_at < CURRENT_DATE - INTERVAL '1 year'
RETURNING id, email;

-- Hard delete (with safety checks)
DELETE FROM users
WHERE active = false
  AND last_login_at < CURRENT_DATE - INTERVAL '2 years'
  AND id NOT IN (
    SELECT DISTINCT customer_id FROM orders
  );

10. Full-Text Search

Request: "Search blog posts by keyword"

PostgreSQL:

-- Create text search index
CREATE INDEX idx_posts_search ON posts
USING GIN (to_tsvector('english', title || ' ' || content));

-- Search query
SELECT
  id,
  title,
  content,
  ts_rank(
    to_tsvector('english', title || ' ' || content),
    plainto_tsquery('english', 'search keywords')
  ) AS relevance
FROM posts
WHERE to_tsvector('english', title || ' ' || content) @@
      plainto_tsquery('english', 'search keywords')
  AND published = true
ORDER BY relevance DESC, created_at DESC
LIMIT 20;

MySQL:

-- Create fulltext index
CREATE FULLTEXT INDEX idx_posts_search ON posts(title, content);

-- Search query
SELECT
  id,
  title,
  content,
  MATCH(title, content) AGAINST('search keywords' IN NATURAL LANGUAGE MODE) AS relevance
FROM posts
WHERE MATCH(title, content) AGAINST('search keywords' IN NATURAL LANGUAGE MODE)
  AND published = true
ORDER BY relevance DESC, created_at DESC
LIMIT 20;

Optimization Tips

1. Use Indexes Wisely:

--  GOOD: Index foreign keys
CREATE INDEX idx_posts_author_id ON posts(author_id);

--  GOOD: Index columns in WHERE clauses
CREATE INDEX idx_posts_published ON posts(published, created_at DESC);

--  GOOD: Partial index for specific queries
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

*2. Avoid SELECT :

--  BAD
SELECT * FROM users;

--  GOOD
SELECT id, email, name FROM users;

3. Use LIMIT:

--  BAD (fetches all rows)
SELECT * FROM posts ORDER BY created_at DESC;

--  GOOD (pagination)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;

4. Optimize JOINs:

-- Use INNER JOIN when possible (faster than LEFT JOIN)
-- Use EXISTS instead of IN for large datasets

--  BAD
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

--  GOOD
SELECT u.* FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Database-Specific Syntax