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
   

   View Slide

2. ࢲͱ͸
   • macopy X: @mackee_w
   
   
   • Perl͕޷͖ Goͱ͸஥ྑ͠
   
   
   • ීஈ͸3DϓϦϯλΛԆʑͱ͍͍ͬͯ͡
   ·͢
   
   
   • ࣸਅ͸ϥεϕΨεͰϏʔϧͰ͢
   

   View Slide

3. sqllaͱ͸
   • Go޲͚ORM
   
   
   • SQLͷ૊Έཱͯʹඞཁͳϝ
   ιουΛDBεΩʔϚఆ͔ٛΒ
   ίʔυੜ੒͢Δͷ͕ಛ௃
   
   
   • ࢲ(macopy)͕࡞੒ɾϝϯςφ
   ϯεΛ͍ͯ͠·͢
   
   
   • gitHub.com/mackee/go-sqlla
   

   View Slide

4. sqllaͷചΓ
   • ςʔϒϧͷΧϥϜʹରԠͨ͠ϝιου͕ίʔυੜ੒͞ΕΔͷͰɺิ׬
   ʹग़ͯ͘Δ
   
   
   • `column IN (…)`, όϧΫΠϯαʔτ, `INSERT ~ ON DUPLICATEKEY
   UPDATE` ʹରԠ
   
   
   • ϑϨʔϜϫʔΫ΍ύοέʔδߏ੒ʹڧ͘ґଘ͠ͳ͍ͷͰɺҰ෦෼͚ͩ
   sqllaΛೖΕΔ͜ͱ͕Մೳ
   

   View Slide

5. ଞͷORMͱൺ΂Δͱ
   
   
   ʮsqlla͕΍Βͳ͍͜ͱʯͰ঺հ͢Δํ͕
   
   
   ࡍཱͭ
   

   View Slide

6. ͦ΋ͦ΋ORMͬͯ
   ͳʹ
   

   View Slide

7. Object-Relational Mapper/Mapping
   • ͭ·ΓRDBMSͷΫΤϦ݁ՌΛϓϩάϥϛϯάݴޠݻ༗ͷΦϒδΣΫ
   τʹϚοϐϯά͢Δߦҝ, ΋͘͠͸ͦΕΛ΍Δπʔϧ/ϥΠϒϥϦ
   

   View Slide

8. ͜͜ͰΈͳ͞Μɺ಄ͷதͰ
   
   
   ORMͱݴΘΕΔϥΠϒϥϦΛࢥ͍ු
   ͔΂ग़͍ͯͩ͘͞
   

   View Slide

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ʹͰ͖Δ͚ͩྻڍͯ͠ͱ͓ئ͍ͨ͠(ͳ͍΋ͷ͕͋Δ͔΋)
   

   View Slide

10. Perlͩͱ
    • Class::DBI
    
    
    • DBIx::Class
    
    
    • Teng
    
    
    • Aniki
    
    
    • Otogiri
    

    View Slide

11. ࠶ܝ: Object-Relational Mapper/Mapping
    • ͭ·ΓRDBMSͷΫΤϦ݁ՌΛϓϩάϥϛϯάݴޠݻ༗ͷΦϒδΣΫ
    τʹϚοϐϯά͢Δߦҝ, ΋͘͠͸ͦΕΛ΍Δπʔϧ/ϥΠϒϥϦ
    

    View Slide

12. ͕ͩɺੈͷதͰݴΘΕ͍ͯΔ
    
    
    ORM͸
    
    
    ͦΕҎ֎ͷ࢓ࣄΛ΍͍ͬͯΔ
    

    View Slide

13. ΫΤϦϏϧμʔ
    • ϓϩάϥϛϯάݴޠͷγϯλοΫεͰDMLΛ૊ΈཱͯΔ
    Teng(Perl)
    

    View Slide

14. εΩʔϚ؅ཧ
    • RDBMSͷDDLΛݴޠͷγϯλοΫεͰఆٛͨ͠Γɺ΋͘͠͸ͦͷٯ
    ม׵Λߦ͏
    gorm(Go)
    

    View Slide

15. εΩʔϚϚΠάϨʔγϣϯ
    • ͢Ͱʹ͋ΔDBͷDDLΛɺ໨తͷঢ়ଶͷDDLʹͳΔΑ͏ʹมԽͤ͞Δ
    ActiveRecord(Ruby)
    

    View Slide

16. ଞʹ΋͜Μͳػೳ͕
    • ૠೖ࣌ɾߋ৽࣌ϑοΫ
    
    
    • DBͷܕͱϓϩάϥϛϯάݴޠͷܕͷม׵
    
    
    • in
    fl
    ate/de
    fl
    ateͳͲͱݺ͹ΕͨΓ͢Δ
    
    
    • ίωΫγϣϯϓʔϦϯά
    

    View Slide

17. ݁ہͷͱ͜ΖɺRDBMSΛ͏·͘ѻ͏
    ͨΊͷϥΠϒϥϦͩͬͨΓ
    
    
    ͦΜͳϥΠϒϥϦͷதͷҰͭͷػೳͱ
    ͯ͠ORM͕͋Δ
    

    View Slide

18. sqlla͸ԿΛ΍ͬͯԿΛ΍Βͳ͍͔
    

    View Slide

19. sqllaͷػೳ
    • ΫΤϦϏϧμʔ
    
    
    • ߋ৽࣌ɾૠೖ࣌ϑοΫ
    
    
    • ORM
    
    
    • Ҏ্ʂ
    

    View Slide

