Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@koji0705(康治 河村)

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

Last updated at Posted at 2022-04-25

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図

スクリーンショット 2022-04-26 10.58.20.png

○挿入データ

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}]
2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?