Go程式新增一筆資料到PostgreSQL資料庫範例如下。
範例環境:
- Go 1.17
- PostgreSQL 14
PostgreSQL的postgres資料庫的employee
資料表如下。
CREATE TABLE IF NOT EXISTS employee (
id bigserial PRIMARY KEY,
name varchar(60) UNIQUE NOT NULL,
age integer,
created_at timestamp NOT NULL
);
在專案根目錄執行go get github.com/lib/pq
下載driver。
~/../go-demo$ go get github.com/lib/pq
下面程式連線到PostgresSQL資料庫並新增一筆資料至employee
資料表並回傳id
。RETURNING
用來返回INSERT
後自動產生的id
序號。
CreateEmployee()
中調用sql.Tx.QueryRow()
執行新增SQL並帶入參數,接著調用Row.Scan()
讀取id
欄位值返回。
sql.DB.Begin()
、sql.Tx.Rollback()
及sql.Tx.Commit()
為交易/事務管理範圍。
main.go
package main
import (
"database/sql"
"fmt"
"time"
_ "github.com/lib/pq"
)
type Employee struct {
ID int64
Name string
Age int
CreatedAt time.Time
}
const (
HOST = "localhost"
PORT = "5432"
DATABASE = "postgres"
USER = "admin"
PASSWORD = "12345"
SSL = "disable"
)
func connect() *sql.DB {
driver := "postgres"
dsn := fmt.Sprintf(
"host=%s port=%s user=%s password=%s dbname=%s sslmode=%s",
HOST, PORT, USER, PASSWORD, DATABASE, SSL)
db, err := sql.Open(driver, dsn)
if err != nil {
panic("open database error")
}
return db
}
func main() {
db := connect()
emp := Employee{
Name: "tony",
Age: 23,
}
id, err := CreateEmployee(db, &emp)
if err != nil {
panic(err)
}
fmt.Println(id)
}
func CreateEmployee(db *sql.DB, emp *Employee) (int64, error) {
tx, err := db.Begin()
if err != nil {
return 0, err
}
defer tx.Rollback()
id := int64(0)
query := `INSERT INTO employee (name, age, created_at)
VALUES ($1, $2, $3)
RETURNING id`
err = tx.QueryRow(query, &emp.Name, &emp.Age, time.Now()).Scan(&id)
if err != nil {
return 0, err
}
if err = tx.Commit(); err != nil {
return 0, err
}
return id, nil
}
沒有留言:
張貼留言