4 ms·

We’ve started using views and friends a lot more recently (now I’ve recovered from my PTSD from a lifetime ago when I worked in a poorly managed sql server shop

by aidos 5y ago

We’ve started using views and friends a lot more recently (now I’ve recovered from my PTSD from a lifetime ago when I worked in a poorly managed sql server shop). As you point out, they’re often not first class citizens. On the flip side, we’ve just moved ours to files on disk (one per object) and hacked in some tooling to autogenerate the migrations from them (we use alembic / sqlalchemy which already does a good job here). It’s kinda the promised land. Edit code on disk, commit and review as usual.

marcus_holmes 5y ago
this. Don't write your SQL into the database. Write them as scripts (starting with "drop XYX", then "create XYZ", check them into git, and treat them as code. Migrations are for schema changes. Views and functions are not schema.
- exdsq 5y ago
  Wait. People don't do this?
  - rmbyrro 5y ago
    No, most don't
    - exdsq 5y ago
      I understand why people might hate the idea of using raw SQL when I suggest it then! I always assumed it’d be version controlled and 100% replicable (minus data) from sql scripts
      - KronisLV 5y ago
        > I always assumed it’d be version controlled and 100% replicable (minus data) from sql scripts It probably goes a bit deeper than that: Some have version controlled migrations, in both directions (both adding changes and reverting them) and have logic that allows getting to the current state from an empty DB/schema, even generating some test data (seeding) along the way if needed, e.g. in a dev/test environment. Others probably are half way there, utilizing whichever of those practices make sense, though sometimes using all of them is impossible for either social reasons or historical ones (e.g. the first migrations weren't properly versioned, so without a full data/schema import or a baseline version, it's impossible to run all of the migrations sequentially and start with an empty DB/schema. Others don't bother with any of that and handle everything in an ad hoc manner, which may work for them, but personally i'd strongly advise against that, because in cases like that touching the DB becomes a lot more risky, unless you do a backup/export before any of your own changes.
        aidos 5y ago
        I’ve worked in all those places (currently in the fully versioned model). We occasionally do a full reset using an export of the current schema as a new base and then start versioning again from there. So there’s not really a good technical reason to stop people from getting a working runnable migration system going - other than social, as you say.
        KronisLV 5y ago
        > So there’s not really a good technical reason... One that i can think of is the application depending on some set of data being present in the system, which was added ages ago and that no one has any idea about across the hundreds of tables. Furthermore, you probably can't just do a full export/import of the initial state, because everyone having to download about 2 GB of data a large portion of which is auditing related information probably also isn't all that good. Well, that's more of a technical aspect that's caused by social factors, to be honest, but probably is a blocker nonetheless. Especially if you can't convince anyone to spend a week exploring the schema and setting up this baseline migration & data, when things currently "work well enough" and when the company doesn't give you a data storage solution to keep it at (e.g. NextCloud), doesn't let you use Git LFS or something like that, anyways...
        marcus_holmes 5y ago
        the data in your system is massively more important than the code. Not spending a week exploring and completely understanding your schema is going to hurt you in the long term.
  - paulryanrogers 5y ago
    In the dark times even SCM was rare. We were ignorant cowboys strait out of school. We learned things the hard way. Then found out most such problems were solved decades before.

by aidos 5y ago

marcus_holmes 5y ago

this. Don't write your SQL into the database. Write them as scripts (starting with "drop XYX", then "create XYZ", check them into git, and treat them as code. Migrations are for schema changes. Views and functions are not schema.

exdsq 5y ago
Wait. People don't do this?
- rmbyrro 5y ago
  No, most don't
  - exdsq 5y ago
    I understand why people might hate the idea of using raw SQL when I suggest it then! I always assumed it’d be version controlled and 100% replicable (minus data) from sql scripts
    - KronisLV 5y ago
      > I always assumed it’d be version controlled and 100% replicable (minus data) from sql scripts It probably goes a bit deeper than that: Some have version controlled migrations, in both directions (both adding changes and reverting them) and have logic that allows getting to the current state from an empty DB/schema, even generating some test data (seeding) along the way if needed, e.g. in a dev/test environment. Others probably are half way there, utilizing whichever of those practices make sense, though sometimes using all of them is impossible for either social reasons or historical ones (e.g. the first migrations weren't properly versioned, so without a full data/schema import or a baseline version, it's impossible to run all of the migrations sequentially and start with an empty DB/schema. Others don't bother with any of that and handle everything in an ad hoc manner, which may work for them, but personally i'd strongly advise against that, because in cases like that touching the DB becomes a lot more risky, unless you do a backup/export before any of your own changes.
      - aidos 5y ago
        I’ve worked in all those places (currently in the fully versioned model). We occasionally do a full reset using an export of the current schema as a new base and then start versioning again from there. So there’s not really a good technical reason to stop people from getting a working runnable migration system going - other than social, as you say.
        KronisLV 5y ago
        > So there’s not really a good technical reason... One that i can think of is the application depending on some set of data being present in the system, which was added ages ago and that no one has any idea about across the hundreds of tables. Furthermore, you probably can't just do a full export/import of the initial state, because everyone having to download about 2 GB of data a large portion of which is auditing related information probably also isn't all that good. Well, that's more of a technical aspect that's caused by social factors, to be honest, but probably is a blocker nonetheless. Especially if you can't convince anyone to spend a week exploring the schema and setting up this baseline migration & data, when things currently "work well enough" and when the company doesn't give you a data storage solution to keep it at (e.g. NextCloud), doesn't let you use Git LFS or something like that, anyways...
        marcus_holmes 5y ago
        the data in your system is massively more important than the code. Not spending a week exploring and completely understanding your schema is going to hurt you in the long term.
- paulryanrogers 5y ago
  In the dark times even SCM was rare. We were ignorant cowboys strait out of school. We learned things the hard way. Then found out most such problems were solved decades before.