How to Implement MySQL Transactions with Golang On Linux Server

Updated on December 8, 2021

Introduction

A transaction is a group of SQL statements that you perform to complete a business logic in a database. For instance, when creating a sales order, you need to save data in multiple tables, including sales_orders, sales_payments, and sales_orders_products. In addition, a transaction must be atomic in nature. That is, all operations in the transaction must be completed in their entirety or have no effect on the database at all.

The main benefit of using atomic transactions in your database is data consistency. When storing related data in multiple tables, you should ensure that there are no partial inserts. If an error occurs during a transaction and some of the tables have not been updated, you should roll back any incomplete operations and abort saving data to disk.

Implementing database transactions with Golang is inevitable, especially when coding financial applications, shopping carts, point of sale applications, subscription services, and more.

In this guide, you'll implement MySQL transactions with Golang on your Linux server.

Prerequisites

To complete this guide, you require:

1. Create a Database, User Account, and Sample Tables

Your sample application requires several tables to process customers' sales orders in this tutorial. SSH to your server to complete the following steps.

Next, enter the root password for your MySQL server and press Enter to proceed. Then, run the statements below to create a store_db database and a store_db_user user account. Replace EXAMPLE_PASSWORD with a strong value.

 mysql> CREATE DATABASE store_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
        CREATE USER 'store_db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
        GRANT ALL PRIVILEGES ON store_db.* TO 'store_db_user'@'localhost';
        FLUSH PRIVILEGES;

Select your new store_db database.
```
 mysql> USE store_db;
```

Next, create a products table. This table stores several items that you intend to sell in your sample store, and you're uniquely identifying them by the product_id column.

 mysql> CREATE TABLE products (
            product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            product_name VARCHAR(50),
            retail_price DOUBLE
        ) ENGINE = InnoDB;

Populate the products table with some items.

 mysql> INSERT INTO products (product_name, retail_price) VALUES ('5G SMARTPHONE', '850');
        INSERT INTO products (product_name, retail_price) VALUES ('FITNESS TRACKER', '94');
        INSERT INTO products (product_name, retail_price) VALUES ('1 LTR MINERAL WATER', '1.8');

Query the products table to ensure you've inserted the items.

 mysql> SELECT
            product_id,
            product_name,
            retail_price
        FROM products;

Output.

 +------------+---------------------+--------------+
 | product_id | product_name        | retail_price |
 +------------+---------------------+--------------+
 |          1 | 5G SMARTPHONE       |          850 |
 |          2 | FITNESS TRACKER     |           94 |
 |          3 | 1 LTR MINERAL WATER |          1.8 |
 +------------+---------------------+--------------+
 3 rows in set (0.00 sec)

Next, create a customers table. This table stores customer_ids and associated names.

 mysql> CREATE TABLE customers (
            customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            first_name VARCHAR(50),
            last_name VARCHAR(50)
        ) ENGINE = InnoDB;

Populate the customers table with sample data.

 mysql> INSERT INTO customers (first_name, last_name) VALUES ('JOHN', 'DOE');
        INSERT INTO customers (first_name, last_name) VALUES ('MARY', 'SMITH');
        INSERT INTO customers (first_name, last_name) VALUES ('STEVE', 'JACOBS');

Query the customers table to ensure the records are in place.

 mysql> SELECT
            customer_id,
            first_name,
            last_name
        FROM customers;

Output.

 +-------------+------------+-----------+
 | customer_id | first_name | last_name |
 +-------------+------------+-----------+
 |           1 | JOHN       | DOE       |
 |           2 | MARY       | SMITH     |
 |           3 | STEVE      | JACOBS    |
 +-------------+------------+-----------+
 3 rows in set (0.00 sec)

Next, create a sales_orders table. You'll use this table to store sales orders made by your customers. First, you'll identify each order using an order_id column. To associate customers with their orders, you'll use a customer_id column in this table that refers back to the same column on the customers table. Then, you'll use the order_date column to capture the date and time when the order is created.
```
mysql> CREATE TABLE sales_orders (
           order_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           customer_id BIGINT,
           order_date DATETIME
       ) ENGINE = InnoDB;
```
Next, create a sales_products table. This table stores the exact products the customer is ordering together with the actual quantity ordered. This table links back to the sales_orders table using the order_id column.
```
mysql> CREATE TABLE sales_products (
           ref_id  BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           order_id BIGINT,
           product_id BIGINT,
           qty DOUBLE
       ) ENGINE = InnoDB;
```
Log out from the MySQL server.
```
mysql> EXIT;
```

