trjhtr

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

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.