4 ms·

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

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. https://sqlc.dev/ https://sqlc.dev/

Ingon 5y ago
Yes! I’ve been using sqlc both at work and on side projects and it works awesome. For me it strikes the right balance between “I want to work with sql/db abstractions” and “I don’t need orm, just automated mapping”. Has a few rough edges when advanced types needs to get mapped, but it’s getting there.
- iknowstuff 5y ago
  >I don’t need orm, just automated mapping” Check out https://github.com/launchbadge/sqlx#sqlx-is-not-an-orm 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?;
henvic 5y ago
Thanks! I love the concept of sqlc, but their support for pgx with its native PostgreSQL interface was quite limited when I checked. I imagine it's better nowadays, and will try again soon!
- lonk11 5y ago
  The support for native pgx in sqlc is good now. I migrated my project from database/sql to pgx recently and posted the instructions at the end of this post: https://linklonk.com/item/1948319982853095424 https://linklonk.com/item/1948319982853095424
- sa46 5y ago
  You might like pggen (I’m the author) which only supports Postgres and pgx. https://github.com/jschaf/pggen https://github.com/jschaf/pggen 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.
  - henvic 5y ago
    Thank you. I'll take a look in the upcoming weeks. It seems promising!
sdevonoes 5y ago
Dumb question: how do you write dynamic queries using sqlc? From the doc I see: -- name: ListAuthors :many SELECT * FROM authors ORDER BY name; But what if I want to get a list of authors by date? Shall I do: -- name: ListAuthors :many SELECT * FROM authors ORDER BY name; -- name: ListAuthorsByDate :many ... And what if later I need to get a list of authors by date and name? Should I write yet another query? I'm used to write one generic (composable) query that is very handy in situations where one need to retrieve rows by many different filtering criteria. Is that possible in sqlc?
- sa46 5y ago
  Dynamic queries are difficult to model when starting from a static query since SQL doesn't compose well. The workarounds I've used: - 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')

Ingon 5y ago

Yes! I’ve been using sqlc both at work and on side projects and it works awesome. For me it strikes the right balance between “I want to work with sql/db abstractions” and “I don’t need orm, just automated mapping”. Has a few rough edges when advanced types needs to get mapped, but it’s getting there.

iknowstuff 5y ago
>I don’t need orm, just automated mapping” Check out https://github.com/launchbadge/sqlx#sqlx-is-not-an-orm 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?;

henvic 5y ago

Thanks! I love the concept of sqlc, but their support for pgx with its native PostgreSQL interface was quite limited when I checked. I imagine it's better nowadays, and will try again soon!

lonk11 5y ago
The support for native pgx in sqlc is good now. I migrated my project from database/sql to pgx recently and posted the instructions at the end of this post: https://linklonk.com/item/1948319982853095424 https://linklonk.com/item/1948319982853095424
sa46 5y ago
You might like pggen (I’m the author) which only supports Postgres and pgx. https://github.com/jschaf/pggen https://github.com/jschaf/pggen 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.
- henvic 5y ago
  Thank you. I'll take a look in the upcoming weeks. It seems promising!

sdevonoes 5y ago

Dumb question: how do you write dynamic queries using sqlc? From the doc I see: -- name: ListAuthors :many SELECT * FROM authors ORDER BY name; But what if I want to get a list of authors by date? Shall I do: -- name: ListAuthors :many SELECT * FROM authors ORDER BY name; -- name: ListAuthorsByDate :many ... And what if later I need to get a list of authors by date and name? Should I write yet another query? I'm used to write one generic (composable) query that is very handy in situations where one need to retrieve rows by many different filtering criteria. Is that possible in sqlc?

sa46 5y ago
Dynamic queries are difficult to model when starting from a static query since SQL doesn't compose well. The workarounds I've used: - 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')