SQL Debug Harness — VS Code Extension

Documentation: deeprajdeveloper.github.io/vscode-sql-debug-harness · Technical design

Statically rewrite a T-SQL stored procedure into a safe debug script: DML becomes SELECT previews, transactions are neutralized, and variables are traced with PRINT / RAISERROR — so you can run the script without mutating real tables.

Not a live debugger. This generates a static harness script — no breakpoints or step-into on SQL Server.

No Python required. The transform engine runs in-process inside the extension (TypeScript). Install from the Marketplace (or a VSIX) and go.

Overview

Stored procedures are hard to reason about from a script diff alone. You can read a CREATE PROCEDURE top to bottom and still not be fully certain which rows an UPDATE will touch — until it has already run.

SQL Debug Harness statically rewrites a T-SQL stored procedure so side-effecting statements become read-only previews — runnable against a real connection without writing anything. The engine is TypeScript and runs in-process.

Workbench preview — analyze a procedure and generate a debug harness in VS Code.

Before / after

Input

CREATE PROCEDURE dbo.usp_SimpleDml
    @Id INT,
    @Name NVARCHAR(100)
AS
BEGIN
    INSERT INTO dbo.Items (Id, Name) VALUES (@Id, @Name);
    UPDATE dbo.Items SET Name = @Name WHERE Id = @Id;
    DELETE FROM dbo.Items WHERE Id = @Id;
END

Generated debug script (excerpt)

-- [DBG] Harness: was CREATE PROCEDURE dbo.usp_SimpleDml; set parameter values below.
DECLARE @Id INT = NULL;  -- TODO: set test value
DECLARE @Name NVARCHAR(100) = NULL;  -- TODO: set test value
BEGIN
    -- [DBG-PREVIEW] Would have executed:
    SELECT N'INSERT to table dbo.Items' AS [DBG_Action], @Id AS [@Id], @Name AS [@Name];
    -- [DBG-PREVIEW] Would have executed:
    SELECT N'UPDATE to table dbo.Items' AS [DBG_Action], @Name AS [@Name]
    FROM dbo.Items
    WHERE Id = @Id;
    -- [DBG-PREVIEW] Would have executed:
    SELECT N'DELETE from table dbo.Items' AS [DBG_Action], *
    FROM dbo.Items
    WHERE Id = @Id;
END

Quickstart (< 5 minutes)

1. Install SQL Debug Harness from the VS Code Marketplace (or code --install-extension dist/sql-debug-harness.vsix).
2. Open samples/fixtures/simple_dml.sql (or any .sql stored procedure).
3. Right-click → SQL Debug Harness → Generate Debug Script.
4. Review the harness in the Workbench — DML is preview-only; set DECLARE values and run against a safe connection.

Optional: Analyze Procedure shows Summary / Warnings / Identified in the Workbench beside the source.

Open from the sidebar

Click the SQL Debug Harness icon in the primary activity bar (left). The Workbench view includes an Open Workbench button, plus Analyze / Generate actions in the view title bar.

Loading SQL: use Select File… in the workbench (Quick Pick of workspace .sql files), Load Active for the open editor, or right-click a file → Open in Workbench.

Commands

The Workbench shows:

• Source — selected text or full .sql file (Select File… or Load Active)
• Debug script — generated harness (when available)
• Analysis — collapsible Summary / Warnings / Identified sections (whole pane can collapse)
• Active log — step-by-step engine log (collapsible)

Panes are resizable (drag the splitters). Collapse state for Analysis / Active log is remembered. Source and Debug previews use T-SQL syntax highlighting that follows theme colors. Colors follow the current IDE theme (light/dark/high-contrast).

From the toolbar you can Analyze, Generate Debug, and save each artifact individually (analysis .txt, debug .sql, log .log), or open the debug script in a normal editor tab.

Available from the SQL Debug Harness right-click submenu on .sql files and from the Command Palette. Non-empty editor selections are used when present.

Settings

Limitations (trust boundary)

This tool prioritizes correctness over coverage. If something cannot be rewritten safely, it is flagged — not silently left as live DML.

• T-SQL only for v1 (no PostgreSQL / Oracle / MySQL dialects yet).
• Static rewrite only — not a live/step-through debugger.
• Dynamic SQL (EXEC(@sql), sp_executesql) is detected and warned; not rewritten.
• Cursors / WHILE — detected and warned; rewriting inside them is best-effort.
• MERGE / OUTPUT — flagged; MERGE is disabled rather than incorrectly previewed.
• Stored procedures are the target — not arbitrary ad-hoc DDL/DML scripts.
• No live DB connection in the extension — generate the script, then run it yourself (e.g. in SSMS / Azure Data Studio).

Why no Python backend?

Earlier versions shelled out to a PyPI package. That forced every user to have Python on PATH, survive first-run pip install, and deal with corporate lockdown / wrong interpreters. VS Code already runs Node.js, so the engine was ported to TypeScript and ships inside the VSIX. Installing the extension is the entire setup.

The engine uses a hybrid approach: optional AST via node-sql-parser (TransactSQL) plus text-scan / regex transforms for real enterprise T-SQL (TRY/CATCH, multi-line DML, etc.) where full AST parsing often fails.

Optional CLI (npx)

Same engine, outside the editor:

npx sql-debug-harness generate -i MyProc.sql -o MyProc_debug.sql
npx sql-debug-harness analyze -i MyProc.sql
npx sql-debug-harness version

(When developing from this repo: npm run compile && node out/cli.js generate -i ….)

Local development

git clone https://github.com/DeeprajDeveloper/vscode-sql-debug-harness.git
cd vscode-sql-debug-harness
npm install
npm test
npm run compile

Press F5 to launch an Extension Development Host.

Package a VSIX

npm run package
code --install-extension dist/sql-debug-harness.vsix

Roadmap

• v2 ideas: optional connect-and-run inside BEGIN…ROLLBACK, richer control-flow handling, and multi-dialect support (the parser stack already spans multiple SQL dialects).

License

MIT