A friend and me are working on a webapp. We’re using git so each of us has a local copy of the app. But the problem is the database. We want to be able to see the latest changes the other person made. From what I understand, we have two options:
- Host the database on a server
- Add the .sql file to every commit we make.
For the first option, we might expose our system to some risks (which I’m scared of).
For the second option, we’re adding unnecessary work to our daily tasks (importing the .sql file and then creating the .sql file).
I’m new to this stuff – so any suggestions are welcome!
4
There are two distinct issues here:
- Schema management
- Test data
By far the bigger and more challenging problem is schema management – you need to have confidence that every instance of the database (that is notionally the same “version”) has the same schema.
There are many and various approaches to this (migrations are built in to Ruby on Rails for example) but fundamentally it comes down to two things:
- Having a mechanism to identify the schema version in the database
- Organising your schema changes so that they can be applied sequentially (and consistently)
The simplest mechanism (and one that works well for me) is to have a schema version number table (e.g. id, date applied, summary of changes, author).
In terms of organising schema changes, I use code – one method per version of the schema – that makes DDL calls to change the schema. Its relatively simple, its effective and it works for me. The advantage is that its repeatable (and can be included in automation), the downside is that its a bit more work to pull create the change scripts. I can then also define acceptable schema version ranges for a revision of the application and refuse to run (or perform schema updates or otherwise take appropriate steps) if they don’t match.
In terms of test data… I don’t have good answers, you may want a script to populate the database with suitable sample data, said script will have to evolve and grow. Alternatively you can version control a reference copy that you update from time to time.
Fundamentally though, you shouldn’t be depending on arbitrary data changes made in another instance to be able to develop your application (I’m not assuming that you are, just making the observation!). If the data changes are required then they probably need to be part of a schema version update.