SQL Testing Framework

Back to Portfolio

When I worked at Clearwater Analytics, we had a lot of SQL queries. They ranged from complex computations run by internal systems to procedures that were relied on by analysts. The problem was, we would inadvertently break them. My solution was to write a simple testing framework that could be run by our existing deployment systems.

Below is an example of the framework's usage.

DECLARE @scenarioId INT
  
-- We create our first test
EXEC @scenarioId = juts.CreateTestScenario
    @name = 'Simple Insert'
,   @comment = 'Ensure we can insert into an empty table'
 
-- Then we add the procedure under test
EXEC juts.addCommandToScenario
    @scenarioId = @scenarioId
,   @command = 'EXEC dbo.TestInsertStuff @key = ''T'', @values = ''A,B,C'''
,   @expectedReturnValue = NULL
,   @expectedResultSetColumnsAndTypes = 'keyId INT, value VARCHAR(100)'
,   @expectedResultSetCSV = '''1'',''A''
''1'',''B''
''1'',''C'''
 
-- Add the values table to our scenario
EXEC juts.AddTableToScenario
    @scenarioId = @scenarioId
,   @tableSchema = 'dbo'
,   @tableName = 'myValueTable'
,   @initialValueCSV = ''
,   @expectedValueCSV = '''1'',''A''
''1'',''B''
''1'',''C'''
,   @resultsColumnOrderBy = 'keyId ASC, value ASC'
 
-- Add the keys table
EXEC juts.AddTableToScenario
    @scenarioId = @scenarioId
,   @tableSchema = 'dbo'
,   @tableName = 'myKeyTable'
,   @initialValueCSV = ''
,   @expectedValueCSV = '''1'',''T'''
,   @ignoreColumns = 'addedOn'

-- Run our test!
EXEC juts.RunScenarios

The company has made the project opensource. Feel free to check it out on GitHub.