Shared development and CI database

Question

I'm just about to set up CI for my web application. I'm using Bitbucket and Pipeline beta as a CI. The web application is strongly coupled to the database, nothing can be tested without data in it.

My team also has some issues with the local devenv, as due to frequent DB schema changes, Django migrations are irreversible, so we are ending up reverting the DB, and setting up the same users every now and then (which is a lot of repeated, manual work, feels like something that could be automated).

I'm wondering if there's a best practice to solve these issues. I had an idea of having a version-controlled database, that can be considered as the single source of truth for local development, and for the e2e tests on the CI. This way we won't end up using a shared database, but everyone can pull the latest version of the DB fixture and update it if necessary. Are there any reasons against this? (I never heard of anyone doing this before).

Edit

We're working with Django backend, Angular frontend and MySQL

Answer 1

We have a set of SQL scripts that we use to create and modify the DB between versions - as its text, its stored in the VCS and tagged along with all the other code.

One of the scripts is a "set up default data" one, and another is a dev-specific script that sets all the usual environment, such as developer users.

When you want a new DB, its trivial to run the script from a cmd line and it creates, configures and sets up a DB for the specific version we're using. Works very well.

The only difficulty is that any custom data that has been entered is lost, but its not too much trouble to use the app to add new data, and is probably a good thing to clear out all the dev crud that builds up regularly anyway.

Answer 2

Lots of people use version control with databases.

There's more than one pattern for doing so and any number of tools but fundamentally its a sound way to go.

Similarly I strongly advocate for local databases for developers if at all reasonable - you will get merge issues if you're changing the schema a lot, but you should be able to work out practices to mitigate this as you go.

For e2e tests off the back of your build you can have a "reference instance" of the database that you migrate to current and test against, this needs to be sanitised but representative of real world data. You'll want to update the reference instance regularly but it doesn't have to be completely up to date (because you have automated migration management) and its quite likely going to be the same database that you want your devs to work against (if a dev's db gets into a mess then you restore the reference and run the migrations and...)

Answer 3

I had thought about a similar setup a few years ago. If you happen to use MS SQL Server, then Visual Studio and/or SQL Server Management Studio have some good tools to help with that. (Check out the database project in Visual Studio. It's made a lot of progress in recent years.)

The only downside is the amount of work that needs to be done upfront, to build the scripts to change between DB versions. For a small team that wasn't even thinking about CI, this proved too big a price to pay.