Hello,

I was searching for alternatives to mattn/go-sqlite3 and came across this library. I really like it so far!

But because I don't just want to make my Go programs a lot slower, I thought about doing some benchmarks first. (I'm new to benchmarking in Go!) You can see them here: https://git.jlel.se/jlelse/GoSqliteBench/src/commit/12db3eac1fd3c0d32e078e9a2d8feb917e83df84/SqliteBench_test.go

Unfortunately the results look like this:

$ go test -bench=.
goos: linux
goarch: amd64
pkg: git.jlel.se/jlelse/GoSqliteBench
cpu: Intel(R) Core(TM) i5-4590S CPU @ 3.00GHz
Benchmark_Zombiezen/Queries-4             149104              7141 ns/op
Benchmark_Mattn/Queries-4                 269184              4256 ns/op
PASS
ok      git.jlel.se/jlelse/GoSqliteBench        3.274s

Am I doing something wrong? Why is this library so much slower?

Right now, sqlitex.Pool.Get can return a nil Conn in a few circumstances, for example if the pool has been closed, or if the provided context has been canceled. This means consumers can't safely use the normal Get/defer Put idiom.

conn := pool.Get(ctx)
defer pool.Put(conn)
// use conn

And instead must do an explicit nil check within each stanza.

conn := pool.Get(ctx)
if conn == nil {
    // return
}
defer pool.Put(conn)
// use conn

It would be better if the Get method always returned a non-nil Conn, and, in the situations where it's not actually usable, have all of its methods short-circuit and return an appropriate error.

Using sqlitex.Transaction I get a lot of:

sqlite: step: database is locked: cannot commit transaction - SQL statements in progress

But if I manually sqlitex.Exec(... "BEGIN") and "END", I don't have this problem. Is there a difference I should be aware of?

I believe SQLite's PRAGMA user_version is one of those shiny traps. It looks like it'll do what you want for schema migrations, but it's not included in a database dump. Dump+restore will confuse your schema migration mechanism.

Instead, please consider using a table to store the schema version.

For inspiration: https://github.com/tv42/securityblanket/tree/master/internal/schema

Commit ed99db83671cdf65aeb34b1bcd490ba66d712e97 made it so the query

SELECT count() FROM objects WHERE id == "baz";

which previously returned 0, to error with

sqlutil.Exec: sqlite: prepare "SELECT count() FROM objects WHERE id == \"baz\"": SQL logic error: no such column: baz

Am I doing something wrong?

My OS is Windows-32bit.

When I try to build my golang program:

set GOARCH=386 go build .

I got this error messages:

zombiezen.com/go/sqlite

C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:68:20: undefined: sqlite3.Xsqlite3session_create C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:90:2: undefined: sqlite3.Xsqlite3session_delete C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:725:37: undefined: sqlite3.SQLITE_CHANGESET_DATA C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:726:37: undefined: sqlite3.SQLITE_CHANGESET_NOTFOUND C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:727:37: undefined: sqlite3.SQLITE_CHANGESET_CONFLICT C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:728:37: undefined: sqlite3.SQLITE_CHANGESET_CONSTRAINT C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:729:37: undefined: sqlite3.SQLITE_CHANGESET_FOREIGN_KEY C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:761:33: undefined: sqlite3.SQLITE_CHANGESET_OMIT C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:765:34: undefined: sqlite3.SQLITE_CHANGESET_ABORT C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:779:36: undefined: sqlite3.SQLITE_CHANGESET_REPLACE C:\Users\username\go\pkg\mod\zombiezen.com\go\[email protected]\session.go:90:2: too many errors

But it's OK in "windows-amd64" and "linux-386" and "linux-arm" and "linux-arm64".

Get/SetInt64 is great but per the SQLite docs...

INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

This would be we should be able to use

byte
int8
int16
int32
int64
uint8
uint16
uint32
uint64

By using the proper size we eliminate extra casting and memory allocations

Brilliant project idea. I decided to try it out in some of my usage of crawshaw.io/sqlite. Running cmd/[email protected] on https://github.com/anacrolix/torrent/commit/8e5ae65837e395430c1c202eb9274f3af335e83c:

