SQL migrations are inconsistent when generating from schema.rs

[YievCkim]

Hi,

I am playing with diesel on PG and it appears I don't get the desired table when I generate the migration from schema.rs .

This is what I want:

-- Your SQL goes here
CREATE TABLE "user" (
  id SERIAL PRIMARY KEY,
  username VARCHAR NOT NULL
);

CREATE TABLE resource_server (
  id SERIAL PRIMARY KEY,
  uri VARCHAR NOT NULL
);

This migration generate this schema.rs:

diesel::table! {
    resource_server (id) {
        id -> Int4,
        uri -> Varchar,
    }
}

diesel::table! {
    user (id) {
        id -> Int4,
        username -> Varchar,
    }
}

diesel::allow_tables_to_appear_in_same_query!(
    resource_server,
    user,
);

But if I try to generate the SQL migration from this schema.rs with diesel migration generate --diff-schema user_rs_draft, I get:

-- Your SQL goes here
CREATE TABLE "resource_server"(
	"id" INT4 NOT NULL PRIMARY KEY,
	"uri" VARCHAR NOT NULL
);

CREATE TABLE "user"(
	"id" INT4 NOT NULL PRIMARY KEY,
	"username" VARCHAR NOT NULL
);

The ids should be 'SERIAL' and not 'INT4'.

I tried to generate migrations from this modified schema.rs too:

diesel::table! {
    resource_server (id) {
        id -> Serial,
        uri -> Varchar,
    }
}

diesel::table! {
    user (id) {
        id -> Serial,
        username -> Varchar,
    }
}

diesel::allow_tables_to_appear_in_same_query!(
    resource_server,
    user,
);

But then I lose the primary key constraint on ids.

Did I miss something ? I suspect it's a bug, but maybe I am wrong. Let me know.

[weiznich]

There is no way for the CLI tool to know if a user really wants to use a auto incrementing ID there or not as there are also cases where this is not desired. In fact the CLI tool will more or less literally use the types as provided by the user. In that case this means if you write Int4 you get an Int4, if you write Serial you get Serial and so on. This is expected behaviour as we don't want to do any heavy processing at that point. After all we generate the SQL for you, but don't automatically apply the migration. The main reason for that is to give you as a user the possibility to modify the generated migration afterwards, for example by tweaking types or adding constraints.

[YievCkim]

Ok I see. The SQL commands are generated and then we can modify it.

That said, it's strange that generation ignores the primary key with serial and deduces it with INT4.

[weiznich]

That said, it's strange that generation ignores the primary key with serial and deduces it with INT4.

We don't parse the SQL on our own, we ask the database which tables and which columns exit. For such primary keys PostgreSQL returns Int4 as Serial is really only an alias there.

[YievCkim]

It is the "PostgreSQL-specific way to create an autoincrementing column" but indeed it's specific to this backend and I understand why you wont bother with that and keeping things simpler. all the more so as there are other ways of doing this on postgres...

[weiznich]

It's not just adding postgresql specific information there. While querying the postgresql information schema you literally get back that this column has the type Int4 even if you specified it at Serial. It would require a lot of work to infer that information from somewhere else, which it is in my opnion just not worth.

[YievCkim]

ah yes I see... Surely the information is elsewhere indeed. I am agree it's not worth the work