Introduction

The sqlx library extends Go's database/sql with named parameter queries, allowing SQL like INSERT INTO users (name, email) VALUES (:name, :email) instead of positional $1, $2 placeholders. Parameter binding fails when struct field names do not match the named parameters, when nil values are not properly handled, or when the query syntax is incompatible with the target database. This error is particularly frustrating because the error message often does not clearly indicate which parameter failed to bind.

Symptoms

bash
missing destination name email in *User

Or:

bash
could not find name in User

Or during NamedExec:

bash
sql: converting argument $1 type: unsupported type main.User, a struct

Or with PostgreSQL:

bash
pq: bind message supplies 2 parameters, but prepared statement requires 3

Common Causes

  • Struct field not exported: Lowercase field names are invisible to reflection-based binding
  • Missing db tag: Field name does not match the named parameter in the query
  • Query uses $N syntax instead of :name: Mixing positional and named parameter styles
  • Nil pointer fields: A nil pointer field causes binding to fail without a clear error
  • Database driver incompatibility: The underlying driver does not support named parameters natively
  • Slice of structs with mismatched fields: NamedExec with a slice where struct fields differ from query parameters

Step-by-Step Fix

Step 1: Match struct tags to named parameters

go type User struct { ID int64 db:"id" Name string db:"name" // Must match :name in query Email string db:"email" // Must match :email in query CreatedAt time.Time db:"created_at"` // Must match :created_at in query }

func (r *UserRepository) Create(ctx context.Context, user *User) error { query := INSERT INTO users (name, email, created_at) VALUES (:name, :email, :created_at) RETURNING id

result, err := r.db.NamedQueryContext(ctx, query, user) if err != nil { return fmt.Errorf("named query failed: %w", err) } defer result.Close()

if result.Next() { if err := result.Scan(&user.ID); err != nil { return fmt.Errorf("scan id failed: %w", err) } } return nil } ```

Step 2: Handle nil pointer fields

go type User struct { ID int64 db:"id" Name string db:"name" Email string db:"email" Bio *string db:"bio" // Nullable string Age *int db:"age"` // Nullable int }

func (r *UserRepository) Update(ctx context.Context, user *User) error { query := UPDATE users SET name = :name, email = :email, bio = :bio, age = :age WHERE id = :id

_, err := r.db.NamedExecContext(ctx, query, user) return err }

// Usage with nil fields bio := "Hello world" user := &User{ ID: 1, Name: "John", Email: "john@example.com", Bio: &bio, // Will bind as the string value Age: nil, // Will bind as SQL NULL } ```

Step 3: Use NamedExec with a slice for batch inserts

go func (r *UserRepository) BulkCreate(ctx context.Context, users []User) error { query := INSERT INTO users (name, email, created_at) VALUES (:name, :email, :created_at) `

_, err := r.db.NamedExecContext(ctx, query, users) return err }

// For PostgreSQL, use a transaction for better performance func (r *UserRepository) BulkCreateTx(ctx context.Context, users []User) error { tx, err := r.db.BeginTxx(ctx, nil) if err != nil { return err } defer tx.Rollback()

query := INSERT INTO users (name, email, created_at) VALUES (:name, :email, :created_at)

_, err = tx.NamedExecContext(ctx, query, users) if err != nil { return err }

return tx.Commit() } ```

Prevention

  • Always use db struct tags that exactly match the named parameters in your SQL
  • Use pointer types (*string, *int) for nullable fields instead of zero values
  • Test named queries with NamedQuery and iterate results before using NamedExec
  • Use sqlx.In() for IN clause queries: sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
  • Add a linter rule that checks db tags against actual database column names
  • Use db.MapperFunc(strings.ToLower) if your Go struct fields use PascalCase but database columns use snake_case