Database & Exercise Setup

[00:00:00]
>> Kyle Simpson: All right, well moving forward here in the course, we're gonna turn our attention from strictly just building a command line utility dealing with files, to trying to use databases to persist data. So we're gonna look, if you'll go ahead and open up exercise four, EX4.js in your exercises folder.

[00:00:20]
I've already got a different file kind of filed out with some of the basic information we're gonna need. And then we're gonna be writing some SQL to do some database query's. So hopefully you're at least roughly familiar with some SQL. If not, we'll walk through how that works, so don't feel too intimidated.

[00:00:37]
But our goal here is to use a database in a very simplistic way. This is not a complex database but we just wanna show the idea of how we can store some data in and how we can pull some data out. And the database that I chose to use is SQLite, specifically SQLite3.

[00:00:56]
You'll notice that I am requiring that package here cuz we're gonna use that. The reason for my choice of SQLite 3 is because it doesn't require a separate database program running on your system. SQLite by virtue of the name is a sort of strip down but not, it's still very powerful.

[00:01:16]
But sort of a strip down environment where the file is maintained directly by your application. So there's gonna be the SQLite3 module literally just modifies the file directly. You don't have to like talk via the network to some other big program like a SQL server or Oracle, or PostgreSQL, or something like that.

[00:01:35]
And it keeps them in flat binary file format locally right where you're working. So there's a lot of advantages to it, if not having to administer a whole separate server. It's not the kind of database where you're gonna run that on a separate machine and have 15 different master and 15 slaves, and bunch of replication.

[00:01:55]
That's not what you're gonna be doing with SQLite. But if you just need to build an application, persist a little bit of data and have sort of a relational SQL access to it. SQLite is actually a really strong powerful option, and you might think it's just for demo purposes.

[00:02:10]
And it's certainly is convenient for us so that you don't have to go and install your own database. But it is used, it is production quality, and is used in a lot of places. In fact, web browsers have SQLite built into them, and they literally use them for their data, for some of their data storage and so forth.

[00:02:28]
So it's actually a really strong, I've launched a number of production websites based entirely on SQLite, and would do so unreservedly. Obviously, I wouldn't run Amazon on SQLite, but if you're running just kind of a basic one-off website and you needed a simple database, SQLite's a great choice to look at.

[00:02:48]
>> Kyle Simpson: We wanna make sure that SQLite is installed, so unlike the other packages which were already included in your exercises distribution. We need to actually make sure that SQLite is installed. And it's because it uses a binary node extension, so your particular operating system and version of node needs to be built.

[00:03:07]
So if you issue the command npm install sqlite3, you're gonna see that mine prints out something a little bit different because I've already got it installed. But if you run it, you're gonna want to install SQLite, and it should at the time anyway, right now it's gonna install that's version 4.0.7.

[00:03:27]
So you should get something roughly like that for the installation of SQLite. And by the way, make sure you install it both in this local directory as well as also in the solutions directory so that it works correctly for you. Okay, so with SQLlite installed, one other little thing to point out I've got this package already installed for you but it's commented out.

[00:03:50]
If you're using a version of node that's beyond version say, I think 10.2 or above. Then you already have this method called console.table built in for you, but 10.1 and before, or somewhere around in those early ten versions. It didn't have that in this package, it was kind of a user land package for patching that.

[00:04:10]
So if you end up running this exercise later and you don't have a console.table because of your version of node, then just simply uncomment that package and use it. Otherwise we'll use the built-in one, orienting you to this exercise a little bit. This exercise file, you'll notice that I'm setting up a couple of paths.

[00:04:29]
Here's one for where I'm gonna store my database, and here's one where I'm gonna store the schema for my database. So I wanna open up that file for just a moment. If you look at the MyDB.SQL, I have all of this stuff commented out. We need to actually uncomment this code so that it will run these particular statements.

[00:04:52]
So if you uncomment all of that, and then save that file, and then you're gonna be able to use, we're gonna load these contents up. And we're gonna execute them against our database. And I've written the SQL in such a way that with the IF NOT EXISTS clause.

[00:05:09]
So that it can rerun over and over, and it doesn't clobber the existing tables if they're already there. So it's just sort of an initialization SQL that we want to run. So go ahead and save that file so that we can use it.
>> Kyle Simpson: And then you'll notice that in our function, I've got a setup here where I am opening up our database.

[00:05:35]
That's initializing it in our memory here. So it's going ahead and creating a database file there, with all the basic things that the SQLite database needs. But it doesn't have any of our SQL stuff in it. And then I'm setting up some helpers here. And I wanna point out what's happening with util.promisify.

[00:05:55]
So each of these methods that is exposed by the SQLite extension, they're callback expecting functions. But I wanna work with promises because it's a cleaner interface for a synchrony. And so node ships with a utility called util.promisify where you can pass it a function that expects callbacks, and you get back a function that gives you promises.

[00:06:17]
It literally just wraps it in a promise creating interface. So that's what I'm doing is just making promise interfaces. You have to do a little bit of extra work with the run, which is what this code is about. But basically, I'm making promise based interfaces around my SQLlite 3 APIs.

[00:06:32]
The four methods that we'll use are run, get, all, and exec. Okay, so first thing is we need to load initSQL. The SQL from that file, we need to load it, and because we're just gonna do a single start up here, I'm gonna go ahead and use readFileSync.

[00:06:50]
But earlier in the course, we talked about how you could've done this with asynchronous file reading as well. Since it's a small chunk of text, should be no performance difference. We need to execute that initSQL against our database. And remember, since it's written in such a way that it can be re-executed, we just basically do it all the time.

[00:07:11]
So we have our SQL3, name, space with out helpers, specifically, we wanna call exec and we're gonna pass in initSQL. And that is an asynchronous operation, we're in an async function, so we need to go ahead and wait on it to finish, so do a wait in front of it.

[00:07:32]
And that will make sure that we've initialized our SQL.
>> Kyle Simpson: Now what is the substance of this exercise? What are we gonna be doing with this exercise? Well, we're gonna make a simple little command line app that when we run it, we can pass in a value as a command line parameter.

[00:07:54]
It takes that, it generates another value and it sticks those two values related with keys into a database record. And so we can add a new record each time we call the program, and then dump all of the current content. So real simple, basic, but it shows you the ins and outs of persisting and retrieving data in a relational way from SLQLite database.

[00:08:16]
So, I got real creative with the naming here. The two values that we're gonna be putting in are called something and other. The something is gonna be a random number. And other is gonna be the value that you're gonna be supplying at the command line. If we scroll up a little bit you'll see that we're expecting you to pass a --other with some value in it.

[00:08:37]
That will be used along with the randomly generated number. If we go back to our schema you'll notice exactly what's happening here is we have a something table, which is gonna hold something values. Those are the randomly generated numbers. And then we have an other table, which stores the normalized.

[00:08:56]
It only stores each unique other value that you've ever passed. So it's relating these two through the foreign key. So if I kept calling it with foo over and over again, there would only be one record in the other table. And a bunch of related records in the something table.

[00:09:13]
[COUGH] So, if we've provided an other and a something, how are we gonna get those values into our database? I'm gonna go ahead and comment out this oops part for just a moment. We'll put that in at the end just to make sure that we're not missing anything.