Managing a development database in a group project

Question

I'm working for a small webdevelopment company and we want to set up development environments. We'll be using the Laravel framework in combination with Homestead. Now, Homestead has its own database and can work with migrations from Laravel, meaning that any changes to the existing database from previous versions can be updated with a single terminal command. The biggest con to Laravel's migrations is that all the previously stored data in the database will be lost for every migration, meaning you would have to fill it up all over again.

As you might imagine, we don't want to keep refilling our database everytime we change something. So we thought of hosting a single development database instead, but this also comes with cons. Someone in our project might change something in the database, meaning all others in our project may have their code not working on their local development environment, due to the database being changed.

We do have a solid database design, but I'm just worried that something may still go wrong during development. How would we go on about managing our development database properly without loss of data and without the hassle when something changes?

Answer 1

I am not a Laravel expert, but the problem you describe is not very special to that framework. You have different instances of a database, and you need to make sure when schema changes have to be applied, that

• all database instances get the same schema updates

• existing data does not get lost, and if necessary, will be migrated to the new schema version

(if that is a production db, a test db or a development db does not really matter).

You seem to be under the impression Laraval addresses only the first point, but I think that is not correct, see below.

The standard solution to those problems is writing SQL scripts with schema updates and data migrations for each step, and keeping those scripts in code! Do not (!) any schema updates by "tools like PHPMyAdmin", as you wrote in your comment - that will not become reproducible. Those SQL scripts should be embededded into some kind of host language or framework, so you can make them robust against out-of-order execution, repeated execution, or handle errors. And that is where tools like Laravel come into play.

By a short web search, I found this old SO post where it is demonstrated how Laravel migrations can be used to migrate the data as well. So your initial premise seems to be wrong, one can write migrations which actually keep the existing data or transform the data. Of course, one has to be careful when writing migrations, by not accidentally deleting a column with data (but that is not different with any other kind of hand-written SQL or host language).

Moreover, you have to be careful with the usage of schema rollbacks. For example, when you add a new column, it might be possible you cannot easily reverse that schema change after you added some data to that column and some referring data elsewhere in the db.

So if you want to use Laravel migrations, do this, but with care. I recommend to test the full sequence of migrations every time you added one on a small, backuped database first, before you distribute the changes among the whole team. Moreover, make sure that all of your databases which contain data you cannot get easily elsewhere, are included in your daily backups.