Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How We Went All In on sqlc/pgx for Postgres and Go

brandur.org

Post reply on HN
Print view View original

91–100 of 160 posts

Re: How We Went All In on sqlc/pgx for Postgres and Go

#91

Post by kgeist »

sam0x17 wrote: post #11

> However, without generics, Go’s type system can only offer so much I was reading the whole article waiting to see this line, and the article did not disappoint. This is still the main reason I will stick with Rust or Crystal (depending on the use-case) and avoid Go if I can for the foreseeable future. Generics are just a must these days for non-trivial software projects. It's a shame too because Go has so much prom…

>This is still the main reason I will stick with Rust or Crystal (depending on the use-case) and avoid Go if I can for the foreseeable future

Generics are to be added to Go 1.18 (Feb 2022)

Re: How We Went All In on sqlc/pgx for Postgres and Go

#93

Post by paraph1n »

sa46 wrote: post #27

I agree whole-heartedly that writing SQL feels right. Broadly speaking, you can take the following approaches to mapping database queries to Go code: - Write SQL queries, parse the SQL, generate Go from the queries (sqlc, pggen). - Write SQL schema files, parse the SQL schema, generate active records based on the tables (gorm) - Write Go structs, generate SQL schema from the structs, and use a custom query DSL (prote…

It seems you forgot sqlboiler [1] which is something like "write SQL schema, parse SQL schema, generate Go structs and functions to build queries using SQL primitives". Not quite like generating activerecords, I think.

[1]: https://github.com/volatiletech/sqlboiler

Re: How We Went All In on sqlc/pgx for Postgres and Go

#94

Post by conroy »

sa46 wrote: post #89
conroy wrote: post #72

Earlier quoted context omitted.

> Notably, the recommended way to run sqlc also requires Docker. This isn't accurate. sqlc is a self-contained binary and does not have any dependencies. Docker is one of the many ways to install and run it, but it is not required. (author of sqlc)

Oh hey! Apologies, not trying to throw shade. Last time I used sqlc was a while ago and I ran it in docker.

No worries! Just wanted to make sure people didn't think Docker was a necessity.

Re: How We Went All In on sqlc/pgx for Postgres and Go

#95

Post by conroy »

rapnie wrote: post #90
conroy wrote: post #32

Author of sqlc here. Just wanted to say thanks to everyone in this thread. It's been a really fun project to work on the last two years. Excited to get to work on adding support for more databases and programming languages.

Thanks a lot for this great project. I looked in the issues for Sqlite support and saw the merge of PR to "Add three new experimental engines, including SQLite" [0] and there's major architecture changes involved. That merge was 1.5 yr ago though, and I am curious what the plans are to take that further. [0] https://github.com/kyleconroy/sqlc/pull/331

I haven't written up a public roadmap yet as I'm still focused on improving the MySQL and PostgreSQL support. While there is technically a SQLite parser in the main tree, it's substantially lower quality than the others. This is due to the fact that it's generated using Bison and not used by any else in production.

SQLite uses a custom parser generator called lemon[0] to parse SQL queries. Sadly that parser is deeply entwined with SQLite itself; it's not trivial to extract a full AST.

My current plan (still a work-in-progress and by no means final) is to use sqlparser-rs[1] via wasmtime. The AST produced by this crate is very high quality and it supports multiple dialects of SQL.

[0] https://www.sqlite.org/lemon.html [1] https://github.com/sqlparser-rs/sqlparser-rs

Re: How We Went All In on sqlc/pgx for Postgres and Go

#96

Post by conroy »

ethanpailes wrote: post #56

I'm a big fan of the database first code generator approach to talking to an SQL database, so much so that I wrote pggen[1] (not to be confused with pggen[2], as far as I can tell a sqlc fork, which I just recently learned about). I'm a really big partisan of this approach, but I think I'd like to play the devil's advocate here and lay out some of the weaknesses of both a database first approach in general and sqlc i…

Really appreciate the in-depth comment. Also, thanks for open-sourcing pggen. I love have more projects in this space. I'll have to take a look at how it works.

I did want to address the last point in your comment.

> This works great until you have a query like `SELECT * FROM foos WHERE field = $1`

When sqlc sees a query with a *, it rewrites the query in the generated code to have explicit column references. For example, if you have an authors table with three columns (id, name, bio), the following query:

  SELECT * FROM authors WHERE name = $1;
will have the * replaced in the final output.

  SELECT id, name, bio FROM authors WHERE name = $1;
You can see it in action here: https://play.sqlc.dev/p/2ea889b6d14ae7a91afdcdf4eebe7d100408...

Re: How We Went All In on sqlc/pgx for Postgres and Go

#97

Post by marcus_holmes »

EB66 wrote: post #57
Sytten wrote: post #30

Earlier quoted context omitted.

Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular. Things like proper syntax highlight or type safety (I remain to be convinced that sqlc can really check the validity at compile, usually it only works in specific basic cases). You just have to choose wisely your tools for sure, but most of the code you write needs to be rewritten anyway every X…

> Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular. At least when it comes to Postgres, I don't understand why more developers don't create their own user-defined functions with PL/pgSQL. It's very a robust and powerful procedural language. In my opinion, ORM's like SQLAlchemy add a completely unnecessary layer of abstraction. ORMs might be con…

I did this on a recent project, and it worked really well.

I had each function definition in its own .sql file, with a preceding "drop function" call, and a Makefile clause to run them all. Which meant managing versions was easy (coupled with migration .sql files). I also got to find out if any of my SQL was broken right up front, and testing the SQL was simple - call the function and check the return.

I also defined views for return types, so mapping the return values to the structs in the Go code was easy (yes it's boilerplate, but it really is not as painful as the author makes out). Query functions always returned the relevant view type (or a set of them).

The author's approach seems to go to great lengths to avoid a relatively small amount of boilerplate.

Re: How We Went All In on sqlc/pgx for Postgres and Go

#98

Post by baq »

EB66 wrote: post #57
Sytten wrote: post #30

Earlier quoted context omitted.

Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular. Things like proper syntax highlight or type safety (I remain to be convinced that sqlc can really check the validity at compile, usually it only works in specific basic cases). You just have to choose wisely your tools for sure, but most of the code you write needs to be rewritten anyway every X…

> Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular. At least when it comes to Postgres, I don't understand why more developers don't create their own user-defined functions with PL/pgSQL. It's very a robust and powerful procedural language. In my opinion, ORM's like SQLAlchemy add a completely unnecessary layer of abstraction. ORMs might be con…

I’d love to hear how you handle code coverage, dynamic queries (custom filters in reports, etc.), live schema migrations (we aren’t brave and don’t do that at all - and don’t use functions either).

I’m coming from a sqlalchemy background if that helps to set the context.

Re: How We Went All In on sqlc/pgx for Postgres and Go

#99

Post by mrweasel »

Sytten wrote: post #30
fprog wrote: post #12

From the article: > I’ve largely covered sqlc’s objective benefits and features, but more subjectively, it just feels good and fast to work with. Like Go itself, the tool’s working for you instead of against you, and giving you an easy way to get work done without wrestling with the computer all day. I've been meaning to write a blog post about sqlc myself, and when I get to it, I'll probably quote this line. sqlc is…

Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular. Things like proper syntax highlight or type safety (I remain to be convinced that sqlc can really check the validity at compile, usually it only works in specific basic cases). You just have to choose wisely your tools for sure, but most of the code you write needs to be rewritten anyway every X…

> Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular.

I believe that you're partly right. In my experience there's also a large number of developers who simply have no SQL training, and don't actually care to learn.

We've frequently have customers who complain about the performance of managed database (either managed by us, or a cloud provider). When we look at the queries it's clear that they use a ORM, without giving the schema it will generate much thought. It can be extremely hard to help make optimizations, because you need to figure out how to wrangle something like Hibernate into generating efficient queries and schemas, while not breaking the object model for the developers.

For my own Go projects I normally just stick to sqlx. I like that I can design the schema the way I need, and just create the queries that will map into my structs. Then it's just updates that are annoying.

Post reply on HN