Upgrade to Pro — share decks privately, control downloads, hide ads and more …

wrench - Schema Management Tool for Cloud Spanner -

1bfc6e2ed04a895bb36f36b86828b689?s=47 Yuki Ito
February 06, 2020

wrench - Schema Management Tool for Cloud Spanner -

1bfc6e2ed04a895bb36f36b86828b689?s=128

Yuki Ito

February 06, 2020
Tweet

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