Golang - How to create helper mysql to generate uuid / uuid_short without create new connection? Here's the code

I'm trying to generate UUID_SHORT() to be an ID. Instead of using trigger, I get the UUID_SHORT() first and then insert as an ID. But, I'm confusing of how to create GetUUID() function as helper.

As my code bellow, it always setup new connection before generate the UUID_SHORT() which means there will be so many connection just to generate UUID_SHORT()

How to create func GetUUID() without creating new connection?

Here's the code:

package database
import (
"database/sql"
"fmt"
"log"
"os"
"time"
_ "github.com/go-sql-driver/mysql"
)
type conncetion struct {
sqlDB *sql.DB
}
func NewMysqlConnection(databaseConnection *sql.DB) *conncetion {
return &conncetion{databaseConnection}
}
func SetupMysqlDatabaseConnection() (db *sql.DB) {
var (
driver   = os.Getenv("DB_DRIVERNAME")
username = os.Getenv("DB_USERNAME")
password = os.Getenv("DB_PASSWORD")
host     = os.Getenv("DB_HOST")
port     = os.Getenv("DB_PORT")
name     = os.Getenv("DB_NAME")
)
connection := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true", username, password, host, port, name)
db, err := sql.Open(driver, connection)
if err != nil {
log.Fatal(err)
}
db.SetMaxOpenConns(100)
db.SetMaxIdleConns(100)
db.SetConnMaxLifetime(100 * time.Millisecond)
return
}
func (c *conncetion) GenerateUUID() (uuid uint64, err error) {
uuid = 0
queryGetUUID := c.sqlDB.QueryRow(`SELECT UUID_SHORT()`)
err = queryGetUUID.Scan(
&uuid,
)
return
}
func GetUUID() (uuid uint64, err error) {
mysql := SetupMysqlDatabaseConnection()
db := NewMysqlConnection(mysql)
uuid, err = db.GenerateUUID()
return
}

How about global variable ?

package database
import (
"database/sql"
"fmt"
"log"
"os"
"time"
_ "github.com/go-sql-driver/mysql"
)
type conncetion struct {
sqlDB *sql.DB
}
var globalConnection *conncetion
func GetDB() *conncetion {
return globalConnection
}
func NewMysqlConnection(databaseConnection *sql.DB) *conncetion {
return &conncetion{databaseConnection}
}
func SetupMysqlDatabaseConnection() (db *sql.DB) {
var (
driver   = os.Getenv("DB_DRIVERNAME")
username = os.Getenv("DB_USERNAME")
password = os.Getenv("DB_PASSWORD")
host     = os.Getenv("DB_HOST")
port     = os.Getenv("DB_PORT")
name     = os.Getenv("DB_NAME")
)
connection := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true", username, password, host, port, name)
db, err := sql.Open(driver, connection)
if err != nil {
log.Fatal(err)
}
db.SetMaxOpenConns(100)
db.SetMaxIdleConns(100)
db.SetConnMaxLifetime(100 * time.Millisecond)
return
}
func (c *conncetion) GenerateUUID() (uuid uint64, err error) {
uuid = 0
queryGetUUID := c.sqlDB.QueryRow(`SELECT UUID_SHORT()`)
err = queryGetUUID.Scan(
&uuid,
)
return
}
func GetUUID() (uuid uint64, err error) {
// mysql := SetupMysqlDatabaseConnection()
// db := NewMysqlConnection(mysql)
db = GetDB()
uuid, err = db.GenerateUUID()
return
}
// can be written on main.go / server.go
func init() {
globalConnection = NewMysqlConnection(database.SetupMysqlDatabaseConnection())
}

But I don't know it is good or not to open an idle connection and spamming request on 1 connection.

You're leveraging the database to generated the UUID for you, as long as you're letting the DB do it you'll need a connection to send the query.

You generally have two options here:

1. let the DB generate the UUID on the fly when inserting your data
2. generate the UUID in your own code before sending it

Generate ID on insert

This means you need to change the way you operate, your command for inserting data will need to look something like this:

INSERT INTO your_table(id, value)
VALUES (
  UUID_SHORT(), 
  -- other values
);

This will automatically generate the ID for you during the insert, without needing to generate it before.

If you need to know the ID after the insert was performed, you have a few options, like using LAST_INSERT_ID() or querying the data you just created.

See this other question for more info.

Generate ID in code

You can use a package like github.com/google/uuid.

import "github.com/google/uuid"

func GenerateUUID() (uint32, error) {
  id, err := uuid.NewRandom()
  if err != nil {
    return 0, err
  }
  return id.ID(), nil
}

Note that you can also get a string representation for the UUID, or you can cast it to uint64 easily from uint32.