SQLite in Go, with and Without Cgo

• sqlite

  73 - -

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 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.

• c2go

  2 306 7.9 Go

  Convert C to Go (by goplus)

  

I agree. And I work at 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.

• InfluxDB

  www.influxdata.com featured

  Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.

  

• xgo

  2 450 6.8 Shell

  Go CGO cross compiler (by techknowlogick)

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

• fyne

  150 23,280 9.6 Go

  Cross platform GUI toolkit in Go inspired by Material Design

  

It's especially helpful to be pure Go when targeting both iOS and Android with https://github.com/fyne-io/fyne#about

• go-sqlite3

  40 7,446 6.3 C

  sqlite3 driver for go using database/sql

The default go sqlite driver is 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 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.

• sqlite

  7 554 0.0 C

  Go SQLite3 driver (by crawshaw)

The default go sqlite driver is 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 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.

• drydock

  3 6 0.0 Go

  Experiment in unit testing with PostgreSQL using Docker

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)

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.

• SaaSHub

  www.saashub.com featured

  SaaSHub - Software Alternatives and Reviews. SaaSHub helps you find the best software and product alternatives

  

• sqinn

  4 74 7.0 C

  SQLite over stdin/stdout

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

• Sqinn-Go

  8 400 5.5 Go

  Golang SQLite without cgo

• zig

  816 30,631 10.0 Zig

  General-purpose programming language and toolchain for maintaining robust, optimal, and reusable software.

  

Except that’s at the very least broken for macOS -> Linux right now. I attempted to use zig cc as the cross compiler for sqlite other day. Ran into https://github.com/ziglang/zig/issues/5882 https://github.com/ziglang/zig/issues/9485 an almost two-year-old issue.

Cross compiling C sucks.

• sqlite

  1 182 6.1 Go

  work in progress (by tailscale)

  

• go-sqlite

  12 677 7.6 Go

  Low-level Go interface to SQLite 3

• tcl

  11 - -

I think the author of modernc.org/sqlite also ported the test suite. They wrote https://gitlab.com/cznic/tcl to run the TCL-based tests, for example.

• oil

  234 2,730 9.9 Python

  Oils is our upgrade path from bash to a better language and runtime. It's also for Python and JavaScript users who avoid shell!

  

Yes 100%, here is my lament from 4 years ago on that topic.

https://news.ycombinator.com/item?id=16741043

A big part of my pain, and the pain I've observed in 15 years of industry, is programming language silos. Too much time is spent on "How do I do X in language Y?" rather than just "How do I do X?"

For example, people want a web socket server, or a syntax highlighting library, in pure Python, or Go, or JavaScript, etc. It's repetitive and drastically increases the amount of code that has to be maintained, and reduces the overall quality of each solution (e.g. think e-mail parsers, video codecs, spam filters, information retrieval libraries, etc.).

There's this tendency of languages to want to be the be-all end-all, i.e. to pretend that they are at the center of the universe. Instead, they should focus on interoperating with other languages (as in the Unix philosophy).

One reason I left Google over 6 years ago was the constant code churn without user visible progress. Somebody wrote a Google+ rant about how Python services should be rewritten in Go so that IDEs would work better. I posted something like ... Meanwhile other companies are shipping features that users care about . Google+ itself is probably another example of that inward looking, out of touch view. (which was of course not universal at Google, but definitely there)

This is one reason I'm working on https://www.oilshell.org -- with a focus on INTEROPERABILITY and stable "narrow waists" (as discussed on the blog https://www.oilshell.org/blog/2022/02/diagrams.html )

• SaaSHub

  www.saashub.com featured

  SaaSHub - Software Alternatives and Reviews. SaaSHub helps you find the best software and product alternatives

  

Suggest a related project