Upgrade to Pro — share decks privately, control downloads, hide ads and more …

github.com/pinzolo/sqlt

pinzolo
January 18, 2019

 github.com/pinzolo/sqlt

pinzolo

January 18, 2019
Tweet

More Decks by pinzolo

Other Decks in Technology

Transcript

  1. ͜Μͳ΍ͭ ex: Doma SELECT * FROM item WHERE status =

    'AVAILABLE' /*%if name != null*/ AND name = /*name*/'John Doe' /*%end*/
  2. Sample code SQL SELECT * FROM users WHERE id IN

    /*% in "ids" %*/(1, 2) AND name = /*% p "name" %*/'John Doe' /*%- if get "onlyMale" %*/ AND sex = 'MALE' /*%- end %*/ ORDER BY /*% out "order" %*/id
  3. Sample code Go st := sqlt.New(sqlt.Postgres) query, args, err :=

    st.Exec(s, map[string]interface{}{ "ids": []int{1, 2, 3}, "order": "name DESC", "onlyMale": false, "name": "Alex", })
  4. Sample code Generated SQL SELECT * FROM users WHERE id

    IN ($1, $2, $3) AND name = $4 ORDER BY name DESC
  5. Sample code Generated SQL (ExecNamed) SELECT * FROM users WHERE

    id IN (:ids__1, :ids__2, :ids__3) AND name = :name ORDER BY name DESC
  6. Sample code Generated SQL (Annotation Option) SELECT * FROM users

    WHERE id IN ($1, $2, $3)/*# ids */ AND name = $4/*# name */ ORDER BY name DESC
  7. Template.Delims ຊདྷ {{ ͱ }} Ͱ͋Δ text/template ͷσϦϛλΛมߋ͢Δ ػೳɻ ͜ΕΛ

    /*% ͱ %*/ ͱ͍͏SQLʹͱͬͯ͸ίϝϯτͰ͋Δจࣈʹ มߋ͍ͯ͠ΔͷͰɺςϯϓϨʔτͷ֤छ໋ྩΛແಟԽ͍ͯ͠Δɻ
  8. ྫ1ʣύϥϝʔλͷ໾ׂ໰୊ SELECT * FROM users WHERE name LIKE /*% infix

    "name" %*/'John Doe' /*% if get "available" %*/ AND status = /*% in "stats" %*/(NULL) /*% end %*/ /*% if get "email" %*/ AND email = /*% p "email" %*/'[email protected]' /*% end %*/
  9. ྫ3ʣΤεέʔϓॲཧͨ͠ύϥϝʔλ͕ ඞཁ SELECT id , name , name = /*%

    out "name" %*/'John' AS matched FROM users WHERE name LIKE /*% infix "name" %*/'John' ORDER BY matched DESC, name
  10. ࢒೦ͳ͕Β type Form struct { Name string } st :=

    sqlt.New(sqlt.Postgres) query, args, err := st.Exec(s, map[string]interface{}{ "form": Form{Name: "' OR 1 = 1;"}, })
  11. range Ͱ΋ type V struct { Value string } st

    := sqlt.New(sqlt.Postgres) query, args, err := st.Exec(s, map[string]interface{}{ "values": []V{ V{"' OR 1 = 1;"}, V{"foo"}, V{"bar"}, }, })
  12. ॻ͚ͯ͠·͏ SELECT * FROM users WHERE ( /*%- range $i,

    $v := get "values" %*/ /*%- if ne $i 0 %*/ OR /*% end %*/ name = '/*% $v.Value %*/' /*%- end %*/ )
  13. ͪͳΈʹ SELECT * FROM users WHERE ( /*%- range $i,

    $v := get "values" %*/ /*%- if ne $i 0 %*/ OR /*% end %*/ name = /*% p (name "values" $i "Value") %*/'' /*%- end %*/ ) ҆શʹॻ͘ํ๏͸ఏڙ͍ͯ͠Δɻ