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