git-schemlexͱ ddl-maker Λ࢖ͬͨDB migrationͷ঺հ @Konboi / golang.tokyo#3

ࣗݾ঺հ • Ryosuke Yabuki a.k.a @Konboi • ໘ന๏ਓKAYAC • εϚϗήʔϜͷ։ൃ/ӡ༻/ٕज़ج൫ • Go/Perl

ຊ୊ DatabaseͷབྷΉΞϓϦέʔγϣϯ migration͕ඞਢ

Databaseͷmigrationͱ͸ • ςʔϒϧͷ࡞੒/มߋ/࡟আ • ΧϥϜͷ௥Ճ/มߋ/࡟আ • ΠϯσοΫεͷ௥Ճ/มߋ/࡟আ • etc ...

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

Goͷmigrationπʔϧ • github/gh-ost • mattes/migrate • rubenv/sql-migrate • elwinar/rambler • DavidHuie/gomigrate gh-ostҎ֎Railsํࣜ1 1 bitbucketͰ։ൃ͞Ε͍ͯΔgoose΋Railsํࣜ

Railsํࣜͱ͸ • migrationͷ౎౓ϑΝΠϧΛੜ੒ • up/downΛఆٛ͢Δ͜ͱͰrollback͕Մೳ • DSLͰఆ࣮ٛ͠ߦ͢Δ

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

RailsํࣜͷϝϦοτ/σϝϦοτ ϝϦοτ ! • ΞϓϦέʔγϣϯͱಉ͡ݴޠͰॻ͚Δ • rollback͕Մೳ σϝϦοτ ! • DSLΛ֮͑Δඞཁ͕͋Δ • ฒߦͰͷ։ൃ͕ଟ͍ͱίϯϑϦΫτ͠΍͍͢

ΧϠοΫͷήʔϜ։ൃͷݱ৔Ͱ͸ • ఆৗΠϕϯτ • ৽ػೳ։ൃ • ௐࠪ/ղੳ༻ͷΧϥϜ/ςʔϒϧ • etc ... ෳ਺ͷϥΠϯ͕ฒߦͰ૸ΔͷͰ σϝϦοτΛແࢹग़དྷͳ͍!

git-schemalex

soh335/git-schemalex • Golang੡ • schemalex(ޙड़)Λར༻ͨ͠migrationπʔϧ • όʔδϣϯ؅ཧʹgitͷcommit hashΛ࢖༻ • migrationΛ࣮ߦͨ͠ࡍͷϑΝΠϧͷcommit hashͱద༻͢Δ ϑΝΠϧͱͷࠩ෼ΛDBʹ൓ө͠·͢ • git؅ཧ͞ΕͨschemaϑΝΠϧ͚ͩ͋Ε͹ྑ͍ͷͰGoҎ֎ͷ ϓϩδΣΫτͰ΋࢖͑·͢

No content

No content

No content

No content

! DEMO !

schemalex • Golang੡ • Generate difference sql of two mysql schema • MySQLݶఆ • 2ͭͷSchemaϑΝΠϧ͔Βࠩ෼ͷSQLΛੜ੒ͯ͘͠ΕΔ

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;

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;

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;

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

git-schemalex/schmealex • SchemaϑΝΠϧ͕༻ҙͰ͖Ε͹! • SQL͕ॻ͚Ε͹ྑ͍ • DSLΛ֮͑Δඞཁ͕ͳ͍ " • SchemaϑΝΠϧͷΈͷ؅ཧ • up/down ͸git ͕໾ׂΛ୲͏ • ฒߦͰͷ։ൃ΋໰୊ͳ͍ "

migration͸ ͍͍ײ͡ʹͳ͚ͬͨͲ ΋ͬͱָ͍ͨ͠

schemaϑΝΠϧ΋ ͍͍ײ͡ʹੜ੒͍ͨ͠ !

schemaϑΝΠϧΛ͍͍ײ͡ʹੜ੒ • tableʹରԠͨ͠struct͕ଘࡏ • struct͔ΒschemaΛࣗಈੜ੒ • mackee/go-gendd • Generate RDB DDL by go struct • go/parser Λ࢖ͬͯߏ଄Λղੳ • ΠϯσοΫεͷΧϥϜॱ΋ݫີʹࢦఆ͍ͨ͠...

kayac/ddl-maker

kayac/ddl-maker • Golang੡ • ddl-maker is generate ddl from Go struct. • reflectΛ࢖ͬͯղੳ • parser෦෼͸90ߦఔ౓ • $ cat parser.go | wc -l ! 87 • ݱঢ়MySQLͷΈରԠ (interfaceʹରԠ͢Ε͹ଞͷͰ΋ར༻Մ)

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") }

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

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;

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

঺հͨ͠πʔϧΛ࢖ͬͨ։ൃͷྲྀΕ 1. DBͷtableͱରԠ͢ΔstructΛ࡞੒ 2. ddl-makerʹԊͬͨܗͰschemaʹඞཁͳ෺Λϝιουఆٛ • Primarykey΍IndexͳͲ 3. ddl-makerͰstructΛschemaϑΝΠϧ΁ 4. git-schemalexΛ࢖ͬͯmigration

·ͱΊ • git-schemalexΛ࢖ͬͨRailsܗࣜͱ͸ҧͬͨmigrationܗࣜͷ ঺հ • kayac/ddl-maker • strcut͔ΒschemaϑΝΠϧΛੜ੒͢Δπʔϧͷ঺հ

! ੋඇ͓ࢼ͋͠Ε !

! ͝੩ௌ ! ͋Γ͕ͱ͏͍͟͝·ͨ͠