SQLite

[00:00:00]
>> Jem: We're going create a database using SQLite. We're going to write through it and we want to write a visitor log for a WebSocket server. Remember that, how we're just console logging data, but it's not really going anywhere. What we seem to a database that way later with Aquarius.

[00:00:18]
So we're gonna be using something called SQLites. Sqlite, in my opinion, is like one of the best databases around. It's not the most powerful, it's not the fastest, but Sqlite is the most commonly used database because it works on any platform. You don't have to install a separate service or anything.

[00:00:41]
You attach the database to your application. Versus any other database you have to create a separate service you have to maintain that with passwords and all these things. So sqlite is portable, it can just attach it to your application, use it anywhere. That's what we're gonna use it today, if we use something like MySQL, we had set of MySQL and then we'd set up password and it'll be a whole thing.

[00:01:02]
We don't want to go there today. So we're gonna use SQLite. So you can do this on your server, you can do this on VS Code and let it pull in. But first thing you want to do is npm install sqlite3. So I'm back in VS Code, I'm in that index.ws or dash WSJS file that express server we created With WebSockets.

[00:01:23]
Now, we're on the database portion. So I want to just make sure I have a note of that, so end WebSockets, just to make it easier to reason about a little bit later. And we're going to say begin database.
>> Jem: Then I wanna make sure I have sqlite installed, cuz I always make this mistake.

[00:01:52]
So npm i sqlite3.
>> Jem: Okay, everybody here so far?
>> Jem: So we didn't do much. We only npm installed sqlite3 so far.
>> Jem: All right, and I will show you how easy it is to use this database versus, well, you won't know about the other ones because we're not going to go into MySQL, Redis or anything like that.

[00:02:31]
But sqlite is just a delight to use. So, first thing we have to do is we have to import the library. So we're gonna say imports or cons, sqlite equals require.
>> Jem: sqlite3, and we're just gonna set up a new database. So we're gonna say const db equals new sqlite database, and it's a database.

[00:03:05]
And for this one, we're gonna put it in memory. So we just say call in memory,
>> Jem: Colon,
>> Jem: So that's pretty useful. A lot of times you're gonna create a file. If you want to say, write this actually to the hard disk as a file, you would say./.fullstackfrontend.db or something like that.

[00:03:39]
>> Jem: It doesn't have to be a.db file. It can actually be anything. The.db is just to let us know it's a database when we see it a little bit later. But for now we're going to use memory just to make things a little bit easier to reason about, but it's really easy to use the file as well

[00:03:59]
>> Jem: Okay, yes.
>> Student 1: Because we're doing it in memory whenever we restart the server, the database will be scrapped from scratch.
>> Jem: It'll be gone. That's correct, yeah. And you're saying, hey, for writing these a memory, why don't we just use a POJO a plain old JavaScript object to save save memory that you can do that to?

[00:04:16]
The challenge with that is the database will be easy to query because we're saving things in a structured way. You can do the same thing with a Say, like a map or a set in JavaScript and make it really structured, but the amount of code it takes to get that structure down right?

[00:04:30]
It's just easier to write a database in this case. But yes, when you write things in memory, it's gonna be the fastest way of doing things, but it'll be gone whenever we restart the server. Good call out. So next thing we wanna do is we actually need to set up a table.

[00:04:45]
And we're gonna set up a table every time the server starts again cuz it's all written in memory. So we don't have to do any checks if the table exists. So we're gonna say db.serialize, that's just a function. We're gonna close it off here.
>> Jem: And serialize command just ensures that the database is set up before we run any queries.

[00:05:12]
So it's going to run this first and make sure the tables are set up. So that way we're not trying to write to a table that doesn't exist just yet. So next thing we need to do, we're gonna write some SQL. Hang on, hang on to yourself. We're gonna write some SQL.

[00:05:25]
It's gonna look a little weird, but it'll make sense of the time. We're all done. We're just gonna add a little bit of SQL. Not enough to make your head hurt hopefully. So whenever we wanna run a SQL command, we need to use db.run and that's gonna let us write SQL.

[00:05:42]
And I'm gonna put the back tick in here because it's gonna be a multi-line string just to make it easier to read. It can be one line. I like to be easy to read. So we're gonna say create table.
>> Jem: And the actual SQL commands, by convention, you write them in Caps.

[00:06:00]
So you understand the difference between what's input and what is actually part of the programming language itself. And we wanna create table called visitors, cuz we're logging the visitors that come to our site. So to create visitors, we need to create the actual fields. Remember, fields were the columns.

