【GO言語】Postgresqlを使うサンプルコード


## DB接続
```golang: DB接続
dbHost := "127.0.0.1"
dbUser := "test"
dbPassword := "password"
dbDatabase := "sample_db"
dbConn := fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=disable", dbHost, dbUser, dbPassword, dbDatabase)
db, err := sql.Open("postgres", dbConn)
if err != nil {
    log.Fatal(err)

defer db.Close()
 ```

## INSERT
 ```golang: INSERT
 	article := models.Article{
		Title:    "insert test",
		Contents: "Can I insert data?",
		UserName: "Im",
	}

	const sqlStr = `
		INSERT INTO articles (title, contents, username, nice, created_at) 
		VALUES ($1, $2, $3, 0, now()) returning id;
	`

	var newArticleID int
	err = db.QueryRow(sqlStr, article.Title, article.Contents, article.UserName).Scan(&newArticleID)
	if err != nil {
		fmt.Println(err)
		return
	}

	fmt.Println(newArticleID)
 
//const sqlStr = `
//    INSERT INTO articles (title, contents, username, nice, created_at) 
//    VALUES ($1, $2, $3, 0, now());
//`


//PostgreSQLでは自動採番の値を返す仕組みがないからエラーになるらしい
//result, err := db.Exec(sqlStr, article.Title, article.Contents, article.UserName);
//if err != nil {
//	fmt.Println(err)
//	return
//}

//fmt.Println(result.LastInsertId())
//fmt.Println(result.RowsAffected())
```

## SELECT
### 単数
```golang
func SelectArticleDetail(db *sql.DB, articleID int) (models.Article, error) {
	const sqlStr = `
		SELECT *
		FROM articles
		WHERE id = $1;
	`

	row := db.QueryRow(sqlStr, articleID)
	if err := row.Err(); err != nil {
		return models.Article{}, err
	}

	var article models.Article
	var createdAt sql.NullTime

	err := row.Scan(&article.ID, &article.Title, &article.Contents, &article.UserName, &article.NiceNum, &createdAt)
	if err != nil {
		fmt.Println(err)
		return models.Article{}, err
	}

	if createdAt.Valid {
		article.CreatedAt = createdAt.Time
	}

	return article, nil
}
```
### 複数
```golang
func SelectArticleList(db *sql.DB, page int) ([]models.Article, error) {
	const sqlStr = `
		SELECT * FROM articles LIMIT 10 OFFSET $1;
	`
 rows, err := db.Query(sqlStr, page-1)
	if err != nil {
		fmt.Println(err)
		return nil, err
	}
	defer rows.Close()

	articleArray := make([]models.Article, 0)
	for rows.Next() {
		var article models.Article
		var createdAt sql.NullTime
		err := rows.Scan(&article.ID, &article.Title, &article.Contents, &article.UserName, &article.NiceNum, &createdAt)
		if createdAt.Valid {
			article.CreatedAt = createdAt.Time
		}
		if err != nil {
			fmt.Println(err)
			return nil, err
		} else {
			articleArray = append(articleArray, article)
		}
	}
	return articleArray, nil
 }
```


:::note info 
QueryとQueryRowの違い

結果が0件か1件の場合は、QueryRowを使うと簡潔に書ける。
Queryでも書けるが、記述が長くなる。
QueryRowとQueryの大きな違いは、結果が0件の場合、QueryRowはErrNoRowsというエラーになるが、
Queryの場合はエラーにならない。

ちなみに、0件かどうかはerrors.Is(err, sql.ErrNoRows)で比較できる
:::

<!--
# Test
## コントローラー層のテスト
- テストデータを作る
- サービスのモックを作る
- main_test.goを作る
```golang
package controllers_test

import (
	"sample_api/controllers"
	"sample_api/controllers/testdata"
	"testing"
)

// sut
var con *controllers.ArticleController

func TestMain(m *testing.M) {
	ser := testdata.NewServiceMock()
	con = controllers.NewArticleController(ser)

	m.Run()
}
```
- テストコードを作成する
```golang

func TestArticleListHandler(t *testing.T) {
	var tests = []struct {
		name       string
		query      string
		resultCode int
	}{
		{name: "number query", query: "1", resultCode: http.StatusOK},
		{name: "alphabet query", query: "aaa", resultCode: http.StatusBadRequest},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			url := fmt.Sprintf("http://localhost:8080/article/list?page=%s", tt.query)
			req := httptest.NewRequest(http.MethodGet, url, nil)
			res := httptest.NewRecorder()

			con.ArticleListHandler(res, req)
			if res.Code != tt.resultCode {
				t.Errorf("unexpected StatusCode: want %d but %d\n", tt.resultCode, res.Code)
			}
		})
	}
}
```
-->