LightWeight Doubles with SQLite

In a previous article, I mentioned that one reason to use a Test Double was to increase performance. One place where this is evident is in database access. Doubles can be very useful for database operations for the following reasons:

• Isolation from the production database – Isolate any changes you make during testing from the production database by using a different database during testing.
• Account for unavailable databases – Perhaps the database is behind a corporate firewall and not accessible to your development or testing machine. Using a locally accessible test database can help with this.
• Transaction Speed – We can prepopulate the database with just enough information to validate our test, instead of searching through millions of records.
• Access Speed – Accessing a database over the network can be slow and is variable depending on routing, network layout, and traffic. Accessing a local database is a much faster solution. An even faster solution is an in-memory database.

SQLite to the rescue

Using an in-memory SQLite database can solve all these problems. We create a new database for each test so it is isolated from our production database. The database is local so there is no network access issues or latency issues. We populate the database with just enough information for our tests so transactions occur fast. And lastly we use an in memory database so it is superfast.

You might be wondering how we can achieve all this. It starts by using a Humble Database Interface. It just handles SQL Queries. I recommend using Dr Powell’s SQLite library for talking to SQLite from LabVIEW. When you create the connection, simply specify an in memory database. Follow the instructions in my previous post for installing test doubles. For easier readability you might consider putting the initial population of the database in a subvi, especially if you are always populating it with the same or similar data.

One caveat is that you have to be using rather generic SQL queries. If you are using some special function that only works in SQL Server or Postgres or if you are making use of stored procedures then you might run into problems.