mysql package auth protocol not supported by MySQL 8

[matthewvolk]

Description

Running npm run db:setup when attempting to connect to a MySQL 8 database throws the following error:

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

The error above is best explained by: mysqljs/mysql#2233

TL;DR: The mysqljs/mysql npm package does not support new authentication protocols introduced in MySQL 8. Until the PR above is merged, users will continue to receive the error above. The sample-app-nodejs documentation does not call out that MySQL 8 is not supported, which results in confusion from consumers of this app.

Workarounds (Do Not Use in Production)

NOT RECOMMENDED FOR PRODUCTION, POTENTIAL SECURITY RISKS: https://stackoverflow.com/a/50131831/8211101

As explained by https://stackoverflow.com/a/56509065/8211101, running the command in the SO answer above negates the benefits of using MySQL 8's more secure authentication methods.

Potential Solution (Needs Investigation)

Until the mysqljs/mysql PR is merged to support MySQL 8's new pluggable authentication methods, the mysql package will continue to throw this error when trying to connect to a MySQL 8 database.

The mysql2 npm package (https://www.npmjs.com/package/mysql2) shares the same API as the mysql npm package, and does support the new authentication methods. This could be researched as a potential option to replace mysql. If mysql2 is backwards compatible with older MySQL versions, that would be ideal. Otherwise, we might need to call out that mysql should be used for MySQL < 8 (need to verify which version mysql breaks on), and that mysql2 should be used for MySQL 8+.

[matthewvolk]

To build on #129 (review), an ORM would also give developers the ability to handle database schema migrations more gracefully — this PR could be an opportunity to introduce an ORM.

Since this app is intended to be forked and eventually ran in production, providing developers with a solution for handling database connections and migrations would be awesome. Two Next.js-compatible ORM's that I've had great experiences with recently are:

Prisma: https://github.com/prisma/prisma Pros: Really love the typesafety that comes with their prisma/client type definition generation based on the database schema defined. Great documentation around deployments, unit testing, integration testing. Comes with Prisma Studio, a GUI tool that runs on localhost and can be used to view data across tables. Cons: Comes with Prisma-specific files such as schema.prisma, introduces a slight learning curve. Have heard it runs slow at scale, but have not personally experienced this.

Drizzle: https://github.com/drizzle-team/drizzle-orm Pros: Lightweight, can run on the edge, does not come with package-specific files, and most importantly is able to take complex queries and generate only a single SQL query behind the scene. Cons: Relatively new, documentation not as robust as Prisma (missing docs for unit testing, etc.).

[bc-chaz]

couple of quick notes: first, it appears that this is largely an issue with mysql 8.0.4+ as noted by mysqljs/mysql#2233. I only mention because I was running 8.0.3 locally and did not run into this. Second, one potential work around is to enable or convert the password to a legacy encryption, which arguably might be best suited for development purposes. Finally, it looks like the mysql2 package is backwards compatible and is able to be used as a drop-in replacement for mysql package that we were using - I've opened up a PR here.