Введение в модульное тестирование T-SQL помощью tSQLt

tSQLt — это мощный фреймворк с открытым исходным кодом для модульного тестирования кода SQL Server. 

Модульное тестирование SQL-кода — полезная практика в разработке баз данных, которая позволяет обнаруживать ошибки до попадания их в продакшн. Хотя надо сказать, есть разные мнения, и некоторые разработчики все еще спорят о необходимости модульного тестирования SQL-кода. Модульное тестирование очень важно для контроля и проверки поведения отдельных частей базы данных и не стоит игнорировать написание модульных тестов кода базы данных. По сути, модульные тесты позволяют автоматически проверять поведение объектов базы данных (хранимых процедур, триггеров, функций и т. д.) при регрессионном тестировании. 

В части тестирования SQL-кода у tSQLt есть ряд преимуществ:

• Тесты пишутся на языке T-SQL. Это основное преимущество. Нет необходимости в изучении нового языка программирования или платформы для написания и выполнения SQL-тестов. Для теста нужна только база данных с установленным tSQLt и SQL Server Management Studio или любой другой редактор запросов.

• Каждый модульный тест автоматически запускается в рамках транзакции. После выполнения теста все операции изменения данных автоматически откатываются и нет необходимости в какой-либо очистке данных.

• tSQLt позволяет использовать заглушки (mock, fake) реальных объектов. Заглушки/фейки имитируют поведение реального объекта, что позволяет изолировать зависимости и писать изолированные тесты. Например, нам нужно протестировать хранимую процедуру, использующую внутри пользовательскую функцию. Мы можем изолировать тест хранимой процедуры, написав mock-функцию, используемую только в рамках данного теста.

• tSQLt полностью бесплатен и с открытым исходным кодом.

• tSQLt можно интегрировать в SSDT-проекты. Для интеграции с Visual Studio можно использовать tSQLt Test Adapter for Visual Studio 2017 или ApexSQL Unit Test.

Установка tSQLt

Установка tSQLt очень проста. Сначала нужно включить CLR (SQL Common Language Runtime) и свойство TRUSTWORTHY базы данных, в которую устанавливается tSQLt.

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
EXEC sp_configure 'clr enabled'

В этой статье мы будем использовать демонстрационную базу данных WideWorldImporters.

USE WideWorldImporters 
GO
ALTER DATABASE WideWorldImporters SET TRUSTWORTHY ON;

Далее скачиваем архив tSQLt с tsqlt.org.

Выполняем содержимое файла tSQLt.class.sql в SQL Server Management Studio. Если установка прошла успешно, вы увидите сообщение "Thank you for using tSQLt" и версию tSQLt.

Примечание — в Azure SQL включать CLR и TRUSTWORTHY не нужно, достаточно выполнить файл tSQLt.class.sql. 

Проверить установку компонент tSQLt можно с помощью следующего запроса:

SELECT @@VERSION,name
 FROM sys.objects sysobj where schema_id = (
select sch.schema_id from sys.schemas sch where name='tSQLt' )
order by sysobj.name

Пишем первый тест

Сначала нужно создать "тест-класс" (тестовый класс), который группирует тесты (test case). Приведенный ниже скрипт создает новый класс с именем DemoUnitTestClass.

USE WideWorldImporters
GO
EXEC tSQLt.NewTestClass 'DemoUnitTestClass';

Тестовый класс tSQLt, по сути, представляет собой схему БД с дополнительным расширенным свойством с информацией о том, что данная схема создана для тестового класса. В этом можно убедиться с помощью следующего запроса:

select SCHEMA_NAME,objtype,name,value from INFORMATION_SCHEMA.SCHEMATA SC
CROSS APPLY fn_listextendedproperty (NULL, 'schema', NULL, NULL, NULL, NULL, NULL) OL
WHERE OL.objname=sc.SCHEMA_NAME COLLATE Latin1_General_CI_AI
and SCHEMA_NAME = 'DemoUnitTestClass'

Также это можно посмотреть через SSMS в расширенных свойствах (extended properties) схемы.

• Откройте в SSMS узел Security->Schemas для базы данных.

• Щелкните правой кнопкой мыши на схеме DemoUnitTestClass и откройте свойства (Properties).

• Выберите страницу Extended Properties.

Для удаления класса можно использовать хранимую процедуру tSQLt.DropClass, принимающую в параметре имя тестового класса.

EXEC tSQLt.DropClass 'DemoUnitTestClass'

Хранимая процедура DropClass удаляет не только сам класс (схему), но и все тестовые объекты, связанные с этим классом. Если создать класс с уже существующим именем, то поведение будет аналогично процедуре DropClass: удаляются все объекты, связанные с тестовым классом, и создается пустой тестовый класс с указанным именем.

Ассерты в tSQLt

Для проверки результата выполнения tSQLt предлагает несколько хранимых процедур:

• tSQLt.AssertEquals

