3 ms·

I don't think migrations (at least as done by Django et al.) solve it - you want a declarative source of truth for what the schema looks like today, not a chain

by OJFord 4y ago

I don't think migrations (at least as done by Django et al.) solve it - you want a declarative source of truth for what the schema looks like today, not a chain of changes that only tell you that after computing the combined effect. Even if they just created a generated file of the final schema, that sat in version control and errored the makemigrations check (just like a missing migration) if it was out of sync, that would be a significant improvement IMO. But I think the Django maintainers at least would say they want the ORM DSL to be that. (But it's way too incomplete, you'd be limited to a tiny subset of postgres, and even then you have to be on board with that being a reasonable description of your schema, not wanting the actual db schema anywhere.)

hermanradtke 4y ago
> you want a declarative source of truth for what the schema looks like today, not a chain of changes that only tell you that after computing the combined effect Applying a series of migrations to get a final db schema is not much different than a version control system like git.
- heipei 4y ago
  It’s completely different as git does not store deltas but snapshots of whole files and file trees.
- OJFord 4y ago
  Ok, so how do I checkout a version and view the file/schema tree? If you like, you can view my comment above as saying 'if they included that tooling, not just the similar tree of changes stored, it would be better'. How to get from one state to the next is interesting to the computer, not to me (not after I've initially written it/done it in prod anyway), I'm interested in the result, where do we stand after all of the migrations (currently on disk having checked out whatever).
  - simonw 4y ago
    You checkout that version, then run the migrations from scratch against a fresh database. In Django that's "./mange.py migrate".
    - OJFord 4y ago
      And then inspect the db, exactly. In other words you can't, there is no declarative 'current state' checked in or provided by the migration tooling.
      - simonw 4y ago
        I don't understand. What are you looking for here? If you want a plain text SQL file to look at you can have that with a bit of extra automation - for example, every time you tag a release you could have a script that runs "./manage.py migrate" against that checkout and then dumps the schema out to a file somewhere (an asset attached to the release on GitHub as one example).
        OJFord 4y ago
        That's pretty much what I was describing in my initial reply to your top-level comment that would be an improvement, just not built in. Ideally though I'd like a tool with first class declarative schema as the source of truth - migrations that Django can correctly autogenerate don't need to concern me at all; if I need to vary them or resolve some ambiguity in a possible path (renamed a column vs. dropped and added one, for a simple example) then there can be some way of specifying that migration. Essentially as Django targets its models as the intended result of migrations (and complains of a missing migration if they don't get there, etc.) I'd prefer instead to target SQL schema. Still check ORM consistency - but against the schema, not the migrations of it. Starting from scratch it seems obvious that you'd have the schema, and the mapping into python models. Django does away with the former by inference, but in so doing limits its scope to the subset of possibly desired SQL that it understands. If you have SQL and Python, then you can have DSL for the understood subset without losing migrations, tracking in a single place, etc. of the rest. You could also give Django ownership of the entire database/schema, so that any manual experiments/adjustments would be blown away on migration, they'd have to be documented in code to be persisted.
simonw 4y ago
If you really, really need to be able to see a SQL schema representing the current state, a cheap trick is to run an automation on every deploy that snapshots the schema and writes it to a GitHub repository. I do a version of that for my own (Django-powered) blog here: https://github.com/simonw/simonwillisonblog-backup/blob/main/simonwillisonblog/blog_entry.metadata.json https://github.com/simonw/simonwillisonblog-backup/blob/main...
hamandcheese 4y ago
This comes up a lot, and I'm always surprised that more people don't know about Flyway and "repeatable migrations": https://flywaydb.org/documentation/tutorials/repeatable https://flywaydb.org/documentation/tutorials/repeatable > Repeatable migrations are very useful for managing database objects whose definition can then simply be maintained in a single file in version control. Instead of being run just once, they are (re-)applied every time their checksum changes.
Wicher 4y ago
One of my pet projects addresses these points you raise about Django + migrations, have a look: https://pypi.org/project/DBSamizdat https://pypi.org/project/DBSamizdat In a nutshell it allows you to keep DB functions, triggers and views around as Python classes, so you can version them together with the rest of your application code. The DB state gets updated for you (in the right dependency order) whenever you change them. It can also run without Django.
- adammarples 4y ago
  This is exactly what I would use dbt for
