DB接続

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

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

単数

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
}

複数

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
 }

QueryとQueryRowの違い

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

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