Catch error when using SQLite in Golang
Tags: go, sqlite
Published: 2022-01-21 02:00:54

I was working on a project using Go and SQLite. For the driver part, I tried 2 libraries:
* [mattn go-sqlite3](https://github.com/mattn/go-sqlite3)
* [modernc.org/sqlite](https://pkg.go.dev/modernc.org/sqlite)

Below are examples to how to catch "primary key conflict" and "no row found" errors:

## With mattn sqlite library:

```go
import (
    "database/sql"
    "github.com/mattn/go-sqlite3"
)

var (
    ErrDup      = errors.New("record already exists")
    ErrNoRecord = errors.New("record not found")
)

func wrapDBError(err error) error {
    var sqliteErr sqlite3.Error
    if errors.As(err, &sqliteErr) {
        if errors.Is(sqliteErr.Code, sqlite3.ErrConstraint) {
	    return ErrDup
        }
    } else if errors.Is(err, sql.ErrNoRows) {
        return ErrNoRecord
    }
    return err
}
```

The problem is that it is using CGO, and I am using Macbook M1 for my development. I want to build a release for linux amd64, if I just use:
```bash
$ GOOS=linux GOARCH=amd64 go build -o app-linux
```

It will throw some errors that can't find some symbol. The library suggest to [use xgo to cross build](https://github.com/mattn/go-sqlite3#cross-compile), but I still got some errors when I was doing so. Without having time to search and find the problem, I fell back to compile the linux binary with a docker image: 

```bash
# I am developing on mac m1, so this can be directly built
GOOS=darwin GOARCH=arm64 go build -o build/myapp-darwin
# for linux, using docker to build it
docker run --rm -v $(PWD):/myapp -w /myapp amd64/golang:bullseye go build -o build/myapp-linux -v
```

This works, both `myapp-darwin` and `myapp-linux` will be compiled and generated under the `build` folder. However, the second build with docker would take much longer time than I thought, approximately 1~2 minutes.

So I start to looking for other libraries that doesn't require CGO.

## With modernc.org/sqlite library

This library doesn't need CGO, and I am able to find the way how to catch the "primary key conflict" and "no rows found" error by looking the [source code](https://gitlab.com/cznic/sqlite):

```go
import (
    "database/sql"
    "modernc.org/sqlite"
    sqlite3 "modernc.org/sqlite/lib"
)

func wrapDBError(err error) error {
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return ErrNoRecord
		}
		if liteErr, ok := err.(*sqlite.Error); ok {
			code := liteErr.Code()
			if code == sqlite3.SQLITE_CONSTRAINT_PRIMARYKEY {
				return ErrDup
			}
		}
	}
	return err
}
```

Since this lib doesn't use CGO, cross-build is easy and fast:
```bash
GOOS=darwin GOARCH=arm64 go build -o build/myapp-darwin
GOOS=linux GOARCH=amd64 go build -o build/myapp-linux
```

It only takes seconds to finish the compilation.

## Conclusion

For now I will stick to the second library which doesn't require CGO. Other than catching the errors part, there are basically no difference between those 2 libraries when writing sql (CRUD) operations.

## Reference
* https://gosamples.dev/sqlite-intro/
* https://github.com/mattn/go-sqlite3
* https://github.com/mattn/go-sqlite3/blob/master/_example/simple/simple.go
* https://pkg.go.dev/modernc.org/sqlite