PostgreSQL uuid primary key about gorm HOT 14 CLOSED

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)

from gorm.

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

from gorm.

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.

from gorm.

@robertmeta Yes, sounds reasonable

from gorm.

+1 :)

from gorm.

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

from gorm.

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"

from gorm.

@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.

from gorm.

+1

from gorm.

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?

from gorm.

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.

from gorm.

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?

from gorm.

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.

from gorm.

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

from gorm.