[DOCKER] Operate Db2 container with Go

Hello. This time, I will introduce how to operate a Db2 container set up with Docker with Go. Please refer to the following articles for information on how to set up a Db2 container and how to insert data during setup.

Set up a Db2 DB container and insert a little data Setup with initial test data inserted in Db2 / DB container

This time, we will start up the Db2 container with data inserted and introduce mainly the implementation in Go.

Codes can be found at here.

Overview

The content is for those who have been able to insert data into the Db2 container, but how to actually get the data and operate it or update the data.

This time I will introduce how to fetch data from Db2 in Go language.

Development environment

Premise

Getting Started The development environment is Windows, but it can be done on Mac or Linux.

This time, we are focusing on confirming communication with Db2, so we have not made it into an API. I will write a program that simply fetches data from Db2 and outputs it to the console. (Someday I will also introduce the REST API implementation in Go.)

1. Explanation of folder structure

First, I will explain the folder structure.

project


project
├─go
|  ├─model
|  |     ├─user.go
|  |     ├─tweet.go
|  |     └─reply.go
|  └─main.go
└─db  
   ├─data
   |    ├─users_insert.csv
   |    ├─tweets_insert.csv
   |    └─replys_insert.csv
   ├─sql
   |   ├─users_create.sql
   |   ├─tweets_create.sql
   |   └─replys_create.sql
   ├─createschema.sh
   ├─Dockerfile
   └─env.list

Actually, I would like to create a domain-driven design, user domain, infrastructure, etc. and make a cool design, but that is another opportunity.

2. Launching the container

First, build the container image using Dockerfile. The command to execute is as follows.

$ cd db
$ docker build -t test-db:v1.0 .

Now that the container image is complete, let's run it immediately.

$ docker run --name go-db --restart=always --detach --privileged=true -p 50000:50000 --env-file env.list test-db:v1.0

Detailed explanation is introduced at here.

The important thing here is that the port is port forwarded at 50000: 50000. Keep in mind that the 50000 port exposed to the client must be specified when connecting to the DB.

3. Package to import

Package to use

3.1. go_ibm_db 基本的にGoでDb2を利用する際は、github.com/ibmdb/go_ibm_dbというパッケージを利用します。

Hit the following command.

$ go get github.com/ibmdb/go_ibm_db

In addition, a driver for operating SQL is required to operate the database. Since there are various operations, I will do it in order.

まず、落としてきたgithub.com/ibmdb/go_ibm_dbを見に行きます。 Probably it is dropped under GOPATH, so if you go down this hierarchy, you will hit a folder called ʻinstaller. The setup.go` in this folder is the clidriver download script.

$ cd PathToInstaller/installer
$ go run setup.go

Now clidriver can be downloaded under ʻinstaller`. (If you get a permission error, try changing the permissions on the installer folder.) I feel that it will take some time.

If you can drop it safely, you need to pass the path of PathToInstaller / installer / clidriver / bin, so let's pass it. This completes the go_ibm_db setup.

If you don't want to drop extra packages into your environment, you can do it with go mod. However, even in that case, sqlcli.h is required, so copy the installed installer to the project, pass the path of clidriver / bin in a shell script, etc., and specify the module to build. You can generate an executable file by doing.

