
Ask HN: Database-first vs. code-first approaches? - ggregoire
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.<p>My issues with this approach so far:<p>- writing SQL migrations is a slow and error-prone process (people here are Python devs (mostly junior btw), not DB&#x2F;SQL experts)<p>- reviewing SQL migrations is a slow and error-prone process (for the same reason)<p>- it&#x27;s impossible to see the model diffs on Git (each migration is a new SQL file, so we can&#x27;t compare before&#x2F;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&#x27;s slow to generate, it&#x27;s 1 more file to commit, and overall it just seems like a hack for patching a bad design choice)<p>- it&#x27;s impossible to track the history of the model diffs  (for the same reasons)<p>And I just don&#x27;t see any benefits <i>for us</i> with the database-first approach.<p>Does anyone here had a similar experience? Did you move your projects to the other approach?<p>For context, we have 1 database&#x2F;schema shared by multiple projects. So I guess we could create a new project handling the SQLAlchemy models&#x2F;migrations and import it in the other projects.
======
taffer
There are tools like sqlacodegen [1] to generate the model code from the
database and liquibase [2] for migrations and diffing.

> 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/](https://pypi.org/project/sqlacodegen/)

[2]
[https://www.liquibase.org/documentation/diff.html](https://www.liquibase.org/documentation/diff.html)

[3] [https://blog.jooq.org/2018/06/06/truth-first-or-why-you-
shou...](https://blog.jooq.org/2018/06/06/truth-first-or-why-you-should-
mostly-implement-database-first-designs/)

~~~
ggregoire
> Sounds like trouble to me. Is there a reason for this design? Normally each
> project should have its own database.

Each project is a "microservice" but they use the same data so they are
connected to the same database.

But I don't think anybody really thought deeply about it. It was probably more
like "monolithic architecture = bad, microservice architecture = good".

