sqlrange

package module
v0.1.4 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Feb 2, 2024 License: BSD-3-Clause, MIT Imports: 7 Imported by: 0

README

sqlrange Go Reference

Library using the database/sql package and Go 1.22 range functions to execute queries against SQL databases.

Installation

This package is intended to be used as a library and installed with:

go get github.com/achille-roussel/sqlrange

⚠ The package depends on Go 1.22 (currently in rc1 release) and enabling the rangefunc experiment.

To download Go 1.22 rc1: https://pkg.golang.ir/golang.org/dl/go1.22rc1

go install golang.org/dl/go1.22rc1@latest
go1.22rc1 download

Then to enable the rangefunc experiment, set the GOEXPERIMENT environment variable in the shell that executes the go commands:

export GOEXPERIMENT=rangefunc

For a more detailed guide of how to configure Go 1.22 with range functions see Go 1.22 rc1 installation to enable the range functions experiment.

Usage

The sqlrange package contains two kinds of functions called Exec and Query which wrap the standard library's database/sql methods with the same names. The package adds stronger type safety and the ability to use range functions as iterators to pass values to the queries or retrieve results.

Note that sqlrange IS NOT AN ORM, it is a lightweight package which does not hide any of the details and simply provides library functions to structure applications that stream values in and out of databases.

Query

The Query functions are used to read streams of values from databases, in the same way that sql.(*DB).Query does, but using range functions to simplify the code constructs, and type parameters to automatically decode SQL results into Go struct values.

The type parameter must be a struct with fields containing "sql" struct tags to define the names of columns that the fields are mapped to:

type Point struct {
    X float64 `sql:"x"`
    Y float64 `sql:"y"`
}

for p, err := range sqlrange.Query[Point](db, `select x, y from points`) {
    if err != nil {
        ...
    }
    ...
}

Note that resource management here is automated by the range function returned by calling Query, the underlying *sql.Rows value is automatically closed when the program exits the body of the range loop consuming the rows.

Exec

The Exec functions are used to execute insert, update, or delete queries against databases, accepting a stream of parameters as arguments (in the form of a range function), and returning a stream of results.

Since the function will send multiple queries to the database, it is often preferable to apply it to a transaction (or a statement derived from a transaction via sql.(*Tx).Stmt) to ensure atomicity of the operation.

tx, err := db.Begin()
if err != nil {
    ...
}
defer tx.Rollback()

for r, err := range sqlrange.Exec(tx,
    `insert into table (col1, col2, col3) values (?, ?, ?)`,
    // This range function yields the values that will be inserted into
    // the database by executing the query above.
    func(yield func(RowType, error) bool) {
        ...
    },
    // Inject the arguments for the SQL query being executed.
    // The function is called for each value yielded by the range
    // function above.
    sqlrange.ExecArgs(func(args []any, row RowType) []any {
        return append(args, row.Col1, row.Col2, row.Col3)
    }),
) {
    // Each results of each execution are streamed and must be consumed
    // by the program to drive the operation.
    if err != nil {
        ...
    }
    ...
}

if err := tx.Commit(); err != nil {
    ...
}
Context

Mirroring methods of the sql.DB type, functions of the sqlrange package have variants that take a context.Context as first argument to support asynchronous cancellation or timing out the operations.

Reusing the example above, we could set a 10 secondstime limit for the query using QueryContext instead of Query:

ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
defer cancel()

for p, err := range sqlrange.QueryContext[Point](ctx, db, `select x, y from points`) {
    if err != nil {
        ...
    }
    ...
}

The context is propagated to the sql.(*DB).QueryContext method, which then passes it to the underlying SQL driver.

Performance

Functions in this package are optimized to have a minimal compute and memory footprint. Applications should not observe any performance degradation from using it, compared to using the database/sql package directly. This is an important property of the package since it means that the type safety, resource lifecycle management, and expressiveness do not have to be a trade off.

This is a use case where the use of range functions really shines: because all the code points where range functions are created get inlined, the compiler's escape analysis can place most of the values on the stack, keeping the memory and garbage collection overhead to a minimum.

Most of the escaping heap allocations in this package come from the use of reflection to convert SQL rows into Go values, which are optimized using two different approaches:

  • Caching: internally, the package caches the reflect.StructField values that it needs. This is necessary to remove some of the allocations caused by the reflect package allocating the Index on the heap. See https://github.com/golang/go/issues/2320 for more details.

  • Amortization: since the intended use case is to select ranges of rows, or execute batch queries, the functions can reuse the local state maintained to read values. The more rows are involved in the query, the great the cost of allocating those values gets amortized, to the point that it quickly becomes insignificant.

