Hacker News Reader

Comment by coleifer | Hacker News Reader

A simple Python interface ... built atop the most powerful and widely used Python ORM. This is the library equivalent of virtualization!

This is crazy, right? Let's take SQLAlchemy, all the experience and expertise that went into building it, throw that out the window and make the dumbest possible wrapper on top.

View on HN

Charles, you gotta calm down.

I love these wrappers around Core. People who hate my ORM get to use my library anyway, the community comes to me and continues to help stability and performance improvements at that level in any case. the ORM was never intended to please everybody; Core was :)

Mike, you're precious!

Core is awesome! It allows me to write complex on-demand analytical queries using a proper AST model instead of gluing together strings - basically the equivalent of my own Tableau or Looker. Most SQL libraries are the equivalent of building a parse tree by gluing together linear strings.

Especially since SQLAlchemy has sqlalchemy-core, which allow to express pretty much any regular SQL, including proprietary one and very complex report generating query, but with type-casting, data checks, code completion, linting and a uniform API no matter the dialect or project.

I mean, we are not talking about just an ORM. SQLAlchemy is damn god-like powerful.

For some perspective: https://docs.sqlalchemy.org/en/13/core/tutorial.html

This is not an ORM.

PugSQL is a Python incarnation of HugSQL, which was inspired by yesql, whose rationale you might want to read:

https://github.com/krisajenkins/yesql#rationale

SQLAlchemy-core is not an ORM either, the ORM is based on it.

You can already write raw sql with it in a separate file if needed, and make a function out of it. I can't see a benefit to adding PugSQL on top of it, unless you plan to use the SQL from several languages.

Other than that, what will happen is that you will end up writing abstraction layers on top of it anyway. Gotta validate those data. Gotta provide a unified API to the rest of the program. And you will rewrite a poorly tested, less expressive sqlalchemy-core.

Not to mention you lose the benefit of being able to create a lib that can talk to several databases, code completion, linting, etc. That are much better in Python than SQL.

So, on one hand: full power of SQL if needed, plenty of additional features when not. On the other hand... what ?

That's what I love about Clojure culture. It started as an acknowledgment of weaknesses of dominant solutions (isn't ORM an anti-pattern?) and lead to this beautiful story of open source: Yesql -> HugSQL -> PugSQL

It's interesting to watch as it clashes here with one-way-of-doing-things Python culture.

Reading the documentation and looking at examples, I think you are being a bit harsh. This project looks to me like an interesting, intuitive and useful abstraction layer for organizing a project's DB queries.

Imagine a query - `select * from users`. Now, in my webapp, the user can pass a username, in which case I want to do `select * from users where username=<some string>`.

In naive sql land, people solve this with string concatenations. Its... fine, but there is a lot of complexity to getting it right.

In sqlalchemy you can do `q = session.query(Users)` followed by `if username: q = q.filter(Users.username == mystring)`. The library handles all the concatenation and type conversions and whatnot for you.

So far as I can tell- with this you literally just write both queries and call a different one? I don't see ANY way of doing code reuse. And in my toy example that may not seem like a big deal, but I remember lots of functions where I had tens of endpoints that each were making similar queries, with a number of optional arguments to each. With sqlalchemy, we can build a "active user filter" function, and reuse it everywhere. This seems to take away that option.

It's either two queries or something like this:

select * from users where username = :username or :username is null

That can be considered slightly better than two queries or slightly worse, depending on who you ask. And it is indeed the state of query reuse if you are not using at least decently smart query builder.

My impression is that many people simply don't have the kind of requirements that call for such advanced run-time query composition.

I see this as a perfectly complementary tool to SQLAlchemy Core itself: you can write your queries in Core, reusing as much as necessary -- but there is always a couple of insanely complex queries that join several tables and subqueries and do all kind of magic to get the right report. In this case PugSQL kicks in, and you can keep your complex queries in SQL and call them from Python as needed.

I've found that most of these insanely complex analytic type queries are easy to model in Core if you treat Core as a DSL and use good programming practice (composition, separation of concerns, etc). The final queries end up being beasts, but if the Core-based pieces are modeled well they are basically equivalent to one I would have written by hand, but with the extraordinary benefit that they can be much more easily parameterized (imagine a financial report where you roll up Year-to-Date, Month-to-Date, Week-to-Date metrics, so using lots of window functions - with the proper structure it's trivial to add a further breakdown allowing you to group by any other metric). I've basically used it to create my own version of Looker.

Dataset is my favorite dumb wrapper on top of SQLAlchemy. https://dataset.readthedocs.io/en/latest/

Thanks for mentioning, I didn't know of this. A classic Python module it is, sweet and simple.

Reply on news.ycombinator.com