Gorm support for Postgres schemas

I made a further comment on issue #1280, where an error occurs if you try and specify the Postgres schema to use. i.e. normally to create a table would would specify:

CREATE TABLE schema_name.table_name ( ... ) If you specify the table name in this form, GORM escapes the table name into:

"schema_name"."table_name"

This causes an error.

e.g. I specified the table as:

func (u *User) TableName() string {
    // custom table name, this is default
    return "schema_1.users"
}

The only way I could get something working was to modify the GORM code as follows. There may be side effects with this approach, and I have not tested all scenarios yet. Also, ideally, all of the db-specific (i.e. "dialect") code should be in the dialect_postgres.go file, which I have not done. In a proper solution, the function to escape table names, column names, etc. should be in the dialect file.

The changes are:

scope.go:

// QuotedTableName return quoted table name
// DEF modified to not add quotes to escape table name if dialect is postgres

func (scope *Scope) QuotedTableName() (name string) {
    oldStr := scope.TableName()
    aDialect := scope.Dialect().GetName()

    if aDialect == "postgres" {
        return oldStr
    }

    if scope.Search != nil && len(scope.Search.tableName) > 0 {
        if strings.Index(scope.Search.tableName, " ") != -1 {
            return scope.Search.tableName
        }

        if aDialect == "postgres" {
            return scope.Search.tableName
        }

        newStr := scope.Quote(scope.Search.tableName)
        return newStr
    }

    if aDialect == "postgres" {
        return oldStr
    }

    newStr := scope.Quote( scope.TableName() )
    return newStr

    //return scope.Quote(scope.TableName())
}

I also made the following changes to dialect_postgres.go:

// DEF: had to modify this to separate table_schema from table_name
func (s postgres) HasIndex(tableName string, indexName string) bool {
    var count int
    parts := strings.Split( tableName, "." )

    s.db.QueryRow("SELECT count(*) FROM pg_indexes WHERE schemaname = $1 AND tablename = $2 AND indexname = $3", parts[0], parts[1], indexName).Scan(&count)
    return count > 0
}

// DEF: may need to modify this to separate table_schema from table_name -- tbd
func (s postgres) HasForeignKey(tableName string, foreignKeyName string) bool {
    var count int
    s.db.QueryRow("SELECT count(con.conname) FROM pg_constraint con WHERE $1::regclass::oid = con.conrelid AND con.conname = $2 AND con.contype='f'", tableName, foreignKeyName).Scan(&count)
    return count > 0
}

// DEF: had to modify this to separate table_schema from table_name
func (s postgres) HasTable(tableName string) bool {

    parts := strings.Split( tableName, "." )
    //fmt.Println("Schema: ", parts[0], " Table: ", parts[1])

    var count int
    s.db.QueryRow("SELECT count(*) FROM INFORMATION_SCHEMA.tables WHERE table_schema = $1 AND table_name = $2 AND table_type = 'BASE TABLE'", parts[0], parts[1]).Scan(&count)
    return count > 0
}

// DEF: had to modify this to separate table_schema from table_name
func (s postgres) HasColumn(tableName string, columnName string) bool {
    var count int
    parts := strings.Split( tableName, "." )

    s.db.QueryRow("SELECT count(*) FROM INFORMATION_SCHEMA.columns WHERE table_schema = $1 AND table_name = $2 AND column_name = $3", parts[0], parts[1], columnName).Scan(&count)
    return count > 0
}

There may be other changes needed as well, which I will post as I discover them. Can we get official code support for Postgres schemas?

Comment From: dfarago

On further thought, its probably better to select the schema in Postgres by using a raw sql execute: e.g. db.Exec("SET search_path TO testdb1")

Thereafter, all operations are directed to the selected schema.

Comment From: jinzhu

Not going to support . for postgres, as it will lose some features that GORM currently support.

Comment From: dvaldivia

@dfarago I'm seeing a problem where goroutines are loosing the search_path, have you experience this?

Comment From: kirugan

@dvaldivia this is because SET search_path TO ... affects just one connection to database, but sql.DB has a pool of connections under the hood.

Comment From: kirugan

Or you can solve it with this dirty hack:

db = db.Begin()
db.Exec("set search_path to my_schema")
defer db.Rollback()
// some useful work here

Comment From: generatordog

func (Block) TableName() string {
    return "\"schema.tablename\""
}

Just for anyone encounter this issue, the above method works with dot, gorm.io/gorm v1.25.2