Can't bind to object types

[nicerobot]

Trying to insert into a <columnName> object using

db.Exec(`insert into test (metadata) values(?)`, value)

throws

Validation failed for metadata: '{metadata = 1}' cannot be cast to type object]

when value is a string.

How do I insert objects using binds?

[herenow]

I did some digging, and this is not supported at the moment. I'm trying to figure something out.

Heres the problem:

Crate object inserts is not actually supported via sql, so something like:

insert into test (metadata) values("{count: 1}")

Would fail, it only supports object inserts via prepared statements via its rest api, so we need to send it a post like so:

{
    "stmt": "insert into test (metadata) values (?)",
    "args": [{count: 1}]
}

And this is what this is what we do, but you probably already know this.

I would like for us to be able to do something like this:

type Metadata struct {
    Count int `json:"count"`
}

stmt, _ := db.Prepare("insert into test (metadata) values (?)")
_, err := db.Exec(&Metadata{Count: 1})

The problem is that go's database/sql/driver does not support structs, so we cannot pass it a struct as an argument.

I'm not sure how I will support this though, since go's database/sql/driver doesn't support structs.

https://github.com/golang/go/blob/0104a31b8fbcbe52728a08867b26415d282c35d2/src/database/sql/driver/types.go#L175

I'll open an issue with them, to see how can we solve this.

[nicerobot]

Thanks. Few things:

The object literal syntax is supported but it's not JSON-style. It's a custom format like:

insert into test (metadata) values({count = 1});

But, yea, can't bind that and there's no array bindings:

insert into test (metadata) values({count = 1}),({count = 2}),({count = 3});

What would be great (for us) is:

db.Exec(`insert into test (metadata) values(?)`, []map[string]interface{}{...})

Struct-bindings would be great for strict objects and map-bindings for dynamic objects. For map-bindings and batching, the Cassandra driver might be inspirational. It's a little cumbersome but does provide the support.

Also, personally, I think batching is a much more enticing feature than just binds alone. In fact, we wouldn't even using binds unless we can batch insert using them. Which is also why I didn't use prepared statements. That is, it appears not to support batch inserts, but I suppose I should have actually verified that.

I'll keep digging too. For now, we have a workaround. It's not elegant but it works.

[herenow]

@nicerobot I opened an issue about this in Go.

golang/go#17300 (comment)

Maybe this could be supported as soon as Go's 1.8 release.

I think we should wait for it. If it doesn't come, we could start providing a new client package with our own interfaces for using crate, it would also have support for blobs & batching.

[didasy]

Any update for this?