概要

GAE(go1.12)からCloud SQLを操作するぞ

事前準備

Cloud SQL インスタンスの作成

Cloud SQLの種類一覧表示や

gcloud sql tiers list

一番安そうなやつにトライ

gcloud sql instances create ucwork --tier=db-f1-micro --region=asia-northeast1
Creating Cloud SQL instance...done.
Created [https://www.googleapis.com/sql/v1beta4/projects/ucwork-ai-000002/instances/ucwork].
NAME    DATABASE_VERSION  LOCATION           TIER         PRIMARY_ADDRESS  PRIVATE_ADDRESS  STATUS
ucwork  MYSQL_5_7         asia-northeast1-b  db-f1-micro  35.243.68.17     -                RUNNABLE

SQL インスタンス情報取得

gcloud sql instances describe ucwork
backendType: SECOND_GEN
connectionName: [your connection name]
...

MySQLパスワードを設定しとく

gcloud sql users set-password root --host % --instance ucwork --password [your password]
Updating Cloud SQL user...done.

Cloud SQL Proxy をインストール&実行

ローカルからCloud SQLに接続するためのProxyをインストール

# 取得
curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 13.6M  100 13.6M    0     0  6792k      0  0:00:02  0:00:02 --:--:-- 6796k

# 実行権限付与
chmod +x cloud_sql_proxy

# 実行
./cloud_sql_proxy

CloudSQLに接続

MySQLコマンドやDataGrip的なツールで以下情報を打ち込み
Cloud SQLに接続できることを確認

ホスト名
- localhost
ポート名
- 3306
ユーザー名
- root
パスワード
- [your password]

構築

Cloud SQLへの接続

CloudSQL初期化開始

initメソッドの中で初期化処理を開始
事前準備で作成したユーザー名やパスワードなどの情報を設定

internal/config.go

var (
    DBSql repository.OrderDatabase
)

func init(){
    var err error
    // Cloud SQLの初期設定
    DBSql, err = configureCloudSQL(cloudSQLConfig{
        Username: "root",
        Password: "[your password]",
        Instance: "[your connection name]",
    })
    if err != nil {
        log.Fatal(err)
    }
}

type cloudSQLConfig struct {
    Username, Password, Instance string
}

Cloud SQLを操作するためのDao的DB作成

GAE用とローカル環境用で接続先が異なる模様

internal/config.go

func configureCloudSQL(config cloudSQLConfig) (repository.OrderDatabase, error) {
    if os.Getenv("GAE_INSTANCE") != "" {
        // Running in production.
        return db.NewMySQLDB(db.MySQLConfig{
            Username:   config.Username,
            Password:   config.Password,
            UnixSocket: "/cloudsql/" + config.Instance,
        })
    }

    // Running locally.
    return db.NewMySQLDB(db.MySQLConfig{
        Username: config.Username,
        Password: config.Password,
        Host:     "localhost",
        Port:     3306,
    })
}

internal/db/sql.go

type MySQLConfig struct {
    // Optional.
    Username, Password string

    // Host of the MySQL instance.
    //
    // If set, UnixSocket should be unset.
    Host string

    // Port of the MySQL instance.
    //
    // If set, UnixSocket should be unset.
    Port int

    // UnixSocket is the filepath to a unix socket.
    //
    // If set, Host and Port should be unset.
    UnixSocket string
}

Cloud SQLへの接続確認&実際に接続&SQL準備

BookShelfチュートリアルの処理が多いので小分けに理解していく

接続確認

ensureTableExistsメソッドで実際に接続させてpingで接続確認
DBとテーブルが存在するか確認して、存在しない場合は必要なものを作成

internal/db/sql.go

func NewMySQLDB(config MySQLConfig) (repository.OrderDatabase, error) {
    // Check database and table exists. If not, create it.
    if err := config.ensureTableExists(); err != nil {
        return nil, err
    }
  
    // 実際に接続
  
    // SQL準備
    
    return db, nil
}

func (config MySQLConfig) ensureTableExists() error {
    conn, err := sql.Open("mysql", config.dataStoreName(""))
    if err != nil {
        return fmt.Errorf("mysql: could not get a connection: %v", err)
    }
    defer conn.Close()

    // Check the connection.
    if conn.Ping() == driver.ErrBadConn {
        return fmt.Errorf("mysql: could not connect to the database. " +
            "could be bad address, or this address is not whitelisted for access.")
    }

    if _, err := conn.Exec("USE ucwork"); err != nil {
        // MySQL error 1049 is "database does not exist"
        if mErr, ok := err.(*mysql.MySQLError); ok && mErr.Number == 1049 {
            return createTable(conn)
        }
    }

    if _, err := conn.Exec("DESCRIBE orders"); err != nil {
        // MySQL error 1146 is "table does not exist"
        if mErr, ok := err.(*mysql.MySQLError); ok && mErr.Number == 1146 {
            return createTable(conn)
        }
        // Unknown error.
        return fmt.Errorf("mysql: could not connect to the database: %v", err)
    }
    return nil
}

// dataStoreName returns a connection string suitable for sql.Open.
func (c MySQLConfig) dataStoreName(databaseName string) string {
    var cred string
    // [username[:password]@]
    if c.Username != "" {
        cred = c.Username
        if c.Password != "" {
            cred = cred + ":" + c.Password
        }
        cred = cred + "@"
    }

    if c.UnixSocket != "" {
        return fmt.Sprintf("%sunix(%s)/%s", cred, c.UnixSocket, databaseName)
    }
    return fmt.Sprintf("%stcp([%s]:%d)/%s", cred, c.Host, c.Port, databaseName)
}

var createTableStatements = []string{
    `CREATE DATABASE IF NOT EXISTS ucwork DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci';`,
    `USE ucwork;`,
    `CREATE TABLE IF NOT EXISTS orders (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      name VARCHAR(255) NULL,
      PRIMARY KEY (id)
  )`,
}

// createTable creates the table, and if necessary, the database.
func createTable(conn *sql.DB) error {
    for _, stmt := range createTableStatements {
        _, err := conn.Exec(stmt)
        if err != nil {
            return err
        }
    }
    return nil
}

実際に接続&SQL準備

実際に接続してPINGで再度接続確認
conn.PrepareメソッドでDB structureにSQLを設定

type mysqlDB struct {
    conn *sql.DB

    list   *sql.Stmt
    insert *sql.Stmt
}

func NewMySQLDB(config MySQLConfig) (repository.OrderDatabase, error) {
  // 接続確認

  conn, err := sql.Open("mysql", config.dataStoreName("ucwork"))
    if err != nil {
        return nil, fmt.Errorf("mysql: could not get a connection: %v", err)
    }
    if err := conn.Ping(); err != nil {
        conn.Close()
        return nil, fmt.Errorf("mysql: could not establish a good connection: %v", err)
    }

    db := &mysqlDB{
        conn: conn,
    }

    // Prepared statements. The actual SQL queries are in the code near the
    // relevant method (e.g. addOrder).
    if db.list, err = conn.Prepare(listStatement); err != nil {
        return nil, fmt.Errorf("mysql: prepare list: %v", err)
    }
    if db.insert, err = conn.Prepare(insertStatement); err != nil {
        return nil, fmt.Errorf("mysql: prepare insert: %v", err)
    }

    return db, nil
}

const listStatement = `SELECT * FROM orders ORDER BY name`

const insertStatement = `INSERT INTO orders ( name ) VALUES ( ? )`

Cloud SQL操作実装

登録

CloudSQLにレコードを登録し、登録されたIDを返却

internal/db/sql.go

func (db *mysqlDB) AddOrder(b *repository.Order) (id int64, err error) {
    r, err := execAffectingOneRow(db.insert, b.Name)
    if err != nil {
        return 0, err
    }

    lastInsertID, err := r.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("mysql: could not get last insert ID: %v", err)
    }
    return lastInsertID, nil
}

