Pg: How specify schema in queries?

[njspok]

I may specify db schema in TableName (example, TableName struct{} 'sql:web.user') field model, but this case not convenient. Im use same models for different schemes. Solve - when create connection make query db.Exec("SET SCHEMA web"), but... application lost connection to db and you library reconnect? If so, query SET SCHEMA not execute again.
How i can specify schema for connection?

[vmihailenco]

I think the easiest way for you is to define separate struct for each schema. If number of schemas is not big that may work for you. E.g.

type User struct {}

type WebUser struct {
    TableName struct{} 'sql:web.user'
    Base
}

For https://github.com/go-pg/sharding I wrote a wrapper for DB that adds some named placeholders that can be used in queries and/or table name - https://github.com/go-pg/sharding/blob/v4/shard.go#L29-L30. You could do something like that too.

[njspok]

Sorry, i find how specify schema when connection create. Work if reconnect.

    params := make(map[string]interface{})
    params["search_path"] = "myschema"

    return pg.Connect(&pg.Options{
        User:     // user
        Database: // db
        Password: // pass
        Addr:     //addr
        SSL:      false,
        Params:   params, // this!
    })

Postgres documentation https://www.postgresql.org/docs/9.1/static/runtime-config-client.html

[vmihailenco]

I would not recommend you to go that way, because you will need separate *DB and pool of connections for each schema. That is not practical.

[mkozjak]

@vmihailenco: what if the number of schemas is huge? ie., in huge multitenancy systems..

[vmihailenco]

See https://github.com/go-pg/sharding

[mdere]

@vmihailenco - so following your example (While I am learning golang)

How does

type User struct {}

type WebUser struct {
    TableName struct{} 'sql:web.user'
    Base
}

Fit into a query example?

[vmihailenco]

This is closed and I can't guess what do you mean.

[john8329]

Hi, the solution proposed by @njspok would actually fit my use case perfectly, where I need a connection to always work inside a specified schema. I see unfortunately that the Params: argument is not supported anymore.

Is there an alternative way for setting the search path / schema at connection time? Manually specifying the schema name in the models is not the right solution for me, it has to be dynamic.

Alternatively I was thinking of using orm.SetTableNameInflector(). Opinions?

Thanks (I know, it's closed, but the problem is closely related to what's discussed here, please tell me if I should create a new issue)

[vmihailenco]

I am using https://github.com/go-pg/sharding/blob/master/cluster.go#L70-L75.

There is also OnConnect hook that allows to execute arbitrary queries.

[john8329]

Ah yes, that's what I was looking for. I was using _, _ = b.Db.Exec(set search_path=?, ctx.SchemaName) right now as a workaround. I saw a previous version of the code that mentioned the Param attribute, this must be its substitute.

Thanks

[john8329]

After some testing it seems that it's not working properly. Maybe we're talking about different things?

I tried using this code to setup the database:

    b.Db = pg.Connect(pgopt).
        WithParam("search_path", ctx.SchemaName)

and queries seemed to ignore the schema prefix. Using my workaround turned out to be good:

    _, _ = b.Db.Exec("set search_path=?", ctx.SchemaName)

I'll just continue using it. Hope this is useful

[john8329]

Testing found out that this works initially and then it forgets it's working in a separate schema. Maybe it's due to connection pooling. None of the mentioned solutions work, should I open a new issue?

[john8329]

For future readers (like myself clicking the same links), the problem is solved by putting the set search_path in the OnConnect method, like this:

pgopt := &pg.Options{
        Addr:     fmt.Sprintf("%s:%d", b.Config.Host, b.Config.Port),
        User:     b.Config.User,
        Password: b.Config.Pass,
        Database: b.Config.Database,
        OnConnect: func(conn *pg.Conn) error {
            _, err := conn.Exec("set search_path=?", ctx.MaxSchemaName)
            if err != nil {
                b.log.Error(err.Error())
            }
            return nil
        },
    }