This sort of thing hasn't really done much to make me like ORMs.
It seems like a lot of code to generate the tables in the first place and you STILL need to read the output scripts just to ensure the ORM isn't generating some garbage you didn't want.
That seems like a lot of extra effort when a simple migration service (such as liquibase) could do the same work running SQL directly. No question on "which indexes are getting created and why". No deep knowledge of Django interactions with sql. Instead, it's just directly running the SQL you want to run.
I do read my migration scripts generated from an ORM to make sure my source code is correct.
Liquibase starts with "Write your database change code in your preferred authoring tool in SQL, YAML, JSON, or XML." So instead of just having my ORM generate that and I just have to read them to ensure correctness, I have to manually write change scripts instead? I don't see how that's is comparable.
Liquibase could certainly come in after I have some SQL scripts generated from my ORM and do whatever it does.
I would say automatic migration generation isn’t a necessary or particularly important part of an ORM. ORMs are there to map your database relational objects to your client language’s objects.
I think the person you're replying to is arguing for using some sort of database migration library without using an ORM library. It's the same position I came to recently.
Yes but they seem to have switched because they didn’t like ORM-generated migration code. I think that’s a bad reason to switch because it wasn’t an important part of ORMs in the first place. Basically, I want to know why they were even using ORMs before.
I don’t want to go without an ORM because I’ll end up making one ad-hoc anyway. I’m not going to do work on raw tuples in my application code.
Most(?) devs nowadays are introduced to database migration tools as a DX feature.
"Wow, 1-2 command and my app and database are in sync!"
In reality, migration tools are 100% about data loss prevention.
If you do not care about data loss, updating your schema is trivial, just drop everything and create. Dev environments should be stateless anyways, using separate data "fixtures" when needed.
Data loss itself is a highly nuanced topic. Some data is replaceable, some might be protected in a separate store. So I agree that ORMs should challenge the assumption that automatic migration tools need to be part of their kitchen sink.
I like that they provide the basic structure of how to apply yet unseen migrations. But they don’t need to generate the SQL at all. You quickly learn to never trust the generated code. It always needs to be manually reviewed.
I’ve done a lot of interviewing and I’ve discovered that many devs (even experienced ones) don’t understand the difference between indexes and foreign keys.
My assumption is that people have used orms that automatically add the index for you when you create a relationship so they just conflate them all. Often they’ll say that a foreign key is needed to improve the performance and when you dig into it, their mental model is all wrong. The sense they have is that the other table gets some sort of relationship array structure to make lookups fast.
It’s an interesting phenomenon of the abstraction.
Don’t get me wrong, I love sqlalchemy and alembic but probably because I understand what’s happening underneath so I know the right way to hold it so things are efficient and migrations are safe.
Is it? In Postgres, all FK references must be to a column with a PK or unique constraint or part of another index. Additionally, Postgres and Maria (maybe all SQL?) automatically create indexes for PKs and unique constraints. There's a high likelihood that a foreign key is already indexed _in the other table_.
Generally, I agree with your statement. Adding a FK won't magically improve performance or create useful indices. But, the presence of a FK or refactoring to support a FK does (tangentially) point back to that index.
Huh, that's interesting. Mixing indexes and FKs is a major conceptual error.
FWIW, I've also asked everyone I've interviewed in the past decade about indexes and FKs. Most folks I've talked to seem to understand FKs. They're often fuzzier on the details of indexes, but I don't recall anyone conflating the two.
Very strange if you ask me and disturbing. I don't know if I'd let such a dev touch a database. Of course nowadays we just vibe code and YOLO everything, but still. This is making me feel old.
This sort of thing hasn't really done much to make me like ORMs.
It seems like a lot of code to generate the tables in the first place and you STILL need to read the output scripts just to ensure the ORM isn't generating some garbage you didn't want.
That seems like a lot of extra effort when a simple migration service (such as liquibase) could do the same work running SQL directly. No question on "which indexes are getting created and why". No deep knowledge of Django interactions with sql. Instead, it's just directly running the SQL you want to run.
I do read my migration scripts generated from an ORM to make sure my source code is correct.
Liquibase starts with "Write your database change code in your preferred authoring tool in SQL, YAML, JSON, or XML." So instead of just having my ORM generate that and I just have to read them to ensure correctness, I have to manually write change scripts instead? I don't see how that's is comparable.
Liquibase could certainly come in after I have some SQL scripts generated from my ORM and do whatever it does.
I would say automatic migration generation isn’t a necessary or particularly important part of an ORM. ORMs are there to map your database relational objects to your client language’s objects.
I think the person you're replying to is arguing for using some sort of database migration library without using an ORM library. It's the same position I came to recently.
Yes but they seem to have switched because they didn’t like ORM-generated migration code. I think that’s a bad reason to switch because it wasn’t an important part of ORMs in the first place. Basically, I want to know why they were even using ORMs before.
I don’t want to go without an ORM because I’ll end up making one ad-hoc anyway. I’m not going to do work on raw tuples in my application code.
I'd call it an anti-feature for most long-lived projects that will end up needing migrations through its lifetime.
I go the liquibase route for migrations, and just use the mapping portion of any ORM.
Most(?) devs nowadays are introduced to database migration tools as a DX feature.
"Wow, 1-2 command and my app and database are in sync!"
In reality, migration tools are 100% about data loss prevention.
If you do not care about data loss, updating your schema is trivial, just drop everything and create. Dev environments should be stateless anyways, using separate data "fixtures" when needed.
Data loss itself is a highly nuanced topic. Some data is replaceable, some might be protected in a separate store. So I agree that ORMs should challenge the assumption that automatic migration tools need to be part of their kitchen sink.
I like that they provide the basic structure of how to apply yet unseen migrations. But they don’t need to generate the SQL at all. You quickly learn to never trust the generated code. It always needs to be manually reviewed.
I’ve done a lot of interviewing and I’ve discovered that many devs (even experienced ones) don’t understand the difference between indexes and foreign keys.
My assumption is that people have used orms that automatically add the index for you when you create a relationship so they just conflate them all. Often they’ll say that a foreign key is needed to improve the performance and when you dig into it, their mental model is all wrong. The sense they have is that the other table gets some sort of relationship array structure to make lookups fast.
It’s an interesting phenomenon of the abstraction.
Don’t get me wrong, I love sqlalchemy and alembic but probably because I understand what’s happening underneath so I know the right way to hold it so things are efficient and migrations are safe.
> their mental model is all wrong.
Is it? In Postgres, all FK references must be to a column with a PK or unique constraint or part of another index. Additionally, Postgres and Maria (maybe all SQL?) automatically create indexes for PKs and unique constraints. There's a high likelihood that a foreign key is already indexed _in the other table_.
Generally, I agree with your statement. Adding a FK won't magically improve performance or create useful indices. But, the presence of a FK or refactoring to support a FK does (tangentially) point back to that index.
By definition, a FK has to reference a PK in the “parent”.
Huh, that's interesting. Mixing indexes and FKs is a major conceptual error.
FWIW, I've also asked everyone I've interviewed in the past decade about indexes and FKs. Most folks I've talked to seem to understand FKs. They're often fuzzier on the details of indexes, but I don't recall anyone conflating the two.
Very strange if you ask me and disturbing. I don't know if I'd let such a dev touch a database. Of course nowadays we just vibe code and YOLO everything, but still. This is making me feel old.
> Django will implicitly add an index on a ForeignKey field unless explicitly stated otherwise.
This is nice to know if you're using Django, but as important to note is that neither Postgres nor SQLAlchemy / Alembic will do this automatically.
How can we determine if an index can be satisfied by a constraint index?
For example, does the FK need to be the first field in a unique together?