SQL Detective
SQL Detective is a lightweight Visual Studio Code extension that statically analyzes .sql files for a handful of common performance and safety concerns. Analysis runs locally in the extension; it does not execute SQL, connect to databases, or send file contents anywhere.
Features
- Real-time diagnostics for open SQL files, including multiline and multi-statement files.
- Quick Fix recommendations, including an explicit OR-to-IN conversion for simple same-column equality conditions.
- A dashboard with issue counts, explanations, suggestions, and links to the affected line.
- Commands to analyze the current file or the entire workspace and clear findings.
Screenshots
Add screenshots of the diagnostics and dashboard here.
Installation and development
- Install Node.js and Visual Studio Code.
- Run
npm install in this project folder.
- Open this folder in VS Code and press F5. This starts an Extension Development Host with SQL Detective enabled.
- Open a
.sql file in the development host, or use the Command Palette (Ctrl+Shift+P) to run a SQL Detective command.
To compile, run npm run compile. To run the unit tests, run npm test.
Supported rules
| Rule |
Severity |
Detection |
SELECT * |
Warning |
Recommends selecting only required columns. |
UPDATE without WHERE |
Critical |
Warns the update may modify every row. |
DELETE without WHERE |
Critical |
Warns the delete may remove every row. |
SELECT without pagination |
Information |
Recommends a bounded page using LIMIT, TOP, or FETCH FIRST/NEXT. |
Repeated same-column OR equality |
Information |
Recommends IN; offers a Quick Fix for simple literal values. |
SELECT without WHERE |
Information |
Checks for unfiltered reads when an explicit pagination clause exists. The no-pagination case is covered by the pagination finding to avoid duplicate warnings. |
Example:
SELECT *
FROM products
WHERE category = 'phone'
OR category = 'laptop'
OR category = 'tablet'
LIMIT 25;
The extension recommends choosing explicit columns and replacing the repeated equality chain with category IN ('phone', 'laptop', 'tablet').
Technology
TypeScript, the VS Code Extension API, VS Code Diagnostics and Code Actions APIs, and a small local SQL tokenizer. No SQL parser dependency or external service is used.
Project structure
src/
analyzer.ts Analysis orchestration
codeActions.ts Quick Fix actions
dashboard.ts Local Webview dashboard
diagnostics.ts VS Code diagnostics
extension.ts Activation and commands
rules.ts SQL rule implementations
sqlParser.ts Comment/string-aware tokenizer and statement splitter
types.ts Shared types
media/
dashboard.css
test/
analyzer.test.ts
rules.test.ts
Limitations
This is a best-effort static analyzer, not a complete SQL grammar or database-aware optimizer. The rules are intentionally conservative and cover common SQL spellings; vendor-specific pagination, nested query scopes, complex Boolean expressions, and dialect-specific semantics may not be recognized. The extension never executes statements or verifies whether a recommendation is suitable for a particular database.
Future improvements
- Add dialect-aware parsing and pagination recognition.
- Improve nested-query and complex-condition analysis.
- Add configurable rules and severity levels.