MENU
アイデアを形にする( プログラミング / クラウド / フレームワーク / ライブラリ / デザイン / IT豆知識 )
  1. ホーム
  2. Go
  3. database/sqlによるデータベース操作

database/sqlによるデータベース操作

Go

標準パッケージの「database/sql」を利用して、データベースを操作する方法を確認します。

「SELECT / INSERT / UPDATE / DELETE などのクエリを実行する方法」、
「トランザクションの活用方法」、
「最大コネクション数の設定方法」などを取り上げます。

目次

動作確認環境の準備

DockerでMySQL立ち上げ

DockerでMySQLを立ち上げて動作確認します。

docker-compose.yml に以下内容を記載します。

version: '3'

services:
  db-go-database-sql:
    image: mysql:5.7
    container_name: db-go-database-sql
    ports:
      - "13306:3306"
    volumes:
      - ./data/db:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: test_db
      MYSQL_USER: test_user
      MYSQL_PASSWORD: test_password

docker-compose up -d を実行して、コンテナを立ち上げます。

DB・Table作成

立ち上げたDBに接続します。
(パスワードは docker-compose.yml を参照)

mysql -utest_user -h 127.0.0.1 --port 13306 -p

動作確認用に、テーブルの作成とレコード挿入を行います。

USE test_db;

