MariaDB query using Channels and Sync WaitGroups in Go

In the realm of concurrent programming, where efficiency and speed are paramount, Go, with its lightweight threads called goroutines and communication channels, emerges as a powerful tool.

In this technical journey, we will explore how to leverage Go’s concurrency primitives to query a MariaDB database efficiently. By employing goroutines to perform parallel database queries and channels to seamlessly communicate results, we’ll witness a performance boost.

Prerequisites:

1. Install Go and set up a Go development environment.
2. Have a running MariaDB instance with a database named mydb and 3 tables named transactions, orders, and widgets.

In this step-by-step tutorial, I will just make use of the mysql cli. So instead of opening any connections to MariaDB inside our code, I will just make use of the “os/exec” package. Of course, you can also make use of the sql package and just open a connection.

For the sake of this short tutorial, this seems straightforward.

Usual command for connecting to a mysql instance, is:

mysql -u <user> -p<pass>

I will show how to install mariadb (on macOS), create a database and grant all permissions to a specific user:

$ brew install mariadb
$ brew services start mariadb
$ sudo mysql -u root
create database mydb;
grant all on mydb.* to 'myusername'@'%' identified by 'secret';
exit;
$ mysql -u myusername -psecret
use mydb;

And this is exactly what we are going to implement in the below code.

Why would we want to use multithreading? Because it’s just fast. Imagine the following: you have a very large list of select queries that you were asked run, but you don’t know how much exact time is going to take for each execution. So you might have to wait 2 minutes for first SELECT, but 0.25ms for second SELECT. It’s pointless to wait for the first one to finish, and just then fire the second SELECT. So why not fire all of them concurrently?

Another example of popular use case is just by doing some GET requests on a list of websites. First website can respond in 0.2ms, but next one in 1 minute. So what we can do, is to fire all of the GET requests at once.

Step-by-Step Code:

1. Import Necessary Packages:

import (
 "fmt"
 "os/exec"
 "sync"
)

• Here we will just pass into a const the connection string, username, password and the database that we want to use.

const (
 connectionString = "someconnection_string"
 username         = "myuser"
 password         = "secret"
 database         = "transactions"
)

Our function which will execute the SQL query will be called `executeSQL`. As I mentioned in the beginning, we will just make use of the mysql cli.
Inside this function we will pass in as a parameter a channel of type string. We will use that channel to send information. For example, in case the command failed, we can send the string error. Else, we just print the output of our query.

func executeSQL(query string, username string, password string, database string, wg *sync.WaitGroup, ch chan string) {
 defer wg.Done()
 fmt.Printf("%s\n\n", query)

 cmd := exec.Command("mysql", "-u", username, "-p"+password, "--database", database, "-e", query)
 output, err := cmd.CombinedOutput()
 if err != nil {
  ch <- fmt.Sprintf("Error executing %s: %v\n", query, err)
 } else {
  ch <- string(output)
 }

}

This is our main function. Inside of this we will start with a slice of strings that will keep the selects that we want to run.Important thing to note here, is the order of my queries.
First we have the select from widgets.
Then from transactions.
Then from orders.
In a normal program execution, everything will run only on one thread, precisely main thread. This means that every query will be executed one by one. Once widgets is finished, it will move to transactions. When transactions is over, it'll move to orders.

func main() {
 queries := []string{
  "select * from widgets;",
  "select * from transactions;",
  "select * from orders;",
 }
 var wg sync.WaitGroup
 ch := make(chan string)

 for _, query := range queries {
  wg.Add(1)
  go executeSQL(query, username, password, database, &wg, ch)
 }
 go func() {
  wg.Wait()
  close(ch)
 }()

 for result := range ch {
  fmt.Println(result)
 }
}

Now let's break down the code for a bit.

1. var wg sync.WaitGroup: This line declares a variable wg of type sync.WaitGroup. The WaitGroup is used to wait for the completion of all goroutines.
2. ch := make(chan string): This line creates a channel ch of type string. Channels are used for communication between goroutines.
3. Loop through a list of SQL queries:

for _, query := range queries {
  wg.Add(1)
  go executeSQL(query, username, password, database, &wg, ch)
 }

• This loop iterates over a list of SQL queries stored in the queries slice.
• For each query, it increments the WaitGroup counter using wg.Add(1).
• It launches a new goroutine using go executeSQL(...). The executeSQL function is expected to perform the SQL query and send the result to the channel ch.

