JT Archie

# Debugging and Resolving Issues with Go and the sqlite3 Library

# How the problem started

I recently embarked on a journey to incorporate geographical data into a Golang program. This was an exciting exploration, as most solutions require PostgreSQL and PostGIS. However, for the project I’m working on, having a whole database would add an extra step in the deployment process. I decided that the application I’m building should either be a static website or have a self-hosted option for searching for geographical information.

Let’s start by defining the data schema. I have a bunch of latitude and longitude coordinates, along with a title for each location. For example, store location 45 with a phone number (123) 555-1234. The objective is to build a store locator so that when you visit this company’s website, you can input your location, say, Denver, Colorado, and find the closest stores. This is a standard feature on many company websites with physical locations.

During my investigation, I discovered that the SQLite database has an extension called Spatialite, which can perform many tasks that the PostGIS extension can do. If you’re unfamiliar with SQLite, it differs from a standard database in that it doesn’t rely on a client-server protocol but works with a local file. Since this data won’t change frequently, this is a suitable solution. However, I encountered some technical challenges when trying to use the extension.

In Go, the most popular library for SQLite is go-sqlite3. It requires CGO, which may deter some programmers, but I didn’t have a hard requirement for or against CGO – I just needed to build a working feature.

The library allows extensions to be loaded at runtime. These extensions are named after their compiled dynamic library. For the latest version of Spatialite, the code looks like this:

sql.Register("sqlite_with_spacialite", &sqlite.SQLiteDriver{
	Extensions: []string{
		"mod_spatialite",
	},
})

This is then used with Golang’s database/sql library with the custom driver name.

db, err := sql.Open("sqlite_with_spacialite", "locations.sqlite")
if err != nil {
	return nil, fmt.Errorf("could not open database file: %w", err)
}

This is a simple setup. However, I ran into issues because of being on a Mac with Apple Silicon. This is where this blog post transitions into debugging mode.

I installed SQLite and Spatialite using brew.

brew install sqlite3 libspatialite spatialite-tools

# Debugging step by step

So, let’s dive in and explore how I successfully debugged and resolved the problem with the help of Google, GitHub Issues, and a bit of persistence!

When I ran the code, it would not execute due to an error related to the dynamic module compiled for Spatialite.

dlopen(mod_spatialite.dylib, 0x000A): tried: 'mod_spatialite.dylib' (no such file), '/System/Volumes/Preboot/Cryptexes/OSmod_spatialite.dylib' (no such file), '/usr/lib/mod_spatialite.dylib' (no such file, not in dyld cache), 'mod_spatialite.dylib' (no such file), '/usr/local/lib/mod_spatialite.dylib'

This error message indicates that the dynamic module could not be found in a lookup path. I’ve enough experience with compilation to understand that. I had to spend some time researching:

  • Where is the mod_spatialite.dylib located?
  • How to tell CGO where to look up that file.

Google search: brew mod_spatialite.dylib.

On Apple Silicon, brew changed the location of the installation directory to /opt/homebrew (see this issue). I found it at /opt/homebrew/lib/mod_spatialite.dylib.

With CGO, there are options to change the lookup path for libraries via ldflags. The docs state that you can use -r to set the dynamic linker search path.

$ go run --ldflags=-r=/opt/homebrew/lib main.go
fatal error: unexpected signal during runtime execution
[signal SIGSEGV: segmentation violation code=0x2 addr=0xc00000000 pc=0x1028cc274]

This led to a runtime panic.

This means that the code compiled without a runtime error, but the panic caused memory access issues. It took time to pinpoint the problem. I had some leads, but I needed more concrete.

Google search: golang sqlite3 "unexpected signal during runtime execution".

I searched through any GitHub issue that mentioned Go, SQLite, and SIGSEGV. This issue said a specific build tag that could be used to compile the local install SQLite – --tags libsqlite3.

$ go run --ldflags=-r=/opt/homebrew/lib --tags libsqlite3 main.go
../../go/pkg/mod/github.com/mattn/[email protected]/sqlite3_load_extension.go:25:8: could not determine kind of name for C.sqlite3_enable_load_extension
../../go/pkg/mod/github.com/mattn/[email protected]/sqlite3_load_extension.go:78:8: could not determine kind of name for C.sqlite3_load_extension

