Is there a way to specify table collation in Prisma schema?

[ryym]

Hi, is there a way to specify table collation in Prisma schema?
Today I tried Prisma Migrate Preview with MySQL and noticed that Prisma creates a table with the utf8mb4_unicode_ci collation.

-- CreateTable
CREATE TABLE `User` (
    `id` INT NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

However I want to use utf8mb4_bin for all tables by default. I know it is OK to modify the generated migration file, but it would be nice if we can specify the default (and individual) collation in Prisma schema because it is cumbersome to modify a migration file every time I added a new table.

Thanks.

[ryands17]

Hey @ryym 👋

The best workaround for now would be set the database collation to utf8mb4_bin and to remove the collation on all tables that Migrate creates before applying the migration.

So the steps would be:

1. Run prisma migrate dev --create-only --preview-feature to create the migrations.
2. Add the alter database command to the migration to set to your required collation and remove all character set changes added by Prisma
3. Run prisma migrate dev --preview-feature to apply all migrations

This will take the collation of the database by default in all further cases. Let me know if that works :)

Also I would suggest creating a feature request to specify table collation for Migrate here :)

[Kosta-Github]

did you create a feature request for this already? I would also like to specify char sets on a per column/property basis...

[shtse8]

did you create a feature request for this already? I would also like to specify char sets on a per column/property basis...

I have the same question, any updates on it?

[ryym]

did you create a feature request for this already?

Sorry for the very late reply. No, unfortunately my project using Prisma was suspended after this discussion, so I didn't file a feature request.

[fkrauthan]

@Kosta-Github did you get around to creating one? Looking for this feature too.

[drichardcarl]

In a project I'm working, I haven't used the prisma migrate dev command to create and applied migrations. I'm using prisma migrate diff to kind of simulate this behavior and I put it in package.json script. So, just using sed will do the magic for me.

npx prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma --script | sed 's/ COLLATE [^;]*;/;/g' > [PATH_TO_YOUR_MIGRATION_FILE.sql]

With this, as long as you set the default charset and collation of all tables in your db, everytime a table is created via a migration the resulting collation will be the default one.

In case you're wondering why I'm not using prisma migrate dev, here are the reasons:

1. I tend to encounter schema drift from time to time (not a big deal, but cumbersome) which forces me to reset db.
2. Data migration is not yet supported and in real cases, schema migration and data migration should be compatible and should be executed in a right manner. e.g. a data migration script can only be run if a certain schema migration has already been run.

With that, I used custom migration solution that can run both schema migration and data migration in the correct order.

[fkrauthan]

For everyone I just created a feature request ticket for this: #16781

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that have already been marked as answered but remain open.

If this discussion still requires further input or clarification, feel free to reopen it or start a new one with updated details. Your contributions are invaluable to the community, and we’re here to help!

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and ongoing support of the Prisma community!