GoのORM入門(go-pg ハンズオン) #Go

go-pgのチュートリアル

開発環境の説明

開発環境

PC：Mac(CPUはintel製)
Go：1.17.6 ←Goのver大事。versionは1.16以降をインストールしてください。
開発エディタ：Visual Studio Code
go-pg:　v10.10.6

ディレクトリ構成

~/go/src/go_pg_lesson $ tree -I ".data|schema"  
.
├── db
│   └── migrations
│       ├── 000001_create_users_table.down.sql
│       └── 000001_create_users_table.up.sql
├── docker-compose.yml
├── go.mod
├── go.sum
└── main.go

2 directories, 6 files

docker-compose.yml

version: '3.1'

services:
  postgres:
    container_name: postgres
    image: postgres:13
    ports:
      - 5432:5432
    volumes:
      - ./.data/postgres:/var/lib/postgresql/data
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres

Lesson1: CRUD処理の基本操作

1. TBLの準備

TBL作成

CREATE TABLE IF NOT EXISTS users(
   id serial PRIMARY KEY,
   user_name VARCHAR (256) NOT NULL,
   password VARCHAR (256) NOT NULL,
   email VARCHAR (256) NOT NULL
);

※go-migrationでマイグレーション実施する場合は下記でいけます。気になる人は記事を読んでみてね！！

2. アプリケーション作成

アプリケーション作成

package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson1()
}

func Lesson1() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()
    user1 := &User{
        Name:   "admin",
		Password: "password",
        Email:  "admin1@admin",
    }
    _, err := db.Model(user1).Insert()
    if err != nil {
        panic(err)
    }

	users := &[]User{}

    err = db.Model(users).Select()
    if err != nil {
        panic(err)
    }

    fmt.Println(users)
}

3. アプリケーション実行

アプリケーション実行（1回目）

~/go/src/go_pg_lesson $ go run main.go
&[{1 admin password admin1@admin}]

DB状態の確認

example=# select * from users;
 id | user_name | password |    email     
----+-----------+----------+--------------
  1 | admin     | password | admin1@admin
(1 row)

もう一回実行。

アプリケーション実行（2回目）

~/go/src/go_pg_lesson $ go run main.go
&[{1 admin password admin1@admin} {2 admin password admin1@admin}]

アプリケーション実行（3回目）

~/go/src/go_pg_lesson $ go run main.go
&[{1 admin password admin1@admin} {2 admin password admin1@admin} {3 admin password admin1@admin}]

これでいけますね！！！

Lesson2: Where句の設定

Lesson2-1. 単一Where句

ソース

package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson2_1()
}

func Lesson2_1() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	user := new(User)
	err := db.Model(user).Where("id = ?", 1).Select()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(user)
}

実行結果

~/go/src/go_pg_lesson $ go run main.go
&{1 admin password admin1@admin}

Lesson2-2. 複数Where句(OR文)

ソース

package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson2_2()
}

func Lesson2_2() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	user := new([]User)
	err := db.Model(user).WhereOr("id = ?", 1).WhereOr("id = ?", 2).Select()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(user)
}

実行結果

~/go/src/go_pg_lesson $ go run main.go
&[{1 admin password admin1@admin} {2 admin password admin1@admin}]

Lesson2-3. 複数Where句(AND文)

ソース

package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson2_3()
}

func Lesson2_3() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	user := new([]User)
	err := db.Model(user).Where("id > ?", 1).
    Where("user_name = ?", "admin2").Select()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(user)
}

実行結果

~/go/src/go_pg_lesson $ go run main.go
&[{2 admin2 password admin2@admin}]

Lesson2-4. 複数要素に含まれる場合

ソース

package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson2_4()
}

func Lesson2_4() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	user := new([]User)

    ids := []int{1, 2}
	err := db.Model(user).Where("id in (?)", pg.In(ids)).
    Select()

	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(user)
}

実行結果

