Nice work! It's fun to see other people hacking away at Golang projects. I've been contemplating how to write tests for my own project, and this has given me some ideas.
Unlicensed is the same as fully copyrighted. There is a presumotion of ownership. Licenses in this case serve to clarify allowable uses. Without a license, nothing is allowable as you maintain the right to do anything within a copyright holder's legal right.
If you really want to use SQLite without anything that isn't Go (since this project involves forking and communicating with a non-Go SQLite in a separate process over pipes), there's a Go translation of SQLite's C source. :)
What’s the case against cgo for SQLite? Just the usual cgo performance overhead?
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).
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.
To be a bit more specific here: pure Go binaries are trivial to cross-compile and they Just Work™ basically all the time. `GOOS="darwin" GOARCH="arm64" go build .` and you're done. Just iterate over the combinations you care about, they'll all work.
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.
Golang works on Plan9. It can even bootstrap itself. A few months ago I was trying to setup some server software on 9Front for giggles and while most stuff worked I couldn't past the Sqlite CGO dependencies.
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)
Which programming environments that do not allow calling C API functions also let you build/ship arbitrary C executables, though? (Genuinely curious what scenarios this unlocks.)
No, but that has the disadvantage of being C compiled into Go, then being compiled into native executable.
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...
Pulling a zig isn't going to solve all problems, as the zig cross compiler itself runs into problems fairly often. I already use CC='zig cc -target x86_64-linux-musl' (or whatever the target is) with cgo to cross-compile mattn/go-sqlite3, and relatively recently it simply stopped working[1] and a workaround had to do be added to about every single project of mine using SQLite through cgo.
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.
I’m going to add a counter argument to all the cgo views raised:
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 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.
Exactly. Good to know I didn’t miss a trade off in the mix.
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.
Just for the record, had to compile sqlite in gomobile literally the day after this comment and it was a big pain . But got it working and sticking with this approach.
Sqinn author here. Nothing against CGO, but I develop/deploy on Win/Linux, and cross-compiling CGO is very painful. Regarding performance: To my own surprise, Sqinn out-performs mattn (and others) for normal workloads, see https://github.com/cvilsmeier/sqinn-go-bench
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.
The logic of easy cross compiles doesn't really hold up for go translated SQLite. It depends on a huge pile of per platform support code, of varying quality. If you're only going to target known working platforms, may as well use cgo and a known working cross compiler.
The WASM runtime https://wazero.io has a compiler on amd64 and arm64 (on Linux, macOS, Windows, and FreeBSD), but the current compiler while very fast (at compiling), is very naive (generates less than optimal code).
An optimizing compiler is currently being developed, and should be released in the coming months. I'm optimistic that this compiler will cover the performance gap between WASM and modernc.
I don't think I understand what the argument for that is, because I've only ever heard it articulated as "cgo isn't go" which doesn't really convey much information.
"cgo isn't go" is repeated as a point of religion by some.
That said, I have a bunch of QEMU VMs just to compile cgo to platforms like OpenBSD, illumos, macOS, Windows, etc.
OpenBSD and such aren't that much of a hassle because it performs pretty well and these systems do what I tell them to do, unlike macOS and especially Windows, which are much more annoying and slower because they seem to spend most of their time running searching indexers, virus scanners, updaters, and who knows what, and then maybe perhaps, if the laptop is held at the right angle, and if it so behoves, also decides to spend some CPU cycles to my compiler.
Either way, the pain is real, at least for me. Although, yeah, that obviously isn't a solution.
In general I'm wary of write/translate from langX to langY. At a previous job another team was rewriting ColdFusion code to Go. "ColdFusion with Go syntax" was certainly a creative and novel use of the Go language. Translating from one language to another always seems hard because your brain gets "primed" by the source language, or something, and not all concepts necessarily map all that well in the first place – I have the same translating text, which I've generally found surprisingly hard.
people are into programming because things can be reasoned, there is logic behind everything. yet there are non-senses that just try to waste people's time & energy by introducing some of their personal briefs backed by no logic into the whole thing.
cross-compiling regular c projects like sqlite should never be an excuse, I don't believe there is anything that can't be scripted when it come to cross-compiling sqlite.
The dynamic linking story in Go is complicated at best, particularly if you shun cgo, because you commonly need to cgo into ld.so to use dynamic linking.
You're right, there are use-cases where SQLite is not appropriate. But nothing beats the ease of installation/backup/maintenance of SQLite compared to server databases like MySQL or Postgres.
Another point is development: For unit-tests, I found that initializing a SQLite database for each test-run is much easier (and faster) than having a VM that runs Postgres/MySQL/etc, which I have to spin up and tear down before/after each test run.
Eh, the backup is a bit of a pain... The sqlite3 tool supports a .backup command but it's basic and can't even write to stdout, only directly to a file.
There is an online backup api though but most APIs and libraries built around sqlite don't even acknowledge it, let alone support it.
Just copying the sqlite file is of course not a real backup and if done at the wrong time will lead to a corrupted backup.
That's exactly what I thought, too. But, to my own surprise, it's as fast as CGO solutions, in most cases even faster. You may check here: https://github.com/cvilsmeier/sqinn-go-bench
As with all things software: you can't eat your cake and have it. This is a compromise which suits many use cases, but not all. I've experimented with the version that was translated from C to go, and it worked fine for me (low intensity service using the db for auth checks and logging). Sqinn-go will probably serve as well.
This is pretty much a os/exec + stdin/stdout redirection library.
I've been using cgo for accessing sqlite for ages, never ever had any trouble. cgo is not remotely as bad as what people might believe. give cgo a go, and you will realize that it saves your time, let you focus on your projects rather than non-senses like how to use stdin/stdout redirection to bypass cgo.