Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How We Went All In on sqlc/pgx for Postgres and Go

brandur.org

Post reply on HN
Print view View original

61–70 of 160 posts

Re: How We Went All In on sqlc/pgx for Postgres and Go

#61

Post by sagarm »

sa46 wrote: post #27

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 (prote…

I like this design! Asking the database to tell you the schema of your result does seem like the simplest, most reliable option.

However it does require you to have a running database as part of your build process; normally you'd only need the database to run integration tests. Doable, but a bit painful.

Re: How We Went All In on sqlc/pgx for Postgres and Go

#62

Post by sk0g »

colesantiago wrote: post #51
bitwize wrote: post #29

Earlier quoted context omitted.

> 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…

Or just to save time and better yet, save you from massive security issues down the line. I've seen raw SQL queries full of fatal SQL injection bugs like these in littered in codebases, very cringeworthy.

Most languages have a way to avoid SQL injection attacks, and linters that enforce usage of that.

For bad workplaces just using an ORM is a lot safer though, I agree. Performance can quickly become an issue when people stop thinking entirely about the DB level operations happening, and this comes up much quicker at workplaces where not enough people care.

Re: How We Went All In on sqlc/pgx for Postgres and Go

#63

Post by ethanpailes »

jakearmitage wrote: post #17

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?

If you want a code generator like this that has support for that kind of thing, https://github.com/opendoor/pggen can automatically infer these kinds of relationships based on foreign key relationships and emit slices of pointers to connect the records together in memory. It can even figure out 1-1 relationships if there is a UNIQUE index on the foreign key. There is a little mini-DSL for specifying exactly how much of the transitive closure of a given record you want to get filled in for you.

Re: How We Went All In on sqlc/pgx for Postgres and Go

#64

Post by harikb »

EB66 wrote: post #57
Sytten wrote: post #30

Earlier quoted context omitted.

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…

> Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular. At least when it comes to Postgres, I don't understand why more developers don't create their own user-defined functions with PL/pgSQL. It's very a robust and powerful procedural language. In my opinion, ORM's like SQLAlchemy add a completely unnecessary layer of abstraction. ORMs might be con…

Two problems

1. How do you handle versioning? Like if you want to try a development branch on a non-branch/shared db. Creating different version of stored procedures creates a recursive problem. A calls B, now A’ has to call B’

2. Sometimes we still need to programmatically decide to include a table in the join or not or get creative on a filter. Pl/pgsql is less flexible in this regard. You get the benefit of syntax checking only when query is verbatim and not dynamically constructed.

Re: How We Went All In on sqlc/pgx for Postgres and Go

#65

Post by [deleted] »

EB66 wrote: post #57
Sytten wrote: post #30

Earlier quoted context omitted.

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…

> Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular. At least when it comes to Postgres, I don't understand why more developers don't create their own user-defined functions with PL/pgSQL. It's very a robust and powerful procedural language. In my opinion, ORM's like SQLAlchemy add a completely unnecessary layer of abstraction. ORMs might be con…

[deleted]

Re: How We Went All In on sqlc/pgx for Postgres and Go

#66

Post by EB66 »

harikb wrote: post #64
EB66 wrote: post #57

Earlier quoted context omitted.

> Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular. At least when it comes to Postgres, I don't understand why more developers don't create their own user-defined functions with PL/pgSQL. It's very a robust and powerful procedural language. In my opinion, ORM's like SQLAlchemy add a completely unnecessary layer of abstraction. ORMs might be con…

Two problems 1. How do you handle versioning? Like if you want to try a development branch on a non-branch/shared db. Creating different version of stored procedures creates a recursive problem. A calls B, now A’ has to call B’ 2. Sometimes we still need to programmatically decide to include a table in the join or not or get creative on a filter. Pl/pgsql is less flexible in this regard. You get the benefit of syntax…

> 1. How do you handle versioning? Like if you want to try a development branch on a non-branch/shared db. Creating different version of stored procedures creates a recursive problem. A calls B, now A’ has to call B’

Writing UDFs and using Pl/pgSQL has no impact on how you do versioning. At my company we follow standard Gitflow and use golang-migrate for schema migrations (or Phinx for our PHP code bases).

If you're working at a company where developers are all forced to use the same shared database, then you're going to have a lot of development challenges that are unrelated to UDFs and Pl/pgSQL. Multiple devs sharing the same database always requires some team coordination to ensure that each member isn't stepping on another's toes -- whether that be prefixing your UDFs with your initials during development or agreeing not to work on the same UDFs at the same time.

> 2. Sometimes we still need to programmatically decide to include a table in the join or not or get creative on a filter. Pl/pgsql is less flexible in this regard. You get the benefit of syntax checking only when query is verbatim and not dynamically constructed.

That's just untrue. Pl/pgSQL fully supports conditional logic, dynamic query string construction, multi-query transactions, storing intermediate result sets in a variable or temp table, etc. The use case you described is actually a great example of when you would decide to use Pl/pgSQL. The language is extremely robust.

Re: How We Went All In on sqlc/pgx for Postgres and Go

#67

Post by Scarbutt »

crescentfresh wrote: post #39

> A big downside of vanilla database/sql or pgx is that SQL queries are strings What's wrong with strings? The argument in the article is that they cannot be compile-time checked, but I'm confused as to the solution to that problem ("you need to write exhaustive test coverage to verify them"). Is this saying that if they weren't strings you wouldn't need test coverage?

Maybe the key word here is 'exhaustive'

What did stand out for me from that section was:

This is fine for simple queries, but provides little in the way of confidence that queries actually work.

Why not just paste the string first to psql to make sure the query actually works?

Re: How We Went All In on sqlc/pgx for Postgres and Go

#68

Post by stubish »

fprog wrote: post #12

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…

I've used similar tools (which I'm not going to recommend, as they have not aged well), and found it great to be able to rely on the compiler for checks. With ORMs or SQL I realized I would be better off working in Python, as without the compile time checks I got none of the benefits of Go and all the down sides. I haven't used sqlc, but do like that you just feed it queries. Other tools rely on using a templating language to generate the Go code from database schema introspection, and it is just awful to work with. Generics should do away with needing the templates, so maybe the database schema introspection approach will improve.

