[Dev] How to properly return arrays from a driver?

[IngwiePhoenix]

Hello! Discussions are not enabled, so I am making a ticket for this... apologies. :)

I am currently working on a SurrealDB Driver and I am currently "fighting" with related records.

Basically, SurrealDB has no JOIN, instead you use FETCH $field... in SELECT to pick fields whose related records should be resolved.

To demonstrate:

DEFINE TABLE book;
DEFINE FIELD author ON book TYPE record<author>;

DEFINE TABLE author;
DEFINE FIELD name ON author TYPE string;

When querying, a JSON object is returned (or a nested CBOR structure) as a result and currently, my driver's Next(dest []any) error method will see any sub-object as being a complex type and return []byte to avoid crashes.

For instance:

{
    "ID": "book:eragon",
    "author": {
        "ID": "author:christoph",
        "name": "Christoph Paolini"
    }
}

And basically, this would result in:

dest[0] = ID -> string
dest[1] = author -> []byte

...but after reading through the docs, I am not so sure if this should be the case or not. Hence, why I would like to ask some people with more experience in this :)

When using sqlx' ScanStruct methods (and similiar), how does it actually determine the keys to use to read into the struct? Is there any "post processing" I could possibly do to assist from within my driver?

So far, I see that perhaps I should instead use dot-notation for the columns: ID, author.ID, author.name. Is that assumption correct?

I just want to make sure my driver is well compatible. So far I tried to make REL work - and it kinda does but just fails when automatically attempting to determine foreign keys which just do not exist in SurrealDB - but I think there is still room for improvements.

Thank you very much and kind regards,

Ingwie

[IngwiePhoenix]

I think I found part of my answer: https://github.com/jmoiron/sqlx/blob/41dac167fdad5e3fd81d66cafba0951dc6823a30/reflectx/reflect.go#L342-L443

As far as I understand, any subelements of a struct will have their name in the TypeQueue be separated by a dot.

type Author struct {
  ID string
  Name string
}

type Book struct {
  ID string
  WrittenBy Author
}

If I am not wrong, this would result, that starting with Book, the names would be:

ID
written_by
written_by.ID
written_by.name

That said, there's so much going on in that function alone, I am quite sure I overlooked at least something...

[IngwiePhoenix]

Changed the title, after doing some tests:

package main

import (
	"fmt"

	_ "github.com/IngwiePhoenix/surrealdb-driver"
	"github.com/jmoiron/sqlx"
)

type Book struct {
	ID    string
	Title string
}

type Author struct {
	Id    string
	Name  string
	Likes []string
}

func main() {
	defSql := `
		DEFINE NAMESPACE IF NOT EXISTS p2;
		USE NS p2;
		DEFINE DATABASE IF NOT EXISTS p2;
		USE DB p2;
		
		DEFINE TABLE IF NOT EXISTS books SCHEMAFULL;
		DEFINE FIELD IF NOT EXISTS title ON books TYPE string;

		DEFINE TABLE IF NOT EXISTS authors SCHEMAFULL;
		DEFINE FIELD IF NOT EXISTS name ON authors TYPE string;
		DEFINE FIELD IF NOT EXISTS likes ON authors TYPE array<string>;
		// DEFINE FIELD IF NOT EXISTS written ON author TYPE array<optional<record<books>>>
	`
	db, err := sqlx.Connect("surrealdb", "ws://db:db@localhost:8000/rpc?method=root&db=p2&ns=p2")
	if err != nil {
		panic(err.Error())
	}

	res, err := db.Exec(defSql)
	if err != nil {
		panic(err.Error())
	}
	ins, _ := res.RowsAffected()
	fmt.Println(ins)


	res, _ = db.Exec(`
		CREATE authors:chris CONTENT {
			name: "Christopher",
			likes: ["a", "lot", "of", "stuff"]
		}
	`) // ignored; exists after rerun

	rows, err := db.Queryx("SELECT * FROM authors;")
	if err != nil {
		panic(err.Error())
	}

	for rows.Next() {
		if rows.Err() != nil {
			panic(rows.Err())
		}
		cols, err := rows.Columns()
		if err != nil {
			panic(err.Error())
		}
		a := Author{}

		fmt.Println("----------")
		fmt.Println(cols)
		err = rows.StructScan(&a) // <- implodes here
		if err != nil {
			panic(err.Error())
		}
		//a.Likes = make([]string, 4)
		//rows.Scan(&a.Id, &a.Likes[0], &a.Likes[1], &a.Likes[2], &a.Likes[3], &a.Name)
		fmt.Println(a)
		fmt.Println("----------")
	}
}

As you can see, author.likes is an array and I am not sure how to best present that to sqlx as it is very much not a common type.

The driver returned the following:

surrealdb:driver:rows:Next []string{"id", "likes.0", "likes.1", "likes.2", "likes.3", "name"} +0s
surrealdb:driver:rows:Next PUT "id" dest[0] = authors:chris +0s
surrealdb:driver:rows:Next PUT "likes.0" dest[1] = a +0s
surrealdb:driver:rows:Next PUT "likes.1" dest[2] = lot +0s
surrealdb:driver:rows:Next PUT "likes.2" dest[3] = of +0s
surrealdb:driver:rows:Next PUT "likes.3" dest[4] = stuff +0s
surrealdb:driver:rows:Next PUT "name" dest[5] = Christopher +0s

The records are internally received as a JSON message - so it's nested.

So, what do I "tell" sqlx, in the columns, to make it like arrays? Do I just return a blank array for likes and keep the .0, .1, .2, .3 columns?

Thanks!

[mrj0]

Just a guess, but when using postgres we'd use pg.Array types. I think if you declare likes as surrealtypes.ArrayOf[string] it'd probably work:

https://github.com/IngwiePhoenix/surrealdb-driver/blob/master/surrealtypes/arrays.go