~/go/src/go_pg_lesson $ go run main.go
&[{1 admin1 password admin1@admin} {2 admin2 password admin2@admin}]

Lesson2-5. あいまい検索

○検索条件：user_nameがadmin始まりのレコードを取得する。
○挿入データ

data

example=# select * from users;
 id | user_name | password |    email     
----+-----------+----------+--------------
  2 | admin2    | password | admin1@admin
  1 | admin1    | password | admin1@admin
  3 | kouji     | password | admin1@admin
(3 rows)

ソース

package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson2_5()
}

func Lesson2_5() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	user := new([]User)
	err := db.Model(user).Where("user_name LIKE ?", "admin%").Select()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(user)
}

実行結果

~/go/src/go_pg_lesson $ go run main.go
&[{2 admin2 password admin1@admin} {1 admin1 password admin1@admin}]

Lesson3: 発展SQL(JOIN、GroupBy)

Lesson3-1. 2つのTBLをJOINして結果を取得する。

TBL変更

実行SQL

CREATE TABLE IF NOT EXISTS users(
   id serial PRIMARY KEY,
   name VARCHAR (256) NOT NULL,
   email VARCHAR (256) NOT NULL
);
CREATE TABLE IF NOT EXISTS stories(
   id serial PRIMARY KEY,
   title VARCHAR (256) NOT NULL,
   authorId INT NOT NULL
);

○挿入データ

data

example=# select * from users join stories on users.id = stories.authorId;
 id |   name    |        email        | id |   title    | authorid 
----+-----------+---------------------+----+------------+----------
  1 | kouji     | kouji@email.com     |  1 | ワンピース |        1
  2 | kkfactory | kkfactory@gmail.com |  2 | ヒロアカ   |        2
(2 rows)

ソース

