No, the performance is certainly orders of magnitude faster than serializing over std streams on a subprocess (c ffi calls in cgo are 10s of nanoseconds).
But one of the big draws of golang is the write-once-compile-anywhere toolchain and calls cgo makes that harder.
As soon as you or a library touches cgo, you have to deal with finding and setting up C cross-compilation tooling for your target(s), dynamic linking details, tons of stuff that may have nothing to do with your code or be an area you're an expert in as a Go developer.
You'll need to use the sqlite3_nolock build tag; concurrent writes will quickly corrupt your database. SetMaxOpenConns(1) is your friend.
But it should work. I'm interested if it doesn't. Feedback appreciated.
> It is used in programming environments that do not allow calling C API functions.
Also I guess which one is easier will be subjective. The steps are sorta similar:
Step 1) install sqlite or squinn on the base system (the latter might be harder)
Step 2) if sqllite use cgo, if squinn just use go (the former might be harder but more performant)
I'm actually surprised by how readable this came out; props to the Go->C compiler author. But you can guess that pushing this sort of thing through the Go compiler is going to cause some slowdowns due to sheer paradigm mismatch: https://gitlab.com/cznic/sqlite/-/blob/master/lib/sqlite_lin...
Maybe this is what you mean by paradigm mismatch, but usually I would think of something like translating an allocation-heavy Java app into Go as paradigm mismatch.
The main go compilation must work in all sorts of environments: dev computers, CI runners.. it should be quick and automated to keep development fast. It should be easy, so everyone on your team can do this.
The assistant process is basically built once and then never changes, you just need to keep a binary somewhere (and they seem to be <1MB so you can check them into git directly). So a single person somewhere has to figure how to do a C build once, and everyone else can benefit. Have your someone ssh into CI runner directly and install gcc. Spend a day installing compiler and messing with Makefiles on exotic OS. You only do it once and you are good forever (or until you want to bump sqlite version)
Right. But you have to do it only once, or you can download a pre-built library from https://github.com/cvilsmeier/sqinn/releases (windows/amd64 or linux/amd64)
I think Go should just pull a Zig and just embed a full blown C compiler into go build.
If I’m choosing to use a C framework (SQLite) I’m okay signing up for the environment costs. Prefer that over abstractions in an intermediate layer that might not be maintained in a few years.
Using this cznic's pure-Go sqlite saved the day.
I also once tried to figure out a way to cross compile mattn/go-sqlite3 to 32bit Windows with zig, and failed.
The best way to make cross compiling painless is to not use cgo at all. Which is why I use modernc/sqlite whenever possible now.
Btw, bundling a C compiler is also much harder when you don't build on top of LLVM.
Personally I’ve had much more portability problems with a lot of the native Go ports of sqlite than I have with mattn’s cgo library.
I author a shell that targets most of the architectures and platforms supported by Go. At the request of some users, I added support for a Go native library because they didn’t want to install a C compilers as well as a Go compiler. I tried a few different sqlite ports (though off hand cannot recall which ones) and they all had massive limitations, like only compiling on Windows and Linux (in one example).
In the end, I gave up and reverted back to the cgo version with the option for other libraries hidden behind a compiler flag.
I found my build pipeline manages just fine with cross compiling and haven’t had any complaints (thus far) with the binaries bar one individual running an ancient version of CentOS.
Maybe I’ve been trying the wrong sqlite ports. But here lies the problem: with cgo I know I’m getting a stable, tested, library. With other ports it’s entirely a lottery with regards to how well maintained and tested it might be. For personal projects that’s a fine risk to take but for any larger open source (or even commercial) projects, that additional uncertainty is a risk that distracts me and the other contributors from working on the core part of the project. And thus defeats the connivance of using 3rd party libraries.
I'm building https://github.com/ncruces/go-sqlite3 and am a community maintainer of https://wazero.io
I can cross compile SQLite into all platforms that Go OOB compiles too, with the caveat that any that aren't linux/windows/darwin/freebsd/illumos (CPU architecture doesn't matter) need a build flag because of file locking: https://github.com/ncruces/go-sqlite3/blob/main/vfs/README.m...
Anything that helps me test portability, or any feedback you might have on it, would be greatly appreciated.
sqlite3 only needs to run from one thread in my use case. Which might explain why I have fewer problems than most.
My SQLite bindings should build/work fine for all your supported platforms, with the caveat that BSDs other than FreeBSD need a build tag, because my locking protocol is not compatible with SQLite's default (if you access your database concurrently with my wrapper and other SQLite processes, you may corrupt data).
Solaris and Plan9 don't have working file locking, so you can't use concurrency at all. This could be change if there was interest in it.
Plus I also have the additional risk that I’m using someone’s hobby project that, and I say this with the greatest of respect for yourself, might get abandoned tomorrow for all I know.
Cgo might have its issues but for sqlite3 it offers far better assurances than anything else available for Go at this point in time.
Given that sqlite3 is a supporting library rather than the differentiator of my project, I want to spend as little time as I have to supporting it. At this point in time, only the cgo library seems to offer that convenience (though if anyone else wants to maintain a fork of my project using a different sqlite library then I’m more than happy with that).
Good luck with your project though. Hopefully the Go community can rally around one of these native solutions so it gains enough traction to become the new de facto standard.
I could easily build on all platforms without tags… I just want to avoid accidental data corruption if users unwittingly access databases without proper synchronization.
The MVCC-WAL-VFS I'm designing now may potentially fix this, as the brokenness of POSIX advisory locks is more manageable there.
1: https://zig.news/kristoff/building-sqlite-with-cgo-for-every...
https://github.com/FiloSottile/homebrew-musl-cross
It works pretty well! It's a thing you might keep in your back pocket to test builds from your ARM dev machine on a dev host, and then let the CI/CD system build the real version later.
If you drop that interface, you get much better performance.
See: https://github.com/eatonphil/gosqlite for example.
Edit: Nevermind, you did include crawshaw (which doesn't use database/sql) in your benchmark!
It seems like a pretty good cgo use case: a decent amount of work, which is typically slow enough that cgo overhead isn’t perf critical (because DB usually means disk reads), a super robust and well tested C library with a super well maintained cgo wrapper (mattn).