[00:06:19]
So we're gonna put a parenthesis there. I'm just gonna make this a new line to make it easier to read. And we really only need two things for this really simple table. We need the counts. So we want to count how many people are here. And we have to give it a type, because SQL is structured, we're dealing with relational databases here.

[00:06:40]
So data's always structured and always has a type.
>> Jem: And we probably want to know what time things are happening. So time in SQLite is just a text. It's nothing fancy. If you're using, say, MySQL, the time has its own type, which is useful for running built-in commands to make it easy for querying types, but having do any sort of conversions.

[00:07:03]
But SQLite is pretty bare-bone, which is what we want in this case.
>> Jem: Yeah, question.
>> Student 2: Could we also use time like a Unix timestamp or the JavaScript now command, that would give us like an integer value of time?
>> Jem: Yeah, however, SQLite has that built in into it.

[00:07:26]
So we can just say now and it'll write it in time as a text format, which is pretty handy. But yeah, you can use JavaScript now, et cetera, but databases almost always have their own way of dealing with time. Because anybody's ever had to deal with like leap years or what's it called like leap seconds.

[00:07:45]
All these like nuances or my mind is blinking on time zones. Time Zones is like a really hard problem in computer science because like I wrote a database at 5 o'clock on Eastern Time. But I'm reading it as a server it's written in West, and I'm in California reading it.

[00:08:07]
What time did the thing actually happen? The time when it is written, or the place where the server is at, or the place where I'm at. It's a whole hard problem. Google has an entire engineering team dedicated to just dealing with time because it's that hard of a problem.

[00:08:20]
And most people just use UTC, and that's what probably we should all use, but everybody's got to have their own timezone now. That's a whole different topic. So we have this command to create a table, and it's gonna have two columns, count in integer or accounts and time.

[00:08:38]
And the serializer is just gonna make sure our database is set up before any queries are ready to go. So now, I'm gonna create a shorthand function so I don't have to repeat my SQL queries over and over. I'm gonna call it getCounts. And this will just query my visitor table to let me get the counts.

[00:09:00]
That way I'll have to rewrite select star from visitors, etc. So we're gonna use that DB command again. This time we're gonna say each, because we're tryna get the output of every single row. We wanna count that together. And I wanna know how many visitors were at every single time interval that we specify.

[00:09:17]
So we're gonna say, DB each, which is gonna say, every row, do something. So DB each, and we're gonna write a start function, old habit. We're gonna write a basic query here. So we're gonna say select star from visitors. Remember, select star is just that wildcard. Give me every single field in that row.

[00:09:41]
I know I can't play visitors, visitors.
>> Jem: I promise I can type. And the second argument is gonna be a function. You can call the function to an arrow function, doesn't really matter.
>> Jem: And the first argument is gonna be error. We don't really have to deal with that too much.

[00:10:06]
But really what we really want is the row. And we're just gonna log the row, oops.
>> Jem: Yeah, not bad. And this saves us from having to write the SQL query every single time. We just wrap it in a function. There's also something called prepared statements, we can save these and just execute them later.

[00:10:36]
That's almost always what you do. You never, never, never wanna write freehand SQL or allow people to write freehand SQL like your clients. Why is that?
>> Student 2: Drop your tables.
>> Jem: Yes, if someone has access to your database, that's almost as bad as access to your server. They can drop tables, they can read all your data.

[00:10:54]
In fact, it's even better cuz they don't have to get into your SSH key and read access, all that mess, you let them into your data storage. So we never run SQL directly, this is the learning experience here. We always have prepared statements. So that way, it's a finite set of instructions that people can run.

[00:11:11]
When it comes to MySQL and Redis or Mongo or some of the other databases, there's always user permissions, what people are allowed to do, similar to the Linux versions. But SQLite has none of that, because we don't need to go that far. It's a simple example. And that's why it's the best database, cuz it's really, really simple.

[00:11:28]
But in real life, database security is very, very, very, very important.
>> Jem: The last command we're gonna run is, we're gonna call this one shutdown DB.
>> Jem: And we're writing a function here.
>> Jem: Because every time we use a database, we need to close it by the time the server is all done.

[00:11:54]
We never wanna leave a database connection open because it'll just stay open persistently. Then you're gonna run out of connections to your database. So whenever we're done, the server's done, we close our database connection. The same way we do when we wrote a server, we ended that request because a server doesn't know when you're done with it.

