Setting up MySQL Waterline adapter

Summary

1. Install the MySQL Adapter:
   Run npm i sails-mysql to install the MySQL adapter in your project.

2. Configure the Datastore:
   Update config/datastores.js with the MySQL connection URL:

   mysql://<user>:<password>@<host>:<port>/<database>

   Replace placeholders with your MySQL credentials.

3. Set Up the Database:

   • Open the MySQL CLI or shell.

   • Run the following commands to create the database and user:

     CREATE DATABASE demo;
     CREATE USER 'demo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'demo';
     GRANT ALL PRIVILEGES ON demo.* TO 'demo'@'localhost';

4. Important Gotcha:
   Use mysql_native_password when creating the user to avoid authentication issues with Sails.

5. Test the Setup:

   • Lift your Sails app using sails lift.

   • Test by creating a record in the database using:

     await User.create({ name: 'Demo User' });

   • Verify the data in a database client like Beekeeper Studio.

Transcript

 Hey, so in this lesson of the course, we're going to be setting up MySQL for local development. MySQL is another popular open-source relational database. Let's get into it. Like the PostgreSQL lesson we did previously, we're going to grab the MySQL adapter for Waterline.

To do that, I’m still in the demo project. Let's grab the adapter by running:

npm i sails-mysql

Run that command, and it's going to save the adapter in the project using npm install.

Now let’s open the project. What we need is the adapter, which is sails-mysql, and the URL. I'm going to set it up just like we did for PostgreSQL. Here’s an example:

mysql://<user>:<password>@<host>:<port>/<database>

The host will be localhost, the port will be the default MySQL port 3306, and the database will be demo. So:

• <user> will be replaced with the username,

• <password> with the password,

• <host> as localhost,

• <database> as demo.

Now, replace these placeholders with valid credentials for your MySQL database.

I’m going to go back to my terminal. I have the MySQL CLI (interactive shell) open. To create the database and user credentials, I’ll run the following commands:

CREATE DATABASE demo;
CREATE USER 'demo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'demo';
GRANT ALL PRIVILEGES ON demo.* TO 'demo'@'localhost';

A key thing to note here is the password authentication method. Newer versions of MySQL use a hashing algorithm called caching_sha2_password. However, if you don't specify mysql_native_password when creating the user, you'll encounter an error when Sails tries to authenticate with the database.

So make sure to use IDENTIFIED WITH mysql_native_password.

Now that the database, user, and privileges are set up, let’s go back to the code and test it by lifting Sails.

Back in the terminal, I’ll run:

sails lift

Sails should lift without any issues. Let’s confirm everything is working by creating a new user.

In the console, I’ll run:

await User.create({ name: 'Demo User' });

This should successfully create a user. To verify, I’ll open Beekeeper Studio, my database client. I’ll log in with the same credentials (demo, demo) and test the connection. Once connected, I’ll see the demo database with the user table and the new user record.

 So that's how to set up MySQL in Sails with the MySQL adapter.