SqlForge.Cli 0.3.0

SqlForge 🔨

Version-controlled, typed stored procedures for MSSQL — with Dapper codegen and AI-ready context output.

dotnet tool install -g SqlForge.Cli

The Problem

Your company has 100+ stored procedures in MSSQL. No one knows what parameters they take, what they return, or which tables they touch — unless they open SSMS and read the T-SQL. SPs change silently with no version history. And feeding this mess to an AI agent? Forget it.

What SqlForge Does

sqlforge sync             → Pulls all SPs from MSSQL, detects changes, bumps versions
sqlforge generate         → Generates typed Dapper wrappers + AI context markdown
sqlforge diff             → Shows what changed without touching anything
sqlforge annotate         → Adds human descriptions to SPs
sqlforge annotate-returns → Creates a WITH RESULT SETS stub for procs whose shape is unknown
sqlforge list             → Lists all tracked procedures

Quickstart

Requirements: the .NET 8 SDK (LTS). sqlforge sync also needs a reachable MSSQL connection; generate/diff work offline from the registry.

# 1. Install
dotnet tool install -g SqlForge.Cli

# 2. Create config in your project root
sqlforge init

# 3. Edit sqlforge.config.json with your connection string

# 4. Pull all SPs from the database
sqlforge sync

# 5. Generate typed Dapper wrappers + AI context
sqlforge generate

What Gets Generated

generated/Procedures.g.cs — Typed Dapper wrappers

// Before SqlForge — you write this manually, no IntelliSense on return shape
var results = await db.QueryAsync<dynamic>(
    "dbo.GetUserOrders", 
    new { userId = 5 },
    commandType: CommandType.StoredProcedure);

// After SqlForge — fully typed, IntelliSense on params AND return columns
var results = await SP.GetUserOrders(db, new(UserId: 5, Status: "active"));
// results is IEnumerable<GetUserOrdersResult>
// result.OrderId, result.Total, result.CreatedAt — all typed ✓

generated/context.md — AI-ready context

Instead of dumping 10,000 lines of T-SQL into your AI agent's context window, SqlForge generates a structured, token-efficient markdown document:

### `[dbo].[GetUserOrders]` (v3)
> Returns all orders for a given user, optionally filtered by status.

**Touches tables/views:** `Orders`, `Users`, `OrderItems`

**Parameters:**
| Name | SQL Type | C# Type | Mode | Nullable |
|------|----------|---------|------|----------|
| `@userId` | `int` | `int` | IN | no |
| `@status` | `varchar(50)` | `string` | IN | yes |

**Returns:**
| Column | SQL Type | C# Type |
|--------|----------|---------|
| `OrderId` | `int` | `int` |
| `Total` | `decimal(18,2)` | `decimal` |

