Is it a good practice to have a pre-filled embedded database for development?

Question

I'm currently working on a project which only uses its database for data storage. This means there are no triggers or stored procedure in it, just tables and data to put into it. In this scenario I'm building the application using Spring 4. The Spring container allows you to have profiles, which basically decide which beans get loaded in your project. You can make sure an embedded in-memory database or a full-fledged oracle database is used for data storage, without having to change a single thing in your code.

This is obviously using for unit and integration testing. Not having to rely on a database is great for your tests. I was wondering if the same doesn't count during development. In this specific project I'm not entirely sure about what my final data model will look like, and because I don't feel like writing SQL over and over to move columns I'd prefer to use some kind of in-memory database. Of course one with pre-filled development data, so I can mimic a system that's been filled with all kinds of data, and I can easily add more for small tests during development.

Is this a recommended practice or inherently bad in a way?

Answer 1

Why not!

Personally, I prefer to have scripts that populate an empty database with new data so I can a) know exactly what is going in and b) to easily change them.

I do this even for our big production databases, keeping a script with the 'usual data' that I would otherwise have to enter by hand - things like a predefined user id for myself and standardised configuration.

This is probably more useful in development than it is for integration or especially QA, who should be creating their own DB using the tools provided in order to test that the tools still work with new versions.

Answer 2

This is a really good idea, and you should do this. I would go above and beyond, though, with preconfigured databases from which to choose as well as the ability to point to shared databases for the team.

• You want to be able to reset your database. You may need it to be in some known, difficult to reproduce state to trigger a particular bug. Being able to test and fix those types of bugs is a huge help.

• Being able to get up and running quickly is crucial. That is unproductive time at the beginning of a project, or during an iteration if you need to reset something.

• Being able to switch between databases can be a huge help. What if QA reports a bug but you are unable to reproduce it? Sure, you can see it on the QA system, but that does not help you step through the code. Being able to reconfigure your development environment to use a different database quickly and easily can help reduce the feedback cycle.

• I would have a set of scripts for quickly adding specific data to a new database for testing specific scenarios. These could be SQL scripts, or automated test scripts such as Selenium.

Being able to configure and use a database quickly and arbitrarily is a tremendous help in development with many upsides and only one downside that I can think of:

• It makes it easy to have multiple people using the same database. If multiple developers and testers are modifying the same data, it may actually invalidate their tests as they step on each others' toes. While this might not be a big deal in production, it can be frustrating in development and testing where data may need to be in a specific state during testing and debugging.

Answer 3

Yes, this is a very good idea.

To make development easy, it is very useful to minimize the steps between "check out sources" and "system is running". Providing a pre-filled database helps with that. I have used this in the past and found it very helpful.

There are different ways of accomplishing that:

• an SQL script that is run automatically (or manually) - often the simplest solution
• you can write some code that populates the database using the same classes/framework that you use in production - lets you re-use code, and means that schema changes automatically apply to your test database
• have some kind of DB dump that you load - less flexible to change, but useful if you want to use real data (of course, take care to anonymize/sanitize first!)

Ideally, the test data generation should run automatically during build or system startup.

Obviously, build in safeguards so all this will never run in production!