Earlier comments I wrote on ORMs:
https://news.ycombinator.com/item?id=14661391,
https://news.ycombinator.com/item?id=19853396It makes it much easier to avoid errors and tab-complete relations across the object graph.
> Some ORMs offer to ‘mirror your live DB’ into a schema. That’s insane to me. It makes any type-checking or linting impossible. You have no idea what you’re getting.
Hm, I believe the author is talking about SQLAlchemy's sqlalchmey.ext.automap: https://docs.sqlalchemy.org/en/13/orm/extensions/automap.htm.... This is a relatively new feature in SQLAlchemy, no need to use it if you don't want (but it is pretty cool).
I can't speak for everyone here, but I doubt most ORM users are using it that way.
> Your test suite will be useless because your local DB probably is getting spun up from a schema.
Most likely a test database is spun up (if it's not a sqlite in-memory db). In the case of django it'll automatically prefix test_{databasename}, run migration files, and go to town.
> If not, then you have another hard problem of doing regular prod dumps to your dev environment. Yuck.
Which leads me to another benefit of ORM, adding fixture data to simulate very intricate states is much easier to do and maintain. Between tests, most likely, transactions are being rolled back.
Since you are using python, you'll be able to use pytest fixtures: https://docs.pytest.org/en/latest/fixture.html. I really like pytest-django, but I'm not sure how well the sqlalchemy equivalent is though. Maybe that's the area you could have having issues with, since when they have bugs, they're notoriously hard to figure out (is it how I'm bootstrapping sqlalchemy? is it my models? is it my fixtures? is it a pytest bug? or the pytest extension?)
> There’s always some surprise about the connection or pool type. It’s never straightforward, it’s always badly documented, it has twice as many layers as necessary because it has to manage bespoke session tracking and table registries. Transactions are undocumented or mysterious, autocommit is implied or hidden.
I recommend Django's ORM. I've used SQLAlchemy for years and couldn't tell you the default behavior cold. I believe autocommit is off by default unless you specify otherwise (https://docs.sqlalchemy.org/en/13/orm/session_api.html, https://docs.sqlalchemy.org/en/13/core/connections.html)
> We can’t have nice things until we move our schemas out of our app languages and into declarative spec languages like SQL, proto, or even jsonschema / swagger (though I’m not a great fan of the last two).
You could have your django (or whatever is using the ORM) server / etc. run an API. What are you building exactly? graphql will get you a typed API and have other languages use it via whatever you like (OAuth2?)
You're probably going to a main programming language of some sort doing the CRUD/migrations. Why? There's normally business logic / layer stuff on top, that's another +1 having other languages access via API may be used so there isn't duplication.
Are there cases where making API wouldn't work? If that's the case you're probably well beyond the ORM questions, and probably learned to just keep ORM to the basics - simple ForeignKey relations, staying away from polymorphism / stuff that doesn't map well to other languages where you may have to access SQL.