CODEWITHSUNDEEP
sqlsqlitego
cowlicks
cowlicks

Reputation: 1138

Checking if a value exists in sqlite db with Go

I'm writing code to manage users in a sqlite database with Go.

I'm trying to check if a username is taken, but my code is ugly.

My table looks like:

    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE,
    password TEXT

And I check if a username is taken with:

func UserExists(db * sql.DB, username string) bool {
    sqlStmt := `SELECT username FROM userinfo WHERE username = ?`
    count := 0
    rows, err := db.Query(sqlStmt, username)
    Check(err)
    for rows.Next() {  // Can I just check if rows is non-zero somehow?
        count++
    }
    return len(rows) != 0
}

Is there a better query I could use that would tell me if the username value exists in the table in a more staight forward way? Or is there a nicer way to check if rows is non-zero?

Upvotes: 2

Views: 15470

Answers (5)

Dan149
Dan149

Reputation: 46

There's lots of ways of doing this. I personally do:

var usernameCount int
r := db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM users WHERE username = '%s';", username))
r.Scan(&usernameCount)
if usernameCount == 0 { 
     // code here 
}

Upvotes: 0

PrayogaBoedihartoyo
PrayogaBoedihartoyo

Reputation: 25

i think we can use above function to check that condition is already exist at database.

if helper.UserExists(db, username) {
    return username, errors.New("data already exist")
}

because that function returned bool that means if that function is called return will return by default is true. i hope this usefull.

Upvotes: 0

Fath
Fath

Reputation: 84

as long as you are only concerned about the existence of one single information in the Database. I found that this method is quite simpler and efficient.

func emailExists(email string) bool {
    row := db.QueryRow("select user_email from users where user_email= ?", email)
    checkErr(err)
    temp := ""
    row.Scan(&temp)
    if temp != "" {
        return true
    }
    return false
}

if u noticed am only getting a single row. the result of my Query gets scanned in a temp Variable.

then I check whether the temp Variable is empty or not.

if it is not empty, true is returned.

Upvotes: 1

Robert Phillips
Robert Phillips

Reputation: 129

I know this is a bit old, but I don't see any clean answers here. Notice below the if rows.Next() statement which will return a boolean if there are any rows or not:

package main

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
    "log"
)

func main() {
    exists, _ := SelectDBRowExists(`SELECT * FROM GEO_VELOCITY_EVENTS WHERE USERNAME='bob'`)
    log.Println(exists)
}

func SelectDBRowExists(query string) (bool, error) {

    DbConn, err := sql.Open("sqlite3", "/path/to/your/sql.sqlite3")
    if err != nil {
        return false, err
    }
    defer DbConn.Close()
    err = DbConn.Ping()

    if err != nil {
        return false, err
    }

    rows, err := DbConn.Query(query)

    if rows.Next() {
        return true, nil
    } else {
        return false, nil
    }

    defer rows.Close()

    return false, nil
}

Upvotes: 1

cd1
cd1

Reputation: 16534

Use QueryRow to query at most one row. If the query doesn't return any row, it returns sql.ErrNoRows.

func UserExists(db * sql.DB, username string) bool {
    sqlStmt := `SELECT username FROM userinfo WHERE username = ?`
    err := db.QueryRow(sqlStmt, username).Scan(&username)
    if err != nil {
        if err != sql.ErrNoRows {
            // a real error happened! you should change your function return
            // to "(bool, error)" and return "false, err" here
            log.Print(err)
        }

        return false
    }

    return true
}

Upvotes: 10

Related Questions