Great article! These days, I find myself reaching for sqlc whenever I need to access Postgres from Go. My experience with the project has been excellent: I get type-safe APIs and control over exactly which queries are executed without much fuss.
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;
-- name: ListAuthorsByDate :many
...
- Use multiple queries that share the same output type.
- Push the predicate into the query directly.
However, you can't pass an expression for an order by column since the order by clause takes a name, not an expression. Postgres doesn't allow using names as arguments to a prepared query so that leaves either adding an annotation like sqlc.order_by that's dynamically added to a query string, or by getting more creative with the structure of the query:
SELECT * FROM AUTHORS WHERE sqlc.arg('by_date') ORDER BY DATE
UNION ALL
SELECT * FROM AUTHORS WHERE NOT sqlc.arg('by_date')pggen occupies the same design space as sqlc but the implementations are quite different. Sqlc figures out the query types using type inference in Go which is nice because you don’t need Postgres at build time. Pggen asks Postgres what the query types are which is nice because it works with any extensions and arbitrarily complex queries.
Check out https://github.com/launchbadge/sqlx#sqlx-is-not-an-orm for an alternative approach.
struct Country { country: String, count: i64 }
let countries = sqlx::query_as!(Country, "SELECT country, count FROM countries WHERE organization = ?", organization)
.fetch_all(&db_pool)
.await?;