golang and sqlite

a_sqlite.go

package main

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"fmt"
	"path"
	"sync"
	"sync/atomic"
	"testing"

	mattnSqlite "github.com/mattn/go-sqlite3"     // with cgo
	tailscaleSqlite "github.com/tailscale/sqlite" // with cgo
	moderncSqlite "modernc.org/sqlite"            // cgo free

	"github.com/google/uuid"
	ongId "github.com/komuw/ong/id"
)

// Also see: https://github.com/benbjohnson/sqlite-bench
// really Good: https://kerkour.com/sqlite-for-servers
//
// Sqlite index automatic recommendations: https://sqlite.org/cli.html#index_recommendations_sqlite_expert_


/*
run:
  goimports -w .;gofumpt -extra -w .;gofmt -w -s .;go mod tidy;go test --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' -race ./...
  go test --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' -timeout 1m -race -run=XXXX -bench=. ./...
  go test --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' -timeout 1m -race -run=XXXX -bench=. -cpu 1,2,4,16 ./... # set different parallelism(list of GOMAXPROCS values for which the tests/benchmarks should be executed.)
  go test --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' -timeout 1m -run=XXXX -bench=. -cpu 1,2,4,16 ./... # without RACE detector.
  # mattnSqlite & tailscaleSqlite share some C definitions. Hence we need to allow multiple of them.
  # The two libraries also have `init` funcs that try and register a driver with same name.
  # Luckily, mattnSqlite offers a way to override it using ldflags.
  # https://stackoverflow.com/a/64796847/2768067
  go test \
    --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' \
	-timeout 1m \
	-run=XXXX \
	-bench=. \
	-cpu 1,2,4,16 ./... # without RACE detector.
*/

/////////////////////////////////////////////// app ///////////////////////////////////////////////

const (
	mattnDriverName     = "mattnSqlite3"
	moderncDriverName   = "moderncSqlite3"
	tailscaleDriverName = "tailscaleSqlite3"
)

var (
	driverRegOnce sync.Once

	listOfStringIds = map[string][]string{
		"ongId":           {},
		"ongUuid4Text":    {},
		"ongUuid8Text":    {},
		"googleUuid4Text": {},
	}

	listOfByteIds = map[string][][]byte{
		"ongUuid4Blob":    {},
		"ongUuid8Blob":    {},
		"googleUuid4Blob": {},
	}
)

func init() {
	{ // driver registration.
		driverRegOnce.Do(func() {
			sql.Register(mattnDriverName, &mattnSqlite.SQLiteDriver{})
			sql.Register(moderncDriverName, &moderncSqlite.Driver{})
			{
				connInitFunc := func(ctx context.Context, conn driver.ConnPrepareContext) error {
					return nil
				}
				connector := tailscaleSqlite.Connector("/tmp/some-dummy-doesnt-matter.db", connInitFunc, nil)
				sql.Register(tailscaleDriverName, connector.Driver())
			}
		})
	}
	{ // init IDs
		for i := 0; i < 500_000; i++ {
			listOfStringIds["ongId"] = append(listOfStringIds["ongId"], ongId.New())
			listOfStringIds["ongUuid4Text"] = append(listOfStringIds["ongUuid4Text"], ongId.UUID4().String())
			listOfStringIds["ongUuid8Text"] = append(listOfStringIds["ongUuid8Text"], ongId.UUID8().String())
			listOfStringIds["googleUuid4Text"] = append(listOfStringIds["googleUuid4Text"], uuid.NewString())

			listOfByteIds["ongUuid4Blob"] = append(listOfByteIds["ongUuid4Blob"], ongId.UUID4().Bytes())
			listOfByteIds["ongUuid8Blob"] = append(listOfByteIds["ongUuid8Blob"], ongId.UUID8().Bytes())
			xxx := uuid.New()
			listOfByteIds["googleUuid4Blob"] = append(listOfByteIds["googleUuid4Blob"], xxx[:])
		}
	}
}

func WriteBlogPost(db *sql.DB, title, content string) error {
	_, err := db.Exec(`insert into posts (title, content) values (?, ?)`, title, content)
	return err
}

var m sync.Mutex

func WriteBlogPostMutexed(db *sql.DB, title, content string) error {
	m.Lock()
	defer m.Unlock()
	_, err := db.Exec(`insert into posts (title, content) values (?, ?)`, title, content)
	return err
}

