Query Building: Building off a common query builder
The query builders in the package are generally stateless: you must pass in the database connection and logger for every query you want to build. If you want to make all queries share the same database connection and logger, you can build off a common BaseQuery struct that is able to transform into a SelectQuery, InsertQuery, UpdateQuery or DeleteQuery depending on the method you call on it.
// New BaseQuery
base := sq.BaseQuery{
DB: DB,
Log: Logger,
LogFlag: logflag,
}
// Using chained methods to do the same thing
base := WithDB(DB).WithLog(Logger, logflag)
// Alternative using the default logger
base := WithDB(DB).WithDefaultLogger(logflag)
// The below queries will all share the same DB and Logger
base.From() // --> SelectQuery
base.Select() // --> SelectQuery
base.InsertInto() // --> InsertQuery
base.Update() // --> UpdateQuery
base.DeleteFrom() // --> DeleteQuery
To make use of the common DB, when you call Fetch() or Exec() just pass nil in as the DB argument.
base := WithDB(DB).WithLog(Logger, logflag)
base.From(table).
SelectRowx(func(row *sq.Row) {
val1 = row.Int(table.VALUE_1)
val2 = row.String(table.VALUE_2)
}).
Fetch(nil) // <-- no need to pass in a DB
base.InsertInto(table).
Columns(table.VALUE_1, table.VALUE_2).
Values(val1, val2).
Values(val3, val4).
Exec(nil, 0) // <-- no need to pass in a DB
However keep in mind if you do pass in a DB connection, it will take precedence over the common DB. This is useful if you generally want to use a common DB connection, but need to switch to a transaction-based connection on a case-by-case basis.
base := WithDB(DB).WithLog(Logger, logflag)
tx, _ := DB.Begin()
defer tx.Close()
base.From(table).
SelectRowx(func(row *sq.Row) {
val1 = row.Int(table.VALUE_1)
val2 = row.String(table.VALUE_2)
}).
Fetch(tx) // <-- tx will override DB
base.InsertInto(table).
Columns(table.VALUE_1, table.VALUE_2).
Values(val1, val2).
Values(val3, val4).
Exec(tx, 0) // <-- tx will override DB