Check out a free preview of the full Electron Fundamentals, v2 course:
The "Persisting Data" Lesson is part of the full, Electron Fundamentals, v2 course featured in this preview video. Here's what you'd learn in this lesson:

Steve hooks the application into an sqlite database to create data that persists even after the app is closed.

Get Unlimited Access Now

Transcript from the "Persisting Data" Lesson

[00:00:00]
>> Steve Kinney: If we didn't have electron forge, we would need to install electron rebuild and put it in the post install step like we saw, but we do have it in place. So here we're gonna just do a npm install, I probably already have in place for this one but just in case.

[00:00:21]
>> Steve Kinney: SQLite again is a compiled dependency.
>> Steve Kinney: We'll go ahead as that is loading, I'm gonna start writing some code. So I'm gonna make a new file hidden here called database.js which is just going to be the abstraction for that so I have to think about it anymore.

[00:00:39] And as I mentioned earlier, this is mostly to show that we can use native databases in our application. You might be like clippings really that involves a SQL database. Okay, I get it, but we're making a point here cool. So in this case we gonna be using this in the lender process so we gonna need to talk a little bit to the main process with remote as well, mostly cuz we wanna get those app, remember app.get path used for desktop earlier.

[00:01:09] We gonna want that for the users data directory, right this is where given applications can store stuff for a given user. So you can keep settings and stuff along those lines. We don't wanna put it like in a desktop. We don't wanna put a SQL database there or documents.

[00:01:24] We wanna put it in the place that we're supposed to put it. Even though we're using this code mostly from the render process. So we're gonna need to use remote module slightly. So let's go ahead in here, and we'll say, import path from path. This is the Node library and we'll do import remote from electron.

[00:01:50] And then we'll just import.
>> Steve Kinney: Knex from knex. Knex is basically Node binding for SQL and SQLite and Postgres. And so rather than writing strings of SQL, you get a premise based JavaScript API, that will do that all for you. And then we'll do import SQLite3. All right, very cool.

[00:02:23] We'll get that app from the remote module just so we can get that directory.
>> Steve Kinney: And we'll configure a database.
>> Steve Kinney: We are going to do this kind of a little fast and loose, right? We're not gonna write a whole set of migrations and stuff like that, which means the very first time we use this, it'll get very angry with us, but it's okay.

[00:02:50] All right, so we'll say that the client that we wanna use for knex because knex will work with MariaDB, MySQL, Postgres, and SQLite. So we're saying hey, I'm gonna be using this with SQLite3. And with SQLite, SQLite just uses a file not necessarily like a database server that you'd connect to directly.

[00:03:11] So we'll say the connection filename path.join, I'll do app.getpath. User data, I'll get ourself our own directory in there. Our own file name will say clipmaster-clippings.sqlite. So path.join basically is useful rather like I put too many clashes because it tried to concatenate strings. It's just basically a way to put together a full path.

[00:03:41] So we're gonna get the applications user data, and then we're gonna make a SQLite database. No semicolon, called clipmaster.clippings.sqlite.
>> Steve Kinney: And one other setting that we need for SQLite is use null as default, it's true. All right, so we've got a database. The only other thing we need is to make a table.

[00:04:07] So we're gonna see if the table exists. If it doesn't we'll go ahead and make it, this means technically like depending on how you run your code. It might blow up the first time and we're refreshing, the table will exist because it's async. But we'll be okay with it.

[00:04:26] So we do a database schema, has table, clippings. So check to see if the database has a table called clippings.
>> Steve Kinney: And that we're trying to boolean on whether or not that table exists. Cuz everything is effectively promise-based in knex. So we'll say, okay. If it doesn't exist, we'll go ahead and make one.

[00:04:56]
>> Steve Kinney: Go ahead and create a table called clippings.
>> Steve Kinney: And that'll give us back the table where we're gonna have to make our columns.
>> Steve Kinney: I got in different engineering cuz I like database migrations. We'll give it that ID column that we had in our react model. It will be the primary, they'll increment every time.

[00:05:26] And we'll create a text field called content, right? Very simple SQL table,
>> Steve Kinney: And then we'll export default our new database. So this will create a SQLite database, which is a file based database, in the user's application settings directory. So that's percent sign app data percent sign on Windows.

