Query Select and Insert if not exists

Question

Are there any good ways to make this createIndexBlock() method shorter?

It looks wastefully long. I have many tables. I don't like to make these kinds of methods.

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

var db, _ = sql.Open("mysql", "xxx:xxx@(database:3306)/xx")

func createIndexBlock(blockHash string) int64 {

    rows, err := db.Query("SELECT id FROM index_blocks WHERE `hash`=? LIMIT 1", blockHash)
    if err != nil {
        panic(err.Error())
    }

    for rows.Next() {
        var id int64
        err := rows.Scan(&id)
        if err != nil {
            panic(err.Error())
        }
        rows.Close()
        return id
    }

    result, err := db.Exec("INSERT INTO index_blocks (`hash`) values (?)", blockHash)
    if err != nil {
        panic(err.Error())
    }

    id, err := result.LastInsertId()
    if err != nil {
        panic(err.Error())
    }

    return id
}

Answer 1

It seems to me that you are trying to retrieve the ID of a specific blockHash in the database, and if the entry does not exist, you want to create a new entry. This is where INSERT ... ON DUPLICATE KEY UPDATE comes in handy.

INSERT INTO `index_blocks` (`hash`) VALUES (?) 
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();

Here's what you can do:

func createIndexBlock(blockHash string) int64 {
    result, err := db.Exec(`INSERT INTO index_blocks (hash) VALUES (?) 
        ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)`, blockHash)
    if err != nil {
        panic(err.Error())
    }

    id, err := result.LastInsertId()
    if err != nil {
        panic(err.Error())
    }
    return id
}

Alternatively, you can look into INSERT IGNORE, followed by a SELECT. That will work too.