All Modules

SQL Test

Database Testing

AI-powered test generation for SQL Server (tSQLt) and PostgreSQL (pgTAP). Unit tests, integration tests, and behavioral verification with full CI/CD integration.

Native Testing Frameworks

🔷

tSQLt (SQL Server)

  • • Unit tests for stored procedures
  • • Table mocking and isolation
  • • Constraint verification
  • • Transaction rollback per test
  • • XML/JUnit output for CI/CD
🐘

pgTAP (PostgreSQL)

  • • TAP-compliant test output
  • • Function and trigger testing
  • • Schema verification
  • • Role and permission tests
  • • Parallel test execution

AI-Generated Tests

Automatically generate tests based on procedure logic and audit patterns

-- Input: GetCustomerOrders procedure

-- AI Generated tSQLt Tests:

EXEC tSQLt.NewTestClass 'GetCustomerOrdersTests';
GO

CREATE PROCEDURE GetCustomerOrdersTests.[test returns orders for valid customer]
AS
BEGIN
    -- Arrange
    EXEC tSQLt.FakeTable 'dbo.Customers';
    EXEC tSQLt.FakeTable 'dbo.Orders';

    INSERT INTO dbo.Customers (CustomerId, Name) VALUES (1, 'Test Customer');
    INSERT INTO dbo.Orders (OrderId, CustomerId, Total) VALUES (100, 1, 99.99);

    -- Act
    CREATE TABLE #Results (OrderId INT, Total MONEY);
    INSERT INTO #Results EXEC dbo.GetCustomerOrders @CustomerId = 1;

    -- Assert
    EXEC tSQLt.AssertEquals 1, (SELECT COUNT(*) FROM #Results);
    EXEC tSQLt.AssertEquals 99.99, (SELECT Total FROM #Results);
END;
GO

CREATE PROCEDURE GetCustomerOrdersTests.[test returns empty for invalid customer]
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.Customers';
    EXEC tSQLt.FakeTable 'dbo.Orders';

    CREATE TABLE #Results (OrderId INT, Total MONEY);
    INSERT INTO #Results EXEC dbo.GetCustomerOrders @CustomerId = 999;

    EXEC tSQLt.AssertEquals 0, (SELECT COUNT(*) FROM #Results);
END;
GO

Test Categories

Unit Tests

  • Procedure logic
  • Function returns
  • Trigger behavior
  • Constraint validation

Integration Tests

  • Multi-step workflows
  • Transaction integrity
  • Cascade effects
  • Cross-schema operations

Behavioral Tests

  • Audit trail verification
  • Permission enforcement
  • Error handling
  • Edge cases

CI/CD Integration

Run tests in your pipeline with standard output formats

# GitHub Actions workflow
name: Database Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Start SQL Server
        run: docker-compose up -d sqlserver

      - name: Run SQL Test
        run: |
          sql2ai test run \
            --connection "${{ secrets.DB_CONNECTION }}" \
            --output junit \
            --coverage

      - name: Publish Results
        uses: dorny/test-reporter@v1
        with:
          name: Database Tests
          path: test-results.xml
          reporter: java-junit

# Test Output:
# ✓ GetCustomerOrdersTests - 4 tests passed
# ✓ UpdateInventoryTests - 6 tests passed
# ✓ ProcessPaymentTests - 8 tests passed
# ────────────────────────────────────
# Total: 18 passed, 0 failed
# Coverage: 87% of procedures tested

Test Coverage Analysis

Track which procedures and code paths are tested

87%
Procedures
92%
Functions
76%
Triggers
45%
Error Paths

Test Your Database Code

AI-generated tests for SQL Server and PostgreSQL with full CI/CD integration.

Start Free TrialView All Features

No credit card required • Free for individual developers