stmt.Execメソッドでinsert処理を実行して１レコードだけ登録されていることを確認

internal/db/sql.go

func execAffectingOneRow(stmt *sql.Stmt, args ...interface{}) (sql.Result, error) {
    r, err := stmt.Exec(args...)
    if err != nil {
        return r, fmt.Errorf("mysql: could not execute statement: %v", err)
    }
    rowsAffected, err := r.RowsAffected()
    if err != nil {
        return r, fmt.Errorf("mysql: could not get rows affected: %v", err)
    } else if rowsAffected != 1 {
        return r, fmt.Errorf("mysql: expected 1 row affected, got %d", rowsAffected)
    }
    return r, nil
}

一覧表示

db.list.Queryでselect処理を実施
scanOrderメソッドで期待する構造体orderに結果を詰め込んでいく

internal/db/sql.go

func (db *mysqlDB) ListOrders() ([]*repository.Order, error) {
    rows, err := db.list.Query()
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var orders []*repository.Order
    for rows.Next() {
        order, err := scanOrder(rows)
        if err != nil {
            return nil, fmt.Errorf("mysql: could not read row: %v", err)
        }

        orders = append(orders, order)
    }

    return orders, nil
}

Scanメソッドでselect結果から期待するカラムを取得

internal/db/sql.go

