wrench - Schema Management Tool for Cloud Spanner -

Transcript

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