[HN Gopher] How We Went All In on sqlc/pgx for Postgres and Go
___________________________________________________________________
How We Went All In on sqlc/pgx for Postgres and Go
Author : conroy
Score : 104 points
Date : 2021-09-08 19:51 UTC (3 hours ago)
(HTM) web link (brandur.org)
(TXT) w3m dump (brandur.org)
| nicoburns wrote:
| 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.
| Sytten wrote:
| 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.
| scrubs wrote:
| Talk about JIT on target article ... I'll play with this at the
| office to tomorrow. I've got plans for it
| sa46 wrote:
| 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
| sam0x17 wrote:
| > 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 promise in other respects.
| hactually wrote:
| They're really not a `must`. What a silly comment - Docker and
| Kubernetes and substantial parts of Google wouldn't be classed
| as trivial.
|
| For the thousands of devs shipping non-trivial code, keep
| going!
| wvenable wrote:
| 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.
| yevpats wrote:
| Java is awful and slow.
|
| Invent Go.
|
| Waiting for generics....
|
| 5 year later
|
| Go looks like Java. Back to square one :)
| Andys wrote:
| sqlc is a great code generator that seems to work miracles.
|
| It uses the official postgres parser to know all the types of
| your tables and queries, and can generate perfect Go structs from
| this.
|
| It even knows your table and field types just from reading your
| migrations, tracking changes perfectly, no need to even pg_dump a
| schema definition.
|
| I also found it works fine with cockroachdb.
| evandwight wrote:
| How are migrations defined?
|
| I ask because I'm still trying to find a good solution for my
| project.
| Andys wrote:
| it supports the migration files of several different Go
| migrator modules. Usually just a series of text .sql files
| with up/down sections.
| koeng wrote:
| 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.
| nickcw wrote:
| I hadn't realized it was now ready for general use...
| SQLite 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156
| 154710873e68b3d9ac5881b0ff3f 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
| :-)
| pingu2 wrote:
| Swswsswwzzwwwwwwwwxw
| justinsaccount wrote:
| 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: */ if
| (int32((*Sqlite3)(unsafe.Pointer(db)).FmallocFailed) == 0) &&
| (int32((*Sqlite3)(unsafe.Pointer(db)).FbBenignMalloc) == 0) {
| (*Sqlite3)(unsafe.Pointer(db)).FmallocFailed = U8(1) if
| (*Sqlite3)(unsafe.Pointer(db)).FnVdbeExec > 0 {
| libc.AtomicStoreNInt32((db + 400 /* &.u1 */ /* &.isInterrupted
| */), int32(1), 0) }
| (*Sqlite3)(unsafe.Pointer(db)).Flookaside.FbDisable++
| (*Sqlite3)(unsafe.Pointer(db)).Flookaside.Fsz = U16(0)
| if (*Sqlite3)(unsafe.Pointer(db)).FpParse != 0 { (*Par
| se)(unsafe.Pointer((*Sqlite3)(unsafe.Pointer(db)).FpParse)).Frc
| = SQLITE_NOMEM } } }
| psanford wrote:
| 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.
| justinsaccount wrote:
| 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 sqlite_linux_arm.go
| sqlite_linux_arm64.go sqlite_linux_s390x.go
| sqlite_windows_386.go sqlite_windows_amd64.go
| throwaway894345 wrote:
| 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.
| ramenmeal wrote:
| We just use something like github.com/Masterminds/squirrel in
| combination with something like github.com/fatih/structs (it's
| archived, but it's easy code to write) to help with sql query
| generation, and use github.com/jmoiron/sqlx for easier scanning.
| I guess it's a little trickier when trying to use postgres
| specific commands, but we haven't run into many problems.
| jakearmitage wrote:
| How does it deal with mapping relationships? For example, a Many-
| to-Many between Posts and Tags, or a Many-to-One like Posts and
| Comments?
| Something1234 wrote:
| His codeblocks have broken horizontal scroll on mobile.
|
| Other than that I like it a lot. I built some codegen stuff in
| the past for test automation and it's really quite nice because
| it reduces a lot of user errors.
| jonbodner wrote:
| If you are looking for a way to map SQL queries to type safe Go
| functions, take a look at my library Proteus:
| https://github.com/jonbodner/proteus
|
| Proteus generates functions at runtime, avoiding code generation.
| Performance is identical to writing SQL mapping code yourself. I
| spoke about its implementation at GopherCon 2017:
| https://www.youtube.com/watch?v=hz6d7rzqJ6Q
| fprog wrote:
| 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 that
| rare tool that just "feels right". I think that feeling comes
| from a combination of things. It's fast. It uses an idiomatic Go
| approach (code generation, instead of e.g. reflection) to solve
| the problem at hand, so it feels at home in the Go ecosystem. As
| noted in the article, it allows you to check that your SQL is
| valid at compile-time, saving you from discovering errors at
| runtime, and eliminating the need for certain types of tests.
|
| But perhaps most of all, sqlc lets you _just write SQL_. After
| using sqlc, using a more conventional ORM almost seemed like a
| crazy proposition. Why would someone author an ORM, painstakingly
| creating Go functions that just map to existing SQL features?
| Such a project is practically destined to be perpetually
| incomplete, and if one day it is no longer maintained, migration
| will be painful. And why add to your code a dependency on such a
| project, when you could use a tool like sqlc that is so
| drastically lighter, and brings nearly all the benefits?
|
| sqlc embraces the idea that the right tool for talking to a
| relational database is the one we've had all along, the one which
| every engineer already knows: SQL. I look forward to using it in
| more projects.
| donio wrote:
| 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.
| pstuart wrote:
| Hopefully they'll get SQLite working on it soon and I'll be all
| over it.
| bitwize wrote:
| > 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.
| Sytten wrote:
| 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.
| grantwu wrote:
| 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 can be intimidating for new developers.
|
| The conditional insert case also just doesn't look particularly
| elegant and the SQL query is pretty large.
|
| sqlc also just doesn't look like it could help with very dynamic
| queries I need to generate - I work on a team that owns a little
| domain-specific search engine. The conditional approach could in
| theory with here, but it's not good for the query planner:
| https://use-the-index-luke.com/sql/where-clause/obfuscation/...
| joppy wrote:
| 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.
| conroy wrote:
| 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.
| jakoblorz wrote:
| For a full featured "go generate(d)" ORM try https://entgo.io/
| Seems rather similar, with the main difference being that you
| define your schema in a specific go package, from which the ORM
| is generated. The nice thing is that you can import this package
| later again to reuse something like default values etc
| robmccoll wrote:
| I've used https://github.com/xo/xo, extended it with some custom
| functions for templating, extended the templates themselves, and
| can now generate CRUD for anything in the database, functions for
| common select queries based on the indices that exist in the
| database, field filtering and scanning, updates for subsets of
| fields including some atomic operations, etc. The sky is the
| limit honestly. It has allowed me to start with something
| approximating a statically generated ORM and extend it with any
| features I want as time goes on. I also write _.extra.go files
| along side the generated_.xo.go files to extend the structs that
| are generated with custom logic and methods to convert data into
| response formats.
|
| I like the approach of starting with the database schema and
| generating code to reflect that. I define my schema in sql files
| and handle database migrations using https://github.com/golang-
| migrate/migrate.
|
| If you take this approach, you can mostly avoid exposing details
| about the SQL driver being used, and since the driver is mostly
| used by a few templates, swapping drivers doesn't take much
| effort.
___________________________________________________________________
(page generated 2021-09-08 23:00 UTC)