8 ms·

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

113 points by cvilsmeier 3y ago

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
    > 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 https://github.com/cvilsmeier/sqinn-go-bench
    - guappa 3y ago
      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…
      - tgv 3y ago
        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.
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.
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

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
  > 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 https://github.com/cvilsmeier/sqinn-go-bench
  - guappa 3y ago
    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…
    - tgv 3y ago
      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.

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.

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]