3

How can I execute raw SQL queries in Buffalo without having to set up my own db connection with sqlx?

To clarify: I have my database connection defined in database.yml, but I don't want to use Pop models at this time.

Wabbitseason
  • 5,641
  • 9
  • 49
  • 60

1 Answers1

2

You can also define your RawQueries with Pop: https://godoc.org/github.com/gobuffalo/pop#Connection.RawQuery

Inside your Buffalo actions you could do something like this:

func (v myResource) MyMethod(c buffalo.Context) error {
    // Get the DB connection from the context
    tx, ok := c.Value("tx").(*pop.Connection) // you get your connection here
    if !ok {
        return errors.WithStack(errors.New("no transaction found"))
    }
    q := tx.RawQuery("select * from foo where id = ?", 1) // you make your query here
    if err := q.All(model); err != nil {
        return errors.WithStack(err)
    }
}

This would be a solution to use Pop but not the predefined models. In that case you just use the connection which comes with the Buffalo context.

apxp
  • 5,240
  • 4
  • 23
  • 43
  • Almost there! I'm having trouble getting the result of the query. I can only read in a single scalar (int or string), but if I try to select an int _and_ a varchar, and define a model with 2 fields like `type MyModel struct { x int y string }` then I get this error: "non-struct dest type struct with >1 columns (2)". Can you please show how to define the model for multiple fields? – Wabbitseason Aug 09 '18 at 19:38
  • 1
    Solved! I had to define the fields as public (with capitals), like so: `type MyModel struct { X int Y string }` – Wabbitseason Aug 09 '18 at 19:48