I've preferred the Django ORM over SQLAlchemy, but I'm curious what others feel. I've gone so far as to use Django ORM for non-web projects as well. It takes a bit of work to extract though. If Django ORM had a better stand-alone story, I think more people would use it.
For example, in Django I can have a User object. I want a update the user's first name:
my_user.first_name = "Joe"
my_user.save()
In SQLALchemy:
my_user.first_name = "Joe"
session.add(my_user)
session.commit()
Users can leave comments, so I want a query that aggregates comments for each user. In Django:
users = User.objects.all().prefetch_related("comments").annotate(comment_count=Count("comments"))
Each user will now have a `comment_count` property that contains the number of comments they left.
In SQLAlchemy:
session.query(User, func.count(Comment.id).label("comment_count")) .outerjoin(User.comments) .group_by(User.id) .all()
However, each User won't have the `comment_count` property. You have to manually associate them from the returned tuple.
I feel like SQLAlchemy wants to force you to do more work, whereas the Django ORM wants to provide you with the data you asked without forcing to you think about how to actually get the data from the database nor how to optimize the query. In Django, session management is done automatically, but it SQLAlchemy, you need to be aware of the session most times.
It's good to know SQL but you can use the Django ORM without knowing it. Not the same with SQLAlchemy. Could be a pro or a con depending on the situation. Definitely a pro for me because I don't like SQL.
If I had to call out one thing it would be that you can't do tests without having a database there. This results in incredibly slow tests for even the simplest things. I don't need to test database persistence every time I'm testing some domain logic. So maybe then don't do fat models and "map" the data from Django models to a domain layer? Well, congrats, you've just manually implemented a data mapper ORM, which is what SQLAlchemy is.
It works well for simple CRUD stuff, which is really useful. But it very quickly becomes a mess and a big ball of mud when you start to do more complicated things. IMO a db access layer or web framework should be completely independent of domain logic, but Django makes that really difficult.
That is an issue/features in Django, depending on your view. You really don't get to do things the framework doesn't want. If you're trying to fight the ORM or any of the components in Django really, including the Django REST framework, you're going to lose and have a bad time.
There are certainly reason why you'd want to separate domain logic from the database access, but then Django isn't what you want. You're also going to miss out many of the things that makes Django easy to work with, like getting most CRUD operations for free.
SELECT \* FROM t1 LEFT JOIN t2 ON t1.id = t2.key AND t2.used_id = 213I find the prevailing model of having DB rows mapped 1:1 to objects in memory and syncing changes automatically to be much more trouble than it's worth, sadly most ORMs seem to use it.
Same here... better API IMO, just the right amount of abstraction (ActiveRecord-like has been fine for all the projects I was involved in) and plenty of escape hatches when needed.