Question about sql error "unsupported type []string, a slice of string"

[bzon]

Help needed

Please see the example code and the error message at the bottom. I'm not sure what is causing the error. My insert function works fine. I'm using this code to insert to a clickhouse database table.

Insert function:

import (
    "context"
    "database/sql"
)

type Item struct {
    Price    float64
    Name     string
    Category []string
}

func AddItems(ctx context.Context, db *sql.DB, items []Item) error {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{})
    if err != nil {
        return err
    }

    stmt, err := tx.PrepareContext(ctx, "INSERT INTO items (price, name) VALUES (?)")
    if err != nil {
        return err
    }
    defer stmt.Close()

    for _, item := range items {
        _, err := stmt.ExecContext(ctx, item.Price, item.Name, item.Category)
        if err != nil {
            return err
        }
    }

    if err := tx.Commit(); err != nil {
        tx.Rollback()
        return err
    }

    return nil
}

Test function:

import (
    "context"
    "testing"

    "github.com/DATA-DOG/go-sqlmock"
)

func TestAddItems(t *testing.T) {
    // testdata
    items := []Item{
        Item{
            Price:    1.00,
            Name:     "apple",
            Category: []string{"fruit", "discounted"},
        },
    }

    db, mock, err := sqlmock.New()
    if err != nil {
        t.Fatal(err)
    }
    defer db.Close()

    mock.ExpectBegin()
    expectedPrepare := mock.ExpectPrepare("INSERT INTO items")
    for _, item := range items {
        expectedExec := expectedPrepare.ExpectExec().WithArgs(item.Price, item.Name, item.Category)
        expectedExec.WillReturnResult(sqlmock.NewResult(1, 1))
    }
    mock.ExpectCommit()

    err = AddItems(context.Background(), db, items)

    if err != nil {
        t.Error(err)
    }

    if err := mock.ExpectationsWereMet(); err != nil {
        t.Errorf("there were unfulfilled expectations: %s", err)
    }

}

Error:

=== RUN   TestAddItems
--- FAIL: TestAddItems (0.00s)
    main_test.go:37: sql: converting argument $3 type: unsupported type []string, a slice of string
    main_test.go:41: there were unfulfilled expectations: there is a remaining expectation which was not matched: ExpectedExec => expecting Exec or ExecContext which:
          - matches sql: 'INSERT INTO items'
          - is with arguments:
            0 - 1
            1 - apple
            2 - [fruit discounted]
          - should return Result having:
              LastInsertId: 1
              RowsAffected: 1

[l3pp4rd]

Hi, the reason is that golang standard sql does not support slices in arguments. It does not know how to transform it into sql.Driver.Value. So, what you need, is to customize the ValueConverter in the Sqlmock, it can be provided as an option and customized using this

though, if clickhouse exposes that slice value converter as public, you could just use it, otherwise you may need to copy it. Every value in sql is either converted with ValueConverter or read from Rows with Scanner interface. So probably you need to read about these to understand how to manage it.

[bzon]

@l3pp4rd I see. That is good to know. 🤔I will check it out. This is the package I'm using https://github.com/kshvakov/clickhouse.

[bzon]

I'm closing this issue now. I've decided to write an integration test instead of a unit test for my SQL code. Hopefully, Go's stdlib can be better.