Golang Query string as part of query

Question

I would like to use string as part of query.

func GetAll(id int) {
  var queryPart string
  if id != 0 {
    queryPart = fmt.Sprintf("WHERE id = %d", id)
  } else {
    queryPart = ""
  }


  database.DB.Query("SELECT name FROM table ?, queryPart)
}

In database.DB I've got *db.Sql instance.

How to get it work? With that code I still get "You have an error in your SQL syntax".

Shawn Throop · Accepted Answer · 2018-01-01 18:31:31Z

5

You can build your query gradually, only adding the arguments if required.

// Build a query (columns and table being strings)
q := fmt.Sprintf("SELECT %s FROM %s", columns, table)
args := []interface{}{}

// Add conditional query/args
if id != 0 {
 q = fmt.Sprintf("%s WHERE id=?",q)
 args = append(args,id)    
}

// Perform the query
database.DB.Query(q,args...)

If you find yourself doing a lot of this, consider writing a small query builder which will let you easily build sql queries without so much fuss. It can be relatively simple, with slots for select, joins etc and an array for args.

edited Jan 1, 2018 at 18:31

Shawn Throop

1,2991 gold badge13 silver badges29 bronze badges

answered Dec 30, 2017 at 14:10

Kenny Grant

9,7132 gold badges35 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kenny Grant Over a year ago

@mkopriva thanks for the edit, I was editing at the same time and it clashed I think.

mkopriva Over a year ago

I saw that, no worries ;)

nexequ Over a year ago

Perfect, if you got some example of your simple query builder, I would really like to see it :)

Kenny Grant Over a year ago

The one I wrote/use is here, it may or may not suit you or provide a useful ref github.com/fragmenta/query

Marc · Accepted Answer · 2017-12-30 11:58:30Z

1

Placeholders (?) are for variables only, not statements.

You should instead have two separate queries:

  if id != 0 {
    database.DB.Query("SELECT name FROM table WHERE id = ?", id)
  } else {
    database.DB.Query("SELECT name FROM table")
  }

Additionally: using Sprintf("WHERE id = %d", id) defeats the purpose of placeholders.

answered Dec 30, 2017 at 11:58

Marc

21.6k6 gold badges54 silver badges56 bronze badges

2 Comments

nexequ Over a year ago

Is it the only way? I've got mega complicated query and I don't want to duplicate code.

mkopriva Over a year ago

@nexequ you can concatenate your query string with + and collect arguments into a slice with append, and then, at the end, execute the query like so: Query(queryString, args...).

Collectives™ on Stack Overflow

Golang Query string as part of query

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related