Hmm.. Sounds like a bug. Why is this not the same value for a foreign key?
If it's a bug, sounds like a "wontfix" to me.
Now perhaps some ORM's prefer to be thinner, to provide more footguns via a leaky abstraction that mixes implementation details with the object mapping. I don't think those are good implementaations.
Or you can just consider user_id to be a reference pointer that is part of foo, while user.id is an attribute of user. Totally different things and I am glad that the distinction is there.
To me, it seems like the (hypothetical?) implementation you're talking about is much more leaky and footgun-y than the more straightforward ("thinner", in your words) version. In order for foo.user.id to not execute a new query, foo.user would have to return some sort of proxy object that only fetched the user row when you tried to access a field that hasn't been loaded. That's way more magic than the more obvious solution—which is to load the row when you access the related object—and could easily cause more problems than it solves in the long run when you need to debug very specific queries.
Furthermore, how is going out of your way to hide a field that exists in the database (user_id) not the definition of a leaky abstraction? What purpose does it serve to direct you through an unnecessary layer if all you need is the ID?
A similar concept called covered queries exists whereby you index a table by foreign key, and a few additional columns that you do not expect to use in join conditions, but you do expect to frequently retrieve. Depending on your database, requests for only columns in the index (some being the join condition, some subsequent columns being in the set of popular additional columns) means faster access to those popular columns only. In the context of ORMs, you would need to do something to avoid a default behavior of "select *" in order to exploit this index.
Not necessarily, it can be overridden: "id" is only the default for models that haven't explicitly been given a field with the "primary_key" kwarg (common on legacy tables where the primary key column might for example be "user_id").
The alias guaranteed to be the same value is ".pk", and I'm not sure what django does if you try to create a column named "pk" that isn't the primary key.
Django does provide relatively easy ways to get over the N+1 issue, though. If you do Book.objects.select_related('user'), only one query is made.
You still need to understand a minimum of SQL and databases, and usually those that complain about the ORM are the ones that expect it to be a "sufficiently advanced compiler", but it has matured so much that nowadays the developers consider a *bug* every time the answer to How do I do this query X? involves something along the lines of use .extra or raw sql.
Even though Django has come a long way, I greatly prefer ORMs like SQLAlchemy and Ecto that map more closely to the SQL query I'm trying to write.
`user` would be a property defined as a User object on the Book model, so accessing `book.user` will cause the framework to fetch the entire user model (even if we then only fetch the id).
On the other hand, `book.user_id` is the auto-generated database column, generated to make the above property definition possible. But since this `user_id` is directly defined on the book object, there is no need to query the user table.