/////////////////////////////////////////////// app ///////////////////////////////////////////////

/////////////////////////////////////////////// tests ///////////////////////////////////////////////

func BenchmarkWriteBlog(b *testing.B) {
	b.ReportAllocs()

	for _, driver := range [3]string{mattnDriverName, moderncDriverName, tailscaleDriverName} {

		b.Run(fmt.Sprintf("%s - write blog post with WAL", driver), func(b *testing.B) {
			db := setupDB(b, driver)
			if driver == moderncDriverName || driver == tailscaleDriverName {
				// not setting this at all causes the modernc version to immediately fail with "DB locked"
				db.SetMaxOpenConns(1)
			}

			var count int32 = 1
			b.ResetTimer()
			b.RunParallel(func(pb *testing.PB) {
				for pb.Next() {
					// The loop body is executed b.N times total across all goroutines.
					if err := WriteBlogPost(db, "Some title", "Some content"); err != nil {
						b.Fatal(err)
					}
					atomic.AddInt32(&count, 1)
				}
			})
			b.ReportMetric(float64(count), "writes/s")
		})

		b.Run(fmt.Sprintf("%s - write blog post with WAL and Go mutex", driver), func(b *testing.B) {
			db := setupDB(b, driver)
			if driver == moderncDriverName || driver == tailscaleDriverName {
				// not setting this at all causes the modernc version to immediately fail with "DB locked"
				db.SetMaxOpenConns(1)
			}

			var count int32 = 1
			b.ResetTimer()
			b.RunParallel(func(pb *testing.PB) {
				for pb.Next() {
					if err := WriteBlogPostMutexed(db, "Some title", "Some content"); err != nil {
						b.Fatal(err)
					}
					atomic.AddInt32(&count, 1)
				}
			})
			b.ReportMetric(float64(count), "writes/s")
		})
	}
}

// https://kerkour.com/sqlite-for-servers
func setupDB(b *testing.B, drivername string) *sql.DB {
	// really Good: https://kerkour.com/sqlite-for-servers
	//
	dbPath := path.Join(b.TempDir(), "benchmark.db")
	db, err := sql.Open(drivername, dbPath)
	if err != nil {
		b.Fatal(err)
	}

	{ // pragmas:
		if _, err := db.Exec("PRAGMA journal_mode = WAL;"); err != nil {
			b.Fatal(err)
		}

		// busy_timeout` pragma in milliseconds: https://www.sqlite.org/pragma.html#pragma_busy_timeout
		if _, err := db.Exec("PRAGMA busy_timeout = 1000;"); err != nil {
			b.Fatal(err)
		}

		if _, err := db.Exec("PRAGMA foreign_keys = ON;"); err != nil {
			b.Fatal(err)
		}
	}

	{
		_, err := db.Exec(`
		create table posts (
			id integer primary key,
			title text not null,
			content text not null,
			created text not null default (strftime('%Y-%m-%dT%H:%M:%fZ'))
		) STRICT;`)
		if err != nil {
			b.Fatal(err)
		}
	}
	return db
}