To illustrate, we can look at the memory profiles for the package benchmarks.

objects allocated on the heap

File: sqlrange.test
Type: alloc_objects
Time: Jan 15, 2024 at 8:32am (PST)
Showing nodes accounting for 23444929, 97.50% of 24046152 total
Dropped 43 nodes (cum <= 120230)
      flat  flat%   sum%        cum   cum%
  21408835 89.03% 89.03%   21408835 89.03%  github.com/achille-roussel/sqlrange_test.(*fakeStmt).QueryContext /go/src/github.com/achille-roussel/sqlrange/fakedb_test.go:1040
   1769499  7.36% 96.39%    1769499  7.36%  strconv.formatBits /sdk/go1.22rc1/src/strconv/itoa.go:199
    217443   0.9% 97.30%     217443   0.9%  github.com/achille-roussel/sqlrange_test.(*fakeStmt).QueryContext /go/src/github.com/achille-roussel/sqlrange/fakedb_test.go:1044
     32768  0.14% 97.43%   21926303 91.18%  database/sql.(*DB).query /sdk/go1.22rc1/src/database/sql/sql.go:1754
     16384 0.068% 97.50%   23925181 99.50%  github.com/achille-roussel/sqlrange_test.BenchmarkQuery100Rows /go/src/github.com/achille-roussel/sqlrange/sqlrange_test.go:145
         0     0% 97.50%   21926303 91.18%  database/sql.(*DB).QueryContext /sdk/go1.22rc1/src/database/sql/sql.go:1731
         0     0% 97.50%   21926303 91.18%  database/sql.(*DB).QueryContext.func1 /sdk/go1.22rc1/src/database/sql/sql.go:1732
         0     0% 97.50%   21746433 90.44%  database/sql.(*DB).queryDC /sdk/go1.22rc1/src/database/sql/sql.go:1806
         0     0% 97.50%   22039082 91.65%  database/sql.(*DB).retry /sdk/go1.22rc1/src/database/sql/sql.go:1566
         0     0% 97.50%    1769499  7.36%  database/sql.(*Rows).Scan /sdk/go1.22rc1/src/database/sql/sql.go:3354
         0     0% 97.50%    1769499  7.36%  database/sql.asString /sdk/go1.22rc1/src/database/sql/convert.go:499
         0     0% 97.50%    1769499  7.36%  database/sql.convertAssignRows /sdk/go1.22rc1/src/database/sql/convert.go:433
         0     0% 97.50%     169852  0.71%  database/sql.ctxDriverPrepare /sdk/go1.22rc1/src/database/sql/ctxutil.go:15
         0     0% 97.50%   21746433 90.44%  database/sql.ctxDriverStmtQuery /sdk/go1.22rc1/src/database/sql/ctxutil.go:82
         0     0% 97.50%   21746433 90.44%  database/sql.rowsiFromStatement /sdk/go1.22rc1/src/database/sql/sql.go:2836
         0     0% 97.50%     202620  0.84%  database/sql.withLock /sdk/go1.22rc1/src/database/sql/sql.go:3530
         0     0% 97.50%   21926303 91.18%  github.com/achille-roussel/sqlrange.QueryContext[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }] /go/src/github.com/achille-roussel/sqlrange/sqlrange.go:213
         0     0% 97.50%    1769499  7.36%  github.com/achille-roussel/sqlrange.QueryContext[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }].Scan[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }].func2 /go/src/github.com/achille-roussel/sqlrange/sqlrange.go:278
         0     0% 97.50%   21926303 91.18%  github.com/achille-roussel/sqlrange.Query[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }] /go/src/github.com/achille-roussel/sqlrange/sqlrange.go:189 (inline)
         0     0% 97.50%     120971   0.5%  github.com/achille-roussel/sqlrange_test.BenchmarkQuery100Rows /go/src/github.com/achille-roussel/sqlrange/sqlrange_test.go:129
         0     0% 97.50%     120971   0.5%  github.com/achille-roussel/sqlrange_test.BenchmarkQuery100Rows.Exec[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }].ExecContext[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }].func4 /go/src/github.com/achille-roussel/sqlrange/sqlrange.go:162
         0     0% 97.50%     120971   0.5%  github.com/achille-roussel/sqlrange_test.BenchmarkQuery100Rows.func1 /go/src/github.com/achille-roussel/sqlrange/sqlrange_test.go:131
         0     0% 97.50%    1769499  7.36%  strconv.FormatInt /sdk/go1.22rc1/src/strconv/itoa.go:29
         0     0% 97.50%   24033864 99.95%  testing.(*B).launch /sdk/go1.22rc1/src/testing/benchmark.go:316
         0     0% 97.50%   24046152   100%  testing.(*B).runN /sdk/go1.22rc1/src/testing/benchmark.go:193

