Go port of SQLite without CGo | Hacker News Reader

Go port of SQLite without CGo

gitlab.com

244 pointscube2222116 comments

If you cross-compile (which everybody using macOS to developer for Linux servers does), it's annoying to introduce SQLite to your project, because immediately the conventional `GOOS= GOARCH= go build` trick stops working. But, in case it's helpful, it's also really easy to set up a full cross-compiling environment, either with Zig or with Filippo's musl-cross:

https://words.filippo.io/easy-windows-and-linux-cross-compil...

I debated doing the pure-Go SQLite thing, but musl-cross worked just fine for me, and kept things simpler.

Is there something like this for cross-compiling to FreeBSD?

You can check how ClickHouse is cross-compiled for FreeBSD.

1. https://github.com/ClickHouse/sysroot/

2. https://github.com/ClickHouse/ClickHouse/blob/master/cmake/f... and the `CMAKE_TOOLCHAIN_FILE` variable.

Cross compiling with zig is so easy. For example I use sqlite in Go projects on arm. The incantation is just:

    export GOARCH=arm64
    export CC=zig cc -target aarch64-linux-musl
    export CXX=zig cc -target aarch64-linux-musl

Cross compilation is always easy when there are no dependencies to the target platform SDKs. When it goes beyond basic libc stuff, then it is when the fun starts.

It seems fairly straightforward - copy over the SDK header files and libraries to the project and then add these envs to the build:

    CC="/path/to/cross/compiler" CGO_CFLAGS=—I/path/to/sdk/header/files/include" CGO_LDFLAGS="-L/path/to/sdk/obj/files/lib”

I suppose with .so files, you might have to specify "-Wl,-rpath" if the SDK libraries don’t reside under /usr/lib or /usr/local/lib on the target system. But I haven’t actually tried any of this yet, so I could be wrong. Are there any gotchas that I’m missing?

Now do the same as Windows UWP SDK, or iOS SDK.

You're missing all the tooling and OS specific steps required to produce a proper package.

For macOS: https://github.com/tpoechtrager/osxcross

Doesn't seem to do IO Kit, DriverKit, Metal, Instruments Plugins, UI frameworks, XPC, and plenty of other stuff dependent on Objective-C/Swift.

I believe it would work for all of that, given that it links to the same libraries an app made with Xcode would.

That repository won't compile Swift, but it does compile objective C.

Either way, you don't need to be able to compile Swift/Obj C to link to a library that was written in Swift/Obj C.

Not really, unless you're talking about specific Go developers, and even there, that is what the CI/CD pipeline is for.

No one should touch servers directly, unless for down tracking stuff or remote development, in which case, they also don't need to cross compile.

Pretty sure lots of people still touch servers directly, and will continue to until servers are no longer a thing.

I mostly touch servers remotely

Sure, but then DevOps aren't doing their jobs.

No devs on production servers unless for tracking down issues, or servers configured for remote development.

Which in both cases, don't require cross compilation to start with.

There are less and less touching servers directly happening. Not only DevOps but the current raise of k8s and serverless deployments making it less relevant. There is also the security aspect of it.

The downside is that people (especially the ones who are just joining the workforce) are less efficient with the command line and more dependant on GUI. For example some of the junior devs do not know git cli anymore and rely on the VSCode plugin to interact with git. This becomes an issue when there is a bug in the plugin and you should use the CLI.

That is easy to sort out, like every git problem, clone the repo and start again.

I know SCM tooling since the RCS days, and couldn't be bothered to master git implementation details to fix broken repos.

Unfortunely we are stuck with git for the time being.

I agree with you based the amount of time someone needs to spend to fix a broken repo.

I just really like to understand the tools that I use. Maybe it is a waste of time.

Yes, we're shelling into k8s containers now

Or worse, when what you think is a big actually is a feature. I basically use vscode git only for staging and merging and sometimes not even that, when it has issues with hunks on the beginning of the file or whatever.

DevOps should IMHO not be a job role but a company philosophy.

The developer should alao maintain their software and deploy it. Has IMHO a lot of advantages

This comment leads me to believe that you’re used to working in one type of organisation to the point where you’re projecting it on your entire understanding of this incredibly broad industry.

Indeed, Fortune 500 consulting.

