Figure out story for dynamically setting PG schemas

[killercup]

While infer_schema! and friends support postgres schemas, they only do so statically; i.e., you specify that for database foo you want the tables from schema bar using infer_schema!("postgres://foo", "bar").

Another use-case for schemas is to run multiple production apps on the same database, using different schemas. And while it is possible to do this with diesel, it requires hacking around with connection URL parameters, as described by @skade in this post. It's not an indented feature, though, and thus doesn't work with diesel setup for example.

[sgrif]

I'm not sure I see much value in doing this over specifying the schema in the table! call, and just glob-importing that module where needed. Can someone elaborate more on the use case?

[skade]

The recommended way of sharing a database on many SaaS and multiuser systems where you get one database assigned (such as Heroku) is assigning a schema to each deployed application.

This name cannot (reasonably) be picked ahead of time, but can easily be configured as a runtime property.

The use case is currently indeed this: sharing a low-tier Heroku database (which costs 50$/month) to multiple mid-traffic applications. In this case, I'd like the user to be able to pick the schema name.

To my current understanding, Diesel mostly supports this through the URL trick and could better support it if it did what Rails does (SET search_path on connection). There main roadbump is that database setup doesn't work, as it ignores the schema.

[lthms]

There main roadbump is that database setup doesn't work, as it ignores the schema.

I ran into that exact problem today. Hence, I am wondering: is there any workaround to have src/schema.rs generated when using a dedicate schema and the search_path URL trick?

[kamek-pf]

Stumbled on this today. Adding ?application_name=my_app&options=-c search_path%3Dmy_app to the connection string as suggested in the article as well as specifying schema in diesel.toml seems to do what I want.

Should we document the connection string options somewhere ? It seems to have helped several people already. Unless this is completely unintended and likely to break ?

[sgrif]

http://docs.diesel.rs/diesel/pg/struct.PgConnection.html documents what it takes

[kamek-pf]

My bad, I missed it. Thanks @sgrif !

[51yu]

If I specify options='-c search_path%3Dmy_schema', after run diesel migration run the src/schema.rs is empty (though tables created correctly). If I remove the option, i.e public, the schema.rs has contents

[weiznich]

@51yu As this open issue indicates official support for switching schemas via the database url is not provided by diesel, so it is kind of expected that things may not work at all.

[Atuadan]

I ran into a related issue where I work on different environments that use different postgres schema names. Therefore I can't hardcode them in my app or use schema in diesel.toml.

Instead, I have set the search_path on each db to the respective schema:

-- set search path for a specific role:
alter role my_user set search_path = my_schema;

-- set search path for a specific database:
alter database my_database set search_path = my_schema;

In general this works quite well with diesel. I don't need to specify the schema anywhere in my code and also diesel setup and diesel migration work as expected, automatically using the custom schema.

The only exception is diesel print-schema which seems to search for tables on public schema by default. If public is empty the command returns an empty schema definition, If there is any table in public, I get an error, because I haven't added public to schema_path. If I add it to the path, print-schema returns the tables on public.

I can overwrite which schema is used with --schema my_schema , but this creates a new pub mod my_schema where each table is fully qualified with my_schema.my_table -> {...}, which is an issue since the schema names on the other environments are different.

When I remove the mod definition and the schema name from all qualified table names, everything works as expected on all environments, since all other processes simply follow search_path. However, doing so means extra manual effort to clean up the schema.rs file after each migration.

My proposal would be to add a new boolean attribute infer_schema_from_search_path (or similar) to diesel.toml. If set to true, diesel print_schema first reads the current search_path, picks the first schema of the list and extracts its tables without qualifying them. If set to false, it reads the tables from public as usual.

[weiznich]

However, doing so means extra manual effort to clean up the schema.rs file after each migration.

You can likely automate that via the patch_file support in diesel-cli. See this guide for details.

My proposal would be to add a new boolean attribute infer_schema_from_search_path (or similar) to diesel.toml. If set to true, diesel print_schema first reads the current search_path, picks the first schema of the list and extracts its tables without qualifying them.

That might be reasonable behavior without the setting. I would be happy to review a PR for that.