Local, recreateable development databases
If you’re a developer, do you have your own personal development database that you can recreate at the touch of a button?
If you said no, you’re not alone. In fact, I’ve been in that situation on many projects, where the entire team shares one big development database that is a backup of production, and all schema changes need to be coordinated with the other developers on the team. Usually things are this way because back in the day someone didn’t create a way to have local databases.
We were in the situation and we made the investment to have someone spend a week or two taking a copy of production, deleting all of the data from tables that weren’t lookup tables, and then creating a way to run all of the new SQL scripts against the small baseline copy. Now I can recreate my development database at the touch of a button.
The benefits of this are obvious, but since many people haven’t made this investment yet, let me list them anyway:
- I can hack up my database and not worry about affecting other developers
- I can hack up my database and then start over if I screw something up (as opposed to piece things back together so that the rest of the team can keep working)
- Automated tests that run against the database run MUCH faster
- No more confusion when someone else on the team changes some important piece of data without telling you
- I can check out any revision of the code and create the database that existed at that point in time
If you don’t have a local, recreateable database yet, make the investment. You will reap the benefits many times over and you’ll be so glad you did.
I totally agree. Each developer should work under their own copy of a database. Nothing is more annoying than tracking down random errors that you think you caused, when in fact, someone changed something on the community database.
I’m a firm believer a database schema, and lookup table data need to be under version control. At any point you should be able to go back in history and say “Yep, this is the right database schema for this version.”
How? Either by one huge create script, using a model to database generator (Ex. Code First Entity Framework, rake, etc.), or some other tool.
If work in the MSSQL world, and willing to drop the dime, I recommend SQL Connect, and/or SQL Compare Pro from RedGate. I love their tools.