[00:05:48] That is the hidden library directory in their user directory on macOS. Somewhere on Linux.
>> Steve Kinney: And it will put that database there, and then we'll have access to it once it's been created.
>> Steve Kinney: So now we can go ahead and we can pull that in in our renderer process, which is, yeah, from the renderer process.

[00:06:10] From the same place we were writing react code, we can directly write SQL queries. That seems safe and good, right? Saves what we're used to. Like who doesn't do that in their normal web applications? All of us.
>> Steve Kinney: Database from,
>> Steve Kinney: ./database. All right, so things that we could is we're basically gonna need to do some crowd actions here.

[00:06:42] We're going to want to get everything out of the database so we can put in the UI. We are going to want to add stuff to the database. And then theoretically, we might want to at least delete stuff from the database. And possibly even update stuff on the database, right?

[00:06:59] Maybe not, right? But we're doing it, so whatever. And so the first thing we might choose to do is have the ability to fetch all of the clippings in the database.
>> Steve Kinney: So we could say something along the lines of fetchClippings.
>> Steve Kinney: And fetchClippings will do basically say database, which table clippings and if we can just call the select method, which is select star that will get everything out of the database.

[00:07:43]
>> Steve Kinney: And once we have those clippings,
>> Steve Kinney: We'll just go ahead and, update the application state, took everything out of the database, put it into the component state.
>> Steve Kinney: We'll just bind that as well up here.
>> Steve Kinney: We can do that when the component mounts. Which point we'll see that the wall will vanish because we'll replace it within the database.

[00:08:22] And what's in the database? Nothing [LAUGH] so it'll be an empty array.
>> Steve Kinney: All right, let's kill it off and start it back up again.
>> Steve Kinney: Preparing native dependencies, zero out of one. Anyone want to venture a guess what that one data dependency is SQLite, right? So at this point it's like hey, you have a compile dependency that is not the right version for Electron.

[00:08:55] So what it's gonna do at this point is it's going to go ahead and recompile that for the version of Node that is bundled with Electron. This is very similar to if we ran an Electron rebuild after installing it in the first place. But it is going ahead and getting that for us with a really nice UI as well.

[00:09:13] I mean nice is a strong word. That thing entertains me. Let's put it that way. How about that. Then I'll go ahead and load right up. So let's just give that one second. Cuz our application will not start until it's there.
>> Steve Kinney: Okay, this stuff fetchItems is not a function, all right?

[00:09:37] Cuz it's not called fetchItems, it's called fetchClippings.
>> Steve Kinney: Would have been great to know before we waited for the database, but cool. At this point, you can see that there's nothing there which is actually surprisingly good since that lull was built in there by default which means the fact that there's nothing on the screen means that it's working [LAUGH] cool.

[00:10:13] Sweet so now we wanna be able to put something into the database, so we're just gonna modify add clippings a little bit. Where we'll say database, look at that clippings table again.
>> Steve Kinney: And will insert the content. We don't have to make our own data now ID because the database is gonna automatically increment for one for us.

[00:10:41] So we'll insert that in there, and then we'll just go ahead and fetch all of the clippings again. Could we do optimistic updates? Yeah, totally, but we're gonna keep this simple. So we get rid of all of this code here.
>> Steve Kinney: And try it like that. I'm just gonna give this a quick refresh, do a copy from clipboard.

[00:11:06] Content is not defined, that is true. I deleted a little bit too much.
>> Steve Kinney: Do clipboard.readText.
>> Speaker 2: Typo.
>> Steve Kinney: Where? Read text, good catch.
>> Steve Kinney: There it is, that's the apparently the last thing I copied, seems right. What happens if I refresh the page now?
>> Steve Kinney: Boom, still there.

[00:11:46] Persisted in the database. I could make a point and go ahead like close the app and stuff like that. I can close the app, delete the app, reinstall the app but it will still be there because I didn't remove the database from my library, but now we have persistence like a real application, right.

[00:12:02] It's not just like holding stuff in the dom and hoping for the best, it is literally stored, this application, we can survive a restart with it, so on and so forth.