On: Litestar is worth a look

[8organicbits]

> So if you’re going to be writing a database-backed web application in Python, and you’re not doing Django, you are almost certainly going to be using SQLAlchemy.

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.

[brokegrammer → 8organicbits]

I'm not a fan of SQLAlchemy because of the Data Mapper pattern, and abstractions that force you to still think in terms of SQL tables and expressions.

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.

[globular-toast → 8organicbits]

SQLAlchemy is just a totally different beast to Django, it has a much higher learning curve but gives you so much more power and flexibility. It's a true data mapper ORM rather than the sad Active Record pattern which starts off well and quickly gets annoying.

[adrianh → globular-toast]

I've been using the Django ORM for 20 years, and it has yet to get annoying. What's your definition of "quickly" — perhaps 25 years?

[globular-toast → adrianh]

Obviously it will depend on what you're doing and one's tolerance for things many deem to be annoying. I've encountered people with incredibly high tolerance for slow and awkward workflows but, alas, I am not one of them.

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.

[mrweasel → globular-toast]

> 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.

[jnpnj → globular-toast]

I'm curious about your experience, I like SQLAlchemy granularity and expressiveness, but after a while I found django fits the "good enough" niche very well, it's quick and not too dirty (the name wrangling for joins is funky but it's ok). What kind of queries or logic did SQLAlchemy allowed you to write that Django would make hell to reach ?

[Ralfp → jnpnj]

Tell me how to extend Django's default JOIN clause with custom AND, eg:

    SELECT \* FROM t1 LEFT JOIN t2 ON t1.id = t2.key AND t2.used_id = 213

[dvdkon → 8organicbits]

Same here. Django's was my first ORM, and at the time I didn't get all the hate directed at ORMs (except for making inefficient queries). Having used a few more ORMs since then, I still consider Django's to have found the best balance.

I 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.

[JodieBenitez → 8organicbits]

> I've preferred the Django ORM over SQLAlchemy, but I'm curious what others feel.

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.