How to do database unit testing?

I have heard that when developing application which uses a database you should do database unit testing.

What are the best practices in database unit testing? What are the primary concerns when doing DB unit testing and how to do it "right"?

> What are the best practices in database unit testing?

The DbUnit framework (a testing framework allowing to put a database in a know state and to perform assertion against its content) has a page listing database testing best practices that, to my experience, are true.

> What are the primary concerns when doing db unit testing

• Creating an up to date schema, managing schema changes
• Setting up data (reference data, test data) and maintaining test data
• Keeping tests independent
• Allowing developers to work concurrently
• Speed (tests involving database are typically slower and will make your whole build take more time)

> and how to do it "right"?

As hinted, follow known good practices and use dedicated tools/frameworks:

• Prefer in memory database if possible (for speed)

• Use one schema per developer is a must (to allow concurrent work)

• Use a "database migration" tool (à la RoR) to manage schema changes and update a schema to the ultimate version

• Build or use a test harness allowing to put the database in a known state before each test and to perform asserts against the data after the execution (or to run tests inside a transaction that you rollback at the end of the test).

A list of items that should be reviewed and considered when staring with database unit testing

• Each tester needs a separate database, in order to avoid interfering with activities of other tester/developer
• To have an easy way of creating a database to be tested (this is related to having a SQL Server database under version control). This is specifically useful when trying to find what went wrong if some tests fail
• Focus on specific areas and creating tests for a single module instead of covering all at once. Adding tests granularly is a good way to be efficient
• Make sure to provide as many details as possible when a test fails, to allow easier debugging
• Use one and the same test data for all tests

If test are implemented using tSQLt framework, the unit testing process could be complicated when dealing with a lot of databases from multiple SQL Server instances. In order to maintain, execute and manage unit tests directly from SQL Server Management Studio, ApexSQL Unit Test can be used as a solution

Take a look at this link. It goes over some of the basics for creating unit testing stored procs in SQL Server as well as the different types of unit tests and when you should use them. I'm not sure what DBMS you are using but obviously this article is geared towards SQL Server.

Stolen from the article:

> Feature Tests > > The first and likely most prevalent > class of database unit test is a > feature test. In my mind, feature > tests test the core features—or APIs, > if you will—of your database from the > database consumer's perspective. > Testing a database's programmability > objects is the mainline scenario here. > So, testing all the stored procedures, > functions, and triggers inside your > database constitute feature tests in > my mind. To test a stored procedure, > you would execute the stored procedure > and verify that either the expected > results were returned or the > appropriate behavior occurred. > However, you can test more than just > these types of objects. You can > imagine wanting to ensure that a view, > for example, return the appropriate > calculation from a computed column. As > you can see, the possibilities in this > realm are large. > > Schema Tests > > One of the most critical aspects of a > database is its schema, and testing to > ensure that it behaves as expected is > another important class of database > unit tests. Here, you will often want > to ensure that a view returns the > expected set of columns of the > appropriate data type in the > appropriate order. You might want to > ensure that your database does, in > fact, contain the 1,000 tables that > you expect. > > Security Tests > > In today's day and age, the security > of the data that is stored within the > database is critical. Thus, another > important class of database unit tests > are those that test the database > security. Here, you will want to > ensure that particular users exist in > your database and that they are > assigned the appropriate permissions. > You will often want to create negative > tests that attempt to retrieve data > from restricted tables or views and > ensure that the access is > appropriately denied. > > Stock-Data Tests > > Many databases contain stock data, or > seed data. This data changes > infrequently and is often used as > lookup data for applications or end > users. ZIP codes and their associated > cities and states are great examples > of this kind of data. Therefore, it is > useful to create tests to ensure that > your stock data does, in fact, exist > in your database.

I'm glad you asked about Unit Testing, and not testing in general.

Databases have many features that need to be tested. Some examples:

