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

141–150 of 160 posts

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

#141

Post by y4mi »

otabdeveloper4 wrote: post #120
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…

> code generation, instead of e.g. reflection "Hey man, we noticed there's not enough compiler in your compiler, so we made a second compiler for your compiler."

The one thing that kept annoying me with compilation type orms like this is the chicken and egg situation.

Your migrations will likely be run by your application, but your application won't compile until you've run your migrations.

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

#142

Post by marcus_holmes »

jerrysievert wrote: post #135
marcus_holmes wrote: post #97

Earlier quoted context omitted.

I did this on a recent project, and it worked really well. I had each function definition in its own .sql file, with a preceding "drop function" call, and a Makefile clause to run them all. Which meant managing versions was easy (coupled with migration .sql files). I also got to find out if any of my SQL was broken right up front, and testing the SQL was simple - call the function and check the return. I also defined…

> I had each function definition in its own .sql file, with a preceding "drop function" call, and a Makefile clause to run them all. why drop instead of CREATE OR REPLACE ?

CREATE OR REPLACE requires that the new function has the same signature as the old one [0]. I understand why, but I needed to change the signature sometimes. It was easier to do a Drop and then a Create as standard. Though this did mean having to manage dependencies between files myself (I prefixed the .sql files with 00_, 01_,02_ etc to indicate order of dependencies). It sounds like a lot of work, but in practice it was easy - it broke very quickly and very loudly if I got it wrong at all ;)

[0] https://www.postgresql.org/docs/13/sql-createfunction.html

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

#143

Post by xorcist »

Cthulhu_ wrote: post #130
xorcist wrote: post #122

Earlier quoted context omitted.

It does indeed look promising! Can it help with migrations? Seeing it has the field definitions right there it should at least be possible. Otherwise I can see how a system like this could become quite complex over time as the database structure changes.

Based on just this article, it looks like it needs a complete table definition to work with. Unless it queries the database for the definitive schema, but that would mean you need a database up and running at compile time.

I took for granted that sqlc could run all these CREATE TABLEs for you, perhaps that's not the case. It probably should, shouldn't it?

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

#144

Post by jvolkman »

sa46 wrote: post #71
sagarm wrote: post #61

Earlier quoted context omitted.

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.

Yep, that’s the main downside. pggen works out of the box with Docker under the hood if you give it some schema files. Notably, the recommended way to run sqlc also requires Docker. I check in the generated code so I only run pggen at dev time, not build time. I do intend to move to build time codegen with Bazel but I built out tooling to launch new instances of Bazel managed Postgres in 200 ms so not that painful. M…

What does your bazel tooling look like? We use postgres and bazel together in automated tests and I'm curious about how others start and manage postgres instances in similar scenarios. Currently were driving postgres from Python (py.test) while running tests.

The issue with our current approach is that we need to keep the bazel tests fairly coarse (e.g., one bazel test for dozens of python tests) to keep the overhead of starting postgres instances down.

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

#145

Post by the-smug-one »

hactually wrote: post #86
sam0x17 wrote: post #46

Earlier quoted context omitted.

> Docker and Kubernetes Both of these projects have had to go way out of their way to make things work without generics, but they are large enough projects and have enough resources that they can do this. Both are actually really great examples of why Go is a bad choice until generics are added and have first-class support. Even C would be preferable over something where there are no generics.

You're incorrect but that's OK. I've been writing Golang for 5 years and have never had need for generics. It's nothing to with resources, it's just understanding how to build software. Your retreat to C is a bit sad - I'd be happy to help you if you've got any Go you're struggling with?

I wrote Go for 3 days at my new job and found several places where people opted for interface{} over generics.

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

#146

Post by jd3 »

sdevonoes wrote: post #126

I think I'm missing something, but I don't get sqlc. Let's say I want to "get a list of authors", so in sqlc I would write: -- name: ListAuthors :many SELECT * FROM authors ORDER BY name; and in Go I can then say `authors, err := queries.ListAuthors(ctx)`. This is cool. Now, if I want to "get a list of American authors" I would write: -- name: ListAuthorsByNationality :many SELECT * FROM authors WHERE nationality = $…

this is the reason why I chose upper/db over pgx/sqlc for my current cockroachdb side project

