sqlrange

func Exec ¶

func Exec[Row any](e Executable, query string, seq iter.Seq2[Row, error], opts ...ExecOption[Row]) iter.Seq2[sql.Result, error]

Exec is like ExecContext but it uses the background context.

type Row struct {
	Age  int    `sql:"age"`
	Name string `sql:"name"`
}

db := newTestDB(new(testing.T), "people")
defer db.Close()

for res, err := range sqlrange.Exec(db, `INSERT|people|name=?,age=?`,
	func(yield func(Row, error) bool) {
		if !yield(Row{Age: 19, Name: "Luke"}, nil) {
			return
		}
		if !yield(Row{Age: 42, Name: "Hitchhiker"}, nil) {
			return
		}
	},
	sqlrange.ExecArgsFields[Row]("name", "age"),
) {
	if err != nil {
		log.Fatal()
	}
	rowsAffected, err := res.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(rowsAffected)
}

Output:

1
1

func ExecContext ¶

func ExecContext[Row any](ctx context.Context, e Executable, query string, seq iter.Seq2[Row, error], opts ...ExecOption[Row]) iter.Seq2[sql.Result, error]

ExecContext executes a query for each row in the sequence.

To ensure that the query is executed atomicatlly, it is usually useful to call ExecContext on a transaction (sql.Tx), for example:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
  ...
}
defer tx.Rollback()
for r, err := range sqlrange.ExecContext[RowType](ctx, tx, query, rows) {
  if err != nil {
    ...
  }
  ...
}
if err := tx.Commit(); err != nil {
  ...
}

Since the function makes one query execution for each row read from the sequence, latency of the query execution can quickly increase. In some cases, such as inserting values in a database, the program can amortize the cost of query latency by batching the rows being inserted, for example:

for r, err := range sqlrange.ExecContext(ctx, tx,
	`insert into table (col1, col2, col3) values `,
	// yield groups of rows to be inserted in bulk
	func(yield func([]RowType, error) bool) {
	  ...
	},
	// append values for the insert query
	sqlrange.ExecArgs(func(args []any, rows []RowType) []any {
	  for _, row := range rows {
	    args = append(args, row.Col1, row.Col2, row.Col3)
	  }
	  return args
	}),
	// generate placeholders for the insert query
	sqlrange.ExecQuery(func(query string, rows []RowType) string {
	  return query + strings.Repeat(`(?, ?, ?)`, len(rows))
	}),
) {
	...
}

Batching operations this way is necessary to achieve high throughput when inserting values into a database.

func Fields ¶

func Fields(t reflect.Type) iter.Seq2[string, reflect.StructField]

Fields returns a sequence of the fields of a struct type that have a "sql" tag.

func Query ¶

func Query[Row any](q Queryable, query string, args ...any) iter.Seq2[Row, error]

Query is like QueryContext but it uses the background context.

type Row struct {
	Age  int    `sql:"age"`
	Name string `sql:"name"`
}

db := newTestDB(new(testing.T), "people")
defer db.Close()

for row, err := range sqlrange.Query[Row](db, `SELECT|people|age,name|`) {
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(row)
}

Output:

{1 Alice}
{2 Bob}
{3 Chris}

func QueryContext ¶

func QueryContext[Row any](ctx context.Context, q Queryable, query string, args ...any) iter.Seq2[Row, error]

QueryContext returns the results of the query as a sequence of rows.

The returned function automatically closes the unerlying sql.Rows value when it completes its iteration. The function can only be iterated once, it will not retain the values that it has seen.

A typical use of QueryContext is:

for row, err := range sqlrange.QueryContext[RowType](ctx, db, query, args...) {
  if err != nil {
    ...
  }
  ...
}

The q parameter represents a queryable type, such as *sql.DB, *sql.Stmt, or *sql.Tx.

See Scan for more information about how the rows are mapped to the row type parameter Row.

func Scan ¶

func Scan[Row any](rows *sql.Rows) iter.Seq2[Row, error]

Scan returns a sequence of rows from a sql.Rows value.

The returned function automatically closes the rows passed as argument when it completes its iteration. The function can only be iterated once, it will not retain the values that it has seen.

A typical use of Scan is:

rows, err := db.QueryContext(ctx, query, args...)
if err != nil {
  ...
}
for row, err := range sqlrange.Scan[RowType](rows) {
  if err != nil {
    ...
  }
  ...
}

Scan uses reflection to map the columns of the rows to the fields of the struct passed as argument. The mapping is done by matching the name of the columns with the name of the fields. The name of the columns is taken from the "sql" tag of the fields. For example:

type Row struct {
  ID   int64  `sql:"id"`
  Name string `sql:"name"`
}

The fields of the struct that do not have a "sql" tag are ignored.

Ranging over the returned function will panic if the type parameter is not a struct.