Save 37% off PRO during our Black Friday Sale! »

Adding unit tests with tSQLt to the database deployment pipeline

Adding unit tests with tSQLt to the database deployment pipeline

Talk presented @ PortoData (30-01-2018)

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=128

Eduardo Piairo

January 30, 2018
Tweet

Transcript

  1. ADDING UNIT TESTS WITH TSQLT TO THE DATABASE DEPLOYMENT PIPELINE

    Eduardo Piairo @EdPiairo #PortoData
  2. ABOUT ME Adding unit tests with tSQLt to the database

    pipeline @EdPiairo, #PortoData @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@gmail.com http://www.eduardopiairo.com/ Eduardo Piairo Operations Engineer DevOps Porto Founder
  3. TSQLT Adding unit tests with tSQLt to the database pipeline

    • Database unit testing framework for Microsoft SQL Server • Allow to write T-SQL code as tests • Tests are automatically run within transactions • Provides a way to isolate code and tables using mocking • Output can be plain text or XML @EdPiairo, #PortoData
  4. TSQLT INSTALL Adding unit tests with tSQLt to the database

    pipeline • tSQLt.class.sql • CLR • clr enabled • clr strict security • Should be installed in the development database @EdPiairo, #PortoData
  5. WHAT’S A TSQLT TEST? Adding unit tests with tSQLt to

    the database pipeline • Stored Procedure • Starts with the word test • Must be in a schema that contains the extended property tSQLt.TestClass = 1 • tSQLt.NewTestClass • Each test is wrapped in a transaction: modifications are rolled back and the results saved • Benefits • Requirements documentation • Ability to refactor • Isolation - unrelated changes do not affect other parts of the system • Help structure code into distinct components @EdPiairo, #PortoData
  6. WHAT’S CAN BE TESTED? Adding unit tests with tSQLt to

    the database pipeline • Stored Procedures • Functions • Views • Tables • Tables constrains that are critical @EdPiairo, #PortoData
  7. TESTING WHITOUT DATA Adding unit tests with tSQLt to the

    database pipeline • Unit tests is about testing code • You do not need a database full of data, you need the opposite • Data setup • Only the necessary data for making the test work is needed • Mocking • tSQLt.FakeTable • tSQLt.FakeFunction • tSQLt.SpyProcedure @EdPiairo, #PortoData
  8. ASSERTS Adding unit tests with tSQLt to the database pipeline

    • tSQLt.AsserEquals • tSQLr.AssertEqualsTable • tSQLt.AssertEmptyTable • tSQLt.AssertEqualsString • tSQLt.AssertEqualsTableS chema • tSQLt.AssertLike • tSQLt.AssertNotEquals • AssertObjectDoesNotExist • AssertObjectExists • AssertResultSetsHaveSam eMetaData • Fail @EdPiairo, #PortoData
  9. DEMO Adding unit tests with tSQLt to the database pipeline

    @EdPiairo, #PortoData
  10. REFERENCES Adding unit tests with tSQLt to the database pipeline

    • http://tsqlt.org/user-guide/tSQLr.AssertEqualsTable • https://courses.agilesql.club/ @EdPiairo, #PortoData
  11. Q&A Operations for databases @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@gmail.com http://www.eduardopiairo.com/ @EdPiairo, #PortoData