Querying SQLite Solution

[00:00:00]
>> Brian Holt: We are gonna write some JavaScript. First thing we're gonna do here, we have the id already, and we're gonna put our code in here. We are already connecting to the database here, database.db, right? And then we're gonna do a db.all.
>> Brian Holt: And I'm gonna write the query first to get the invoice.

[00:00:22]
And so I'm gonna do,
>> Brian Holt: A select statement here. SELECT,
>> Brian Holt: InvoiceID as id,
>> Brian Holt: InvoiceDate as date.
>> Brian Holt: BillingAddress as address,
>> Brian Holt: BillingCity,
>> Brian Holt: As city, BillingState as state, BillingCountry as country, BillingPostalCode as postalcode, and Total as total.
>> Brian Holt: I will say that I find using select star in code to be a bit of an anti-pattern to the point that I think it's almost never right unless you truly intend.

[00:01:36]
I need everything from this query and it's arbitrary, as in maybe that table gets altered and added to and subtracted to frequently and that doesn't matter. You still want everything from the table. One, I have serious questions about what you're doing with your data, but that would be the intent that I would derive from star.

[00:01:57]
I don't approve of it in terms of just being lazy about not wanting to write all of this. I think this is annoying to write, but it's better to maintain. And I always err on the side of maintaining code. Secondly, if you have something like that, you should use a JSON store, which we'll talk about at the end of the course as well.

[00:02:22]
FROM Invoice,
>> Brian Holt: WHERE InvoiceId = ?.
>> Brian Holt: Okay, and then we're gonna take id here. And here, we're gonna take our node error back.
>> Brian Holt: InvoiceRows,
>> Brian Holt: Okay, it's an error function if you've not seen that before. It's just equal and angle bracket, my editor combines them. If err, then you would do your whatever error handling you wanna do here.

[00:03:12]
I don't care, you can do nothing if you want. I think I actually did take the time to say, reply.code(500). And reply.send (error: err, and errorLocation).
>> Brian Holt: Reply.send, I'll just pass back the error. You can do whatever error handling you want here. This was mostly when I was just writing this myself, I was writing it so I could have some better errors.

[00:03:48]
Cuz I'm going write two queries, I'm gonna let myself know this happened with the invoice query, not the other one. This is actually probably not a great idea for a code, you don't wanna tell people what's going on in your code. But for our purposes, it works just fine.

[00:04:04]
So the beauty of a node callback is if we get past this, then we know that invoiceRows is fine to operate with.
>> Brian Holt: So now, I'm gonna say, if invoiceRows.length = 0.
>> Brian Holt: Then, we know we've 404, right? So cuz there's no invoice there, so we're gonna say reply.code(404).

[00:04:41]
And reply.send( error: err, errorLocation: Invoice).
>> Brian Holt: Okay, now, we know we have rows if we'll get past this point.
>> Brian Holt: You also need to put returns in there, right, return and return.
>> Brian Holt: Now, I'm gonna do my second query. Now, I'm nesting queries here. In reality, these two queries could be totally run in parallel, right?

[00:05:19]
In theory, that's faster, but also we're using SQLite where it's so fast. We're talking, I don't even know if we can really time the difference between how slow our nozzle is gonna be. It would all be in the JavaScript stack. So milliseconds, single, they're probably even smaller than that.

[00:05:40]
I don't know, someone should test it and tell you, okay? But I'm just gonna nest them, right? I'm gonna nest them, because that made the code easier to write. So another db.all call. Okay, and en I'm gonna do another SELECT. And we're gonna have to do a join here.

[00:06:01]
We're gonna have to do a few joins, because we want to get all of the artist names, album names, and track names, and the unit prices as well from the invoice list. So we're gonna do i.UnitPrice as unitPrice, i.Quantity as quantity. You could have ignored quantity, cuz I didn't see anyone that ordered the same track twice, but it's in there, so I put it in there.

[00:06:37]
t.Name as trackName, a.Title as albumTitle, and ar.name as artistName, okay? That's all of our selects. We're gonna select FROM InvoiceLine i.
>> Brian Holt: And a couple of JOIN here,
>> Brian Holt: Track t ON t.track,
>> Brian Holt: Id = i.TrackId, JOIN album a ON a.albumId.
>> Brian Holt: Trying to keep this consistent, even if it doesn't matter, t.AlbumId.

[00:07:49]
>> Brian Holt: Again, I don't think you can do natural joins here, because multiple means name, right? So this would be a place where natural joins could seem like they work, but in reality wouldn't.
>> Brian Holt: It's bad pattern, don't do it.
>> Brian Holt: ArtistId = a.ArtistId.
>> Brian Holt: WHERE InvoiceId equals, actually have this in here as without even putting something on it.

[00:08:29]
Again, I think because it's not ambiguous, or they're all joined to each other, I think it's fine, but you might as well put,
>> Brian Holt: InvoiceId as i.
>> Brian Holt: Okay, we've got to pass it our Id parameter,
>> Brian Holt: And you can do the object one as well, that's totally fine, err, trackRows.

[00:09:02]
>> Brian Holt: Then if, err, then reply.code (500), reply.send (error: err, errorLocation). SQLite does not give very helpful messages of what went wrong, just be aware of that. It's very likely gonna be something like SQLite error, but just be aware of that. This is track or invoice line. You can call this whatever you want or nothing at all.

[00:09:43]
You don't have to do any error handling, I just did. Return,
>> Brian Holt: Okay, there's the error, there's that.
>> Brian Holt: And then, now here, we can just say, reply.send. In fact, we can just move this up here, because that's really what we want. InvoiceLine, we want invoiceRows[0], cuz I did all.

[00:10:16]
You probably could do get, I didn't try that, but I'm just doing invoiceRows all. And then it's only gonna return one record. So we'll get the first one and then lines will just be trackRows, because we want all of them. And because up here I named them all correctly, I can just pass them directly back to the user.

[00:10:37]
If you didn't do all these as statements, you'd have to go through and rename them to be all the correct thing. That's why this as aliasing stuff can be super helpful.
>> Brian Holt: And cool, I think, see how we did. All right, I need to go change my index.

[00:10:53]
You shouldn't have to change yours.
>> Brian Holt: But now, make sure my server is running. That's what this Nodemon does for you is every time you save your note code or restarts it.
>> Brian Holt: Do be aware if you're doing SQLite and Nodemon together, make sure you exclude your database file from what Nodemon is watching or any file watcher for that matter.

[00:11:20]
Because every time you change your database, it's gonna restart your database or your app, which may not be what you want. Ask me how I know. Cuz I was doing it when I was developing this class. I was like, why does this keep changing? That's why. Okay, so refresh the page, and looks like we're working,
>> Brian Holt: Like magic.