Cli: Bug? db:create cannot set default charset with connection information.

[ALiangLiang]

What you are doing?

// config/config.js
require('dotenv').config()
module.exports = {
  development: {
    username:: process.env.DEV_DB_USER,
    password: process.env.DEV_DB_PASS,
    database: process.env.DEV_DB_NAME, //database_dev
    host: process.env.DEV_DB_HOST,
    dialect: process.env.DEV_DB_TYPE,
    dialectOptions: {
      charset: 'utf8mb4',
      collate: 'utf8mb4_general_ci'
    },
    logging: true
  }
}

$ sequelize db:create --env development

What do you expect to happen?

Create a database with charset in config/config.js

Executing (default): CREATE DATABASE `database_dev` DEFAULT CHARSET=`utf8mb4 DEFAULT COLLATE=`utf8mb4_general_ci`

What is actually happening?

Doesn't use this configure.

# log
Executing (default): CREATE DATABASE `database_dev`

__Dialect:__ mysql
__Database version:__ 5.7.20
__Sequelize CLI version:__ 3.1.0
__Sequelize version:__ 4.22.15

[chris-fung]

Met the same problem with yours. Just need to modify the charset and collate by myself after creating db

[galki]

according to this using define instead of dialectOptions works when creating a sequelize connection, but it doesn't work when using cli to db:create

i also tried to set the charset/collation in the model migration like the docs say by adding the third arg to:

queryInterface.createTable(
  tableName,
  { /*... columns*/ },
  { charset: 'utf8' }
)

and that results in error:

ERROR: Can't create table `mydb`.`mytable` (errno: 150 "Foreign key constraint is incorrectly formed")

Sequelize CLI version: 4.0.0

[kwbtdisk]

I avoid this problem by querying ALTER DATABASE from a migration file like this:

/db/migrations/00000-fix-db-charset.js

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query(
        `ALTER DATABASE ${queryInterface.sequelize.config.database}
        CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;`
      )
  },
  down: (queryInterface, Sequelize) => { }
}

With naming the file like 00000-xxxxx.js, we can throw the query just before running other migrations.

[jeanpchr]

+1

[perzanko]

Problem occured for me as well.

[jsyang666]

so finally can I set charset by db:create with config.json now?

[ALiangLiang]

@huangyanyang
No, but you can specify with args. sequelize db:create --charset <CHARSET>

[vishwasraj]

@huangyanyang
No, but you can specify with args. sequelize db:create --charset <CHARSET>

It would have been good. If it was taken from the config file.

[rcupic]

I avoid this problem by querying ALTER DATABASE from a migration file like this:

/db/migrations/00000-fix-db-charset.js

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query(
        `ALTER DATABASE ${queryInterface.sequelize.config.database}
        CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;`
      )
  },
  down: (queryInterface, Sequelize) => { }
}

With naming the file like 00000-xxxxx.js, we can throw the query just before running other migrations.

This solution worked for me. Is there any update on this by sequelize team?

[abhijeet029]

add { charset: 'utf8' } in your migration file after the data column object

[rem711]

I avoid this problem by querying ALTER DATABASE from a migration file like this:

/db/migrations/00000-fix-db-charset.js

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query(
        `ALTER DATABASE ${queryInterface.sequelize.config.database}
        CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;`
      )
  },
  down: (queryInterface, Sequelize) => { }
}

With naming the file like 00000-xxxxx.js, we can throw the query just before running other migrations.

It works for me like that, but if someone is still experiencing the same issue, I needed to use back quotes around database name `${queryInterface.sequelize.config.database}` or it throws a syntax error.