sqinn-go

A single sqinn instance is inherently single-threaded; requests are served one after another.

To achieve true database-level concurrency, you should:

1. Spin up multiple sqinn instances.
2. Implement your own connection pooling.

Warning: When accessing a SQLite database concurrently via multiple instances, you may encounter SQLITE_BUSY errors. Using PRAGMA busy_timeout in your SQL commands can help mitigate this.

While sqinn-go provides prebuilt binaries for common platforms, you should build sqinn yourself in the following scenarios:

1. Security/Trust: If you do not trust the binaries provided by GitHub runners.
2. Unsupported Platforms: If you require sqinn for a platform not officially supported by the embedded set (e.g., arm32).
3. Special SQLite Features: If you require specific SQLite configurations that are not present in the default builds, such as an mmap size greater than 2G.

To execute the automated unit tests on linux/amd64 or windows/amd64, follow these steps:

1. Initialize a test module and install the library:

go mod init test
go get -v -u github.com/cvilsmeier/sqinn-go/v2

2. Run the tests:

go test github.com/cvilsmeier/sqinn-go/v2

3. Check test coverage:

go test github.com/cvilsmeier/sqinn-go/v2 -coverprofile=cover.out
go tool cover -html=cover.out

To use Sqinn-Go in your project, install the v2 package using go get:

go get -u github.com/cvilsmeier/sqinn-go/v2

By default, the library uses embedded prebuilt binaries for linux_amd64 and windows_amd64. If you have compiled your own sqinn binary, you can specify its location in the sqinn.Options struct using the Sqinn field.

sq := sqinn.MustLaunch(sqinn.Options{
	Sqinn: "/path/to/sqinn",
})

The sqinn.Options struct defines how the sqinn subprocess is initialized:

Note: The default Sqinn is :prebuilt: and the default Db is :memory:.

The Query method executes a SQL statement and fetches result rows using a callback.

Parameters:

• sql: The SQL query string.
• params: A slice of sqinn.Value for parameter binding.
• coltypes: A byte slice defining the expected types for each column (e.g., using ValInt32, ValString, etc.).
• consume: A callback func(row int, values []Value) called for every row returned.

Alternatively, use QueryRows(sql string, params []Value, coltypes []byte) to fetch all rows into a [][]Value slice at once.

// Example: Querying rows and consuming them via callback
coltypes := []byte{sqinn.ValInt32, sqinn.ValString}
err := s.Query("SELECT id, name FROM users", nil, coltypes, func(row int, values []sqinn.Value) {
    id, _ := sqinn.Scan(values).NextInt32()
    name, _ := sqinn.Scan(values).NextString()
    fmt.Printf("Row %d: %d - %s\n", row, id, name)
})

The Exec method runs a SQL statement. It supports multiple iterations and parameter binding via a ProduceFunc callback.

• niterations: How many times to execute the SQL. If 0, it is a no-op. If 1, it runs once.
• nparams: How many parameters to bind per iteration.
• produce: A callback func(iteration int, params []Value) used to populate the params slice for each iteration.

For simple single-statement execution without parameters, use ExecSql(sql string).

// Example: Executing a statement with parameters across 3 iterations
err := s.Exec("INSERT INTO users (name) VALUES (?)", 3, 1, func(iteration int, params []sqinn.Value) {
    params[0] = sqinn.StringValue(fmt.Sprintf("user_%d", iteration))
})

Use sqinn.Launch(opt Options) to start a new sqinn subprocess. This provides an interface to SQLite databases without using cgo. You can specify the path to the sqinn executable, the database file (or :memory:), and logging preferences.

If you use the special name :prebuilt:, the library will automatically extract an embedded sqinn binary for linux/amd64 or windows/amd64 into a temporary directory and execute it.

import "github.com/cvilsmeier/sqinn-go/v2"

opt := sqinn.Options{
    Sqinn: sqinn.Prebuilt,
    Db:    "/path/to/database.db",
}

s, err := sqinn.Launch(opt)
if err != nil {
    panic(err)
}
defer s.Close()

The sqinn.Value type is a container for SQLite data types. Supported types are:

• ValNull (0)
• ValInt32 (1)
• ValInt64 (2)
• ValDouble (3)
• ValString (4)
• ValBlob (5)

Use the helper functions to create values:

• NullValue()
• Int32Value(int)
• Int64Value(int64)
• DoubleValue(float64)
• StringValue(string)
• BlobValue([]byte)

To convert a slice of standard Go types (nil, int, int64, float64, string, []byte) into []sqinn.Value, use sqinn.Bind(params []any).

// Using Bind to prepare parameters
params := sqinn.Bind([]any{123, "hello", nil})
// params is now []sqinn.Value{Int32Value(123), StringValue("hello"), NullValue()}