3.2. errors It also implements errors, so drop the ʻerrors` package as well.

$ go get github.com/pkg/errors

4. Implementation of Go

Basically the implementation is really as introduced in 3. I will introduce it while looking at the main function of main.go.

First this code

main.go


  config := "HOSTNAME=localhost;DATABASE=USERDB;PORT=50000;UID=db2inst1;PWD=password"
	conn, err := sql.Open("go_ibm_db", config)
	if err != nil {
		fmt.Printf("Failed to connect to DB.%+v", err)
	}
	defer conn.Close()

Store the DB connection information in config. Except for HOSTNAME and PORT, use the information on env.list. Make a connection with DB with sql.Open below it. The first argument specifies the driver name. This time it is go_ibm_db. The second argument specifies the DB connection information. Since you can take an error, you must handle the error. The connection must always be closed, so use Go's practice defer to close the connection.

Now you have a connection with the Db2 container. We will use this to manipulate the data.

First, we get all the users, store the information in the user structure, and create an array of instances.

main.go


users, err := model.GetAllUser(conn)
if err != nil {
  fmt.Printf("Failed to get%+v", err)
}

Now let's look at user.go, which defines the user DAO and DTO.

user.go


// User is users entity
type User struct {
	id        string
	name      string
	mail      string
	password  string
	createdAt time.Time
	updatedAt time.Time
}

func (u *User) String() string {
	return fmt.Sprintf(
		"username:%s",
		u.name,
	)
}

// GetID returns user's id
func (u *User) GetID() string {
	return u.id
}

The user structure defines table-defined columns in the fields. The GetID method is a method to get the user's ID. I'm writing this because the fields in the user struct are privately specified to pass the ID to queries in other tables. Well, I think that this area will do similar things in other languages.

Below that, there is a method to get all users,

user.go


// GetAllUser returns all user instances
func GetAllUser(conn *sql.DB) ([]User, error) {
	selectAllUserQuery := `SELECT * FROM users`

	selectAllUserPstmt, err := conn.Prepare(selectAllUserQuery)
	if err != nil {
		return []User{}, errors.Wrapf(err, "Statement creation failed")
	}

	var users []User

	rows, err := selectAllUserPstmt.Query()
	if err != nil {
		return []User{}, errors.Wrap(err, "Query execution failed")
	}
	for rows.Next() {
		var user User
		if err := rows.Scan(
			&user.id,
			&user.name,
			&user.mail,
			&user.password,
			&user.createdAt,
			&user.updatedAt,
		); err != nil {
			return []User{}, errors.Wrap(err, "Result reading failure")
		}
		users = append(users, user)
	}
	return users, nil
}

There are various ways to write it here, but after preparing the statement with the Prepare () method, write it by executing the query.

When you do this, the retrieved records will be stored in rows. rows has a Next method, and you can turn each record with a for statement. Furthermore, if you pass the user instance information to rows.Scan (), the record information will be stored there.

You have now stored your user information in your user instance. Returns an array of users.

Let's go back to main.

From now on, I'm fetching the ID from the user instance, passing it to the WHERE clause of Tweet, and fetching the record associated with the user. The ID is further fetched from the fetched tweet record, the reply associated with it is fetched and output, and it is processed for the user record.

main.go


//Since the number of cases is small, use a triple for statement.
	for _, user := range users {
		fmt.Println(user.String())
		tweets, err := model.GetAllTweets(conn, user.GetID())
		if err != nil {
			fmt.Printf("Failed to get%+v", err)
		}
		for _, tweet := range tweets {
			fmt.Println(tweet.String())
			replys, err := model.GetAllReplys(conn, tweet.GetID())
			if err != nil {
				fmt.Printf("Failed to get", err)
			}
			for _, reply := range replys {
				fmt.Println(reply.String())
			}
		}
	}

In order to pass the ID to the WHERE clause, the SQL statement should be? , Such asSELECT * FROM Tweets WHERE user_id =?. You can customize the WHERE clause by giving a second argument for each parameter.

How to write rows, err := selectAllTweetPstmt.Query(userID) It looks like this.

5. Execution result

When executed on Windows, the Japanese part will be displayed as garbled characters when the value is received from the container. Since the container used in Db2 is a Linux container, it seems that it is caused by the character string being sent with the character code as UTF-8.

The execution result is as follows.

username:hoge
Tweet body:�����̓e�X�g�ł��B,Created date:2020-10-09 12:00:00 +0900 JST
Reply username:fugaaaa,Reply text:�e�X�g�m�F���܂����B,Created date:2020-10-11 12:00:00 +0900 JST
-----------------------
username:fuga
Tweet body:�����̓e�X�g�ł��B,Created date:2020-10-10 12:00:00 +0900 JST
Reply username:hogeeee,Reply text:�e�X�g�m�F���܂����B,Created date:2020-10-11 12:00:00 +0900 JST
-----------------------

Well, the characters are garbled. sad. That's why I will post the result of running on Mac.

username:hoge
Tweet body:This is a test.,Created date:2020-10-09 12:00:00 +0900 JST
Reply username:fugaaaa,Reply text:I confirmed the test.,Created date:2020-10-11 12:00:00 +0900 JST
-----------------------
username:fuga
Tweet body:This is a test.,Created date:2020-10-10 12:00:00 +0900 JST
Reply username:hogeeee,Reply text:I confirmed the test.,Created date:2020-10-11 12:00:00 +0900 JST
-----------------------

Like this, I can get it from Db2.

6. Summary

I introduced the method of connecting to the Db2 container with Go, despite the harmful effects of the character code.

With this, API development can be done easily.

Recommended Posts

Operate Db2 container with Go
Operate Linux Network Namespace with Go
Python with Go
Environment construction with VSCode + Remote Container (Go / Application)
Operate Kinesis with Python
Operate Blender with Python
Operate Excel with Python (1)
Operate Excel with Python (2)
Try to operate DB with Python and visualize with d3
Operate Excel with Python openpyxl
Draw Bezier curves with Go
Operate TwitterBot with Lambda, Python
Getting Started with Go Assembly
Bit full search with Go
Connect to Postgresql with GO
[Note] Operate MongoDB with Python
Hot reload with Go + Air
Operate your website with Python_Webbrowser
Try implementing perfume with Go
[Python] [SQLite3] Operate SQLite with Python (Basic)
ROS course 105 Operate toio with ROS
Operate Nutanix with REST API Part 2
Learn algorithms with Go @ recursive call
GUI development with Fyne by Go
Try to operate Facebook with Python
Go see whales with combinatorial optimization
Tips for running Go with docker
Upload & move GCS files with Go
Using Lambda with AWS Amplify with Go
Realize DB connection pool with golang
Operate ECHONET Lite appliances with Python
Using cgo with the go command
Operate Maya with OSC from vvvv
Metaprogram go with YAML + Mustache + go-generate
Use curl / jq library with Go