20. sqlla͕΍Βͳ͍͜ͱ
    • εΩʔϚϚΠάϨʔγϣϯ
    
    
    • DBίωΫγϣϯ؅ཧ
    
    
    • ߴ౓ͳܕม׵
    

    View Slide

21. DBίωΫγϣϯΛ࣋ͨͳ͍ͱ͸
    ΫΤϦ࣮ߦ࣌ʹຖճʹsqlla.DB interfaceΛຬͨ͢ΦϒδΣΫτΛ౉͢
    

    View Slide

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ͷίϯτϦϏϡʔλʔ)ײँͯ͠·͢ʂ
    

    View Slide

23. ࠷ۙͷ೰Έ
    
    
    ʮJOINΛsqllaͰѻ͍͍ͨʯ
    

    View Slide

24. ORMʹ͓͚ΔJOIN໰୊
    • ୯ମςʔϒϧΛҾ࣌͘͸RDBMSͷ݁Ռͷܗ͕มΘΔ͜ͱ͸͋Μ·Γ
    ͳ͍
    
    
    • ΧϥϜ਺͕ݮΔ͙Β͍
    
    
    • JOINΛ͢ΔͱΧϥϜ਺͕૿ݮ͢Δ ΧϥϜͷܕ΋ೖΓࠞͬͯ͡͠·͏
    
    
    • ੩తʹఆٛͨ͠ΦϒδΣΫτʹϚοϐϯά͕Ͱ͖ͳ͍ ͜ͱ͕͋Δ
    

    View Slide

25. ݱঢ়ͷsqllaͷJOINͷѻ͍ํ
    

    View Slide

26. ݱঢ়ͷ໰୊఺
    • ୯Ұͷstructʹ͔͠ಉ࣌ʹϚοϐϯά͕Ͱ͖ͳ͍
    
    
    • ྫͩͱaccountςʔϒϧͷΈ
    
    
    • ෳ਺ʹඥ͚ͮΔʹ͸ ToSqlͰSQLΛు͖ͭͭखಈͰScanΛճ͍ͯ͠
    Δ
    

    View Slide

27. ΞΠσΟΞ: ϏϡʔΛ࢖͏ͷ͸Ͳ͏͔
    • ϏϡʔΛ࢖ͬͯJOIN͞ΕͨΫΤϦͱ୯ମͷςʔϒϧʹݟ͔͚ͤΔ
    
    
    • ϏϡʔͳΒUNION΋ѻ͑Δ
    

    View Slide

28. View Slide

29. ͜ΕͰ͍͍Μ͡Όͳ͍ʁʁ
    

    View Slide

30. ͍ͭͰʹgenddlଆͰ΋VIEWΛ࡞ΕΔΑ͏ʹ͢Δ
    

    View Slide

31. ΋͏গ᩵͠୔͍ͨ͠
    • ݩʑͷςʔϒϧͷΧϥϜΛؙ͝ͱ͖͍࣋ͬͯͨ
    
    
    • ݸผͷςʔϒϧΛUPDATEͨ͘͠ͳͬͨΓ͢Δέʔε
    
    
    • structʹϏδωεϩδοΫͷϝιουΛషΓ෇͚͍ͯΔέʔε
    
    
    • TonamelͩͱActiveRecordύλʔϯతʹͦ͏͍͏;͏ʹ͍ͯ͠Δ
    

    View Slide

32. Ͱ͖ΔΑ͏ʹͯ͠Έͨ
    

    View Slide

33. ͏·͍͖ͦ͘͏(࣮ݧத)
    

    View Slide

34. ՝୊
    • Where۟༻ͷϝιουͰ໊લ͕ඃͬͨΒͲ͏͢Δͷ͔
    
    
    • Account.ID͸AccountID(v, operator…)ʹͳΔ͕ɺ͜ͷ๏ଇͰ͍͘ͱඃͬͯ͠·͏͜ͱ͕͋Δͷ
    Ͱ͸ɾɾɾʁ
    
    
    • Ҋ֎ͳ͍͔΋ʁ
    
    
    • εΩʔϚϚΠάϨʔγϣϯ࣌ʹϏϡʔͷΧϥϜ͕৯͍ҧͬͯഁ໓͠ͳ͍͔Ͳ͏͔
    
    
    • ςετͰΘ͔Δ͔Βେৎ෉ʁɹͨͩ `u.*` Έ͍ͨͳࢦఆΛ͢ΔͱɺDML࣮ߦத͏ʹരൃ͢Δ͔΋
    
    
    • MySQLͰsqldefΛ࢖͏ࡍʹϏϡʔΛؚΜͩDDLΛ࢖͏ͱৗʹࠩ෼͕ൃੜ͢Δ
    
    
    • ຊମʹڍಈΛվળ͢ΔPull RequestΛૹΕͳ͍͔ௐࠪத… ࠙਌ձͰ୭͔ʹฉ͔͘΋
    

    View Slide

35. ͦͷଞ sqlla ʹؔ͢Δల๬
    • Goͷdatabase/sqlʹNull[T]͕ೖͬͨΒଈରԠ͢Δͭ΋ΓͰ͢
    
    
    • ΧελϜςϯϓϨʔτ
    
    
    • sqllaͷੜ੒࣌ͷςϯϓϨʔτΛϨσΟϝΠυͷ΋ͷ͡Όͳͯ͘Ϣʔ
    βʔ͕࡞ͬͨ΋ͷΛద༻Ͱ͖ΔΑ͏ʹ͢Δ
    
    
    • υΩϡϝϯτ
    
    
    • sqlla handbook ͳͲ͸Ͳ͏͔ͱݴΘΕ͍ͯΔ
    

    View Slide

36. Ҏ্ʂ
    

    View Slide