Request: common interface for update and select builders

[veqryn]

I have an application that applies a ton of where statements to all queries. There is a common struct detailing all of the different filters and where statements that can be added, and I have a common method that currently takes in SelectBuilder and then adds all the Where statements to it for any of the selected filters. We now have some places where we need to do updates on the same table, using the same filters.

I thought there would be an interface or something so that the common method could take in both SelectBuilder and UpdateBuilder, since their Where functions are identical. There is not, and unfortunately I can not simply make my own because the SelectBuilder.Where(...) function returns a SelectBuilder, and the UpdateBuilder.Where(...) function returns an UpdateBuilder.

It would be great if on a redesign there could be a common interface for some of the builder functions that are common between the different builders.

[lann]

Unfortunately this is impossible with squirrel's fundamental design and Go's type system.

[seantcanavan]

Would love this as well. We have a lot of syntatic magic written around a SelectBuilder wrapper function and all of a sudden I've found I need to send an UpdateBuilder to the function as well. The function is 100 lines long so duplicating it just to change the argument to UpdateBuilder and have it work exactly the same as the one that accepts a SelectBuilder is unfortunate.

[veqryn]

I ended up using this interface:

type SqlBuilder interface {
    Where(pred interface{}, args ...interface{}) SqlBuilder
    ToSql() (string, []interface{}, error)
}

[sn0rk64]

@veqryn This will not work. Your interface returns SqlBuilder, the Go type system can't understand it and will throw an error, that Where must return squirrel.SelectBuilder or squirrel.UpdateBuilder

[veqryn]

@sn0rk64 you are correct. I looked at my code and I have an adapter around it:

type SqlBuilder interface {
    Where(pred interface{}, args ...interface{}) SqlBuilder
    ToSql() (string, []interface{}, error)
}

// GenericSelectBuilder exists to generify squirrel.SelectBuilder
type GenericSelectBuilder struct {
    sq.SelectBuilder
}

// Where calls squirrel.SelectBuilder.Where
func (b GenericSelectBuilder) Where(pred interface{}, args ...interface{}) SqlBuilder {
    return GenericSelectBuilder{SelectBuilder: b.SelectBuilder.Where(pred, args...)}
}

// GenericUpdateBuilder exists to generify squirrel.UpdateBuilder
type GenericUpdateBuilder struct {
    sq.UpdateBuilder
}

// Where calls squirrel.UpdateBuilder.Where
func (b GenericUpdateBuilder) Where(pred interface{}, args ...interface{}) SqlBuilder {
    return GenericUpdateBuilder{UpdateBuilder: b.UpdateBuilder.Where(pred, args...)}
}

// GenericWhereBuilder exists to generify GenericBuilder
type GenericWhereBuilder struct {
    GenericBuilder
}

// Where calls GenericBuilder.Where
func (b GenericWhereBuilder) Where(pred interface{}, args ...interface{}) SqlBuilder {
    return GenericWhereBuilder{GenericBuilder: b.GenericBuilder.Where(pred, args...)}
}

GenericBuilder is a copy of the common parts of squirrel's update and select builders, so that I can add 'where' clauses onto other things like 'alter' statements (ie: Clickhouse's ALTER TABLE ... DELETE WHERE ...)

I could make a PR if people were interested... @lann

[src-r-r]

I was in a similar situation. Because I'm lazy I'm making a generic Query method that (originally) was going to take a Builder instance as an argument. Quickly found out that Builder doesn't have a ToSql method, which makes turning it into a query impossible.

As it stands I have to have separate Query methods depending on the type of query builder. An ugly (or beautiful, depending on your perspective) work-around I've found is to pass in the <Whatever>Builder.ToSql method as an argument...

type ToSqlFunc func() (string, []interface{}, error)
func (mydb *MyDbCnx) Execute(toSqlFunc ToSqlFunc) (sql.Result, error) {
  sql, args, err := toSqlFunc()
  // ...and so on and so forth...
}

Since all ToSql functions have the same signature this would work for any statement builder.

But I think a good solution would be to have core method (like ToSql) in a central "base interface", e.g.

type GenericSqlBuilder builder;
func (g *GenericSqlBuilder) ToSql() {
 // ...
}
type SelectBuilder GenericBuilder;

Currently everything is based on builder.Builder.