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

wrench - Schema Management Tool for Cloud Spanner -

Yuki Ito
February 06, 2020

wrench - Schema Management Tool for Cloud Spanner -

Yuki Ito

February 06, 2020
Tweet

More Decks by Yuki Ito

Other Decks in Technology

Transcript

  1. wrench
    - Schema Management Tool for Cloud Spanner -
    GCPUG Spanner Day
    Yuki Ito

    View full-size slide

  2. Merpay
    Architect Team
    Backend Engineer
    Yuki Ito

    View full-size slide

  3. wrench
    ʮGoͷ৽ػೳΛ࢖ͬͨDBεΩʔϚ ؅ཧπʔϧʯ
    https://gihyo.jp/magazine/SD/archive/2019/201911

    View full-size slide

  4. wrench
    https://github.com/mercari/wrench
    https://hub.docker.com/r/mercari/wrench

    View full-size slide

  5. wrench
    > wrench help
    Usage:
    wrench [command]
    Available Commands:
    create
    drop
    reset
    load
    apply
    migrate

    View full-size slide

  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/

    View full-size slide

  7. wrench create

    View full-size slide

  8. wrench drop
    > wrench drop

    View full-size slide

  9. wrench reset
    > wrench reset --directory ./_examples/

    View full-size slide

  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);

    View full-size slide

  11. wrench apply -ddl
    > cat ./_examples/ddl.sql
    ALTER TABLE Singers ADD COLUMN LastName STRING(1024);
    > wrench apply --ddl ./_examples/ddl.sql

    View full-size slide

  12. wrench apply -ddl

    View full-size slide

  13. wrench apply -dml
    > cat ./_examples/dml.sql
    INSERT INTO Singers(SingerID, FirstName) VALUES("1", "Yuki");
    > wrench apply --dml ./_examples/dml.sql

    View full-size slide

  14. wrench apply -dml

    View full-size slide

  15. wrench migrate
    > cat ./_examples/migrations/000001.sql
    ALTER TABLE Singers ADD COLUMN LastName STRING(1024);
    > wrench migrate up --directory ./_examples/
    1/up

    View full-size slide

  16. wrench migrate

    View full-size slide

  17. Development

    DB DB DB
    Instance

    View full-size slide

  18. Development
    CREATE TABLE Singers (
    SingerID STRING(36) NOT NULL,
    FirstName STRING(1024),
    ) PRIMARY KEY(SingerID);

    View full-size slide

  19. Development
    CREATE TABLE Singers (
    SingerID STRING(36) NOT NULL,
    FirstName STRING(1024),
    LastName STRING(1024),
    ) PRIMARY KEY(SingerID);

    View full-size slide

  20. Development
    Apply Migration
    Load Schema
    Commit DDL
    Create DDL File
    Write DDL
    Reset DB
    NG
    OK

    View full-size slide

  21. Development
    > cat ./_examples/schema.sql
    CREATE TABLE Singers (
    SingerID STRING(36) NOT NULL,
    FirstName STRING(1024),
    ) PRIMARY KEY(SingerID);

    View full-size slide

  22. Development
    Apply Migration
    Load Schema
    Commit DDL
    Create DDL File
    Write DDL
    Reset DB
    NG
    OK

    View full-size slide

  23. Development
    > wrench migrate create --directory ./_examples/
    _examples/migrations/000001.sql is created
    > ls _examples/migrations/
    000001.sql

    View full-size slide

  24. Development
    Apply Migration
    Load Schema
    Commit DDL
    Create DDL File
    Write DDL
    Reset DB
    NG
    OK

    View full-size slide

  25. Development
    > cat ./_examples/migrations/000001.sql
    ALTER TABLE Singers ADD COLUMN LastNamo STRING(1024);

    View full-size slide

  26. Development
    Apply Migration
    Load Schema
    Commit DDL
    Create DDL File
    Write DDL
    Reset DB
    NG
    OK

    View full-size slide

  27. Development
    > wrench migrate up --directory ./_examples/
    1/up

    View full-size slide

  28. Development
    typo!!

    View full-size slide

  29. Development
    Apply Migration
    Load Schema
    Commit DDL
    Create DDL File
    Write DDL
    Reset DB
    NG
    OK

    View full-size slide

  30. Development
    > cat ./_examples/schema.sql
    CREATE TABLE Singers (
    SingerID STRING(36) NOT NULL,
    FirstName STRING(1024),
    ) PRIMARY KEY(SingerID);
    > wrench reset --directory ./_examples/

    View full-size slide

  31. Development
    Apply Migration
    Load Schema
    Commit DDL
    Create DDL File
    Write DDL
    Reset DB
    NG
    OK

    View full-size slide

  32. Development
    > cat ./_examples/migrations/000001.sql
    ALTER TABLE Singers ADD COLUMN LastName STRING(1024);

    View full-size slide

  33. Development
    Apply Migration
    Load Schema
    Commit DDL
    Create DDL File
    Write DDL
    Reset DB
    NG
    OK

    View full-size slide

  34. Development
    > wrench migrate up --directory ./_examples/
    1/up

    View full-size slide

  35. Development
    Apply Migration
    Load Schema
    Commit DDL
    Create DDL File
    Write DDL
    Reset DB
    NG
    OK

    View full-size slide

  36. 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);

    View full-size slide

  37. 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);

    View full-size slide

  38. 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);

    View full-size slide

  39. Development
    Apply Migration
    Load Schema
    Commit DDL
    Create DDL File
    Write DDL
    Reset DB
    NG
    OK

    View full-size slide

  40. Development

    DB DB DB
    Instance

    View full-size slide

  41. Deployment

    Create Tag
    Cloud Build
    Hook
    GCR
    Cloud Spanner
    GKE
    Watch
    Push
    Migrate
    Deploy

    View full-size slide

  42. Deployment

    Create Tag
    Cloud Build
    Hook
    GCR
    Cloud Spanner
    GKE
    Watch
    Push
    Migrate
    Deploy

    View full-size slide

  43. Deployment
    Migration Deployment
    Pipeline
    Cloud Spanner GKE

    View full-size slide

  44. Deployment
    Migration Deployment
    Pipeline
    Cloud Spanner GKE

    View full-size slide

  45. Deployment
    GKE Cloud Spanner
    Fetch Job Manifest
    ᶅ Exec `wrench migrate`
    ᶄ Run Migration Job

    View full-size slide

  46. Deployment
    GKE Cloud Spanner
    Fetch Job Manifest
    ᶅ Exec `wrench migrate`
    ᶄ Run Migration Job

    View full-size slide

  47. 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…

    View full-size slide

  48. Deployment
    GKE Cloud Spanner
    Fetch Job Manifest
    ᶅ Exec `wrench migrate`
    ᶄ Run Migration Job

    View full-size slide

  49. Deployment
    GKE Cloud Spanner
    Fetch Job Manifest
    ᶅ Exec `wrench migrate`
    ᶄ Run Migration Job

    View full-size slide

  50. Deployment
    wrench migrate up --directory /db

    View full-size slide

  51. Deployment
    GKE Cloud Spanner
    Fetch Job Manifest
    ᶅ Exec `wrench migrate`
    ᶄ Run Migration Job

    View full-size slide

  52. Deployment
    Migration Deployment
    Pipeline
    Cloud Spanner GKE

    View full-size slide

  53. Deployment
    Migration Deployment
    Pipeline
    Cloud Spanner GKE

    View full-size slide

  54. Deployment
    GKE
    ᶄ Apply Deployment Manifest
    (Deploy New Container)
    ᶃ Fetch Deployment Manifest

    View full-size slide

  55. Deployment

    Create Tag
    Cloud Build
    Hook
    GCR
    Cloud Spanner
    GKE
    Watch
    Push
    Migrate
    Deploy

    View full-size slide

  56. Merpay Tech Fest 2020
    https://events.merpay.com/techfest-2020

    View full-size slide