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.
It's slower. But wazero is developing an optimizing compiler for amd64/arm64 (the current one is very naive) which I hope will close the gap on those platforms.
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.)
I use it for SQLite Database access in Go and Java. Java lets you theoretically interface with C code, but it's a lot of JNI/DLL/SO work. It's much easier for me to just os/exec (or Runtime/exec in Java) and send data back and forth. Your mileage may vary, of course.
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...
I don’t think the paradigm is particularly mismatched, right? If you translate C to Go, it would be pretty much best case for Go (neither language likes lots of small allocations). But Go lacks a lot of optimizations that most C compilers support, like return value optimization, aggressive inlining, etc. C also has lighter-weight function calls and so on that you pay for in Go.
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.
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.
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.
Here lies the problem. I now need to instruct users that different build flags are required for different platforms. Whereas that problem doesn’t exist with the cgo bindings.
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.
I'll definitely keep an eye on this project. And likewise, if you feel you reach a new stable milestone that addresses my concerns, do feel free to raise an issue on my projects Github page for me to switch away from cgo :)
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.