The compilation is now failing, with no more runtime errors.

Google search: golang "could not determine kind of name for".

Searches suggested that this error has to do with the compilation of the C code. The recommendations were to remove a newline before an import call. In Go, when importing C headers, the Go parser requires import "C" to be immediately after them.

I didn’t have any import "C" in my codebase. After inspecting the file sqlite3_load_extension.go, I didn’t see the problem mentioned. This required some further searching.

Google search: golang "could not determine kind of name for" sqlite3.

Eventually I found a Github comment, which indicated that I needed to point to the sqlite headers. This required setting CGO_CFLAGS. I had to find where the headers were for the sqlite installation I had.

`$ find /opt/homebrew | grep sqlite | grep '\.h$'
/opt/homebrew/Cellar/libspatialite/5.0.1_2/include/spatialite/sqlite.h
/opt/homebrew/Cellar/sqlite/3.41.2/include/sqlite3.h
/opt/homebrew/Cellar/sqlite/3.41.2/include/sqlite3ext.h

It looks like they’re located in /opt/homebrew/Cellar/sqlite/3.41.2, which corresponds to the version that was installed.

$ CGO_CFLAGS="-I/opt/homebrew/Cellar/sqlite/3.41.2/include" \
  go run \
    --ldflags=-r=/opt/homebrew/lib \
    --tags libsqlite3 \
    main.go
/opt/homebrew/Cellar/go/1.20.2/libexec/pkg/tool/darwin_arm64/link: running clang failed: exit status 1
ld: warning: directory not found for option '-L/usr/local/opt/sqlite/lib'
Undefined symbols for architecture arm64:
  "_sqlite3_enable_load_extension", referenced from:
      __cgo_621878b5dd3b_Cfunc_sqlite3_enable_load_extension in 000007.o
     (maybe you meant: __cgo_621878b5dd3b_Cfunc_sqlite3_enable_load_extension)
  "_sqlite3_load_extension", referenced from:
      __cgo_621878b5dd3b_Cfunc_sqlite3_load_extension in 000007.o
     (maybe you meant: __cgo_621878b5dd3b_Cfunc_sqlite3_load_extension)
ld: symbol(s) not found for architecture arm64
clang: error: linker command failed with exit code 1 (use -v to see invocation)

Another compilation error. This time, however, it provided a clue to the issue.

The linker is searching for the SQLite dynamic library in the incorrect location. I needed to find where sqlite had been compiled within /opt/homebrew as a dynamic library.

$ find /opt/homebrew | grep sqlite | grep dylib
/opt/homebrew/Cellar/sqlite/3.41.2/lib/libsqlite3.0.dylib
/opt/homebrew/Cellar/sqlite/3.41.2/lib/libsqlite3.dylib

To set the location of the dynamic library, you can use CGO_LDFLAGS.

CGO_CFLAGS="-I/opt/homebrew/Cellar/sqlite/3.41.2/include" \
CGO_LDFLAGS="-L/opt/homebrew/Cellar/sqlite/3.41.2/lib" \
  go run \
    --ldflags=-r=/opt/homebrew/lib \
    --tags libsqlite3 \
    main.go
# command-line-arguments
ld: warning: directory not found for option '-L/usr/local/opt/sqlite/lib'
INFO program started

It’s working! There’s still a warning message, but I’m not overly concerned. Warnings can be treated as recommendations.

I’ve set up everything above in environment variables within a .envrc file. This allows me to avoid polluting the commands and not have to remember them.

export GOFLAGS="--tags=libsqlite3 --ldflags=-r=/opt/homebrew/lib"
export CGO_LDFLAGS="-L/opt/homebrew/Cellar/sqlite/3.41.2/lib"
export CGO_CFLAGS="-I/opt/homebrew/Cellar/sqlite/3.41.2/include"

This solution works, but there needs to be an issue with it. If brew upgrades sqlite, I’ll need to update the directories. This can be fixed with some directory lookups within the .envrc script, but at this point, I’m done investigating this.

By leveraging Google and GitHub Issues, I could identify the root cause of the problem, explore various solutions, and ultimately find the one that worked for my situation. As developers, we often encounter challenges that require us to dig deep and think critically. I hope this post has shown you the value of perseverance and resourcefulness when tackling such issues. Happy coding!