2. Create a `project` Directory and a `main.go` File

To easily separate your application from the rest of the Linux files, you need a new directory. Then, you'll set up the main.go file that executes when you run your application.

Create a project directory.
```
 $ mkdir project
```
Navigate to the new project directory.
```
 $ cd project
```
Then, use nano to open a new main.go file.
```
 $ nano main.go
```

Enter the following information into the main.go file.

 package main

 import (
     "net/http"
 "encoding/json"
     "fmt"
 )

 func main() {
     http.HandleFunc("/sales_orders", salesOrdersHandler)        
     http.ListenAndServe(":8080", nil)
 }

 func salesOrdersHandler(w http.ResponseWriter, req *http.Request) {

     params := map[string]interface{}{}

     err := json.NewDecoder(req.Body).Decode(&params)

     if err != nil {

         fmt.Fprintf(w, "Failed to decode Json body.")

     } else {

         response, _ := createSalesOrder(params)

         fmt.Fprintf(w, response + "\r\n")
     }

 }

Save and close the main.go file when you're through with editing.
In the above file, you're importing the net/http library to implement HTTP functions. Then, you're using the encoding/json package to decode JSON inputs and the fmt package to print output.
When you fire the main(){} function, you're redirecting any request to the /sales_orders resource to the salesOrdersHandler function, which captures JSON inputted data and redirects the same to a createSalesOrder function which you'll create in a new file later.
The main.go file allows your application to work as a web server and to listen for incoming requests on port 8080. You're achieving this using the statement http.ListenAndServe(":8080", nil).

2. Create a `sales.go` File

Once you receive an HTTP request on your main.go file, you're directing it to the function createSalesOrder. You'll create this function on a separate file in this step.

Use nano to open a new sales.go file.
```
 $ nano sales.go
```

Then, enter the information below into the file. Replace EXAMPLE_PASSWORD with the correct username for your store_db_user user account.

 package main

 import (
     "database/sql"
     _ "github.com/go-sql-driver/mysql"
     "fmt"
 )

 func createSalesOrder(params map[string]interface{}) (string, error) {

     db, err := sql.Open("mysql", "store_db_user:EXAMPLE_PASSWORD@tcp(127.0.0.1:3306)/store_db")                                                       
     if err != nil {
         return "", err
     } 

     defer db.Close()

     tx, err := db.Begin()           

     queryString := "insert into sales_orders(customer_id, order_date) values (?, ?)"

     response, err := tx.Exec(queryString, params["customer_id"], params["order_date"])  

     if err != nil {
         tx.Rollback()
         return "Failed to create a sales order, transaction rolled back. Reason: " + err.Error() + "\r\n", err
     }

     orderId, err := response.LastInsertId()

     if err != nil {
         tx.Rollback()
         return "Failed to retrieve order_id, transaction rolled back. Reason: " + err.Error() + "\r\n", err
     }

     queryString = "insert into sales_products(order_id, product_id, qty) values (?, ?, ?)" 

     product := map[string]interface{}{}

     for _, value := range params["sales_products"].([]interface{}) { 

         product = value.(map[string]interface{})

         _, err := tx.Exec(queryString, orderId, product["product_id"], product["qty"])  

         if err != nil {
             tx.Rollback()
             return "Failed to insert sales order product. Transaction rolled back. Reason: " +  err.Error() + "\r\n", err
         }

     }        

     err = tx.Commit()

     if err != nil {            
         return "Failed to create the order.\r\n,", err
     }

     return "Success, Sales id is: " + fmt.Sprint(orderId) + "\r\n", nil
 }

Save and close the file when you're through with editing.
In the above sales.go file, you have a single createSalesOrder(...){...} function that accepts a parameter of type map[string]interface{}. The map contains order details, including the customer_id, order_date, and sales_products. In this function, your main objective is to insert the sales order details into the sales_orders and sales_products tables atomically using a MySQL transaction.
The first thing you're doing in this createSalesOrder function is creating a connection to your database using the statement db, err := sql.Open("mysql", "store_db_user:EXAMPLE_PASSWORD@tcp(127.0.0.1:3306)/store_db").
Next, you're using the statement tx, err := db.Begin() to instruct MySQL to begin a transaction. This means any change you make will not be saved to the disk until you issue a tx.Commit() statement.
Then, you're using the statement response, err := tx.Exec(...) to insert the customer_id and order_date to the sales_orders table. In case the transaction fails, you're using the statement ...if err != nil { tx.Rollback()}.. to roll back the database to its original state. Otherwise, you're retrieving a new orderId using the statement orderId, err := response.LastInsertId().
Next, you're looping through all sales_products items using the statement for _, value := range params["sales_products"].([]interface{}) {...} and inserting each order item to the sales_products table. Once you've inserted everything into the database, you're issuing a tx.Commit() command to commit the changes permanently to the database.
Your main idea in this function is to block any statement that may put your database into an inconsistent state(For instance, a sales order with an invalid date). You're explicitly doing this using the tx.Rollback() statement.

