11 ms·

SQLite in Go, with and Without Cgo

150 points by ngaut 4y ago

mattrighetti 4y ago
With modernc version there are also a lot less compiling hurdles for niche devices, I have a bunch of apps that use the modernc version because it was the easiest solution to compile the binary for a 32-bit ARM device like the Raspberry Pi.
- ainar-g 4y ago
  Yep! This is pretty much the main reason I don't use SQLite in Go unless I know that the project won't ever be compiled on anything other than what the devs and the server run. Simple and easy cross-compilation is one of Go's often forgotten selling points, and depending on how you want to use SQLite, using CGo may or may not be a good solution for you.
- cpuguy83 4y ago
  32-bit pi is not a niche device. - Load up Debian - install cross-build-essential - dpkg --add-architecture armhf - install the lib you want to link - set "CC" to the armhf compiler - go build
Cthulhu_ 4y ago
TL;DR this article compares the performance of mattn/sqlite3 (a wrapper around sqlite's C code, requiring `cgo` to compile and embed) with modernc.org/sqlite, an automatic conversion of C to Go code, and finds that the "native" Go code is half as fast as the cgo version. Which brings us to the obvious question, what improvements can be made? What if the Go code was handcrafted instead of automatically generated? https://gitlab.com/cznic/sqlite/-/issues/39 https://gitlab.com/cznic/sqlite/-/issues/39 is one issue from a year ago where they talk about insert performance and some optimizations as well, so I believe the author is aware of it. Anyway, I hope a native Go version does pick up (for everything currently depending on cgo for that matter), it makes cross-compilation a lot easier.
- tgv 4y ago
  I don't hope someone is going to attempt to rewrite sqlite in go. The C version is very well tested. A rewrite can only add bugs. Automatic transcription is the best way forward, the performance hit (when you want to avoid cgo) be damned. I do suppose that the group_by performance can be improved by working on the c->go compiler, though. I think that's a more worthwhile effort than rewriting sqlite.
  - xushiweizh 4y ago
    I agree. And I work at https://github.com/goplus/c2go https://github.com/goplus/c2go recently. Its goal is converting any C project into Go without any human intervention and keeping performance close to C.
  - GordonS 4y ago
    > Automatic transcription is the best way forward, the performance hit (when you want to avoid cgo) be damned But if performance is worse than the C wrapper, there is surely no point whatsoever for such a native version to exist? Isn't the whole point of a native Go/whatever version to avoid the interop penalty? > The C version is very well tested Couldn't the same test suite be used for a Go/whatever rewrite, or at least be ported to Go too?
    - tgv 4y ago
      > there is surely no point whatsoever for such a native version to exist If you want to avoid cgo. There are two reasons for that: easy cross-compilation, and https://dave.cheney.net/2016/01/18/cgo-is-not-go https://dave.cheney.net/2016/01/18/cgo-is-not-go > Couldn't the same test suite be used for a Go/whatever rewrite, or at least be ported to Go too? And then port each and every update as well? The only use of such a rewrite is performance improvement in go programs. Surely improving the c-to-go compiler is a better investment of effort? It would benefit other projects too. Rewriting sqlite in go will only lead to less effort on the c-to-go compiler.
    - hexmiles 4y ago
      > Isn't the whole point of a native Go/whatever version to avoid the interop penalty? CGO require a c compiler (not always easy available) and make cross-compilation harder
jks 4y ago
Then there's crawshaw.io/sqlite (https://github.com/crawshaw/sqlite https://github.com/crawshaw/sqlite) which has some extra features.
- tgv 4y ago
  That still requires cgo, I think. Just open sqlite.go. The repo even has a .c file.
  - jrockway 4y ago
    The pure go fork is https://github.com/zombiezen/go-sqlite https://github.com/zombiezen/go-sqlite.
newby3 4y ago
Can this be generalized to say, as a rule of thumb, that an automatic translation of code from C to go produces code that is twice as slow?, are there other projects in which the automatic translation performance is divided by 2?
- alophawen 4y ago
  No. The real insight is "it depends".
- josefx 4y ago
  > that an automatic translation of code from C to go produces code that is twice as slow? try several thousand times, twice as slow was the best case measured. The group by performance is catastrophic.
- tedunangst 4y ago
  That's approximately the same performance hit the go compiler itself experienced switching from c to go.
ricardobeat 4y ago
I don't buy the 'cgo is not go' mantra. The original argument was that reimplementing everything in Go is better and avoids a bunch of trade-offs, mainly: more complicated and slower builds, loss of cross-compilation, tooling. But it completely ignores the real cost of maintaining software, even if 'just a translation'. Having the same underlying libraries interoperating with Go, Node, Java, etc is a massive advantage, gives you predictable performance, memory usage, and known reliability regardless of the host language. Who would ever port and maintain a beast such as a libvips or IM port and all the 25 other image libraries they depend on? What really should be addressed is precisely that pain, so users of modules using CGO don't have to worry about it. Fix it, not abandon it.
- rob74 4y ago
  I agree with everything you write, but the opposition to "too much cgo" has another reason too: what's the point of using a memory safe language if you tie it to a large body of code that is not memory safe? Rust applications using C libraries have this issue too, although the performance penalty of calling C from Rust is less than for Go. Of course, there are still valid reasons for using cgo. But if you are building a library, you should ask yourself the "cgo or not cgo" question even more seriously, because you will be forcing everybody who uses your library to use cgo too...
  - srer 4y ago
    Is it memory safe? As I understood it you lose that as soon as you start using the namesake keyword, "go".
    - matthewmacleod 4y ago
      Yes – Go is generally memory safe, with the exception of the `unsafe` package which is (obviously) not. Outside of this, there is no access to raw pointers or pointer arithmetic. The use of goroutines does not affect memory safety. None of this means that you can't make an absolute mess of concurrency, but that's not a memory safety concern.
cube2222 4y ago
Mandatory plug of this article[0] describing how to use the Zig language toolchain to get easy cross-compilation with CGO. It's really cool because cross-compilation is, imo, the most painful part of CGO - right after being incompatible with the whole runtime model, being blocking, and being hard to profile with Go profiling tools. [0]: https://dev.to/kristoff/zig-makes-go-cross-compilation-just-work-29ho https://dev.to/kristoff/zig-makes-go-cross-compilation-just-... Disclaimer: Not affiliated with the author in any way.
- makeworld 4y ago
  Wow this is awesome, thanks! I'm working on a Go project that uses sqlite right now, and I thought I was going to have to use xgo[1], which is cool and all, but it's like an 8 GB docker container, and I'd still be worried about glibc issues. [1]: https://github.com/techknowlogick/xgo https://github.com/techknowlogick/xgo
  - pstuart 4y ago
    The default go sqlite driver is https://github.com/mattn/go-sqlite3 https://github.com/mattn/go-sqlite3, which is quite lovely, but I ran into issues with concurrency on read only databases. I'm now using https://github.com/crawshaw/sqlite https://github.com/crawshaw/sqlite and it seems to address those issues (but I haven't gotten around to setting up a proper test to confirm). It may be worth perusing if you do run into performance problems. It does come with the caveat of not being a database/sql driver though.
    - mattikus 4y ago
      Also of note is https://github.com/tailscale/sqlite https://github.com/tailscale/sqlite, also written by David Crawshaw. It _does_ provide a database/sql driver.
      - pstuart 4y ago
        The current README does not encourage adoption but definitely worth tracking.
swah 4y ago
Off topic: I like when HN "follows" a topic (sqlite in this case) for a few days.
- bsaul 4y ago
  i've always wondered how that worked. Is it simply a statistical side-effect of people having read about one thing will post & like other posts about the same topic ?
  - gavinray 4y ago
    In this case, the author was messing around with trying to get DuckDB & SQLite to build/link statically in Go yesterday after the post about it, it's probably related to that: https://twitter.com/phil_eaton/status/1524861485670973458 https://twitter.com/phil_eaton/status/1524861485670973458
  - S1ngleM4lt 4y ago
    My guess was always that a post on a particular topic might make someone interested to learn more about that topic. If that person then finds a related link interesting, they are more likely to share it if the original post did well enough to indicate that other members of the community are interested in the topic. In some ways it feels a bit like the meme culture of other communities, where users are more likely to post items that are related to previous successful posts. The main interesting thing to me is when the “topic de jour” has very little to do with what I’d normally associate with HN (language genealogy, Neanderthals, and ancient currencies are some examples from recent memory).
xushiweizh 4y ago
Yet another way to convert C to Go: https://github.com/goplus/c2go https://github.com/goplus/c2go
proofrock 4y ago
I switched from mattn to modernc for my ws4sqlite project and could not measure a difference; really interesting though. Of course I made the switch out of cross-compoling consideration, not for the performances.
srer 4y ago
Wanting SQLite in Go touches on something that I think is quite a waste in modern Go circles, but happens everywhere to varying degrees. There's often (for instance, in Go projects wanting to avoid cgo) a desire for everything to be in the single source language - Go. In what resembles NIH syndrome, there will be clones of existing libraries, offering little over the original except being "Written in Go". From experience this often makes for more bugs, as the Go version is commonly much younger and lessor used than the existing non-Go library. The Python world does it a lot less, perhaps the slowness of Python helps encourage using non-python libraries in Python modules. But that sure does making building and distributing Python projects "fun". What I'm trying to say is that: A world where every language community has it's own SQLite project because the communities shun code written in other languages just feels like a profound waste.
- earthboundkid 4y ago
  It created a lot of churn, but I think it has been net positive for Go because they now have a huge ecosystem of stuff that can be installed without the black hole of C/C++ dependency installation.
  - pjmlp 4y ago
    Dependencies are sorted out when using Conan or vcpkg. However the security aspect is much more relevant.
- AtlasBarfed 4y ago
  Well, sqllite is almost as valuable as a FILE FORMAT as it would be as an application, if the "store config as sqllite" crowd would contend. Thus language-native versions of sqllite really can be viewed as language-specific file format readers/writers like any other JSON/YAML library.
- throw_m239339 4y ago
  I guess the reason is that it is easier to cross compile by keeping everything in go? I have no knowledge of Sqlite cross compilation (on Linux targeting windows for instance) but I guess it's also possible but it makes build process a little more complex. Node.js can take advantage of WASM which is pretty handy in some cases.
numbsafari 4y ago
I have absolutely zero problems using mattn/sqlite3 while cross compiling from Linux to windows. Zero. I’m not some deeply knowledgeable go-internals core team committer. I’m a pretty mediocre go dev who spends most days fighting silly fires in the 6-7 other languages any project ends up having to deal with. Zero problems with cross compiling the cgo version of SQLite for 3 years now.
draxil 4y ago
I assume this is nice if you have a strong benefit from not going into the cgo black box. No real surprise that auto-transpiled code isn't optimal.
svnpenn 4y ago
Author did not address the other project by the same author: modernc.org/ql https://gitlab.com/cznic/sqlite/-/issues/38 https://gitlab.com/cznic/sqlite/-/issues/38
forgotAgain 4y ago
Is the trade off worth it? Is removing the pain of building with Cgo worth giving up the dependability and support of C based SQLite? I don't see how but I'm curious to what others think.
orcasauce 4y ago
Considering the great lengths the SQLite source goes through to validate correctness (code that rides in critical airplane systems gets no free lunch), I'd be immediately concerned a "SQLite in ______" lacks remotely the same degree of vigor in ensure implementation correctness. The overhead in using cgo feels like a small price to not worry "SQLite in Go" will remain maintained to the same degree the base SQLite project is.
- nullbytesmatter 4y ago
  Not quite. Cgo makes static linking not possible, reducing cross platform ability.
- koeng 4y ago
  They have `0 errors out of 928271 tests`. The code is mostly auto-generated if I recall, so it's essentially just using translated C from the original SQLite project.
  - Spivak 4y ago
    Yeah, this is the benefit of having such a comprehensive test suite. If your implementation passes all the tests then ship it.
staunch 4y ago
The thing that scared me off this option is the that the incredible SQLite test suite can't be run against the Go translation, IIRC. But I also really don't like using cgo, for all the reasons stated elsewhere. So these days I generally avoid SQLite in my Go programs, which is unfortunate. What I think I'd like best of all is if there was an out-of-process SQLite network daemon mode that could run over Unix sockets. So one could run `sqlite3 daemon unix:///run/sqlite.sock` and then communicate with it like a traditional SQL database, but with most of the simplicity and power of SQLite intact. I know there are at least some attempts at creating network daemons for SQLite, but my impression was that none of them seemed up to the (very high) bar set by SQLite itself. Also, there's no SQLite network driver for Go. Please someone correct me if I'm wrong about these though.
- cristoperb 4y ago
  I've not used it, but sqinn is one sqlite server meant specifically to be used by languages without c calling conventions: https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn-go https://github.com/cvilsmeier/sqinn-go
  - staunch 4y ago
    Thanks, that seems like an interesting approach.
- jrockway 4y ago
  I think the author of modernc.org/sqlite also ported the test suite. They wrote https://gitlab.com/cznic/tcl https://gitlab.com/cznic/tcl to run the TCL-based tests, for example.
bborud 4y ago
I have been using SQLite in Go projects for a few years now. During early stages of development I always start with SQLite as the main database, then when the project matures, I usually add support for PostgreSQL. (I usually make a Store interface which is application specific and doesn't even assume there is an SQL database underneath. Then I make "driver" packages for each storage system - be it PostgreSQL, SQLite, flat files, timeseries etc. I have only one set of unit tests that is then run against all drivers. And when I have a caching layer, I also run all the unit tests with or without caching. The cache is usually just an adapter that wraps a Store type. I maintain separate schemas and drivers for each "driver" because I have found that this is actually faster and easier than trying to make generic SQL drivers for instance.) However, I always keep the SQLite support and it is usually the default when you start up the application without explicitly specifying a database. This means that it is easy for other developers to do ad-hoc experiments or even create integration tests without having to fire up a database, which even when you are able to do it quickly, still takes time and effort. In production you usually want to point to a PostgreSQL (or other) database. Usually, but not always. I also use it extensively in unit tests (often creating and destroying in-memory databases hundreds of times during just a couple of seconds of tests). I run all my tests on every build while developing and then speed matters a lot. When testing with PostgreSQL I usually set a build tag that specifies that I want to run the tests against PostgreSQL as well. I always want to run all the database tests - I don't always need to run them against PostgreSQL (Actually, I made a quick hack called Drydock which takes care of creating a PostgreSQL instance and creates one database per test. This is experimental, but I've gotten a lot of use out of it: https://github.com/borud/drydock https://github.com/borud/drydock) The reason I do this is that it results in much quicker turnaround during the initial phase when the data model may go through several complete rewrites. The lack of friction is significant. SQLite has actually surprised me. I use it in a project where I routinely have tens of millions of rows in the biggest table. And it still performs well enough at well north of 100M rows. I wouldn't recommend it in production, but for a surprising number of systems you could if you wanted to. The transpiled SQLite is very interesting to me for two reasons. It makes cross compiling a lot less complex. I make extensive use of Go and SQLite on embedded ARM platforms and then you either have to choose between compiling on the target platform or mess around with C libraries. It also eliminates the need to do two stage Docker builds (which cuts down building Docker images from 50+ seconds to perhaps 4-5 seconds). The transpiled version is slower by quite a lot. I haven't done a systematic benchmark, but I noticed that a server that stores 30-40 datapoints per second went from 0.5% average CPU load to about 2% average CPU load. I'm not terribly worried about it, but it does mean that when I increase the influx of data I'm most likely going to hit a wall sooner. I'll be using the transpiled SQLite a lot more in the coming year and I'll be on the Gophers Slack so if anyone is interested in sharing experiences, discussing SQLite in Go, please don't be shy.
pcj-github 4y ago
Good to know the pure go one has acceptable performance for most workloads.
terinjokes 4y ago
If you do decide to use "modernc.org/sqlite" you should make it switchable with mattn's behind a build tag for those of us that prefer to use the system libsqlite3. I've noticed that the auto-converted package seems to have different behaviors in it's handling of concurrent updates, which makes me concerned.
- jrockway 4y ago
  I don't think you ever want to make the database backend switchable at compile time. Every database engine has slight semantic differences and it's crucial that your program handle that correctly. If there's a difference, like you mention, you want to tell the user to get the one that you've tested. Something that has caused me a lot of trouble in the past and is now on my "never do" list is using SQLite for tests against an app that runs Postgres in production. It's pretty similar, but passing tests easily equal failing production. (The last straw for me was SQLite considering "f" true, while Postgres considers it false.) Your suggestion is a little different than that, but the same principle applies. Test your app against the database you're going to use. If you must support two database engines, then you need a test matrix that runs tests against both.
  - terinjokes 4y ago
    For an application being developed professionally I agree. At least until you have an good enough reason to deviate from that course. I wrote my comment with the experience working with deploying open source software personally recently. They already had support for multiple database backends, with SQLite being one of them. They already had test suites, and while adding support for another SQLite implementation isn't free, it did only take a few minutes.

150 points by ngaut 4y ago

With modernc version there are also a lot less compiling hurdles for niche devices, I have a bunch of apps that use the modernc version because it was the easiest solution to compile the binary for a 32-bit ARM device like the Raspberry Pi.

ainar-g 4y ago
Yep! This is pretty much the main reason I don't use SQLite in Go unless I know that the project won't ever be compiled on anything other than what the devs and the server run. Simple and easy cross-compilation is one of Go's often forgotten selling points, and depending on how you want to use SQLite, using CGo may or may not be a good solution for you.
cpuguy83 4y ago
32-bit pi is not a niche device. - Load up Debian - install cross-build-essential - dpkg --add-architecture armhf - install the lib you want to link - set "CC" to the armhf compiler - go build

Cthulhu_ 4y ago

TL;DR this article compares the performance of mattn/sqlite3 (a wrapper around sqlite's C code, requiring `cgo` to compile and embed) with modernc.org/sqlite, an automatic conversion of C to Go code, and finds that the "native" Go code is half as fast as the cgo version. Which brings us to the obvious question, what improvements can be made? What if the Go code was handcrafted instead of automatically generated? https://gitlab.com/cznic/sqlite/-/issues/39 https://gitlab.com/cznic/sqlite/-/issues/39 is one issue from a year ago where they talk about insert performance and some optimizations as well, so I believe the author is aware of it. Anyway, I hope a native Go version does pick up (for everything currently depending on cgo for that matter), it makes cross-compilation a lot easier.

tgv 4y ago
I don't hope someone is going to attempt to rewrite sqlite in go. The C version is very well tested. A rewrite can only add bugs. Automatic transcription is the best way forward, the performance hit (when you want to avoid cgo) be damned. I do suppose that the group_by performance can be improved by working on the c->go compiler, though. I think that's a more worthwhile effort than rewriting sqlite.
- xushiweizh 4y ago
  I agree. And I work at https://github.com/goplus/c2go https://github.com/goplus/c2go recently. Its goal is converting any C project into Go without any human intervention and keeping performance close to C.
- GordonS 4y ago
  > Automatic transcription is the best way forward, the performance hit (when you want to avoid cgo) be damned But if performance is worse than the C wrapper, there is surely no point whatsoever for such a native version to exist? Isn't the whole point of a native Go/whatever version to avoid the interop penalty? > The C version is very well tested Couldn't the same test suite be used for a Go/whatever rewrite, or at least be ported to Go too?
  - tgv 4y ago
    > there is surely no point whatsoever for such a native version to exist If you want to avoid cgo. There are two reasons for that: easy cross-compilation, and https://dave.cheney.net/2016/01/18/cgo-is-not-go https://dave.cheney.net/2016/01/18/cgo-is-not-go > Couldn't the same test suite be used for a Go/whatever rewrite, or at least be ported to Go too? And then port each and every update as well? The only use of such a rewrite is performance improvement in go programs. Surely improving the c-to-go compiler is a better investment of effort? It would benefit other projects too. Rewriting sqlite in go will only lead to less effort on the c-to-go compiler.
  - hexmiles 4y ago
    > Isn't the whole point of a native Go/whatever version to avoid the interop penalty? CGO require a c compiler (not always easy available) and make cross-compilation harder

jks 4y ago

Then there's crawshaw.io/sqlite (https://github.com/crawshaw/sqlite https://github.com/crawshaw/sqlite) which has some extra features.

tgv 4y ago
That still requires cgo, I think. Just open sqlite.go. The repo even has a .c file.
- jrockway 4y ago
  The pure go fork is https://github.com/zombiezen/go-sqlite https://github.com/zombiezen/go-sqlite.

newby3 4y ago

Can this be generalized to say, as a rule of thumb, that an automatic translation of code from C to go produces code that is twice as slow?, are there other projects in which the automatic translation performance is divided by 2?

alophawen 4y ago
No. The real insight is "it depends".
josefx 4y ago
> that an automatic translation of code from C to go produces code that is twice as slow? try several thousand times, twice as slow was the best case measured. The group by performance is catastrophic.
tedunangst 4y ago
That's approximately the same performance hit the go compiler itself experienced switching from c to go.

ricardobeat 4y ago

I don't buy the 'cgo is not go' mantra. The original argument was that reimplementing everything in Go is better and avoids a bunch of trade-offs, mainly: more complicated and slower builds, loss of cross-compilation, tooling. But it completely ignores the real cost of maintaining software, even if 'just a translation'. Having the same underlying libraries interoperating with Go, Node, Java, etc is a massive advantage, gives you predictable performance, memory usage, and known reliability regardless of the host language. Who would ever port and maintain a beast such as a libvips or IM port and all the 25 other image libraries they depend on? What really should be addressed is precisely that pain, so users of modules using CGO don't have to worry about it. Fix it, not abandon it.

rob74 4y ago
I agree with everything you write, but the opposition to "too much cgo" has another reason too: what's the point of using a memory safe language if you tie it to a large body of code that is not memory safe? Rust applications using C libraries have this issue too, although the performance penalty of calling C from Rust is less than for Go. Of course, there are still valid reasons for using cgo. But if you are building a library, you should ask yourself the "cgo or not cgo" question even more seriously, because you will be forcing everybody who uses your library to use cgo too...
- srer 4y ago
  Is it memory safe? As I understood it you lose that as soon as you start using the namesake keyword, "go".
  - matthewmacleod 4y ago
    Yes – Go is generally memory safe, with the exception of the `unsafe` package which is (obviously) not. Outside of this, there is no access to raw pointers or pointer arithmetic. The use of goroutines does not affect memory safety. None of this means that you can't make an absolute mess of concurrency, but that's not a memory safety concern.

cube2222 4y ago

Mandatory plug of this article[0] describing how to use the Zig language toolchain to get easy cross-compilation with CGO. It's really cool because cross-compilation is, imo, the most painful part of CGO - right after being incompatible with the whole runtime model, being blocking, and being hard to profile with Go profiling tools. [0]: https://dev.to/kristoff/zig-makes-go-cross-compilation-just-work-29ho https://dev.to/kristoff/zig-makes-go-cross-compilation-just-... Disclaimer: Not affiliated with the author in any way.

makeworld 4y ago
Wow this is awesome, thanks! I'm working on a Go project that uses sqlite right now, and I thought I was going to have to use xgo[1], which is cool and all, but it's like an 8 GB docker container, and I'd still be worried about glibc issues. [1]: https://github.com/techknowlogick/xgo https://github.com/techknowlogick/xgo
- pstuart 4y ago
  The default go sqlite driver is https://github.com/mattn/go-sqlite3 https://github.com/mattn/go-sqlite3, which is quite lovely, but I ran into issues with concurrency on read only databases. I'm now using https://github.com/crawshaw/sqlite https://github.com/crawshaw/sqlite and it seems to address those issues (but I haven't gotten around to setting up a proper test to confirm). It may be worth perusing if you do run into performance problems. It does come with the caveat of not being a database/sql driver though.
  - mattikus 4y ago
    Also of note is https://github.com/tailscale/sqlite https://github.com/tailscale/sqlite, also written by David Crawshaw. It _does_ provide a database/sql driver.
    - pstuart 4y ago
      The current README does not encourage adoption but definitely worth tracking.

swah 4y ago

Off topic: I like when HN "follows" a topic (sqlite in this case) for a few days.

bsaul 4y ago
i've always wondered how that worked. Is it simply a statistical side-effect of people having read about one thing will post & like other posts about the same topic ?
- gavinray 4y ago
  In this case, the author was messing around with trying to get DuckDB & SQLite to build/link statically in Go yesterday after the post about it, it's probably related to that: https://twitter.com/phil_eaton/status/1524861485670973458 https://twitter.com/phil_eaton/status/1524861485670973458
- S1ngleM4lt 4y ago
  My guess was always that a post on a particular topic might make someone interested to learn more about that topic. If that person then finds a related link interesting, they are more likely to share it if the original post did well enough to indicate that other members of the community are interested in the topic. In some ways it feels a bit like the meme culture of other communities, where users are more likely to post items that are related to previous successful posts. The main interesting thing to me is when the “topic de jour” has very little to do with what I’d normally associate with HN (language genealogy, Neanderthals, and ancient currencies are some examples from recent memory).

xushiweizh 4y ago

Yet another way to convert C to Go: https://github.com/goplus/c2go https://github.com/goplus/c2go

proofrock 4y ago

I switched from mattn to modernc for my ws4sqlite project and could not measure a difference; really interesting though. Of course I made the switch out of cross-compoling consideration, not for the performances.

srer 4y ago

Wanting SQLite in Go touches on something that I think is quite a waste in modern Go circles, but happens everywhere to varying degrees. There's often (for instance, in Go projects wanting to avoid cgo) a desire for everything to be in the single source language - Go. In what resembles NIH syndrome, there will be clones of existing libraries, offering little over the original except being "Written in Go". From experience this often makes for more bugs, as the Go version is commonly much younger and lessor used than the existing non-Go library. The Python world does it a lot less, perhaps the slowness of Python helps encourage using non-python libraries in Python modules. But that sure does making building and distributing Python projects "fun". What I'm trying to say is that: A world where every language community has it's own SQLite project because the communities shun code written in other languages just feels like a profound waste.

earthboundkid 4y ago
It created a lot of churn, but I think it has been net positive for Go because they now have a huge ecosystem of stuff that can be installed without the black hole of C/C++ dependency installation.
- pjmlp 4y ago
  Dependencies are sorted out when using Conan or vcpkg. However the security aspect is much more relevant.
AtlasBarfed 4y ago
Well, sqllite is almost as valuable as a FILE FORMAT as it would be as an application, if the "store config as sqllite" crowd would contend. Thus language-native versions of sqllite really can be viewed as language-specific file format readers/writers like any other JSON/YAML library.
throw_m239339 4y ago
I guess the reason is that it is easier to cross compile by keeping everything in go? I have no knowledge of Sqlite cross compilation (on Linux targeting windows for instance) but I guess it's also possible but it makes build process a little more complex. Node.js can take advantage of WASM which is pretty handy in some cases.

numbsafari 4y ago

I have absolutely zero problems using mattn/sqlite3 while cross compiling from Linux to windows. Zero. I’m not some deeply knowledgeable go-internals core team committer. I’m a pretty mediocre go dev who spends most days fighting silly fires in the 6-7 other languages any project ends up having to deal with. Zero problems with cross compiling the cgo version of SQLite for 3 years now.

draxil 4y ago

I assume this is nice if you have a strong benefit from not going into the cgo black box. No real surprise that auto-transpiled code isn't optimal.

svnpenn 4y ago

Author did not address the other project by the same author: modernc.org/ql https://gitlab.com/cznic/sqlite/-/issues/38 https://gitlab.com/cznic/sqlite/-/issues/38

forgotAgain 4y ago

Is the trade off worth it? Is removing the pain of building with Cgo worth giving up the dependability and support of C based SQLite? I don't see how but I'm curious to what others think.

orcasauce 4y ago

Considering the great lengths the SQLite source goes through to validate correctness (code that rides in critical airplane systems gets no free lunch), I'd be immediately concerned a "SQLite in ______" lacks remotely the same degree of vigor in ensure implementation correctness. The overhead in using cgo feels like a small price to not worry "SQLite in Go" will remain maintained to the same degree the base SQLite project is.

nullbytesmatter 4y ago
Not quite. Cgo makes static linking not possible, reducing cross platform ability.
koeng 4y ago
They have `0 errors out of 928271 tests`. The code is mostly auto-generated if I recall, so it's essentially just using translated C from the original SQLite project.
- Spivak 4y ago
  Yeah, this is the benefit of having such a comprehensive test suite. If your implementation passes all the tests then ship it.

staunch 4y ago

The thing that scared me off this option is the that the incredible SQLite test suite can't be run against the Go translation, IIRC. But I also really don't like using cgo, for all the reasons stated elsewhere. So these days I generally avoid SQLite in my Go programs, which is unfortunate. What I think I'd like best of all is if there was an out-of-process SQLite network daemon mode that could run over Unix sockets. So one could run `sqlite3 daemon unix:///run/sqlite.sock` and then communicate with it like a traditional SQL database, but with most of the simplicity and power of SQLite intact. I know there are at least some attempts at creating network daemons for SQLite, but my impression was that none of them seemed up to the (very high) bar set by SQLite itself. Also, there's no SQLite network driver for Go. Please someone correct me if I'm wrong about these though.

cristoperb 4y ago
I've not used it, but sqinn is one sqlite server meant specifically to be used by languages without c calling conventions: https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn-go https://github.com/cvilsmeier/sqinn-go
- staunch 4y ago
  Thanks, that seems like an interesting approach.
jrockway 4y ago
I think the author of modernc.org/sqlite also ported the test suite. They wrote https://gitlab.com/cznic/tcl https://gitlab.com/cznic/tcl to run the TCL-based tests, for example.

bborud 4y ago

I have been using SQLite in Go projects for a few years now. During early stages of development I always start with SQLite as the main database, then when the project matures, I usually add support for PostgreSQL. (I usually make a Store interface which is application specific and doesn't even assume there is an SQL database underneath. Then I make "driver" packages for each storage system - be it PostgreSQL, SQLite, flat files, timeseries etc. I have only one set of unit tests that is then run against all drivers. And when I have a caching layer, I also run all the unit tests with or without caching. The cache is usually just an adapter that wraps a Store type. I maintain separate schemas and drivers for each "driver" because I have found that this is actually faster and easier than trying to make generic SQL drivers for instance.) However, I always keep the SQLite support and it is usually the default when you start up the application without explicitly specifying a database. This means that it is easy for other developers to do ad-hoc experiments or even create integration tests without having to fire up a database, which even when you are able to do it quickly, still takes time and effort. In production you usually want to point to a PostgreSQL (or other) database. Usually, but not always. I also use it extensively in unit tests (often creating and destroying in-memory databases hundreds of times during just a couple of seconds of tests). I run all my tests on every build while developing and then speed matters a lot. When testing with PostgreSQL I usually set a build tag that specifies that I want to run the tests against PostgreSQL as well. I always want to run all the database tests - I don't always need to run them against PostgreSQL (Actually, I made a quick hack called Drydock which takes care of creating a PostgreSQL instance and creates one database per test. This is experimental, but I've gotten a lot of use out of it: https://github.com/borud/drydock https://github.com/borud/drydock) The reason I do this is that it results in much quicker turnaround during the initial phase when the data model may go through several complete rewrites. The lack of friction is significant. SQLite has actually surprised me. I use it in a project where I routinely have tens of millions of rows in the biggest table. And it still performs well enough at well north of 100M rows. I wouldn't recommend it in production, but for a surprising number of systems you could if you wanted to. The transpiled SQLite is very interesting to me for two reasons. It makes cross compiling a lot less complex. I make extensive use of Go and SQLite on embedded ARM platforms and then you either have to choose between compiling on the target platform or mess around with C libraries. It also eliminates the need to do two stage Docker builds (which cuts down building Docker images from 50+ seconds to perhaps 4-5 seconds). The transpiled version is slower by quite a lot. I haven't done a systematic benchmark, but I noticed that a server that stores 30-40 datapoints per second went from 0.5% average CPU load to about 2% average CPU load. I'm not terribly worried about it, but it does mean that when I increase the influx of data I'm most likely going to hit a wall sooner. I'll be using the transpiled SQLite a lot more in the coming year and I'll be on the Gophers Slack so if anyone is interested in sharing experiences, discussing SQLite in Go, please don't be shy.

pcj-github 4y ago

Good to know the pure go one has acceptable performance for most workloads.

terinjokes 4y ago

If you do decide to use "modernc.org/sqlite" you should make it switchable with mattn's behind a build tag for those of us that prefer to use the system libsqlite3. I've noticed that the auto-converted package seems to have different behaviors in it's handling of concurrent updates, which makes me concerned.

jrockway 4y ago
I don't think you ever want to make the database backend switchable at compile time. Every database engine has slight semantic differences and it's crucial that your program handle that correctly. If there's a difference, like you mention, you want to tell the user to get the one that you've tested. Something that has caused me a lot of trouble in the past and is now on my "never do" list is using SQLite for tests against an app that runs Postgres in production. It's pretty similar, but passing tests easily equal failing production. (The last straw for me was SQLite considering "f" true, while Postgres considers it false.) Your suggestion is a little different than that, but the same principle applies. Test your app against the database you're going to use. If you must support two database engines, then you need a test matrix that runs tests against both.
- terinjokes 4y ago
  For an application being developed professionally I agree. At least until you have an good enough reason to deviate from that course. I wrote my comment with the experience working with deploying open source software personally recently. They already had support for multiple database backends, with SQLite being one of them. They already had test suites, and while adding support for another SQLite implementation isn't free, it did only take a few minutes.