• tSQLr.AssertEqualsTable

• tSQLt.AssertEmptyTable

• tSQLt.AssertEqualsString

• tSQLt.AssertEqualsTableSchema

• tSQLt.AssertLike

• tSQLt.AssertNotEquals

• tSQLt.AssertObjectDoesNotExist

• tSQLt.AssertObjectExists

• tSQLt.AssertResultSetsHaveSameMetaData

• tSQLt.Fail

Рассмотрим подробнее tSQLt.AssertEquals.

Процедура tSQLt.AssertEquals сравнивает ожидаемое и фактическое значение и принимает три параметра:

• @expected — ожидаемое значение, которое будет сравниваться с проверяемым значением.

• @actual — полученный результат, который и тестируем.

• @message — сообщение, которое выводится при падении теста.

Давайте напишем простой модульный тест функции, вычисляющей налог для суммы, переданной в параметре.

Сама функция CalculateTaxAmount:

CREATE OR ALTER FUNCTION CalculateTaxAmount(@amt MONEY)
RETURNS MONEY
AS BEGIN
RETURN (@amt /100)*18 
END;
GO
 
select dbo.CalculateTaxAmount(100) AS TaxAmount

В этом примере мы передаем в параметре 100 и получаем в результате 18. Теперь напишем тест для этой функции.

EXEC tSQLt.NewTestClass 'DemoUnitTestClass';
GO
 
 
CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount]
AS
BEGIN
DECLARE @TestedAmount as money = 100
DECLARE @expected as money  = 18
DECLARE @actual AS money 
SET @actual = dbo.CalculateTaxAmount(100)
 
EXEC tSQLt.AssertEquals @expected , @actual
 
END

Для выполнения теста используется хранимая процедура tSQLt.Run. В параметре можно передать имя тестового класса, чтобы запустить все тесты, связанные с указанным классом, или указать конкретный тест.

tSQLt.Run 'DemoUnitTestClass.[test tax amount]'

tSQLt.Run 'DemoUnitTestClass'

Как видно, наш тест выполнился успешно. Давайте изменим ожидаемый результат и повторно запустим тест.

CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount]
AS
BEGIN
DECLARE @TestedAmount as money = 100
DECLARE @expected as money  = 20
DECLARE @actual AS money 
SET @actual = dbo.CalculateTaxAmount(100)
 
EXEC tSQLt.AssertEquals @expected , @actual
 
END
GO
 
tSQLt.Run 'DemoUnitTestClass.[test tax amount]'

Тест упал. Сообщения о результатах выполнения теста вполне понятные. В первой строке мы можем увидеть причины падения теста: ожидаемые и фактические значения различны.

Важный момент — именование тестов. Имена тестов должны начинаться с "test". Если назвать по-другому, то tSQLt не сможет найти и выполнить тест. 

CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount]
AS
BEGIN
DECLARE @TestedAmount as money = 100
DECLARE @expected as money  = 20
DECLARE @actual AS money 
DECLARE @Message AS VARCHAR(500)='Wrong tax amount'
SET @actual = dbo.CalculateTaxAmount(100)
 
EXEC tSQLt.AssertEquals @expected , @actual ,@Message
 
END
GO
 
tSQLt.Run 'DemoUnitTestClass.[test tax amount]'

Мы рассмотрели только самый базовый пример, в реальном мире, конечно, тесты могут быть намного сложнее. Поэтому необходимо хорошо понимать паттерн Arrange-Act-Assert относительно SQL-тестов. Этот паттерн предлагает разделять тест на три части:

• Arrange — объявление переменных, определение предварительных условий и входных данных.

• Act — выполнение проверяемого кода и фиксация результата выполнения.

• Assert — сравнение ожидаемого и фактического значения.

Укажем в нашем тесте эти части в комментариях:

CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount]
AS
BEGIN
----------------------Arrange-----------------------------
DECLARE @TestedAmount as money = 100                   ---
DECLARE @expected as money  = 20                       --- 
DECLARE @actual AS money          --- 
DECLARE @Message AS VARCHAR(500)='Wrong tax amount'    ---
----------------------------------------------------------
----------------------Act---------------------------------
SET @actual = dbo.CalculateTaxAmount(100)              ---
----------------------------------------------------------
----------------------Assert------------------------------
EXEC tSQLt.AssertEquals @expected , @actual ,@Message  ---
----------------------------------------------------------
END

Заключение

В этой статье мы поговорили о важности модульного тестирования SQL-кода и о том, как реализовать этот подход с помощью фреймворка tSQLt.

---

Модульное тестирование кода бэкенда прочно вошло в нашу жизнь, но код базы данных по-прежнему мало кто тестирует. Приглашаем всех заинтересованных на открытое занятие «Тестирование T-SQL кода с помощью tSqlt». На этом уроке поговорим о модульном тестировании кода SQL Server и использовании для этого tSqlt. Регистрация открыта по ссылке.