[00:12:11]
So we have to be very explicit here. So here, before we shut down, I wanna say, give me the final counts for the day of all the visitors to my server. So we'll just call it getCounts, which will console log out all the rows. And then we'll add a nice little helper here, a nice little log that just says, shutting down, oops.

[00:12:41]
>> Jem: Db.
>> Jem: And then we just call db.close.
>> Jem: But when we call shutdownDB, we see cuz there's syntax highlighting it saying, hey, you wrote this function, you never call it. All we need to do is, we need to catch the interrupt signal. So that's the Ctrl+C is known as a signal interrupts through our process, and you'll see a call to seconds.

[00:13:05]
And we just need to catch that and then call shutdown command on that. Other than that, the server doesn't know when it's getting killed cuz it's just it just stops. That'd be kind of ridiculous if it's like hey, hey, hey, you need to stop and the server's like, well, I'm not done yet, let me finish my thing cuz that would be annoying.

[00:13:21]
Cuz if you're trying to kill a server, you want it to be done. So we need to catch that signal interrupts before anything happens. And right when we're unloading the server, we wanna dump the information we need. So we're gonna do that with what's called a listener, and we talked a bit about listeners.

[00:13:38]
But on this one, we're gonna use a process listener. So I'm gonna put it right here. We can say process.on,
>> Jem: And SIGINT is signal interrupts, the control C.
>> Jem: Make an anonymous function to respond to do something. So on SIGINT, we wanna make sure we shut down the server gracefully.

[00:14:07]
So we call server.close just to clean up any loose connections. In that itself, we can put a function to call. Now, that one we're gonna call the shutdownDB.
>> Jem: Okay, we're almost there. What are we missing though? So we created a database, we created a table, we make sure everything's cleaned up nicely and it's gonna dump the data to the console before we close it.

[00:14:35]
What did we forget to do?
>> Student 3: [INAUDIBLE]
>> Jem: Yeah, we never actually wrote any data to the database. We have this beautiful database all set up, we have the columns, but we didn't actually write any data. So let's do that inside our websocket server itself, or inside our websocket connection.

[00:14:52]
So whenever a connection happens, we wanna log the total number of visitors in the current time. So this we could probably wrap as a function, but we'll just do it in here. So we'll say dbrun. So to insert something into the database, we say insert, oops, caps lock,

[00:15:16]
>> Jem: INTO the table, visitors.
>> Jem: We're gonna insert these two columns, fields, counts, and time
>> Jem: And let's close that off. Next, we need to insert the values. And this is where SQL's kind of, it's less than intuitive that you have to do this. Oops, make this multi line look a little easier.

[00:15:58]
>> Jem: So we're inserting to the count time, so we have to call values as the actual things we're inserting into.
>> Jem: And the values are the total number of clients, so that counts. And we save that to a variable up here, so this is called num clients.
>> Jem: And next value is now, however that's a built-in.

[00:16:24]
We talked about time and how databases that have their own ways of dealing with it. So that's actually a built-in function into SQL Lite. So let's call daytime as a function, and we'll just say now.
>> Student 2: Since date time is a built-in function should that be capitalized, or is it particularly, like is all caps specifically for things that are immediately related to tables?

[00:16:57]
>> Jem: Yeah, all caps is generally convention, you don't have to do it, but it's just for the SQL commands because it makes it a lot easier to read. And you're like what's my data versus what's built in. datetime is a function so it's lowercase generally, but good question.

[00:17:14]
So now, the moment of truth. Does this all work? I don't know, we'll see where we made a mistake, or where I made a mistake somewhere along the way. So let's fire this up. We say node index WS. No errors so far. We can jump back to my website, but we're on localhost.

[00:17:40]
>> Jem: Something went wrong, it just crashed, let's find out.
>> Jem: A syntax error.
>> Jem: Error one SQLITE ERROR near time, where is time?
>> Student 3: Also on the line 56 [INAUDIBLE].
>> Jem: Line 56.
>> Student 3: [INAUDIBLE]
>> Jem: Looks right.
>> Student 3: [INAUDIBLE]
>> Student 3: 36?
>> Jem: It's the little things that'll get you.

[00:18:24]
It's got a comma.
>> Jem: I knew it, it's always something. That's why we do this together, we do it live.
>> Student 3: Before you, go back and check line 66 also.
>> Jem: 66.
>> Student 3: [INAUDIBLE]
>> Jem: Good catch.
>> Student 3: Yeah, [INAUDIBLE].
>> Jem: Good catch, chat. All right, let's try this again.

