Consider the following case: When creating a user (database insert) with their profile (another insert), other users must be updated (database update) with a new score value. Score is just a float for which a dummy formula will be used. And then an action record is needed (insert), which marks the fact that a user was created.
The tech context is PostgreSQL in Go with pgx as database driver and Echo framework for the HTTP server. The database setup is straight forward using Docker; it also includes a database management interface which will be available at http://localhost:54321. If you clone the sample repository, and start the setup with Docker Compose (docker compose up -d), when the PostgreSQL Docker container is built, a database is created with the schema used in this post.
CREATE TABLE "users" ( "id" serial NOT NULL, "username" CHARACTER VARYING (100) NOT NULL, "score" DECIMAL NOT NULL DEFAULT 0, "created" TIMESTAMP(0) WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated" TIMESTAMP(0) WITH TIME ZONE ); CREATE TABLE "user_profile" ( "user_id" INTEGER NOT NULL, "firstname" CHARACTER VARYING (100) NOT NULL, "lastname" CHARACTER VARYING (100) NOT NULL ); CREATE TABLE "actions" ( "id" serial NOT NULL, "description" text NOT NULL, "created" TIMESTAMP(0) WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP );
Data integrity is of interest, so all the queries will be sent on a database transaction. And because there are multiple user update queries, they will be sent all at the same time in a batch of operations. Continue reading PostgreSQL batch operations in Go