Why GOLANG MySQL Driver Return "bad connection" in UPDATE & DELETE SQL?

i'm getting Stuck as TRY my first CRUD with MySQL (5.7) using GO lang (1.18). CREATE & READ SQL not return the error, but in UPDATE & DELETE return the error. I was searching and looks like about bad or wrong configuration for connection...but i dont know how to trace it.

Driver MySQL: github.com/go-sql-driver/mysql v1.6.0

My Repo: https://github.com/ramadoiranedar/GO_REST_API

The Error is:

[mysql] 2022/09/22 00:04:44 connection.go:299: invalid connection
driver: bad connection

And i have functions SQL look likes this:

CONNECTION

func NewDB() *sql.DB {
    db, err := sql.Open("mysql", "MY_USERNAME:MY_PASSWORD@tcp(localhost:3306)/go_restful_api")
    helper.PanicIfError(err)

    db.SetMaxIdleConns(5)
    db.SetMaxIdleConns(20)
    db.SetConnMaxLifetime(60 * time.Minute)
    db.SetConnMaxIdleTime(10 * time.Minute)

    return db
}

UPDATE

func (repository *CategoryRepositoryImpl) Update(ctx context.Context, tx *sql.Tx, category domain.Category) domain.Category {
   SQL := "update category set name = ? where id = ?"
   _, err := tx.ExecContext(ctx, SQL, category.Name, category.Id)
   helper.PanicIfError(err)
   return category
}

DELETE

func (repository *CategoryRepositoryImpl) Delete(ctx context.Context, tx *sql.Tx, category domain.Category) {
   SQL := "delete from category where id = ?"
   _, err := tx.ExecContext(ctx, SQL, category.Name, category.Id)
   helper.PanicIfError(err)
}

CREATE

func (repository *CategoryRepositoryImpl) Create(ctx context.Context, tx *sql.Tx, category domain.Category) domain.Category {
SQL := "insert into category (name) values (?)"
   result, err := tx.ExecContext(ctx, SQL, category.Name)
   helper.PanicIfError(err)

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

READ

func (repository *CategoryRepositoryImpl) FindAll(ctx context.Context, tx *sql.Tx) []domain.Category {
    SQL := "select id, name from category"
    rows, err := tx.QueryContext(ctx, SQL)
    helper.PanicIfError(err)

    var categories []domain.Category
    for rows.Next() {
        category := domain.Category{}
        err := rows.Scan(
            &category.Id,
            &category.Name,
        )
        helper.PanicIfError(err)
        categories = append(categories, category)
    }
    return categories
}

func (repository *CategoryRepositoryImpl) FindById(ctx context.Context, tx *sql.Tx, categoryId int) (domain.Category, error) {
    SQL := "select id, name from category where id = ?"
    rows, err := tx.QueryContext(ctx, SQL, categoryId)
    helper.PanicIfError(err)

    category := domain.Category{}
    if rows.Next() {
        err := rows.Scan(&category.Id, &category.Name)
        helper.PanicIfError(err)
        return category, nil
    } else {
        return category, errors.New("CATEGORY IS NOT FOUND")
    }
}

Solution

Long story short: you are using database transactions where you should not.

Keep them for group of writing operations that must succeed.

I would suggest you to redefine your repository and its implementation. The whole idea of splitting layers is to be independant of a repository implementation and to be able to switch from mysql to mongo to postgres, or w/e.

//category_controller.go
type CategoryRepository interface {
    Create(ctx context.Context, category domain.Category) domain.Category
    Update(ctx context.Context, category domain.Category) domain.Category
    Delete(ctx context.Context, category domain.Category)
    FindById(ctx context.Context, categoryId int) (domain.Category, error)
    FindAll(ctx context.Context) []domain.Category
}
//category_repository_impl.go
type CategoryRepositoryImpl struct {
    db *sql.DB 
}

func NewCategoryRepository(db *sql.DB) CategoryRepository {
    return &CategoryRepositoryImpl{db: db}
}

Please see following gists with modified version of your code files: https://gist.github.com/audrenbdb/94660f707a206d385c42f64ceb93a4aa