while upper/db is not as type safe, with proper testing infrastructure, it felt most similar to django due to its simplicity/composability/query building support

i'm also excited to see how upper/db grows after generics land in Go later this year

https://github.com/upper/db

https://upper.io/

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

#147

Post by EB66 »

doctor_eval wrote: post #125
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…

Yep, close to 100% of my data manipulation is done in pl/pgsql. It’s awesome. At least 50% fewer LOC, and 10-100x faster than the equivalent code written in Java or Go, due to all the round trips.

Oh yeah, I completely forgot to mention that. The performance gains are immense when you use Pl/pgSQL to eliminate round-trips to the database. That's easily one of the most important reasons to use Pl/pgSQL.

The vast majority of data-heavy web apps today must have the database running on the same server or within the same datacenter -- they can't tolerate any kind of latency between the application and the database server because they failed to reduce round-trips. Ever tried deploying MediaWiki on an application server with >20ms latency to the database server? It just doesn't work -- each page takes several seconds to load.

If you minimize round-trips to the database, it gives you more flexibility on how you can deploy/host your database server. That's flexibility you want when you're designing failover/disaster recovery schemes.

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

#148

Post by sa46 »

jvolkman wrote: post #144
sa46 wrote: post #71

Earlier quoted context omitted.

Yep, that’s the main downside. pggen works out of the box with Docker under the hood if you give it some schema files. Notably, the recommended way to run sqlc also requires Docker. I check in the generated code so I only run pggen at dev time, not build time. I do intend to move to build time codegen with Bazel but I built out tooling to launch new instances of Bazel managed Postgres in 200 ms so not that painful. M…

What does your bazel tooling look like? We use postgres and bazel together in automated tests and I'm curious about how others start and manage postgres instances in similar scenarios. Currently were driving postgres from Python (py.test) while running tests. The issue with our current approach is that we need to keep the bazel tests fairly coarse (e.g., one bazel test for dozens of python tests) to keep the overhead…

I uploaded most of our Workspace setup here: https://github.com/jschaf/bazel-postgres-sketch. The tooling is a bunch of Go to manage the Postgres process. Basically, give it schema files and receive a running database with a tear down command.

We make temp instances of Postgres quickly by:

- avoiding Docker, especially on Mac

- keeping the data dir on tmpfs

- Disable initdb cleanup

- Disable fsync and other data integrity flags

- Use unlogged tables.

- Use sockets instead of TCP localhost.

For a test suite, it was 12x faster to call createdb with the same Postgres cluster for each test than than to create a whole new db cluster. The trick was to create a template database after loading the schema and use that for each createdb call.

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

#149

Post by jvolkman »

sa46 wrote: post #148
jvolkman wrote: post #144

Earlier quoted context omitted.

What does your bazel tooling look like? We use postgres and bazel together in automated tests and I'm curious about how others start and manage postgres instances in similar scenarios. Currently were driving postgres from Python (py.test) while running tests. The issue with our current approach is that we need to keep the bazel tests fairly coarse (e.g., one bazel test for dozens of python tests) to keep the overhead…

I uploaded most of our Workspace setup here: https://github.com/jschaf/bazel-postgres-sketch . The tooling is a bunch of Go to manage the Postgres process. Basically, give it schema files and receive a running database with a tear down command. We make temp instances of Postgres quickly by: - avoiding Docker, especially on Mac - keeping the data dir on tmpfs - Disable initdb cleanup - Disable fsync and other data int…

Cool, thanks for the link.

For what it's worth, we use rules_nixpkgs to source Postgres (for Linux and Darwin) as well as things such as C and Python toolchains, and it's been working really well. It does require that the machine have Nix installed, though, but that opens up access to Nix's wide array of prebuilt packages.

https://github.com/tweag/rules_nixpkgs

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

#150

Post by mariushn »

marcus_holmes wrote: post #97
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…

I did this on a recent project, and it worked really well. I had each function definition in its own .sql file, with a preceding "drop function" call, and a Makefile clause to run them all. Which meant managing versions was easy (coupled with migration .sql files). I also got to find out if any of my SQL was broken right up front, and testing the SQL was simple - call the function and check the return. I also defined…

Could you please give 2 specific examples on how this would work? Are the functions only for UPDATE/INSERT, or also for reading data? I'm using views to simplify queries, but still via ORM.
Post reply on HN