package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"name"`
    Email string `json:"email" pg:"email"`
}

func (u User) String() string {
    return fmt.Sprintf("User<%d %s %v>", u.Id, u.Name, u.Email)
}

type Story struct {
    Id       int64 `json:"id" pg:"id"`
    Title    string `json:"title" pg:"title"`
    AuthorId int64 `json:"authorid" pg:"authorid"`
    Author   *User `json:"author" pg:"rel:has-one, fk:authorid"`
}

func (s Story) String() string {
    return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author)
}


func main(){
	ExampleDB_Model()
}

func ExampleDB_Model() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

    // Select all users.
    var users []User
    err := db.Model(&users).Select()
    if err != nil {
        panic(err)
    }

	var story []Story
    // story := new(Story)
    err = db.Model(&story).
        Relation("Author").
        Select()
    if err != nil {
        panic(err)
    }

    fmt.Println(users)
    fmt.Println(story)
}

実行結果

~/go/src/go_pg_lesson $ go run main.go                                                                                                                                     
[User<1 kouji kouji@email.com> User<2 kkfactory kkfactory@gmail.com>]
[Story<1 ワンピース User<1 kouji kouji@email.com>> Story<2 ヒロアカ User<2 kkfactory kkfactory@gmail.com>>]

○学習のPOINT

リレーションを貼る！！！

type Story struct {
    Id       int64 `json:"id" pg:"id"`
    Title    string `json:"title" pg:"title"`
    AuthorId int64 `json:"authorid" pg:"authorid"`
    Author   *User `json:"author" pg:"rel:has-one, fk:authorid"`
}

Author   *User `json:"author" pg:"rel:has-one, fk:authorid"`　これのようにリレーションを貼る必要があります。
今回は1対１のリレーションであるため、「rel:has-one」と定義。 fk:authoridを結合時のキーとなります。今回の場合だと、stories.authorid=users.idで連結しています！！

Lesson3-2. 2つのTBLをJOINして結果を取得する。

実行SQL

CREATE TABLE IF NOT EXISTS users(
   id serial PRIMARY KEY,
   user_name VARCHAR (256) NOT NULL,
   password VARCHAR (256) NOT NULL,
   email VARCHAR (256) NOT NULL,
   department_no INT NOT NULL
);
CREATE TABLE IF NOT EXISTS department(
   id serial PRIMARY KEY,
   department_name VARCHAR (256) NOT NULL
);
ALTER TABLE "users" ADD FOREIGN KEY ("department_no") REFERENCES "department" ("id") ON DELETE SET NULL ON UPDATE NO ACTION;

作成後のER図

○挿入データ

data

example=# select * from users join department on users.department_no = department.id;
 id | user_name |  password  |   email   | department_no | id | department_name 
----+-----------+------------+-----------+---------------+----+-----------------
  1 | kouji     | password01 | gmail.com |             1 |  1 | システム開発部
  2 | kkfactory | password01 | gmail.com |             2 |  2 | システム運用部
(2 rows)

ソース

package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
)

type User struct {
	tableName struct{} `pg:"users"`
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
    Password   string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
	DepartmentNo int64 `json:"department_no" pg:"department_no"`
	Department *Department `json:"department" pg:"rel:has-one, fk:department_no"`
}

func (u User) String() string {
    return fmt.Sprintf("User<%d %s %s %d %v>", u.Id, u.Name, u.Email, u.DepartmentNo, u.Department)
}

type Department struct {
	tableName struct{} `pg:"department"`
    Id       int64 `json:"id" pg:"id"`
    DepartmentName   string `json:"department_name" pg:"department_name"`
}

func (s Department) String() string {
    return fmt.Sprintf("Department<%d %s>", s.Id, s.DepartmentName)
}


func main(){
	ExampleDB_Model()
}

func ExampleDB_Model() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

    var users []User
    err := db.Model(&users).Select()
    if err != nil {
        panic(err)
    }
	fmt.Println(users)

	err = db.Model(&users).Relation("Department").Select()
    if err != nil {
        panic(err)
    }
	fmt.Println(users)
}

実行結果

~/go/src/go_pg_lesson $ go run main.go
[User<1 kouji gmail.com 1 <nil>> User<2 kkfactory gmail.com 2 <nil>>]
[User<1 kouji gmail.com 1 Department<1 システム開発部>> User<2 kkfactory gmail.com 2 Department<2 システム運用部>>]

Lesson3-3. Group化する

○挿入データ

data

example=# select department_no, count(*) from users group by department_no;
 department_no | count 
---------------+-------
             1 |     1
             2 |     2
(2 rows)

ソース

package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
)

type User struct {
	tableName struct{} `pg:"users"`
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
    Password   string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
	DepartmentNo int64 `json:"department_no" pg:"department_no"`
	Department *Department `json:"department" pg:"rel:has-one, fk:department_no"`
}

func (u User) String() string {
    return fmt.Sprintf("User<%d %s %s %d %v>", u.Id, u.Name, u.Email, u.DepartmentNo, u.Department)
}

type Department struct {
	tableName struct{} `pg:"department"`
    Id       int64 `json:"id" pg:"id"`
    DepartmentName   string `json:"department_name" pg:"department_name"`
}

func (s Department) String() string {
    return fmt.Sprintf("Department<%d %s>", s.Id, s.DepartmentName)
}

func main(){
	ExampleDB_Model()
}

func ExampleDB_Model() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

    var users []User
    err := db.Model(&users).Select()
    if err != nil {
        panic(err)
    }
	fmt.Println(users)

	var res []struct {
		DepartmentNo  int
		DepartmentCount int
	}

	err = db.Model(&users).Column("department_no").ColumnExpr("count(*) As department_count").Group("department_no").Select(&res)

    if err != nil {
        panic(err)
    }
	fmt.Println(res)
}

実行結果

~/go/src/go_pg_lesson $ go run main.go
[User<1 kouji gmail.com 1 <nil>> User<2 kkfactory gmail.com 2 <nil>> User<3 masahiro gmail.com 2 <nil>>]
[{1 1} {2 2}]

Lesson4. サブクエリを実行する

SQLの中にSQLを書くサブクエリの例となります。

Lesson4-1. サブクエリを実行する(From句をサブクエリで設定する)

DDL

CREATE TABLE IF NOT EXISTS books(
   id serial PRIMARY KEY,
   title VARCHAR (256) NOT NULL,
   author_id INT NOT NULL
);

実行したいSQL

SELECT * FROM (
  SELECT "book"."id", "book"."title", "book"."text"
  FROM "books" AS "book" WHERE (author_id = 1)
) AS book

格納データ

id	title	author_id
1	ワンピース	1
2	トリコ	1
3	呪術廻戦	1
4	ヒロアカ	2

main.go

package main

import (
	"fmt"
	"log"

	"github.com/go-pg/pg/v10"
)

func main() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
        Password: "postgres",
        Database: "sample",
        Addr: "localhost:5433",
    })
    defer db.Close()

    type Book struct {
        Id     int64 `json:"id" pg:"id"`
        Title   string `json:"title" pg:"title"`
        AuthorId string `json:"author_id" pg:"author_id"`
    }
    var books []Book

    // サブクエリを作成する。実際のSQLは「SELECT id FROM "books" AS "book" WHERE (author_id = 1)」
	authorBooks := db.Model((*Book)(nil)).Where("author_id = ?", 1)

	err := db.Model().TableExpr("(?) AS book", authorBooks).Select(&books)

    if err != nil {
        log.Fatalln(err)
    }
    fmt.Println("authorBooks: ",authorBooks)
    for _, v := range books {
        fmt.Println("v: ",v)
    }
    fmt.Println("books: ",books)
}

実行結果

~/go/src/go-pg-sample $ go run main.go
authorBooks:  &{0xc00012a008 0xc00011ea80 <nil> 0xc000146090 0xc000146090 0 [] [] [] [0xc0001bdd40] [] map[] [] [0xc000135340] [] [] [] [] [] <nil> [] 0 0 <nil> <nil> []}
v:  {1 ワンピース 1}
v:  {2 トリコ 1}
v:  {3 呪術廻戦 1}
books:  [{1 ワンピース 1} {2 トリコ 1} {3 呪術廻戦 1}]

意外と簡単にできますね！！！go-pgのいいところですね！！

Lesson５. bulk insertを実装する。

main.go

package main

import (
	"fmt"

	"github.com/go-pg/pg/v10"
)

type User struct {
    Id     int64 `json:"id" pg:"id"`
    Name   string `json:"name" pg:"user_name"`
	Password string `json:"password" pg:"password"`
    Email string `json:"email" pg:"email"`
}

func main(){
	Lesson1()
}

func Lesson1() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
		Password: "postgres",
		Database: "example",
		Addr: "localhost:5432",
    })
    defer db.Close()

	bulkinsert(db)
}

func bulkinsert(db *pg.DB) {
	
	var user1 = &User{
		Name: "admin1", Password: "password", Email: "admin1@admin",
	}
	var user2 = &User{
		Name: "admin2", Password: "password", Email: "admin1@admin",
	}
	var user3 = &User{
		Name: "admin3", Password: "password", Email: "admin1@admin",
	}

	var users []User

	users = append(users, *user1, *user2, *user3)

	res, err := db.Model(&users).Insert()

	if err != nil {
		panic(err)
	}
	fmt.Println(res, users)
}

実行結果

~/go/src/go_pg_jikken$ go run main.go
&{0x1400013e3c0 3 3} [{3 admin1 password admin1@admin} {4 admin2 password admin1@admin} {5 admin3 password admin1@admin}]
~/go/src/go_pg_jikken$ go run main.go
&{0x140000e63c0 3 3} [{6 admin1 password admin1@admin} {7 admin2 password admin1@admin} {8 admin3 password admin1@admin}]