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

#21

Post by psanford » Wed, Sep 08, 2021, 10:07 PM UTC

justinsaccount wrote: post #14

koeng wrote: post #4

As an aside - for anyone working with databases in Go, check out https://pkg.go.dev/modernc.org/sqlite It allows drop in replacement of SQLite that is in pure Go - no CGO or anything required for compilation, while still having everything implemented from SQLite. Insert speed is a bit lacking (about ~6x slower in my experience compared to the CGO sqlite3 package), but its good enough for me.

It's not really pure go, it's transpiled using https://gitlab.com/cznic/ccgo Just about all the code looks like this: // Call this routine to record the fact that an OOM (out-of-memory) error // has happened. This routine will set db->mallocFailed, and also // temporarily disable the lookaside memory allocator and interrupt // any running VDBEs. func Xsqlite3OomFault(tls *libc.TLS, db uintptr) { /* sqlite3.c:28548:21…

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.

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

#22

Post by throwaway894345 » Wed, Sep 08, 2021, 10:19 PM UTC

psanford wrote: post #21

justinsaccount wrote: post #14

Earlier quoted context omitted.

It's not really pure go, it's transpiled using https://gitlab.com/cznic/ccgo Just about all the code looks like this: // Call this routine to record the fact that an OOM (out-of-memory) error // has happened. This routine will set db->mallocFailed, and also // temporarily disable the lookaside memory allocator and interrupt // any running VDBEs. func Xsqlite3OomFault(tls *libc.TLS, db uintptr) { /* sqlite3.c:28548:21…

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.

OP mentioned that the pure-Go version is ~6 times slower, so the cgo calling overhead is clearly made up for by C. Also, I've heard that sqlite is the rare piece of C software that is actually bulletproof, so I don't think the pure-Go version can make the usual boasts about correctness and security in this particular case.

Not needing extra external compilers is still a nice proposition, however.

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

#23

Post by joppy » Wed, Sep 08, 2021, 10:28 PM UTC

grantwu wrote: post #20

I was really really excited when I saw the title because I've been having a lot of difficulties with other Go SQL libraries, but the caveats section gives me pause. Needing to use arrays for the IN use case (see https://github.com/kyleconroy/sqlc/issues/216 ) and the bulk insert case feel like large divergences from what "idiomatic SQL" looks like. It means that you have to adjust how you write your queries. And that…

Arrays are nicer for the IN case because Postgres does not understand an empty list, i.e “WHERE foo IN ()” will error. Using the “WHERE foo = ANY(array)” works as expected with empty arrays.

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

#24

Post by donio » Wed, Sep 08, 2021, 10:35 PM 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…

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.

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

#25

Post by Sytten » Wed, Sep 08, 2021, 10:36 PM UTC

As an alternative I suggest people to look at https://github.com/go-jet/jet. I had a good experience working with it and the author is quite responsive.

It really feels like writing SQL but you are writing typesafe golang which I really enjoy doing.

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

#26

Post by pingu2 » Wed, Sep 08, 2021, 10:38 PM UTC

nickcw wrote: post #7

koeng wrote: post #4

As an aside - for anyone working with databases in Go, check out https://pkg.go.dev/modernc.org/sqlite It allows drop in replacement of SQLite that is in pure Go - no CGO or anything required for compilation, while still having everything implemented from SQLite. Insert speed is a bit lacking (about ~6x slower in my experience compared to the CGO sqlite3 package), but its good enough for me.

I hadn't realized it was now ready for general use... SQLite 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f 0 errors out of 928271 tests on 3900x Linux 64-bit little-endian Whee, I shall have to give it a go - thanks for the heads-up :-)

Swswsswwzzwwwwwwwwxw

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

#27

Post by sa46 » Wed, Sep 08, 2021, 10:40 PM UTC

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 (proteus).

- Write custom query language (YAML or other), generate SQL schema, queries, and Go query interface (xo).

- Skip generated code and use a non-type-safe query builder (squirrel, goqu).

I prefer writing SQL queries so that app logic doesn't depend on the the database table structure.

I started off with sqlc but ran into limitations with more complex queries. It's quite difficult to infer what a SQL query will output even with a proper parse tree. sqlc also didn't work with generated code.

I wrote pggen with the idea that you can just execute the query and have Postgres tell you what the output types and names will be. Here's the original design doc [1] that outlines the motivations. By comparison, sqlc starts from the parse tree, and has the complex task of computing the control flow graph for nullability and type outputs.

[1]: https://docs.google.com/document/d/1NvVKD6cyXvJLWUfqFYad76CW...

Disclaimer: author of pggen (https://github.com/jschaf/pggen), inspired by sqlc

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

#28

Post by pstuart » Wed, Sep 08, 2021, 10:40 PM 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…

Hopefully they'll get SQLite working on it soon and I'll be all over it.

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

#29

Post by bitwize » Wed, Sep 08, 2021, 10:41 PM 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…

> Why would someone author an ORM, painstakingly creating Go functions that just map to existing SQL features?

The answer to this question lies in the assumption you make in this statement:

> the one which every engineer already knows: SQL.

Not every engineer knows, or wants to learn, SQL. I've met very competent engineers, SMEs over their particular system, who were flummoxed by SQL. And many more just want to work in their preferred language. I don't like ORMs either but, like, half the reason why they exist is so the programmer can talk to the RDBMS in Java, JavaScript, etc. and not touch SQL.

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

#30

Post by Sytten » Wed, Sep 08, 2021, 10:44 PM 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…

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