Show HN: Sqinn-Go is a Golang library for accessing SQLite databases in pure Go | Hacker News Reader

Show HN: Sqinn-Go is a Golang library for accessing SQLite databases in pure Go

github.com

113 pointscvilsmeier61 comments

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.

Thank you! Nice to hear :-) Use the code any way you want, it's unlicensed.

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.

https://www.synopsys.com/blogs/software-security/unlicensed-...

The project uses The Unlicense:

https://github.com/cvilsmeier/sqinn-go/blob/master/LICENSE

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. :)

https://gitlab.com/cznic/sqlite

https://datastation.multiprocess.io/blog/2022-05-12-sqlite-i...

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).

the other pain point I know of is that it's hard to cross-compile. You can do it with zig[1], but it's still not pleasant.

1: https://zig.news/kristoff/building-sqlite-with-cgo-for-every...

Or with musl-cross:

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.

And Zig only covers a pretty limited range of platforms to start with.

mattn's sqlite3 is probably the most ideal use case for cgo imaginable. But it can still be annoying to set up cgo to build across many platforms.

I think Go should just pull a Zig and just embed a full blown C compiler into go build.

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.

Yeah, once I was on windows and couldn't get cgo sqlite working on a variety of mingw and alike compilers... felt like 90s.

Using this cznic's pure-Go sqlite saved the day.

Didn't it? Isn't that what 5c, 6c, and 8c were.

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.

[1] https://github.com/mattn/go-sqlite3/issues/1164

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.

Can you say more, like name your project?

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.

Sorry, just seen this. It is https://murex.rocks

sqlite3 only needs to run from one thread in my use case. Which might explain why I have fewer problems than most.

Just reread your comment and realised I was confusing your library with mattm’s library of the same name.

Interesting project, thanks for getting back!

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.

Thanks for the feedback! I get it.

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.

https://www.sqlite.org/src/artifact/2e8b12?ln=1073-1161

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 :)

> Just the usual cgo performance overhead?

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.

If you still have that itch to scratch, you can try: https://github.com/ncruces/go-sqlite3

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.

Very neat! Any idea how its performance compares to the modernc port?

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.

But compiling the non-go assistant process is not going to be any easier than cgo, right?

In the mentioned https://gitlab.com/cznic/sqlite there would not be any assistant process, right?

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.

Well its a different access pattern. As the underlying library points out:

> 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)

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.

I don't think so.

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)

> But compiling the non-go assistant process is not going to be any easier than cgo, right?

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)

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

I think it's a somewhat unfair (though who cares if it's unfair) comparison because you aren't using the database/sql interface and mattn does.

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!

Would it be possible to link a pure go codebase to pre-compiled sqlite binaries and not need to worry about cgo when cross compiling?

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.

But there's this if you wanna try: https://github.com/ebitengine/purego

Yep, confirming that cznic's pure-Go modernc.org/sqlite works great.

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.

Does compiling to WASM, using a cgo free WASM runtime, and replacing the OS layer (VFS) with portable Go code count?

That's the elevator pitch (so far) for: https://github.com/ncruces/go-sqlite3/tree/main

Yeah, that's cool. :) although probably a bit slower running through an interpreter.

Modernc/libc takes a rather different approach. It's got some pretty funny files in it. https://gitlab.com/cznic/libc/-/blob/master/musl_openbsd_arm...

It is slower.

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 cznic's argument is about cross compiling but just being able to avoid cgo. Since some people really don't like cgo.

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.

Is this go? https://gitlab.com/cznic/libc/-/blob/master/libc_openbsd.go?...

I mean technically I suppose it is code that conforms to the go language grammar, but I'm not sure why a language purist would accept this.

"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.

when there is no logic behind such "dislike".

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.

This is so cool... I see it has FTS5 support too. Now is the time to rewrite some of my apps into independent binaries

It’s not 100% Go, though. It forks a process that manages the sqlite file. Since the communication with it is written in go, it avoids using cgo.

Wow, that sounds incredibly inefficient!

If you're going to run the database in a background process, you may as well run MySQL or one of its derivatives, or Postgres. More concurrency.

> you may as well run MySQL

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.

> Wow, that sounds incredibly inefficient!

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

The benchmarks seem to put load on the db but not on the data.

I'd want a benchmark that is just select * from table, where table is a few gigabytes…

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.

Reply on news.ycombinator.com