Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Sign up for free
Menu
Search
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Pricing
Search
Sign in
Sign up for free
Go向けORM sqllaの紹介と JOINやUNIONを含んだクエリの扱い方
Search
mackee
August 26, 2023
Programming
5.5k
0
Share
Embed
Copy iframe code
Copy JS code
Copy link
Start on current slide
Go向けORM sqllaの紹介と JOINやUNIONを含んだクエリの扱い方
湘南.pm #1
https://shonanpm.connpass.com/event/289094/
mackee
August 26, 2023
Transcript
36 slides
Go͚ORM sqllaͷհͱ JOINUNIONΛؚΜͩΫΤϦ ͷѻ͍ํ macopy a.k.a @mackee_w 2023-08-26 ভೆ.pm #1
ࢲͱ • macopy X: @mackee_w • Perl͕͖ Goͱྑ͠ • ීஈ3DϓϦϯλΛԆʑͱ͍͍ͬͯ͡
·͢ • ࣸਅϥεϕΨεͰϏʔϧͰ͢
sqllaͱ • Go͚ORM • SQLͷΈཱͯʹඞཁͳϝ ιουΛDBεΩʔϚఆ͔ٛΒ ίʔυੜ͢Δͷ͕ಛ • ࢲ(macopy)͕࡞ɾϝϯςφ ϯεΛ͍ͯ͠·͢
• gitHub.com/mackee/go-sqlla
sqllaͷചΓ • ςʔϒϧͷΧϥϜʹରԠͨ͠ϝιου͕ίʔυੜ͞ΕΔͷͰɺิ ʹग़ͯ͘Δ • `column IN (…)`, όϧΫΠϯαʔτ, `INSERT
~ ON DUPLICATEKEY UPDATE` ʹରԠ • ϑϨʔϜϫʔΫύοέʔδߏʹڧ͘ґଘ͠ͳ͍ͷͰɺҰ෦͚ͩ sqllaΛೖΕΔ͜ͱ͕Մೳ
ଞͷORMͱൺΔͱ ʮsqlla͕Βͳ͍͜ͱʯͰհ͢Δํ͕ ࡍཱͭ
ͦͦORMͬͯ ͳʹ
Object-Relational Mapper/Mapping • ͭ·ΓRDBMSͷΫΤϦ݁ՌΛϓϩάϥϛϯάݴޠݻ༗ͷΦϒδΣΫ τʹϚοϐϯά͢Δߦҝ, ͦ͘͠ΕΛΔπʔϧ/ϥΠϒϥϦ
͜͜ͰΈͳ͞Μɺ಄ͷதͰ ORMͱݴΘΕΔϥΠϒϥϦΛࢥ͍ු ͔ग़͍ͯͩ͘͞
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ʹͰ͖Δ͚ͩྻڍͯ͠ͱ͓ئ͍ͨ͠(ͳ͍ͷ͕͋Δ͔)
Perlͩͱ • Class::DBI • DBIx::Class • Teng • Aniki •
Otogiri
࠶ܝ: Object-Relational Mapper/Mapping • ͭ·ΓRDBMSͷΫΤϦ݁ՌΛϓϩάϥϛϯάݴޠݻ༗ͷΦϒδΣΫ τʹϚοϐϯά͢Δߦҝ, ͦ͘͠ΕΛΔπʔϧ/ϥΠϒϥϦ
͕ͩɺੈͷதͰݴΘΕ͍ͯΔ ORM ͦΕҎ֎ͷࣄΛ͍ͬͯΔ
ΫΤϦϏϧμʔ • ϓϩάϥϛϯάݴޠͷγϯλοΫεͰDMLΛΈཱͯΔ Teng(Perl)
εΩʔϚཧ • RDBMSͷDDLΛݴޠͷγϯλοΫεͰఆٛͨ͠Γɺͦ͘͠ͷٯ มΛߦ͏ gorm(Go)
εΩʔϚϚΠάϨʔγϣϯ • ͢Ͱʹ͋ΔDBͷDDLΛɺతͷঢ়ଶͷDDLʹͳΔΑ͏ʹมԽͤ͞Δ ActiveRecord(Ruby)
ଞʹ͜Μͳػೳ͕ • ૠೖ࣌ɾߋ৽࣌ϑοΫ • DBͷܕͱϓϩάϥϛϯάݴޠͷܕͷม • in fl ate/de fl
ateͳͲͱݺΕͨΓ͢Δ • ίωΫγϣϯϓʔϦϯά
݁ہͷͱ͜ΖɺRDBMSΛ͏·͘ѻ͏ ͨΊͷϥΠϒϥϦͩͬͨΓ ͦΜͳϥΠϒϥϦͷதͷҰͭͷػೳͱ ͯ͠ORM͕͋Δ
sqllaԿΛͬͯԿΛΒͳ͍͔
sqllaͷػೳ • ΫΤϦϏϧμʔ • ߋ৽࣌ɾૠೖ࣌ϑοΫ • ORM • Ҏ্ʂ
sqlla͕Βͳ͍͜ͱ • εΩʔϚϚΠάϨʔγϣϯ • DBίωΫγϣϯཧ • ߴͳܕม
DBίωΫγϣϯΛ࣋ͨͳ͍ͱ ΫΤϦ࣮ߦ࣌ʹຖճʹsqlla.DB interfaceΛຬͨ͢ΦϒδΣΫτΛ͢
sqlla͚ͩͩͱ͖͍ͭͷͰɺݱ࣮తʹ͜͏ͯ͠ ·͢ • εΩʔϚཧ github.com/mackee/go-genddl • sqllaޓͷstruct͔ΒDDLΛग़ྗ͢Δ • εΩʔϚϚΠάϨʔγϣϯ github.com/k0kubun/sqldef
• DDL͔ΒALTERจΛੜ͢Δ • ίωΫγϣϯཧ database/sql • ΧϥϜͷܕͱGoͷܕͷ૬ޓม֤driverʹ͍ͤͯΔ • ήετͷshogo82148͞Μ(go-sql-driver/mysqlͷίϯτϦϏϡʔλʔ)ײँͯ͠·͢ʂ
࠷ۙͷΈ ʮJOINΛsqllaͰѻ͍͍ͨʯ
ORMʹ͓͚ΔJOIN • ୯ମςʔϒϧΛҾ࣌͘RDBMSͷ݁Ռͷܗ͕มΘΔ͜ͱ͋Μ·Γ ͳ͍ • ΧϥϜ͕ݮΔ͙Β͍ • JOINΛ͢ΔͱΧϥϜ͕૿ݮ͢Δ ΧϥϜͷܕೖΓࠞͬͯ͡͠·͏ •
੩తʹఆٛͨ͠ΦϒδΣΫτʹϚοϐϯά͕Ͱ͖ͳ͍ ͜ͱ͕͋Δ
ݱঢ়ͷsqllaͷJOINͷѻ͍ํ
ݱঢ়ͷ • ୯Ұͷstructʹ͔͠ಉ࣌ʹϚοϐϯά͕Ͱ͖ͳ͍ • ྫͩͱaccountςʔϒϧͷΈ • ෳʹඥ͚ͮΔʹ ToSqlͰSQLΛు͖ͭͭखಈͰScanΛճ͍ͯ͠ Δ
ΞΠσΟΞ: ϏϡʔΛ͏ͷͲ͏͔ • ϏϡʔΛͬͯJOIN͞ΕͨΫΤϦͱ୯ମͷςʔϒϧʹݟ͔͚ͤΔ • ϏϡʔͳΒUNIONѻ͑Δ
None
͜ΕͰ͍͍Μ͡Όͳ͍ʁʁ
͍ͭͰʹgenddlଆͰVIEWΛ࡞ΕΔΑ͏ʹ͢Δ
͏গ͍᩵ͨ͠͠ • ݩʑͷςʔϒϧͷΧϥϜΛؙ͝ͱ͖͍࣋ͬͯͨ • ݸผͷςʔϒϧΛUPDATEͨ͘͠ͳͬͨΓ͢Δέʔε • structʹϏδωεϩδοΫͷϝιουΛషΓ͚͍ͯΔέʔε • TonamelͩͱActiveRecordύλʔϯతʹͦ͏͍͏;͏ʹ͍ͯ͠Δ
Ͱ͖ΔΑ͏ʹͯ͠Έͨ
͏·͍͖ͦ͘͏(࣮ݧத)
՝ • Where۟༻ͷϝιουͰ໊લ͕ඃͬͨΒͲ͏͢Δͷ͔ • Account.IDAccountID(v, operator…)ʹͳΔ͕ɺ͜ͷ๏ଇͰ͍͘ͱඃͬͯ͠·͏͜ͱ͕͋Δͷ Ͱɾɾɾʁ • Ҋ֎ͳ͍͔ʁ •
εΩʔϚϚΠάϨʔγϣϯ࣌ʹϏϡʔͷΧϥϜ͕৯͍ҧͬͯഁ໓͠ͳ͍͔Ͳ͏͔ • ςετͰΘ͔Δ͔Βେৎʁɹͨͩ `u.*` Έ͍ͨͳࢦఆΛ͢ΔͱɺDML࣮ߦத͏ʹരൃ͢Δ͔ • MySQLͰsqldefΛ͏ࡍʹϏϡʔΛؚΜͩDDLΛ͏ͱৗʹ͕ࠩൃੜ͢Δ • ຊମʹڍಈΛվળ͢ΔPull RequestΛૹΕͳ͍͔ௐࠪத… ࠙ձͰ୭͔ʹฉ͔͘
ͦͷଞ sqlla ʹؔ͢Δల • Goͷdatabase/sqlʹNull[T]͕ೖͬͨΒଈରԠ͢ΔͭΓͰ͢ • ΧελϜςϯϓϨʔτ • sqllaͷੜ࣌ͷςϯϓϨʔτΛϨσΟϝΠυͷͷ͡Όͳͯ͘Ϣʔ βʔ͕࡞ͬͨͷΛద༻Ͱ͖ΔΑ͏ʹ͢Δ
• υΩϡϝϯτ • sqlla handbook ͳͲͲ͏͔ͱݴΘΕ͍ͯΔ
Ҏ্ʂ
More Decks by mackee
See All by mackee
Go1.27で導入されるジェネリクスメソッドでできること
mackee
0
890
perlをWebAssembly上で動かすと何が嬉しいの??? / Where does Perl-on-Wasm actually make sense?
mackee
0
1.6k
Agentに至る道 〜なぜLLMは自動でコードを書けるようになったのか〜
mackee
6
7k
今!ソフトウェアエンジニアがハードウェアに手を出すには
mackee
14
7.8k
ワンバイナリWebサービスのススメ
mackee
10
9.4k
tanukistack ライブコーディング / tanukistack live-coding
mackee
0
1k
range over funcの使い道と非同期N+1リゾルバーの夢 / about a range over func
mackee
0
1.2k
perl for shell, awk and sed programmers
mackee
3
3k
今更GoのWebフレームワークを作ろうとしているワケ / Why am I trying to create a Go web framework now?
mackee
1
1.2k
Other Decks in Programming
See All in Programming
Kotlin Fest 2026: Kotlin コンパイラに Contribute してみよう! - Kotlin/Wasm で深い再帰を書けるようにした話
ternbusty
1
390
Python Lambdaにおけるテストしやすい設計とAI支援の活用 / Designing Testable Python Lambda Functions with AI
kitano_yuichi
1
340
あなたはMCPの機能を、まだ知らない。
4geru
1
680
[DroidKnights 2026] Workflow AX 적용기 — 자동 정책 정리부터 E2E까지
dayeon
0
110
自作Discord音声botをLambda MicroVMsに移行した話 / Migrating My Discord Voice Bot to Lambda MicroVMs
akihisaikeda
1
380
ローカルLLMの基本と近況 2611 / Local LLM basics and recent situation 2611
kishida
3
600
TS 7.0 で baseUrl が使えなくなった今、パスエイリアスをどうするか
bicstone
2
740
Growing PicoRuby's Ecosystem on ESP32
yuuu
2
150
Kotlin JVM用に作られたライブラリのKMPを前提とした再設計
jsoizo
0
210
조합이폭발하는디자인시스템이걸눈으로도테스트하라고요
giljongyu
0
780
あなたの await、OS まで届いてる? strace で覗く JavaScript の非同期
hasutosasaki
1
2.4k
Vue.jsで作る空間解析アプリとCapacitorプラグインのData Bridge
hal_spidernight
1
180
Featured
See All Featured
Skip the Path - Find Your Career Trail
mkilby
1
290
Bootstrapping a Software Product
garrettdimon
PRO
306
120k
DevOps and Value Stream Thinking: Enabling flow, efficiency and business value
helenjbeal
2
450
No one is an island. Learnings from fostering a developers community.
thoeni
21
3.9k
How to make the Groovebox
asonas
2
2.6k
The Anti-SEO Checklist Checklist. Pubcon Cyber Week
ryanjones
0
310
Darren the Foodie - Storyboard
khoart
PRO
5
4.1k
How to Get Subject Matter Experts Bought In and Actively Contributing to SEO & PR Initiatives.
livdayseo
1
250
The SEO Collaboration Effect
kristinabergwall1
1
620
Context Engineering - Making Every Token Count
addyosmani
9
1.3k
A designer walks into a library…
pauljervisheath
211
25k
Marketing Yourself as an Engineer | Alaka | Gurzu
gurzu
0
380