$30 off During Our Annual Pro Sale. View Details »

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

mackee
August 26, 2023

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

mackee

August 26, 2023
Tweet

More Decks by mackee

Other Decks in Programming

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