// rowScanner is implemented by sql.Row and sql.Rows
type rowScanner interface {
    Scan(dest ...interface{}) error
}

// scanOrder reads a order from a sql.Row or sql.Rows
func scanOrder(s rowScanner) (*repository.Order, error) {
    var (
        id              int64
        name            sql.NullString
    )
    if err := s.Scan(&id, &name); err != nil {
        return nil, err
    }

    order := &repository.Order{
        ID:            id,
        Name:         name.String,
    }
    return order, nil
}

検証

ローカル環境で実行

起動

go run cmd/ucwork/main.go
2019/09/26 10:00:52 Defaulting to port 8080
2019/09/26 10:00:52 Listening on port 8080

アクセス

登録

curl -v -X POST -H "Content-Type: application/json" -d '{"Name":"ucwork"}' http://localhost:8080/orders
Note: Unnecessary use of -X or --request, POST is already inferred.
*   Trying ::1...
* TCP_NODELAY set
* Connected to localhost (::1) port 8080 (#0)
> POST /orders HTTP/1.1
> Host: localhost:8080
> User-Agent: curl/7.54.0
> Accept: */*
> Content-Type: application/json
> Content-Length: 17
>
* upload completely sent off: 17 out of 17 bytes
< HTTP/1.1 200 OK
< Date: Thu, 26 Sep 2019 01:01:33 GMT
< Content-Length: 24
< Content-Type: text/plain; charset=utf-8
<
* Connection #0 to host localhost left intact
{"ID":0,"Name":"ucwork"}%

一覧確認

curl -v http://localhost:8080/orders
*   Trying ::1...
* TCP_NODELAY set
* Connected to localhost (::1) port 8080 (#0)
> GET /orders HTTP/1.1
> Host: localhost:8080
> User-Agent: curl/7.54.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Date: Thu, 26 Sep 2019 01:02:24 GMT
< Content-Length: 51
< Content-Type: text/plain; charset=utf-8
<
* Connection #0 to host localhost left intact
[{"ID":1,"Name":"ucwork"}]%

GAE環境で構築

デプロイ

いつも通りデプロイしたら怒られた

gcloud app deploy deployments/app.yaml
Services to deploy:

descriptor:      [/Users/shintaro.a.uchiyama/project/github.com/shintaro123/ucwork-go/deployments/app.yaml]
source:          [/Users/shintaro.a.uchiyama/project/github.com/shintaro123/ucwork-go/deployments]
target project:  [ucwork-ai-000002]
target service:  [default]
target version:  [20190926t100502]
target url:      [https://ucwork-ai-000002.appspot.com]


Do you want to continue (Y/n)?  y

Beginning deployment of service [default]...
Created .gcloudignore file. See `gcloud topic gcloudignore` for details.
╔════════════════════════════════════════════════════════════╗
╠═ Uploading 16 files to Google Cloud Storage               ═╣
╚════════════════════════════════════════════════════════════╝
File upload done.
Updating service [default]...failed.
ERROR: (gcloud.app.deploy) Error Response: [9] Cloud build 8b178da0-ca9d-4066-a56b-4c6c0c9a3859 status: FAILURE.
Build error details: {"error":{"errorType":"BuildError","canonicalCode":"INVALID_ARGUMENT","errorId":"7873E743","errorMessage":"2019/09/26 01:05:37 Building /tmp/staging/srv, with main package at ./deployments, saving to /tmp/staging/usr/local/bin/start\n2019/09/26 01:05:37 Running \u0026{/usr/local/go/bin/go [go build -o /tmp/staging/usr/local/bin/start ./deployments] [PATH=/go/bin:/usr/local/go/bin:/builder...

app.yamlをdeployment、main.goをcmdディレクトリに移動したからmainがどこにあるのかわかってないっぽい。
app.yamlを以下の通り修正して再度デプロイ

gcloud app deploy deployments/app.yaml
Services to deploy:

descriptor:      [/Users/shintaro.a.uchiyama/project/github.com/shintaro123/ucwork-go/deployments/app.yaml]
source:          [/Users/shintaro.a.uchiyama/project/github.com/shintaro123/ucwork-go/deployments]
target project:  [ucwork-ai-000002]
target service:  [default]
target version:  [20190926t120405]
target url:      [https://ucwork-ai-000002.appspot.com]


Do you want to continue (Y/n)?  y

Beginning deployment of service [default]...
╔════════════════════════════════════════════════════════════╗
╠═ Uploading 2 files to Google Cloud Storage                ═╣
╚════════════════════════════════════════════════════════════╝
File upload done.
Updating service [default]...done.
Setting traffic split for service [default]...done.
Deployed service [default] to [https://ucwork-ai-000002.appspot.com]

You can stream logs from the command line by running:
  $ gcloud app logs tail -s default

To view your application in the web browser run:
  $ gcloud app browse

アクセス

登録

curl -kv -X POST -H "Content-Type: application/json" -d '{"Name":"ucwork_gae"}' http://ucwork-ai-000002.appspot.com/orders
Note: Unnecessary use of -X or --request, POST is already inferred.
*   Trying 172.217.161.84...
* TCP_NODELAY set
* Connected to ucwork-ai-000002.appspot.com (172.217.161.84) port 80 (#0)
> POST /orders HTTP/1.1
> Host: ucwork-ai-000002.appspot.com
> User-Agent: curl/7.54.0
> Accept: */*
> Content-Type: application/json
> Content-Length: 21
>
* upload completely sent off: 21 out of 21 bytes
< HTTP/1.1 200 OK
< Content-Type: text/plain; charset=utf-8
< Vary: Accept-Encoding
< X-Cloud-Trace-Context: d90b597e3668e8d9a47fca8d83bfe594;o=1
< Date: Thu, 26 Sep 2019 03:18:23 GMT
< Server: Google Frontend
< Content-Length: 28
<
* Connection #0 to host ucwork-ai-000002.appspot.com left intact
{"ID":0,"Name":"ucwork_gae"}%

一覧確認

curl -kv https://ucwork-ai-000002.appspot.com/orders
*   Trying 172.217.161.84...
* TCP_NODELAY set
* Connected to ucwork-ai-000002.appspot.com (172.217.161.84) port 443 (#0)
* ALPN, offering h2
* ALPN, offering http/1.1
* Cipher selection: ALL:!EXPORT:!EXPORT40:!EXPORT56:!aNULL:!LOW:!RC4:@STRENGTH
* successfully set certificate verify locations:
*   CAfile: /etc/ssl/cert.pem
  CApath: none
* TLSv1.2 (OUT), TLS handshake, Client hello (1):
* TLSv1.2 (IN), TLS handshake, Server hello (2):
* TLSv1.2 (IN), TLS handshake, Certificate (11):
* TLSv1.2 (IN), TLS handshake, Server key exchange (12):
* TLSv1.2 (IN), TLS handshake, Server finished (14):
* TLSv1.2 (OUT), TLS handshake, Client key exchange (16):
* TLSv1.2 (OUT), TLS change cipher, Client hello (1):
* TLSv1.2 (OUT), TLS handshake, Finished (20):
* TLSv1.2 (IN), TLS change cipher, Client hello (1):
* TLSv1.2 (IN), TLS handshake, Finished (20):
* SSL connection using TLSv1.2 / ECDHE-RSA-CHACHA20-POLY1305
* ALPN, server accepted to use h2
* Server certificate:
*  subject: C=US; ST=California; L=Mountain View; O=Google LLC; CN=*.appspot.com
*  start date: Sep  5 20:18:07 2019 GMT
*  expire date: Nov 28 20:18:07 2019 GMT
*  issuer: C=US; O=Google Trust Services; CN=GTS CA 1O1
*  SSL certificate verify ok.
* Using HTTP2, server supports multi-use
* Connection state changed (HTTP/2 confirmed)
* Copying HTTP/2 data in stream buffer to connection buffer after upgrade: len=0
* Using Stream ID: 1 (easy handle 0x7fdfc4806c00)
> GET /orders HTTP/2
> Host: ucwork-ai-000002.appspot.com
> User-Agent: curl/7.54.0
> Accept: */*
>
* Connection state changed (MAX_CONCURRENT_STREAMS updated)!
< HTTP/2 200
< content-type: text/plain; charset=utf-8
< vary: Accept-Encoding
< x-cloud-trace-context: 62b1a9540c1a9e024724646c7b904c95;o=1
< date: Thu, 26 Sep 2019 03:18:54 GMT
< server: Google Frontend
< content-length: 80
< alt-svc: quic=":443"; ma=2592000; v="46,43",h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000
<
* Connection #0 to host ucwork-ai-000002.appspot.com left intact
[{"ID":1,"Name":"ucwork"},{"ID":2,"Name":"ucwork"},{"ID":3,"Name":"ucwork_gae"}]%

まとめ

GAEってえいやでアプリケーションできるけど、ネットワークの制御とかどうなってるのかいまいち理解できてないな・・・
とりあえず一通り触るだけ触ってから気になるとこ調べていこう

元営業WEBエンジニアのアプリ開発日記

営業出身のWEB系エンジニアが気になったものから作ってはメモを残してくブログ

GAE(go1.12)でCloud SQLを操作してみるぞ

概要

事前準備

Cloud SQL インスタンスの作成

Cloud SQL Proxy をインストール&実行

CloudSQLに接続

構築

Cloud SQLへの接続

CloudSQL初期化開始

Cloud SQLを操作するためのDao的DB作成

Cloud SQLへの接続確認&実際に接続&SQL準備

接続確認

実際に接続&SQL準備

Cloud SQL操作実装

登録

一覧表示

検証

ローカル環境で実行

起動

アクセス

GAE環境で構築

デプロイ

アクセス

まとめ

概要

事前準備

Cloud SQLインスタンスの作成

Cloud SQL Proxy をインストール&実行

CloudSQLに接続

構築

Cloud SQLへの接続

CloudSQL初期化開始

Cloud SQLを操作するためのDao的DB作成

Cloud SQLへの接続確認&実際に接続&SQL準備

接続確認

実際に接続&SQL準備

Cloud SQL操作実装

登録

一覧表示

検証

ローカル環境で実行

起動

アクセス

GAE環境で構築

デプロイ

アクセス

まとめ

Cloud SQL インスタンスの作成