"sql: no rows in result set"

Question

I am handling user auth data posted to my Go backend through an HTML form. I am building on some boilerplate to learn Go better.

My problem is what the following func returns:

func (ctrl UserController) Signin(c *gin.Context) {

  var signinForm forms.SigninForm
  user, err := userModel.Signin(signinForm)

  if err := c.ShouldBindWith(&signinForm, binding.Form); err != nil {
      c.JSON(406, gin.H{"message": "Invalid signin form", "form": signinForm})
      c.Abort()
      return
  }

    if err == nil {
        session := sessions.Default(c)
        session.Set("user_id", user.ID)
        session.Set("user_email", user.Email)
        session.Set("user_name", user.Name)
        session.Save()

        c.JSON(200, gin.H{"message": "User signed in", "user": user})
    } else {
        c.JSON(406, gin.H{"message": "Invalid signin details", "error": err.Error()})
    }

}

The first if statement validates the input, and that works fine (error if the email isn't in proper email format, no error if it is). However, if input is properly validated, the else clause of the second statement is triggered, and the following JSON is returned:

{
error: "sql: no rows in result set",
message: "Invalid signin details"
}

It is probably useful to also post the relevant code in my User model:

//User ...
type User struct {
    ID        int    `db:"id, primarykey, autoincrement" json:"id"`
    Email     string `db:"email" json:"email"`
    Password  string `db:"password" json:"-"`
    Name      string `db:"name" json:"name"`
    UpdatedAt int64  `db:"updated_at" json:"updated_at"`
    CreatedAt int64  `db:"created_at" json:"created_at"`
}

//UserModel ...
type UserModel struct{}

//Signin ...
func (m UserModel) Signin(form forms.SigninForm) (user User, err error) {

    err = db.GetDB().SelectOne(&user, "SELECT id, email, password, name, updated_at, created_at FROM public.user WHERE email=LOWER($1) LIMIT 1", form.Email)

    if err != nil {
        return user, err
    }

    bytePassword := []byte(form.Password)
    byteHashedPassword := []byte(user.Password)

    err = bcrypt.CompareHashAndPassword(byteHashedPassword, bytePassword)

    if err != nil {
        return user, errors.New("Invalid password")
    }

    return user, nil
}

How do I resolve the sql: no rows in result set error?

Solution

You should change the order of operations in your code. At first you need to get data from request with if err := c.ShouldBindWith(&signinForm, binding.Form); err != nil { and after that you need to try get data from database with user, err := userModel.Signin(signinForm)

Comments

[223seneca]

I still get the same error. f err := c.ShouldBindWith(&signinForm, binding.Form); err != nil { shouldn't effect this issue, as it is merely validating the form. I haven't written the user signup route yet, so there is currently nothing in the database. Would that be causing this error, perhaps?

[mkopriva]

@223seneca Pavlo's answer is correct, your ordering is just wrong, it would never work even if you had rows in your database because without setting the request's data to the singinForm value, which is done by the binding mechanism, you would always be passing an empty form.Email to the query and an empty form.Password to the bcrypt's compare function.

[223seneca]

@mkopriva Regarding your comment about the error sql: no rows in result set, I was unsure given the fact that I find that behavior strange (it's odd that it throws an error, as the possibility that there just aren't any users yet doesn't seem error-worthy). I can do without the condescension though, thanks.

[mkopriva]

@223seneca and regardless of whether your order would work or not you should be very careful about passing unvalidated data to your database.

[223seneca]

Good point. I'm super new to Go so I'm likely to miss such subtleties in the drive to simply get my code working.

[mkopriva]

@223seneca the db and the db driver doesn't know or care what the tables represent in your app, whether no-row is ok or not that's not their business. If your signin functionality should continue working if no user with given email exists, then your UserModel.Signin should check specifically for the sql.ErrNoRows value and return nil or whatever you want.