• Data Types/Size/Character sets (try inserting a swedish name, or long urls or numbers from the real worlds, and see if your column definitions are ok)
• Triggers
• Contraints (foreign keys, uniqueness...)
• Views (check that data is correctly included/excluded/transformed)
• Stored Procedures
• UDFs
• Permissions
• ...

This is useful not only when you change something in your database, but also when you upgrade your dbms, or change something in your settings.

Generally, Integration Testing is done. This means that a Test Suite in a programming language like PHP or Java is created, and the tests issue some queries. But if something fails, or there are some exceptions, it's harder to understand the problem, for 2 reasons:

• The problem could be in your PHP code, or in PHP configuration, or in the network, or...
• The SQL statements are harder to read and modify, if they are embedded in another programming language.

So, in my opinion, for complex databases you need to use a Unit Testing framework which is written in SQL (using stored procedures and tables). You have to choose it carefully, because that kind of tools is not widely used (and thus not widely tested). For example, if you use MySQL I know these tools:

• STK/Unit http://stk.wikidot.com/stk-unit
• utMySQL http://utmysql.sourceforge.net/

I use junit/nunit/etc and code up database unit tests with java or c#. These can then run on an integration server perhaps using a separate schema to the test database.

The latest oracle sql developer comes with a built in unit testing framework. I had a look into this but would NOT use it. It uses a GUI to create and run tests and stores all the tests in the database so not so easy to put test cases under version control. There are probably other testing frameworks out there I imagine they might be specific to your database.

Good practices are similar to regular unit tests:

• put the tests under source control
• make tests that run fast - don't test too much at once
• make your tests reproducible

Take a look on DBTestDriven framework. It works great for us. Download it from GitHub or their website.

As for JVM development, unit tests can benefit from JDBC abstraction: as soon as you know which JDBC data are raised by DB access, these JDBC data can be 'replayed'.

Thus DB access case can be 'reproduced' for testing, without the target DB: no test/data isolation complexity, ease continuous integration.

My framework Acolyte is an helpful framework in this way (including studio GUI tool to 'record' DB result): https://github.com/cchantep/acolyte

The application of unit testing allows you to ensure that once you write something, it can be verified, and then when it needs to change, you can verify that all of the previously passed tests will continue to pass. No matter if you are the only database person in your company or if there are 1000 people in your company. No matter if you have one database or 1000 databases.

That will give you confidence that your changes will be more accurate and less likely to break other things that rely on your code. And after all, that will inevitably help you sleep at night, enjoy your vacations more and be more confident in what you develop.

QA engineers control views, triggers in the database testing, and they can create a blank instance of the database to get started with minimal building blocks.

Here is how testers can perform unit testing on databases:

• The first step in the process is to create a blank database instance. You can start modifying items and adding new ones until it has everything necessary for your test;
• It would be best if we could automate testing procedures to ensure that the database is in a known state before every test run and verify its current condition after each one;
• You can also look for problems like missing references that can happen due to accidentally removing or renaming objects, which is often the result of a failed database update;
• A test should be conducted to ensure that the database is restored when finished with testing.

Now databases differ significantly from application code – they require heightened precision. They must be tested periodically to avoid breaches of data integrity etc.

What we should remember while performing unit database testing:

1. Unit tests can be automated, and you can script a set of database operations with the same ease as executing code;
2. Unit tests are great for testing individual triggers, views, and sprocs. You can test the behavior of each one to make sure that it works just as you want it to;
3. Unit tests are a fantastic way to create an executable representation of your database testing operations so that you can quickly test and validate new code before rolling it out;
4. Unit tests can produce consistent results. You will have a clear understanding of what outputs can be expected if everything goes according to plan if every input is mapped as part of the test;
5. Unit tests should be independent of one another. You will have to manage some setup and teardown, but the test should not have any relationship with other unit tests.

There are many test management and test automation tools that can be helpful while performing unit testing for SQL databases. I use ​​Data Factory, aqua ALM, Mockup Data and DTM Data Test Generator.