allow setting "default search path"

[rehatkathuria]

Related to #8.

[vzsg]

Not just databases, the schema name should be configurable too.

[tanner0101]

@vzsg schema name?

[vzsg]

Sorry, I was being imprecise. What I actually meant is setting a default search path after the connection is made, some database drivers expose it. The search path is important in (typ. enterprisish) scenarios where schemas are used as an extra level of grouping/access control over tables.

This has been a pretty common feature request for the libpq-based postgresql package and the -provider.

[tanner0101]

Ah okay, will re-open to track that issue then.

[tanner0101]

Looks like this can be done by simply running:

SET search_path TO myschema;

This could be done in boot.swift. We could eventually add a convenience for this via the provider, but not a priority for the 1.0.0 release.

[fwgreen]

Thanks for finding a solution. I'm very eager to see how using boot.swift would work.

public func boot(_ app: Application) throws {
    // what would actually go here?
}

[tanner0101]

@fwgreen something like this:

public func boot(_ app: Application) throws {
    try app.withNewConnection(to: .psql) { conn in
        return conn.simpleQuery("SET search_path TO myschema").transform(to: ())
    }.wait()
}

[MrMage]

I think that would only change that setting for that single (ephemeral) connection. You could prepend it with ALTER DATABASE xyz, I think.

[fwgreen]

I can confirm that prepending with ALTER DATABASE does work!

public func boot(_ app: Application) throws {
    try app.withNewConnection(to: .psql) { conn in
        return conn.simpleQuery("ALTER DATABASE my_db SET search_path TO my_schema").transform(to: ())
    }.wait()
}

Maybe one day users could have something as simple as

static let entity = "my_schema.my_table"

But this gets us up and running, so I'll take it. Thank you all!

[tanner0101]

Ah, didn't realize that was ephemeral to the connection. You can use DatabasesConfig to add connection configuration closures:

var databases = DatabasesConfig()
// register your dbs and what have you
databases.appendConfigurationHandler(on: .psql) { conn in
    return conn.simpleQuery("SET search_path TO myschema")
}
services.register(databases)

appendConfigurationHandler will run the closure each time a new connection is created.

[fwgreen]

@tanner0101 Will this workaround continue to work in the next major release?

[BlameOmar]

This workaround is not perfect, at least not in Vapor 3. PostgreSQLConnection contains a TableNameCache, which maps OIDs <-> table names using a pair of dictionaries. Unfortunately, this isn't a 1:1 relation in the general case, and breaks whenever multiple schemas use the same table names. I wanted to create new schemas per testcase (eg. 'testing_UserTests_20190718_202247'), which would allow my tests to execute in parallel, but the corrupted TableNameCache cause them to not find their own tables.

I have not tried this in Vapor 4. It seems like the Postgres driver has been completely redone, so it's possible that this works now. However, without explicit support and the tests to back it up, it's possible that this accidentally breaks even if it is currently working. I'm willing to help in this regard. Let me know what your plans are for this.

[tanner0101]

TableNameCache no longer exists so that shouldn't be a problem anymore. I've added a searchPath property to PostgresConfiguration. This property is an optional array of strings.

configuration.searchPath = ["foo", "bar", "baz"]

When set, the following query will be run automatically on new connections:

SET search_path = "foo", "bar", "baz"