How We Went All In on sqlc/pgx for Postgres and Go • Hacker News Board

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

#121

Post by noisem4ker » Thu, Sep 09, 2021, 9:43 AM UTC

donio wrote: post #24

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…

A few years ago I had spent a year working with a Go project that made heavy use of one of the (then) popular Go ORMs. Learned my lesson, never again. Magic=Bad.

What ORM was that?

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

#122

Post by xorcist » Thu, Sep 09, 2021, 10:10 AM UTC

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…

It does indeed look promising!

Can it help with migrations? Seeing it has the field definitions right there it should at least be possible.

Otherwise I can see how a system like this could become quite complex over time as the database structure changes.

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

#123

Post by Foobar8568 » Thu, Sep 09, 2021, 10:31 AM UTC

erdemozg wrote: post #112

Foobar8568 wrote: post #111

Earlier quoted context omitted.

Yes, I was about to reply to myself with a link to this issue as I couldn't see anything in official docs unless looking at the github issues. https://github.com/kyleconroy/sqlc/issues/1157

But it seems like this discussion is about the lack of support for null values in enum types in joins (not joins in general). Am I missing something?

I got it wrong because of lack of documentations and examples in the official documentation. So one would be only aware of the feature if they read the issue tracker which is dumb, joining two entities (or more) is like the first thing you want to do with a database.

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

#124

Post by JulianMorrison » Thu, Sep 09, 2021, 10:39 AM UTC

otabdeveloper4 wrote: post #120

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…

> code generation, instead of e.g. reflection "Hey man, we noticed there's not enough compiler in your compiler, so we made a second compiler for your compiler."

"Your compiler compiles A, but you also want to compile B, so we added an extra compiler to help you compile it"

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

#125

Post by doctor_eval » Thu, Sep 09, 2021, 11:01 AM UTC

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…

Yep, close to 100% of my data manipulation is done in pl/pgsql. It’s awesome. At least 50% fewer LOC, and 10-100x faster than the equivalent code written in Java or Go, due to all the round trips.

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

#126

Post by sdevonoes » Thu, Sep 09, 2021, 11:05 AM UTC

I think I'm missing something, but I don't get sqlc. Let's say I want to "get a list of authors", so in sqlc I would write:

    -- name: ListAuthors :many
    SELECT * FROM authors
    ORDER BY name;

and in Go I can then say `authors, err := queries.ListAuthors(ctx)`. This is cool. Now, if I want to "get a list of American authors" I would write:

    -- name: ListAuthorsByNationality :many
    SELECT * FROM authors
    WHERE nationality = $1;

and in Go I can then say `americanAuthors, err := queries.ListAuthorsByNationality(ctx, "American")`. Now, if I want to "get a list of American authors that are dead", I would have to write:

    -- name: ListDeadAuthorsByNationality :many
    SELECT * FROM authors
    WHERE nationality = $1 AND dead = 1;

... I like the idea of getting Go structs that represent table rows, but I don't want to keep a record of every query variation I may need to execute in Go code. I want to write in Go:

    deadAmericanAuthors, err := magic.GetAuthorsBy(Params{
         Nationality: "American",
         Dead: true
    })

without having to write manually the N potential sql queries that the above code may represent.

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

#127

Post by therein » Thu, Sep 09, 2021, 11:11 AM UTC

justinsaccount wrote: post #31

psanford wrote: post #21

Earlier quoted context omitted.

Being translated means it doesn't have the normal cgo calling overhead. It also means you can cross compile it for every platform that the Go toolchain supports without any external compilers.

Nope, note their readme says: These combinations of GOOS and GOARCH are currently supported darwin amd64, darwin arm64, freebsd amd64, linux 386, linux amd64, linux arm, linux arm64, windows amd64 and if you look at their source tree https://gitlab.com/cznic/sqlite/-/tree/master/lib you can see they have sqlite_darwin_amd64.go sqlite_darwin_arm64.go sqlite_freebsd_amd64.go sqlite_linux_386.go sqlite_linux_amd64.go sq…

That's nice and impressive as far as it goes for portability however on the other side, we have C.

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

#128

Post by ggktk » Thu, Sep 09, 2021, 11:31 AM UTC

Unfortunately this tool only does static analysis on your SQL. I prefer tools like sqlx (the Rust one), which gets types by running your queries against an actual database. It feels more bulletproof and futureproof than the approach that sqlc is taking.

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

#129

Post by Cthulhu_ » Thu, Sep 09, 2021, 11:36 AM UTC

otabdeveloper4 wrote: post #120

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…

> code generation, instead of e.g. reflection "Hey man, we noticed there's not enough compiler in your compiler, so we made a second compiler for your compiler."

Makes sense, Go can't compile SQL and vice versa.

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

#130

Post by Cthulhu_ » Thu, Sep 09, 2021, 11:37 AM UTC

xorcist wrote: post #122

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…

It does indeed look promising! Can it help with migrations? Seeing it has the field definitions right there it should at least be possible. Otherwise I can see how a system like this could become quite complex over time as the database structure changes.

Based on just this article, it looks like it needs a complete table definition to work with. Unless it queries the database for the definitive schema, but that would mean you need a database up and running at compile time.