This article was originally published on GitHub.
setup
$ docker run -d --name docker-postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:alpine
...
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d9065d8c1f30 postgres:alpine "docker-entrypoint.s…" 3 seconds ago Up 4 seconds 0.0.0.0:5432->5432/tcp docker-postgres
Connect PostgreSQL
$ touch connect.go
$ go get -u github.com/lib/pq
// connect.go
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
func main() {
// connStr := "user=postgres dbname=postgres sslmode=disable"
// db, err := sql.Open("postgres", connStr)
// [user]:[password]@[address]/[DB name]
connStr := "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
db, err := sql.Open("postgres", connStr)
if err != nil {
fmt.Println(err)
return
}
defer db.Close()
err = db.Ping()
if err != nil {
fmt.Println(err)
return
}
fmt.Println("Ping OK")
}
$ go run connectdb.go
Ping OK
Create Tables
$ touch queries.go
// queries.go
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
const createTable = `
DROP TABLE IF EXISTS ACCOUNT;
CREATE TABLE ACCOUNT
(
ID serial,
NAME varchar(50),
MAIL_ADDRESS varchar(50),
LANG varchar(5)
);
`
func main() {
db, err := connectPostgres()
if err != nil {
return
}
defer db.Close()
if _, err = db.Exec(createTable); err != nil {
fmt.Println(err)
return
}
if _, err = db.Exec("DROP TABLE ACCOUNT"); err != nil {
fmt.Println(err)
return
}
}
func connectPostgres() (*sql.DB, error) {
connStr := "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
db, err := sql.Open("postgres", connStr)
if err != nil {
fmt.Println(err)
return nil, err
}
err = db.Ping()
if err != nil {
fmt.Println(err)
return nil, err
}
fmt.Println("Ping OK")
return db, nil
}
$ go run queries.go
Ping OK
Exercise 3-1
Add a function insertAccount()
into queries.go
. It inserts records which contain data of any static accounts to the account
table by inside a transaction.
// queries.go
func main() {
db, err := connectPostgres()
if err != nil {
return
}
defer db.Close()
if _, err = db.Exec(createTable); err != nil {
fmt.Println(err)
return
}
if err := insertAccounts(db); err != nil {
fmt.Println(err)
return
}
if _, err = db.Exec("DROP TABLE ACCOUNT"); err != nil {
fmt.Println(err)
return
}
}
// You should implement the function
func insertAccounts(db *sql.DB) error {}
HINTS: You can create a transaction with DB.begin
and commit it with Tx.Commit
.
Exercise 3-2
Add a function getAccounts()
into queries.go
. It returns all records in the account
table.
// queries.go
if err := insertAccounts(db); err != nil {
fmt.Println(err)
return
}
accounts, err := getAccounts(db)
if err != nil {
fmt.Println(err)
return
}
fmt.Printf("%+v\n", accounts)
...
// You should implement the function and define the Account struct
func getAccounts(db *sql.DB) ([]Account, error) {}
$ go run queries.go
Ping OK
[{ID:1 Name:My Name MailAddress:my_name@example.com Lang:ja} {ID:2 Name:Your Name MailAddress:your_name@example.com Lang:en}]
HINTS: You can execute SELECT
queries with DB.Query
and get the results with Rows.Scan
.
The answer is queries.go.
Top comments (0)