4 ms·

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 throu

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
Use case for you based on a real example. Early Monday morning, your site goes down under load. You really want a DBA to be able to hop on it, identify that the cause is, for instance, a poorly executing query, and lock a decent query plan in place. Oracle lets DBAs do that, but I've not seen a database migration tool that knows how to understand/express that. Secondly when it comes time to update your schema, the easiest solution is to simply lock tables and do updates. However locking the table can involve a forced outage. If, however, you limit yourself to very specific kinds of updates, then you can update your schema without major outages. Now rolling forward is cheap. Rolling it back is very, very expensive. Tools generally just try to do what you asked, and don't know how to say, "Really?" If you pay attention to this, then you want to be able to just roll forward. And you want to be able to roll your database forward independently of the web server. Now you have the option of rolling the database forward, then rolling out a new version of the web server code. Should you have an emergency problem, roll the web server code back, and leave the database on the new version. (This only works if the database is "dumb".) One strategy to support this is to let the DBAs be in charge of the schema, separately from your development tree. Developers are allowed to create patches that alter the database on dev, and will submit those patches to the DBAs. If the DBAs do not object to the change, they are in charge of putting it on QA or production. (If the DBA does object, of course, it goes nowhere.) And now the pattern is that DBAs maintain the official definition of the database. DBAs are willing to apply proven patches to production, and can make QA a clone of production whenever they want. In emergencies, DBAs can fix problems directly on production. (You try to have this happen as little as possible.) All developer changes to the database schema have to be blessed by DBAs. Database patches need to be applied in production separately from and strictly before release. I've seen a number of different strategies for handling database schemas and code. This is the one that, in my opinion, worked best in practice.
- joevandyk 13y ago
  Oracle lets DBAs do that, but I've not seen a database migration tool that knows how to understand/express that. I'm assuming that Oracle lets you do this via some sort of a sql file/script? I'd think the process would be: 1. Create SQL file that locks the query plan 2. Test it 3. Check into version control 4. Using migration tool, apply to production database PostgreSQL lets you do all sorts of updates without locks, fwiw, and that's what I'm most experienced with. And all postgresql administration can be done via text files that can be checked into version control.

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

Use case for you based on a real example. Early Monday morning, your site goes down under load. You really want a DBA to be able to hop on it, identify that the cause is, for instance, a poorly executing query, and lock a decent query plan in place. Oracle lets DBAs do that, but I've not seen a database migration tool that knows how to understand/express that. Secondly when it comes time to update your schema, the easiest solution is to simply lock tables and do updates. However locking the table can involve a forced outage. If, however, you limit yourself to very specific kinds of updates, then you can update your schema without major outages. Now rolling forward is cheap. Rolling it back is very, very expensive. Tools generally just try to do what you asked, and don't know how to say, "Really?" If you pay attention to this, then you want to be able to just roll forward. And you want to be able to roll your database forward independently of the web server. Now you have the option of rolling the database forward, then rolling out a new version of the web server code. Should you have an emergency problem, roll the web server code back, and leave the database on the new version. (This only works if the database is "dumb".) One strategy to support this is to let the DBAs be in charge of the schema, separately from your development tree. Developers are allowed to create patches that alter the database on dev, and will submit those patches to the DBAs. If the DBAs do not object to the change, they are in charge of putting it on QA or production. (If the DBA does object, of course, it goes nowhere.) And now the pattern is that DBAs maintain the official definition of the database. DBAs are willing to apply proven patches to production, and can make QA a clone of production whenever they want. In emergencies, DBAs can fix problems directly on production. (You try to have this happen as little as possible.) All developer changes to the database schema have to be blessed by DBAs. Database patches need to be applied in production separately from and strictly before release. I've seen a number of different strategies for handling database schemas and code. This is the one that, in my opinion, worked best in practice.

joevandyk 13y ago
Oracle lets DBAs do that, but I've not seen a database migration tool that knows how to understand/express that. I'm assuming that Oracle lets you do this via some sort of a sql file/script? I'd think the process would be: 1. Create SQL file that locks the query plan 2. Test it 3. Check into version control 4. Using migration tool, apply to production database PostgreSQL lets you do all sorts of updates without locks, fwiw, and that's what I'm most experienced with. And all postgresql administration can be done via text files that can be checked into version control.