4 ms·

Why can't you version control database code/schema? I can do it fine using Rails migrations. I've also been looking at http://sqitch.org/ http://sqitch.org/ as

Why can't you version control database code/schema? I can do it fine using Rails migrations. I've also been looking at http://sqitch.org/ http://sqitch.org/ as well. Why can't you create parallel instances for each dev/environment? (If licensing costs are a problem, maybe look at a free one?)

btilly 13y ago
Why can't you version control database code/schema? I can do it fine using Rails migrations. I've also been looking at http://sqitch.org/ http://sqitch.org/ as well. If you're willing to use a third party tool to manage the database, and ONLY use that tool, then you can solve version control/migration. I've used and written such tools myself. However once you have software development and database administration separated, the DBAs generally need access to the database in a way that does not go through your tool / software development cycle / etc. And once you have random things in your database that your tool knows nothing about, you're on your way to maintenance headaches. Furthermore third party tools like this generally want your site to be offline during database migrations. But if you've got a production site going 24x7, then you really want to figure out how to roll out upgrades without downtime. This is much, much easier if you can upgrade database and website independently of each other, with the database going first. That important detail tends to be poorly handled in my experience. (Though things change and I'd love to hear if that had improved.) Furthermore you have the headache of identifying the difference between data tables and metadata tables. That is, some tables contain data that gets modified on the fly during normal use. (eg user information) Other tables contain data that is really application configuration. (eg allowed statuses for something) Why can't you create parallel instances for each dev/environment? (If licensing costs are a problem, maybe look at a free one?) You can. The difficulty lies in keeping them synchronized with what is in your repository. And also in providing ways to copy user information to the private instances so that devs can reproduce production problems. (Doubly so when your production problem is a performance issue that requires a copy of production data to recreate.) These problems are all solvable. I've solved them. But the general story that databases provide on these issues does not match the simplicity and flexibility of having databases focus on providing a common data structure, and the application being stored elsewhere in your favorite source control system (eg git).
- joevandyk 13y ago
  However once you have software development and database administration separated, the DBAs generally need access to the database in a way that does not go through your tool / software development cycle / etc. Why would this happen? Database changes should be treated just like any other code update. Furthermore third party tools like this generally want your site to be offline during database migrations. They do? Why? Furthermore you have the headache of identifying the difference between data tables and metadata tables. That is, some tables contain data that gets modified on the fly during normal use. (eg user information) Other tables contain data that is really application configuration. (eg allowed statuses for something) I don't see why this is a problem at all (or worth mentioning). The difficulty lies in keeping them synchronized with what is in your repository. This is why you use migrations/tools/etc for managing database updates. And also in providing ways to copy user information to the private instances so that devs can reproduce production problems. (Doubly so when your production problem is a performance issue that requires a copy of production data to recreate.) I'm not sure how this is related. But the general story that databases provide on these issues does not match the simplicity and flexibility of having databases focus on providing a common data structure, and the application being stored elsewhere in your favorite source control system (eg git). I have no idea why you wouldn't want the database change scripts/functions/schema to be stored in git, alongside the application code.
  - nbouscal 13y ago
    > I have no idea why you wouldn't want the database change scripts/functions/schema to be stored in git, alongside the application code. I couldn't agree more. It boggles my mind how many people are resistant to storing this stuff in version control. It is code! Treat it like code! The exact same thing applies to configuration management. Your chef cookbooks should live in your git repository right next to your application code, and right next to your database code. That's the only possible way for every node in your source history graph to be functional in isolation. If your database changes independently of your version control, you simply cannot check out an arbitrary commit and expect anything to work.
  - btilly 13y ago

btilly 13y ago

Why can't you version control database code/schema? I can do it fine using Rails migrations. I've also been looking at http://sqitch.org/ http://sqitch.org/ as well. If you're willing to use a third party tool to manage the database, and ONLY use that tool, then you can solve version control/migration. I've used and written such tools myself. However once you have software development and database administration separated, the DBAs generally need access to the database in a way that does not go through your tool / software development cycle / etc. And once you have random things in your database that your tool knows nothing about, you're on your way to maintenance headaches. Furthermore third party tools like this generally want your site to be offline during database migrations. But if you've got a production site going 24x7, then you really want to figure out how to roll out upgrades without downtime. This is much, much easier if you can upgrade database and website independently of each other, with the database going first. That important detail tends to be poorly handled in my experience. (Though things change and I'd love to hear if that had improved.) Furthermore you have the headache of identifying the difference between data tables and metadata tables. That is, some tables contain data that gets modified on the fly during normal use. (eg user information) Other tables contain data that is really application configuration. (eg allowed statuses for something) Why can't you create parallel instances for each dev/environment? (If licensing costs are a problem, maybe look at a free one?) You can. The difficulty lies in keeping them synchronized with what is in your repository. And also in providing ways to copy user information to the private instances so that devs can reproduce production problems. (Doubly so when your production problem is a performance issue that requires a copy of production data to recreate.) These problems are all solvable. I've solved them. But the general story that databases provide on these issues does not match the simplicity and flexibility of having databases focus on providing a common data structure, and the application being stored elsewhere in your favorite source control system (eg git).

joevandyk 13y ago
However once you have software development and database administration separated, the DBAs generally need access to the database in a way that does not go through your tool / software development cycle / etc. Why would this happen? Database changes should be treated just like any other code update. Furthermore third party tools like this generally want your site to be offline during database migrations. They do? Why? Furthermore you have the headache of identifying the difference between data tables and metadata tables. That is, some tables contain data that gets modified on the fly during normal use. (eg user information) Other tables contain data that is really application configuration. (eg allowed statuses for something) I don't see why this is a problem at all (or worth mentioning). The difficulty lies in keeping them synchronized with what is in your repository. This is why you use migrations/tools/etc for managing database updates. And also in providing ways to copy user information to the private instances so that devs can reproduce production problems. (Doubly so when your production problem is a performance issue that requires a copy of production data to recreate.) I'm not sure how this is related. But the general story that databases provide on these issues does not match the simplicity and flexibility of having databases focus on providing a common data structure, and the application being stored elsewhere in your favorite source control system (eg git). I have no idea why you wouldn't want the database change scripts/functions/schema to be stored in git, alongside the application code.
- nbouscal 13y ago
  > I have no idea why you wouldn't want the database change scripts/functions/schema to be stored in git, alongside the application code. I couldn't agree more. It boggles my mind how many people are resistant to storing this stuff in version control. It is code! Treat it like code! The exact same thing applies to configuration management. Your chef cookbooks should live in your git repository right next to your application code, and right next to your database code. That's the only possible way for every node in your source history graph to be functional in isolation. If your database changes independently of your version control, you simply cannot check out an arbitrary commit and expect anything to work.
- btilly 13y ago