Querying SQLite Exercise

[00:00:00]
>> Brian Holt: The only file that you need to modify here is this invoice.js. You're gonna connect to the database here. The name of the file is data.db, so you're basically just gonna say, const db = new sqlite3.database. And you're gonna do data.db, because it is in the same directory.

[00:00:25]
You can also do dot slash, if that makes you feel any better. And now you have a DB object. One thing, I know someone's gonna wanna come in here and say database like this. I don't believe that this will work, cuz I don't think SQLite3 is ES module-aware, if I remember correctly.

[00:00:46]
You might correct me, but I think that's the case.
>> Brian Holt: Okay, your code is gonna go here. And what I want you to do here is you're going to, I gave you the ID that's already coming back from Fastify. So that's this number here. And you're gonna do at least a couple of queries to get everything from the invoice table, and then you're gonna have to get everything from the invoice line table, which is where all of the various different line items and every invoice is.

[00:01:22]
You're then going to aggregate it into an objects that look like this, right? So this is the object I want you to return. You're gonna have an invoice object that's going to have the ID, date, address, city, state, I don't know where that is, was on a different line.

[00:01:44]
State, city, and state for Dublin, I guess for the same thing, country, Ireland. Ireland has no postal code, but if you're coming from Massachusetts, it will, the total, which will be a number. And then it's gonna have the results from the invoice line table, and it's going to be an array of these objects with unit price, quantity, track name, album title, and artist name.

[00:02:12]
>> Brian Holt: Notice that these are not capitalized, so I'm gonna suggest that you might use ads in your queries to get these to be the correct names as well.
>> Brian Holt: And then down here I already send the reply.send. But you're gonna have to populate this invoice object and this lines object.

[00:02:33]
>> Brian Holt: And do I call done for you? I do, so you don't have to worry about calling done.
>> Brian Holt: I would love for you to take a running leap at this, give it a shot. It's likely possible, it is. It's possible to do this all in one query.

[00:02:51]
It would be weird and gross to do it that way, so please don't. I would not write this as one query, I would definitely write this as two plus queries. Likely two, I can't imagine writing three for this. And then let's pop back in here. I think I have to tell you a little bit about how SQLite3 works.

[00:03:11]
If you wanna see what these look like, you can hit the endpoint for invoice complete here, invoice dash complete, and it'll show you what this looks like. I just wanted you to see what a properly formatted query would look like.
>> Brian Holt: This is what it should return to you.

[00:03:32]
In fact, I think I can I just, can I just say in the frontend, and then you can see what it looks like? Invoice-complete.
>> Brian Holt: Yeah, there you go. So if you change it to use invoice-complete instead of invoice, you'll actually be able to see what these things look like at the frontend, they'll look like this.

[00:04:01]
I don't worry about area handling or anything like that, you can assume that they're well formatted queries. Not interested in that right now, I'm really just interested in you querying the database. If you're not familiar with Node, and specifically old Node, which is how SQLite3 works, there are Nodebacks, they're not promises.

[00:04:24]
You can't await answers, you have to use callbacks, right? There is docs here, if you click here, this will take you to the official docs. This particular official SDK or library is maintained by the people at Ghost, which is really cool. So that's there available for you. Again, SQLite3, it's cuz it's version 3 of SQLite.

[00:04:51]
>> Brian Holt: I showed you how to do the connection there already. So the ones that you're probably gonna be most interested in, you're gonna be interested in probably in db.all. That's one that I tended to use, but you can do get, or each, or there's a bunch of different things.

[00:05:04]
Each is basically map for the result set. Get, if I remember correctly, only one line at a time, and you can basically iterate as well. And then you use run for updates and deletes, right? So something that you're firing off a query and you don't care what you get back like you would with an update, you would use run for that.

[00:05:25]
I think in my code, I just used all for both of them. I did, yeah, so up to you. Nodebacks, Node.js style callbacks. If you know what they are, you can pause or fast forward or something like that. But basically, you're gonna give it the query. You're gonna give it, we'll talk about the array here in just a second, but an empty array.

[00:05:49]
And then this function is called a Nodeback or a Node JSL callback, where it's always a function. The first parameter is always if there's an error, and the second thing is the result. Then you can always say, if error, handle all of my errors here. And then if you get past this if statement, then you know that there's no error, and you can keep working on it.

[00:06:10]
So that rows is valid. Back before promises, this was how everything was done in Node. Was pleasant enough, if you're not mad about using Nodebacks. So that's that. This is the code that'll run once the query comes back. Let's talk about SQL injection for just a second because we're writing code now and SQL injection's a very real thing.

[00:06:37]
What is wrong with this query right here?
>> Brian Holt: Well, what's that?
>> Speaker 1: I don't know if you were asking, but [CROSSTALK] I'm just saying it's not parameterized, you could just stick whatever you wanted.
>> Brian Holt: That's exactly right. So this Git ID from user, assuming that a user can type whatever they want there, they give you an ID of drop-table users like this, this would totally work.

[00:07:05]
You can run two queries at the same time if you do the db.get or db.run or something like that. And so if you did this, and they put in something like this, 15 drop-table customer, all of a sudden you have lost your entire customer table, right? This is called SQL injection, cuz this is the query that would actually get run.

[00:07:25]
It would be two queries, the select star, and then drop-table customer.
>> Brian Holt: SQL injection, extremely common. I think I read one time that SQL injection is the most common vector of attack on websites. So how do you avoid this? It's extremely simple. You do select star from track where track ID equals question mark, and then you just provide ID here.

[00:07:50]
And anytime that it finds the question mark, it'll just replace it. And you can be guaranteed that they've covered all of the corner cases. They've done a really good job, it's well tested that you're not gonna get SQL-injected if you do it this way.
>> Brian Holt: Okay, you can also use this way as well where you identify them as names, right, and then you give it an object, and it will replace the objects instead, right?

[00:08:17]
Cuz here if you have five different question marks, it's gonna replace them in order. So you would have five different parameters in there as well.
>> Brian Holt: Both are fine, I use question marks when I have one or two, I use the object for everything else. That's up to you.

[00:08:37]
>> Brian Holt: Okay.
>> Brian Holt: Yeah, never, ever, ever put user input directly into a SQL statement. If you learn one thing from this course, it's that, right? This is how entire companies get ruined. This is why if you go to haveibeenpwned.com, your stuff is everywhere, right? Probably half of it is from SQL injection, the other half is from social engineering.

[00:09:07]
>> Brian Holt: Okay, any questions about the Node.js driver of SQLite?
>> Speaker 1: Again, it does not have promises, which I apologize.
>> Brian Holt: I don't use this one directly myself, I use one that has promises, right, because I like promises. I'm fine if you feel like you wanna rip it out and replace it with something else, totally cool with me.

[00:09:27]
But I wanted you to use the OG because it is still the most popular one. So again, you can click there, that's the file you're gonna edit. You already went through that quite a bit, so you've been explained to that. It's a Fastify app, but doesn't matter. It's written in HTMX and CSS.

[00:09:45]
Again, you shouldn't have to touch any frontend code. I don't care if this is the most optimized code. The code that I wrote was intentionally simple and not the most optimized, so I'm encouraging you to just make it work. For example, if you want to nest callbacks, totally okay with that.

[00:10:03]
You can make multiple queries, you can make single query, all of it's fine. I've installed SQLite3 for you already, so you don't have to MPM-install anything. The point here is to learn and experiment. So give it a shot, and then we will come back and we'll write it together, we'll go through the solution.