~/go/src/github.com/anacrolix/torrent$ zombiezen-sqlite-migrate ./...
zombiezen-sqlite-migrate: loading packages...
zombiezen-sqlite-migrate: packages loaded
diff -u /Users/anacrolix/go/src/github.com/anacrolix/torrent/storage/sqlite-piece-completion.go /var/folders/rx/s8fbr8m17nvg88dq7lkblhz00000gn/T/zombiezen-sqlite-1540460039.go
--- /Users/anacrolix/go/src/github.com/anacrolix/torrent/storage/sqlite-piece-completion.go	2021-11-01 11:46:47.000000000 +1100
+++ /var/folders/rx/s8fbr8m17nvg88dq7lkblhz00000gn/T/zombiezen-sqlite-1540460039.go	2021-11-19 15:47:18.000000000 +1100
@@ -8,9 +8,9 @@
 	"path/filepath"
 	"sync"
 
-	"crawshaw.io/sqlite"
-	"crawshaw.io/sqlite/sqlitex"
 	"github.com/anacrolix/torrent/metainfo"
+	"zombiezen.com/go/sqlite"
+	"zombiezen.com/go/sqlite/sqlitex"
 )
 
 type sqlitePieceCompletion struct {
panic: interface conversion: types.Type is *types.Interface, not *types.Named [recovered]
	panic: interface conversion: types.Type is *types.Interface, not *types.Named

goroutine 1 [running]:
golang.org/x/tools/go/ast/astutil.Apply.func1()
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:47 +0x89
panic({0x124f280, 0xc00190c330})
	/Users/anacrolix/src/go1.17/src/runtime/panic.go:1038 +0x215
main.process.func1(0xc001eba250)
	/Users/anacrolix/go/pkg/mod/zombiezen.com/go/sqlite/cmd/[email protected]/migrate.go:326 +0xc52
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f79c0, 0xc001b97fc0}, {0x1285cfd, 0xc001b97fc0}, 0x123cd60, {0x12f7b28, 0xc00207dea0})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:198 +0x202
golang.org/x/tools/go/ast/astutil.(*application).applyList(0xc001eba240, {0x12f79c0, 0xc001b97fc0}, {0x1285cfd, 0x5})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:473 +0xae
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f79e8, 0xc001b377d0}, {0x12855f9, 0x0}, 0x8, {0x12f79c0, 0xc001b97fc0})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:220 +0x15f5
golang.org/x/tools/go/ast/astutil.(*application).applyList(0xc001eba240, {0x12f79e8, 0xc001b377d0}, {0x12855f9, 0x4})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:473 +0xae
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f7bf0, 0xc002113980}, {0x1287927, 0x0}, 0x0, {0x12f79e8, 0xc001b377d0})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:226 +0x1ba5
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f79c0, 0xc0016c6000}, {0x1285731, 0x5}, 0xc000ae13b0, {0x12f7bf0, 0xc002113980})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:295 +0x1352
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f79e8, 0xc001b37800}, {0x12855f9, 0x8}, 0xc000ae13a8, {0x12f79c0, 0xc0016c6000})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:221 +0x1645
golang.org/x/tools/go/ast/astutil.(*application).applyList(0xc001eba240, {0x12f79e8, 0xc001b37800}, {0x12855f9, 0x4})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:473 +0xae
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f7ab0, 0xc0021139f8}, {0x12868f9, 0x4}, 0x0, {0x12f79e8, 0xc001b37800})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:226 +0x1ba5
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f7a60, 0xc001b37950}, {0x1285731, 0x1015c0f}, 0x0, {0x12f7ab0, 0xc0021139f8})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:291 +0x6e5
golang.org/x/tools/go/ast/astutil.(*application).apply(0xc001eba240, {0x12f8320, 0xc00157e3b0}, {0x1285645, 0x28}, 0x28, {0x12f7a60, 0xc001b37950})
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:417 +0x17b1
golang.org/x/tools/go/ast/astutil.Apply({0x12f7a60, 0xc001b37950}, 0xc00190c300, 0x0)
	/Users/anacrolix/go/pkg/mod/golang.org/x/[email protected]/go/ast/astutil/rewrite.go:52 +0x166
main.process(0xc0007ede60, 0xc001c7d580)
	/Users/anacrolix/go/pkg/mod/zombiezen.com/go/sqlite/cmd/[email protected]/migrate.go:304 +0x454
main.run({0x12f94a8, 0xc000104780}, 0x0, {0xc000010050, 0x1, 0x1})
	/Users/anacrolix/go/pkg/mod/zombiezen.com/go/sqlite/cmd/[email protected]/migrate.go:58 +0x4fa
main.main()
	/Users/anacrolix/go/pkg/mod/zombiezen.com/go/sqlite/cmd/[email protected]/migrate.go:35 +0x145

