I inherited several legacy Python projects written with a database-first approach, i.e. the migrations are written in raw SQL then we update the SQLAlchemy models.
My issues with this approach so far:
- writing SQL migrations is a slow and error-prone process (people here are Python devs (mostly junior btw), not DB/SQL experts)
- reviewing SQL migrations is a slow and error-prone process (for the same reason)
- it's impossible to see the model diffs on Git (each migration is a new SQL file, so we can't compare before/after. I thought about generating schema diffs as text and committing them, allowing the reviewer to see the impacts of every migration, but the diffs are noisy, it's slow to generate, it's 1 more file to commit, and overall it just seems like a hack for patching a bad design choice)
- it's impossible to track the history of the model diffs (for the same reasons)
And I just don't see any benefits for us with the database-first approach.
Does anyone here had a similar experience? Did you move your projects to the other approach?
For context, we have 1 database/schema shared by multiple projects. So I guess we could create a new project handling the SQLAlchemy models/migrations and import it in the other projects.
> And I just don't see any benefits for us with the database-first approach.
The main motivation for this is that the database is likely to outlive the application code and you will get a cleaner database design than what the ORM would generate for you [3].
> For context, we have 1 database/schema shared by multiple projects.
Sounds like trouble to me. Is there a reason for this design? Normally each project should have its own database.
[1] https://pypi.org/project/sqlacodegen/
[2] https://www.liquibase.org/documentation/diff.html
[3] https://blog.jooq.org/2018/06/06/truth-first-or-why-you-shou...