.sp-registry/procedures/*.json — Version history

Each SP gets its own JSON file, committed to git:

{
  "name": "GetUserOrders",
  "schema": "dbo",
  "version": 3,
  "lastModifiedUtc": "2025-06-01T10:00:00Z",
  "description": "Returns all orders for a user, optionally filtered by status",
  "parameters": [ ... ],
  "returns": [ ... ],
  "dependsOn": ["Orders", "Users", "OrderItems"],
  "history": [
    { "version": 1, "changedAtUtc": "2024-01-15T...", "changeNote": "First discovered" },
    { "version": 2, "changedAtUtc": "2024-08-22T...", "changeNote": null },
    { "version": 3, "changedAtUtc": "2025-06-01T...", "changeNote": "Added status filter" }
  ]
}

Configuration

sqlforge.config.json:

{
  "connectionString": "Server=localhost;Database=YourDB;Trusted_Connection=True;TrustServerCertificate=True;",
  "schemas": ["dbo"],
  "exclude": ["sp_*", "xp_*", "dt_*"],
  "generation": {
    "namespace": "YourApp.Data.Procedures",
    "outputPath": "generated/",
    "generateDapperWrappers": true,
    "generateAiContext": true
  },
  "registry": {
    "path": ".sp-registry/"
  },
  "introspection": {
    "useStaticDescribe": true,
    "useScriptDomAnalysis": true,
    "useRuntimeExecution": true,
    "useCatalogDummyValues": true,
    "useBranchExhaustion": true,
    "useLoopback": false,
    "loopbackServerName": "loopback",
    "runtimeTimeoutSeconds": 5,
    "annotationsPath": ".sqlforge/annotations/"
  }
}

Version Control Workflow

your-project/
├── .sp-registry/              ← commit this to git
│   └── procedures/
│       ├── dbo.GetUserOrders.json
│       ├── dbo.CreateInvoice.json
│       └── ...
├── generated/                 ← optionally gitignore (regenerate on build)
│   ├── Procedures.g.cs
│   └── context.md
└── sqlforge.config.json       ← gitignore if it contains credentials

Recommended git workflow:

# Before committing a DB change
sqlforge sync --note "Added @status parameter to GetUserOrders"
sqlforge generate
git add .sp-registry/ generated/
git commit -m "chore: sync SP registry"

Now SP changes show up in PRs like code changes. Reviewers can see exactly what changed.

Annotating Stored Procedures

sqlforge annotate GetUserOrders --schema dbo "Returns paginated orders for a user. Status can be: active, cancelled, pending."
sqlforge generate   # re-run to include description in context.md

Descriptions survive future syncs — SqlForge never overwrites them.

How return shapes are resolved

MSSQL has no catalog that says "this procedure returns these columns", so SqlForge runs a cascade of strategies (fastest/safest first) and union-merges the results so conditional branches and multiple result sets are captured even when no single strategy sees everything:

1. sp_describe_first_result_set — static, never executes the SP.
2. ScriptDom AST analysis — parses the body with Microsoft's T-SQL parser, finds every SELECT across all IF/ELSE branches, and resolves column types from the catalog.
3. Runtime execution — runs the SP with catalog-aware dummy values inside a rolled-back transaction, reading all result sets via NextResult().
4. Branch exhaustion — re-runs with varied values for detected branching parameters and unions the distinct shapes.
5. Loopback OPENQUERY — optional (off by default); SELECT TOP 0 * INTO #t FROM OPENQUERY(...).

Each procedure records a ReturnStatus (Resolved, Partial, Unresolved, or Annotated) and the strategy used. Anything that stays Unresolved gets a one-time, git-tracked WITH RESULT SETS annotation via sqlforge annotate-returns — the T-SQL-native way to declare a contract — after which it's typed forever.

These strategies are individually toggled via the introspection block in sqlforge.config.json.

Known Limitations

• Some procedures build their result schema from runtime data (e.g. SELECT * FROM @tableName) and cannot be inferred by any automatic strategy — annotate these once with sqlforge annotate-returns.
• Static analysis types simple column references from the catalog; computed expressions (CASE, function calls) are reported as Partial with an sql_variant placeholder.
• Runtime strategies execute the procedure inside a rolled-back transaction. Disable introspection.useRuntimeExecution for databases with side-effecting SPs that can't be safely rolled back.
• Output parameters (@out / INOUT) use DynamicParameters in the generated wrappers — check the generated code.
• Versioning bumps on a semantic SP body hash; whitespace and comment-only edits are ignored, while metadata-only changes are not yet versioned separately.
• Only MSSQL is supported currently.

Roadmap

See ROADMAP.md for the full path to v1.0.0 (milestones, non-goals, and versioning policy). Highlights:

• Registry correctness: handle removed SPs, detect metadata-only changes, no silent drift.
• Safer, opt-in return-shape inference and per-parameter nullability.
• Contract completeness: multiple result sets and expected exceptions.
• DX: stable CLI dependency, sqlforge watch, CLI + DB integration tests, NuGet v1.0.0.

Explicit non-goals: nested SP call-graph expansion, linked-server resolution, and deployment/migration (SqlForge stays read-only introspection).

License

MIT