3. Test the Golang MySQL Transaction Application

You'll use curl to send a sample sales order payload to your application and see if everything is working as expected.

Before you do this, download the SQL driver you've used in your application and run the project.
```
 $ go get github.com/go-sql-driver/mysql
 $ go run ./
```
Your server should now listen on port 8080. Don't enter any other command on this SSH session.

Next, SSH to your server on a new terminal window and issue the command below to create a sample sales order for a customer.

 $ curl -i -X POST localhost:8080/sales_orders -H "Content-Type: application/json" -d '{"customer_id": 1, "order_date": "2021-11-29", "sales_products": [{"product_id": 1, "qty": 6}, {"product_id": 2, "qty": 9}, {"product_id": 3, "qty": 17}]}'

The output below shows that the MySQL transaction has been completed.

 Success, Sales id is: 1

Attempt creating an order with an invalid date. For instance ..."order_date": "2021-111-311"....

 $ curl -i -X POST localhost:8080/sales_orders -H "Content-Type: application/json" -d '{"customer_id": 1, "order_date": "2021-111-311", "sales_products": [{"product_id": 1, "qty": 6}, {"product_id": 2, "qty": 9}, {"product_id": 3, "qty": 17}]}'


You should see the error below, and the transaction should be rolled back.


 Failed to create a sales order, transaction rolled back. Reason: Error 1292: Incorrect datetime value: '2021-111-311' for column 'order_date' at row 1

Again, attempt creating a new order with an invalid product_id value. For instance, xxx.

 $ curl -i -X POST localhost:8080/sales_orders -H "Content-Type: application/json" -d '{"customer_id": 1, "order_date": "2021-11-29", "sales_products": [{"product_id": "xxx", "qty": 6}]}'

You should get the error below showing the transaction failed, and the entire order has been rolled back.

 Failed to insert sales order product. Transaction rolled back. Reason: Error 1366: Incorrect integer value: 'xxx' for column 'product_id' at row 1

Connect to your database and check the state of your sales orders from the tables.
```
 $ sudo mysql -u root -p
```
Enter your root password for the database and press Enter to proceed. Then, switch to the store_db database.
```
 mysql> USE store_db;
```

Issue a JOIN statement against the sales_orders and sales_products tables.

 mysql> SELECT
            sales_orders.order_id,
            order_date,
            customer_id,
            sales_products.product_id,
            sales_products.qty
       FROM sales_orders
       LEFT JOIN sales_products
       ON sales_orders.order_id = sales_products.order_id;

You can now see that the first order with correct data was created in the database.

 +----------+---------------------+-------------+------------+------+
 | order_id | order_date          | customer_id | product_id | qty  |
 +----------+---------------------+-------------+------------+------+
 |        1 | 2021-11-29 00:00:00 |           1 |          3 |   17 |
 |        1 | 2021-11-29 00:00:00 |           1 |          2 |    9 |
 |        1 | 2021-11-29 00:00:00 |           1 |          1 |    6 |
 +----------+---------------------+-------------+------------+------+
 3 rows in set (0.00 sec)

Your application is now working as expected.

Conclusion

In this guide, you've implemented MySQL transactions with Golang on your Linux server.

Read more Golang tutorials by following the links below:

How to Implement MySQL Transactions with Golang On Linux Server

Introduction

Prerequisites

1. Create a Database, User Account, and Sample Tables

2. Create a `project` Directory and a `main.go` File

2. Create a `sales.go` File

3. Test the Golang MySQL Transaction Application

Conclusion

Products

Features

Marketplace

Resources

Company

How to Implement MySQL Transactions with Golang On Linux Server

Introduction

Prerequisites

1. Create a Database, User Account, and Sample Tables

2. Create a project Directory and a main.go File

2. Create a sales.go File

3. Test the Golang MySQL Transaction Application

Conclusion

2. Create a `project` Directory and a `main.go` File

2. Create a `sales.go` File