memory allocated on the heap

File: sqlrange.test
Type: alloc_space
Time: Jan 15, 2024 at 8:32am (PST)
Showing nodes accounting for 626.05MB, 97.66% of 641.05MB total
Dropped 33 nodes (cum <= 3.21MB)
      flat  flat%   sum%        cum   cum%
  408.51MB 63.72% 63.72%   408.51MB 63.72%  github.com/achille-roussel/sqlrange_test.(*fakeStmt).QueryContext /go/src/github.com/achille-roussel/sqlrange/fakedb_test.go:1040
  174.04MB 27.15% 90.87%   174.04MB 27.15%  github.com/achille-roussel/sqlrange_test.(*fakeStmt).QueryContext /go/src/github.com/achille-roussel/sqlrange/fakedb_test.go:1044
      27MB  4.21% 95.09%       27MB  4.21%  strconv.formatBits /sdk/go1.22rc1/src/strconv/itoa.go:199
    5.50MB  0.86% 95.94%     5.50MB  0.86%  github.com/achille-roussel/sqlrange_test.(*fakeStmt).QueryContext /go/src/github.com/achille-roussel/sqlrange/fakedb_test.go:1064
    5.50MB  0.86% 96.80%     5.50MB  0.86%  database/sql.(*DB).queryDC /sdk/go1.22rc1/src/database/sql/sql.go:1815
    4.50MB   0.7% 97.50%     4.50MB   0.7%  strings.genSplit /sdk/go1.22rc1/src/strings/strings.go:249
    0.50MB 0.078% 97.58%   635.05MB 99.06%  github.com/achille-roussel/sqlrange_test.BenchmarkQuery100Rows /go/src/github.com/achille-roussel/sqlrange/sqlrange_test.go:145
    0.50MB 0.078% 97.66%   602.05MB 93.92%  database/sql.(*DB).query /sdk/go1.22rc1/src/database/sql/sql.go:1754
         0     0% 97.66%     5.50MB  0.86%  database/sql.(*DB).ExecContext /sdk/go1.22rc1/src/database/sql/sql.go:1661
         0     0% 97.66%     5.50MB  0.86%  database/sql.(*DB).ExecContext.func1 /sdk/go1.22rc1/src/database/sql/sql.go:1662
         0     0% 97.66%   602.05MB 93.92%  database/sql.(*DB).QueryContext /sdk/go1.22rc1/src/database/sql/sql.go:1731
         0     0% 97.66%   602.05MB 93.92%  database/sql.(*DB).QueryContext.func1 /sdk/go1.22rc1/src/database/sql/sql.go:1732
         0     0% 97.66%     5.50MB  0.86%  database/sql.(*DB).exec /sdk/go1.22rc1/src/database/sql/sql.go:1683
         0     0% 97.66%        5MB  0.78%  database/sql.(*DB).queryDC /sdk/go1.22rc1/src/database/sql/sql.go:1797
         0     0% 97.66%   589.55MB 91.97%  database/sql.(*DB).queryDC /sdk/go1.22rc1/src/database/sql/sql.go:1806
         0     0% 97.66%        5MB  0.78%  database/sql.(*DB).queryDC.func2 /sdk/go1.22rc1/src/database/sql/sql.go:1798
         0     0% 97.66%   607.55MB 94.77%  database/sql.(*DB).retry /sdk/go1.22rc1/src/database/sql/sql.go:1566
         0     0% 97.66%       27MB  4.21%  database/sql.(*Rows).Scan /sdk/go1.22rc1/src/database/sql/sql.go:3354
         0     0% 97.66%       27MB  4.21%  database/sql.asString /sdk/go1.22rc1/src/database/sql/convert.go:499
         0     0% 97.66%       27MB  4.21%  database/sql.convertAssignRows /sdk/go1.22rc1/src/database/sql/convert.go:433
         0     0% 97.66%        8MB  1.25%  database/sql.ctxDriverPrepare /sdk/go1.22rc1/src/database/sql/ctxutil.go:15
         0     0% 97.66%   589.55MB 91.97%  database/sql.ctxDriverStmtQuery /sdk/go1.22rc1/src/database/sql/ctxutil.go:82
         0     0% 97.66%   589.55MB 91.97%  database/sql.rowsiFromStatement /sdk/go1.22rc1/src/database/sql/sql.go:2836
         0     0% 97.66%     8.50MB  1.33%  database/sql.withLock /sdk/go1.22rc1/src/database/sql/sql.go:3530
         0     0% 97.66%   602.05MB 93.92%  github.com/achille-roussel/sqlrange.QueryContext[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }] /go/src/github.com/achille-roussel/sqlrange/sqlrange.go:213
         0     0% 97.66%       27MB  4.21%  github.com/achille-roussel/sqlrange.QueryContext[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }].Scan[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }].func2 /go/src/github.com/achille-roussel/sqlrange/sqlrange.go:278
         0     0% 97.66%   602.05MB 93.92%  github.com/achille-roussel/sqlrange.Query[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }] /go/src/github.com/achille-roussel/sqlrange/sqlrange.go:189 (inline)
         0     0% 97.66%        6MB  0.94%  github.com/achille-roussel/sqlrange_test.BenchmarkQuery100Rows /go/src/github.com/achille-roussel/sqlrange/sqlrange_test.go:129
         0     0% 97.66%        6MB  0.94%  github.com/achille-roussel/sqlrange_test.BenchmarkQuery100Rows.Exec[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }].ExecContext[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }].func4 /go/src/github.com/achille-roussel/sqlrange/sqlrange.go:162
         0     0% 97.66%     5.50MB  0.86%  github.com/achille-roussel/sqlrange_test.BenchmarkQuery100Rows.Exec[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }].ExecContext[go.shape.struct { Age int "sql:\"age\""; Name string "sql:\"name\""; BirthDate time.Time "sql:\"bdate\"" }].func4.3 /go/src/github.com/achille-roussel/sqlrange/sqlrange.go:170
         0     0% 97.66%        6MB  0.94%  github.com/achille-roussel/sqlrange_test.BenchmarkQuery100Rows.func1 /go/src/github.com/achille-roussel/sqlrange/sqlrange_test.go:131
         0     0% 97.66%       27MB  4.21%  strconv.FormatInt /sdk/go1.22rc1/src/strconv/itoa.go:29
         0     0% 97.66%     4.50MB   0.7%  strings.Split /sdk/go1.22rc1/src/strings/strings.go:307 (inline)
         0     0% 97.66%   640.05MB 99.84%  testing.(*B).launch /sdk/go1.22rc1/src/testing/benchmark.go:316
         0     0% 97.66%   641.05MB   100%  testing.(*B).runN /sdk/go1.22rc1/src/testing/benchmark.go:193

