2

I'm currently setting up integration testing for my company. I haven't done it before. We are developing a Java web application which uses MySQL as datasource.

I know it is very common to use an in-memory database like H2 or HyperSQL for development.

My question is, is it best practice to use a separate in-memory DB or can i just choose MySQL.

closed as primarily opinion-based by Scant Roger, user22815, durron597 Dec 7 '15 at 20:38

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Possible duplicate of Databases and Unit/Integration Testing – gnat Nov 28 '15 at 20:53
  • 1
    @gnat: that other question deals with how to prepare a test database before running any integration tests, it has almost nothing to do with the current question. Did you read only a bit more than the question title before hitting the "close" button? – Doc Brown Nov 28 '15 at 21:05
  • no i don't think so. That thread main focus is all about having pre-populated data or not to do integration testing. This thread is all about whether we should or should not use an in-memory db to execute integration tests – SoftwareDeveloper Nov 28 '15 at 21:10
2

Do you have to use an in-memory database?

No, you don't have to. But depending on what kind of application you are developing, and depending on what kind of tests you are going to implement, it can have some benefits.

Pros:

  • tests may probably run faster than on a MySQL server
  • administrative overhead may be smaller, because you want each dev to have his isolated database instance, and managing a MySQL server for each dev will probably mean more effort than a zero-administration DB
  • you will be forced to develop your application in a database-independent manner (which may improve the design of the app)

Cons

  • you cannot make use of any MySQL feature which is not also available at the in-memory system
  • you will be forced to develop your application in a database-independent manner (which may impose some restrictions and overhead to your app)
  • 1
    Aside, HyperSQL does have a database emulation mode where it tries really hard to match MySQL (or Oracle, or PostgreSQL, or ...) feature set. Stored procedures are a bit of a pain in it, but it does match the built in functions and data types very well. – user40980 Nov 28 '15 at 20:36
  • thanks for your answer plus outlining pros & cons + extra info. Really give me thought... – SoftwareDeveloper Nov 28 '15 at 21:11
1
  • What happens when all the code is right, but the database is down?
  • What happens when all the code is right, but the data in the database (or the time) has changed (select count(1) from table where date > sysdate - 1)?
  • What happens when two unit tests run at once?
  • What happens when one test terminates early and doesn't clean up?

All of these things make the actual database much harder to use for unit testing and you would find yourself spending a significant amount of time trying to account for them. For automated tests, the can create false negatives - where the test fails but everything is working correctly. "The build broke, time to check if the database was up last night at 2am..." and all the joys of going through that to try to figure out what went wrong.

The key to automated testing is being able to reproduce it. No matter when it runs, or what the situation of the rest of the network, the automated test shouldn't fail if the code is correct. And if you are going to have databases that might be up or down, or data that might have changed - the additional effort to try to make it work using an external database becomes excessive.

So, spin up a database in memory. Load it up with exactly the right data. Have it only be for that instance of the tests (and if another build fires off while its running, it gets its own instance). This makes it easier to rerun the test at any time.

The big danger of automated tests failing because of something beyond the control of the programmer is that those false negatives will be a "oh, the test failed, might be the database - I'll check it later" and that starts leading to ignored failing tests and a longer time frame between the failing test and the bug fix (when there is a problem with the code).

There are libraries for making testing against a database easier - that you fire up a new database schema just for your test. Though this has the additional cost of maintenance on the database (and if the database is down, it still fails) and only partially mitigates the bullet points at the start.

  • You make the (IMHO unfounded) assumption that not using an in-mem DB means all devs share one MySQL server - why? – Doc Brown Nov 28 '15 at 20:37
  • @DocBrown I'm making the assumption that the build server is using just one database server. Devs can do whatever they want and test it... though speaking from experience dev run local mysql databases can become painfully inconsistent with each other for their integration tests ("oh, I changed the price on that sku to test this other thing I was working on and it broke your test?") – user40980 Nov 28 '15 at 20:39
  • If you see that as a problem, I don't see why it makes a difference with an in-mem DB. – Doc Brown Nov 28 '15 at 20:43
  • 1
    Its a "when the nightly build runs at 2am, make sure everything is consistent" - that the dbas haven't taken the database down for maintenance that day; that some other dev hasn't committed code at 1:55 and made the database inconsistent for this unit test; that there aren't 100 other schemas that are taking up space and unused (maybe) from past runs. Being able to create a new instance of a database with a small foot print and loaded with exactly the right data to be able to run the tests in a predicable way is key. The way to do this with the least administrative overhead is an in memory db. – user40980 Nov 28 '15 at 20:47
  • Well, when you have a build server and run nightly integration tests, there should always be a dedicated DB for this, clearly not the production database, and not under control of any "dbas" outside the dev team. I agree with you that an in-memory DB maybe the perfect choice for this. but a MySql DB could probably be used for that purpose, too. It seems you are not answering the question "in-mem DB vs. MySQL", but "in-mem DB vs. shared, non-dedicated, hard-to-recreate from scratch DB server". – Doc Brown Nov 28 '15 at 20:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.