ãã®èšäºã¯ every Tech Blog Advent Calendar 2024(å€) 5 æ¥ç®ã®èšäºã§ãã ã¯ããã« ããã«ã¡ã¯ãTIMELINE éçºéš Service Development ãããŠãã ã»ãã ã§ãïŒ åã® Go Conference ãªãã©ã€ã³åæŠãªã®ã§æµ®ãããŠã仿¥ãã®é ã§ãã ä»åã¯ã¹ããåããããã¹ãŒããŒã¢ããªã® API ã Python ãã Go ãžç§»è¡ããéã®ããŒã¿ããŒã¹æäœã®èгç¹ã§ã®èª²é¡ãšå®éã«ã©ã®ãããªè§£æ±ºçãåã£ãã®ãå®è£
ãã¡ã€ã³ã«ç޹ä»ããŸãã ãããã¹ãŒããŒã¢ããªã®ãªãã¬ã€ã¹ãè¡ãããšã«ããèæ¯ãã·ã¹ãã å
šäœã®èª²é¡ã解決çã«é¢ããŠã¯ååã®ããã°ã«èšè¿°ããŠããã®ã§æ¯éãäžèªãã ããã tech.every.tv æè¡ã¹ã¿ã㯠以äžã¯ä»åã®èšäºã«é¢ä¿ã®ãããªãã¬ã€ã¹ååŸã®æè¡ã¹ã¿ãã¯ã«ãªããŸãã èšèª DB ORM ãªãã¬ã€ã¹å Python MySQL PyMySQL ãªãã¬ã€ã¹åŸ Go MySQL sqlboiler + sqlx èª²é¡ ãªãã¬ã€ã¹ãè¡ãã«ãããããŒã¿ããŒã¹æäœã®èгç¹ã§ä»¥äžã® 4 ç¹ã®èª²é¡ããããŸããã ãã¹ãã®äžå𠿢åã®å®è£
ã«ãã¹ãããªãããããªãã¬ã€ã¹åŸã®ã³ãŒããæ£ããæ©èœããããæ€èšŒããææ®µãéãããŠããŸããããã«ãããä¿®æ£åŸã®ã³ãŒããæåŸ
éãã®åäœããããã®å€æãå°é£ã§ãã é·å€§ãª SQL ã®æ±ã 200 è¡ãè¶
ããé·å€§ãª SQL ã¯ãšãªã sqlboiler ã§æžãæããããšã¯éåžžã«å°é£ã§ããããã¯ãsqlboiler ã䞻㫠CRUD æäœã«æé©åãããŠãããè€éãªã¯ãšãªã®æ±ãã«ã¯åããŠããªãããã§ãã ååä»ããã¬ãŒã¹ãã«ããŒã®åé¡ å
ã®ã¯ãšãªã§ã¯ä»¥äžã®äŸã®ããã«ååä»ããã¬ãŒã¹ãã«ããŒ( %(format)s )ãå€çšãããŠããŸãããsqlboiler ã¯ãã®æ©èœããµããŒãããŠããŸãããããã«ããããã¬ãŒã¹ãã«ããŒ(MySQL ã§ã¯ ? )ã§å®è£
ãããã¯ãšãªã§ã¯ãã¯ãšãªãé·ããªãã»ã©å¯èªæ§ãšä¿å®æ§ãæãªãããŸãã WHERE item.item_name like %(search_word)s OR item.item_area like %(search_word)s OR item.item_spec like %(search_word)s OR event_item.event_item_name like %(search_word)s OR event_item.event_item_area like %(search_word)s OR event_item.event_item_spec like %(search_word)s åã®å³æ Œå æ¢åã® Python å®è£
ã§ã¯ã¬ã¹ãã³ã¹ã dict åã§è¿ããããããæè»ãªããŒã¿æ§é ãæ±ãããšãã§ããŸããããããsqlboiler ã§ããŒã¿ããŒã¹æäœãè¡ããšã¬ã¹ãã³ã¹ã¯ tag ãå
ã«æ§é äœã«ãã€ã³ãããããã峿 Œãªåå®çŸ©ãå¿
èŠãšãªããããããªãã¬ã€ã¹ã®éã®è¿œå ã®èª²é¡ãšãªããŸãã å®è£
å
ã«æãã課é¡ç¹ã«å¯ŸåŠããããã以äžã®å®è£
æ¹éãæ¡çšããŸããã é·å€§ãªã¯ãšãªã®ç§»è¡ : é·å€§ãªã¯ãšãªã¯ãå¯èœãªéããã®ãŸãŸ Go ã«ç§»è¡ããŸããããã«ãããæ¢åã®ã¯ãšãªããžãã¯ãä¿æããç§»è¡ã«äŒŽããªã¹ã¯ãæå°éã«æããããšãã§ããŸãã ååä»ããã¬ãŒã¹ãã«ããŒã®äœ¿çš : sqlx ã䜿çšããŠãååä»ããã¬ãŒã¹ãã«ããŒãå®è£
ããŸããããã«ãããã¯ãšãªã®å¯èªæ§ãšä¿å®æ§ãåäžãããããšãã§ããŸãã æ±çšçãªå®è¡é¢æ°ã®äœæ : çã® SQL ã¯ãšãªãå®è¡ããçµæã Go ã®æ§é äœã«ãã€ã³ãããæ±çšçãªé¢æ°ãäœæããŸãããã®ã¢ãããŒãã«ãããç°ãªãã¿ã€ãã®ã¯ãšãªã«å¯ŸããŠãæè»ã«å¯Ÿå¿ããããšãå¯èœã«ãªããŸãã ã¯ãšãªã®ç§»è¡ã«ã€ã㊠ãé·å€§ãªã¯ãšãªã¯å¯èœãªéããã®ãŸãŸ Go ã«ç§»è¡ããããšããæ¹éã«åºã¥ããsqlboiler ã§ç§»è¡å¯èœãªã¯ãšãªãšçã¯ãšãªãæç¢ºã«åºå¥ããããã«ã次ã®ãããªãã£ã¬ã¯ããªæ§æãæ¡çšããŸããã repository/ âââ models/ â âââ item.go â âââ favorite.go â âââ menu.go â âââ user.go âââ rawquery/ â âââ util.go â âââ item_builder.go â âââ menu_builder.go âââ item.go âââ favorite.go âââ menu.go âââ user.go repository ãã£ã¬ã¯ããªçŽäžã«ã¯ãsqlboiler ãçšããŠç§»è¡ãããã¯ãšãªã®å®è£
ããããŸããäžæ¹ã§ãrepository/rawquery ãã£ã¬ã¯ããªã«ã¯ãçã¯ãšãªãçŽæ¥æ±ãå®è£
ãé
眮ããŠããŸãããããã®çã¯ãšãªã¯ãsqlboiler ã® Raw 颿°ãã©ãããããŠãŒãã£ãªãã£é¢æ°ãä»ããŠãrepository çŽäžã®ãã¡ã€ã«ããåŒã³åºãããŸããrepository/models ãã£ã¬ã¯ããªã«ã¯ãã¯ãšãªå®è¡æã«çµæããã€ã³ãããããã®æ§é äœãå®çŸ©ãããŠããŸãã ãã®æ§æã«ãããã¯ãšãªã®çš®é¡ããšã«è²¬åãåé¢ããã³ãŒãã®æŽçãšä¿å®æ§ã®åäžãå³ã£ãŠããŸãã ååä»ããã¬ãŒã¹ãã«ããŒã sqlx ã§å®è£
次ã«ãååä»ããã¬ãŒã¹ãã«ããŒã®å®è£
ã«ã€ããŠèª¬æããŸããæ¢åã® Python å®è£
ã§ã¯ pymysql ã䜿çšãã %(format)s 圢åŒã§ååä»ããã¬ãŒã¹ãã«ããŒãå®è£
ããŠããŸãããããããsqlboiler ã«ã¯ãã®æ©èœããªããããsqlx ãæ¡çšããŸããã ååä»ããã¬ãŒã¹ãã«ããŒã䜿çšããããšã§ãé·å€§ãªã¯ãšãªã«ããã倿°ã®åŒæ°ãéè€ããåŒæ°ã®åãæ±ãã容æã«ãªããŸããããã§ã¯ãååä»ããã¬ãŒã¹ãã«ããŒãå«ãçã¯ãšãªãåŒæ°ã®å®è£
ãããã³ãããããã€ã³ããã颿°ã®å®è£
ã«ã€ããŠé ã远ã£ãŠèª¬æããŸãã 以äžã¯ãrepository/rawquery ã«ããååä»ããã¬ãŒã¹ãã«ããŒã«æž¡ãããåŒæ°ããã£ãŒã«ãã«æã€æ§é äœãåæå颿°ãååä»ããã¬ãŒã¹ãã«ããŒãå«ãçã¯ãšãªãè¿ãã¡ãœãããããã³åŒæ°ãè¿ãã¡ãœããã®å®è£
äŸã§ãã // repository/rawquery/item_builder.go package rawquery type ItemBuilder struct { price int janCode string tax int } func NewItemBuilder(name string , price int , janCode string , tax int ) *ItemBuilder { return &ItemBuilder{ price: price, janCode: janCode, tax: tax, } } func (b *ItemBuilder) BuildQueryWithArgs() (ReBindedQueryArgs, error ) { return buildQueryWithArgsDefault(b.rawQuery(), b.args()) } func (b *ItemBuilder) rawQuery() string { q := ` SELECT name,item_code,price,jan_code,tax_rate FROM item WHERE price > :price AND jan_code = :jan_code` if b.tax != nil { q += " AND tax_rate = :tax_rate" } return q } func (b *ItemBuilder) args() map [ string ] interface {} { args := map [ string ] interface {}{ "price" : b.price, "jan_code" : b.janCode, } if b.tax != nil { args[ "tax_rate" ] = *b.tax } return args } ItemBuilder æ§é äœã¯ãã¯ãšãªã«å¿
èŠãªåŒæ°ãä¿æããŸãã BuildQueryWithArgs ã¡ãœãããåŒã³åºããšãsqlx ã䜿çšããŠååä»ããã¬ãŒã¹ãã«ããŒãå«ãŸããçã¯ãšãªã®ãã¬ãŒã¹ãã«ããŒãé©åãªåœ¢åŒã«çœ®ãæããåŒæ°ã®é åºã«æºæ ãã interface{} åã®ã¹ã©ã€ã¹ãè¿ããŸãã 以äžã¯ã BuildQueryWithArgs ã¡ãœããã®å®è¡çµæã®äŸã§ãã // repository/rawquery/util.go type ReBindedQueryArgs struct { Query string Args [] interface {} } func buildQueryWithArgsDefault(rawQuery string , args map [ string ] interface {}) (ReBindedQueryArgs, error ) { namedQuery, namedArgs, err := sqlx.Named(rawQuery, args) if err != nil { return ReBindedQueryArgs{}, err } return ReBindedQueryArgs{Query: sqlx.Rebind(sqlx.QUESTION, namedQuery), Args: namedArgs}, nil } sqlx.Named(rawQuery, args) ã¯ãçã¯ãšãª(rawQuery)ãšåŒæ°(args)ãåãåããã¯ãšãªå
ã®ååä»ããã¬ãŒã¹ãã«ããŒãåŒæ°ã®å€ã§çœ®ãæããŸãã眮ãæããããã¯ãšãª(namedQuery)ãšåŒæ°(namedArgs)ãè¿ããŸãã sqlx.Rebind(sqlx.QUESTION, namedQuery) ã䜿çšããŠãååä»ããã¬ãŒã¹ãã«ããŒã ? ã«åãã€ã³ãããŸãããããŠãåãã€ã³ããããã¯ãšãªãšåŒæ°ãå«ã ReBindedQueryArgs ãè¿ããŸãã 以äžã¯ buildQueryWithArgsDefault ãå®è¡ããçµæã«ãªããŸãã sql := ` SELECT name,item_code,price,jan_code,tax_rate FROM item WHERE price > :price AND jan_code = :jan_code` args := map [ string ] interface {}{ "jan_code" : 12345 , "price" : 200 , } queryArgs, _ := buildQueryWithArgsDefault(sql, args) fmt.Println(queryArgs) # å®è¡çµæ { SELECT name,item_code,price,jan_code,tax_rate FROM item WHERE price > ? AND jan_code = ? [ 200 12345 ] } ååä»ããã¬ãŒã¹ãã«ã㌠:price , :jan_code ã ? ã«ãåŒæ°ãååä»ããã¬ãŒã¹ãã«ãã«å¯Ÿå¿ããé åºã® slice ã«ãªã£ãŠããããšãããããŸãã sqlboiler ãçšããã¯ãšãªã®å®è¡é¢æ° 次ã«çã¯ãšãªãå®è¡ã Go ã®æ§é äœã« bind ããæ±çšçãªé¢æ°ã«ã€ããŠèª¬æããŸãã 以äžãå
·äœçãªå®è£
ã«ãªããŸãã // repository/rawquery/util.go func Execute[T any](ctx context.Context, exec boil.ContextExecutor, queryArgs ReBindedQueryArgs) (*T, error ) { var result T if err := queries.Raw(queryArgs.Query, queryArgs.Args...).Bind(ctx, exec, &result); err != nil { return nil , err } return &result, nil } ååŒæ° T ã«ã¯ response ã«æåŸ
ããæ§é äœãæå®ããŸãã åŒæ°ã«æå®ããã ReBindedQueryArgs ã® Query ãš Args ãçšã㊠queries.Raw ã§ã¯ãšãªãçæã Bind ã§ result ã«ã¯ãšãªã®çµæããã€ã³ããŸãã å®è¡æ¹æ³ æåŸã« repository çŽäžã®ãã¡ã€ã«ã®å®è£
ã«ã€ããŠèª¬æããŸãã 以äžã®ããã«å®è£
ããããšã§çã¯ãšãªãæèããããšãªãããŒã¿ããŒã¹æäœãè¡ããããã«ããããšãçã¯ãšãªã廿¢ã sqlboiler ã§ã®å®è£
ã«çµ±äžããæã®åœ±é¿ãæå°éã«ãªãããã«ããŠããŸãã // repository/item.go type ItemRepository struct {} func NewItemRepository() *ItemRepository { return &ItemRepository{} } func (r *ItemRepository) ListItem(ctx context.Context, exec boil.ContextExecutor, name string , price int , janCode string , tax int ) (*models.Items, error ) { queryArgs, err := rawquery.NewItemBuilder(name, price, janCode, tax).BuildQueryWithArgs() if err != nil { return nil , fmt.Errorf( "failed to build item query args: %w" , err) } res, err := rawquery.Execute[models.Items](ctx, exec, queryArgs) if err != nil { return nil , fmt.Errorf( "failed to get items: %w" , err) } return res, nil } ãŸãšã ãã®èšäºã§ã¯ããªãã¬ã€ã¹ãããžã§ã¯ãã«ãããããŒã¿ããŒã¹æäœã®èª²é¡ãšãããã«å¯Ÿããå®è£
æ¹éã«ã€ããŠè©³ãã玹ä»ããŸãããçæ³çã«ã¯ããªãã¬ã€ã¹åã«æ¢åã³ãŒãã«ãã¹ãã远å ããæäœéã®ãªãã¡ã¯ã¿ãªã³ã°ãè¡ãããšãæãŸããã§ããããããä»åã¯è¿
éãªç§»è¡ãšãGo ãžã®æžãæãåŸã«ãªãã¡ã¯ã¿ãªã³ã°ãé²ãããšããæ¹éã®ããšãçã¯ãšãªããã®ãŸãŸç§»è¡ããããšã«ããŸããã sqlboiler ãš sqlx ãšããäºã€ã®ç°ãªã ORM ã䜵çšããããšã«ã¯ç¡çãããããã«æããããããããŸããããçµæãšããŠè²¬åãé©åã«åå²ãããããè¯ãã³ãŒããžãšè¿ã¥ãããšæããŠããŸãã Go Conference 2024 ãŸã§ãããš 3 æ¥ïŒ gocon.jp æ ªåŒäŒç€ŸãšããªãŒ ã¯ãPlatinum Gold ã¹ãã³ãµãŒãšã㊠Go Conference 2024 ã«åå ããŸãã ãã²ãããŒã¹ãã»ãã·ã§ã³ã§ãäŒãããŸãããïŒ gocon.jp