BTW, if you are interested there is an application of sqlbind in a non reporting context (https://github.com/baverman/wadwise/blob/main/wadwise/model....)

It's a small app with an attempt (and a goal) to model persistence without ORM. I think it suits quite well, could be fully type hinted (with some raw force though) and somewhat less verbose in this particular context.

But also I see how it could be a maintenance hell for a medium/large scale apps.

Yeah! With SQLAlchemy as a query builder I can easily find all usages of a particular column with Find Usages action in PyCharm. With strings it’s not as easy, and some columns are not really greppable (id, author_id, user_id, etc).

Also, haven’t seen how sqlbind handles optional JOINs and especially optional LATERAL.

In this case it's hard to make something fancy :)

    from sqlbind import WHERE, FIELDS, join_fragments

    q = Q()  # a QueryParams factory
    fields = []
    joins = []
    filters = []

    if some_condition:
        fields.append('sub_table.field')
        joins.append('INNER JOIN sub_table ON (subid)')
        filters.append(q.sub_table.date > since)

    sql = f'''\
    SELECT {FIELDS('table.field', *fields)}
    FROM table
    {join_fragments(' ', joins)}
    {WHERE(*filters)}
    '''

Personally I prefer this explicitness to ORM for complex queries which include recursive or multiple joins to the same table.

But I agree ORM shines with simple joins especially if models include proper relationship and there is no need to specify 'ON' condition every time.

Whenever we do recursive, or really long queries (not often), we’re dropping to basically raw SQL with sqlalchemy.text(). It’s really a lot to keep it wholly in SQLAlchemy: write a complex query in SQL, convert to SQLA, and remember how all the moving parts should be connected.

Should be much easier now with LLMs, though :)