Everyone _should_ also have 100% test coverage, canary deployments, and local dev parity. Unfortunately, the real world gets in the way and means we don’t all work with optimal setups.

In that case, it starts by having a local environment that matches the OS of the servers.

Linux OEM vendors will appreciate it.

You’re running graviton arm locally? It’s all docker containers in the end, so that’s what you get.

No, and why should I?

I got into UNIX development back when the whole team shared a development server and we used telnet and X Windows for our "IDE".

No one was running SPARC, RISC System/6000, PA-RISC, MIPS, Eclipse MV locally.

When everyone keeps worshipping UNIX, maybe it is time to learn how grey beards used to develop for it.

> it starts by having a local environment that matches the OS of the servers

> No, and why should I?

These two statements are in direct conflict. CPU architecture is as much part of “matching the server” as operating system.

When I built software for SPARC, I was also running SPARC locally…

That's how I came up too. It sucked. It's good that it's being forgotten.

The following post [0] describes how to build static binaries even with cgo enabled:

    GOOS= GOARCH= CGO_ENABLED=1 go build \
      -tags osusergo,netgo,sqlite_omit_load_extension \
      -ldflags="-extldflags=-static"

[0]: https://www.arp242.net/static-go.html

As someone who has had to develop desktop software (ugh) with true cross-platform support for MacOS, Linux, and Windows (thanks Animation and VFX industry) using golang, I can assure everyone that it is not “really easy” to make things like Qt compile for different OSes on different OSes. IIRC trying to build for M1 Mac on AMD64 Windows was the worst.

As someone who has had to develop desktop software (ugh) with true cross-platform support for MacOS, Linux, and Windows (thanks Animation and VFX industry) using golang, I can assure everyone that it is not “really easy” to make things like Qt cross-compile with golang. IIRC building for Mac on Windows was the worst.

Beware: This library seems to have bugs that cause it to break on some writes. Recently held an event that heavily used this library and it broke on us half way through, had to wipe the database and switch it back to the cgo version (the data inside was mostly ephemeral, which was quite a relief).

Yeah I would be very suspect about this library. Writing a transpiler that handles all aspects of the C spec correctly is hard enough, to say nothing of replicating stuff that is technically undefined behavior but known to work a certain way. C is not Go and Go is not C--simply rewriting syntax will not result in exactly the same behavior.

I've noticed it has different transaction behavior than normal Sqlite, which can cause things to break more easily. I patch Go software that uses it to use the C library as a result, and I stopped having corruption problems.

Hey, sorry to hear that. I'm a contributor on the project and if you're able to open an issue (https://gitlab.com/cznic/sqlite/-/issues/new) with any info you have it would be very appreciated.

Note that this is not “go port”. It’s C transpiled to go with their custom C compiler, and it’s transpiled differently for each architecture (as it’s using unsafe go heavily)

I look at it like a neat exercise but not really something to use in production. (I actually use it in tests though.)

It is definitely neat, but I think it's more than an exercise. I believe he runs it through the extensive SQLite test suite (I've seen that before, though I can't see where that's stated now).

My understanding was that the sqlite test suite is closed source, and kept under pretty tight grip as it represents their competitive advantage to anyone trying to fork sqlite.

Pretty sure the lead dev said something along those lines when he did an episode of Corecursive?

The bits in https://gitlab.com/cznic/sqlite/-/blob/master/tcl_test.go run the full suite of SQLite TCL tests as part of a Go test. That's in turn run as part a CI setup with results at https://modern-c.appspot.com/-/builder/?importpath=modernc.o....

Those are not all the tests for SQLite: https://sqlite.org/testing.html#harnesses

Only the TCL and SQL Logic Test are publicly available; it's one of the ways that the SQLite developers get paid.

You are correct, I should have clarified that it's just the publicly-available tests. Still, those are pretty expansive.

i use it in production, at high volume and scale

it's got quirks but it's powerful and performs well

It's a really neat project. I did some simple benchmarks of it and it was between 10% slower to twice as slow. Which depending on your use case isn't that bad!

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

What makes it slower?

My guess is that over the years many optimizations have gone into SQLite that would take a lot of effort to catch up to.

It’s hard for Go to beat C in a straight benchmark - Go is garbage collected and allocations often have extra overhead due to the memory layout of interfaces.

