
Show HN: PugSQL, a Python Port of HugSQL - mcfunley
https://pugsql.org
======
avolcano
I really like the idea of this! Potentially quite helpful for the GraphQL
space, I think, since these query files could map 1:1 with resolvers (if I
understand GraphQL correctly).

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.

~~~
petetnt
> 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,"

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/](https://www.graphile.org/) [1]
[https://github.com/dotansimha/graphql-code-
generator](https://github.com/dotansimha/graphql-code-generator) [2]
[https://oss.prisma.io/graphqlgen/](https://oss.prisma.io/graphqlgen/)

------
cjauvin
The context seems relevant to plug my own take at this "problem" (aka. finding
an alternative to a full-blown Python ORM), which involves talking to Postgres
via only builtin data structures:

[https://github.com/cjauvin/little_pger](https://github.com/cjauvin/little_pger)

------
benatkin
Perhaps slightly unrelated: I'm considering moving to asyncpg and using quart,
which is a port of flask to async python. What I wonder if it's time to start
using async python, and if these libraries are mature enough. If so, I hope
libraries like this and little_pger will switch to it or support it!

[https://github.com/MagicStack/asyncpg](https://github.com/MagicStack/asyncpg)
[https://gitlab.com/pgjones/quart](https://gitlab.com/pgjones/quart)

~~~
linsomniac
I haven't yet used this model, but a previous HN discussion months ago brought
Quart and asyncpg to my attention and my memory was the discussion was very
favorable.

~~~
bpicolo
I really like Quart as an almost drop-in Flask, asyncio alternative.

------
holtalanm
i really really want to work on porting HugSQL to Elixir. debating starting on
that for my next side project.

i remember when learning clojure, HugSQL was one my of favorite things ever.
it was just...clean, simple, and awesome.

~~~
kungfooguru
If you do, please use
[https://hex.pm/packages/eql](https://hex.pm/packages/eql)
([https://github.com/artemeff/eql](https://github.com/artemeff/eql)) for the
file parser :)

~~~
holtalanm
o.0 thanks for this. reading up on the docs now.

------
loop0
I started the same project with the same name a few months ago, I’m happy to
see someone went farther than I did. Congrats on this project, I’m testing
later. HugSQL achieves the right balance between orm and boilerplate code.

------
fulafel
Nice to see Python ports of Clojure libraries. Are there others? Is there
something close to spec or Plumatic Schema?

~~~
jjwiseman
I want a Python port of instaparse!

------
born2discover
Interesting. But what a potential use case for this? I mean what makes it
stand out when put side by side with SQLAlchemy? Does it do anything
differently?

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?

~~~
Scarbutt
Looks like sqlalchemy is just used for the "being able to handle multiple
databases" part, not for its higher level abstractions.

~~~
michelpp
You may of course already be aware of this but it's worth pointing out. To
many, SQLAlchemy is an ORM, but the SA developers very purposefully made the
ORM layer completely optional. The SQLAlchamy-Core layer can be used to
functionally compose queries without mapping types at all, and below that, SA
can be used as an abstract but featureful generic client for executing just
raw SQL.

~~~
Drdrdrq
Thank you (and sibling for the example), I was _not_ aware of that. I did
check SA out, but missed this somehow, so I'm using psycopg2 directly now...
But if I gain portability to other DBs for low effort, then I'm all for it.
Thanks!

------
buremba
The Java/Kotlin alternative would be `the amazing library` JDBI:
[http://jdbi.org/](http://jdbi.org/)

~~~
BossingAround
Wow, this looks really good. Is there a different de-facto standard library
for connecting to a DB from Java other than Hibernate? After using Hibernate
for one of my extremely simple projects, I felt like I was using a tank to
kill mosquitoes.

~~~
encloser
JDBC is the low level connection standard. Everything I am aware of is built
ontop of JDBC.

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

~~~
BossingAround
Are you building the DB layer in Clojure and call it from Java then, or are
you writing your whole app in Clojure?

------
gigatexal
Very cool! Kudos to the dev(s)

------
cwp
Awesome. I do the same sort of thing in Javascript and it works great. Nice to
see it available in Python.

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

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

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

------
robodale
Watch your naming - pug is also a template engine for npm.

~~~
mcfunley
If you want to put sql in your templates, I can't help you

~~~
benbristow
I mean if you run a website with pages about SQL you might want to.

