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

Question

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
}

score 0 · Answer 1 · answered Jan 27 '22 at 09:07

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.

Let me test whether it's good or not – Elbo Shindi Pangestu Jan 27 '22 at 09:10 — Elbo Shindi Pangestu, Jan 27 '22 at 09:10

score 0 · Accepted Answer · answered Jan 27 '22 at 09:13

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:

let the DB generate the UUID on the fly when inserting your data
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.

I have some constraint that I forget to mention. My company has standard to use UUID_SHORT(). Then, If it generate on the fly, It will be hard to get return ID because database will always be return null. I need to get the ID as foreign key of other table that will be insert on the same endpoint. — Elbo Shindi Pangestu, Jan 27 '22 at 09:19
DB always return null on ID, see my question here: https://stackoverflow.com/questions/69430905/sql-last-insert-id-always-return-0. — Elbo Shindi Pangestu, Jan 27 '22 at 09:20
Given the constraints then your best bet is likely making a query after generating the first ID. If you need to use the database for generating you'll always need a connection to access it. — Vale, Jan 27 '22 at 09:24

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

2 Answers2

Generate ID on insert

Generate ID in code