sqlutil

sqlutil

sqliteutil provides utility functions to work with the standard database/sql package in Go. Its main components are:

1. Meta[T] that contains information about the column names and struct fields obtained by reflection.
2. Builder a builder to generate parameterized SQL with printf syntax.

Struct Tags

The Meta[T] object works using struct tags. Each field that should be retrievable must contain a struct tag named db.

The tag value consists of a field name, and optional "tags". The empty tag is implicitly given to all struct fields.

Example

type Car struct {
	ID    int64  `db:"id,select"`
	Make  string `db:"make,select,insert,update"`
	Model string `db:"model,select,insert,update"`
	Year  int64  `db:"year,select,insert,update"`
	Color string `db:"color,select,insert,update"`
}

// extract all columns with the "insert" tag
var insertCarMeta = sqlutil.NewMeta[Car]("insert")

func InsertCarSimple(tx *sql.Tx, c Car) (int64, error) {
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	insertCarMeta.InsertValues(&b, "car", c)

	id, err := b.ExecLastInsertID(tx)
	return id, err
}

func InsertCar(tx *sql.Tx, c Car) (int64, error) {
	// create a Builder for sqlite (using the ? placeholder)
	// and write the query. It will end up looking like
	// INSERT INTO car (make, model, year, color) VALUES (?, ?, ?, ?)
	// and the arguments will be pointers to the correspoinging struct fields
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	b.Printf("INSERT INTO car (%s) VALUES (%s)", insertCarMeta.Cols(), insertCarMeta.Vals(c))

	// execute the query using the (matching) number of arguments
	// to simplify this, there is a helper b.Exec(tx)
	res, err := tx.Exec(b.String(), b.Args()...)
	if err != nil {
		return 0, fmt.Errorf("exec: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("get last insert id: %w", err)
	}
	return id, nil
}

// extract all columns with the "select" tag
var selectCarMeta = sqlutil.NewMeta[Car]("select")

func SelectCarsSimple(tx *sql.Tx) ([]Car, error) {
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	b.Printf("SELECT %v FROM car", selectCarMeta.Cols())
	return selectCarMeta.ScanSlice(&b, tx)
}

func SelectCars(tx *sql.Tx) ([]Car, error) {
	var c Car
	// build the query only using the "select" cols
	// the Fields method can be used in combination with
	// b.ScanFields and b.ScanRowFields.
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	b.Printf("SELECT %v FROM car", selectCarMeta.Fields(&c))

	// execute the query
	// See sqlutil.Slice and sqlutil.MappedSlice for other scan targets
	// that support straight forward scanning into slices.
	var cars []Car
	rows := b.ScanFields(tx)
	defer rows.Close()
	for rows.Next() {
		cars = append(cars, c)
	}
	return cars, rows.Err()
}

// extract all columns with the "update" tag
var updateCarMeta = sqlutil.NewMeta[Car]("update")

func UpdateCarSimple(tx *sql.Tx, c Car) error {
	// this generates the same SQL as UpdateCar
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	updateCarMeta.UpdateWhere(&b, "car", c, "id = %v", c.ID)
	_, err := b.Exec(tx)
	return err
}

func UpdateCar(tx *sql.Tx, c Car) error {
	// create a Builder for sqlite (using the ? placeholder)
	// and use it to format the update statement.
	// JoinBy is used to join multiple calls to Printf
	// with ", "
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	b.Printf("UPDATE car SET ")
	b.JoinBy(", ")
	for col, val := range updateCarMeta.IterVals(c) {
		b.Printf("%s = %v", col, val)
	}
	b.JoinBy("")
	b.Printf(" WHERE id = %v", c.ID)
	_, err := b.Exec(tx)
	return err
}

Licensing

sqlutil is released under the MPL v2.0 license