The Complete Production SOP: How to Deploy and Run sqlx Ergonomic SQL Extensions for Go database/sql in Autonomous AI Agents

---
name: "sqlx-general-purpose-sql-extensions"
description: "Query databases and map rows directly to Go structs without heavy ORM overhead using sqlx."
metadata:
  author: "Jason Moiron"
  version: "1.0.0"
  runtime_compatibility:
    - "codex"
    - "claude-code"
    - "cursor"
    - "windsurf"
triggers:
  - "query into struct sqlx"
  - "sqlx namedexec"
  - "sqlx select slice"
permissions:
  fs_read: true
  fs_write: "workspace"
  shell_exec: false
  net_access: false
---

package main

import (
	"log"
	"time"

	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
)

func InitDB(dataSourceName string) *sqlx.DB {
	db, err := sqlx.Connect("postgres", dataSourceName)
	if err != nil {
		log.Fatalf("Failed to connect to database: %v", err)
	}

	db.SetMaxOpenConns(50)
	db.SetMaxIdleConns(25)
	db.SetConnMaxLifetime(5 * time.Minute)

	return db
}

package repository

import (
	"context"
	"time"

	"github.com/jmoiron/sqlx"
)

type User struct {
	ID        int64     `db:"id"`
	Email     string    `db:"email"`
	Active    bool      `db:"active"`
	CreatedAt time.Time `db:"created_at"`
}

type UserRepository struct {
	db *sqlx.DB
}

func NewUserRepository(db *sqlx.DB) *UserRepository {
	return &UserRepository{db: db}
}

// GetByID queries a single row directly into a User struct
func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) {
	var user User
	query := `SELECT id, email, active, created_at FROM users WHERE id = $1`
	if err := r.db.GetContext(ctx, &user, query, id); err != nil {
		return nil, err
	}
	return &user, nil
}

// ListActive users queries multiple rows directly into a slice of structs
func (r *UserRepository) ListActive(ctx context.Context) ([]User, error) {
	var users []User
	query := `SELECT id, email, active, created_at FROM users WHERE active = $1 ORDER BY id DESC`
	if err := r.db.SelectContext(ctx, &users, query, true); err != nil {
		return nil, err
	}
	return users, nil
}

// CreateUser uses NamedExec to bind struct fields directly to named query parameters
func (r *UserRepository) CreateUser(ctx context.Context, user *User) error {
	query := `INSERT INTO users (email, active, created_at) VALUES (:email, :active, :created_at)`
	_, err := r.db.NamedExecContext(ctx, query, user)
	return err
}

package repository_test

import (
	"testing"
	"your-module/repository"
)

func TestUserStructDBTags(t *testing.T) {
	u := repository.User{
		ID:     1,
		Email:  "[email protected]",
		Active: true,
	}
	if u.Email == "" {
		t.Fatal("expected non-empty user email")
	}
}