How to write unit tests for database calls

Question

I'm almost starting a new project and (sigh!) The first time I try to include unit tests in my project.

I am having trouble developing separate tests. I have several methods that were easy enough for testing (pass two values ​​and check the expected result). I have other pieces of code that do more complex things, such as making database queries, and I'm not sure how to test them.

public DataTable ExecuteQuery(SqlConnection ActiveConnection, string Query, SqlParameterCollection Parameters) { DataTable resultSet = new DataTable(); SqlCommand queryCommand = new SqlCommand(); try { queryCommand.Connection = ActiveConnection; queryCommand.CommandText = Query; if (Parameters != null) { foreach (SqlParameter param in Parameters) { queryCommand.Parameters.Add(param); } } SqlDataAdapter queryDA = new SqlDataAdapter(queryCommand); queryDA.Fill(resultSet); } catch (Exception ex) { //TODO: Improve error handling Console.WriteLine(ex.Message); } return resultSet; } 

This method essentially takes all the necessary bits and fragments to retrieve some data from the database and returns the data in a DataTable.

The first question is probably the most difficult: what should I test in this situation?

After that, the question arises as to whether to mock the database components or try to test the actual database.

Answer 1

What are you testing?

There are three possibilities: from the head:

but. You test the DAO class (data access object), making sure that it correctly marshals the values ​​/ parameters that are transferred to the database, and correctly the marshaling / conversion / packaging results received in the database.

In this case, you do not need to connect to the database at all; you just need a unit test, which replaces the database (or an intermediate level, like JDBC, (N) Hibernate, iBatis) with a layout.

C. You check the syntactic correctness of (generated) SQL.

In this case, because the SQL dialects are different, you want to run (possibly generated) SQL with the correct version of your RDBMS, instead of trying to mock all the quirks of your RDBMS (and so that any RDBMS updates that change the functionality get into your tests) .

C. You check the semantic correctness of your SQL, that is, for a given basic data set, your operations (access / selection and mutations / inserts and updates) create the expected new data set.

To do this, you want to use something like dbunit (which allows you to set the baseline and compare the result set with the expected result set) or, possibly, do a full test in the database using the technique described here: The best way to test SQL queries .

Answer 2

That's why (IMHO) unit tests can sometimes create a false sense of security on the part of developers. In my experience with applications that speak to the database, errors are usually the result of the data being in an unexpected state (unusual or missing values, etc.). If you regularly model data access in your unit tests, you will think that your code works fine when it is actually still vulnerable to this kind of error.

I believe your best approach is to have a test database convenient, filled with gobs from shitty data and run tests of database components. Keeping in mind that your users will be much better than you twisting your data.

Answer 3

The whole purpose of unit test is to isolate the block (duh). The whole point of the database call is to integrate with another block (database). Ergo: this makes no sense for unit test database calls.

However, you must use the integration database calls (and you can use the same tools that you use for unit testing).

Answer 4

For love of God, do not check a live, already populated database. But you knew that.

In general, you already have an idea of ​​what data each request will receive, regardless of whether you are authenticating users, viewing entries in the phone book / org chart, or something else. You know which fields are of interest to you, and you know what restrictions exist on them (for example, UNIQUE , NOT NULL , etc.). You are testing a module that interacts with a database, not a database, so think about how to test these functions. If it is possible for the field to be NULL , you should have a test to verify that your code correctly processes NULL values. If one of the fields is a string ( CHAR , VARCHAR , TEXT , & c), check if you control the escaped characters correctly.

Suppose users try to put something * in the database and generate test cases accordingly. You want to use mock objects for this.

* Inclusion of unwanted, malicious or invalid input.

Answer 5

You can test everything except: queryDA.Fill(resultSet);

Once you run queryDA.Fill(resultSet) , you should either queryDA.Fill(resultSet) / fake the database, or do integration testing.

For example, I do not consider integration testing to be bad, it will simply catch errors of a different kind, have different chances of false negative and false positive results, and it is unlikely to be carried out very often, because it is so. slow.

If I conducted unit testing of this code, I would check if the parameters are correctly assembled, does the linker command create the correct number of parameters? Do they all have value? Are zeros, empty strings and DbNull handled correctly?

Actually populating a dataset is testing your database, which is an unstable component that goes beyond your DAL.

Answer 6

Strictly speaking, a test that writes / reads from a database or file system is not a unit test. (Although it may be an integration test, and it can be written using NUnit or JUnit). Unit tests must test the operations of one class, isolating its dependencies. Therefore, when you write a block test for interfaces and layers of business logic, you do not need a database at all.

OK, but how do you test the database access level? I like the advice from this book: xUnit Test Patterns (link points to the book "Testing with DB". Keys:

• use round-robin tests
• Do not write too many tests in test equipment to access the data, because they will work much slower than your "real" unit tests.
• if you can avoid testing with a real database, test without a database

Answer 7

For unit tests, I usually mock or fake a database. Then use your false or fake implementation through dependency injection to test your method. You probably also have integration tests that will check for constraints, foreign key relationships, etc. In your database.

As for what you would check, you have to make sure that the method uses a connection from the parameters, that the query string is assigned to the command, and that the result set you return is the same as the one you provide through the wait by the Fill method. Note. It is probably easier to test the Get method, which returns a value, than the Fill method modifies the parameter.

Answer 8

To do this correctly, you must use some dependency injection (DI), and for .NET a few. I am currently using the Unity Framework, but there are others that are easier.

Here is one link from this site to this topic, but there are others: Injecting dependencies in .NET with examples?

This will allow you to more easily make fun of other parts of your application by simply using the mock class to implement the interface so that you can control how it will respond. But it also means developing an interface.

Since you asked about best practices, this will be the one, IMO.

Then, without going to db, if you do not need, as expected, this is different.

If you need to test certain types of behavior, such as cascading deletion of a foreign key, you might want to write database tests for this, but as a rule, it is better not to go to a real database, because more than one person can run unit test for times, and if they go to the same database tests, they may fail because the expected data may change.

Edit: by unit test database, I mean this, since it is designed to use t-sql to do some configuration, testing and breaking. http://msdn.microsoft.com/en-us/library/aa833233%28VS.80%29.aspx

Answer 9

In a JDBC-based project, a JDBC connection can be modeled so that tests can be performed without an operational DBMS, with each test case isolated (no data conflict).

This allows you to check if the persistence code passes the correct requests / parameters (for example, https://github.com/playframework/playframework/blob/master/framework/src/anorm/src/test/scala/anorm/ParameterSpec.scala ) JDBC (parsing / mapping) results are expected ("takes all the necessary bits and pieces to retrieve some data from the database and returns the data in a DataTable").

A framework such as jOOQ or my Acolyte framework can be used for: https://github.com/cchantep/acolyte .