Keep text in UTF-8 at every layer

How to do it

One layer in another encoding turns "Åsa" into "Ã…sa" or loses an emoji, and the damage stays in the data. In MySQL and MariaDB, utf8 is an alias for utf8mb3, which holds at most three bytes per character. Set the server, database, tables, columns and connection to utf8mb4. Create PostgreSQL databases with ENCODING 'UTF8'. Send charset=utf-8 in every text Content-Type, and declare it first in HTML, as Declare UTF-8 first says. Read uploads and imported files as UTF-8, and reject invalid bytes with a clear error instead of guessing. Write CSV files meant for Excel with a UTF-8 byte order mark, because Excel otherwise opens them in an older code page. Never put a byte order mark in JSON, which RFC 8259 forbids.

Examples

SQL example

-- MySQL or MariaDB
ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Python example

import csv
# utf-8-sig writes the byte order mark Excel needs
with open("customers.csv", "w", encoding="utf-8-sig", newline="") as file:
    csv.writer(file).writerows(rows)

JavaScript example

// Node.js: Excel reads the file as UTF-8 only with the byte order mark
res.type('text/csv; charset=utf-8').send('\uFEFF' + toCsv(rows));

How to check

A round-trip test sends "Åsa Ødegård 😀 中文" through a form, the API, the database, an export and an email. Every copy reads back byte for byte the same. A schema test finds utf8mb4 on every text column, and the CSV export starts with the bytes EF BB BF.

Sources

• WHATWG Encoding Standard
• RFC 3629 (UTF-8)
• MySQL – The utf8mb4 Character Set

Related rules

• WEB-3 Declare UTF-8 first in Website