sf-soql: Salesforce SOQL Query Expert
Expert database engineer specializing in Salesforce Object Query Language (SOQL). Generate optimized queries from natural language, analyze query performance, and ensure best practices for governor limits and security.
Core Responsibilities
- Natural Language → SOQL: Convert plain English requests to optimized queries
- Query Optimization: Analyze and improve query performance
- Relationship Queries: Build parent-child and child-parent traversals
- Aggregate Functions: COUNT, SUM, AVG, MIN, MAX with GROUP BY
- Security Enforcement: Ensure FLS and sharing rules compliance
- Governor Limit Awareness: Design queries within limits
Workflow (4-Phase Pattern)
Phase 1: Requirements Gathering
Ask the user to gather:
- What data is needed (objects, fields)
- Filter criteria (WHERE conditions)
- Sort requirements (ORDER BY)
- Record limit requirements
- Use case (display, processing, reporting)
Phase 2: Query Generation
Natural Language Examples:
| Request |
Generated SOQL |
| "Get all active accounts with their contacts" |
SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE IsActive__c = true |
| "Find contacts created this month" |
SELECT Id, Name, Email FROM Contact WHERE CreatedDate = THIS_MONTH |
| "Count opportunities by stage" |
SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY StageName |
| "Get accounts with revenue over 1M sorted by name" |
SELECT Id, Name, AnnualRevenue FROM Account WHERE AnnualRevenue > 1000000 ORDER BY Name |
Phase 3: Optimization
Query Optimization Checklist:
- Selectivity: Does WHERE clause use indexed fields?
- Field Selection: Only query needed fields (not SELECT *)
- Limit: Is LIMIT appropriate for use case?
- Relationship Depth: Avoid deep traversals (max 5 levels)
- Aggregate Queries: Use for counts instead of loading all records
Phase 4: Validation & Execution
# Test query
sf data query --query "SELECT Id, Name FROM Account LIMIT 10" --target-org my-org --json
# Analyze query plan
sf data query --query "..." --target-org my-org --use-tooling-api --plan
Best Practices (100-Point Scoring)
| Category |
Points |
Key Rules |
| Selectivity |
25 |
Indexed fields in WHERE, selective filters |
| Performance |
25 |
Appropriate LIMIT, minimal fields, no unnecessary joins |
| Security |
20 |
WITH SECURITY_ENFORCED or stripInaccessible |
| Correctness |
15 |
Proper syntax, valid field references |
| Readability |
15 |
Formatted, meaningful aliases, comments |
Scoring Thresholds: 90-100 = Production-optimized, 80-89 = Good (minor optimizations possible), 70-79 = Performance concerns, <70 = Needs improvement.
Quick Reference
Security (Always Apply)
-- Enforce FLS (throws exception on inaccessible fields)
SELECT Id, Name, Phone FROM Account WITH SECURITY_ENFORCED
-- Respect sharing rules
SELECT Id, Name FROM Account WITH USER_MODE
See references/query-optimization.md for stripInaccessible in Apex, SYSTEM_MODE, governor limits, SOQL FOR loops, indexing strategy, and selectivity rules.
Governor Limits (Key Numbers)
| Limit |
Synchronous |
Asynchronous |
| Total SOQL Queries |
100 |
200 |
| Records Retrieved |
50,000 |
50,000 |
Anti-pattern: Never query inside a loop. Use Map<Id, SObject> with WHERE Id IN :idSet instead.
SOQL Syntax, Relationships & Aggregates
See references/soql-syntax-reference.md for the complete reference including: basic query structure, WHERE operators, date literals, child-to-parent dot notation, parent-to-child subqueries, relationship names, aggregate functions (COUNT, SUM, AVG, GROUP BY, HAVING, ROLLUP), polymorphic queries (TYPEOF), semi-joins, and anti-joins.
Key patterns:
- Child-to-Parent:
SELECT Contact.Account.Name FROM Case (up to 5 levels)
- Parent-to-Child:
SELECT Id, (SELECT Id FROM Contacts) FROM Account
- Custom relationships: Use
__r suffix (e.g., Custom_Object__r.Name)
- Aggregates:
SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry HAVING COUNT(Id) > 10
Query Optimization
See references/query-optimization.md for indexing strategy, selectivity rules, optimization patterns, query plan analysis, and efficient Apex patterns.
Key rules:
- Use indexed fields in WHERE (Id, Name, CreatedDate, Email, External IDs)
- Trailing wildcards use indexes (
LIKE 'Acme%'), leading wildcards don't (LIKE '%corp')
- Filter in SOQL, not in Apex — use
LIMIT appropriate to use case
- Use
sf data query --plan to analyze query cost
Natural Language Examples
| Request |
SOQL |
| "Get me all accounts" |
SELECT Id, Name FROM Account LIMIT 1000 |
| "Find contacts without email" |
SELECT Id, Name FROM Contact WHERE Email = null |
| "Top 10 opportunities by amount" |
SELECT Id, Name, Amount FROM Opportunity ORDER BY Amount DESC LIMIT 10 |
| "Contacts with @gmail emails" |
SELECT Id, Name, Email FROM Contact WHERE Email LIKE '%@gmail.com' |
| "Opportunities closing this quarter" |
SELECT Id, Name, CloseDate FROM Opportunity WHERE CloseDate = THIS_QUARTER |
| "Total revenue by industry" |
SELECT Industry, SUM(AnnualRevenue) FROM Account GROUP BY Industry |
CLI Commands
# Basic query (JSON output)
sf data query --query "SELECT Id, Name FROM Account LIMIT 10" --target-org my-org --json
# CSV output to file
sf data query --query "SELECT Id, Name FROM Account" --target-org my-org --result-format csv --output-file accounts.csv
# Bulk export (> 2,000 records)
sf data export bulk --query "SELECT Id, Name FROM Account" --target-org my-org --output-file accounts.csv
# SOSL search
sf data search --query "FIND {Acme} IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, Name)" --target-org my-org
Cross-Skill Integration
| Skill |
When to Use |
Example |
| sf-apex |
Embed queries in Apex |
Use the sf-apex skill: "Create service with SOQL query for accounts" |
| sf-data |
Execute queries against org |
Use the sf-data skill: "Query active accounts from production" |
| sf-debug |
Analyze query performance |
Use the sf-debug skill: "Analyze slow query in debug logs" |
| sf-lwc |
Generate wire queries |
Use the sf-lwc skill: "Create component with wired account query" |
Document Map
References (Extracted)
| Document |
Description |
| SOQL Syntax Reference |
Complete syntax, operators, dates, relationships, aggregates, advanced features |
| Query Optimization |
Indexing, selectivity, patterns, query plan, governor limits, security |
Docs
| Document |
Description |
| soql-reference.md |
Complete SOQL syntax reference |
| cli-commands.md |
SF CLI query commands |
| anti-patterns.md |
Common mistakes and how to avoid them |
| selector-patterns.md |
Query abstraction patterns (vanilla Apex) |
| field-coverage-rules.md |
Ensure queries include all accessed fields |
Templates
| Template |
Description |
| basic-queries.soql |
Basic SOQL syntax examples |
| aggregate-queries.soql |
COUNT, SUM, GROUP BY patterns |
| relationship-queries.soql |
Parent-child traversals |
| optimization-patterns.soql |
Selectivity and indexing |
| selector-class.cls |
Selector class template |
| bulkified-query-pattern.cls |
Map-based bulk lookups |
Dependencies
Required: Target org with sf CLI authenticated
Recommended: sf-debug (for query plan analysis), sf-apex (for embedding in Apex code)
Credits
See CREDITS.md for acknowledgments of community resources that shaped this skill.
1---2name: sf-soql-23description: Advanced SOQL skill with natural language to query generation, query optimization, relationship traversal, aggregate functions, and performance analysis. Build efficient queries that respect governor limits and security requirements.4license: MIT5---6
7# sf-soql: Salesforce SOQL Query Expert
8
9Expert database engineer specializing in Salesforce Object Query Language (SOQL). Generate optimized queries from natural language, analyze query performance, and ensure best practices for governor limits and security.
10
11## Core Responsibilities
12
131. **Natural Language → SOQL**: Convert plain English requests to optimized queries
142. **Query Optimization**: Analyze and improve query performance
153. **Relationship Queries**: Build parent-child and child-parent traversals
164. **Aggregate Functions**: COUNT, SUM, AVG, MIN, MAX with GROUP BY
175. **Security Enforcement**: Ensure FLS and sharing rules compliance
186. **Governor Limit Awareness**: Design queries within limits
19
20## Workflow (4-Phase Pattern)
21
22### Phase 1: Requirements Gathering
23
24**Ask the user** to gather:
25- What data is needed (objects, fields)
26- Filter criteria (WHERE conditions)
27- Sort requirements (ORDER BY)
28- Record limit requirements
29- Use case (display, processing, reporting)
30
31### Phase 2: Query Generation
32
33**Natural Language Examples**:
34
35| Request | Generated SOQL |
36|---------|----------------|
37| "Get all active accounts with their contacts" | `SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE IsActive__c = true` |
38| "Find contacts created this month" | `SELECT Id, Name, Email FROM Contact WHERE CreatedDate = THIS_MONTH` |
39| "Count opportunities by stage" | `SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY StageName` |
40| "Get accounts with revenue over 1M sorted by name" | `SELECT Id, Name, AnnualRevenue FROM Account WHERE AnnualRevenue > 1000000 ORDER BY Name` |
41
42### Phase 3: Optimization
43
44**Query Optimization Checklist**:
45
461. **Selectivity**: Does WHERE clause use indexed fields?
472. **Field Selection**: Only query needed fields (not SELECT *)
483. **Limit**: Is LIMIT appropriate for use case?
494. **Relationship Depth**: Avoid deep traversals (max 5 levels)
505. **Aggregate Queries**: Use for counts instead of loading all records
51
52### Phase 4: Validation & Execution
53
54```bash
55# Test query
56sf data query --query "SELECT Id, Name FROM Account LIMIT 10" --target-org my-org --json
57
58# Analyze query plan
59sf data query --query "..." --target-org my-org --use-tooling-api --plan
60```
61
62---
63
64## Best Practices (100-Point Scoring)
65
66| Category | Points | Key Rules |
67|----------|--------|-----------|
68| **Selectivity** | 25 | Indexed fields in WHERE, selective filters |
69| **Performance** | 25 | Appropriate LIMIT, minimal fields, no unnecessary joins |
70| **Security** | 20 | WITH SECURITY_ENFORCED or stripInaccessible |
71| **Correctness** | 15 | Proper syntax, valid field references |
72| **Readability** | 15 | Formatted, meaningful aliases, comments |
73
74**Scoring Thresholds**: 90-100 = Production-optimized, 80-89 = Good (minor optimizations possible), 70-79 = Performance concerns, <70 = Needs improvement.
75
76---
77
78## Quick Reference
79
80### Security (Always Apply)
81
82```sql
83-- Enforce FLS (throws exception on inaccessible fields)
84SELECT Id, Name, Phone FROM Account WITH SECURITY_ENFORCED
85
86-- Respect sharing rules
87SELECT Id, Name FROM Account WITH USER_MODE
88```
89
90> See [references/query-optimization.md](references/query-optimization.md) for `stripInaccessible` in Apex, `SYSTEM_MODE`, governor limits, SOQL FOR loops, indexing strategy, and selectivity rules.
91
92### Governor Limits (Key Numbers)
93
94| Limit | Synchronous | Asynchronous |
95|-------|-------------|--------------|
96| Total SOQL Queries | 100 | 200 |
97| Records Retrieved | 50,000 | 50,000 |
98
99> **Anti-pattern**: Never query inside a loop. Use `Map<Id, SObject>` with `WHERE Id IN :idSet` instead.
100
101---
102
103## SOQL Syntax, Relationships & Aggregates
104
105> See [references/soql-syntax-reference.md](references/soql-syntax-reference.md) for the complete reference including: basic query structure, WHERE operators, date literals, child-to-parent dot notation, parent-to-child subqueries, relationship names, aggregate functions (COUNT, SUM, AVG, GROUP BY, HAVING, ROLLUP), polymorphic queries (TYPEOF), semi-joins, and anti-joins.
106
107**Key patterns:**
108- **Child-to-Parent**: `SELECT Contact.Account.Name FROM Case` (up to 5 levels)
109- **Parent-to-Child**: `SELECT Id, (SELECT Id FROM Contacts) FROM Account`
110- **Custom relationships**: Use `__r` suffix (e.g., `Custom_Object__r.Name`)
111- **Aggregates**: `SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry HAVING COUNT(Id) > 10`
112
113## Query Optimization
114
115> See [references/query-optimization.md](references/query-optimization.md) for indexing strategy, selectivity rules, optimization patterns, query plan analysis, and efficient Apex patterns.
116
117**Key rules:**
118- Use indexed fields in WHERE (Id, Name, CreatedDate, Email, External IDs)
119- Trailing wildcards use indexes (`LIKE 'Acme%'`), leading wildcards don't (`LIKE '%corp'`)
120- Filter in SOQL, not in Apex — use `LIMIT` appropriate to use case
121- Use `sf data query --plan` to analyze query cost
122
123---
124
125## Natural Language Examples
126
127| Request | SOQL |
128|---------|------|
129| "Get me all accounts" | `SELECT Id, Name FROM Account LIMIT 1000` |
130| "Find contacts without email" | `SELECT Id, Name FROM Contact WHERE Email = null` |
131| "Top 10 opportunities by amount" | `SELECT Id, Name, Amount FROM Opportunity ORDER BY Amount DESC LIMIT 10` |
132| "Contacts with @gmail emails" | `SELECT Id, Name, Email FROM Contact WHERE Email LIKE '%@gmail.com'` |
133| "Opportunities closing this quarter" | `SELECT Id, Name, CloseDate FROM Opportunity WHERE CloseDate = THIS_QUARTER` |
134| "Total revenue by industry" | `SELECT Industry, SUM(AnnualRevenue) FROM Account GROUP BY Industry` |
135
136---
137
138## CLI Commands
139
140```bash
141# Basic query (JSON output)
142sf data query --query "SELECT Id, Name FROM Account LIMIT 10" --target-org my-org --json
143
144# CSV output to file
145sf data query --query "SELECT Id, Name FROM Account" --target-org my-org --result-format csv --output-file accounts.csv
146
147# Bulk export (> 2,000 records)
148sf data export bulk --query "SELECT Id, Name FROM Account" --target-org my-org --output-file accounts.csv
149
150# SOSL search
151sf data search --query "FIND {Acme} IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, Name)" --target-org my-org
152```
153
154---
155
156## Cross-Skill Integration
157
158| Skill | When to Use | Example |
159|-------|-------------|---------|
160| sf-apex | Embed queries in Apex | Use the **sf-apex** skill: "Create service with SOQL query for accounts" |
161| sf-data | Execute queries against org | Use the **sf-data** skill: "Query active accounts from production" |
162| sf-debug | Analyze query performance | Use the **sf-debug** skill: "Analyze slow query in debug logs" |
163| sf-lwc | Generate wire queries | Use the **sf-lwc** skill: "Create component with wired account query" |
164
165---
166
167## Document Map
168
169### References (Extracted)
170| Document | Description |
171|----------|-------------|
172| [SOQL Syntax Reference](references/soql-syntax-reference.md) | Complete syntax, operators, dates, relationships, aggregates, advanced features |
173| [Query Optimization](references/query-optimization.md) | Indexing, selectivity, patterns, query plan, governor limits, security |
174
175### Docs
176| Document | Description |
177|----------|-------------|
178| [soql-reference.md](references/soql-reference.md) | Complete SOQL syntax reference |
179| [cli-commands.md](references/cli-commands.md) | SF CLI query commands |
180| [anti-patterns.md](references/anti-patterns.md) | Common mistakes and how to avoid them |
181| [selector-patterns.md](references/selector-patterns.md) | Query abstraction patterns (vanilla Apex) |
182| [field-coverage-rules.md](references/field-coverage-rules.md) | Ensure queries include all accessed fields |
183
184### Templates
185| Template | Description |
186|----------|-------------|
187| [basic-queries.soql](assets/basic-queries.soql) | Basic SOQL syntax examples |
188| [aggregate-queries.soql](assets/aggregate-queries.soql) | COUNT, SUM, GROUP BY patterns |
189| [relationship-queries.soql](assets/relationship-queries.soql) | Parent-child traversals |
190| [optimization-patterns.soql](assets/optimization-patterns.soql) | Selectivity and indexing |
191| [selector-class.cls](assets/selector-class.cls) | Selector class template |
192| [bulkified-query-pattern.cls](assets/bulkified-query-pattern.cls) | Map-based bulk lookups |
193
194---
195
196## Dependencies
197
198**Required**: Target org with `sf` CLI authenticated
199
200**Recommended**: sf-debug (for query plan analysis), sf-apex (for embedding in Apex code)
201
202---
203
204## Credits
205
206See [CREDITS.md](CREDITS.md) for acknowledgments of community resources that shaped this skill.
tools-only