Post/Code

HomeAboutUsesNow

MySQL and TypeORM.

Below are a few thoughts and notes on using MySQL with TypeORM.

MySQL

  • The default location for MySQL installation (on Mac) is: /usr/local/mysql
  • If you get an error when running: mysql --version it likely means you do not have mysql added to your $PATH. Check this by:
  • echo $PATH
  • If it is not there, you will need either add it to your session via: export path=$PATH:/usr/local/mysql/bin
  • Or you can add it to your .bash_profile file, this is typically located at: /usr
  • In order to start your MySQL session, in terminal, run: mysql -h hostNameGoesHere -u usernameGoesHere -p
  • You can start or stop your MySQL server from System Preferences.
  • To access a specific database, you can run: mysql -h hostNameGoesHere -u usernameGoesHere -p databaseNameGoesHere
  • In MySQL terms, Schemas and Databases are synonyms.
  • You can install MySQL Workbench separately as a GUI for interfacing with MySQL. (I tried using DBeaver, but I had some issues with the MySQL Driver, so have not got it working yet.)

TypeORM

If you run into the following error when trying to connect to MySQL from TypeORM:

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

The reason this happens is explained in detail here, but in short, MySQL 8.0.4 introduced a new default authentication mechanism 'cachingsha2pasword'. This is more secure than the previous method used by MySQL, which is 'mysqlnativepassword'. Now, TypeORM, under the hood, uses mysqljs (I think). Work is being done in order to support this new authentication, however, as of writing, it has not been merged into master. As such, in order to get around this, on MySQL, you need to do the following: 

ALTER USER 'userName'@'hostName' IDENTIFIED WITH mysql_native_password BY 'passwordHere';
SELECT plugin FROM mysql.user WHERE User = 'root';

Thereafter, you need to run (from the MySQL terminal):

  1. FLUSH PRIVILEGES;
  2. quit
  3. Stop MySQL server.
  4. Start MySQL server.

You should not be able to run TypeORM again and hopefully it will connect. If not, you may find some guidance by reviewing the following:

TypeORM and Nest.js

Ultimately, I dug into all of the above in order to add a MySQL database to my Itemly API. The work-in-progress code is here.