sqlutil

sqlutil

Package sqliteutil provides utility functions to work with the standard database/sql package in Go. Its main purpose is to format SQL queries and map columns to struct fields.

Getting started

Installation

go get codeberg.org/phipu/sqlutil

Define a struct with db struct tags.

type Car struct {
	ID    int64  `db:"id"`
	Make  string `db:"make"`
	Model string `db:"model"`
}

Use reflection to extract the column names from the struct tags. This can be done globally.

var cols = sqlutil.NewCols[Car](nil)

Select

With this setup, we can start to query the DB. In the following examples we use SQLite. To build a query, we use the Builder. It provides a fmt.Printf style interface. We can use the normal database/sql interface to execute the query.

func selectCarByID1(id int64, tx *sql.Tx) (Car, error) {
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	b.Printf("SELECT %s FROM car WHERE id = %v", cols.Names(), id)

	row := tx.QueryRow(b.String(), b.Args()...)

	var res Car
	err := row.Scan(cols.BindPtr(&res).Vals(0)...)
	return res, err
}

This works well, but we have to repeat the columns we want to select. First we have to specify the column names in the query, and then we have to specify the column values in the call to scan. This works for one argument, but can be tedious, especially in a join. To avoid this, we can use bound calls in the Printf call and the builder will remember the Vals(0) in its internal ScanArgs. ScanBoundRow scans into ScanArgs.

func selectCarByID2(id int64, tx *sql.Tx) (Car, error) {
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	var c Car
	b.Printf("SELECT %s FROM car WHERE id = %v", cols.BindPtr(&c), id)
	err := b.ScanBoundRow(tx)
	return c, err
}

Insert

Inserting can be done the same way as selecting. But assume we want the DB to decide the ID. If we use cols.Names() this includes the id. The solution is to use the argument to NewCols to filter out the ID.

var insertCols = sqlutil.NewCols[Car](func(name string) bool { return name != "id" })

We can then use this to insert a new car without specifying the ID. On the Builder there is a helper that will call Exec followed by GetLastInsertId called ExecLastInsertID.

func insertCar(c Car, tx *sql.Tx) (int64, error) {
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	b.Printf("INSERT INTO car (%s) VALUES (%s)", insertCols.Names(), insertCols.Bind(c).Vals(0))
	id, err := b.ExecLastInsertID(tx)
	return id, err
}

Update

To update a row, there is the Assign wrapper. It formats bound columns to "col = ?". Note the use of b.Exec() which is just a shortcut for tx.Exec(b.String(), b.Args()...).

var updateCols = sqlutil.NewCols[Car](func(name string) bool { return name != "id" })

func updateCar(c Car, tx *sql.Tx) error {
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	b.Printf("UPDATE car SET %v WHERE id = %v", updateCols.Bind(c).Assign(), c.ID)
	_, err := b.Exec(tx)
	return err
}

Join

The builder can be used to generate a SQL query joining multiple tables. For this example, we have 3 Tables, make, model and car. The example function joins make and model and maps everything to Car.

type dbCar struct {
	ID      int64 `db:"id"`
	MakeID  int64 `db:"make_id"`
	ModelID int64 `db:"model_id"`
}

type dbMake struct {
	ID   int64  `db:"id"`
	Name string `db:"name"`
}

type dbModel struct {
	ID   int64  `db:"id"`
	Name string `db:"name"`
}

var (
	dbCarCols   = sqlutil.NewCols[dbCar](nil)
	dbMakeCols  = sqlutil.NewCols[dbMake](nil)
	dbModelCols = sqlutil.NewCols[dbModel](nil)
)

type Car struct {
	ID      int64
	MakeID  int64
	Make    string
	ModelID int64
	Model   string
}

func getCar(id int64, tx *sql.Tx) (Car, error) {
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}

	var (
		c  dbCar
		ma dbMake
		mo dbModel
	)

	b.Printf("SELECT %s, %s, %s FROM car AS c ",
		dbCarCols.Map(sqlutil.MapPrefix("c.")).BindPtr(&c),
		dbMakeCols.Map(sqlutil.MapPrefix("ma.")).BindPtr(&ma),
		dbModelCols.Map(sqlutil.MapPrefix("mo.")).BindPtr(&mo),
	)
	b.Printf("JOIN make AS ma ON c.make_id = ma.id ")
	b.Printf("JOIN model AS mo ON c.model_id = mo.id ")
	b.Printf("WHERE c.id = %v", id)

	err := b.ScanBoundRow(tx)
	if err != nil {
		return Car{}, err
	}

	return Car{c.ID, ma.ID, ma.Name, mo.ID, mo.Name}, nil
}

Licensing

sqlutil is released under the MPL v2.0 license