4 ms·

Historyically, I've been against putting logic inside (MySQL) database since it's a bitch to version and maintain (especially cross-database-user) Maybe Postgr

Historyically, I've been against putting logic inside (MySQL) database since it's a bitch to version and maintain (especially cross-database-user) Maybe Postgres is a lot better, but for MySQL I'm staying the hell away from it

vinceguidry 11y ago
I think the idea is that you don't have to version and maintain it, your database layer changes far less frequently than your code does. Kind of like how servers used to be managed back in the day. Rebuilding one of those was a major life event, and much of your experience revolved around managing servers through scripts and whatnot, so you didn't sweat the lack of versioning much. Of course nowadays with easy cloud provisioning and tools like Docker, you can version server configurations, so you don't have to put up with it if you don't want to. But databases, I think are at that same in that you should really understand the one you're working with such that you could replicate your current usage from scratch if you really had to, but honestly the chances of you being called upon to do it are minuscule, and the costs of such are going to be borne by the organization you work for anyway.
olefoo 11y ago
It does mean you need to treat your data schema and functions as code. My short checklist for doing database centric development (postgresql centered): * Database schema is kept under version control ( essentially all the DDL for a given database is kept in one place and managed as a unit ). You can use `pg_dump -s` to extract the schema from an extant database. * data types and functions are kept in separate files under version control * the database is built and filled automatically for tests, for migrations and for upgrades. Shell script, Makefile, Chef; the tool doesn't matter so long as the process is repeatable and automatic. * data fixtures for testing are kept separate from the code ( own repo usually, since you want to be able to add edge cases and errors to the test database ). * migrations on the production database are run immediately after a backup. Nothing too fancy, just basic hygiene. But staying on top of that means you're ready to do things like build your application onto a new set of machines; or make drastic changes to how your data is structured. There are a number of tools that do much of the scutwork for you ( South for Django, ActiveRecord::Migration for Rails, etc.) but the tools are not a substitute for understanding what your database is doing.
- geon 11y ago
  > pg_dump -s I wholehartedly agree that the schema should be versioned. But the schema dump is imho unreadable. I edit it manually instead, which has its own drawbacks. Since I referr to the schema a lot, I think it is worth optimizing for readability.

vinceguidry 11y ago

I think the idea is that you don't have to version and maintain it, your database layer changes far less frequently than your code does. Kind of like how servers used to be managed back in the day. Rebuilding one of those was a major life event, and much of your experience revolved around managing servers through scripts and whatnot, so you didn't sweat the lack of versioning much. Of course nowadays with easy cloud provisioning and tools like Docker, you can version server configurations, so you don't have to put up with it if you don't want to. But databases, I think are at that same in that you should really understand the one you're working with such that you could replicate your current usage from scratch if you really had to, but honestly the chances of you being called upon to do it are minuscule, and the costs of such are going to be borne by the organization you work for anyway.

olefoo 11y ago

It does mean you need to treat your data schema and functions as code. My short checklist for doing database centric development (postgresql centered): * Database schema is kept under version control ( essentially all the DDL for a given database is kept in one place and managed as a unit ). You can use `pg_dump -s` to extract the schema from an extant database. * data types and functions are kept in separate files under version control * the database is built and filled automatically for tests, for migrations and for upgrades. Shell script, Makefile, Chef; the tool doesn't matter so long as the process is repeatable and automatic. * data fixtures for testing are kept separate from the code ( own repo usually, since you want to be able to add edge cases and errors to the test database ). * migrations on the production database are run immediately after a backup. Nothing too fancy, just basic hygiene. But staying on top of that means you're ready to do things like build your application onto a new set of machines; or make drastic changes to how your data is structured. There are a number of tools that do much of the scutwork for you ( South for Django, ActiveRecord::Migration for Rails, etc.) but the tools are not a substitute for understanding what your database is doing.

geon 11y ago
> pg_dump -s I wholehartedly agree that the schema should be versioned. But the schema dump is imho unreadable. I edit it manually instead, which has its own drawbacks. Since I referr to the schema a lot, I think it is worth optimizing for readability.