Seed the Database

[00:00:00]
>> Scott Moss: So now that we have that, the next thing we wanna do is create a seed script. So what is a seed script? It's going to seed our database. It's going to put, and it's not always fake data.

[00:00:14]
It's going to put, and it's not always fake data. There's a lot of ways to see the Database and for different reasons, but essentially populating our Database with the data so we can do things. Most of the time you wanna see the database, might be like for making a feature it might be easier to see some part of the database so you can make a feature because it relies on some type of relation or maybe if you're. Doing, you know, full stack, you're also working on the front end and you wanna work on the front end first, but you don't have any data like you wanna work on this view that's a table, sure you can mock the data out on the front end and do all that, but then you have to Go back and add all the network logic when you finally figure out the back end.

[00:00:30]
Doing, you know, full stack, you're also working on the front end and you wanna work on the front end first, but you don't have any data like you wanna work on this view that's a table, sure you can mock the data out on the front end and do all that, but then you have to Go back and add all the network logic when you finally figure out the back end. Instead you can just go ahead and hook up the back end, do everything, and then just see the Database with fake data. So from the front end's perspective, nothing will ever change because that data is the same shape and it doesn't know that the data is fake it's just data so. There's many reasons why you wanna see the database, you know, you know, sometimes you might wanna pull in production data into your local database for whatever reason to try to replicate an error or an issue that someone might be having and it's easier to do it that way.

[00:00:47]
There's many reasons why you wanna see the database, you know, you know, sometimes you might wanna pull in production data into your local database for whatever reason to try to replicate an error or an issue that someone might be having and it's easier to do it that way. So, but at the end of the day, it's just putting data in the database, you know, fake data with something like faker JS, other ones out there that are like, you know, come up with fake data. You can curate the data yourself like hand create, hand select the data that you want to Go into the database. And make that.

[00:01:01]
And make that. You can also, like I said, do a production data import, so if you have, you know, a dump of a database somewhere, whether it's proud or staging or some version of that. You can export that and then import that into your database. A lot of databases actually have that feature built in natively where you can export and import or you can do it manually like this example is doing.

[00:01:18]
A lot of databases actually have that feature built in natively where you can export and import or you can do it manually like this example is doing. What we're gonna do is create our own C script and we're just gonna use fake data, right? So. Let's do that.

[00:01:31]
Let's do that. So what we're gonna do is head over to database folder, make a new file, call it CD. And we're gonna see the database. So the first thing is we need to import our DB from the connection.

[00:01:45]
So the first thing is we need to import our DB from the connection. And then we need to import all of our tables that we need to use to create data, so that's our users, that's our habits, that's our entries, that's our tags and our habit tags. From, schema. And then we need to, we're gonna do some stuff with passwords here, so we're gonna.

[00:02:00]
And then we need to, we're gonna do some stuff with passwords here, so we're gonna. Import. Hash, did we do all yet? I don't think we did all yet, so actually I don't think we're gonna do that yet, and that's fine, so.

[00:02:12]
I don't think we did all yet, so actually I don't think we're gonna do that yet, and that's fine, so. The first thing we're gonna do, well, really the only thing we're gonna do is make this seed function that when called will see the database so. And there really is no right or wrong way to do this, so I'm just gonna do it this way so we'll say exports, or actually let's just say. Constant see like this.

[00:02:28]
Constant see like this. So async function. Doesn't take any arguments and it's nice to run the C script and see like exactly what it's doing so it's for me I like to do a lot of logging inside of a CD script so when I run again in terminal I can see where it is and what's happening so I'll you know, do a log here you can do whatever you want but I'll say. Let me see, what is that like, Bean or?

[00:02:47]
Let me see, what is that like, Bean or? Plants Beanstalk. I know, it's seed there it is, OK, so I'm like, what is that? Starting Database seed, I'll do that, something like that, and then I'm gonna try to catch this just in case it breaks.

