pq: operator does not exist: uuid ~~ unknown

[njacob1001]

I have defined my Postgres database with id as UUID type and the POST is working correctly

POST: http://localhost:1337/api/accounts

but for PATCH action the response is "pq: operator does not exist: uuid ~~ unknown"

PATCH: http://localhost:1337/api/accounts/401a16b7-5131-4e61-84ce-9f6d69907fba

[smyrman]

Hi. We need more information in order to help you. It could be this is an issue with rest-layer, or it can be an issue with the third-party storer implementation.

1. Which Storer implementation are you using?

2. How does you schema look like (or the ID field?

[njacob1001]

Hi @smyrman

1. https://github.com/apuigsech/rest-layer-sql with https://github.com/lib/pq
2. this is a fragment of the code implementation

// I have the server struct data
type Server struct {
    httpAddress     string
    router          *chi.Mux
    db              *sql.DB
    shutdownTimeout time.Duration
}

index := resource.NewIndex()

// using sqlStorage "github.com/apuigsech/rest-layer-sql"
// s.db is server.db of type 
h := sqlStorage.NewHandlerWithDB("postgres", s.db, "accounts", nil)

index.Bind("accounts", domain.Account, h, resource.Conf{
    AllowedModes: resource.ReadWrite,
})

api, err := rest.NewHandler(index)
if err != nil {
    log.Fatalf("Invalid API configuration: %s", err)
}

s.router.Mount("/api", http.StripPrefix("/api", api))

/// THE SCHEMA FOR ACCOUNT IS

var Account = schema.Schema{
    Description: "Accounts schemas",
    Fields: schema.Fields{
        "id": {
            Required: true,
            Filterable: true,
            Sortable:   true,
        },
        "identifier": {
            Required: true,
            Filterable: false,
            Sortable: false,
        },
        "password": {
            Required: true,
            Filterable: false,
            Sortable: false,
        },

    },
}

// in postgres i created this table

create table if not exists accounts
(
    id                     uuid         not null,
    etag                   varchar(128),
    corporate_account      boolean      not null default false,
    identifier             varchar(250) not null unique,
    password               varchar(250) not null,
    last_login             timestamptz,
    created_at             timestamptz  not null default now(),
    updated_at             timestamptz  not null default now(),
    primary key (id)
);

[smyrman]

You can try to add a schema.FieldValidator to the ID field that converts an input of type string to an uuid.ID on Validate, and if needed, from an uuid.ID to string on Serialize.

This may help because the query preparation in rest-layer will run the field validator on relevant fields before running the query.

[smyrman]

You can use which ever uuid library that implements the relevant interfaces to convert from/to an SQL value.

[njacob1001]

You can try to add a schema.FieldValidator to the ID field that converts an input of type string to an uuid.ID on Validate, and if needed, from an uuid.ID to string on Serialize.

This may help because the query preparation in rest-layer will run the field validator on relevant fields before running the query.

I created the validator with

type UUID struct {}

func (u UUID) Validate(value interface{}) (interface{}, error) {
    s, ok := value.(string)

    if !ok {
        if b, ok := value.([]byte); ok {
            id, error := identifier.Validate(string(b))
            if error != nil {
                return nil, error
            }
            return id.ID(), nil
        }
        return "", identifier.ErrInvalidUUID
    }

    id, error := identifier.Validate(s)

    if error != nil {
        return nil, error
    }

    return id.ID(), nil
}

But now POST actions is not working

{
    "code": 520,
    "message": "pq: invalid input syntax for type uuid: \"452129999\""
}

If I change id.String() instead of id.ID() the POST action is working, but UPDATE is not working is showing the same error

[njacob1001]

Issue was related to https://github.com/apuigsech/rest-layer-sql/blob/master/query.go#L222

it uses LIKE operator to compare the id, so for string works but with uuid type it doesn't, I had to clone the repo and add my own customization

[smyrman]

Nice that you where able to solved it.

You may consider raising a bug against reset-layer-sql. Using LIKE does not sound like something you would want to do for an ID comparison...