tgpiler

tgpiler

A T-SQL to Go transpiler and runtime interpreter. Converts T-SQL stored procedures to Go functions, and provides a runtime interpreter for dynamic SQL execution including transactions.

Purpose

This tool helps developers migrate business logic from Microsoft SQL Server stored procedures to Go. It provides two execution modes:

Transpiler Mode — Static code generation:

• Procedural constructs (variables, control flow, expressions)
• DML operations (SELECT, INSERT, UPDATE, DELETE)
• Cursors → idiomatic Go rows.Next() iteration
• JSON functions (JSON_VALUE, JSON_QUERY, JSON_MODIFY, OPENJSON, FOR JSON)
• XML functions (.value(), .query(), .exist(), .nodes(), .modify(), OPENXML, FOR XML)
• Temp tables (#temp), RAISERROR/THROW

Interpreter Mode — Dynamic SQL execution at runtime:

• Everything above, plus:
• Transactions (BEGIN TRAN, COMMIT, ROLLBACK, nested transactions)
• Dynamic SQL (EXEC(@sql), sp_executesql with parameters)
• Scrollable cursors (FETCH ABSOLUTE, FETCH RELATIVE, FETCH FIRST/LAST)
• Full error handling (TRY/CATCH, ERROR_NUMBER(), ERROR_MESSAGE(), XACT_STATE())

Installation

go install github.com/ha1tch/tgpiler/cmd/tgpiler@latest

Or build from source:

git clone https://github.com/ha1tch/tgpiler.git
cd tgpiler
make build

Dependencies

• ha1tch/tsqlparser - T-SQL parser
• shopspring/decimal - Arbitrary-precision decimals

Usage

Usage:
  tgpiler [options] <input.sql>
  tgpiler [options] -s < input.sql
  tgpiler [options] -d <path>

Input (mutually exclusive):
  <file.sql>            Read single file
  -s, --stdin           Read from stdin
  -d, --dir <path>      Read all .sql files from directory

Output (mutually exclusive):
  (no flag)             Write to stdout
  -o, --output <file>   Write to single file
  -O, --outdir <path>   Write to directory (creates if needed)

Options:
  -p, --pkg <name>      Package name for generated code (default: main)
  --dml                 Enable DML mode (SELECT, INSERT, temp tables, JSON/XML)
  -f, --force           Allow overwriting existing files
  -h, --help            Show help
  -v, --version         Show version

Examples

# Transpile procedural logic (default mode)
tgpiler input.sql

# Transpile with DML support (database operations, JSON/XML)
tgpiler --dml input.sql

# Transpile with custom package name
tgpiler -p mypackage input.sql -o output.go

# Read from stdin
tgpiler -s < input.sql
cat input.sql | tgpiler -s

# Transpile directory of SQL files with DML mode
tgpiler --dml -d ./sql -O ./go -p procedures

gRPC Backend

tgpiler can transpile T-SQL stored procedures to gRPC client calls, enabling migration from database-centric architectures to horizontally-scalable microservices.

Backend Selection

# SQL backend (default) — generates database/sql calls
tgpiler --dml --backend=sql input.sql

# gRPC backend — generates gRPC client calls
tgpiler --dml --backend=grpc --grpc-package=orderpb input.sql

# Mock backend — generates mock store calls (for testing)
tgpiler --dml --backend=mock input.sql

The Four Actions

For proto-based development, tgpiler provides four complementary actions:

# 1. Preview procedure-to-method mappings
tgpiler --show-mappings --proto-dir ./protos --sql-dir ./procedures

# 2. Generate repository implementations
tgpiler --gen-impl --proto-dir ./protos --sql-dir ./procedures -o repo.go

# 3. Generate gRPC server stubs
tgpiler --gen-server --proto-dir ./protos -o server.go

# 4. Generate mock server scaffolding
tgpiler --gen-mock --proto-dir ./protos -o mocks.go

Temp Table Handling

When using --backend=grpc, temp tables automatically fall back to SQL:

# Temp tables use SQL, regular tables use gRPC
tgpiler --dml --backend=grpc --grpc-package=orderpb input.sql
# Shows: info: Temp tables detected. Using --fallback-backend=sql (default).

# Explicit fallback
tgpiler --dml --backend=grpc --grpc-package=orderpb --fallback-backend=sql input.sql

See docs/GRPC.md for complete gRPC documentation.

Testing

The project includes a comprehensive test suite with 80 T-SQL sample files.

Quick Start

# Run all tests
make test

# Quick smoke test
make test-quick

# End-to-end tests (transpile → compile → execute)
make test-e2e

Test Categories

Structured Data Tests (JSON/XML)

# Run all structured data tests
go test -v ./tests/... -run "TestCompilationStructured|TestStructuredFullBuild|TestE2EExecute"

# Compilation only (fast)
go test -v ./tests/... -run TestCompilationStructuredDML

# Full build test (all 25 files as a package)
go test -v ./tests/... -run TestStructuredFullBuild

# E2E execution tests
go test -v ./tests/... -run "TestE2EExecuteJSON|TestE2EExecuteXML"

Sample Files

The project includes 90 T-SQL sample files across 5 categories:

Basic Algorithms (tsql_basic/) — 20 files

Non-Trivial Algorithms (tsql_nontrivial/) — 15 files

Financial Calculations (tsql_financial/) — 20 files

Structured Data — JSON/XML (tsql_structured/) — 25 files

CTEs and Window Functions (tsql_cte/) — 10 files

Supported Features

Procedural Constructs

DML Operations (--dml mode)

JSON Functions (--dml mode)

XML Functions (--dml mode)

Window Functions (--dml mode)

Window functions are fully supported with automatic type inference. The transpiler analyses each window function and assigns the correct Go type:

CTEs (Common Table Expressions)

CTEs are fully supported in both transpiler and interpreter modes:

Type Mapping

Expressions & Functions

String functions: LEN, UPPER, LOWER, TRIM, LTRIM, RTRIM, SUBSTRING, LEFT, RIGHT, CHARINDEX, REPLACE, REPLICATE, CONCAT, CONCAT_WS, STRING_AGG

Math functions: ABS, CEILING, FLOOR, ROUND, POWER, SQRT, SIGN, LOG, LOG10, EXP

Date functions: GETDATE, SYSDATETIME, DATEADD, DATEDIFF, YEAR, MONTH, DAY, DATEPART, DATENAME, EOMONTH

NULL functions: ISNULL, COALESCE, NULLIF

Conversion: CAST, CONVERT, TRY_CAST, TRY_CONVERT

Other: CASE expressions, IIF, CHOOSE, ERROR_MESSAGE

Examples

Basic Procedural Logic

Input (discount.sql):

CREATE PROCEDURE dbo.CalculateDiscount
    @Price DECIMAL(10,2),
    @Quantity INT,
    @Total DECIMAL(10,2) OUTPUT
AS
BEGIN
    DECLARE @Discount DECIMAL(10,2) = 0
    SET @Total = @Price * @Quantity
    
    IF @Quantity >= 100
        SET @Discount = @Total * 0.15
    ELSE IF @Quantity >= 50
        SET @Discount = @Total * 0.10
    
    SET @Total = @Total - @Discount
END

Output:

package main

import "github.com/shopspring/decimal"

func CalculateDiscount(Price decimal.Decimal, Quantity int32) (Total decimal.Decimal) {
    var Discount decimal.Decimal = decimal.NewFromInt(0)
    Total = Price.Mul(decimal.NewFromInt(int64(Quantity)))
    if Quantity >= 100 {
        Discount = Total.Mul(decimal.NewFromFloat(0.15))
    } else if Quantity >= 50 {
        Discount = Total.Mul(decimal.NewFromFloat(0.10))
    }
    Total = Total.Sub(Discount)
    return Total
}

JSON Processing (--dml mode)

Input:

CREATE PROCEDURE dbo.ParseCustomerJson
    @JsonData NVARCHAR(MAX),
    @CustomerName NVARCHAR(100) OUTPUT,
    @CustomerId INT OUTPUT,
    @Email NVARCHAR(200) OUTPUT
AS
BEGIN
    SET @CustomerName = JSON_VALUE(@JsonData, '$.customer.name')
    SET @CustomerId = CAST(JSON_VALUE(@JsonData, '$.customer.id') AS INT)
    SET @Email = JSON_VALUE(@JsonData, '$.customer.email')
END

Output (tgpiler --dml):

package main

import "strconv"

func ParseCustomerJson(jsonData string) (customerName string, customerId int32, email string) {
    customerName = JsonValue(jsonData, "$.customer.name")
    customerId = func() int32 { 
        v, _ := strconv.ParseInt(JsonValue(jsonData, "$.customer.id"), 10, 32)
        return int32(v) 
    }()
    email = JsonValue(jsonData, "$.customer.email")
    return customerName, customerId, email
}

XML Validation (--dml mode)

Input:

CREATE PROCEDURE dbo.ValidateOrderXml
    @XmlData XML,
    @IsValid BIT OUTPUT,
    @HasCustomer BIT OUTPUT
AS
BEGIN
    SET @HasCustomer = @XmlData.exist('/order/customer')
    IF @HasCustomer = 0
        SET @IsValid = 0
    ELSE
        SET @IsValid = 1
END

Output (tgpiler --dml):

package main

func ValidateOrderXml(xmlData string) (isValid bool, hasCustomer bool) {
    hasCustomer = XmlExist(xmlData, "/order/customer")
    if !hasCustomer {
        isValid = false
    } else {
        isValid = true
    }
    return isValid, hasCustomer
}

Cursor Processing (--dml mode)

Input:

CREATE PROCEDURE dbo.ProcessUsers
AS
BEGIN
    DECLARE @UserID INT, @Email NVARCHAR(100)
    
    DECLARE user_cursor CURSOR FOR
        SELECT ID, Email FROM Users WHERE IsActive = 1
    
    OPEN user_cursor
    FETCH NEXT FROM user_cursor INTO @UserID, @Email
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @Email
        FETCH NEXT FROM user_cursor INTO @UserID, @Email
    END
    
    CLOSE user_cursor
    DEALLOCATE user_cursor
END

Output (tgpiler --dml):

package main

import "fmt"

func ProcessUsers() {
    var userId int32
    var email string
    
    // Cursor becomes idiomatic Go row iteration
    userCursorRows, err := r.db.QueryContext(ctx, 
        "SELECT ID, Email FROM Users WHERE IsActive = 1")
    if err != nil {
        return err
    }
    defer userCursorRows.Close()
    
    for userCursorRows.Next() {
        if err := userCursorRows.Scan(&userId, &email); err != nil {
            return err
        }
        fmt.Println(email)
    }
}

Window Functions with Type Inference (--dml mode)

Input:

CREATE PROCEDURE dbo.GetSalesRankings
    @CategoryID INT
AS
BEGIN
    SELECT 
        ProductID,
        ProductName,
        Price,
        ROW_NUMBER() OVER (ORDER BY Price DESC) AS PriceRank,
        PERCENT_RANK() OVER (ORDER BY Price) AS PercentileRank,
        SUM(Price) OVER (ORDER BY Price ROWS UNBOUNDED PRECEDING) AS RunningTotal,
        LAG(Price, 1, 0) OVER (ORDER BY Price) AS PreviousPrice
    FROM Products
    WHERE CategoryID = @CategoryID
END

Output (tgpiler --dml):

package main

import "github.com/shopspring/decimal"

func GetSalesRankings(categoryId int32) error {
    var productId int64
    var productName string
    var price decimal.Decimal
    var priceRank int64           // ROW_NUMBER → int64
    var percentileRank float64    // PERCENT_RANK → float64
    var runningTotal decimal.Decimal  // SUM(Price) → decimal.Decimal
    var previousPrice decimal.Decimal // LAG(Price) → same as Price
    
    rows, err := r.db.QueryContext(ctx, 
        `SELECT ProductID, ProductName, Price,
         ROW_NUMBER() OVER (ORDER BY Price DESC) AS PriceRank,
         PERCENT_RANK() OVER (ORDER BY Price ASC) AS PercentileRank,
         SUM(Price) OVER (ORDER BY Price ASC ROWS UNBOUNDED PRECEDING) AS RunningTotal,
         LAG(Price, 1, 0) OVER (ORDER BY Price ASC) AS PreviousPrice
         FROM Products WHERE CategoryID = $1`, categoryId)
    if err != nil {
        return err
    }
    defer rows.Close()
    
    for rows.Next() {
        if err := rows.Scan(&productId, &productName, &price, 
            &priceRank, &percentileRank, &runningTotal, &previousPrice); err != nil {
            return err
        }
    }
    return nil
}

CTE with Recursive Query (--dml mode)

Input:

CREATE PROCEDURE dbo.GetEmployeeHierarchy
    @RootEmployeeID INT
AS
BEGIN
    WITH EmployeeCTE AS (
        -- Anchor: root employee
        SELECT EmployeeID, Name, ManagerID, 0 AS Level
        FROM Employees
        WHERE EmployeeID = @RootEmployeeID
        
        UNION ALL
        
        -- Recursive: subordinates
        SELECT e.EmployeeID, e.Name, e.ManagerID, cte.Level + 1
        FROM Employees e
        INNER JOIN EmployeeCTE cte ON e.ManagerID = cte.EmployeeID
    )
    SELECT EmployeeID, Name, Level
    FROM EmployeeCTE
    ORDER BY Level, Name
END

Output (tgpiler --dml):

package main

func GetEmployeeHierarchy(rootEmployeeId int32) error {
    var employeeId int64
    var name string
    var level int64
    
    rows, err := r.db.QueryContext(ctx, 
        `WITH EmployeeCTE AS (
            SELECT EmployeeID, Name, ManagerID, 0 AS Level
            FROM Employees WHERE EmployeeID = $1
            UNION ALL
            SELECT e.EmployeeID, e.Name, e.ManagerID, (cte.Level + 1)
            FROM Employees AS e
            INNER JOIN EmployeeCTE AS cte ON (e.ManagerID = cte.EmployeeID))
         SELECT EmployeeID, Name, Level FROM EmployeeCTE
         ORDER BY Level ASC, Name ASC`, rootEmployeeId)
    if err != nil {
        return err
    }
    defer rows.Close()
    
    for rows.Next() {
        if err := rows.Scan(&employeeId, &name, &level); err != nil {
            return err
        }
    }
    return nil
}

Project Structure

tgpiler/
├── cmd/tgpiler/           # CLI entry point
├── transpiler/            # Core transpilation logic
│   ├── transpiler.go      # Main transpiler, control flow
│   ├── expressions.go     # Expression handling
│   ├── dml.go             # DML statement transpilation
│   ├── types.go           # Type mapping
│   ├── symbols.go         # Symbol table
│   └── comments.go        # Comment preservation
├── tsqlruntime/           # Runtime library
│   ├── json.go            # JSON function implementations
│   ├── xml.go             # XML function implementations
│   ├── ddl.go             # Temp table support
│   └── functions.go       # Built-in function implementations
├── adapter/               # Database adapter patterns
├── storage/               # DML analysis utilities
├── protogen/              # Protocol buffer generation
├── mock/                  # Mock implementations for testing
├── tests/                 # Test suite
│   ├── e2e_test.go        # End-to-end tests
│   ├── structured_test.go # JSON/XML DML tests
│   ├── compilation_test.go
│   ├── basic_test.go
│   ├── nontrivial_test.go
│   └── financial_test.go
├── tsql_basic/            # 20 basic T-SQL samples
├── tsql_nontrivial/       # 15 non-trivial T-SQL samples
├── tsql_financial/        # 20 financial T-SQL samples
├── tsql_structured/       # 25 JSON/XML T-SQL samples
├── tsql_cte/              # 10 CTE and window function samples
├── scripts/               # Convenience scripts
├── Makefile               # Build and test automation
└── README.md

Runtime Library

The tsqlruntime package provides both function implementations and a full T-SQL interpreter.

Functions

import "github.com/ha1tch/tgpiler/tsqlruntime"

// JSON functions
value := tsqlruntime.JSONValue(jsonStr, "$.customer.name")
modified := tsqlruntime.JSONModify(jsonStr, "$.status", "active")

// XML functions  
value := tsqlruntime.XMLValue(xmlStr, "/order/id", tsqlruntime.TypeInt)
exists := tsqlruntime.XMLExist(xmlStr, "/order/customer")

// Temp tables
tempTables := tsqlruntime.NewTempTableManager()
tempTables.CreateTempTable("#Orders", columns)

Interpreter (Dynamic SQL Execution)

The interpreter executes T-SQL at runtime, supporting dynamic SQL and transactions:

import "github.com/ha1tch/tgpiler/tsqlruntime"

// Create interpreter
interp := tsqlruntime.NewInterpreter(db, tsqlruntime.DialectPostgres)

// Set parameters
interp.SetVariable("@userID", 42)
interp.SetVariable("@amount", decimal.NewFromFloat(100.00))

// Execute dynamic SQL with transactions
result, err := interp.Execute(ctx, `
    BEGIN TRANSACTION
    
    DECLARE @balance DECIMAL(18,2)
    SELECT @balance = Balance FROM Accounts WHERE ID = @userID
    
    IF @balance >= @amount
    BEGIN
        UPDATE Accounts SET Balance = Balance - @amount WHERE ID = @userID
        INSERT INTO Transactions (AccountID, Amount, Type) VALUES (@userID, @amount, 'DEBIT')
        COMMIT
    END
    ELSE
    BEGIN
        ROLLBACK
        RAISERROR('Insufficient funds', 16, 1)
    END
`, nil)

// Access results
for _, rs := range result.ResultSets {
    for _, row := range rs.Rows {
        // Process rows
    }
}

Cursors

// Scrollable cursor support
cursor, _ := cursorMgr.DeclareCursor("myCursor", query, false,
    tsqlruntime.CursorStatic, tsqlruntime.CursorScrollForward, tsqlruntime.CursorReadOnly)
cursor.Open(columns, rows)

row, status := cursor.FetchNext()
row, status = cursor.FetchAbsolute(5)   // Jump to row 5
row, status = cursor.FetchRelative(-2)  // Go back 2 rows
row, status = cursor.FetchLast()        // Jump to last row

Makefile Targets

make help              # Show all available targets
make build             # Build the transpiler
make test              # Run all unit tests
make test-e2e          # Full end-to-end tests
make test-structured   # JSON/XML DML tests
make test-quick        # Quick smoke test
make transpile-all     # Transpile all samples to /tmp
make fmt               # Format code
make lint              # Run go vet
make clean             # Remove build artifacts

Execution Modes

tgpiler supports two execution modes with different capabilities:

Transpiler Mode (Static Code Generation)

Converts T-SQL to standalone Go code. Use tgpiler or tgpiler --dml:

Interpreter Mode (Dynamic Execution)

Executes T-SQL at runtime via tsqlruntime.Interpreter. Supports everything above plus:

// Interpreter example
interp := tsqlruntime.NewInterpreter(db, tsqlruntime.DialectPostgres)
interp.SetVariable("@amount", 100.00)

result, err := interp.Execute(ctx, `
    BEGIN TRAN
    UPDATE Accounts SET Balance = Balance - @amount WHERE ID = 1
    UPDATE Accounts SET Balance = Balance + @amount WHERE ID = 2
    COMMIT
`, nil)

Limitations

The following T-SQL features are not yet supported:

• User-defined functions (UDFs)
• MERGE statements
• Linked servers / distributed queries
• WAITFOR / Service Broker
• Full-text search

Note: CTEs (Common Table Expressions) and window functions (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, etc.) are fully supported in DML mode — queries are passed through to the database which handles them natively. Window functions have proper type inference: ranking functions return int64, percentage functions return float64, and navigation functions inherit their argument types.

Documentation

Author

Copyright (C) 2025 haitch — h@ual.fi

Licence

GNU General Public License v3.0

https://github.com/ha1tch/tgpiler?tab=GPL-3.0-1-ov-file#readme

Related Projects

• tsqlparser - The T-SQL parser used by this project

SPLogger - Structured Error Logging

The transpiler supports a professional logging system for stored procedure errors. Instead of generating inline error handling code with XML parameter building, you can use SPLogger for clean, structured logging.

Enabling SPLogger

tgpiler --dml --splogger input.sql

Before (without SPLogger)

defer func() {
    if _recovered := recover(); _recovered != nil {
        hasError = true
        var parameters string = fmt.Sprintf(`<RootXml>...`, ...)
        result, err := r.db.ExecContext(ctx, "INSERT INTO Error.Log...", ...)
        // ...
    }
}()

After (with SPLogger)

defer func() {
    if _recovered := recover(); _recovered != nil {
        _spErr := tsqlruntime.CaptureError("ProcName", _recovered, params)
        hasError = true
        _ = spLogger.LogError(ctx, _spErr)
    }
}()

SPLogger Implementations

The tsqlruntime package provides several logger implementations:

Example Setup

package main

import (
    "context"
    "database/sql"
    "log/slog"
    "os"
    
    "github.com/ha1tch/tgpiler/tsqlruntime"
)

func main() {
    // Option 1: Log to database (like original T-SQL)
    db, _ := sql.Open("postgres", "...")
    dbLogger := tsqlruntime.NewDatabaseSPLogger(db, "Error.LogForStoreProcedure", "postgres")
    
    // Option 2: Use Go's structured logging
    slogLogger := tsqlruntime.NewSlogSPLogger(slog.Default())
    
    // Option 3: Log to file
    fileLogger, _ := tsqlruntime.NewFileSPLogger("/var/log/sp_errors.json", "json")
    defer fileLogger.Close()
    
    // Option 4: Multiple destinations
    multiLogger := tsqlruntime.NewMultiSPLogger(dbLogger, slogLogger)
    
    // Set as default (used by generated code)
    tsqlruntime.SetDefaultSPLogger(multiLogger)
}

SPError Structure

type SPError struct {
    ProcedureName  string                 // Name of the stored procedure
    Parameters     map[string]interface{} // Input parameters at time of error
    ErrorMessage   string                 // Error message (ERROR_MESSAGE())
    ErrorNumber    int                    // Error number (ERROR_NUMBER())
    Severity       int                    // Error severity (ERROR_SEVERITY())
    State          int                    // Error state (ERROR_STATE())
    Line           int                    // Line number (runtime.Caller)
    Timestamp      time.Time              // When error occurred
    StackTrace     string                 // Go stack trace
}

The SPError type provides ToXML() and ToJSON() methods for serialisation.