Database Transactions in Go

Database transactions are coherent, dependable units of operations on the database using a database management system (DBMS). The status of the processes are dependent on one another.

Database transactions must be ACID (Atomicity, Consistency, Isolation, and Durability). Atomic such that transactions are

• treated as a unit,
• consistent in state after transactions,
• isolated such that transactions don’t affect one another, and
• durable since the database updates regardless of the system’s state.

Database transactions are most popular for record-keeping and accounting-related activities where operations are interdependent of one another (debiting account A to credit account B, where both processes MUST go through or get canceled).

Getting Started With Database Transactions in Go

Since database transactions are mainstream, the Go ecosystem’s most popular database packages and tools provide execution functionality.

Go’s built-in database/sql package for interacting with SQL databases also supports transactions on the most popular SQL databases depending on your preferred database driver.

The package provides the methods that make transactions ACID. You get to commit, execute, prepare, query, and roll back transactions in a similar fashion as you would on single database operations.

Connecting to a SQL Database in Go With the database/sql Package

You’ll need to connect to your database to operate on the database and execute transactions. You can use the Open method of the sql package to connect to a SQL database using a driver that interfaces with the sql package’s API.

In this tutorial, you’ll use an SQLite database and the sqlite3 database driver to connect to your database and execute transactions.

Run this command in the terminal of your working directory to install the go-sqlite3 package.

go get github.com/mattn/go-sqlite3

Import these packages for the transaction. You’ll use the context package to set a context for the transaction and the log package for logging errors to the console.

import (
  "context"
  "database/sql"
  _ "github.com/mattn/go-sqlite3"
  "log"
)

You can connect to a database with the Open method of the sql package. The Open method opens a connection to the database with the specified driver and returns a database connection instance.

func createAndInsert() {

  db, err := sql.Open("sqlite3", "goodnessuc.db")
  if err != nil {
    log.Printf("Failed to open database connecton")
  }
  defer db.Close()

}

The createAndInsert function connects to the database with the Open method and closes the connection with a defer statement and the Close method.

You can ping the database to verify your connection status with the Ping method of the connection instance that returns connection errors, if there are any.

err = db.Ping()
if err != nil {
    log.Fatalln("There was an error pinging the database", err)
}

Executing Database Transactions in Go

You’ll begin a transaction, write the SQL operations you want to execute, roll back changes if there are any errors with operations, and commit the changes for a successful transaction.

You’ll need to create a context with the context package to execute transactions. You can use the Background method to create a background context that will never get canceled.

// create a context for the transaction
ctx := context.Background()

You can begin a transaction with the BeginTx method of your database instance. The BeginTx method takes in the context instance and additional options and returns a transaction instance for your operations.

//begin the transaction
transaction, err := db.BeginTx(ctx, nil)
if err != nil {
  log.Println("Error beginning the transaction", err)
}

In this article, the transaction will feature an insert operation and a query operation. Here’s the current state of the database before the transaction.

Here’s an insert statement that inserts a new username and balance to the database.

insert, err := transaction.PrepareContext(ctx, "INSERT INTO accounts (username, balance) VALUES (?, ?)")
insert.Exec("David", 8958)
if err != nil {
  err := transaction.Rollback()
  if err != nil {
    log.Println("Error rolling back transaction on the insertion", err)
  }
}

If there are any errors with the insertion, the transaction gets rolled back; the database will not reflect any changes.

Here’s a query operation that’s part of the operations for the transaction.

row := transaction.QueryRow("SELECT * FROM accounts WHERE balance=5000")
  var (
    username string
    balance  int
  )

  err = row.Scan(&username, &balance)
  if err != nil {
    transaction.Rollback()
    log.Println("Error querying the row", err)

  }

The QueryRow method helps with querying the database for fields where the balance equals 5000.

You can commit changes when there are no errors in operations with the Commit method. The Commit method returns possible errors or applies the changes to the database if there are no errors.

// Commit the changes
  err = transaction.Commit()
  if err != nil {
    log.Fatal(err)
  }

Conclusion

You’ve learned about transactions, how transactions work, properties of SQL transactions, how you can execute SQL queries and insertions, and how you can execute transactions in Go. You’ll find transactions useful when you’re building complex applications. See more posts here about Go.