// BenchmarkIds benchmarks using different ID's.
func BenchmarkIds(b *testing.B) {
	// go test --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' -timeout 1m -race -run=XXXX -bench=BenchmarkIds ./...

	b.ReportAllocs()

	setup := func(b *testing.B, idType string) *sql.DB {
		dbPath := path.Join(b.TempDir(), "benchmark.db")
		db, err := sql.Open(mattnDriverName, dbPath)
		if err != nil {
			b.Fatal(err)
		}

		{ // pragmas:
			if _, err := db.Exec("PRAGMA journal_mode = WAL;"); err != nil {
				b.Fatal(err)
			}

			// busy_timeout` pragma in milliseconds: https://www.sqlite.org/pragma.html#pragma_busy_timeout
			if _, err := db.Exec("PRAGMA busy_timeout = 1000;"); err != nil {
				b.Fatal(err)
			}

			if _, err := db.Exec("PRAGMA foreign_keys = ON;"); err != nil {
				b.Fatal(err)
			}
		}

		{
			switch idType {
			default:
				b.Fatalf("id type `%v` is not known", idType)
			case "integer":
				_, err := db.Exec(`
				CREATE TABLE posts (
					id integer primary key,
					title text not null
				) STRICT;`)
				if err != nil {
					b.Fatal(err)
				}
			case "ongId", "ongUuid4Text", "ongUuid8Text", "googleUuid4Text":
				_, err := db.Exec(`
				CREATE TABLE posts (
					id text primary key,
					title text not null
				) STRICT, WITHOUT ROWID;`)
				if err != nil {
					b.Fatal(err)
				}

			case "ongUuid4Blob", "ongUuid8Blob", "googleUuid4Blob":
				_, err := db.Exec(`
				CREATE TABLE posts (
					id blob primary key,
					title text not null
				) STRICT, WITHOUT ROWID;`)
				if err != nil {
					b.Fatal(err)
				}
			}
		}

		return db
	}

	writeString := func(db *sql.DB, idType, idValue string) {
		switch idType {
		default:
			b.Fatalf("id type `%v` is not known", idType)
		case "integer":
			_, err := db.Exec(`insert into posts (title) values (?)`, "hello World")
			if err != nil {
				b.Fatal(err)
			}
		case "ongId", "ongUuid4Text", "ongUuid8Text", "googleUuid4Text":
			_, err := db.Exec(`insert into posts (id, title) values (?, ?)`, idValue, "hello World")
			if err != nil {
				b.Fatal(err)
			}
		}
	}

	writeBytes := func(db *sql.DB, idType string, idValue []byte) {
		switch idType {
		default:
			b.Fatalf("id type `%v` is not known", idType)
		case "ongUuid4Blob", "ongUuid8Blob", "googleUuid4Blob":
			_, err := db.Exec(`insert into posts (id, title) values (?, ?)`, idValue, "hello World")
			if err != nil {
				b.Fatal(err)
			}
		}
	}

	k := "integer"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeString(db, k, "")
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "ongId"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeString(db, k, listOfStringIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "ongUuid4Text"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeString(db, k, listOfStringIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "ongUuid8Text"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeString(db, k, listOfStringIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "googleUuid4Text"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeString(db, k, listOfStringIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "ongUuid4Blob"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeBytes(db, k, listOfByteIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "ongUuid8Blob"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeBytes(db, k, listOfByteIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "googleUuid4Blob"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeBytes(db, k, listOfByteIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})
}

/////////////////////////////////////////////// tests ///////////////////////////////////////////////

sqlite-and-Golang-Dockerfile

FROM golang:1.19.4-bullseye AS builder

# docker build -t benchbin .
#
# docker \
#     run \
#     -it \
#     --memory-reservation=80m \
#     --memory=120m \
#     --memory-swap=125m \
#     --cpu-quota=25000 \
#     --cpu-period=50000 \
#     benchbin:latest
#
# Restrict memory to a max of 125MB.
# Restrict cpu to 50% run-time every 50_000 microsec(50 millisec)


WORKDIR /app

COPY go.mod ./
COPY go.sum ./
RUN go mod download

COPY *.go ./

RUN go test \
    --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' \
    -timeout 2m \
    -run=XXXX \
    -bench=. \
    -cpu 1,2,4,16 \
    -c \
    -o benchbin.test \
    ./...

CMD ["./benchbin.test", "-test.run=XXXX", "-test.bench=.", "-test.cpu=1,2,4,16"]

Taken from;

1. https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7ec112bfc887be
2. https://news.ycombinator.com/item?id=33894995
3. https://github.com/benbjohnson/wtf/blob/v0.2.2/sqlite/sqlite.go#L61-L144
4. https://github.com/benbjohnson/sqlite-bench
5. https://github.com/kalafut/go-sqlite-bench#findings
6. https://www.golang.dk/articles/benchmarking-sqlite-performance-in-go-secret

on my machine;

go test \                      
    --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' \
        -timeout 1m \
        -run=XXXX \
        -bench=. \
        -cpu 1,2,4,16 ./...

goos: linux
goarch: amd64
pkg: cool2
cpu: Intel(R) Core(TM) i7-10510U CPU @ 1.80GHz
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL                      14_208 ns/op   84_265 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL-2                    13_621 ns/op   83_007 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL-4                    14_749 ns/op   80_920 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL-16                   15_230 ns/op   70_631 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL_and_Go_mutex         12_158 ns/op   84_147 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-2       14_012 ns/op   72_961 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-4       14_269 ns/op   83_966 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-16      14_917 ns/op   82_406 writes/s
//
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL                    18_375 ns/op   62_215 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL-2                  23_420 ns/op   51_491 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL-4                  22_851 ns/op   51_777 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL-16                 22_746 ns/op   49_732 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL_and_Go_mutex       18_974 ns/op   63_115 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-2     19_678 ns/op   57_652 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-4     20_381 ns/op   52_957 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-16    20_931 ns/op   55_633 writes/s
//
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL                  11_246 ns/op   97_666 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL-2                14_833 ns/op   80_212 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL-4                14_329 ns/op   79_747 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL-16               14_631 ns/op   73_673 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL_and_Go_mutex     12_255 ns/op   93_839 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-2   14_699 ns/op   80_216 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-4   14_970 ns/op   78_218 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-16  16_695 ns/op   68_062 writes/s

on docker(to simulate a constrained environment):

# restrict to 125MB and 50% cpu-share every 50_000 microsecs(50 millisecs)
docker \
  run \
  -it \
  --memory-reservation=80m \
  --memory=120m \
  --memory-swap=125m \
  --cpu-quota=25000 \
  --cpu-period=50000 \
  benchbin:latest

goos: linux
goarch: amd64
pkg: cool2
cpu: Intel(R) Core(TM) i7-10510U CPU @ 1.80GHz
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL                       65_177 ns/op      19_634 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL-2                     64_982 ns/op      18_947 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL-4                     99_620 ns/op      13_958 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL-16                    87_558 ns/op      11_811 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL_and_Go_mutex          53_319 ns/op      23_373 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-2        55_984 ns/op      19_190 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-4        67_402 ns/op      18_849 writes/s
BenchmarkWriteBlog/mattnSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-16       68_927 ns/op      18_778 writes/s
//
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL                     3_531_356 ns/op    333.0 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL-2                   3_302_306 ns/op    369.0 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL-4                   3_487_099 ns/op    412.0 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL-16                  3_505_174 ns/op    334.0 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL_and_Go_mutex        3_482_946 ns/op    344.0 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-2      3_474_251 ns/op    320.0 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-4      3_213_141 ns/op    344.0 writes/s
BenchmarkWriteBlog/moderncSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-16     3_121_613 ns/op    411.0 writes/s
//
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL                   50_067 ns/op      31_001 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL-2                 47_794 ns/op      27_122 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL-4                 48_758 ns/op      23_182 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL-16                48_538 ns/op      23_669 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL_and_Go_mutex      51_000 ns/op      26_698 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-2    53_994 ns/op      20_563 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-4    65_120 ns/op      18_580 writes/s
BenchmarkWriteBlog/tailscaleSqlite3_-_write_blog_post_with_WAL_and_Go_mutex-16   59_106 ns/op      18_671 writes/s

Also see: https://github.com/benbjohnson/sqlite-bench

Benchmarking using different ID types as primary keys.

go \
test \
--ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' \
-timeout 1m \
-run=XXXX \
-bench=BenchmarkIds ./...

BenchmarkIds/integer_-_BenchmarkIds-8         	   8_069 ns/op	  127_786 writes/s	  18 allocs/op
BenchmarkIds/ongUuid8Text_-_BenchmarkIds-8    	   9_685 ns/op	  118_908 writes/s	  21 allocs/op
BenchmarkIds/ongUuid8Blob_-_BenchmarkIds-8    	   9_907 ns/op	  117_398 writes/s	  21 allocs/op
BenchmarkIds/ongId_-_BenchmarkIds-8           	  11_892 ns/op	  115_629 writes/s	  21 allocs/op
BenchmarkIds/googleUuid4Blob_-_BenchmarkIds-8 	  12_471 ns/op	  113_888 writes/s	  21 allocs/op
BenchmarkIds/ongUuid4Text_-_BenchmarkIds-8    	  12_694 ns/op	  111_411 writes/s	  21 allocs/op
BenchmarkIds/googleUuid4Text_-_BenchmarkIds-8 	  13_113 ns/op	  108_937 writes/s	  21 allocs/op
BenchmarkIds/ongUuid4Blob_-_BenchmarkIds-8    	  12_087 ns/op	  108_673 writes/s	  21 allocs/op

_with_WAL & with_WAL_and_Go_mutex basically perform the same.

Even with the busy_timeout increased from 1 second to 120 seconds, the modernc.org/sqlite driver still errors with: main_test.go:85: database is locked (5) (SQLITE_BUSY)

fixed by adding db.SetMaxOpenConns(1)

@komuw in my understanding, you basically restrict both reads and writes to one at a time when using db.SetMaxOpenConns(1).