8 ms·
Show HN: PugSQL, a Python Port of HugSQL
- buremba 7y agoThe Java/Kotlin alternative would be `the amazing library` JDBI: http://jdbi.org/ http://jdbi.org/
- BossingAround 7y agoWow, 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 7y agoJDBC 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 7y agoAre you building the DB layer in Clojure and call it from Java then, or are you writing your whole app in Clojure?
- robodale 7y agoWatch your naming - pug is also a template engine for npm.
- deleted 7y ago[deleted]
- lkschubert8 7y agoIt's probably safe to call it PugSQL though.
- noir_lord 7y agoAye 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.
- mcfunley 7y agoIf you want to put sql in your templates, I can't help you
- deleted 7y ago[deleted]
- benbristow 7y agoI mean if you run a website with pages about SQL you might want to.
- gmueckl 7y agoNaming collisions for open source software are the norm rather than the exception. At some point you just have to start living with it.
- gigatexal 7y agoVery cool! Kudos to the dev(s)
- coleifer 7y agoA 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.
- sametmax 7y agoEspecially 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 https://docs.sqlalchemy.org/en/13/core/tutorial.html
- nathell 7y agoThis 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 https://github.com/krisajenkins/yesql#rationale
- sametmax 7y agoSQLAlchemy-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 ?
- ptttr 7y ago
- born2discover 7y agoInteresting. 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 7y agoLooks like sqlalchemy is just used for the "being able to handle multiple databases" part, not for its higher level abstractions.
- eshyong 7y agoFYI, you can also execute queries directly against a database in SQLAlchemy: 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()
- michelpp 7y agoYou 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 7y agoThank 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!
- phaer 7y agoI 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 7y agoNice to see Python ports of Clojure libraries. Are there others? Is there something close to spec or Plumatic Schema?
- jjwiseman 7y agoI want a Python port of instaparse!
- cjauvin 7y agoThe 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
- deleted 7y ago[deleted]
- benatkin 7y agoPerhaps 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 7y agoI 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 7y agoI really like Quart as an almost drop-in Flask, asyncio alternative.
- BerislavLopac 7y agoInstead of Quart and asyncpg, I recommend you to take a look at Starlette [0] and databases [1]. [0] https://www.starlette.io/ https://www.starlette.io/ [1] https://github.com/encode/databases https://github.com/encode/databases
- cwp 7y agoAwesome. I do the same sort of thing in Javascript and it works great. Nice to see it available in Python.
- avolcano 7y agoI 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 7y ago> 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/
- Rotareti 7y ago> 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/ https://github.com/samuelcolvin/pydantic/
- loop0 7y agoI 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.
- holtalanm 7y agoi 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 7y agoIf 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 7y agoo.0 thanks for this. reading up on the docs now.
- arthurbrown 7y agoEcto 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.
- holtalanm 7y agoecto is awesome, don't get me wrong. i really enjoy it. not saying the slightest i want to ignore it, but i simply found HugSQL to be an absolute pleasure to use when I was learning Clojure. I like ecto, but i liked HugSQL more.
- deleted 7y ago[deleted]