Migrate: Ordering Of Table Columns & Default Values

[Alexander-Karsten]

Hey,

I couldn't find an issue template, so I am just outlining my issues below.
I've run into an issue using prisma2 with Postgres, specifically with the order of columns generated and default values used for schemas.

I have the following data model:

model User {
    id          Int        @id @default(autoincrement())
    firstName   String
    lastName    String
    email       String     @unique
    password    String
    token       String?
    createdAt   DateTime   @default(now())
    updatedAt   DateTime   @updatedAt
}

Unfortunately, the DB migration generated by looks like this:

CREATE TABLE "prisma"."User" (
    "createdAt" timestamp(3)  NOT NULL DEFAULT '1970-01-01 00:00:00',
    "email" text  NOT NULL DEFAULT '',
    "firstName" text  NOT NULL DEFAULT '',
    "id" SERIAL,
    "lastName" text  NOT NULL DEFAULT '',
    "password" text  NOT NULL DEFAULT '',
    "token" text   ,
    "updatedAt" timestamp(3)  NOT NULL DEFAULT '1970-01-01 00:00:00',
    PRIMARY KEY ("id")
) 

CREATE UNIQUE INDEX "User.email" ON "prisma"."User"("email")

As you can see there is a few issues:

• The PK (id) is in the middle of table. This is extremely awkward when manually querying the schema and just a bit weird. It would be great if the PK was the first column of the table
• createdAt and updatedAt have a default value of epoch, these don't seem like reasonable defaults
• All the string fields which are marked as not null have a default value of empty string, which kind of defeats the purpose of setting them as not null.

Also I have noticed a small issue, where some files generated by Prisma migrate do not have empty lines at the end of the files, it would be very nice to see this fixed.

Thank you and please let me know if you have any questions.

[janpio]

Thanks @Alexander-Karsten for creating an issue, the only thing missing might be the Prisma version you are using - so I assume most recent for now.

Some enquiries:

createdAt and updatedAt have a default value of epoch, these don't seem like reasonable defaults

What default would you expect here?

All the string fields which are marked as not null have a default value of empty string, which kind of defeats the purpose of setting them as not null.

Again, what default should be there in your opinion?

[Alexander-Karsten]

Hello @janpio, thanks for your quick response!
Sorry, you are correct, I am using [email protected].

In regards to the default, I think:

• If the field is not nullable then there should be no default value unless explicitly set by the user with @default.
• Since Prisma automatically populates updatedAt and we set a default value of now() for createdAt, having a default for non nullable fields could lead to invalid data ending up in the DB.

[janpio]

Thanks, now it is explicit what you would prefer and we can look into it.

Just for clarification:

If the field is _not_ nullable then there should be _no_ default value unless explicitly set by the user with @default.

I do not disagree, but does that even matter in the end, functionality wise? With no default set, wouldn't a text column get an empty string as value anyway if you do not set it via the SQL query?

[Alexander-Karsten]

Sorry @janpio you correct on that count, that was a misunderstanding of the DB behaviour on my part. Please do take a look at it and let me know what you decide.

Thanks!

[janpio]

Not setting a default is still different than setting an empty string, so we might very well decide to use that if we indeed do not have a good reason to keep the current behavior. Thanks!

[Alexander-Karsten]

Cheers, for me having the PK at the beginning of the table would probably be the biggest quality of life enhancement.

Thanks again for your quick responses.

[JoRyGu]

@janpio Has there been any discussion about the ordering of the table columns since this issue was last discussed? I'd love for the order of the columns in the schema file to be preserved when creating the tables. Like @Alexander-Karsten said, I think it would be a huge quality of life enhancement, and that behavior is what I would expect when using other database tools and when manually creating the table as well.

[janpio]

Not that I am aware of. This issue tracks that need, and will be considered when we focus on Migrate again. (I am also one that orders their columns, so I get it ;) )

[mbrookson]

Would also like this. Perhaps it could be a configurable option? Ideally I would expect the generated columns to be in the same order as specific in my schema.prisma

[SharadKumar]

With v2.6.2, I noticed that when you introspect... my schema (inc relations) is maintained. However, the ordering of columns in DB is applied to my schema file and jumbles up all my comments and chosen order in file.

So it seems that Prisma currently uses DB (Postgres in my case) as source of truth for column order, and not the file.

I wish it was configurable. Introspect is not useful until then for me!

[tomhoule]

@SharadKumar since your problem is related to introspection, can you create a separate issue so it can be tracked separately? Your comment will probably be forgotten if it stays in the middle of a migrate issue, unfortunately.

[SharadKumar]

Done #587
@tomhoule

[tomhoule]

Columns are now generated in the same order as the schema in migrations. Thanks for opening this issue!