JSON

[jpaulgs]

Hey,

I was playing around ramsql today and I've got a json column

db, err = sql.Open("ramsql", "store")
if err != nil {
	panic(fmt.Sprintf("sql.Open : Error : %s\n", err))
}
db.Exec(`CREATE TABLE test (sequence_number BIGSERIAL PRIMARY KEY, aggregate_id UUID, type TEXT, data JSON, created_at TIMESTAMP);`)

Insertion seems to work but unfortunately everything I put into the data column gets an extra level of escaping when I retrieve it for example: {\"id\":\"c05d13bd-9d9b-4ea1-95f2-9b11ed3a7d38\",\"name\":\"\"}

Which breaks the json.Unmarshal process. This behaviour isn't consistent with postgresql.

[proullon]

Hi,

I added a test but was unable to reproduce the behavior you describe, can you look at it and tell me what did you do specifically ?

[jpaulgs]

Hey,

Sorry about this I was actually wrong, the issue is not to do with inserting a JSON string. It is actually about inserting []byte into a JSON field.

The first test inserting a []byte array fails, the second passes. If I run these tests against a Postgresql database using the driver "github.com/lib/pq" they both pass....

I'm actually not sure if the pq driver is converting from []byte to string or if that is happening within the DB.

package test

import (

	. "github.com/onsi/ginkgo"
	. "github.com/onsi/gomega"

	"database/sql"
	"fmt"
	_ "github.com/proullon/ramsql/driver"

	"encoding/json"
)

var _ = Describe("RamSQL JSON test", func() {
	var (
		db *sql.DB
		err error
		j []byte
	)

	BeforeEach(func() {
		db, err = sql.Open("ramsql", "store")
		if err != nil {
			panic(fmt.Sprintf("sql.Open : Error : %s\n", err))
		}
		db.Exec(`CREATE TABLE test (sequence_number BIGSERIAL PRIMARY KEY, json JSON, created_at TIMESTAMP);`)

		db.Exec(`DELETE FROM test WHERE 1 = 1;`)

		j, _ = json.Marshal(map[string]string{"a": "a"})
	})

	AfterEach(func() {
		db.Close()
	})

	Context("when inserting a []byte of json", func() {
		BeforeEach(func() {
			tx, err := db.Begin()
			if _, err = tx.Exec("INSERT INTO test (json) VALUES ($1)", j); err != nil {
				panic(err)
			}
			err = tx.Commit()
		})

		It("the string stored in the DB equals the inserted []byte converted to a string", func() {
			var jsdb string
			db.QueryRow("SELECT json FROM test limit 1;").Scan(&jsdb)

			Expect(jsdb).To(Equal(string(j)))
		})
	})

	Context("when inserting a json string", func() {
		var js string
		BeforeEach(func() {
			js = string(j)
			tx, err := db.Begin()
			if _, err = tx.Exec("INSERT INTO test (json) VALUES ($1)", js); err != nil {
				panic(err)
			}
			err = tx.Commit()
		})

		It("the string stored in the DB equals the inserted string", func() {
			var jsdb string
			db.QueryRow("SELECT json FROM test limit 1;").Scan(&jsdb)

			Expect(jsdb).To(Equal(js))
		})
	})
})

[Joematpal]

Inserting JSON is still broken. If you try to insert with a "?" instead of a "$1" it will break.

[Joematpal]

#73