[00:03:02]
Starting Database seed, I'll do that, something like that, and then I'm gonna try to catch this just in case it breaks. I don't want, you know, everything to die. So, first thing you wanna do in the seed scripts. At least for in this example because it's just a It's just my database that I'm connecting to.

[00:03:24]
At least for in this example because it's just a It's just my database that I'm connecting to. I want to clear the all the data in the database. So Be careful not to run a seed script on your production database. First of all, you should never have production database credentials locally on your computer.

[00:03:40]
First of all, you should never have production database credentials locally on your computer. That's just not good. If for whatever reason you have production Database credentials locally on your computer. First of all, who gave them to you and why did they let you get them And two, don't do it You, I can't tell you how many times I've accidentally like, oh I just dropped the production database.

[00:03:54]
First of all, who gave them to you and why did they let you get them And two, don't do it You, I can't tell you how many times I've accidentally like, oh I just dropped the production database. I thought that was my local database, and thankfully like Neon has like backups like every minute I could just click a button and restore everybody's data, but yeah, it's bad, don't do that, and most companies won't even ever give you access to that anyway it's like a whole team it's like dedicated to like making sure people can access the production Database for legal reasons and technical reasons. So, but anyway. We're gonna delete everything, so I'm gonna just write everything out.

[00:04:10]
We're gonna delete everything, so I'm gonna just write everything out. We could probably loop over this and do it quicker, but I'm gonna say DB. Delete and then just the name of. The table, so we want to delete the entries, we want to delete the habit tags, we wanna delete.

[00:04:26]
The table, so we want to delete the entries, we want to delete the habit tags, we wanna delete. The habits. Everything must go. We want to delete the tags, and we want to delete the users.

[00:04:40]
We want to delete the tags, and we want to delete the users. So, drop all of that. And then now that everything is dropped, we want to create some demo users, so I'm just gonna log that Users. And you can.

[00:04:55]
And you can. Have as many users as you want or not, it's totally up to you. I'm gonna say. I'll make, I mean I'll make at least one obviously, but if we need to, I'll make 2, but first thing is I'm gonna say.

[00:05:11]
I'll make, I mean I'll make at least one obviously, but if we need to, I'll make 2, but first thing is I'm gonna say. Let's just say this. I'll say results and I'll talk about this in a minute. So I'll say results equals a weight the DB and then to do to put something in the in the database, you can do the dot insert method, and then you can put the table on which you want to insert into.

[00:05:30]
So I'll say results equals a weight the DB and then to do to put something in the in the database, you can do the dot insert method, and then you can put the table on which you want to insert into. In this case I wanna insert into the user's table that I imported from the schema. And then what do I want to insert? I can do values.

[00:05:42]
I can do values. Values can take an array of objects if I wanna insert many or I can just take one object if I just want to insert one. This is gonna be type checked to the table that I'm inserting into so it knows what a user can have, right? So if I do this, you can see here are all the fields on the user.

[00:06:00]
So if I do this, you can see here are all the fields on the user. And all the ones with question marks are the ones that I don't have to add So in this case I need an email, I need a password, I need a username, right? So I'm gonna add those. So for email, you can put whatever you want.

[00:06:18]
So for email, you can put whatever you want. I'm just gonna put demo at App.com. And then for the password for now. I know maybe in the notes I have like the hash password stuff.

[00:06:32]
I know maybe in the notes I have like the hash password stuff. We haven't gotten to off yet, so just put whatever password you want. You can har. Right now we're gonna hardcode the password, and obviously for good reasons you don't want to do that in production, but it's totally fine here.

[00:06:49]
Right now we're gonna hardcode the password, and obviously for good reasons you don't want to do that in production, but it's totally fine here. First name, put whatever you want. Demo Last name. Person And then you want to do returning like this.

[00:07:03]
Person And then you want to do returning like this. And then I need a username. I think that's why it's freaking out, yeah, username would just be demo. Cool, so I'm just putting one person in here, calling it this.

