Yep, it's quite handy for complex reporting. An original motivation was to disentangle a 1.5k lines (SQL + corresponding python code to assemble the query) of almost identical clickhouse queries. There were two big almost similar data tables with own dictionary relations. And there is a bunch of reporting with complex grouping and filtering. Around of 20 report instances. Each report was a separate monstrous SQL.

Schema changes were quite often and reports started to rot up to the point of showing wrong data.

After refactoring it became 200 lines and allows to query more reports due to increased generality.

Haven’t seen a mention in readme, but I’ve found in the sources some nicety for INSERT/VALUES too!

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 :)