CREATE TABLE users
(
    id         INTEGER NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name  VARCHAR(50),
    age        INTEGER,
    created    DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE posts
(
    id       INTEGER NOT NULL AUTO_INCREMENT,
    user_id  INTEGER NOT NULL,
    content  TEXT    NOT NULL,
    created  DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (first_name, last_name, age) 
VALUES 
    ("りな", "みかみ", 43),
    ("じゅん", "くさの", 34),
    ("ひでき", "やまだ", 23);

database/sqlによるDB操作

DBオープン
( Open / Close )

DBの種類ごとに database/sql/driver のインタフェースを実装したDriverをインポートする必要があります。

今回DBにMySQLを利用するので、importに _ "github.com/go-sql-driver/mysql" を記載します。
_(アンダーバー) と指定している理由は、github.com/go-sql-driver/mysql の init関数 のみ実行させたいためです。

package main

import (
	"database/sql"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "test_user:test_password@tcp(127.0.0.1:13306)/test_db")
	if err != nil {
		log.Fatalf("main sql.Open error err:%v", err)
	}
	defer db.Close()
}

Open関数で取得した sql.DB構造体のインスタンス は、コネクションプールを管理しています。

このインスタンスを通じて操作することにより、以下のようにコネクションを活用できます。

  • 利用可能なコネクションがなければ新たにコネクションを作成(設定や、利用中コネクション数によります)
  • アイドルコネクションがあれば、アイドルを活用
  • 複数のgoroutineで同時使用した場合にも安全に利用可能

コネクションの最大数などの設定は後述します。

SELECT
( Query / QueryRow )

SELECTでレコードを取得したい場合、Queryメソッド QueryRowメソッド を活用できます。
( QueryContextメソッド などもあります。)

  • Queryメソッド
    • 複数レコードを取得したいときに活用できます。
    • 戻り値(rows)
      • rows.Next() を活用することで、各レコードに対して操作できます。
      • rows.Scan() を活用することで、引数に渡したポインタにレコードの内容を読み込ませることができます。
  • QueryRowメソッド
    • 1レコードだけ取得したいときに活用できます。
    • エラー
      • レコードが存在しないとき、sql.ErrNoRows のエラーを返します。
package main

import (
	"database/sql"
	"errors"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

type User struct {
	ID        int
	FirstName string
	LastName  string
	Age       string
	Created   time.Time
	Updated   time.Time
}

func main() {
	db, err := sql.Open("mysql", "test_user:test_password@tcp(127.0.0.1:13306)/test_db?parseTime=true&loc=Asia%2FTokyo")
	if err != nil {
		log.Fatalf("main sql.Open error err:%v", err)
	}
	defer db.Close()

	fmt.Println("------------------")
	getRows(db)
	fmt.Println("------------------")
	getSingleRow(db, 1)
	fmt.Println("------------------")
	getSingleRow(db, 4) // 存在しないUserID
}

func getRows(db *sql.DB) {
	rows, err := db.Query("SELECT * FROM users")
	if err != nil {
		log.Fatalf("getRows db.Query error err:%v", err)
	}
	defer rows.Close()

	for rows.Next() {
		u := &User{}
		if err := rows.Scan(&u.ID, &u.FirstName, &u.LastName, &u.Age, &u.Created, &u.Updated); err != nil {
			log.Fatalf("getRows rows.Scan error err:%v", err)
		}
		fmt.Println(u)
	}

	err = rows.Err()
	if err != nil {
		log.Fatalf("getRows rows.Err error err:%v", err)
	}
}

func getSingleRow(db *sql.DB, userID int) {
	u := &User{}
	err := db.QueryRow("SELECT * FROM users WHERE id = ?", userID).
		Scan(&u.ID, &u.FirstName, &u.LastName, &u.Age, &u.Created, &u.Updated)
	if errors.Is(err, sql.ErrNoRows) {
		fmt.Println("getSingleRow no records.")
		return
	}
	if err != nil {
		log.Fatalf("getSingleRow db.QueryRow error err:%v", err)
	}
	fmt.Println(u)
}
------------------
&{1 りな みかみ 43 2022-04-30 14:48:05 +0900 JST 2022-04-30 14:48:10 +0900 JST}
&{2 じゅん くさの 34 2022-04-30 14:48:05 +0900 JST 2022-04-30 14:48:11 +0900 JST}
&{3 ひでき やまだ 23 2022-04-30 14:48:05 +0900 JST 2022-04-30 14:48:12 +0900 JST}
------------------
&{1 りな みかみ 43 2022-04-30 14:48:05 +0900 JST 2022-04-30 14:48:10 +0900 JST}
------------------
getSingleRow no records.

プレースホルダー

パラメータのプレースホルダー( "SELECT * FROM users WHERE id = ?" の ? の部分 )は利用するDBによって異なります。
今回、MySQLを利用したので ? を指定しています。

time.Time型の利用

sql.Openの第二引数も修正しています。( ?parseTime=true&loc=Asia%2FTokyo を追加 )

parseTime=true を追加しないと以下エラーが発生するためです。

sql: Scan error on column index 4, name "created": unsupported Scan, storing driver.Value type []uint8 into type *time.Time

time.Time型 を利用したいときは、指定必要です。

※参考
https://github.com/go-sql-driver/mysql#parsetime

INSERT / UPDATE / DELETE
( Exec )

INSERT / UPDATE / DELETE といったクエリを実行したい場合、 Execメソッド を活用できます。

package main

import (
	"database/sql"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "test_user:test_password@tcp(127.0.0.1:13306)/test_db?parseTime=true&loc=Asia%2FTokyo")
	if err != nil {
		log.Fatalf("main sql.Open error err:%v", err)
	}
	defer db.Close()

	userID := insertUser(db, "さとし", "やまだ", 27)
	insertPost(db, userID, "hello world")
}

func insertUser(db *sql.DB, firstName, lastName string, age int) int64 {
	res, err := db.Exec(
		"INSERT INTO users (first_name, last_name, age) VALUES (?, ?, ?)",
		firstName,
		lastName,
		age,
	)
	if err != nil {
		log.Fatalf("insertUser db.Exec error err:%v", err)
	}

	id, err := res.LastInsertId()
	if err != nil {
		log.Fatalf("insertUser res.LastInsertId error err:%v", err)
	}
	return id
}

func insertPost(db *sql.DB, userID int64, content string) int64 {
	res, err := db.Exec("INSERT INTO posts (user_id, content) VALUES (?, ?)",
		userID,
		content,
	)
	if err != nil {
		log.Fatalf("insertPost db.Exec error err:%v", err)
	}

	id, err := res.LastInsertId()
	if err != nil {
		log.Fatalf("insertPost res.LastInsertId error err:%v", err)
	}
	return id
}

Transaction
( Begin / Commit / Rollback )

先述の例を、トランザクションを利用した形で書き直してみます。

package main

import (
	"database/sql"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "test_user:test_password@tcp(127.0.0.1:13306)/test_db?parseTime=true&loc=Asia%2FTokyo")
	if err != nil {
		log.Fatalf("main sql.Open error err:%v", err)
	}
	defer db.Close()

	transaction(db)
}

func transaction(db *sql.DB) {
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}
	defer func() {
		if err := recover(); err != nil {
			if err := tx.Rollback(); err != nil {
				log.Fatalf("transaction rollback error err:%v", err)
			}
		}
	}()

	userID, err := insertUserTx(tx, "さとし", "やまだ", 27)
	if err != nil {
		if err := tx.Rollback(); err != nil {
			log.Fatalf("transaction rollback error err:%v", err)
		}
		log.Fatalf("transaction insertUserTx error err:%v", err)
	}

	_, err = insertPostTx(tx, *userID, "hello world")
	if err != nil {
		if err := tx.Rollback(); err != nil {
			log.Fatalf("transaction rollback error err:%v", err)
		}
		log.Fatalf("transaction insertPostTx error err:%v", err)
	}

	if err := tx.Commit(); err != nil {
		log.Fatalf("transaction commit error err:%v", err)
	}
}