michaelsalim 4y ago
Had a thought recently. What's stopping someone from separating table migrations from others like functions/triggers? The standard tables can use the standard process while triggers, etc are run declaratively. When you change something, a tool could simply tear everything down and rebuild it. No need to worry about data since those are handled separately. Would performance be a concern? Or is there something I'm missing?

by OJFord 4y ago

hermanradtke 4y ago

> you want a declarative source of truth for what the schema looks like today, not a chain of changes that only tell you that after computing the combined effect Applying a series of migrations to get a final db schema is not much different than a version control system like git.

heipei 4y ago
It’s completely different as git does not store deltas but snapshots of whole files and file trees.
OJFord 4y ago
Ok, so how do I checkout a version and view the file/schema tree? If you like, you can view my comment above as saying 'if they included that tooling, not just the similar tree of changes stored, it would be better'. How to get from one state to the next is interesting to the computer, not to me (not after I've initially written it/done it in prod anyway), I'm interested in the result, where do we stand after all of the migrations (currently on disk having checked out whatever).
- simonw 4y ago
  You checkout that version, then run the migrations from scratch against a fresh database. In Django that's "./mange.py migrate".
  - OJFord 4y ago
    And then inspect the db, exactly. In other words you can't, there is no declarative 'current state' checked in or provided by the migration tooling.
    - simonw 4y ago
      I don't understand. What are you looking for here? If you want a plain text SQL file to look at you can have that with a bit of extra automation - for example, every time you tag a release you could have a script that runs "./manage.py migrate" against that checkout and then dumps the schema out to a file somewhere (an asset attached to the release on GitHub as one example).
      - OJFord 4y ago
        That's pretty much what I was describing in my initial reply to your top-level comment that would be an improvement, just not built in. Ideally though I'd like a tool with first class declarative schema as the source of truth - migrations that Django can correctly autogenerate don't need to concern me at all; if I need to vary them or resolve some ambiguity in a possible path (renamed a column vs. dropped and added one, for a simple example) then there can be some way of specifying that migration. Essentially as Django targets its models as the intended result of migrations (and complains of a missing migration if they don't get there, etc.) I'd prefer instead to target SQL schema. Still check ORM consistency - but against the schema, not the migrations of it. Starting from scratch it seems obvious that you'd have the schema, and the mapping into python models. Django does away with the former by inference, but in so doing limits its scope to the subset of possibly desired SQL that it understands. If you have SQL and Python, then you can have DSL for the understood subset without losing migrations, tracking in a single place, etc. of the rest. You could also give Django ownership of the entire database/schema, so that any manual experiments/adjustments would be blown away on migration, they'd have to be documented in code to be persisted.

simonw 4y ago

If you really, really need to be able to see a SQL schema representing the current state, a cheap trick is to run an automation on every deploy that snapshots the schema and writes it to a GitHub repository. I do a version of that for my own (Django-powered) blog here: https://github.com/simonw/simonwillisonblog-backup/blob/main/simonwillisonblog/blog_entry.metadata.json https://github.com/simonw/simonwillisonblog-backup/blob/main...

hamandcheese 4y ago

This comes up a lot, and I'm always surprised that more people don't know about Flyway and "repeatable migrations": https://flywaydb.org/documentation/tutorials/repeatable https://flywaydb.org/documentation/tutorials/repeatable > Repeatable migrations are very useful for managing database objects whose definition can then simply be maintained in a single file in version control. Instead of being run just once, they are (re-)applied every time their checksum changes.

Wicher 4y ago

One of my pet projects addresses these points you raise about Django + migrations, have a look: https://pypi.org/project/DBSamizdat https://pypi.org/project/DBSamizdat In a nutshell it allows you to keep DB functions, triggers and views around as Python classes, so you can version them together with the rest of your application code. The DB state gets updated for you (in the right dependency order) whenever you change them. It can also run without Django.

adammarples 4y ago
This is exactly what I would use dbt for

michaelsalim 4y ago

Had a thought recently. What's stopping someone from separating table migrations from others like functions/triggers? The standard tables can use the standard process while triggers, etc are run declaratively. When you change something, a tool could simply tear everything down and rebuild it. No need to worry about data since those are handled separately. Would performance be a concern? Or is there something I'm missing?