In addition, a lot of optimizations (like loop unrolling) are left on the table to preserve Go's fast compile times.

Gccgo is the answer here.

Gccgo at its core may be fast but when it comes to how the go frontend interpret it to gcc ir that pales in comparison than just straight up optimizating the Go compiler

There’ll more opportunities for more optimizations since they move to SSA ir since 1.5 and in turn compile time will definitely go down

I was surprised with the announcement that Go's standard compiler had register allocation for function arguments[0] added to it. This seemed like table stakes for a very long time but it really illustrated just how Go's compiler is much, much simpler than most in many areas.

On one hand it makes you wonder how much could be squeezed out of Go and how many basic things they're still leaving out of the compiler. On the other it tells part of the story of why Go compiles fast with the backdrop that despite this lack of work by the compiler people use Go productively all day and deliver services they are happy with from a performance perspective.

[0] - https://go.dev/doc/go1.17#compiler (2021-08)

I wonder if they'll ever switch to a "normal = fast compile, low optimisation; release = slow compile, high optimisation" model like Rust. Although I suppose other languages already have the "high optimisation" parts covered (rust, julia, python+numpy, etc.) which might make it a fool's errand.

It goes to show the impact of optimizing from top-down instead of bottom-up can have. Go was designed with performance in mind ("mechanical sympathy") as opposed to most other GC languages for which performance was an afterthought.

If you get high-level language constructs right, you'll get great performance by default, even with a simple compiler:

- structs instead of objects

- value types

- exposed pointers

- straightforward allocation semantics

Most compilers out there are busy fighting bad language designs. There's only so much a compiler can do when faced with fat objects hidden behind a spider web of pointers. So they have to do these microoptimizations to claw back at least some performance.

It is a matter of skill, Go can do manual memory management and stack allocation just as well.

Also, Go barely optimizes code, that’s how it compiles as fast.

Not only Go compiler is not as good at optimisation as C/C++/Rust/Zig/D compilers, but also Go FFI to C has significant overhead, which means there is often not much point in using low-level Linux APIs for performance advantage. Same problem like with Java. And in a database system you quite likely want a fine degree of control with non-standard stuff.

However, it is absolutely plausible for Go-calling-Go to beat Go-calling-C-via-CGo, since CGo has plenty of overhead.

Other than what the other responders said, since it uses a C to Go compiler, I'd imagine the Go code that's generated isn't as fast as handwritten Go code could be.

C -> GPT -> Go -> Edit -> commit?

-> fail to compile / crash at startup

I raised this question on twitter awhile back and the response that I got was that (for read-only tasks) the connection string might ameliorate this. I haven't been able to test it, though:

https://twitter.com/frioux/status/1483235674228596739

at segment we benchmarked https://github.com/segmentio/ctlstore against this driver. We saw about a 50% hit to read performance, so we didn't move forward with it, but the improvements in service build times were really appealing.

Really tho, how much is build time vs runtime? Maybe is just work small projects but I rarely care about build time. Am I missing something?

If you have a lot of people and a lot of different builds it can become increasingly significant. It wasn't in our case, but it felt like it could have become so. And a lot of services wouldn't really notice too much if the occasional read from disk was slower, given that the nature of the data was control data.

Honestly, on a non-toy project, build times with cgo are _brutal_. I agree with you usually, but when a build time on a beefy computer switches from under a second to >1min you notice it.

Linters and IDEs get slow when they check for errors, tests run slow, feedback drags, and all your workflows that took advantage of Go's fast compile times are now long enough that your flow and mental context disappear.

I'm way more lenient with other languages since the tooling and ecosystem are built around long build times. Your workflows compensate. But Go's tooling and ecosystem assume it compiles fast and treat things more like a scripting language. When that expectation is violated it hurts and everything feels like it's broken.

Why would you have to recompile sqlite every time?

I guess you just need to compile the .a once and then just reuse it?

If you're rebuilding it every single time, your build is set up wrong.

In my experience, encapsulating the access to sqlite in a go package helps a lot with avoiding recompilation of the c source, which indeed is brutally slow. It acutally seems to be way slower than compiling with gcc from the command line. Anyone knows why this is the case?

I’d go so far as to say it’s completely acceptable for 90+% of use cases.

I should mention that if you use this, somebody will eventually discover this line of code and be unhappy.

