Support for specifying or using default collation in Prisma schema

[kelvinCJJ]

Question

Hi Prisma team and community,

I noticed that Issue #29171 (“Default MySQL collation for new tables/columns”) hasn’t received feedback.

This feature is important because:

It affects consistency across new tables/columns.

Developers often need explicit control over collation for multilingual or case-sensitive applications.

Other ORMs provide schema-level collation options, so adding this would improve parity.

This has also been discussed here: #14179 and #4743

I’d love to hear from both the Prisma team and the wider community:

How to reproduce (optional)

In MySQL, when new models are added to the schema file and migrated, Prisma generates SQL queries using the utf8mb4 character set and utf8mb4_unicode_ci collation. When integrating new services to the same database, there is a collation matching error due to mismatched collation

Expected behavior (optional)

Allow us to specify the default-collation, so that there wouldn't be mismatched collation

Information about Prisma Schema, Client Queries and Environment (optional)

No response

[CharlieJamesGwapo]

Schema-level collation is not supported in Prisma today — that is exactly what the linked issues (#29171, #14179) are tracking. While that is open, a few practical workarounds:

1. Edit the migration SQL directly. prisma migrate dev writes a migration.sql file you can hand-edit before applying. Add COLLATE utf8mb4_unicode_ci to the column or ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci and check it in. Prisma will execute the file as-is, so this survives across environments.

2. Set the database default. If you want one collation for everything new, set it at the database level:

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Every new table/column then inherits that collation automatically, so your Prisma schema does not need to know about it.

3. Raw SQL for one-offs. For converting existing tables, prisma.$executeRaw or a raw migration step works fine.

For prioritization: discussions on Prisma do not get the same triage visibility as issues, so 👍 reactions on issue #29171 are the strongest signal you can give the team that you want this in the schema language.

[tamish-max]

As far as I know, Prisma still doesn't have a top-level schema setting for "default collation for everything generated from this schema". The practical workarounds are:

• set the database/table default collation outside Prisma, so new Prisma tables inherit it
• or edit the generated migration SQL and add the desired CHARACTER SET ... COLLATE ... clauses manually

That generally works fine with Prisma, but the source of truth for the default collation stays in SQL / database config rather than schema.prisma. So I think the linked issues are still the right place to track first-class schema support.

[GitNimay]

This is a genuinely painful limitation that keeps coming up across multiple issues and discussions, so it is worth consolidating the community position clearly.

The core problem

Prisma hardcodes utf8mb4_unicode_ci in generated DDL regardless of what your MySQL server or database default collation is set to. This means every table and column Prisma creates overrides the server default explicitly, making it impossible to align Prisma-managed tables with tables created by other services or tools that inherit the server default.

The specific scenario that breaks things repeatedly is integrating Prisma into an existing database or alongside another ORM or service. If the existing tables use utf8mb4_0900_ai_ci (the MySQL 8 default) and Prisma creates new tables with utf8mb4_unicode_ci, any join or comparison across those tables produces a collation mismatch error at runtime.

What would actually fix this

A collation field at the datasource block level would solve the majority of cases:

datasource db {
  provider  = "mysql"
  url       = env("DATABASE_URL")
  collation = "utf8mb4_0900_ai_ci"
}

A field-level override would cover the remaining cases:

model Post {
  title String @db.VarChar(255) @collation("utf8mb4_unicode_ci")
}

Current workaround

The only reliable workaround right now is a post-migration SQL script that alters affected tables. You can hook into this using Prisma's db execute or by maintaining a migrations/collation_fix.sql file that you run after every prisma migrate deploy:

ALTER TABLE Post CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE User CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

This is obviously not sustainable at scale but it unblocks you in the short term.

Ask for the Prisma team

Given that this has been raised in #4743, #14179, and #29171 spanning several years, it would be helpful to get an official position on whether this is on the roadmap and at what priority. Even a confirmed "not planned" allows the community to make informed decisions about workarounds or alternative tooling. The absence of any response is the most frustrating outcome for a frequently requested feature.

Adding a thumbs up to #29171 directly will help signal priority to the team.

[DucMinhNe]

Just to set expectations on the current state: there is no way to express collation in the Prisma schema language today. It isn't in the schema reference — there's no @db-style collation attribute and no datasource-level "default collation" setting — so Prisma Migrate will keep emitting CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci for new MySQL tables/columns. The schema-level request is the open issue you linked (#14179); nothing has shipped, so for now this has to be handled at the migration / database level.

Two practical ways to get the collation you actually want:

1. Edit the generated migration SQL (--create-only). This is the supported escape hatch for anything the schema can't express. Generate the migration as a draft instead of applying it:

npx prisma migrate dev --create-only

Then open the new file under prisma/migrations/.../migration.sql and adjust the CREATE TABLE (or add an ALTER TABLE) to your collation, e.g.:

ALTER TABLE `MyTable` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Apply it with npx prisma migrate dev (or prisma migrate deploy in CI/prod). The edited SQL is committed with the migration, so it's reproducible across environments. The trade-off is you have to remember to do this for each new table.

2. Set the default collation on the database/server so new tables inherit it. Since the mismatch is between Prisma's utf8mb4_unicode_ci default and the collation of the database you're integrating with, aligning the database default removes the per-table work:

ALTER DATABASE `mydb` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

A column with no explicit COLLATE inherits the table default, and a table with no explicit collation inherits the database default — so once the database default matches, Prisma's generated CREATE TABLE statements line up with the surrounding schema and the "collation matching" errors go away. (You can verify with SHOW TABLE STATUS / SHOW FULL COLUMNS FROM <table>.)

Option 2 is usually the cleaner fix for the "integrating new services into the same database" case you described, since it makes Prisma-managed tables consistent with everything else by default; option 1 is there when you need a specific collation on specific columns.

[logusivam]

This is a long-standing, well-documented gap, and you're right that it's gotten surprisingly little traction given how often it comes up. The good news is there's a working manual workaround today; the bad news is there's currently no schema-level config option, and this has been requested for years without being implemented.

---

History of this request

This isn't a new ask — it traces back further than you may realize:

• Issue Default MySQL collation for new tables/columns #14179, filed July 2022, describes exactly your symptom: when adding new models in the schema file and migrating with MySQL, Prisma generates SQL with character set utf8mb4 and collation utf8mb4_unicode_ci, with the requested fix being either an option to not specify collations at all (so tables inherit the database's default) or an option to specify the default character set and collation in the schema file.
• An earlier related request, issue Specify collation in Prisma schema #12845, proposed schema-level @@collate(...) and @collate(...) directives at both the table and column level — for example specifying utf8mb4_bin per-table or per-column — with example generated SQL showing exactly how that would translate to COLLATE clauses in the CREATE TABLE statement.
• The original discussion, Is there a way to specify table collation in Prisma schema? #4743, goes back even further, with the same complaint that Prisma Migrate creates tables with utf8mb4_unicode_ci when the user wants utf8mb4_bin for all tables by default, and that manually editing every generated migration file is cumbersome.

So #29171 is at minimum the fourth time this exact request has been raised in some form across multiple years. That's worth noting in your issue as evidence of sustained demand, since maintainers triage partly by how often something recurs.

---

The current workaround (works today, no plugin or core change needed)

The workaround the Prisma team has pointed people to is: set the database's default collation to your desired value (e.g. utf8mb4_bin) and remove the explicit COLLATE clause that Migrate adds to each generated migration file before applying it. Since tables inherit the database default when no explicit collation is specified, this avoids the mismatch entirely going forward.

Step-by-step for your case:

1. Set your database's default collation once:

   ALTER DATABASE your_database_name
     CHARACTER SET utf8mb4
     COLLATE utf8mb4_general_ci;  -- or whatever collation your other services use

2. Generate migrations with --create-only so you can edit before applying:

   npx prisma migrate dev --create-only

3. Open the generated .sql file and strip the explicit collation Prisma adds:

   -- Prisma generates:
   CREATE TABLE `User` (
     `id` INT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`id`)
   ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

   -- Edit to:
   CREATE TABLE `User` (
     `id` INT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`id`)
   ) DEFAULT CHARACTER SET utf8mb4;

4. Apply the edited migration:

   npx prisma migrate dev

This is workable but exactly as tedious as you'd expect — you must remember to do it on every single migration that creates a table, indefinitely, since there's no schema-level switch to make it automatic.

---

A related but distinct issue worth being aware of

If you're hitting collation mismatches specifically around foreign keys and joins (not just table creation), be aware there's a separate, nastier failure mode: manually adjusting the character/collation set in a migration can cause the table creation to silently fail with no error when a subsequent ADD FOREIGN KEY statement requires matching collation between the referencing and referenced columns — removing the explicit collation makes the table create successfully, but then the foreign key fails for the same underlying reason. If you go the manual-edit route above, make sure any foreign-keyed columns end up with the same collation on both sides, not just matching the database default in isolation.

---

What I'd recommend doing with this report

Since #29171 already exists and you've cross-referenced #14179 and the #4743 discussion, the most useful next step is consolidating rather than spreading the conversation across yet more threads:

1. Add a 👍 reaction and a comment on Default MySQL collation for new tables/columns #14179 specifically (the oldest, most complete version of this exact request) summarizing your use case — multi-service collation mismatches are a good concrete example that strengthens the case.
2. If Default MySQL collation for new tables/columns #29171 is a near-duplicate of Default MySQL collation for new tables/columns #14179, consider asking a maintainer (or noting in your comment) whether they should be merged, since fragmented duplicate issues are part of why long-standing requests like this can stay under-triaged — engagement gets split across multiple threads instead of concentrating on one.
3. Reference the foreign-key failure mode in Adjusting character/collation set in migration results in table creation being skipped with no error #9796 if your use case involves relations, since it strengthens the argument that this isn't just a cosmetic naming preference — it has real failure consequences in production schemas.

Hope this helps in the meantime — the manual workaround above should at least get you unblocked for new tables while the feature request sits in the backlog.