03

3. Connect DB and Execute queries

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.

PREV | NEXT