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

71–80 of 160 posts

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

#71

Post by sa46 »

sagarm wrote: post #61
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…

I like this design! Asking the database to tell you the schema of your result does seem like the simplest, most reliable option. However it does require you to have a running database as part of your build process; normally you'd only need the database to run integration tests. Doable, but a bit painful.

Yep, that’s the main downside. pggen works out of the box with Docker under the hood if you give it some schema files. Notably, the recommended way to run sqlc also requires Docker.

I check in the generated code so I only run pggen at dev time, not build time. I do intend to move to build time codegen with Bazel but I built out tooling to launch new instances of Bazel managed Postgres in 200 ms so not that painful.

More advanced database setups can point pggen at a running instance of Postgres meaning you can bring your own database which is important to support custom extensions and advanced database hackery.

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

#72

Post by conroy »

sa46 wrote: post #71
sagarm wrote: post #61

Earlier quoted context omitted.

I like this design! Asking the database to tell you the schema of your result does seem like the simplest, most reliable option. However it does require you to have a running database as part of your build process; normally you'd only need the database to run integration tests. Doable, but a bit painful.

Yep, that’s the main downside. pggen works out of the box with Docker under the hood if you give it some schema files. Notably, the recommended way to run sqlc also requires Docker. I check in the generated code so I only run pggen at dev time, not build time. I do intend to move to build time codegen with Bazel but I built out tooling to launch new instances of Bazel managed Postgres in 200 ms so not that painful. M…

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

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

#73

Post by iudqnolq »

nicoburns wrote: post #13

I'm still waiting for a compile-to-sql language in the vein of coffeescript or typescript. It seems like there is so much that could be improved with some very simple syntax sugar: variables, expression fragments and even real basics like trailing commas.

For me that's ecto (an elixir dsl for performing queries). The single defining improvement is I can define reusable building blocks. (This is also why I like react-style frameworks over raw js).

    entry_of(record)
    |> select_basic_info()


    def entry_of(record) do
        Entry
        |> where(record_id: record.id)
    end


    def select_basic_info(query) do
        query
        |> select([entry], BasicEntey.new(entry.foo, entry.bar))
    end

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

#74

Post by smoyer »

crescentfresh wrote: post #39

> A big downside of vanilla database/sql or pgx is that SQL queries are strings What's wrong with strings? The argument in the article is that they cannot be compile-time checked, but I'm confused as to the solution to that problem ("you need to write exhaustive test coverage to verify them"). Is this saying that if they weren't strings you wouldn't need test coverage?

I like SQL queries as strings but I also like my IDE to syntax check them ... Since there are already so many links to projects in this thread I'll happily introduce fileconst which provides the best of both worlds - https://github.com/PennState/fileconst.

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

#76

Post by pphysch »

sam0x17 wrote: post #44
wvenable wrote: post #33

Earlier quoted context omitted.

There's plenty of non-trival code written in C as well. That's not a good argument for the benefit of a programming language. You can work around any limitation with enough work -- this article is a perfect example. It's an ugly solution to a simple problem but it works.

C has few enough restrictions though that you can for example make a struct and then make an array of that struct. In Go this is like rocket science.

We're never going to get to Mars if `arr := [100]myStruct` qualifies as rocket science.

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

#78

Post by earthboundkid »

sk0g wrote: post #58
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…

JetBrains IDEs help with this actually. When I was working with Go, all SQL longer than one or two lines went into the package level sql.go file, each query being a multi-line string. If you preface it with a comment like `// language=PostgreSQL`, you get syntax highlighting for PGSQL. What's more, if you have databases configured in your IDE, it tries to cross-reference the tables, columns, etc, and validates the qu…

With Go 1.16, you can embed SQL files into your Go app instead of using a multiline literal.

I actually am curious if a SQLC competitor should be written using embed + generics once generics drop in 1.18. I haven’t totally worked on what it would look like yet, but it’s an interesting idea.

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

#79

Post by earthboundkid »

benhoyt wrote: post #50
arwineap wrote: post #43

Earlier quoted context omitted.

sqlc definitely uses reflection But don't let that stop you, it looks like a nice solution and reflection isn't really all that bad anyway :)

Are you sure? Well, it uses "reflection" in a general sense of introspecting your SQL code, but not in the Go sense of using using type information at runtime via the "reflect" package. sqlc compiles your SQL at build time to statically typed, non-reflect-using functions, as shown here: https://docs.sqlc.dev/en/stable/howto/select.html

Technically, reflection is used by Go’s database Scanner interface, but it’s not what most people think of when they complain about reflection.

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

#80

Post by arwineap »

benhoyt wrote: post #50
arwineap wrote: post #43

Earlier quoted context omitted.

sqlc definitely uses reflection But don't let that stop you, it looks like a nice solution and reflection isn't really all that bad anyway :)

Are you sure? Well, it uses "reflection" in a general sense of introspecting your SQL code, but not in the Go sense of using using type information at runtime via the "reflect" package. sqlc compiles your SQL at build time to statically typed, non-reflect-using functions, as shown here: https://docs.sqlc.dev/en/stable/howto/select.html

It needs reflect in order to translate struct field names into column names at the very least
Post reply on HN