Re: How We Went All In on sqlc/pgx for Postgres and Go

#69

Post by harikb »

EB66 wrote: post #66
harikb wrote: post #64

Earlier quoted context omitted.

Two problems 1. How do you handle versioning? Like if you want to try a development branch on a non-branch/shared db. Creating different version of stored procedures creates a recursive problem. A calls B, now A’ has to call B’ 2. Sometimes we still need to programmatically decide to include a table in the join or not or get creative on a filter. Pl/pgsql is less flexible in this regard. You get the benefit of syntax…

> 1. How do you handle versioning? Like if you want to try a development branch on a non-branch/shared db. Creating different version of stored procedures creates a recursive problem. A calls B, now A’ has to call B’ Writing UDFs and using Pl/pgSQL has no impact on how you do versioning. At my company we follow standard Gitflow and use golang-migrate for schema migrations (or Phinx for our PHP code bases). If you're…

I get that pgsql can construct dynamic queries, but I was assuming you were talking about the benefit of install time / compile time verification of query syntax. This is true for most regular stored procedures except when query is dynamic. Obviously the exact query isn’t known until runtime. I agree it is not a major downside.

Re: How We Went All In on sqlc/pgx for Postgres and Go

#70

Post by candiddevmike »

Scarbutt wrote: post #67
crescentfresh wrote: post #39

> A big downside of vanilla database/sql or pgx is that SQL queries are strings What's wrong with strings? The argument in the article is that they cannot be compile-time checked, but I'm confused as to the solution to that problem ("you need to write exhaustive test coverage to verify them"). Is this saying that if they weren't strings you wouldn't need test coverage?

Maybe the key word here is 'exhaustive' What did stand out for me from that section was: This is fine for simple queries, but provides little in the way of confidence that queries actually work. Why not just paste the string first to psql to make sure the query actually works?

There is a significant number of developers who think requiring a database to run tests is abhorrent, even in the age of containers. Instead, they'd rather write tests and validations in their app code against the query syntax, which ends up being more work and not as comprehensive.
Post reply on HN