Postgres non-public schema support

1

I’m using Diesel 1.4.5 and Diesel CLI 1.4.1.

I’ve been searching for a way to use a schema other than public in Postgres via the Diesel.

I’ve tried various ways and the closest I’ve come is to create the Postgres schema through migrations but, even though the migrations succeed, schema.rs isn’t generated.

Are non-public Postgres schemas not supported?

2

For any schema other then public you need to configure for which schema schema.rs should be generated. See the documentation here for details.

3

@weiznich
That was one of the things I tried.

I added schema = “some_schema” to my diesel.toml file and ran “diesel migration run”. schema.rs was update with

pub mod some_schema {}

and the tables were created in public again.

So, maybe I’m misunderstanding something about those docs?

4

Maybe you can provide a list of steps to reproduce your issue, otherwise I fear it’s not possible to give another answer than: It’s supported but requires some setup.

5

I’ll see if I can duplicate it in a separate project and push it to github if I can.

6

See the following repo for a project demonstrating the problem.

I’ve added the schema field to diesel.toml.

https://github.com/ereichert/dieselschematest/blob/main/diesel.toml#L6

When I run

==> diesel migration run

the tables are created in the public schema in the dieselschematest database.

Screen Shot 2020-12-06 at 2.14.49 PM

In addition, the schema isn’t written to src/schema.rs.

If I comment out the schema field in diesel.toml everything works as expected using the public schema.

I’m happy to provide more information.

7

Ok that seems easy to answer. To make this easier to follow I will copy the relevant parts of your project into my post.

You migration looks like this:

CREATE TABLE somethings
(
    st_id SERIAL PRIMARY KEY,
    fielda TEXT NOT NULL,
    fieldb TEXT NOT NULL
);

According to the postgres documentation this creates a table in the default schema (which is public not somethingsschema).

If you want to create a table in a custom schema you need to explicitly create that schema first as part of your migrations and then create the table inside of this schema. A corresponding schema would look like this:

CREATE SCHEMA somethingsschema;

CREATE TABLE somethingsschema.somethings
(
    st_id SERIAL PRIMARY KEY,
    fielda TEXT NOT NULL,
    fieldb TEXT NOT NULL
);
8

This is interesting. When I was troubleshooting this issue last week I tried that in the real project I’m working on. I tried it again in the real project this morning. It does not work.

I tried with the example project and it works.

Should I delete all of the Diesel files in the real project and try ‘diesel setup’? Or is there something else I should investigate first?

9

It shouldn’t be necessary to delete any diesel files here. The only thing you need to check if you’ve already applied your migrations and changed them (inside of your migration directory) afterwards. If that’s the case just delete and recreate the database.

10

I just figured this out.

One has to include the correct SQL AND the correct config for everything to work correctly.

With the correct migrations defined as @weiznich indicated above, the correct schema will be generated in the database. However, without adding the schema = “some_schema”in the ./diesel.toml file, the ./src/schema.rs will not be generated properly.

Should the docs be updated to reflect this?

11

It’s not clear what documentation should be updated to reflect this. I mean the diesel cli documentation already mentions the schema flag/key in various places. Otherwise this is just normal SQL for postgresql, which is documented in their documentation.