3 ms·

I love to see that more projects are using SQLite as their main database. One thing that I always wondered though: does anyone knows a big project/service that

I love to see that more projects are using SQLite as their main database. One thing that I always wondered though: does anyone knows a big project/service that uses Golang and is backed by SQLite? This because SQLite would require CGO and CGO generally adds extra complexities and performance costs. I wonder how big Golang applications fare with this.

mholt 5y ago
Not a "big project/service" but a Go project that uses Sqlite is one of my own, Timeliner[1] and its successor, Timelinize[2] (still in development). Yeah the cgo dependency kinda sucks but you don't feel it in code, just compilation. And it easily manages Timeline databases of a million and more entries just fine. [1]: https://github.com/mholt/timeliner https://github.com/mholt/timeliner [2]: https://twitter.com/timelinize https://twitter.com/timelinize
- brunoluiz 5y ago
  Interesting project! It seems to be perfect for SQLite, considering it seems to be mostly for reads instead of writes. I wonder if heavy write applications are a bit of a trouble in Golang because of Golang goroutines x C threads model (which I believe SQLite might use?).
hoaljasio 5y ago
https://github.com/gravitational/teleport/ https://github.com/gravitational/teleport/ has the option to use it, but it only uses it as a key value store. CGO isnt too big a problem and if it is a real dealbreaker something like https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite will work as it transpiled the c into go and passes the sqlite test suite. I think there is performance degradation with writes but reads are still pretty quick.
koeng 5y ago
You can use pure Golang SQLite (without requiring CGO) - https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite It works well, but the performance is worse than C version. Not a big deal for what I used it for, though. It was approx. 6x worse at inserts.
anderspitman 5y ago
arp242 has an excellent post[0] about statically compiling sqlite for Go programs, which may be useful. Isn't there some issue where SQLite basically has to be single-threaded in Golang programs, at least if you use the stdlib SQL library? [0]: https://www.arp242.net/static-go.html https://www.arp242.net/static-go.html
- brunoluiz 5y ago
  > Isn't there some issue where SQLite basically has to be single-threaded in Golang programs, at least if you use the stdlib SQL library? I guess not exactly, but iirc there were some caveats/performance issues around C threads x Go routines. A bit is touched in this post: https://www.cockroachlabs.com/blog/the-cost-and-complexity-of-cgo/ https://www.cockroachlabs.com/blog/the-cost-and-complexity-o... But well, it seems arp242 is GoatCounter's developer. Based on the post content, it is powered by SQLite and this is probably a good write-heavy application example. I wonder if there are any blog posts about the SQLite performance for this service.
nn3 5y ago
As a author of a library that got rewritten by another team in go with a similar rationale, I must say that the Go ecosystem's unwillingness (or inability) to interface seamlessly with other languages seems like its greatest drawback.
usrbinbash 5y ago
Using https://github.com/mattn/go-sqlite3 https://github.com/mattn/go-sqlite3 for a pet project right now. So far not a single issue with it, I never had to think about any CGO specifics.

mholt 5y ago

Not a "big project/service" but a Go project that uses Sqlite is one of my own, Timeliner[1] and its successor, Timelinize[2] (still in development). Yeah the cgo dependency kinda sucks but you don't feel it in code, just compilation. And it easily manages Timeline databases of a million and more entries just fine. [1]: https://github.com/mholt/timeliner https://github.com/mholt/timeliner [2]: https://twitter.com/timelinize https://twitter.com/timelinize

brunoluiz 5y ago
Interesting project! It seems to be perfect for SQLite, considering it seems to be mostly for reads instead of writes. I wonder if heavy write applications are a bit of a trouble in Golang because of Golang goroutines x C threads model (which I believe SQLite might use?).

hoaljasio 5y ago

https://github.com/gravitational/teleport/ https://github.com/gravitational/teleport/ has the option to use it, but it only uses it as a key value store. CGO isnt too big a problem and if it is a real dealbreaker something like https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite will work as it transpiled the c into go and passes the sqlite test suite. I think there is performance degradation with writes but reads are still pretty quick.

koeng 5y ago

You can use pure Golang SQLite (without requiring CGO) - https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite It works well, but the performance is worse than C version. Not a big deal for what I used it for, though. It was approx. 6x worse at inserts.

anderspitman 5y ago

arp242 has an excellent post[0] about statically compiling sqlite for Go programs, which may be useful. Isn't there some issue where SQLite basically has to be single-threaded in Golang programs, at least if you use the stdlib SQL library? [0]: https://www.arp242.net/static-go.html https://www.arp242.net/static-go.html

brunoluiz 5y ago
> Isn't there some issue where SQLite basically has to be single-threaded in Golang programs, at least if you use the stdlib SQL library? I guess not exactly, but iirc there were some caveats/performance issues around C threads x Go routines. A bit is touched in this post: https://www.cockroachlabs.com/blog/the-cost-and-complexity-of-cgo/ https://www.cockroachlabs.com/blog/the-cost-and-complexity-o... But well, it seems arp242 is GoatCounter's developer. Based on the post content, it is powered by SQLite and this is probably a good write-heavy application example. I wonder if there are any blog posts about the SQLite performance for this service.

nn3 5y ago

As a author of a library that got rewritten by another team in go with a similar rationale, I must say that the Go ecosystem's unwillingness (or inability) to interface seamlessly with other languages seems like its greatest drawback.

usrbinbash 5y ago

Using https://github.com/mattn/go-sqlite3 https://github.com/mattn/go-sqlite3 for a pet project right now. So far not a single issue with it, I never had to think about any CGO specifics.