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

git-schemlexとddl-makerを使ったDB migrationの紹介 / git...

Konboi
January 27, 2017

git-schemlexとddl-makerを使ったDB migrationの紹介 / git-schemalex and ddl-maker migration #golangtokyo

Konboi

January 27, 2017
Tweet

More Decks by Konboi

Other Decks in Technology

Transcript

  1. Goͷmigrationπʔϧ • github/gh-ost • mattes/migrate • rubenv/sql-migrate • elwinar/rambler •

    DavidHuie/gomigrate GithubͰmigration && Go && StartॱͰݕࡧͨ݁͠Ռ
  2. Goͷmigrationπʔϧ • github/gh-ost • mattes/migrate • rubenv/sql-migrate • elwinar/rambler •

    DavidHuie/gomigrate gh-ostҎ֎Railsํࣜ1 1 bitbucketͰ։ൃ͞Ε͍ͯΔgoose΋Railsํࣜ
  3. Railsํࣜͱ͸ # create new migration file in path migrate -url

    -url driver://url -path ./migrations create golangtokyo3 # Migration files 1485416317_golangtokyo3.down.sql 1485416317_golangtokyo3.up.sql # Exec migrate -url driver://url -path ./migrations up
  4. soh335/git-schemalex • Golang੡ • schemalex(ޙड़)Λར༻ͨ͠migrationπʔϧ • όʔδϣϯ؅ཧʹgitͷcommit hashΛ࢖༻ • migrationΛ࣮ߦͨ͠ࡍͷϑΝΠϧͷcommit

    hashͱద༻͢Δ ϑΝΠϧͱͷࠩ෼ΛDBʹ൓ө͠·͢ • git؅ཧ͞ΕͨschemaϑΝΠϧ͚ͩ͋Ε͹ྑ͍ͷͰGoҎ֎ͷ ϓϩδΣΫτͰ΋࢖͑·͢
  5. schemalex • Golang੡ • Generate difference sql of two mysql

    schema • MySQLݶఆ • 2ͭͷSchemaϑΝΠϧ͔Βࠩ෼ͷSQLΛੜ੒ͯ͘͠ΕΔ
  6. schemalex # entry_00.sql CREATE TABLE `entry` ( `id` INTEGER NOT

    NULL AUTO_INCREMENT, `title` VARCHAR(100) NOT NULL, `content` TEXT NOT NULL, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;
  7. schemalex # entry_01.sql CREATE TABLE `entry` ( `id` INTEGER NOT

    NULL AUTO_INCREMENT, `title` VARCHAR(100) NOT NULL, `public` TINYINT(1) NOT NULL DEFAULT 0, /* Add Column */ `content` TEXT NOT NULL, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, INDEX `title_idx` (`title`), /* Add Index */ PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;
  8. schemalex $ schemalex sql/entry_00.sql sql/entry_01.sql BEGIN; SET FOREIGN_KEY_CHECKS = 0;

    ALTER TABLE `entry` ADD COLUMN `public` TINYINT (1) NOT NULL DEFAULT 0; ALTER TABLE `entry` ADD INDEX `title_idx` (`title`); SET FOREIGN_KEY_CHECKS = 1; COMMIT;
  9. schemalex $ schemalex sql/entry_01.sql sql/entry_00.sql BEGIN; SET FOREIGN_KEY_CHECKS = 0;

    ALTER TABLE `entry` DROP COLUMN `public`; ALTER TABLE `entry` DROP INDEX `title_idx`; SET FOREIGN_KEY_CHECKS = 1; COMMIT
  10. schemaϑΝΠϧΛ͍͍ײ͡ʹੜ੒ • tableʹରԠͨ͠struct͕ଘࡏ • struct͔ΒschemaΛࣗಈੜ੒ • mackee/go-gendd • Generate RDB

    DDL by go struct • go/parser Λ࢖ͬͯߏ଄Λղੳ • ΠϯσοΫεͷΧϥϜॱ΋ݫີʹࢦఆ͍ͨ͠...
  11. kayac/ddl-maker • Golang੡ • ddl-maker is generate ddl from Go

    struct. • reflectΛ࢖ͬͯղੳ • parser෦෼͸90ߦఔ౓ • $ cat parser.go | wc -l ! 87 • ݱঢ়MySQLͷΈରԠ (interfaceʹରԠ͢Ε͹ଞͷͰ΋ར༻Մ)
  12. kayac/ddl-maker type Entry struct { Id int32 `ddl:"auto"` Title string

    `ddl:"size=100"` Public bool `ddl:"default=0"` Content string `ddl:"type=text"` CreatedAt time.Time UpdatedAt time.Time } func (e *Entry) PrimaryKey() dialect.PrimaryKey { return mysql.AddPrimaryKey("id", "created_at") }
  13. kayac/ddl-maker CREATE TABLE `entry` ( `id` INTEGER NOT NULL AUTO_INCREMENT,

    `title` VARCHAR(100) NOT NULL, `public` TINYINT(1) NOT NULL DEFAULT 0, `content` TEXT NOT NULL, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`, `created_at`) );
  14. func (e *Entry) Indexes() dialect.Indexes { return dialect.Indexes{ mysql.AddIndex("title"), }

    } ↓ CREATE TABLE `entry` ( ... INDEX `title_idx` (`title`), /* here */ ... )ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;
  15. kayac/ddl-maker structͷύʔε෦෼ func parseField(field reflect.StructField, d dialect.Dialect) dialect.Column { tagStr

    := strings.Replace(field.Tag.Get(TAGPREFIX), " ", "", -1) var typeName string if field.Type.PkgPath() != "" { // ex) time.Time pkgName := field.Type.PkgPath() if strings.Contains(pkgName, "/") { pkgs := strings.Split(pkgName, "/") pkgName = pkgs[len(pkgs)-1] } typeName = fmt.Sprintf("%s.%s", pkgName, field.Type.Name()) } else if field.Type.Kind().String() == "ptr" { // pointer type typeName = fmt.Sprintf("*%s", field.Type.Elem()) } else { typeName = field.Type.Name() } return newColumn(snaker.CamelToSnake(field.Name), typeName, tagStr, d) }