Migrating from https://pkg.go.dev/crawshaw.io/sqlite but I'm missing the Changeset support. Any plans for implementation, would it be a welcome addition?

Bumps actions/checkout from 2 to 2.3.4.

Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.

An interesting observation that @anacrolix made on #30:

Adds optional checking that all parameters are set. I've been burned so many times by not setting parameters. I'm using this successfully in a production system. Interestingly the tests pass, I guess there's no tests for missing parameters.

I'm not sure that I want to add this to the existing API surface, since it could constitute a breaking change (what if someone is using the NULL default intentionally?), but I could be more easily convinced that any of the new ExecOptions-based functions (include those to be added for #5) could have this check by default.

Hello @zombiezen, Thanks for this repo, been using this library in my side projects for couple of months now. I have one question, Is there anyway I can get the the generated SQL statement print it out to stdout? or possibly passing a logger?

Thank you

I'd be nice to be able to have Get/Set of time.Time directly. Versions for rfc3339, int64 unix sec && int64 unix milliseconds it what i tend to use most. Are you open to PRs?

module temp

go 1.16

require zombiezen.com/go/sqlite v0.5.0 // indirect

package main

import (
        "context"
        "fmt"
        "sync"

        "zombiezen.com/go/sqlite"
        "zombiezen.com/go/sqlite/sqlitex"
)

func main() {
        var wg sync.WaitGroup
        for i := 0; i < 32; i++ {
                go func() { defer wg.Done(); f() }()
        }
        wg.Wait()
}

func f() {
        var (
                ctx      = context.Background()
                uri      = "file::memory:?mode=memory&cache=shared"
                flags    = sqlite.OpenReadWrite | sqlite.OpenCreate | sqlite.OpenURI | sqlite.OpenNoMutex
                poolSize = 32
        )

        pool, err := sqlitex.Open(uri, flags, poolSize)
        if err != nil {
                panic(err)
        }
        defer pool.Close()

        conn := pool.Get(ctx)
        if conn == nil {
                panic(fmt.Errorf("nil conn"))
        }
        defer pool.Put(conn)

        if err := sqlitex.Exec(conn, `SELECT 1;`, nil); err != nil {
                panic(err)
        }
}

$ go run -race main.go
==================
WARNING: DATA RACE
Read at 0x000001b620d4 by goroutine 20:
  modernc.org/sqlite/lib.Xsqlite3_initialize()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:158713 +0x158
  modernc.org/sqlite/lib.openDatabase()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:161013 +0x364
  modernc.org/sqlite/lib.Xsqlite3_open_v2()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:161348 +0x684
  zombiezen.com/go/sqlite.openConn()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlite.go:136 +0x648
  zombiezen.com/go/sqlite.OpenConn()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlite.go:80 +0xe7
  zombiezen.com/go/sqlite/sqlitex.Open()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlitex/pool.go:103 +0x2e8
  main.f()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:28 +0x8f
  main.main.func1()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:15 +0x64

Previous write at 0x000001b620d4 by goroutine 19:
  modernc.org/sqlite/lib.Xsqlite3_initialize()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:158728 +0x65b
  modernc.org/sqlite/lib.openDatabase()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:161013 +0x364
  modernc.org/sqlite/lib.Xsqlite3_open_v2()
      /Users/pbourgon/pkg/mod/modernc.org/[email protected]/lib/sqlite_darwin_amd64.go:161348 +0x684
  zombiezen.com/go/sqlite.openConn()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlite.go:136 +0x648
  zombiezen.com/go/sqlite.OpenConn()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlite.go:80 +0xe7
  zombiezen.com/go/sqlite/sqlitex.Open()
      /Users/pbourgon/pkg/mod/zombiezen.com/go/[email protected]/sqlitex/pool.go:103 +0x2e8
  main.f()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:28 +0x8f
  main.main.func1()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:15 +0x64

Goroutine 20 (running) created at:
  main.main()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:15 +0x94

Goroutine 19 (running) created at:
  main.main()
      /var/folders/m4/192hdv6n19j9x2v86k1r29rw0000gn/T/tmp.z2VKC9Xr/main.go:15 +0x94
==================
Found 1 data race(s)
exit status 66

Protecting the OpenConn call with a mutex solves it, apparently.

https://github.com/zombiezen/go-sqlite/blob/88da00a4e88fcd2c572032dacaec6c3d0ba7faee/cmd/zombiezen-sqlite-migrate/migrate.go#L42

I also want the tool to rewrite _test.go files. I'll need to keep track of files so I don't double-process a file.