AI-powered SQL query template generator for MemberJunction. Analyzes your database schema, generates meaningful business questions, creates SQL queries, tests them, refines them through iterative feedback, and exports them to MemberJunction metadata format.
graph TD
A["QueryGen CLI"] --> B["Schema Analysis"]
B --> C["Entity Grouper
(AI Semantic Grouping)"]
C --> D["Question Generator
(Business Questions)"]
D --> E["Vector Similarity
(Few-Shot Examples)"]
E --> F["Query Writer
(Nunjucks SQL)"]
F --> G["Query Tester"]
G -->|fails| H["Query Fixer
(up to 5 attempts)"]
H --> G
G -->|passes| I["Query Evaluator"]
I -->|needs improvement| J["Query Refiner
(up to 3 iterations)"]
J --> G
I -->|passes| K["Validator"]
K --> L["Metadata Exporter"]
style A fill:#2d6a9f,stroke:#1a4971,color:#fff
style C fill:#7c5295,stroke:#563a6b,color:#fff
style D fill:#7c5295,stroke:#563a6b,color:#fff
style E fill:#b8762f,stroke:#8a5722,color:#fff
style F fill:#2d8659,stroke:#1a5c3a,color:#fff
style G fill:#2d6a9f,stroke:#1a4971,color:#fff
style H fill:#b8762f,stroke:#8a5722,color:#fff
style J fill:#b8762f,stroke:#8a5722,color:#fff
style K fill:#2d8659,stroke:#1a5c3a,color:#fff
style L fill:#2d8659,stroke:#1a5c3a,color:#fff
QueryGen automates the creation of SQL query templates through an 11-phase AI-powered pipeline:
Status: Production-ready with complete implementation of all 11 phases.
# From MJ repository root
cd packages/QueryGen
npm install
npm run build
# Link for global CLI usage (optional)
npm link
QueryGen is integrated into the MemberJunction CLI (mj) for seamless workflow integration. You can also use the standalone CLI for direct access.
# Generate queries for all entities
mj querygen generate
# Generate with verbose output
mj querygen generate -v
# Generate for specific entities
mj querygen generate --entities "Customers,Orders,Products"
# Exclude specific schemas
mj querygen generate --exclude-schemas "sys,INFORMATION_SCHEMA"
# Validate existing queries
mj querygen validate
# Export queries from database
mj querygen export
# Generate queries for all entities
mj-querygen generate
# Generate with verbose output
mj-querygen generate -v
# Generate for specific entities
mj-querygen generate -e Customers Orders Products
# Exclude specific schemas
mj-querygen generate -s sys INFORMATION_SCHEMA
# Validate queries with MJ CLI
mj querygen validate --path ./metadata/queries
# Export from database with MJ CLI
mj querygen export --output ./exported-queries
# Or use standalone CLI
mj-querygen validate -p ./metadata/queries
mj-querygen export -o ./exported-queries
mj-querygen generate [options]
Options:
-e, --entities <names...> Specific entities to generate queries for
-x, --exclude-entities <names...> Entities to exclude from generation
-s, --exclude-schemas <names...> Schemas to exclude (default: sys, INFORMATION_SCHEMA)
-m, --max-entities <number> Max entities per group (default: 3)
-r, --max-refinements <number> Max refinement iterations (default: 3)
-f, --max-fixes <number> Max error-fixing attempts (default: 5)
--model <name> Preferred AI model (overrides config)
--vendor <name> Preferred AI vendor (overrides config)
-o, --output <path> Output directory (default: ./metadata/queries)
--mode <mode> Output mode: metadata|database|both (default: metadata)
-v, --verbose Enable verbose output
-h, --help Display help information
mj-querygen validate [options]
Options:
-p, --path <path> Path to queries metadata directory (default: ./metadata/queries)
-v, --verbose Enable verbose output
-h, --help Display help information
mj-querygen export [options]
Options:
-o, --output <path> Output directory (default: ./metadata/queries)
-v, --verbose Enable verbose output
-h, --help Display help information
Add a queryGen section to your mj.config.cjs file:
module.exports = {
// ... other MJ configuration
queryGen: {
// Entity Filtering
includeEntities: [], // Allowlist (if provided, ONLY these entities processed)
excludeEntities: [], // Denylist (ignored if includeEntities is set)
excludeSchemas: ['sys', 'INFORMATION_SCHEMA', '__mj'], // Exclude system schemas
// Entity Grouping
questionsPerGroup: 2, // Questions to generate per entity group
minGroupSize: 2, // Minimum entities per group (multi-entity groups)
maxGroupSize: 3, // Maximum entities per group (keep focused)
// AI Configuration
modelOverride: undefined, // Optional: override AI model (e.g., "GPT-OSS-120B")
vendorOverride: undefined, // Optional: override AI vendor (e.g., "Groq")
embeddingModel: 'text-embedding-3-small', // Embedding model for vector similarity
// Iteration Limits
maxRefinementIterations: 3, // Max query refinement cycles
maxFixingIterations: 5, // Max error-fixing attempts
// Few-Shot Learning
topSimilarQueries: 5, // Number of golden queries to use as examples
// Similarity Weighting (for vector search)
similarityWeights: {
userQuestion: 0.2, // 20% weight for question similarity
description: 0.4, // 40% weight for description similarity
technicalDescription: 0.4 // 40% weight for technical description similarity
},
// Output Configuration
outputMode: 'metadata', // 'metadata', 'database', or 'both'
outputDirectory: './metadata/queries',
outputCategoryDirectory: undefined, // Optional: separate directory for categories
rootQueryCategory: 'Auto-Generated', // Root category for generated queries
autoCreateEntityQueryCategories: false, // Create per-entity subcategories
// Performance
parallelGenerations: 1, // Parallel query generation (future enhancement)
enableCaching: true, // Cache AI prompt results
// Validation
testWithSampleData: true, // Test queries before export
requireMinRows: 0, // Minimum rows required (0 = allow empty results)
maxRefinementRows: 10, // Max rows used for refinement evaluation (cost optimization)
// Logging
verbose: false // Enable verbose logging (all logs gated by this flag)
}
};
Configuration is merged in this order (highest to lowest priority):
QueryGen orchestrates an 11-phase workflow:
┌─────────────────────────────────────────────────────────────┐
│ Phase 1: Entity Analysis │
│ - Load entities from Metadata │
│ - Filter by include/exclude lists │
│ - Build foreign key relationship graph │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 2: Entity Grouping (LLM-Based Semantic Analysis) │
│ - AI analyzes relationship graph for meaningful groupings │
│ - Generates groups of 2-3 related entities │
│ - Includes business context: domain, rationale, questions │
│ - Validates connectivity and deduplicates groups │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 3: Business Question Generation │
│ - Use AI to generate domain-specific questions │
│ - 1-2 questions per entity group │
│ - Vary complexity (simple aggregations → complex joins) │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 4: Vector Similarity Search │
│ - Embed business question using local embeddings │
│ - Find top-K similar golden queries │
│ - Weighted cosine similarity across multiple fields │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 5: SQL Query Generation │
│ - Use AI with few-shot examples │
│ - Generate Nunjucks SQL templates │
│ - Define parameters and output fields │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 6: Query Testing │
│ - Render template with sample parameter values │
│ - Execute SQL against database │
│ - Validate results (row count, schema) │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 7: Error Fixing (if needed) │
│ - Pass error message to AI │
│ - AI fixes SQL syntax/logic errors │
│ - Retry up to maxFixingIterations │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 8: Query Evaluation │
│ - AI evaluates if query answers business question │
│ - Checks result relevance, completeness, correctness │
│ - Generates improvement suggestions │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 9: Query Refinement (if needed) │
│ - AI refines query based on evaluation feedback │
│ - Iterative loop up to maxRefinementIterations │
│ - Returns best refined query │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 10: Validation │
│ - Comprehensive validation of all generated queries │
│ - Type checking, parameter validation, execution tests │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 11: Metadata Export │
│ - Export to JSON metadata files (metadata mode) │
│ - Insert into database tables (database mode) │
│ - Create Queries, Query Fields, Query Params records │
└─────────────────────────────────────────────────────────────┘
See docs/ARCHITECTURE.md for detailed technical architecture.
Uses AI to generate semantically meaningful entity groups with business context:
const grouper = new EntityGrouper(config);
const groups = await grouper.generateEntityGroups(entities, contextUser);
// Returns groups with businessDomain, businessRationale, expectedQuestionTypes
Generates business questions using AI with validation:
const generator = new QuestionGenerator(contextUser, config);
const questions = await generator.generateQuestions(entityGroup);
// Returns validated questions with complexity, aggregation, and join flags
Generates SQL templates using AI with few-shot learning and validation:
const writer = new QueryWriter(contextUser, config);
const query = await writer.generateQuery(
businessQuestion,
entityMetadata,
fewShotExamples
);
// Returns Nunjucks SQL template with parameters
// Includes retry logic with validation feedback (up to 3 attempts)
Tests queries by rendering templates and executing against database:
const tester = new QueryTester(entityMetadata, question, contextUser, config);
const result = await tester.testQuery(query, 5); // max 5 error-fixing attempts
// Returns success, sample rows, or error details
// Integrates with QueryFixer for automatic error correction
Evaluates and refines queries iteratively based on AI feedback:
const refiner = new QueryRefiner(tester, contextUser, config);
const refined = await refiner.refineQuery(
query,
businessQuestion,
entityMetadata
);
// Evaluation → Feedback → Refinement cycle (up to 3 iterations)
// Uses first 10 sample rows for cost optimization
Exports validated queries to MemberJunction metadata JSON format:
const exporter = new MetadataExporter();
const result = await exporter.exportQueries(
validatedQueries,
uniqueCategories,
config.outputDirectory,
config.outputCategoryDirectory
);
// Creates .queries-{timestamp}.json and .query-categories-{timestamp}.json
// Compatible with mj-sync push for database synchronization
Writes queries directly to database as Query entities:
const writer = new QueryDatabaseWriter();
await writer.writeQueriesToDatabase(validatedQueries, contextUser);
// Creates QueryEntity records using GetEntityObject pattern
// QueryFields and QueryParams are automatically extracted by QueryEntity.server.ts
# Generate queries for customer-related entities
mj-querygen generate \
-e Customers Orders "Order Details" Products \
-m 2 \
-v
# Output:
# ✓ Metadata loaded
# ✓ Found 6 entity groups
# ✓ Embedded 20 golden queries
# [1/6] Processing Customers...
# [1/6] ✓ Customers complete (2 queries)
# [2/6] Processing Orders...
# [2/6] ✓ Orders complete (2 queries)
# ...
# ✓ Exported to ./metadata/queries/queries-1234567890.json
#
# ✓ Query generation complete!
#
# Summary:
# Entity Groups Processed: 6
# Queries Generated: 12
# Output Location: ./metadata/queries
# Validate all queries in metadata directory
mj-querygen validate -p ./metadata/queries -v
# Output:
# ✓ Metadata loaded
# ✓ Found 3 query files
# [1/3] Validating queries-1234567890.json...
# [1/3] ✓ Top Customers By Revenue
# [1/3] ✓ Recent Orders By Status
# [1/3] ✗ Product Sales Analysis: Column 'ProductName' not found
# ...
# ⚠ Validation completed with errors
#
# Summary:
# Total Queries: 15
# Passed: 12
# Failed: 3
# Export all queries from database
mj-querygen export -o ./exported-queries -v
# Output:
# ✓ Metadata loaded
# ✓ Found 25 queries
# [1/25] Exporting Customer Summary...
# [1/25] ✓ Exported Customer Summary
# ...
# ✓ All 25 queries exported successfully!
Error: Metadata provider not configured
Solution: Ensure database connection is configured in mj.config.cjs
Error: Prompt 'Business Question Generator' not found
Solution: Sync AI prompts to database with npx mj-sync push
Error: Template rendering failed: Unknown filter 'sqlString'
Solution: Verify QueryParameterProcessor is imported and SQL filters are registered
Error: Query returned no results
Solution: Ensure database has sample data or set requireMinRows: 0
Solutions:
maxEntitiesPerGroup (3 → 2)questionsPerGroup (2 → 1)parallelGenerations (1 → 3)enableCaching: trueSolutions:
topSimilarQueries (5 → 3)maxRefinementIterations (3 → 2)maxFixingIterations (5 → 3)QueryGen can be used as a library in your applications:
import {
EntityGrouper,
QuestionGenerator,
QueryWriter,
QueryTester,
QueryRefiner,
MetadataExporter
} from '@memberjunction/query-gen';
async function generateQueriesForEntity(entityName: string, contextUser: UserInfo) {
// 1. Load entity metadata
const md = new Metadata();
const entity = md.Entities.find(e => e.Name === entityName);
// 2. Create entity group
const grouper = new EntityGrouper();
const groups = await grouper.generateEntityGroups([entity], 1, 1);
// 3. Generate business questions
const questionGen = new QuestionGenerator(contextUser);
const questions = await questionGen.generateQuestions(groups[0]);
// 4. Generate and test SQL queries
const queryWriter = new QueryWriter(contextUser);
const query = await queryWriter.generateQuery(
questions[0],
entityMetadata,
fewShotExamples
);
// 5. Test and refine
const tester = new QueryTester(dataProvider, entityMetadata, questions[0], contextUser);
const testResult = await tester.testQuery(query, 5);
if (testResult.success) {
const refiner = new QueryRefiner(tester, contextUser);
const refined = await refiner.refineQuery(query, questions[0], entityMetadata, 3);
return refined.query;
}
}
See docs/API.md for detailed API documentation.
QueryGen uses AI to analyze database schemas and generate meaningful entity groupings based on business context, replacing traditional deterministic algorithms. Each group includes:
This approach generates more meaningful queries aligned with actual business use cases.
Few-shot learning uses multi-field weighted cosine similarity to find relevant golden query examples:
Each field is embedded separately using local embeddings (text-embedding-3-small) for precise similarity matching.
QueryGen implements a sophisticated refinement loop:
This multi-stage approach ensures high-quality queries that actually solve business problems.
QueryGen generates SQL templates with parameters, but QueryFields and QueryParams are automatically extracted by QueryEntity.server.ts during Save(). This eliminates duplication and ensures SQL is the single source of truth.
QueryGen uses 6 AI prompts, each configured with 6-model failover:
Model/vendor can be overridden with --model and --vendor CLI flags.
text-embedding-3-smallEmbedTextLocal() method--entities or --exclude-entities to focus on specific domains-v flag for detailed logging during initial runsmaxGroupSize: 3 for focused, manageable queries--model and --vendor for cost/performance optimizationmetadata mode for review, database mode for direct importQueryGen includes 20 golden queries in /src/data/golden-queries.json covering common patterns:
To add your own golden queries:
golden-queries.jsonsampleValuequestionsPerGroup: 1 for faster generationmaxRefinementIterations: 0 for draft queriesQueryGen is part of the MemberJunction project. Contributions are welcome!
# Development setup
cd MJ/packages/QueryGen
npm install
npm run build
# Run in watch mode
npm run watch
# Lint and format
npm run lint
npm run format
MIT License - see LICENSE file for details