MS SQL uniqueidentifier with Golang SQL driver and UUID

Question

I am connecting to a SQL-Server DB using Go 1.6.2. I am using the go-mssqldb driver (github.com/denisenkom/go-mssqldb) with the sql package.

My problem is that I am creating uuids with the github.com/twinj/uuid package (I also tried some others) and they insert correctly but when I try to scan them out

err := Database.QueryRow("SELECT Id FROM ...").Scan(&struct.Id)

it returns the Id with the first half flipped. I found an explanation in this SO post (https://dba.stackexchange.com/questions/121869/sql-server-uniqueidentifier-guid-internal-representation) and some other articles/posts.

However, I have been unable to find anything specific for solving this in Go. I can't do anything to change the way SQL-server stores the guids. Am I missing something with the UUID scan implementations? I haven't seen a way to change the way it reads. My last option will be to write my own implementation to swap the bits but wanted to reach out to see if I was missing something with the driver or UUID packages or some other library out there.

Algetic

Could you change your query to use "SELECT convert(Id as varchar(36)) FROM ..."? – Subtropics

@mark I'll try that out and let you know! – Algetic

Sorry, correction: convert(char(36), Id) or cast(Id as char(36)) data type conversions – Subtropics

@mark If you add it as an answer I'll mark it answered. – Algetic

great! Could you please try cast(Id as binary(16)). It's likely more efficient. Does it also give a correct uuid? – Subtropics

Answer 1

Casting (or converting) the Id to a char returns the Id in the expected byte ordering:

"SELECT cast(Id as char(36)) FROM ..."

Note casting to binary gives the same result as querying the Id directly.

0F F1 4D 44 F4 72 49 59 9F C8 18 C2 DB 26 3C 3C  As inserted

44 4D F1 0F 72 F4 59 49 9F C8 18 C2 DB 26 3C 3C  Id (no cast)
44 4D F1 0F 72 F4 59 49 9F C8 18 C2 DB 26 3C 3C  cast(Id as binary(16))
0F F1 4D 44 F4 72 49 59 9F C8 18 C2 DB 26 3C 3C  cast(Id as char(36))

Subtropics

Answer 2

I used a custom type to replace the uuid.UUID which worked fine, and I can avoid having to custom every SQL query (credit to https://github.com/go-gorm/gorm/issues/5419#issuecomment-1412398444) :

import (
"database/sql/driver"
mssql "github.com/microsoft/go-mssqldb"
)

type Guid struct {
guid mssql.UniqueIdentifier
}

func (j *Guid) Scan(value interface{}) error {
return j.guid.Scan(value)
}

func (j Guid) Value() (driver.Value, error) {
return j.guid.Value()
}

func (j Guid) String() string {
return j.guid.String()
}

func (j Guid) MarshalText() ([]byte, error) {
return []byte(j.guid.String()), nil
}

Medallion