New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

allow setting "default search path" #9

Open
rehatkathuria opened this Issue Jan 27, 2018 · 10 comments

Comments

Projects
None yet
6 participants
@rehatkathuria
Copy link

rehatkathuria commented Jan 27, 2018

Related to #8.

@vzsg

This comment has been minimized.

Copy link
Member

vzsg commented Jan 29, 2018

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

@Joannis Joannis added the enhancement label Jan 29, 2018

@tanner0101 tanner0101 referenced this issue Feb 1, 2018

Merged

password support #11

5 of 5 tasks complete

@tanner0101 tanner0101 closed this Feb 1, 2018

@tanner0101

This comment has been minimized.

Copy link
Member

tanner0101 commented Feb 1, 2018

@vzsg schema name?

@vzsg

This comment has been minimized.

Copy link
Member

vzsg commented Feb 1, 2018

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

This comment has been minimized.

Copy link
Member

tanner0101 commented Feb 1, 2018

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

@tanner0101 tanner0101 reopened this Feb 1, 2018

@tanner0101 tanner0101 added this to the 1.0.0 milestone Feb 1, 2018

@tanner0101 tanner0101 changed the title Support database names allow setting "default search path" Feb 1, 2018

@tanner0101 tanner0101 added this to Backlog in Vapor 3 Feb 12, 2018

@tanner0101 tanner0101 removed this from the 1.0.0 milestone Jun 15, 2018

@tanner0101

This comment has been minimized.

Copy link
Member

tanner0101 commented Jun 15, 2018

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

This comment has been minimized.

Copy link

fwgreen commented Jun 15, 2018

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

This comment has been minimized.

Copy link
Member

tanner0101 commented Jun 15, 2018

@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

This comment has been minimized.

Copy link
Contributor

MrMage commented Jun 15, 2018

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

@fwgreen

This comment has been minimized.

Copy link

fwgreen commented Jun 15, 2018

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

This comment has been minimized.

Copy link
Member

tanner0101 commented Jun 22, 2018

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment