sql: expected 8 arguments, got 0

Issue

CODE SNIPPET:

model.go

type User struct {
    ID        int       `db:"id" json:"id"`
    CreatedAt time.Time `db:"created_at" json:"created_at"`
    UpdatedAt time.Time `db:"updated_at" json:"updated_at"`
    DeletedAt time.Time `db:"deleted_at" json:"deleted_at"`
    Username  string    `db:"username" json:"username"`
    Password  string    `db:"password" json:"password"`
    FirstName string    `db:"first_name" json:"first_name"`
    LastName  string    `db:"last_name" json:"last_name"`
    Phone     string    `db:"phone" json:"phone"`
    Status bool         `db:"status" json:"status"`
    Addrs  []UserAddress
}

---

Query:

queryInsertUserData = `
    INSERT INTO users (id, created_at, updated_at, deleted_at, username, password, first_name, last_name, phone, status) VALUES($1, now(), now(), $2, $3, $4, $5, $6, $7, $8)
`

---

type queries struct {
    insertUserData, 
    insertAddrData *sqlx.Stmt
}

//prepareStatement is a method for preparing sqlx statement
func (queries *queries) prepareStatement(db *sqlx.DB, query string) (*sqlx.Stmt, error) {
    stmt, err := db.Preparex(query) //https://go.dev/doc/database/prepared-statements
    if err != nil {
        return nil, err
    }

    return stmt, err
}

constructUserData, err := queries.prepareStatement(db, queryInsertUserData)
queries.insertUserData = constructUserData

---

Implementation:

// Insert User data
func (postgres *Postgres) InsertUserData(ctx context.Context) (*entity.User, error) {
    c := entity.User{}

    err := postgres.queries.insertUserData.SelectContext(ctx, &c) //<---here
    if err != nil {
        return nil, err
    }
    return &c, nil
}

---

my ctx is :

ctx = context.WithValue(ctx, "ID", 1)
ctx = context.WithValue(ctx, "Username", "John")
ctx = context.WithValue(ctx, "Password", "pass")

---

when I am passing to postgres.queries.insertUserData.SelectContext(ctx, &c)

I am getting: sql: expected 8 arguments, got 0

why it is saying got 0? Can anyone help me with this? How to pass ctx and provide the insert query values?

Solution

I didn’t get the structure of your code, but from the main context (inserting query) you should do something like this:

package main

import (
    "context"
    "database/sql"
    "log"
)

var db *sql.DB
type User struct {
    ID        int       `db:"id" json:"id"`
    CreatedAt time.Time `db:"created_at" json:"created_at"`
    UpdatedAt time.Time `db:"updated_at" json:"updated_at"`
    DeletedAt time.Time `db:"deleted_at" json:"deleted_at"`
    Username  string    `db:"username" json:"username"`
    Password  string    `db:"password" json:"password"`
    FirstName string    `db:"first_name" json:"first_name"`
    LastName  string    `db:"last_name" json:"last_name"`
    Phone     string    `db:"phone" json:"phone"`
    Status bool         `db:"status" json:"status"`
    Addrs  []UserAddress
}
func main() {
    users := []User {
        {...User 1 data...},
        {...User 2 data...},
    }

    stmt, err := db.Prepare("INSERT INTO users (id, created_at, updated_at, deleted_at, username, password, first_name, last_name, phone, status) VALUES($1, now(), now(), $2, $3, $4, $5, $6, $7, $8)")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

    for id, user := range users {
        if _, err := stmt.Exec(id+1, user.Username, user.Password,... other data); err != nil {
            log.Fatal(err)
        }
    }
}

also, you can use the Grom powerful package for using all relational databases.

Answered By – ttrasn

Answer Checked By – Timothy Miller (GoLangFix Admin)