Introduction
A transaction is a unit of work that you want to treat as “a whole.” It has to either happen in full or not at all. In Go lang, a transaction is essentially an object that reserves a connection to the database. You begin a transaction with a call to db.Begin() and close it with a commit() or rollback() method on the resulting Tx variable.
In this post, we will learn how to implement database transactions in your Go lang application.
Prerequisites
To be able to achieve our objective i.e. Transactions in PostgreSQL from Go , there are some prerequisites. Sticking to the core subject of this blog, I won’t cover them, however, I’m providing with some references
Install PostgreSQL, setup an instance and create a test database – https://www.postgresqltutorial.com/install-postgresql/
Install Go and configure workspace – https://www.callicoder.com/golang-installation-setup-gopath-workspace/
For this tutorial, I’m using Postgres 12 and Go 1.13.xx
Objective – Transactions in PostgreSQL using Go lang database/sql package
Step 1. SQL Table definition
We’ll re-use our test table EMP that we created in the last post
-- create a sample table EMP CREATE TABLE emp ( empno SERIAL PRIMARY KEY, ename TEXT, sal INT CHECK (sal>0), email TEXT UNIQUE NOT NULL );
Step 2. Basic SQL transactions
For this example, let’s just assume that we want following SQL statements to be executed together
-- Stmt #1. INSERT a new employee record
INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com');
-- Stmt #2. UPDATE sal for this new employee
UPDATE emp SET sal = 800 WHERE ename = 'Smith';
Now think of a scenario where only the first SQL statement succeeds, however, the second statement fails: you now have a new employee Smith, however, does not have a salary allocated,
- Can we treat this as a success, since Smith does not have a salary allocated
- Also, Is this a failure, since Smith employee record is created
- If we consider this a failure and retry the transaction, then employee record of ‘Smith’ will be inserted twice
This is a classic situation and in relational systems, we handle them using database transactions i.e. allowing both the SQL statements to Pass or Fail together. Below is an example of database transactions to handle the above scenario.
BEGIN;
INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com');
UPDATE emp SET sal = 800 WHERE ename = 'Smith';
COMMIT;
Here
- First, open a transaction by issuing the BEGIN command
- Second, issue SQL statements to SELECT or INSERT or UPDATE data in the database. The SQL statements are executed one after the other
- Third, commit the changes to the database by using the COMMIT statement
- If you do not want to save the changes, you can roll back using the ROLLBACK statement
Step 3. Implementing Transactions in Go lang
You begin a transaction with a call to db.Begin(), and close it with a Commit() or Rollback() method on the resulting Tx variable. Under the covers, the Tx gets a connection from the pool, and reserves it for use only with that transaction. The methods on the Tx map one-for-one to methods you can call on the database itself, such as Query() and so forth.
// First You begin a transaction with a call to db.Begin()
ctx := context.Background()
tx, err := db.BeginTx(ctx, nil)
if err != nil {
log.Fatal(err)
}
While you are working inside a transaction you should be careful not to make calls to the db variable. Make all of your calls to the Tx variable that you created with db.Begin(). db is not in a transaction, only the Tx object is. If you make further calls to db.Exec() or similar, those will happen outside the scope of your transaction, on other connections.
// Second, execute SQL queries against on the transaction instance. Note these are not applied to the database yet
_, err = tx.ExecContext(ctx, "INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com')")
if err != nil {
// Incase we find any error in the query execution, rollback the transaction
tx.Rollback()
return
}
// The next query is handled similarly
_, err = tx.ExecContext(ctx, "UPDATE emp SET sal = 0 WHERE ename = 'Smith'")
if err != nil {
tx.Rollback()
fmt.Println("\n", (err), "\n ....Transaction rollback!\n")
return
}
A Tx will maintain that single connection for its entire life cycle, releasing it only when Commit() or Rollback() is called. You should take care to call at least one of these, or else the connection will be held until garbage collection.
// close the transaction with a Commit() or Rollback() method on the resulting Tx variable.
err = tx.Commit()
if err != nil {
log.Fatal(err)
}
Step 4. Putting this all together
package main
// modifying data and using transactions
import (
"context"
"database/sql"
"fmt"
"log"
_ "github.com/lib/pq"
)
const (
host = "localhost"
port = 5432
user = "postgres"
password = "postgres"
dbname = "connect"
)
var (
ename string
sal int
)
func main() {
psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
"password=%s dbname=%s sslmode=disable",
host, port, user, password, dbname)
db, err := sql.Open("postgres", psqlInfo)
if err != nil {
panic(err)
}
defer db.Close()
// First You begin a transaction with a call to db.Begin()
// `tx` is an instance of `*sql.Tx` through which we can execute our queries
ctx := context.Background()
tx, err := db.BeginTx(ctx, nil)
if err != nil {
log.Fatal(err)
}
// Second, execute SQL queries against on the transaction instance. Note these are not applied to the database yet
_, err = tx.ExecContext(ctx, "INSERT INTO public.emp (ename, email) VALUES('Smith', 'smith@acme.com')")
if err != nil {
// Incase we find any error in the query execution, rollback the transaction
tx.Rollback()
fmt.Println("\n", (err), "\n ....Transaction rollback!\n")
return
}
// The next query is handled similarly
_, err = tx.ExecContext(ctx, "UPDATE emp SET sal = 0 WHERE ename = 'Smith'")
if err != nil {
tx.Rollback()
fmt.Println("\n", (err), "\n ....Transaction rollback!\n")
return
}
// close the transaction with a Commit() or Rollback() method on the resulting Tx variable.
// this applies the above changes to our database
err = tx.Commit()
if err != nil {
log.Fatal(err)
} else {
fmt.Println("....Transaction committed\n")
}
}
Conclusion
In this post we learned how to implement database transactions in PostgreSQL using Go lang database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. This is also concludes my blog post series for on Working with PostgreSQL in Go Lang. Below are all other post in this series:
1. My journey from DBA to DevOps and why I started learning Go lang
2. Connecting to PostgreSQL from Go lang project
3. Inserting rows in PostgreSQL db from Go lang project
4. Updating and Deleting rows in PostgreSQL from Go lang project
5. Querying rows from PostgreSQL from Go lang project
If you find my post helpful, I’d be very grateful if you’d help it spread by emailing it to a friend, or sharing it on Twitter or Facebook.
Thank you 🙋♂️
