sqlc isn’t an ORM, but it implements one of the most useful features of one – mapping a query back into a struct without the need for boilerplate. If you have query with a SELECT * or RETURNING *, it knows which fields a table is supposed to have, and emits the result to a standard struct representing its records. All queries for a particular table that return its complete set of fields get to share the same output struct.
Rather than implement its own partially-complete SQL parser, sqlc uses PGAnalyze’s excellent pg_query_go, which bakes in the same query parser that Postgres really uses. It’s never given me trouble so far – even complex queries with unusual Postgres embellishments work.
This query parsing also gives you some additional pre-runtime code verification. It won’t protect you against logical bugs, but it won’t compile invalid SQL queries, which is a far shot better than the guarantees you get with SQL-in-Go-strings. And thanks to SQL’s declarative nature, it tends to produce fewer bugs than comparable procedural code. You’ll still want to write tests, but you don’t need to test every query and corner case as exhaustively.
database/sql is database agnostic, which is kind of nice, but by extension that means it conforms to the lowest common denominator, and has no support for Postgres-specific features.
The library pgx is a very well-written and very thorough package for full-featured, performant connections to Postgres. However, it’s opinionated about not offering any ORM-like features, and gets you very little beyond a basic query interface. Like with database/sql, hydrating database results into structs is painful – not only do you have to list target fields off ad nauseam in a SELECT statement, but you also have to Scan them into a struct manually.
When we started using sql in Go, a major reason not to use pgx is that sqlc didn’t support pgx. A 2021 pull request has addressed this problem by giving sqlc support for multiple drivers, and the feature’s now available and mature in the sqlc’s latest release.
When we started using SQL in Go, we used sqlmock as a widely available mocking library, but it doesn't support pgx. Since then, https://github.com/pashagolub/pgxmock has emerged and matured to provide mature and full-featured pgx mocking.
On top of all that, pgx/v5 was a very major change, which provides a dramatic number of new features and performance improvements. Experimental support for :code:pgx/v5 was added in sqlc v1.17.2. Full support will be included in sqlc v1.18.0. Until then, you'll need to pass the :code:--experimental
I would like us to start to plan to move to using pgx (at least v4) before we move progress reports to use kacontext for database connections.
https://github.com/jackc/pgmock
https://github.com/jackc/pgmock https://github.com/sasakiyori/pgxmock-examples
database/sql provides a concurrency safe connection pool in *sql.DB
// stdlib -> pgx
pgxConn, err := stdlib.AcquireConn(sqlDB)
if err != nil {
return errors.New("failed to acquire pgx connection")
}
defer func() {
if err := stdlib.ReleaseConn(sqlDB, pgxConn); err != nil {
log.Printf("failed to release connection: %v\n", err)
}
}()
https://medium.com/avitotech/how-to-work-with-postgres-in-go-bad2dabd13e4
https://github.com/pashagolub/pgxmock
https://github.com/cockroachdb/copyist
https://blog.manabie.io/2021/10/snapshot-test-your-postgresql-in-golang-pgx-driver/
https://github.com/supabase/cli/blob/5c57860808a2b9333c654093edb9037bcbbb74cb/internal/testing/pgtest/step.go#L7 https://github.com/supabase/cli/blob/5c57860808a2b9333c654093edb9037bcbbb74cb/internal/testing/pgtest/mock.go
https://github.com/driftprogramming/pgxpoolmock/blob/main/pgx_pool.go