Summary

SQL Lesson Summary

In this lesson, we focused on creating a SQL table to manage entries.

Key Steps

Creating the Table:
- You can create the table manually or use an online SQL generator, such as Wtools.io.
- The table created is named entries with two columns: id and text.
  - id: Auto-increment, primary key.
  - text: VARCHAR type with a maximum length of 256 characters and cannot be null.
```
CREATE TABLE entries (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    text VARCHAR(256) NOT NULL
);
```
Dropping and Creating the Table:
- Using a MySQL client, the existing entries table is dropped, and the new table definition is executed.
Inserting Data:
- Insert initial dummy entries into the table using an SQL INSERT statement. This step can also be automated via a script, allowing insertion of multiple records programmatically.
```
INSERT INTO entries (text) VALUES ('Sample entry text');
```
Querying the Table:
- A simple SELECT query is executed to fetch all entries:
```
SELECT * FROM entries;
```
Handling Query Results:
- Upon receiving the results, check for errors and return an empty array if no entries exist.
- Log errors for debugging without exposing them to the user to maintain good user experience.
Error Handling:
- If there is a database connection error, the application should handle it gracefully, letting the server remain operational.
- Testing error scenarios such as malformed SQL queries is encouraged to ensure robust handling.

Best Practices

Code Organization: As the project evolves, consider separating routes, database connections, and queries into different files for better maintainability.
User Experience: While handling errors, ensure smooth user experience by not exposing internal server issues to the client.

This lesson lays the groundwork for working with SQL in your application. In the next lesson, we will continue building upon this foundation.

Video Transcript