[00:07:25]
Cool, so I'm just putting one person in here, calling it this. Insert always returns an array, no matter what, that's why I have results. So JavaScript trick, you can just destructure this, right? By putting an array around that and then calling whatever you want, so I'll just call this the demo user So this will, this is basically saying grab the first thing from the array that's returned from here.

[00:07:40]
By putting an array around that and then calling whatever you want, so I'll just call this the demo user So this will, this is basically saying grab the first thing from the array that's returned from here. And call it and make a variable called demo user to represent that first thing. That's what destructuring is. That's what this is.

[00:08:02]
That's what this is. Now I have the demo user. The next thing I could do is I can create some tags, so I'll do that console a log, and I'm gonna say, creating tags like that. And then I will say, Same thing, I'm gonna say health.

[00:08:28]
And then I will say, Same thing, I'm gonna say health. Health. Health tag and I'm gonna say a wait D. Insert.

[00:08:46]
Insert. Tags just like this. Take the values I say name, call whatever you want. I'm gonna say this is a health tag.

[00:09:06]
I'm gonna say this is a health tag. Color But any hexadecimal color you want, I just put. Something like that. And then not returning.

[00:09:26]
And then not returning. If you don't do dot returning, it'll return the metadata on the insert. It'll like, oh, I inserted this many rolls at this table. It'll give you all the metadata on that operation and not the not the data itself, not the rows itself.

[00:09:42]
It'll give you all the metadata on that operation and not the not the data itself, not the rows itself. So if you don't do returning, you'll just get the metadata on the operation itself and we don't want that, we actually want. The thing that we inserted, so we got that. Any questions so far?

[00:09:58]
Any questions so far? No All right. The next thing we can do, and we can make more attacks and in my example, in the notes I have tons of attacks, tons of stuff. We don't have to make all of these.

[00:10:18]
We don't have to make all of these. You can if you want, but I'm gonna move on to the next thing, and I'm going to. Create a habit, so I'll say, let's make a new habit, we'll call it the exercise habit. Like that, same thing, DD.insert.

[00:10:32]
Like that, same thing, DD.insert. For the habits. Top values. For this one.

[00:10:48]
For this one. We need to associate. A habit. A habit cannot be created unless it has a user, so we gotta give it a User ID here, so we have our demo user and we can do the dot ID of that demo user.

[00:11:11]
A habit cannot be created unless it has a user, so we gotta give it a User ID here, so we have our demo user and we can do the dot ID of that demo user. Have it must have a name. You call wherever you want. I'm gonna call it exercise.

[00:11:26]
I'm gonna call it exercise. got a description here which is not required. I'm gonna put one, you know, daily. Work out.

[00:11:52]
Work out. And then frequency. There's no enum here, so you can put whatever you want. And target count because, you know, small wins, we'll put one.

[00:12:06]
And target count because, you know, small wins, we'll put one. And also returning. So now we have our exercise habit. And then what we wanna do is we want to join the Exercise habit to the.

[00:12:35]
And then what we wanna do is we want to join the Exercise habit to the. Health tag, so that means this habit is tagged as health if that makes sense because that was the whole point of having tags that we can tag habits with different tags that we created, so we'll do that. By using the habit tag. Mini to mini table or joint table, however you wanna call it, so we'll just say wait, D.

[00:12:56]
Mini to mini table or joint table, however you wanna call it, so we'll just say wait, D. Insert, habit tags. Top values like this. You put an array or you can put an object of arrays, doesn't really matter either the same.

[00:13:10]
You put an array or you can put an object of arrays, doesn't really matter either the same. And we'll say cool for this one, the habit ID is gonna be the exercise habit. ID and then the tag ID is gonna be the health tag. ID.

[00:13:34]
ID. And that's it. We don't need a result of this, we just need it to exist. So now we associate those two together.