func insertUserTx(tx *sql.Tx, firstName, lastName string, age int) (*int64, error) {
	res, err := tx.Exec(
		"INSERT INTO users (first_name, last_name, age) VALUES (?, ?, ?)",
		firstName,
		lastName,
		age,
	)
	if err != nil {
		return nil, err
	}

	id, err := res.LastInsertId()
	if err != nil {
		return nil, err
	}
	return &id, nil
}

func insertPostTx(tx *sql.Tx, userID int64, content string) (*int64, error) {
	res, err := tx.Exec("INSERT INTO posts (user_id, content) VALUES (?, ?)",
		userID,
		content,
	)
	if err != nil {
		return nil, err
	}

	id, err := res.LastInsertId()
	if err != nil {
		return nil, err
	}
	return &id, nil
}

コネクション数、ライフタイムの設定

動作確認用のテーブル追加

時間のかかるクエリを作りたいため、動作確認用のテーブルを追加します。

CREATE TABLE `tests` (
  `content` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `tests`
    (`content`)
VALUES
    ('xxx'),
    ('xxx'),
    ('xxx'),
    ('xxx'),
    ('xxx'),
    ('xxx'),
    ('xxx'),
    ('xxx'),
    ('xxx'),
    ('xxx');

INSERT INTO `tests` (`content`)
SELECT `t1`.`content` FROM `tests` t1, `tests` t2, `tests` t3, `tests` t4, `tests` t5, `tests` t6;

testsテーブル に1000010レコードを登録しました。

mysql> SELECT COUNT(*) FROM tests;
+----------+
| COUNT(*) |
+----------+
|  1000010 |
+----------+
1 row in set (1.93 sec)

動作確認

  • SetConnMaxLifetimeメソッド
    • コネクションを利用できる期間を設定できます。
    • 長すぎるとDB側から接続が切られている可能性があります。(MySQLだと wait_timeout などの設定が影響します。)
  • SetMaxOpenConnsメソッドSetMaxIdleConnsメソッド
    • コネクション数の上限を設定できます。

下記コードでコネクション数の上限設定によって、処理時間にどういった影響がでるのかを確認します。

package main

import (
	"database/sql"
	"fmt"
	"log"
	"strconv"
	"sync"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "test_user:test_password@tcp(127.0.0.1:13306)/test_db?parseTime=true&loc=Asia%2FTokyo")
	if err != nil {
		log.Fatalf("main sql.Open error err:%v", err)
	}
	defer db.Close()

	fmt.Printf("%+v\n", db.Stats())
	db.SetConnMaxLifetime(time.Minute * 1)
	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)
	fmt.Printf("%+v\n", db.Stats())

	var wg sync.WaitGroup
	s := time.Now()
	for i := 0; i < 2; i++ {
		wg.Add(1)
		go request(&wg, db, i)
	}
	wg.Wait()
	e := time.Now()
	fmt.Printf("処理秒数: %v\n", e.Sub(s).Round(time.Millisecond))
}

func request(wg *sync.WaitGroup, db *sql.DB, i int) {
	defer wg.Done()

	fmt.Printf("[request start] i: %v\n", i)
	defer fmt.Printf("[request end] i: %v\n", i)

	rows, err := db.Query("SELECT * FROM tests")
	if err != nil {
		log.Fatalf("request db.Query error err:%v", err)
	}
	defer rows.Close()
}

最大コネクション数が1のとき

最大コネクションが1なので、コネクションが空くまで待ち時間が発生します。

{MaxOpenConnections:0 OpenConnections:0 InUse:0 Idle:0 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
{MaxOpenConnections:1 OpenConnections:0 InUse:0 Idle:0 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
[request start] i: 1
[request start] i: 0
[request end] i: 1
[request end] i: 0
処理秒数: 9.034s

最大コネクション数が2のとき

以下のように修正して再確認してみます。

	db.SetMaxOpenConns(2)
	db.SetMaxIdleConns(2)

最大コネクションが2なので、コネクションの空き待ちが発生せず、処理時間が短くなりました。

{MaxOpenConnections:0 OpenConnections:0 InUse:0 Idle:0 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
{MaxOpenConnections:2 OpenConnections:0 InUse:0 Idle:0 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
[request start] i: 1
[request start] i: 0
[request end] i: 0
[request end] i: 1
処理秒数: 4.915s

参考

Go
よかったらシェアしてね！

関連記事

目次