How We Went All In on sqlc/pgx for Postgres and Go
1–10 of 160 posts
Re: How We Went All In on sqlc/pgx for Postgres and Go
#2We 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.
Re: How We Went All In on sqlc/pgx for Postgres and Go
#3sqlc 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.
Re: How We Went All In on sqlc/pgx for Postgres and Go
#4As 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.
Re: How We Went All In on sqlc/pgx for Postgres and Go
#5I'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.
Re: How We Went All In on sqlc/pgx for Postgres and Go
#6Talk about JIT on target article ... I'll play with this at the office to tomorrow. I've got plans for it
Re: How We Went All In on sqlc/pgx for Postgres and Go
#7As 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
Re: How We Went All In on sqlc/pgx for Postgres and Go
#8sqlc 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.
How are migrations defined?
I ask because I'm still trying to find a good solution for my project.
Re: How We Went All In on sqlc/pgx for Postgres and Go
#9sqlc 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.
How are migrations defined? I ask because I'm still trying to find a good solution for my project.
it supports the migration files of several different Go migrator modules. Usually just a series of text .sql files with up/down sections.
Re: How We Went All In on sqlc/pgx for Postgres and Go
#10For 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