[00:18:53]
All right, back to our browser,
>> Jem: All right, well, it didn't crash this time, so that's good. Let me open another window. Why did it crash there? That's a new one. So it worked the first time, it didn't work the second time, so there must be another error.

[00:19:17]
>> Jem: Table visitor has no column time.
>> Student 1: If we crash the server ungracefully, we need to remake the table, cuz we didn't delete it properly.
>> Jem: Because it's all sort of memory the minute the server goes down, it's gonna just be erased for memory. That's why we're using the memory and not the db, otherwise we'd have to check if the table existed and then rebuild it.

[00:19:41]
We could do it, it's just another command.
>> Student 1: I'm getting an error that table visitors already exists.
>> Jem: Man, the comma again, it's the little things. It's the little things that'll get you, especially when writing SQL. All right, let's try this again. Big money, big money, no whammy, no whammy.

[00:20:00]
All right, it didn't crash. Let's try opening another one. Two visitors, it didn't crash. Okay, so I'm already doing better than I did before. So let's go ahead and stop that server and see if we actually wrote anything to the database. So I'm going to open VS code again.

[00:20:16]
Ctrl+C is gonna give it a signal interrupt. I did something wrong here cuz the signal isn't working.
>> Jem: Max listeners event leak, so there's a memory leak somewhere. That means I didn't close something properly. Let me double check. So let me add,
>> Jem: Maybe a log in here,

[00:20:46]
>> Jem: See if it even makes it this far, but we're stuck. But remember, this whole slide on in case you get stuck. And what do we say we can do? We can get an exit back to my computer. We can pkill node, cuz remember, I said it's possible to get stuck in a running process and now I can't kill it.

[00:21:07]
Cuz I did something of but I'm gonna say pkill node, which terminated my server. So we ran into a little bit of trouble with the sigands and a race condition, it seemed like something wasn't closing. But we dove in a little bit and we found out, you know what the problem is is we never close the WebSocket connection.

[00:21:30]
That's actually keeping it all open, so hence the sigand never works. So we never shut down the database properly. So the way we can do that is we'll go through all the clients, and right before the server shuts down let's make sure we close the database out. So we can say WSS, same as we did before, let's grab all the clients, and we'll say for each,

[00:21:56]
>> Jem: And we'll say function, and give it a name, so easier debugging. And that's gonna take a client. It's gonna give us a client, and then,
>> Jem: So next, we just call client.close.
>> Jem: So what this does is when we tell the server with Ctrl+C we're gonna go through every single WebSocket connection, and we're going to close it and then we're going to close the server.

[00:22:28]
And then we're gonna shut down the database, so let's see if that works.
>> Jem: And we'll open up our browser again, we'll write a few times, maybe a few more.
>> Jem: And there we go.
>> Jem: So it's tricky, once you start catching signals and doing things like that, you have to think really carefully about what's going on.

[00:22:59]
So in this case we had three open connections. So we had three types of open connections. We had the WebSocket connection that was open to all the clients. We had the server connection that was open, and we have the database connection, and it's shut down properly and cleanly.

[00:23:12]
We had to run all of those. So that was a fun debugging experience. A little bit tricky, it's the edge cases you have to look out for.
>> Jem: So now we have our WebSocket server, we're logging into the database. We have a database as well. Let's go ahead and commit all that to our websites.

[00:23:34]
So I'm gonna run git status as I do, to see what's going on. Git add, git commits-am, Add database logging,
>> Jem: And we'll just git push. And because of cron jobs running on a server, it will pull it down and we'll have a database now running our application really easy.

[00:24:01]
And that's why I love SQL lite is we don't have to install anything other than the SQL lite library. It doesn't require some service to bring up the start and stop. We do that all from the application itself. It's pretty cool. If you've ever worked with databases, you know kind of what a pain they are, and this is so much easier.

[00:24:18]
So we did it. We installed SQL Lite, we set up a database, we set up the table, the columns, and then on connection we say the visitor count. Three simple instructions, but it turns out it's a little bit more complicated than we thought.
>> Jem: And there's a link to slides.

[00:24:35]
The fully working example if you need.
>> Jem: And if you wanna play with MySQL, MySQL.js is probably the default if you wanna get really complicated. But, depending on how this went for you, you may not want it to have SQL yet for a long time. I don't blame you, it's for some people, it's not for others.