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.
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.
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.)
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.
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.
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
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.
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.
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