4. Launch a goroutine to close the channel after all queries are done:

go func() {
  wg.Wait()
  close(ch)
 }()

• This anonymous goroutine waits for all goroutines to finish by calling wg.Wait().
• After waiting, it closes the channel ch using close(ch) to signal that no more results will be sent.

5. Loop through the channel to receive and print results:

for result := range ch {
  fmt.Println(result)
 }

• This loop continuously receives values from the channel ch using range.
• As long as the channel is open and values are being sent, it prints each result to the console.

Output: And as you can see, besides the initial list of queries, because the transaction had much more data that had to be retrieved, it was printed last.

id      name    description     inventory_level price   created_at      updated_at      image   is_recurring    plan_id
1       Widget  A very nice widget.     10      1000    2023-11-15 19:39:18     2023-11-15 19:39:18     widget.png      0
2       Bronze Plan     Get three widgets for the price of two every month!     100000  2000    2021-07-08 00:00:00     2021-07-08 00:00:00    0price_1OD5kxCH1ETc2VX56rVPS057

id      widget_id       transaction_id  status_id       quantity        amount  created_at      updated_at      customer_id
1       1       5       1       0       1000    2023-11-16 09:48:38     2023-11-16 09:48:38     5
2       1       6       1       0       1000    2023-11-16 09:50:53     2023-11-16 09:50:53     6
3       1       7       1       0       1000    2023-11-16 10:10:15     2023-11-16 10:10:15     8
4       1       8       1       0       1000    2023-11-16 10:44:20     2023-11-16 10:44:20     9

id      amount  currency        last_four       bank_return_code        transaction_status_id   created_at      updated_at      expiry_month   expiry_year      payment_intent  payment_method
1       1000    cad     4242    ch_3OD1xTBnheRwo4ja1KXI09VP     2       2023-11-16 09:37:11     2023-11-16 09:37:11     0       0
2       1000    cad     4242    ch_3OD218BnheRwo4ja3i0n4RdY     2       2023-11-16 09:37:48     2023-11-16 09:37:48     0       0
3       1000    cad     4242    ch_3OD22MBnheRwo4ja1YjDU8z9     2       2023-11-16 09:39:05     2023-11-16 09:39:05     0       0
4       1000    cad     4242    ch_3OD27dBnheRwo4ja3kRhI9Np     2       2023-11-16 09:44:32     2023-11-16 09:44:32     0       0
5       1000    cad     4242    ch_3OD2BbBnheRwo4ja20xPnO8n     2       2023-11-16 09:48:38     2023-11-16 09:48:38     0       0
6       1000    cad     4242    ch_3OD2BbBnheRwo4ja20xPnO8n     2       2023-11-16 09:50:53     2023-11-16 09:50:53     0       0
7       1000    cad     4242    ch_3OD2WWBnheRwo4ja1JHI724w     2       2023-11-16 10:10:15     2023-11-16 10:10:15     4       2024    pi_3OD2WWBnheRwo4ja1y7ufXzk     pm_1OD2WXBnheRwo4jaCH33vFRT
8       1000    cad     4242    ch_3OD33VBnheRwo4ja0gWC4pLT     2       2023-11-16 10:44:20     2023-11-16 10:44:20     4       2024    pi_3OD33VBnheRwo4ja0aXR9P4N     pm_1OD33WBnheRwo4jaNcc8IafN
9       1000    cad     4242    ch_3OD3baBnheRwo4ja0LcQO3bW     2       2023-11-16 11:19:33     2023-11-16 11:19:33     4       2024    pi_3OD3baBnheRwo4ja0vzN6Rok     pm_1OD3bbBnheRwo4jad1igS7Bt

better a CORRECT program than a FAST but WRONG program!!!

Please keep in mind that this is not necessarily always the way to do it. We can encounter multiple problems while doing threading programming, such as:

1. bottlenecks (e.g. maybe the database is waiting for the previous request to finish, and then your goroutine will just get stuck)
2. race conditions: whenever multiple threads access the same data, there is a big risk of corrupting the data

Golang with it’s unique channels (and other things like WaitGroup, …) makes it easier to avoid race conditions than most other languages but that does not mean you should always write multi-threaded programs. It’s still more difficult than single-threaded because you have to be mindful about the synchronisation between the threads/goroutines.

That’s it! This code demonstrates how to use channels and Go routines to concurrently query a MariaDB database. Adjust the connection details and queries based on your specific database setup.