Go向けORM sqllaの紹介と JOINやUNIONを含んだクエリの扱い方

湘南.pm #1

https://shonanpm.connpass.com/event/289094/

Transcript

1. Go޲͚ORM sqllaͷ঺հͱ JOIN΍UNIONΛؚΜͩΫΤϦ ͷѻ͍ํ macopy a.k.a @mackee_w 2023-08-26 ভೆ.pm #1
2. ࢲͱ͸ • macopy X: @mackee_w • Perl͕޷͖ Goͱ͸஥ྑ͠ • ීஈ͸3DϓϦϯλΛԆʑͱ͍͍ͬͯ͡ ·͢ • ࣸਅ͸ϥεϕΨεͰϏʔϧͰ͢
3. sqllaͱ͸ • Go޲͚ORM • SQLͷ૊Έཱͯʹඞཁͳϝ ιουΛDBεΩʔϚఆ͔ٛΒ ίʔυੜ੒͢Δͷ͕ಛ௃ • ࢲ(macopy)͕࡞੒ɾϝϯςφ ϯεΛ͍ͯ͠·͢ • gitHub.com/mackee/go-sqlla
4. sqllaͷചΓ • ςʔϒϧͷΧϥϜʹରԠͨ͠ϝιου͕ίʔυੜ੒͞ΕΔͷͰɺิ׬ ʹग़ͯ͘Δ • `column IN (…)`, όϧΫΠϯαʔτ, `INSERT ~ ON DUPLICATEKEY UPDATE` ʹରԠ • ϑϨʔϜϫʔΫ΍ύοέʔδߏ੒ʹڧ͘ґଘ͠ͳ͍ͷͰɺҰ෦෼͚ͩ sqllaΛೖΕΔ͜ͱ͕Մೳ
5. ଞͷORMͱൺ΂Δͱ ʮsqlla͕΍Βͳ͍͜ͱʯͰ঺հ͢Δํ͕ ࡍཱͭ
6. ͦ΋ͦ΋ORMͬͯ ͳʹ
7. Object-Relational Mapper/Mapping • ͭ·ΓRDBMSͷΫΤϦ݁ՌΛϓϩάϥϛϯάݴޠݻ༗ͷΦϒδΣΫ τʹϚοϐϯά͢Δߦҝ, ΋͘͠͸ͦΕΛ΍Δπʔϧ/ϥΠϒϥϦ
8. ͜͜ͰΈͳ͞Μɺ಄ͷதͰ ORMͱݴΘΕΔϥΠϒϥϦΛࢥ͍ු ͔΂ग़͍ͯͩ͘͞
9. Django ORM, SQLAlchemy, Peewee, Pony ORM, Tortoise ORM, Hibernate, EclipseLink, JOOQ, MyBatis, ActiveJDBC, ActiveRecord, Sequel, DataMapper, Entity Framework, Dapper, LINQ to SQL, Massive, Eloquent ORM, Doctrine ORM, Propel, Sequelize, TypeORM, Waterline, Objection.js, GORM, xorm, Storm, Pop, Exposed, Ktorm, OrmLite ChatGPTʹͰ͖Δ͚ͩྻڍͯ͠ͱ͓ئ͍ͨ͠(ͳ͍΋ͷ͕͋Δ͔΋)
10. Perlͩͱ • Class::DBI • DBIx::Class • Teng • Aniki • Otogiri
11. ࠶ܝ: Object-Relational Mapper/Mapping • ͭ·ΓRDBMSͷΫΤϦ݁ՌΛϓϩάϥϛϯάݴޠݻ༗ͷΦϒδΣΫ τʹϚοϐϯά͢Δߦҝ, ΋͘͠͸ͦΕΛ΍Δπʔϧ/ϥΠϒϥϦ
12. ͕ͩɺੈͷதͰݴΘΕ͍ͯΔ ORM͸ ͦΕҎ֎ͷ࢓ࣄΛ΍͍ͬͯΔ
13. ΫΤϦϏϧμʔ • ϓϩάϥϛϯάݴޠͷγϯλοΫεͰDMLΛ૊ΈཱͯΔ Teng(Perl)
14. εΩʔϚ؅ཧ • RDBMSͷDDLΛݴޠͷγϯλοΫεͰఆٛͨ͠Γɺ΋͘͠͸ͦͷٯ ม׵Λߦ͏ gorm(Go)
15. εΩʔϚϚΠάϨʔγϣϯ • ͢Ͱʹ͋ΔDBͷDDLΛɺ໨తͷঢ়ଶͷDDLʹͳΔΑ͏ʹมԽͤ͞Δ ActiveRecord(Ruby)
16. ଞʹ΋͜Μͳػೳ͕ • ૠೖ࣌ɾߋ৽࣌ϑοΫ • DBͷܕͱϓϩάϥϛϯάݴޠͷܕͷม׵ • in fl ate/de fl ateͳͲͱݺ͹ΕͨΓ͢Δ • ίωΫγϣϯϓʔϦϯά
17. ݁ہͷͱ͜ΖɺRDBMSΛ͏·͘ѻ͏ ͨΊͷϥΠϒϥϦͩͬͨΓ ͦΜͳϥΠϒϥϦͷதͷҰͭͷػೳͱ ͯ͠ORM͕͋Δ
18. sqlla͸ԿΛ΍ͬͯԿΛ΍Βͳ͍͔
19. sqllaͷػೳ • ΫΤϦϏϧμʔ • ߋ৽࣌ɾૠೖ࣌ϑοΫ • ORM • Ҏ্ʂ
20. sqlla͕΍Βͳ͍͜ͱ • εΩʔϚϚΠάϨʔγϣϯ • DBίωΫγϣϯ؅ཧ • ߴ౓ͳܕม׵
21. DBίωΫγϣϯΛ࣋ͨͳ͍ͱ͸ ΫΤϦ࣮ߦ࣌ʹຖճʹsqlla.DB interfaceΛຬͨ͢ΦϒδΣΫτΛ౉͢
22. sqlla͚ͩͩͱ͖͍ͭͷͰɺݱ࣮తʹ͸͜͏ͯ͠ ·͢ • εΩʔϚ؅ཧ github.com/mackee/go-genddl • sqllaޓ׵ͷstruct͔ΒDDLΛग़ྗ͢Δ • εΩʔϚϚΠάϨʔγϣϯ github.com/k0kubun/sqldef • DDL͔ΒALTERจΛੜ੒͢Δ • ίωΫγϣϯ؅ཧ database/sql • ΧϥϜͷܕͱGoͷܕͷ૬ޓม׵͸֤driverʹ೚͍ͤͯΔ • ήετͷshogo82148͞Μ(go-sql-driver/mysqlͷίϯτϦϏϡʔλʔ)ײँͯ͠·͢ʂ
23. ࠷ۙͷ೰Έ ʮJOINΛsqllaͰѻ͍͍ͨʯ
24. ORMʹ͓͚ΔJOIN໰୊ • ୯ମςʔϒϧΛҾ࣌͘͸RDBMSͷ݁Ռͷܗ͕มΘΔ͜ͱ͸͋Μ·Γ ͳ͍ • ΧϥϜ਺͕ݮΔ͙Β͍ • JOINΛ͢ΔͱΧϥϜ਺͕૿ݮ͢Δ ΧϥϜͷܕ΋ೖΓࠞͬͯ͡͠·͏ • ੩తʹఆٛͨ͠ΦϒδΣΫτʹϚοϐϯά͕Ͱ͖ͳ͍ ͜ͱ͕͋Δ
25. ݱঢ়ͷsqllaͷJOINͷѻ͍ํ
26. ݱঢ়ͷ໰୊఺ • ୯Ұͷstructʹ͔͠ಉ࣌ʹϚοϐϯά͕Ͱ͖ͳ͍ • ྫͩͱaccountςʔϒϧͷΈ • ෳ਺ʹඥ͚ͮΔʹ͸ ToSqlͰSQLΛు͖ͭͭखಈͰScanΛճ͍ͯ͠ Δ
27. ΞΠσΟΞ: ϏϡʔΛ࢖͏ͷ͸Ͳ͏͔ • ϏϡʔΛ࢖ͬͯJOIN͞ΕͨΫΤϦͱ୯ମͷςʔϒϧʹݟ͔͚ͤΔ • ϏϡʔͳΒUNION΋ѻ͑Δ
29. ͜ΕͰ͍͍Μ͡Όͳ͍ʁʁ
30. ͍ͭͰʹgenddlଆͰ΋VIEWΛ࡞ΕΔΑ͏ʹ͢Δ
31. ΋͏গ᩵͠୔͍ͨ͠ • ݩʑͷςʔϒϧͷΧϥϜΛؙ͝ͱ͖͍࣋ͬͯͨ • ݸผͷςʔϒϧΛUPDATEͨ͘͠ͳͬͨΓ͢Δέʔε • structʹϏδωεϩδοΫͷϝιουΛషΓ෇͚͍ͯΔέʔε • TonamelͩͱActiveRecordύλʔϯతʹͦ͏͍͏;͏ʹ͍ͯ͠Δ
32. Ͱ͖ΔΑ͏ʹͯ͠Έͨ
33. ͏·͍͖ͦ͘͏(࣮ݧத)
34. ՝୊ • Where۟༻ͷϝιουͰ໊લ͕ඃͬͨΒͲ͏͢Δͷ͔ • Account.ID͸AccountID(v, operator…)ʹͳΔ͕ɺ͜ͷ๏ଇͰ͍͘ͱඃͬͯ͠·͏͜ͱ͕͋Δͷ Ͱ͸ɾɾɾʁ • Ҋ֎ͳ͍͔΋ʁ • εΩʔϚϚΠάϨʔγϣϯ࣌ʹϏϡʔͷΧϥϜ͕৯͍ҧͬͯഁ໓͠ͳ͍͔Ͳ͏͔ • ςετͰΘ͔Δ͔Βେৎ෉ʁɹͨͩ `u.*` Έ͍ͨͳࢦఆΛ͢ΔͱɺDML࣮ߦத͏ʹരൃ͢Δ͔΋ • MySQLͰsqldefΛ࢖͏ࡍʹϏϡʔΛؚΜͩDDLΛ࢖͏ͱৗʹࠩ෼͕ൃੜ͢Δ • ຊମʹڍಈΛվળ͢ΔPull RequestΛૹΕͳ͍͔ௐࠪத… ࠙਌ձͰ୭͔ʹฉ͔͘΋
35. ͦͷଞ sqlla ʹؔ͢Δల๬ • Goͷdatabase/sqlʹNull[T]͕ೖͬͨΒଈରԠ͢Δͭ΋ΓͰ͢ • ΧελϜςϯϓϨʔτ • sqllaͷੜ੒࣌ͷςϯϓϨʔτΛϨσΟϝΠυͷ΋ͷ͡Όͳͯ͘Ϣʔ βʔ͕࡞ͬͨ΋ͷΛద༻Ͱ͖ΔΑ͏ʹ͢Δ • υΩϡϝϯτ • sqlla handbook ͳͲ͸Ͳ͏͔ͱݴΘΕ͍ͯΔ
36. Ҏ্ʂ