Show HN: PugSQL, a Python Port of HugSQL

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

[Rotareti]

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

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/samuelcolvin/pydantic/

[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

[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://gitlab.com/pgjones/quart

[BerislavLopac]

Instead of Quart and asyncpg, I recommend you to take a look at Starlette [0] and databases [1].

[0] https://www.starlette.io/

[1] https://github.com/encode/databases

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

[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://github.com/artemeff/eql) for the file parser :)

[arthurbrown]

Ecto is one of the very best things about elixir from my perspective. It seems bizarre that anyone would have this great tool uniquely available to their language and want to ignore it.

[_x5tx]

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?

[phaer]

I haven't used PugSQL yet, but I like the principle because it allows to keep SQL and Python code in separate files, which makes it easier to use linters or even just proper syntax highlighters for the SQL compared to strings embedded into python or other programming languages.

[fulafel]

One clear advantage of the yesql/hugsql style is that you get to manage your SQL files in a consistent way. You're going to have migration files and stored procedure/view/etc definitions in .sql files too. This lets you export your queries as Clojure/Python functions simply but still have them live next to the rest of the SQL, for easy refactoring etc.

[Scarbutt]

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

[nicwolff]

SQLAlchemy Core is powerful but hardly complete; every real-world application I've written with it has ended up needing substantial hand-written SQL to cover the gaps. So I see a clear use case on top of SQLAlchemy to organize those queries.

[buremba]

The Java/Kotlin alternative would be `the amazing library` JDBI: 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.

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

[sametmax]

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

[nathell]

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

[ethanpil]

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.

[nrjames]

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

[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

[gmueckl]

Naming collisions for open source software are the norm rather than the exception. At some point you just have to start living with it.

[lkschubert8]

It's probably safe to call it PugSQL though.

[noir_lord]

Aye but the reason it's called pug is they got into problems because Jade was already used.

Naming is hard.

I really like pug though, with Vue SFC it's really clean.