# Supporting collation settings on multiple database backends? **URL:** \ **Category:** Using the ORM\ **Created:** [March 11, 2022, 11:36pm UTC](https://forum.djangoproject.com/t/supporting-collation-settings-on-multiple-database-backends/12561 "2022-03-11T23:36:37Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![ormsbee](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/ormsbee/32/286_2.png) [@ormsbee](https://forum.djangoproject.com/u/ormsbee)\ **Post date:** [March 11, 2022, 11:36pm UTC](https://forum.djangoproject.com/t/supporting-collation-settings-on-multiple-database-backends/12561/1 "2022-03-11T23:36:38Z")
I work on [Open edX](https://openedx.org/), an open source project that for historical reasons uses MySQL for its production/development backend, and SQLite for running tests. I’m curious if there are established patterns for simultaneously supporting collations for multiple database types in Django code. For instance: - Applying specific character encoding and collation types on an app+database basis, e.g. “always use `utf8mb4` character encoding for my app’s models when running in MySQL”. - Normalizing index/sorting case sensitivity behavior between MySQL and PostgreSQL/SQLite. I want some fields to always be case sensitive, regardless of database, like institution-assigned content identifiers. I want other fields to always be case insensitive, like titles. I know that there’s a [db\_collation](https://docs.djangoproject.com/en/4.0/ref/models/fields/#django.db.models.CharField.db_collation) setting on CharFields now, which seems like it’d do exactly what I would want for one database backend, but wouldn’t work for more than one platform at a time. I know I can force case insensitivity in sorts and comparisons with things like `iexact` or using `Lower`, but my understanding is that would have severe performance penalties (some of our models have to scale out to billions of rows). We currently do some ad hoc detection and switching of behavior in migrations files using `db.connection.vendor`. Does it make sense to try to make new subclasses like `CaseSensitiveCharField` and `CaseInsensitiveCharField`? A separate utility function to call to manually set the collation in the migration after the normal CharField has been created? Are there existing patterns for this kind of thing? Thank you. ---
**Author:** ![ormsbee](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/ormsbee/32/286_2.png) [@ormsbee](https://forum.djangoproject.com/u/ormsbee)\ **Post date:** [May 15, 2023, 3:34am UTC](https://forum.djangoproject.com/t/supporting-collation-settings-on-multiple-database-backends/12561/2 "2023-05-15T03:34:28Z")
Dredging this old thread up because I actually got around to trying to implement this. I created a subclass of `CharField` like: ```python class MultiCollationCharField(models.CharField): def __init__ (self, *args, db_collations=None, **kwargs): super(). __init__ (*args, **kwargs) self.db_collations = db_collations or {} def db_parameters(self, connection): db_params = models.Field.db_parameters(self, connection) # Now determine collation based on DB vendor (e.g. 'sqlite', 'mysql') if connection.vendor in self.db_collations: db_params["collation"] = self.db_collations[connection.vendor] return db_params def deconstruct(self): name, path, args, kwargs = super().deconstruct() if self.db_collations: kwargs['db_collations'] = self.db_collations return name, path, args, kwargs ``` I then invoke it in my model like: ```python key = MultiCollationCharField( max_length=255, blank=False, null=False, db_collations={ 'sqlite': 'BINARY', 'mysql': 'utf8mb4_bin', } ) ``` And that seems to actually do what I want in Django 4.2. The migration file generates and the `sqlmigrate` management command on my migration shows the column is being created like this on SQLite: `"key" varchar(255) COLLATE BINARY NOT NULL` But in Django 3.2, `column_sql` doesn’t check `db_params` for the collation. It instead goes straight to querying the field for its `db_collation` attribute: > Unfortunately, I still need to support Django 3.2 for another half year or so. ☹ I thought I might make a `db_collation` property on my field and have it pick the right thing to send back based on the current connection, but I don’t know how to get at that connection object using only the field itself: ```python @property def db_collation(self): # I have the dict of vendors to collations here (self.db_collations), # but no connection, and so no vendor, so I don't know which one to # send back. ``` I could do something _really hacky_ and set some vendor attribute on my field as a side-effect when the `db_parameters` method is called. That seems… bad… but I don’t know how else to make this work. (Edit: Verified that this hack does “work”.) Anyone have any advice? Edit: In retrospect, I should have put this thread in the “Using the ORM” section, but I don’t think I can move it. Apologies to the moderators. ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [May 15, 2023, 3:51am UTC](https://forum.djangoproject.com/t/supporting-collation-settings-on-multiple-database-backends/12561/3 "2023-05-15T03:51:59Z")
Moved. (No worries, no apologies necessary.) ---
**Author:** ![ormsbee](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/ormsbee/32/286_2.png) [@ormsbee](https://forum.djangoproject.com/u/ormsbee)\ **Post date:** [May 15, 2023, 4:30pm UTC](https://forum.djangoproject.com/t/supporting-collation-settings-on-multiple-database-backends/12561/4 "2023-05-15T16:30:41Z")
Okay, more of me talking with myself (but maybe someone will find it useful one day): I realized we do have a corner of the codebase that actually already does something like this by setting a module-level constant based on settings: > Then using it like so: > One place where it does act weirdly is the migrations themselves, since running it would “bake” the actual value of `DB_COLLATION` into the migration files based on whatever was running at the time the migration was created. But that can be fixed by editing the migration to use the constant instead: > It’s also weird because it doesn’t actually bake the history of the field in. I don’t think there’s any way to detect “it _used_ to be encoding A in migration `0002`, and was then changed to encoding B in migration `0003`”, because it’s just pulling that value from the constant each time.