6 ms·

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

113 points by cvilsmeier 3y ago

eatonphil 3y ago
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://gitlab.com/cznic/sqlite https://datastation.multiprocess.io/blog/2022-05-12-sqlite-in-go-with-and-without-cgo.html https://datastation.multiprocess.io/blog/2022-05-12-sqlite-i...
- scosman 3y ago
  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).
  - llimllib 3y ago
    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-os-4cic https://zig.news/kristoff/building-sqlite-with-cgo-for-every...
    - tptacek 3y ago
      Or with musl-cross: https://github.com/FiloSottile/homebrew-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.
    - arp242 3y ago
      And Zig only covers a pretty limited range of platforms to start with.
  - infogulch 3y ago
    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.
    - kunley 3y ago
      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.
    - scosman 3y ago
      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.
      - scosman 3y ago
        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.
    - pram 3y ago
      Didn't it? Isn't that what 5c, 6c, and 8c were.
    - oefrha 3y ago
      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 https://github.com/mattn/go-sqlite3/issues/1164
  - parhamn 3y ago
    > 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.
    - Groxx 3y ago
      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.
      - irusensei 3y ago
        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.
        ncruces 3y ago
        If you still have that itch to scratch, you can try: https://github.com/ncruces/go-sqlite3 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.
        llimllib 3y ago
        Very neat! Any idea how its performance compares to the modernc port?
  - cvilsmeier 3y ago
    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 https://github.com/cvilsmeier/sqinn-go-bench
    - eatonphil 3y ago
      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 https://github.com/eatonphil/gosqlite for example. Edit: Nevermind, you did include crawshaw (which doesn't use database/sql) in your benchmark!
- hamandcheese 3y ago
  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?
  - ncruces 3y ago
    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 https://github.com/ebitengine/purego
- kunley 3y ago
  Yep, confirming that cznic's pure-Go modernc.org/sqlite works great.
- tedunangst 3y ago
  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.
  - ncruces 3y ago
    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 https://github.com/ncruces/go-sqlite3/tree/main
    - tedunangst 3y ago
      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_arm64.go?ref_type=heads https://gitlab.com/cznic/libc/-/blob/master/musl_openbsd_arm...
      - ncruces 3y ago
        It is slower. The WASM runtime https://wazero.io 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.
  - eatonphil 3y ago
    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.
    - tedunangst 3y ago
- deleted 3y ago
  [deleted]
- JodieBenitez 3y ago
  This is so cool... I see it has FTS5 support too. Now is the time to rewrite some of my apps into independent binaries
tgv 3y ago
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.
- deleted 3y ago
  [deleted]
- guappa 3y ago
  Wow, that sounds incredibly inefficient!
  - Animats 3y ago
    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.
    - cvilsmeier 3y ago
      > 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.
      - sureglymop 3y ago
        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.
  - cvilsmeier 3y ago
twodave 3y ago
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.
- cvilsmeier 3y ago
  Thank you! Nice to hear :-) Use the code any way you want, it's unlicensed.
  - nighmi 3y ago
    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-open-source-scenarios.html https://www.synopsys.com/blogs/software-security/unlicensed-...
    - thde 3y ago
      The project uses The Unlicense: https://github.com/cvilsmeier/sqinn-go/blob/master/LICENSE https://github.com/cvilsmeier/sqinn-go/blob/master/LICENSE
38 3y ago
[flagged]
tw1984 3y ago
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.
- deleted 3y ago
  [deleted]

113 points by cvilsmeier 3y ago

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://gitlab.com/cznic/sqlite https://datastation.multiprocess.io/blog/2022-05-12-sqlite-in-go-with-and-without-cgo.html https://datastation.multiprocess.io/blog/2022-05-12-sqlite-i...

scosman 3y ago
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).
- llimllib 3y ago
  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-os-4cic https://zig.news/kristoff/building-sqlite-with-cgo-for-every...
  - tptacek 3y ago
    Or with musl-cross: https://github.com/FiloSottile/homebrew-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.
  - arp242 3y ago
    And Zig only covers a pretty limited range of platforms to start with.
- infogulch 3y ago
  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.
  - kunley 3y ago
    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.
  - scosman 3y ago
    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.
    - scosman 3y ago
      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.
  - pram 3y ago
    Didn't it? Isn't that what 5c, 6c, and 8c were.
  - oefrha 3y ago
    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 https://github.com/mattn/go-sqlite3/issues/1164
- parhamn 3y ago
  > 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.
  - Groxx 3y ago
    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.
    - irusensei 3y ago
      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.
      - ncruces 3y ago
        If you still have that itch to scratch, you can try: https://github.com/ncruces/go-sqlite3 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.
        llimllib 3y ago
        Very neat! Any idea how its performance compares to the modernc port?
- cvilsmeier 3y ago
  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 https://github.com/cvilsmeier/sqinn-go-bench
  - eatonphil 3y ago
    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 https://github.com/eatonphil/gosqlite for example. Edit: Nevermind, you did include crawshaw (which doesn't use database/sql) in your benchmark!
hamandcheese 3y ago
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?
- ncruces 3y ago
  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 https://github.com/ebitengine/purego
kunley 3y ago
Yep, confirming that cznic's pure-Go modernc.org/sqlite works great.
tedunangst 3y ago
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.
- ncruces 3y ago
  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 https://github.com/ncruces/go-sqlite3/tree/main
  - tedunangst 3y ago
    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_arm64.go?ref_type=heads https://gitlab.com/cznic/libc/-/blob/master/musl_openbsd_arm...
    - ncruces 3y ago
      It is slower. The WASM runtime https://wazero.io 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.
- eatonphil 3y ago
  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.
  - tedunangst 3y ago
deleted 3y ago
[deleted]
JodieBenitez 3y ago
This is so cool... I see it has FTS5 support too. Now is the time to rewrite some of my apps into independent binaries

tgv 3y ago

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.

deleted 3y ago
[deleted]
guappa 3y ago
Wow, that sounds incredibly inefficient!
- Animats 3y ago
  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.
  - cvilsmeier 3y ago
    > 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.
    - sureglymop 3y ago
      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.
- cvilsmeier 3y ago

twodave 3y ago

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.

cvilsmeier 3y ago
Thank you! Nice to hear :-) Use the code any way you want, it's unlicensed.
- nighmi 3y ago
  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-open-source-scenarios.html https://www.synopsys.com/blogs/software-security/unlicensed-...
  - thde 3y ago
    The project uses The Unlicense: https://github.com/cvilsmeier/sqinn-go/blob/master/LICENSE https://github.com/cvilsmeier/sqinn-go/blob/master/LICENSE

38 3y ago

[flagged]

tw1984 3y ago

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.

deleted 3y ago
[deleted]