I’m working on a backend service to add a user to a Postgres database using squirrel and echo packages. I have the following the code below but I keep getting an postgres error pq: syntax error at or near ",", when I do a POST request with the following JSON:
{
"user_name": "IronMan",
"first_name": "Tony",
"last_name": "Stark",
"email": "[email protected]",
"user_status": "I",
"department": "Comics"
}
Note: in the database table, a user_id is automatically generated.
Here is my code:
func CreateUser(c echo.Context) error {
newUser := new(models.User)
c.Bind(&newUser)
//dbTableName := os.Getenv("DB_TABLE_NAME")
sqlStatement, _, err := sq.Insert(dbTableName).
Columns("user_name", "first_name", "last_name", "email", "user_status", "department").
Values(newUser.User_Name, newUser.First_Name, newUser.Last_Name, newUser.Email,
newUser.User_Status, newUser.Department).
ToSql()
if err != nil {
c.Logger().Panicf("Unable to create query: %s n%s", sqlStatement, err)
}
row := db.DB.QueryRow(sqlStatement)
err = row.Scan(newUser.USER_ID, newUser.User_Name, newUser.First_Name, newUser.Last_Name,
newUser.Email, newUser.User_Status, newUser.Department)
if err != nil {
c.Logger().Panicf("Unable to scan results query:n%sn%s", err, row)
}
//defer sqlStatement.Close()
return c.JSON(http.StatusCreated, newUser)
}
The error appears to coming from the Scan function in squirrel, but I don’t understand if the error is saying I am missing a column, because I have them all listed.
If you print your sqlStatement variable, you’ll see that it is a parameterized statement, which should be prepared. As the docs of ToSql()(string, []interface{}, error say, “ToSql returns a SQL representation of the Sqlizer, along with a slice of args as passed to e.g. database/sql.Exec”.
So what you should do, is something along these lines:
sqlStmt, sqlParams, err := sq.Insert("foo").Columns("bar").Values("baz").ToSql()
// sqlStmt is now INSERT INTO foo (bar) VALUES ($1)
// sqlParams is now []interface{{"baz"}}
db.QueryRow(sqlStmt, sqlParams...).Scan()
I suggest taking a look at insert_test.go to see what other options you have.
2