pgx VS migrate

By reading this tutorial, you'll learn how to use PostgreSQL with the Go programming language using the pgx driver and toolkit in a very productive manner. Furthermore, with the provided source code, you'll be able to learn how to write efficient and sound unit and integration tests, ready to be run locally or on a Continuous Integration environment, such as GitHub Actions. Use the Table of Contents to skip to a specific part of this long post. Don't forget to check out the accompanying repository github.com/henvic/pgxtutorial.

I'm using github.com/jackc/pgx/stdlib to connect on Postgres.

Just doing some testing with Golang postgres library pgx's connection pools, and I figure I could set this up outside of the main() function to share the connection pool with concurrent lambda executions. One thing I can't figure out though is I don't think I can safely, and explicitly call a close() on the connection. Is this normal to just let the connection time out if the lambda is not being used? If the connection times out before the lambda shuts off, then how can I re-establish a globally available connection pool? I imagine this is a potential problem for any db that's not accessible over HTTP(s)?

Someone correct me if I'm wrong here, but I believe Go 1.17 with "Pruned module graphs in go 1.17 modules" fixes this issue, but for pgx, it won't take effect until both your own and their go.mod only support go 1.17 or above.

I don't think so, but you should look at https://github.com/jackc/pgx which have low level features maybe for replication ?

The way it parses dates using Unix time causes issues when you compare the values returned due to Go setting the location property:

https://github.com/jackc/pgx/issues/863

pgx doesn't seem to handle reconnects the same way lib/pg does, but I haven't tested this recently: https://github.com/jackc/pgx/issues/672

String manipulation could lead to nasty SQL injections and really because you're using pgx there's no need to do that either, see the Conn.SendBatch method, it should allow you to achieve what you're trying to do: execute multiple queries in a transaction and still get their results. (test example)

pgx

In the process of trying to get better with Go, I have been trying to create a program that will scrape a subreddit and then save some info into a Postgres table. In this case, I decided to use r/frugalmalefashion and save the title and flair of recent posts into the table. I am using Colly to scrape data, and pgx as the database driver.

Makefile requires migrate - a migration tool: https://github.com/golang-migrate/migrate. I stated it in Makefile and in README, but it's probably not very obvious: Requirements: migrate Migration tool: https://github.com/golang-migrate/migrate

Comparison section to other tools like Flyway, migrate in golang land, etc.

Migrations: migrate

Not that I know of. Generally the approach taken is to have ordered migration scripts and a schema version, and then have code automatically apply the appropriate set of migrations in the right order, for example using golang-migrate.

I use this https://github.com/golang-migrate/migrate in a deploy step to each environment with the upgrade / downgrade scripts committed to the repo alongside the code. The scripts can do pretty much anything you need to do in PG including defining and executing functions.

https://github.com/golang-migrate/migrate/tree/master/cmd/migrate

curl -L https://github.com/golang-migrate/migrate/releases/download/$version/migrate.$platform-amd64.tar.gz | tar xvz

I've used https://github.com/xo/xo, extended it with some custom functions for templating, extended the templates themselves, and can now generate CRUD for anything in the database, functions for common select queries based on the indices that exist in the database, field filtering and scanning, updates for subsets of fields including some atomic operations, etc. The sky is the limit honestly. It has allowed me to start with something approximating a statically generated ORM and extend it with any features I want as time goes on. I also write .extra.go files along side the generated .xo.go files to extend the structs that are generated with custom logic and methods to convert data into response formats.

I like the approach of starting with the database schema and generating code to reflect that. I define my schema in sql files and handle database migrations using https://github.com/golang-migrate/migrate.

If you take this approach, you can mostly avoid exposing details about the SQL driver being used, and since the driver is mostly used by a few templates, swapping drivers doesn't take much effort.