Freigeben über

Types of database unit tests

  • Artikel

Since database unit testing is a fairly new concept, I thought I’d give you a sense for some types of unit tests you can write for your database.

 

Feature Tests

The first and likely most prevalent type 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 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 occured. However, it is more than just these types of objects. You can imagine wanting to ensure a view, for example, returns the appropriate calculation from a computed column. The possibilities here are large, but I think you get my drift.

 

Schema Tests

One of the most critical aspects of a database is its schema and testing to ensure the schema is 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 may want to ensure that your database does in fact contain the 1000 tables that it is expected to.

 

Security Tests

In today’s day and age, the security of the data 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 various users 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

Oftentimes a database contains 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 city and state are a great example of this kind of data. It is thus useful to create tests to ensure that you stock data does in fact exist in your database.

 

This is of course is just my short list and I hope you find many other uses for database unit testing. And when you do, please let me know! I’d love to keep tabs on all the innovative ways this feature is put to use.

 

Sachin Rekhi

Comments