Slide 1
Slide 1 text
wrench
- Schema Management Tool for Cloud Spanner -
GCPUG Spanner Day
Yuki Ito
Slide 2
Slide 2 text
Merpay
Architect Team
Backend Engineer
Yuki Ito
Slide 3
Slide 3 text
wrench
ʮGoͷ৽ػೳΛͬͨDBεΩʔϚ ཧπʔϧʯ
https://gihyo.jp/magazine/SD/archive/2019/201911
Slide 4
Slide 4 text
wrench
https://github.com/mercari/wrench
https://hub.docker.com/r/mercari/wrench
Slide 5
Slide 5 text
wrench
> wrench help
Usage:
wrench [command]
Available Commands:
create
drop
reset
load
apply
migrate
Slide 6
Slide 6 text
wrench create
> cat ./_examples/schema.sql
CREATE TABLE Singers (
SingerID STRING(36) NOT NULL,
FirstName STRING(1024),
) PRIMARY KEY(SingerID);
> wrench create --directory ./_examples/
Slide 7
Slide 7 text
wrench create
Slide 8
Slide 8 text
wrench drop
> wrench drop
Slide 9
Slide 9 text
wrench reset
> wrench reset --directory ./_examples/
Slide 10
Slide 10 text
wrench load
> wrench load --directory ./_examples/
> cat ./_examples/schema.sql
CREATE TABLE Singers (
SingerID STRING(36) NOT NULL,
FirstName STRING(1024),
) PRIMARY KEY(SingerID);
Slide 11
Slide 11 text
wrench apply -ddl
> cat ./_examples/ddl.sql
ALTER TABLE Singers ADD COLUMN LastName STRING(1024);
> wrench apply --ddl ./_examples/ddl.sql
Slide 12
Slide 12 text
wrench apply -ddl
Slide 13
Slide 13 text
wrench apply -dml
> cat ./_examples/dml.sql
INSERT INTO Singers(SingerID, FirstName) VALUES("1", "Yuki");
> wrench apply --dml ./_examples/dml.sql
Slide 14
Slide 14 text
wrench apply -dml
Slide 15
Slide 15 text
wrench migrate
> cat ./_examples/migrations/000001.sql
ALTER TABLE Singers ADD COLUMN LastName STRING(1024);
> wrench migrate up --directory ./_examples/
1/up
Slide 16
Slide 16 text
wrench migrate
Slide 17
Slide 17 text
Development
DB DB DB
Instance
Slide 18
Slide 18 text
Development
CREATE TABLE Singers (
SingerID STRING(36) NOT NULL,
FirstName STRING(1024),
) PRIMARY KEY(SingerID);
Slide 19
Slide 19 text
Development
CREATE TABLE Singers (
SingerID STRING(36) NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
) PRIMARY KEY(SingerID);
Slide 20
Slide 20 text
Development
Apply Migration
Load Schema
Commit DDL
Create DDL File
Write DDL
Reset DB
NG
OK
Slide 21
Slide 21 text
Development
> cat ./_examples/schema.sql
CREATE TABLE Singers (
SingerID STRING(36) NOT NULL,
FirstName STRING(1024),
) PRIMARY KEY(SingerID);
Slide 22
Slide 22 text
Development
Slide 23
Slide 23 text
Development
Apply Migration
Load Schema
Commit DDL
Create DDL File
Write DDL
Reset DB
NG
OK
Slide 24
Slide 24 text
Development
> wrench migrate create --directory ./_examples/
_examples/migrations/000001.sql is created
> ls _examples/migrations/
000001.sql
Slide 25
Slide 25 text
Development
Apply Migration
Load Schema
Commit DDL
Create DDL File
Write DDL
Reset DB
NG
OK
Slide 26
Slide 26 text
Development
> cat ./_examples/migrations/000001.sql
ALTER TABLE Singers ADD COLUMN LastNamo STRING(1024);
Slide 27
Slide 27 text
Development
Apply Migration
Load Schema
Commit DDL
Create DDL File
Write DDL
Reset DB
NG
OK
Slide 28
Slide 28 text
Development
> wrench migrate up --directory ./_examples/
1/up
Slide 29
Slide 29 text
Development
Slide 30
Slide 30 text
Development
typo!!
Slide 31
Slide 31 text
Development
Apply Migration
Load Schema
Commit DDL
Create DDL File
Write DDL
Reset DB
NG
OK
Slide 32
Slide 32 text
Development
> cat ./_examples/schema.sql
CREATE TABLE Singers (
SingerID STRING(36) NOT NULL,
FirstName STRING(1024),
) PRIMARY KEY(SingerID);
> wrench reset --directory ./_examples/
Slide 33
Slide 33 text
Development
Slide 34
Slide 34 text
Development
Apply Migration
Load Schema
Commit DDL
Create DDL File
Write DDL
Reset DB
NG
OK
Slide 35
Slide 35 text
Development
> cat ./_examples/migrations/000001.sql
ALTER TABLE Singers ADD COLUMN LastName STRING(1024);
Slide 36
Slide 36 text
Development
Apply Migration
Load Schema
Commit DDL
Create DDL File
Write DDL
Reset DB
NG
OK
Slide 37
Slide 37 text
Development
> wrench migrate up --directory ./_examples/
1/up
Slide 38
Slide 38 text
Development
Slide 39
Slide 39 text
Development
Slide 40
Slide 40 text
Development
Apply Migration
Load Schema
Commit DDL
Create DDL File
Write DDL
Reset DB
NG
OK
Slide 41
Slide 41 text
Development
> wrench load --directory ./_examples/
> cat ./_examples/schema.sql
CREATE TABLE SchemaMigrations (
Version INT64 NOT NULL,
Dirty BOOL NOT NULL,
) PRIMARY KEY(Version);
CREATE TABLE Singers (
SingerID STRING(36) NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
) PRIMARY KEY(SingerID);
Slide 42
Slide 42 text
Development
> wrench load --directory ./_examples/
> cat ./_examples/schema.sql
CREATE TABLE SchemaMigrations (
Version INT64 NOT NULL,
Dirty BOOL NOT NULL,
) PRIMARY KEY(Version);
CREATE TABLE Singers (
SingerID STRING(36) NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
) PRIMARY KEY(SingerID);
Slide 43
Slide 43 text
Development
> wrench load --directory ./_examples/
> cat ./_examples/schema.sql
CREATE TABLE SchemaMigrations (
Version INT64 NOT NULL,
Dirty BOOL NOT NULL,
) PRIMARY KEY(Version);
CREATE TABLE Singers (
SingerID STRING(36) NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
) PRIMARY KEY(SingerID);
Slide 44
Slide 44 text
Development
Slide 45
Slide 45 text
Development
Apply Migration
Load Schema
Commit DDL
Create DDL File
Write DDL
Reset DB
NG
OK
Slide 46
Slide 46 text
Development
DB DB DB
Instance
Slide 47
Slide 47 text
Deployment
Create Tag
Cloud Build
Hook
GCR
Cloud Spanner
GKE
Watch
Push
Migrate
Deploy
Slide 48
Slide 48 text
Deployment
Create Tag
Cloud Build
Hook
GCR
Cloud Spanner
GKE
Watch
Push
Migrate
Deploy
Slide 49
Slide 49 text
Deployment
Migration Deployment
Pipeline
Cloud Spanner GKE
Slide 50
Slide 50 text
Deployment
Migration Deployment
Pipeline
Cloud Spanner GKE
Slide 51
Slide 51 text
Deployment
GKE Cloud Spanner
Fetch Job Manifest
ᶅ Exec `wrench migrate`
ᶄ Run Migration Job
Slide 52
Slide 52 text
Deployment
GKE Cloud Spanner
Fetch Job Manifest
ᶅ Exec `wrench migrate`
ᶄ Run Migration Job
Slide 53
Slide 53 text
Deployment
---
apiVersion: batch/v1
kind: Job
metadata:
name: db-migration
spec:
template:
spec:
containers:
- command:
- /db/migrate.sh
env:
- name: SPANNER_INSTANCE_ID
value: xxx
- name: SPANNER_DATABASE_ID
value: xxx
- name: GCP_PROJECT_ID
value: xxx
- name: GOOGLE_APPLICATION_CREDENTIALS
value: xxx
image: gcr.io/xxxx…
Slide 54
Slide 54 text
Deployment
GKE Cloud Spanner
Fetch Job Manifest
ᶅ Exec `wrench migrate`
ᶄ Run Migration Job
Slide 55
Slide 55 text
Deployment
GKE Cloud Spanner
Fetch Job Manifest
ᶅ Exec `wrench migrate`
ᶄ Run Migration Job
Slide 56
Slide 56 text
Deployment
wrench migrate up --directory /db
Slide 57
Slide 57 text
Deployment
GKE Cloud Spanner
Fetch Job Manifest
ᶅ Exec `wrench migrate`
ᶄ Run Migration Job
Slide 58
Slide 58 text
Deployment
Migration Deployment
Pipeline
Cloud Spanner GKE
Slide 59
Slide 59 text
Deployment
Migration Deployment
Pipeline
Cloud Spanner GKE
Slide 60
Slide 60 text
Deployment
GKE
ᶄ Apply Deployment Manifest
(Deploy New Container)
ᶃ Fetch Deployment Manifest
Slide 61
Slide 61 text
Deployment
Create Tag
Cloud Build
Hook
GCR
Cloud Spanner
GKE
Watch
Push
Migrate
Deploy
Slide 62
Slide 62 text
Merpay Tech Fest 2020
https://events.merpay.com/techfest-2020