Almost all the memory allocated on the heap is done in the SQL driver. The fake driver employed for tests isn't very efficient, but it still shows that the package does not contribute to the majority of memory usage. Programs that use SQL drivers for production databases like MySQL or Postgres will have performance characteristics dictated by the driver and won't suffer from utilizing the sqlrange package abstractions.

Documentation

Overview

Package sqlrange integrates database/sql with Go 1.22 range functions.

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

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.

Example 
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) {
		_ = yield(Row{Age: 19, Name: "Luke"}, nil) &&
			yield(Row{Age: 42, Name: "Hitchhiker"}, nil)
	},
	sqlrange.ExecArgsFields[Row]("name", "age"),
) {
	if err != nil {
		log.Fatal(err)
	}
	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.

Example 
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.

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.

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.

Types

type ExecOption 

type ExecOption[Row any] func(*execOptions[Row])

ExecOption is a functional option type to configure the Exec and ExecContext functions.

func ExecArgs 

func ExecArgs[Row any](fn func([]any, Row) []any) ExecOption[Row]

ExecArgs is an option that specifies the function being called to generate the list of arguments passed when executing a query.

By default, the Row value is converted to a list of arguments by taking the fields with a "sql" struct tag in the order they appear in the struct, as defined by the reflect.VisibleFields function.

The function must append the arguments to the slice passed as argument and return the resulting slice.

func ExecArgsFields 

func ExecArgsFields[Row any](columnNames ...string) ExecOption[Row]

ExecArgsFields constructs an option that specifies the fields to include in the query arguments from a list of column names.

This option is useful when the query only needs a subset of the fields from the row type, or when the query arguments are in a different order than the struct fields.

func ExecQuery 

func ExecQuery[Row any](fn func(string, Row) string) ExecOption[Row]

ExecQuery is an option that specifies the function being called to generate the query to execute for a given Row value.

The function receives the original query value passed to Exec or ExecContext, and returns the query to execute.

This is useful when parts of the query depend on the Row value that the query is being executed on, for example when the query is an insert.

type Executable 

type Executable interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
}

Executable is the interface implemented by sql.DB, sql.Stmt, or sql.Tx.

type Queryable 

type Queryable interface {
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
}

Queryable is an interface implemented by types that can send SQL queries, such as *sql.DB, *sql.Stmt, or *sql.Tx.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.