Summary

Summary of MySQL Driver Installation for Node.js

In this lesson, we will install the MySQL driver for Node.js to connect our Node.js server to a MySQL database.

Steps Covered

Install the MySQL Driver:
- Use the command:
```
npm install mysql
```
- This adds the MySQL package to the project's dependencies in the package.json file.
Require the MySQL Package:
- In the server.js file, include the MySQL driver:
```
const mysql = require('mysql');
```

Establish a Database Connection:

When the server starts, create a connection using:

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'mysql'  // Replace with your database
});

Execute Queries:

Use the connection to execute a query:

connection.query('SELECT * FROM user', (error, results) => {
    if (error) throw error;
    console.log(results);
});

Error Handling:
- Handle potential errors from the database connection and queries to debug any issues.
Connecting to the Database:
- It may be necessary to handle authentication errors if they arise, potentially indicating version compatibility issues with MySQL.
Environment Variables:
- To avoid hardcoding sensitive information like your MySQL password, consider using environment variables.
Troubleshooting:
- If facing issues like "Client does not support authentication protocol requested by server", check the MySQL community for solutions or consider using a different version of the MySQL package.

Conclusion

Though issues may arise during setup, understanding the connection process and referring to documentation or community support will help in resolving them. In the next lesson, we may explore using a more updated package if necessary.

Video Transcript

Welcome back. In this lesson, we are going to install the MySQL driver for Node.js. So we got MySQL server running. We verified using the MySQL command decline. Now we need a way to connect to the database using our server, Node.js server. One way of doing that, we need what's called a driver for the database of our choice, MySQL. NPM has a package called MySQL, which is the same name as the database. If you go to npmjs.com.slash package slash MySQL. This is a package that we can use to connect to the database and perform queries. There's a lot to this, but let's get started. Let's go to the terminal. First, I'm going to say npm install MySQL. It should add an entry to my dependencies in my package JSON. Let's verify. Now it's here. Line 17. Now with that, we can use it from our server.js. Up here, I'm going to say const MySQL equals require MySQL. Now we can use this and first we have to do, what we have to do is connect to the database when the server is started. You can reference the guide here in their page. They say how to do it. You can see right here after requiring it, we can call the createConnection function, pass it an object with options. Those options will contain things such as the host, user password and database. Right now we do not have a specific database for our application. We are going to create it later. We have a local host. It's going to be our host here for development. User, we can use root and you can use the password if you have root. Then you can call the connect on the connection object that was created and you can execute the query here. Then connection in. Let's try it out. Let's go to the server code and have this open at the same. I got that, express and so on. I can do it anywhere after line five. You choose perhaps I want to do before the server or after. I don't know. Let's try here. Call my SQL.createConnection. We are going to create a connection to our database. Pass an object as the argument. Now they are going to need a host. We are going to give local host for development. Another key is the user. We can use root for development in our case. Password will be whatever you type. I am going to defer that. We don't have a database right now. Let's use something that is already there. If I say to do my SQL and I say show databases, there is some information scheme or whatever. Let's say my SQL is the database. Let's try that. With that, we can say, we have to start this object in a variable. Let's say, const connection. Mind you they are using ES5 var. That's fine. You can use that. But I am using ES6 const. I don't need to redefine connection again after my first assignment. That's why. There is also lat and so on. We have a connection. Let's connect. Let's try a query. Let's check the tables here. Describe my SQL. I don't think that's a... Not described. Use my SQL. Now we can see show tables. We have a lot of tables and so on. I don't know. Let's try something. Select star from slow log. Nothing. Select. ID from user. I don't even know if ID... Let's see what's in user. Describe user table. Got a lot of stuff. User table has a lot of fields. As a field user. OK. Let's try something. Select user from... What happened there? Select user from... MySQL where user root. Select user from... MySQL where user root. What was the name of the table? I messed up here. The table is called user. .user. There you go. We can use this query as a sample example to see if it's working. I'm going to copy this. I'm going to copy this. Let's see. We got this query. We're going to get some results. Let's see the way they do. They're using... We got the query first and they have a callback function here. Right up. Second argument will be a callback function. We can use the error function or function doesn't matter. Error, results. What was the other thing? Fields. If error... We want to handle the error whichever way. Anyway, I'm going to console log whatever this is. What is solution on a rule? OK. So select one plus one add solution. Probably the table. Let's just do the results. We got that. Make sure to close the connection. The server dies. I don't have a mechanism right now. Do I? Let's do this. I think this might be asynchronous? I don't know. Let's see. I want to see if it reaches this line. We got a connect query and so on and so on. Let's see. Connection here. Let's try it out. So node monority restarted. Let me do it again. Client does not support authentication protocol requested by server. Consider upgrading my SQL client. Not supported. OK. Let's see. I think there might be a problem with the NPN package. I'm not. I'm just looking. When you get this kind of problem and you don't know what to do, you want to look in their page. Usually there's a page where you can ask questions. You can see other people's questions. In this case you can go to their homepage here. And you can go here to issues. And you can copy and paste the error you got. And you press enter and you can see there's some open issues and some close. You want to see what's going on. Let's see the close ones. Try this. Duplicate of this. Let's try my SQL 8. Although I don't think I'm using my SQL 8. Let's do the service. SQL status. My SQL community server. My SQL. This is the client version. My SQL. I don't know if you have server. I don't think they have a command. They have a command. Okay. Let's go to ATC my SQL. ATC my SQL. Nothing here. Okay. Let's backtrack here. Go back to the package website. Perhaps it's asking for my password. I don't know. Let's see. So usually you don't want to put the password in the code base. That's pretty bad. Usually you can store that in environment variables. So I'm going to do just that. I'm going to save my SQL. My SQL. Password. Oops. So I'm going to define this variable here. You can do so like this. If it's temporary you can save my SQL password. It's whatever. If you want to make it permanent you can copy this and add to your bash profile whatever terminal you're using. ZSHRC bash profile bash RC whatever you use. So I'm going to do this while here. So give me one moment I'm going to open a new window and I don't want you to see my password. Let me do... ... ... ... ... ... Alright I'm going to source my bash RC. Let's try again. I don't know if that's related at all. So still the problem. ... ... ... Maybe we're going to have to use a different npm package that's more updated in my SQL 2 I think. Maybe. Let's try to add it in the next lesson. See you then.