# Understanding Database Collation in Rails

You've likely seen **collation** settings in your schema.rb or database.yml many times, but do you know exactly how they impact your application?

In a standard Rails application, you might see this in a migration:

```ruby
add_column :users, :name, :string, collation: "utf8mb4_unicode_ci"
```

Or in your database.yml:

```yaml
encoding: utf8mb4
collation: utf8mb4_0900_ai_ci
```

Let's break down what collation actually is, why it matters, and which settings you should use in a modern Rails environment.

## 1\. The Core Concept: Character Set vs. Collation

To understand collation, you must distinguish it from character sets.

* **Character Set (The Container):** Defines which characters you can store.
    
* **Collation (The Rulebook):** Defines how those characters are **compared** and **sorted**.
    

**Example Scenario:** Imagine you have a list of names: \["Amit", "amit", "Ámit"\].

* `utf8mb4_unicode_ci`: Case-insensitive. It sees all three as identical matches for a search.
    
* `utf8mb4_bin`: Binary comparison. It sees three completely different values.
    

## 2\. Why Collation Matters

Collation directly dictates how your database engine (and by extension, Rails) handles data retrieval and organization.

1. **Search Behavior (WHERE clauses):** If a user searches for name = 'AMIT', the result depends entirely on collation.
    
2. **Sorting Order (ORDER BY):** Sorting multilingual content requires language-aware rules (e.g., how to sort accented characters like ñ or ö).
    
3. **Data Integrity (Joins):** Joining two tables with different collations can cause SQL errors or severe performance degradation because the database cannot reliably compare the keys.
    

## 3\. The "Types" of Collation

When looking at a MySQL collation string (e.g., `utf8mb4_0900_ai_ci`), the suffixes tell you the rules used:

* `_ci` (Case-Insensitive)
    
* `_cs` (Case-Sensitive)
    
* `_ai` (Accent-Insensitive)
    
* `_as` (Accent-Sensitive)
    
* `_bin` (Binary)
    

## 4\. Best Practices: Which to Use When?

For a modern Rails application (Rails 6/7+) running on MySQL 8, here are the recommended strategies:

### ✅ The Modern Default

**utf8mb4\_0900\_ai\_ci**

* **What it is:** The modern Unicode standard. It is accent-insensitive and case-insensitive.
    
* **Use case:** General storage for names, descriptions, and comments where you want "cafe" to be found even if the user types "café".
    

### 🔍 Searchable Text

**utf8mb4\_unicode\_ci or utf8mb4\_0900\_ai\_ci**

* **Use case:** Usernames, emails, and addresses.
    
* **Why:** Ensures a smooth user experience. A user searching for "STEPHEN" should generally find "Stephen."”
    

### 🔐 Strict Security & Tokens

**utf8mb4\_bin**

* **Use case:** Password hashes, API tokens, invite codes, or case-sensitive identifiers (e.g., a URL shortener code where AbC is different from abc).
    
* **Why:** You need an exact, byte-for-byte comparison.
    

## 5\. Implementation in Rails

### Global Default (database.yml)

This sets the baseline for any new table or column created.

```yaml
production:
  adapter: mysql2
  encoding: utf8mb4
  collation: utf8mb4_0900_ai_ci
```

### Column-Level Override (Migrations)

Use this when a specific column requires different behavior than the database default (e.g., a strict token field).

```ruby
class AddTokenToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :api_token, :string, collation: "utf8mb4_bin"
  end
end
```

### Fixing Existing Data

If you have "broken" sorting or emoji support, you cannot just change the config file. You must migrate the actual data table.

```sql
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

## Summary

* **Character Set** is for storing data; **Collation** is for comparing data.
    
* Inconsistent collation leads to join errors and weird search bugs.
    
* **Recommendation:** Start your projects with `utf8mb4_0900_ai_ci` as the default.
    
* **Exception:** Use `_bin` (binary) collation only for fields that require strict exact matching (tokens, hashes).