https://gitlab.com/cznic/libc/-/blob/v1.22.2/libc_openbsd_am...

line 213 if you’re on mobile

Don't feel bad, GitLab's line navigation thing doesn't work reliably on desktop, either. I guess they put that on the list of the other 15,000 issues that are "on the backlog"

I dunno, works ideally for me on both mobile and desktop, both Firefox.

openbsd/amd64 is not a supported target. Here's the list: https://pkg.go.dev/modernc.org/sqlite#hdr-Supported_platform...

I've use this. Slightly slower, but for my use case that was fine (a small blog) and immeasurably better doing away with all the CGO hassle. Especially when I might cross compile to an Arm architecture, or some docker container that uses a different approach to the c libraries (like Alpine vs Ubuntu Server or whatever) it was always a hassle, and thats all gone away now.

Always seemed silly that one of the most popular storage technologies couldn't be accessed with pure Go.

>Always seemed silly that one of the most popular storage technologies couldn't be accessed with pure Go.

I don't know why Google didn't pay someone to do this or do it themselves. Surely they use Go and sqlite together.

You’re overestimating the influence Google as a cooperation has on Go, you think they’re gonna pay for what they’re not gonna use ?

> you think they’re gonna pay for what they’re not gonna use

This is it. They have internal datastores that they use for nearly everything. They might be using SQLite somewhere for something, but I really doubt this would even land near the bottom of their priority list.

Why would someone at google ever use sqlite?

Chrome and Android make heavy use of SQLite. Not that Golang is very relevant to those projects

Why wouldn't they? Are you implying that, over the course of decades of Google's existence and thousands of software projects, not once has there ever had the need for a lightweight SQL store that can be embedded with a server or CLI tool?

Your comment truly does SQLite a disservice. SQLite is probably one of the most impressive pieces of software to have ever graced this earth.

Google's practice is that every service they build is built that way that it can scale to millions of users. On servers i don't think they would use sqlite unlike in android. If i build stuff i usually start with sqlite.

It's much more that servers are ephemeral, so the DBs are as a service from another team.

Would you run sqlite in kubernetes? (if so... whyyyyyyy?)

Pretty much any database is NOT written in Go, so what's the difference?

Other databases (Postgres, MySQL, etc.) already have clients written in pure Go. SQLite does not have a client/server architecture, so a pure Go version of it requires porting (or transpiling) the entire engine to Go.

Etcd, CockroachDB, InfluxDB, Consul. There are more.

Also Bolt is pure go which is a highly performant KV store

I tested that for a while but it seemed to suffer lock contention with a lot of go routines trying to use one database at the same time.

Why would you want to do this? SQLLITE is so stable. Why would you switch to some port that will be dead in a year?

The project converts the SQLite C code and has been kept very up to date, so it is far more than a one-off port. The reason is to avoid dealing with CGO in your project, which is necessary with other drivers such as mattn/go-sqlite3.

Implementation decisions of Go have various advantages in terms of control and memory use but they make linking to binary artefacts problematic. Not complicated, but it impacts the toll chain’s flexibility drastically. The FFI is also extremely slow.

That is why the go ecosystem tends to reimplement everything in go.

C has no telemetry

Each call out to a C library from Go locks an OS thread rather than participating in the go routine scheduling. If stuff happens that makes those threads slow or have some issue, they just sit here. I had a server with C Kafka libraries and each time the network glitched the CPU would spike up wards and not decline. With pure go the scheduler is able to just swap out and ignore code that is stuck some where (unless it were in a busy loop).

With this SQLite implementation I can stick it in my server (as a background, near real time mirror of memory state to disk) without worrying that the main loop will get clogged or my real time path will be hurt.

I use glabarez’ wrapper which makes it have an API like other Go databases: https://github.com/glebarez/sqlite/ and hey have been very responsive to issues I raised.

I have been running load tests against it for a few weeks, and it is quite solid.

Worth noting that busy loops are handled as well since 1.18.

Doesn't Go runtime offer anything like spawn_blocking in Rust async runtimes? Spawn_blocking allows to run any blocking operation without blocking the main executor threads, by spawning blocking operation into a separate thread-pool. This way there is no reason to worry "that the main loop will get clogged or my real time path will be hurt."

