Show HN: PugSQL, a Python Port of HugSQL
pugsql.org
pugsql.org
I've been working on a Node+TypeScript web app on and off for the last couple years, and one thing that's always bugged me with it is database access - it uses Knex.js as a query builder. Knex is a solid (if imperfect) DSL for interacting with SQL, but as time goes on and I get more comfortable with SQL, I've started wishing I could write raw SQL instead more easily. I think an architecture like PugSQL might help bridge the gap between "passing a bunch of SQL strings around" and a query builder.
Slightly off topic further thinking - one problem I've always had with Knex and TypeScript, though, is the lack of static typing - I've been writing runtime validations for each individual query result. This has been a bit annoying to maintain at scale since I don't have very good patterns for it. With a system like PugSQL, though, I could imagine just having input and output validators for each parameterized query.
Of course, the long term dream would be to generate type definitions from the SQL files, but I assume that would require a heck of a lot of magic (e.g. "actually run a query, figure out what the schema of the result table is, and create a snapshot of that"). I haven't seen a lot of prior art in terms of "static typing of DB access without a big ol' ORM," but I'm hopeful there's some options.
Regarding GraphQL, a combination of something like Postgraphile[0] and graphql-code-generator[1] or graphqlgen[2] gets you pretty much there without writing a single line of code.
[0] https://www.graphile.org/ [1] https://github.com/dotansimha/graphql-code-generator [2] https://oss.prisma.io/graphqlgen/
I think pydantic [0] solves this problem perfectly. The objects you create with pydantic work with type systems such as mypy too, so you get all the editor support for them, if you want.
https://github.com/MagicStack/asyncpg https://gitlab.com/pgjones/quart
i remember when learning clojure, HugSQL was one my of favorite things ever. it was just...clean, simple, and awesome.
From what I've been able to gather from that website, PugSQL is a wrapper around SQLAlchemy. So my question, why do we need a wrapper around an already well established, popular, robust and very powerful library?
engine = create_engine('mysql://scott:tiger@localhost/test')
connection = engine.connect()
result = connection.execute("select username from users")
for row in result:
print("username:", row['username'])
connection.close()Secondly, you can do that with Path.read_text() and pass it to SQLalchemy-core if a query is so complex you want to (although you would lose many other architectural benefits by doing so, it is sometime necessary).
But writing your entire program like that seems very slow, SQL being verbose, and you having to write additional wrappers on top of it anyway.
As you write, in practice it sucks for SQL. Formatting is another issue, can become quite awkward in embedded code
> Secondly, you can do that with Path.read_text() and pass it to SQLalchemy-core if a query is so complex you want to
Yes, if you use one file per query. Otherwise you'd need something similar to the naming-scheme of PugSQL, just implemented by your self.
> But writing your entire program like that seems very slow, SQL being verbose, and you having to write additional wrappers on top of it anyway.
This depends entirely on your program and/or problem domain. It might be overkill for simple CRUD applications with a few INSERT/UPDATE/SELECT queries, but might make a lot of sense if you use more advanced SQL features.
Many people use the JPA libraries like Hibernate and EclipseLink. Another popular ORM is (my|i)batis. JDBI also seems to have a decent following.
After years of using ORMs, I vastly prefer interfaces like HugSQL unless I'm building a dead simple CRUD app. (I'm never building simple CRUD apps.)
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.
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
PugSQL is a Python incarnation of HugSQL, which was inspired by yesql, whose rationale you might want to read:
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 ?
It's interesting to watch as it clashes here with one-way-of-doing-things Python culture.
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.
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 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 :)
Naming is hard.
I really like pug though, with Vue SFC it's really clean.