I found db.Rebind() to help with this. So:
name := "my name"
var p = property{}
// language=SQL
s := "SELECT * FROM property WHERE name=?"
err := db.Get(&p, db.Rebind(s), name)
The language comment at the top is so that IntelliJ can still syntax-check the SQL statement for me in the UI.
I also had to write separate CREATE statements for each database (my application is simultaneously supporting mysql, postgres, and sqlite).
I also found the UPDATE statement syntax between mysql and sqlite to be the same, but postgres required special handling. Since my UPDATE statements are very consistent, I was able to write a function to just kludge-translate from the mysql dialect to the postgres dialect. This is definitely not a generic solution but worked well enough for my unit and integration tests to pass. YMMV.
// RebindMore takes a MySQL SQL string and convert it to Postgres if necessary.
// The db.Rebind() handles converting '?' to '$1', but does not handle SQL statement
// syntactic changes needed by Postgres.
//
// convert: "UPDATE table_name SET a = ?, b = ?, c = ? WHERE d = ?"
// to: "UPDATE table_name SET (a, b, c) = ROW (?, ?, ?) WHERE d = ?"
func RebindMore(db *sqlx.DB, s string) string {
if db.DriverName() != "postgres" {
return s
}
if !strings.HasPrefix(strings.ToLower(s), "update") {
return db.Rebind(s)
}
// Convert a MySQL update statement into a Postgres update statement.
var idx int
idx = strings.Index(strings.ToLower(s), "set")
if idx < 0 {
log.Fatal().Msg("no SET clause in RebindMore (" + s + ")")
}
prefix := s[:idx+3]
s2 := s[idx+3:]
idx = strings.Index(strings.ToLower(s2), "where")
if idx < 0 {
log.Fatal().Msg("no WHERE clause in RebindMore (" + s + ")")
}
suffix := s2[idx:]
s3 := s2[:idx]
s4 := strings.TrimSpace(s3)
arr := strings.Split(s4, ",")
var names = ""
var values = ""
for i := 0; i < len(arr); i++ {
nameEqValue := arr[i]
s5 := strings.ReplaceAll(nameEqValue, " ", "")
nvArr := strings.Split(s5, "=")
if names != "" {
names += ","
}
names += nvArr[0]
if values != "" {
values += ","
}
values += nvArr[1]
}
s6 := prefix + " (" + names + ") = ROW (" + values + ") " + suffix
return db.Rebind(s6)
}
Call it this way:
// language=SQL
s := RebindMore(db, "UPDATE table_name SET a = ?, b = ? WHERE c = ?")
db.MustExec(s, value1, value2)
At some point I will need to add migration, and expect to just add separate code per DB to handle the differences (like for CREATE).
One final thing worth pointing out is that MySQL and Postgres handle capitalization very differently. I ended up just converting every table and column name to lower_case to avoid unnecessary complexity.