I've been using this for one of my personal projects (https://dmd.tanna.dev) to simplify the cross-compilation process and so far it's been very good.

When I noticed the SHA3 extension was missing (https://gitlab.com/cznic/sqlite/-/issues/139) it was noted it could be very easily patched client-side which is handy, as well as adding functionality into the core library to handle it.

It is really a cool project. We are using it as a default driver in PocketBase and although it is not a "drop-in" replacement of the CGO alternative `mattn/go-sqlite3` (different dsn format, some differences in the error messages, etc.), with a small abstraction it works fine for most cases and greatly simplify cross compilation.

Performance wise I haven't done intensive benchmark tests yet, but from my local experiments last year it performed ~1.5-2x slower than the CGO version for some queries (it is especially noticeable with LIKE expressions on large string data), but as mentioned previously, for most use cases it is already good enough.

Here are the most recent benchmarks I could find (I think from one of the maintainers of the lib) comparing the CGO version and the pure Go port - https://docs.google.com/spreadsheets/d/1YOP1D_ZhuR-ednQhTH6S...

How does this compare to https://github.com/glebarez/go-sqlite ?

Using that for https://www.octobench.com/ and I'm very happy.

It looks like your library is using some of the plumbing from the parent. I can't tell what benefit it adds on top of it because the parent is also compatible with the Go standard library sql/driver API.

Thanks!

What’s the point of something like this instead of just using SQLite? Seriously, “foo but in bar!” is not exactly the sort of thing that solves real problems for most people.

This isn’t reinventing the wheel in another language. This is a SQLite driver/implementation that is an alternative to the main one which relies on CGO which is annoying for development in some cases. Making this pure go makes compilation easier.

CGO is a real problem for many people. Due to how Go handles its async-by-default concurrency model, every call to an FFI library requires setting up a stack and marshaling data. It ends up being rather expensive. So the "without CGo" stuff is about retaining the fast performance people expect of these highly optimized C libraries.

How does it really solve the FFI issue? Now instead of an FFI boundary between the app and the database, you have an FFI boundary between the database (now implemented in Go) and the operating system C API. And the database quite likely performs multiple calls into the C API per each user app query, so it might be in fact worse.

Interactions with the OS are managed by the go runtime, and on Linux it does direct syscalls (except when it tries to use vdso).

Every program needs to do syscalls at some point, so that’s built in. It is not “in fact worse”.

That applies only to the stuff exposed by the go stdlib, which is the lowest common denominator between various systems. Databases often use many system specific, advanced calls, to gain more control.

What exactly do you think "sqlite" is? By the way you asked the question, it seems like you're assuming it's a separate database that you execute alongside your application, similarly to postgres/mariadb/etc. That's not the case though.

I’ve owned the SQLite integration in a major OS, I know quite well what it is. (And I’m also quite familiar with the difference between static and dynamic/shared libraries too, thanks.)

Rephrasing my question, why wouldn’t you just use the SQLite library from Go, instead of cross-compiling it from C to Go? It sounds like the non-enthusiast “reimplement everything in my favorite language” answer is that Go’s FFI is a pain, even for C.

What approach is used here? WebAssembly (or something similar)?

I see a bunch of huge Go files with architecture names.

It's hard to find actual info on this other than Reddit comments but the author wrote a C to Go compiler and then ran it on the SQLite C code base to get a SQLite Go code base. Which means you don't need CGo to embed SQLite if you use that version.

https://gitlab.com/cznic/ccgo

Edit: Look around the source for "ccgo". Like https://gitlab.com/cznic/sqlite/-/blob/master/doc.go#L202 and look for "ccgo" in the closed issues.

Wow! It consumes C ASTs and produces Go code? That is much more impressive than what I had suspected (and what I would have done).

JFYI a Wasm approach here

https://github.com/ncruces/go-sqlite3

This for sure isn’t meant for performance critical services, best when it comes to appengine

Wow, I sure wish I had this a couple of years ago!

I was just looking for something like this.

i used it for a bit but was constantly getting transaction lock errors. i even added manual mutex for all transactions since it natively does not have exclusive transactions but even that did not worked. there is not a single good sqlite library for go out there. period. waste of time fiddling with it.

Wait, so I can use sqlite and Go on Windows (yeah, I know) without gcc!?

Yes.

Reply on news.ycombinator.com