Using Transactions for Unit Tests

Unit tests should preferably be independent of external services, systems and files. The standard way to achieve this is to create mocks. A mock is an object that can be used in place of the real resource and act in a predictable way to ensure the tests always give the same result. I think that this is a perfectly valid approach for any external services that the system is integrated with. The problem for business applications is the database. Mocking the database by providing a mock implementation of the database access layer is a huge task. It is too huge to be worth the effort. Another approach that I often use is to use a database in the tests, but keep it unchanged through transactions.

Transaction Safety for Manual SQL Updates

When maintaining a system that is in production there are often issues that need to be solved by running custom SQL scripts directly in the production database. There can be different reasons for this:

  • Updating a lookup table to add a new option to dropdown boxes, where there is no user interface for handling the table.
  • Adjusting data in a way not permitted by the normal work flow.
  • Reverting an incorrect operation performed by a user.
Whenever it is time to run SQL directly in the live database I tend to become nervous. One single error and a lot of data could be lost or made incorrect. Usually I make such an update in three steps:

  1. Find the data that should be changed.
  2. Create the update statement and validate it
  3. Run the complete statement.
Software Development is a Job – Coding is a Passion

I'm Anders Abel, an independent systems architect and developer in Stockholm, Sweden.

profile for Anders Abel at Stack Overflow, Q&A for professional and enthusiast programmers

Code for most posts is available on my GitHub account.

Popular Posts



Powered by WordPress with the Passion for Coding theme.