db-migrate / db-migrate/mysql

Incorrect charset collate ordering

Open
#40 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
JavaScript
Stars
51
Forks
37
PR merge metrics
No merged PRs in 30d

Description

According to MySQL documentation, `COLLATE` must appear after `CHARACTER SET` https://dev.mysql.com/doc/refman/8.0/en/charset-table.html

Currently this module has it the other way around. This means when attempting to specify a collation and character set like so:
```js
await db.createTable('test_table', {
columns: {
id: { type: 'int', notNull: true, primaryKey: true, autoIncrement: true },
},
charset: 'utf8mb4',
collate: 'utf8mb4_unicode_ci'
})
```

The following SQL is generated:
```sql
CREATE TABLE `test_table` (`id` INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL) COLLATE 'utf8mb4_unicode_ci' CHARACTER SET utf8mb4
```

This results in the correct `utf8mb4` character set, but with a collation set to the default schema `utf8mb4_general_ci` not `utf8mb4_unicode_ci`

Correct SQL generated:
```sql
CREATE TABLE `test_table` (`id` INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL) CHARACTER SET utf8mb4 COLLATE 'utf8mb4_unicode_ci'
```

@wzrdtales would you be open to a PR to resolve this? Looks like `spec.collate` and `spec.charset` need swapping in `_applyTableOptions`

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.