Welcome back to another lesson. We are going to start doing my SQL stuff now. In this lesson, we are going to build the table to hold our entries. Let's get started. So to build the table to create the table, we can write it from scratch by hand. Or if you don't remember the syntax for SQL, you can always go online and find some sort of online SQL generator. For example, if I go to my browser, does this one, great table online SQL generator at this website, you can see here URL, Wtools.io slash generate dash SQL dash create dash table. So it's pretty simple, you can give the table name in this case, it's going to be entries. And the angel leave the file, the only calling name you have is the ID and the text, right? So we can do, perhaps the ID here, type in is fine auto increment for the primary key, allow no, no. And I think the ski means primary key. So I'm going to check that. Let me let me see what it gives us when I click generate, where's the generate button index is generated right here. Create table entries, correct. ID is not no auto increment primary key ID, that's correct. That's fine. That's going to be the primary key. Now we need the text, right? So I'm going to click add new column, column names going to be text. We can do a bear care. And remember, we had some limits to the length of our entry that would be 256 characters. Allow no, we don't want to allow a blank entry. So I'm going to uncheck that it's not a primary key. Okay, so let's try generate. So now we have ID, we have text bear cat 256 not no. So that's fine. We can copy this. And we can run this statement to create the trade will we're gonna work on. Let me go to my terminal. I have a SQL, my SQL client running right now. And I already had this table that I had created for demonstration, I'm going to drop it the top table entries. And I'm going to run that statement here, I'm just going to paste what I had. And I'm going to do that. Now we can see if we do describe entries, semi colon, I got a new table. If I do select star from entries, I get nothing right at the set. Now can start working with this. So let's see. Let's add some dummy entries. Let's copy the data that we have right now. Remember, we have 123 dummy ones. Let's go to our server.js. See this, this is what we want to eliminate right now, this array of objects. So let's do the following let's try. But we could write some script to take all of these and insert programmatically. That is one way. I'm just going to add one. I'll leave that as an exercise if you want to do it be able to take an array of objects in this format, and generate SQL queries to create the cert those records, you could use the connection. Like we did here, it doesn't even need to be under the express server.js could be a separate file. This is just a script that runs once in and then it finishes. That script would look like the definition of all the entries as an array of objects. Then you call to connect to the database. Then you run the query to insert into entries, whatever. And then that's it. Anyway, I'm going to copy this text. I'm going to my SQL client, I'm going to say insert into entries. And I'm going to say text here, under parentheses values, and LVD. And I pressed weird character there. There you go. And if I do select start for entries, I got the entry here. Okay, so once we hook up the my SQL connection to our end point, we should be able to see only one in the list as we reload the page. So let's get started. Here we have the route right to send all the entries. Now these entries are now in the database. So we need to make a query select start from entries. How can we do that? Let's go here. Remember, we already know how to do the entries here. Before, right? So we try this lines 13 through 19, I'm not going to have this here anymore. So I'm going to cut this. And we are going to face it lines 36, 342. So we are going to use the connection object here, which is global to this whole file. So I can access it from my endpoint. Mind you, of course, that these probably would the way it is right now, we have everything in a single file. But in practice, we want to separate many concerns of our server into multiple files, for example, file, certain files for routes, certain files for the database connection configuration, certain files for the queries, so on. But since we're just getting started, it's fine to have everything in a single file. So keep that in mind. Maybe you can think of a better way to organize your code. So we have connection dot query select start from entries, that's the query we want. Now, once this gets called, it's going to run the query might take some time. After that, it calls this callback function right here. In the callback function, we first check if there is an error. Well, I don't want to throw the error. That's not really a good thing to do in our case here. If there is an error with the query there, well, I can only if we think about it, if there was no there were no entries at all, that would be fine, right? There's no error there. So we could still send the entries here in line 44. That would not be an error. But if indeed we somehow mistyped this query or whatever happened here, you have to think of a way how to mitigate that error. You probably want to log that error in some sort of error message so you can know it can look up later for debugging purposes. As for the user side, perhaps one way we could you could do is just send nothing to the user. And the user is going to think, okay, there's nothing or you could make send an explicit error. But if you do send an explicit error to the user to the client, he's going to know something bad happening in your server. And he's probably going to think that he's going to think that your service has a lower quality. So you have to weigh in your choices, think about what kind of image you want to give to the client. For now, what I'm going to do is I'm just going to arrest dot send an empty array. And I'm going to make sure to return otherwise all the subsequent code is going to get executed. Actually, if I think about this, will this return be so this function, so I got to be careful there actually, this is going to return from this. So be careful with the code after that. So I don't want to have this code outside that function. So everything will lie will continue from the callback, and there won't be anything outside. So let's try that, we have that we got the error, send the error. Before that, I probably want to console error, and say that something bad happened, or I can just error the message or whatever. Some sort of log message. I want to use back tick to interpolate here. I'm going to send empty. And then if everything went well, it's going to go to line 43. Now what we want to do? Well, we're going to get some results, right, an array of rows. Remember, we had some sort of row object, we can access the properties from that. Let's console log and see. Let's see something if I do res dot send results, what what's going to happen. Let's try it out here. I'm going to reload my server. And I'm going to try reloading here. Look at the network tab. So preview. So we get this 200. Okay, seems everything went fine as a response, we got the JSON with that. So seems everything went just fine, right? Let's look at the log for the console. So the console log something an array of row data packet objects. And it seems like during the process of JSON stringify that this object actually was stringify and is to JSON. So we didn't have to do it ourselves. So that's actually a good thing. I was actually thinking about do we need to actually convert this object into a simple plane object that can be used and be converted into a JSON text. So it seems like it's doing it for us. So I'm going to leave it as is. Now if you want to test this error here, we can do so if we stop the MySQL server. Let me show you. I am going to stop MySQL server. Let me exit. I'm going to do sudo so for my case, since I'm using Ubuntu, I can do sudo servers MySQL stop. And I can stop the server, the database server. Okay, let's try the browser. You can see now that we have something going on here fail to fetch. Oh, what happened? Aha. So I wasn't expecting this actually. Server is listening, server is listening on the handle error, the server, the server closed the connection. So I was just hoping that the server would keep would keep on while the actual database would be down, but it seems like it automatically saw that the database went down. So the server also failed. So error connection lost, the reclose connection. We did error event, a connection instance. So it seems like we're not handling this. We should probably handle that. Connection instance. That's something we could reference in the npm package. Let me see here, if I can think of. Next. Let me go to npm. I'm just going to see if there's something, if not, we can just go on. I'm going to look for error handling. Connection require create connection, there you go, connect and function error. Seems like connect takes a callback with the error. So let me see that. So if we pass the function, you can say error or error, whatever you want to call it. Console error, I'm going to do console error. And I'm not going to handle anything. I'm just going to send a message. What I want, what I'm trying to do is make the server still be up while the database server is down. So we can simulate that error handling in our route. I'm going to restart this. I can't because the server is down. So I'm going to restart my SQL server. And then I'm going to restart my server. I said no, where's that from? Is that from console log? I wonder. Let's see my SQL stop. So actually it kept doing it. So this didn't matter. Actually, it's just called back after the connect were. Oh, I see what they're doing. They actually did a wrong connection here. And that's why it called error here. And then my error was no because there was no error. I got it. So it seems like when the connection is properly set, this error will be no. That's why it comes along no. So this gets immediately called after the connection. What I was looking for is just a way to have the server upper running while the database just closed. Actually, I have a better way to test this. How about we mess up our query? That's actually a way for a simpler way. So let's say we did some mistake in the query. And that's surely going to send an error here. And it was going to hit that. Let's try it out. Let's let me restart my SQL server. And then my server here, my API server. And I'm going to go back here. Everything is fine. So it seems. Aha, there you go. So we actually got something right, an empty array of entries. So the client from the client perspective, he didn't see anything wrong. He just thinks there isn't any entry. But from the server perspective, we got a console error here saying, error parse error, you have an error in a SQL syntax. So that's precisely what we have. And this is great. Now from the server perspective, we know there was a problem. We probably could have had some sort of monitor to look and be warned and be alerted that something went wrong, we can fix it. And then the client later on will won't even notice, right? He will notice something is missing. If he was keeping track of the data. But then he, if we quickly fix this, he can try again, if we'll get everything fine, instead of having some sort of really bad error, and bad user experience. Anyway, let's fix that query. And now you have it. We tested this part. And everything's fine for our entries. So for this lesson, that's all and I'll see you in the next one.