JSON1 support go-sqlite3 using gorm

Question

In the following example I use json_extract(...) using the go-sqlite3 driver in Golang.

package main

import (
    _ "github.com/mattn/go-sqlite3"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
    "fmt"
    "encoding/json"
    "net/http"
)

func main() {

    var db *gorm.DB

    type Session struct {
        gorm.Model
        SessionID string `json:"session_id"`
        Options string `json:"options"`
    }

    db, err := gorm.Open("sqlite3", "./app.db")
    if err != nil {
        fmt.Println(err)
    }

    defer db.Close() 
    db.AutoMigrate(&Session{})

    var m map[string]int
    m = make(map[string]int)
    m["a"] = 1
    m["b"] = 2
    m["c"] = 3

    js, err := json.Marshal(m)

    sess := Session{
        SessionID: "test",
        Options: string(js),
    }

    db.Create(&sess)
    db.Save(&sess)

    var b = &Session{}

    type Result struct {
        Options string
    }

    // JSON test
    var res Result
    db.Raw("SELECT json_extract(options, '$.a') as Options from sessions").Scan(&res)

    fmt.Println(sess.ID)
    fmt.Println(res)

}

The problem is that I cannot get the JSON1 module to be activated when rebuilding the go-sqlite3 driver. It will error undefined function: json_extract on the db.Raw(...) line.

In any case, I know that for JSON support, github.com/mattn/go-sqlite3 has to be compiled with -flags "sqlite_json1". I've tried several variations:

go build -a -flags "sqlite_json1"  github.com/mattn/go-sqlite3
go install -a -flags "sqlite_json1"  github.com/mattn/go-sqlite3
go build -a --flags "sqlite_json1"  github.com/mattn/go-sqlite3
go install -a --flags "sqlite_json1"  github.com/mattn/go-sqlite3

and more variations in flags, such as sqlite_json, json, json1, etc. Nothing will get rid of the undefined function error. Any ideas how to properly rebuild go-sqlite3?

Obviously I rebuild my own code after that as well.

Some info:

go version: go version go1.13.1 linux/amd64 (platform: Kubuntu 18.04)

go-sqlite3 version: github.com/mattn/go-sqlite3 current master

gorm version: github.com/jinzhu/gorm current master

score 0 · Answer 1 · answered Oct 10 '19 at 06:46

According to go-sqlite3's docs, any of the following flags will work:

sqlite_json
sqlite_json1
json1

Here is the contents of sqlite3_opt.json1.go which define the tags that include this file.

// Copyright (C) 2014 Yasuhiro Matsumoto <mattn.jp@gmail.com>.
//
// Use of this source code is governed by an MIT-style
// license that can be found in the LICENSE file.

// +build sqlite_json sqlite_json1 json1

package sqlite3

/*
#cgo CFLAGS: -DSQLITE_ENABLE_JSON1
*/
import "C"

Reference: https://github.com/mattn/go-sqlite3/blob/master/sqlite3_opt_json1.go

Also to point out, you're using --flags in your go build command, but give --tags a try instead.

Both `--tags` and `--flags` with any of the values `sqlite_json`, `sqlite_json1` and `json1` still produce a build with the same errors. Strangely enough, doing it directly in the C amalgamation does work, ie: `#define SQLITE_ENABLE_JSON1 1` in `sqlite3-binding.c` and running `go build -a` in the `src` directory. — riccardo_92, Oct 10 '19 at 07:25

JSON1 support go-sqlite3 using gorm

1 Answers1