[00:13:55]
So now we associate those two together. Now I'm gonna add some completion, some entries for our habits. So, adding completion entries. Let's just make one for today, so I'll say today equals new date.

[00:14:12]
Let's just make one for today, so I'll say today equals new date. Set the hours. It's a lot to do. Today that's set hours.

[00:14:28]
Today that's set hours. Basically Like midnight Cool. And then I have some code here that basically just adds completions every day for up until today, like for a week, so that's basically what I'm doing here, so we can do the same thing, so I'll just make a for loop here. And I'll just say like I go 0.

[00:14:42]
And I'll just say like I go 0. I'm minus 7 days for a week. Or I less than 7 days for a week. I Get the dates.

[00:15:01]
I Get the dates. Which is today. And then we want to subtract I'm sorry, they do. Set the date.

[00:15:17]
Set the date. It's gonna be whatever the date is today. Minus 1. So we're gonna go back I amount of days.

[00:15:38]
So we're gonna go back I amount of days. And I'm gonna insert that, wait, D insert. Into the entries, the values. The habit ID, so for this habit, which is the exercise habit.

[00:15:59]
The habit ID, so for this habit, which is the exercise habit. I want to completion date. Is date And then for any notes. Basically, if I is not zero, I can put, you know, great workout or whatever, but you don't have to put a note, it's totally fine.

[00:16:13]
Basically, if I is not zero, I can put, you know, great workout or whatever, but you don't have to put a note, it's totally fine. And now we log some entries for this habit. Super simple. Notice that when we did this insert here, this is not creating an entry because we already have, I'm sorry, this is not creating a habit because we already have a habit it's creating a new entry for this habit right?

[00:16:33]
Notice that when we did this insert here, this is not creating an entry because we already have, I'm sorry, this is not creating a habit because we already have a habit it's creating a new entry for this habit right? That's the, that's the relationship right? This habit ID for this. This entry has this habit ID that relates to this exercise habit ID, so take note of that the relationship.

[00:17:08]
This entry has this habit ID that relates to this exercise habit ID, so take note of that the relationship. We'll keep moving, so outside of the entries. Let me see. Oh, you know, all I really have here, I had an example of just like how you might be able to query it, but we don't need to do that in a seed.

[00:17:29]
Oh, you know, all I really have here, I had an example of just like how you might be able to query it, but we don't need to do that in a seed. That's just like a sanity check to make sure that everything works, but that's fine. Here's where I'll just start adding some more of the logs of information about what happened because if you're gonna see the user with a an email and a password, sometimes it's nice just to print that out so you can know what to log into when you go test it in Postman or your UI or something like that. So I'll just say database see it.

[00:17:45]
So I'll just say database see it. To see it successfully if we got here. And then You can put whatever you want. I have some stuff here in the notes that are pretty good, but essentially, I just want to see like that demo user, and I wanna see that password, so, I'll say demo user as Just some information here.

[00:18:04]
I have some stuff here in the notes that are pretty good, but essentially, I just want to see like that demo user, and I wanna see that password, so, I'll say demo user as Just some information here. User, whereas user with habits? Oh no, that's the query. Oh yeah, maybe I won't log that one then cause I didn't do that query so I won't log that.

[00:18:18]
Oh yeah, maybe I won't log that one then cause I didn't do that query so I won't log that. I'll just go ahead and get those credentials. That's really all I care about. User credentials.

[00:18:34]
User credentials. Email will be Whatever that user's email is So it is demo user.email Username, just in case I need that. And password. Which for now is plain text.

[00:18:53]
Which for now is plain text. Cool. And if it fails then let's log that. Do that and then.

[00:19:11]
Do that and then. Stir the error again or you could just say process. Exit. What?

[00:19:32]
What? Either way. And the last bit here is a fun little trick for the latest version of Note, and it just keeps the seed script flexible. What we can do is we can detect if this file is ran directly from the terminal or if Someone imported the seed function and tried to run it.

