Gorm: PostgreSQL uuid primary key

[dre1080]

Hi,
will you be adding support to allow PostgreSQL's UUID data type to be used as primary keys? Instead of just int64.
If I had to do this myself which files and methods would I need to alter?

[jinzhu]

Yes, UUID support is considered, but still not sure have to deal with it for other databases like mysql.

[dre1080]

Possibly as a string or bytes if the database doesn't have native support for UUID.
For eg. in MySQL it could be stored as a CHAR(36) or BINARY(16)

[robertmeta]

Using the new plugin system, I wonder if there will be an easy way to add custom types? PostgreSQL has a lot of great special types (uuid, xml, json, etc) -- it would be awesome if we could wire up proper handling of them without breaking general support for MySQL and other databases.

[jinzhu]

@robertmeta Yes, sounds reasonable

[gravis]

+1 :)

[dre1080]

@robertmeta @jinzhu any examples for that kind of implementation?

[ghost]

I just created a fork where I add experimental support of UUID ID column for PostgreSQL - https://github.com/8protons/gorm/commit/84b970b6310d51a0798fa4364e4f53ec1026eac3

It affects only PostgreSQL by checking for string type in pg dialect and then - on create by checking id for slice type (and converting it to string - PostgreSQL returns []uint8 type for uuid).

With this we can use string type for Id column. In postgresql I use id uuid NOT NULL DEFAULT uuid_generate_v4()

And... it works. Auto-populate Id column on Create and return valid uuid string on Query.

You can test it by:

import "github.com/8protons/gorm"

[dre1080]

@reterius Ahh thanks I merged into my fork. Works great.

Don't forget to add PRIMARY KEY index -> id uuid PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL

Also added support for Hstore columns using this as reference: https://github.com/lib/pq/blob/master/hstore/hstore.go
and using only ID name as mentioned here in #60

Should find a way to turn this into a plugin soon, but this will do for now.

[degenhard]

+1

[levpaul]

So if I understand this correctly, we can't currently do something like this?

type Session struct {
    Id      string `sql:"type:varchar(36);primary key"`
    // Otherfields ...
}

And this is because only int64 is supported for primary keys?

[jinzhu]

varchar is supported as primary key, but you need to initialize the value by yourself. (because varchar doesn't support AUTO_INCREMENT)

So actually postgres's uuid is also supported as primary key, you could config postgres to support it.

[levpaul]

Actually this code doesn't work:

package main

import (
    "github.com/jinzhu/gorm"
    _ "github.com/mattn/go-sqlite3"
)

type Session struct {
  Id      string `sql:"type:varchar(36)"`
  UserId  int64
}

func main() {
    db, _ := gorm.Open("sqlite3", "test.db")
    db.LogMode(true)

    db.CreateTable(new(Session))

    testSession := Session{Id: "test", UserId: int64(1)}
    db.Create(&testSession)
}

Running it creates:

(/usr/local/go/src/pkg/runtime/panic.c:248)
[2014-07-10 18:46:57]  [0.27ms]  INSERT INTO "sessions" ("id","user_id") VALUES ('test','1')
panic: reflect.Set: value of type int64 is not assignable to type string

goroutine 1 [running]:
runtime.panic(0x41ac440, 0xc21005ae00)
    /usr/local/go/src/pkg/runtime/panic.c:266 +0xb6
reflect.Value.assignTo(0x41ac1a0, 0x1, 0x60, 0x420fd50, 0xb, ...)
    /usr/local/go/src/pkg/reflect/value.go:2198 +0x312
reflect.Value.Set(0x41ac440, 0xc2100567e0, 0x186, 0x41ac1a0, 0x1, ...)
    /usr/local/go/src/pkg/reflect/value.go:1385 +0x9b
github.com/jinzhu/gorm.setFieldValue(0x41ac440, 0xc2100567e0, 0x186, 0x41ac1a0, 0x1, ...)
    /Users/llovelock/go/src/github.com/jinzhu/gorm/utils.go:115 +0x22e
...

I guess string Id isn't supported?

[jinzhu]

Hi @levilovelock

Thank you for your feedback, just looked at this issue, seems this is caused by database driver returned int as LastInsertId (but should be string in this case).

Just fixed the problem, please recheck it after upgrade gorm.

[levpaul]

@jinzhu - Just tested the fix and it works a charm! Thanks a bunch for that man :)