[00:19:47]
What we can do is we can detect if this file is ran directly from the terminal or if Someone imported the seed function and tried to run it. Either way we want it to work. If you ran this file directly from the terminal, we want the seed function to run immediately. If you, but otherwise you should just be able to import this seed function into another file and call it, and we want it to work either way so what we can do, we can do import.meta.

[00:20:00]
If you, but otherwise you should just be able to import this seed function into another file and call it, and we want it to work either way so what we can do, we can do import.meta. URL which is essentially. That just gives you the you that gives you the file path of the current file that you're in and in previous versions of Node, the way you would have gotten this is doing like. You have used like the path import and you would have did like.

[00:20:16]
You have used like the path import and you would have did like. God, I forgot what it was I used to use it all the time, I forgot what it was, but you used to be, there was another way to to get this. It was like oh yeah, dirname. And using the path, you'll be able to get like if you ever seen this.

[00:20:35]
And using the path, you'll be able to get like if you ever seen this. Like that their name and then using like path.join to get the current file you would have done.net. You can't do that in the latest version of Node when you're using ES modules. ES modules prevents that from working, so you have to do import.meta.

[00:20:54]
ES modules prevents that from working, so you have to do import.meta. URL, which is the current file that you're in. So if the current file that we're in equals this file path like this. Of Process and I'm gonna explain this part in a minute.

[00:21:13]
Of Process and I'm gonna explain this part in a minute. ARGV1. What does that mean? So process.

[00:21:29]
So process. ARGv when you run a command. From the terminal, every single thing that has a space in between it is gonna be added to an array called ARGV. And they're gonna be indexed from left to right.

[00:21:45]
And they're gonna be indexed from left to right. So whenever you run anything in Node space. Anything Node would be the first argument, so that would be process ARGV index 0, that would be Node. The second thing would be the file that you're trying to tell Node to run.

[00:21:54]
The second thing would be the file that you're trying to tell Node to run. So basically if the file that we told Node to run is the same file that we're in automatically call the seed script That means somebody's running this from the terminal. Does that make sense? So I'll go ahead and call the seed scripts.

[00:21:54]
So I'll go ahead and call the seed scripts. Then if that's successful. That process. Exit cleanly with a 0.

[00:21:54]
Exit cleanly with a 0. Otherwise. I guess I could catch it here Well, if I catch it here, This is never really gonna matter cause I'm already catching it here, but sure, we'll just say catch it here. And process.exit.

[00:21:54]
And process.exit. What? Cool. Otherwise.

[00:21:54]
Otherwise. You can go ahead and. Programmatically use this seed function in your code and do it that way versus having to do it from the terminal. So it's flexible.

[00:21:54]
So it's flexible. You can run it from the terminal and it'll run instantly or you can import this and run it programmatically, however you want to do it. Cool, so let's run this and see. If we broke something.

[00:21:54]
If we broke something. Bump this up. So now I'll say NPM Run DV. See There are my logs, looks good.

[00:21:54]
See There are my logs, looks good. So mine successfully seeded, if you had any errors. Might want to look at the code, see what went on. I can verify this by going to Drizzle Studio.

[00:21:54]
I can verify this by going to Drizzle Studio. Refreshing this and my data should be in here. There we go, here are my 7 days of entries. I can see that they're attached to these habits, so the relationships worked.

[00:21:54]
I can see that they're attached to these habits, so the relationships worked. Here's my habit. I only have one. I can see that, it should be attached to a habit tag somewhere.

[00:21:54]
I can see that, it should be attached to a habit tag somewhere. It is, it's attached to this habit tag, it's attached to this user, and it has all of these 7 entries. I can look at this one habit tag, and it should have one habit ID. And one tag ID and they both should point to a habit in a tag, and they do.

[00:21:54]
And one tag ID and they both should point to a habit in a tag, and they do. I got this one tag called health that should point to a habit tag, it does. And I have a user that has a bunch of habits, I believe, or one habit I guess.