So now that we have our schema finished I want to go back to our seed script and I want to create a couple of units, lessons, challenges and challenge options. So let's revisit our scripts folder seed.ts. So if you've done it like this, like I have, you should know the ID of each of your courses. So that can be quite useful for us. First things first, let's go ahead and extend our removal before seeding.
So let's await database.delete schema.units. Then let's go ahead and copy and paste this and let's delete our lessons and then let's go ahead and do that for the rest of our tables here. So we have challenge options here. We also have challenge progress here. And I think that should be enough.
So make sure that you remove everything first in the seed script and then after we create our courses let's go ahead and do the following. I'm gonna go ahead and pick one course ID. So I'm gonna pick Spanish. So that is ID of one. And in here, I'm gonna write await database dot insert schema dot units dot values.
I'm gonna open an array, and I'm simply gonna add the very first unit here with an ID of 1 and course ID of 1. So this is Spanish, right? Because I know that from above. And then let's create a title with unit 1. Description is going to be Learn the Basics of Spanish.
And Order is going to be 1. There we go. And it seems like I have to add a comma here. There we go. So now we should have a unit.
So let me go ahead and prepare those two. So I'm going to npm run database studio in one terminal here. So let me just open that so we can keep track of things. Then I'm going to open a new terminal and I'm going to go ahead and do npm run database seed. So let's go ahead and see if this will work properly.
There we go, let me just refresh this and now I have one unit and four courses. So if I go inside of my courses here, I should have the units for the first one, which is the Spanish one. If I go ahead and choose The second one, I have nothing here. So I only have it for the very first one, which is Spanish. If I click on units, there we go, unit one.
So let's go ahead and check it out actually inside of its own view here. There we go. ID of one, unit one, learn the basics of Spanish, matching course ID, order and the relation to the course. But we do not have a relation to the lesson here. So let's go ahead and do the following.
We're going to go ahead and extend our seed script here. So we're going to create some lessons. So again, we now know the ID of our unit, so we can use that as our unit ID. So let's go ahead and write a weight database insert schema dot my apologies of lessons dot values and let's pass in ID of one unit ID of one which is going to be our unit one, learn the basics, right? Then we're going to have an order of one and the title, for example, nouns.
And then you can go ahead and copy this and give it an ID of 2, keep the unit ID to be 1 and give this an order of 2 and make this verbs for example so we would have two challenges in this unit, my apologies, two lessons in this unit. So we can try running our script again, npm run database seed. We're gonna remove everything and then we're going to create some new things. So inside of here we now have two lessons as you can see so let me go ahead and expand this. There we go.
So as you can see we now have a relation with the unit here. Both of them should have the same unit. There we go unit one and if I close this and check the second one there we go unit one as well. Let's check the units here and there we go. One thing that I'm noticing here is that this is called a lesson but I think this should be called lessons instead.
Let me just quickly check that inside of my schema here. So we are talking about the units table. Let's find our units table and perhaps we've made a mistake. There we go. So I wrote course here and I probably used the single way of expressing lesson here as well, when this should have been lessons because we're using the many relation.
So I'm going to go ahead and quickly confirm that in my original source code. Yes, I use lessons there. So I made a mistake. Find your units relations which should be just above your units table and make sure that when you use many you use the multiple type expression of the word. So I'm gonna quickly confirm that I do that in other places as well.
Units, many. For progress, it's okay because it'll be progresses, I guess, so it's just easier for me to spell it like this. So I'm fine with progress. The same with, I believe we also have challenge progress, right? So I'm fine with this not being in a expression of a multiple, that's fine.
Of course this won't mess with your code, right? But I think it pays to be consistent. So there we go. Let me go ahead and do this I'm gonna write a colon many and I'm gonna check all of them options progress is fine progress is fine units lessons challenges options progress there we go everything else is perfectly fine So let me save this schema now, and I'm gonna go ahead and shut down my studio here. And let's see if I can fix this by running npm run database push.
So will this give me an option to fix the conflict by selecting the rename or is this going to be a bit more complicated perhaps nuking the database? It seems like changes have been applied. Let's see if that is true. Npm run database studio so will this simply rename this? So if I go ahead and refresh this there we go now it says lessons looks like it was a very easy conflict for drizzle kit.
Perfect. If you run into a problem you can always go into Neon, delete your database and simply run npm database push again after you've created a new database. If needed you might also need to change the environment key in your database URL field. But for me it was just a matter of renaming it in my schema, saving the file and running database push and now this says lessons as it should. Perfect!
And let me finish wrapping up my seed script here. So we now know how to insert lessons. And now that we know how to insert lessons, we can do the same thing, but for challenges. So let's go ahead and do the same thing. So we can pick an ID for a lesson.
So let's pick ID 1. That will be nouns, right? So I'm going to do await database insert schema.challenges .values and I'm going to pass in an id of 1, lesson id is going to be 1 which is going to be our nouns and then we're going to have a type of a lesson which as you can see uses our enum. So this is going to be a type of select for example, order of one and question is going to be which one of these is the man for example. So let me just change this from not using double quotes but instead I'm going to use single quotes so that I can then use double quotes in the string like this.
So, for example, I want them to translate the word man into Spanish, right? Which would be El Hombre, I believe. Please apologize. Excuse my pronunciation of that. There we go.
So if you want to, you can create another challenge here with an ID of 2 and a lesson ID of 2 which would then be verbs, right? And then you can give this an order of 2 if you want to, but I'm gonna keep it simple. And I suggest you do the same. So let's actually do this. Let's remove the second challenge from here.
The second lesson. Let's just keep the first lesson here and let's just keep the first challenge for the first lesson here just so we have the same results right. So I'm fine with having multiple courses here But let's keep the units one, lessons to one, challenges to one. So we know exactly that each of this is only for the last one. And now let's go ahead and do the following.
Let's wrap it up by creating challenge options. So schema.challengeOptions.insert, my apologies, values. Let's give this an ID of one. Challenge ID is going to be one. Image source will be slash men.svg, which we don't have yet, but we are going to have later.
Correct is going to be true. Text is going to be el hombre and we're gonna have an audio source which is going to be slash es underscore man dot mp3. So that's how I'm going to label my audio files. I'm going to prefix them with a language and then what they are representing. And I'm gonna show you how we're gonna use 11Labs AI to generate AI voices for different languages.
There we go. So this is the correct challenge option. So for this question, which one of this is the man, I want them to select this option which would have this picture and which will say El Hombre. So let me copy this, give this an ID of two and yes this is the challenge ID right so we created this challenge right here and it has an ID of one so this will represent which one of this is the man. That is the question that this is answering for.
The same is true for this one, but this one will be a woman. Correct is going to be false. And instead of el hombre, it would be la mujer. Mujer. And let's change this to eswoman.mp3.
And let's give it a third option, so id of 3. The challenge id stays the same because these are all options for the question above, right? And this would be a picture of, for example, a robot. And this is also false. This would be L-robot.
And we would have an ES-robot right here. There we go. So I believe that is fine. This is enough for us to work with. We can now pretty much render everything we need.
The one more thing we're gonna have to do later when we get to that is we're gonna create one more challenge with a different type so this is a type of select later we're gonna create a type of assist but make sure you choose select for this one because it's easier so let's go ahead and go inside of our terminal here and let me go ahead and open a new one and run npm run database push. So this should add all the new values now. Let's just wait a second. There we go, change is applied. Let me refresh my database studio.
And I should have... There we go, I have four courses, I have one unit. This unit has two lessons, nouns and verbs. But It seems like I don't have any challenges. Let me refresh this to see if it is true.
It seems like I don't have... Oh, because I ran the invalid command. I should have run database seed, not database push. There we go. Npm run database seed.
My apologies, wrong command. And now I should have the following I should have four courses one unit one lesson because we removed that so we only have nouns now and I should have one challenge and this challenge should have three options inside and only one of them should be correct. There we go, perfect. We now have our setup here And make sure you have the same. If you don't have the same, I want you to go to my GitHub and copy this seed script that I will prepare for you.
And as well as confirm that your schema is working as well. Make sure you do all the necessary npm run database push. Here's a quick reminder how our database push looks like. So database push is npx drizzlekit push postgres. Of course if you were using mysql your would be different right.
Database Studio is simply DrizzleKit Studio and our seed uses TSX and runs from the scripts folder. So now what I wanna do is close everything. Let me close this and go with npm run dev again. So I no longer need to look at my database because I know everything here is correct. And what we are going to do now is we're gonna go ahead and select Spanish because that is the ID which we created in our seed script as the one which has all the units, all the lessons and challenges.
So there we go. Course ID for my units is Spanish, which means that I have to select Spanish in order to load all of my units here. So that's what we are going to go ahead and prepare. So let's quickly go ahead inside of our app folder, main, learn and let's go inside of page.dsx and now we have to create a query similar to getUserProgress inside of our database queries, right here, called getUnits. So when calling this units, I also want them to call all the lessons and challenges within.
And I also want to help us on the front end by normalizing our data in a way that we are going to introduce a new field called completed. So that's not something I want to keep in the database. So I don't want to constantly update that status whether we completed a lesson or not. So instead, this is what I'm going to do. I'm gonna use our field called course progress to detect, sorry, challenge progress to detect whether we finished a specific lesson or not.
So let's start simple. Let's go ahead and do export const get units. That's going to be our cache function. My apology it's going to be a cached function that's what I wanted to say And it's going to be an asynchronous arrow function here. And first let's get the user progress.
And let's do await get user progress. If we don't have user progress or if we don't have active course ID, let's simply return an empty array. No units to load for this user. Then let's go ahead and get our data using await database.query.units.findMany. Let's use WHERE equals units, which you can import from database schema so let me go ahead and prepare this like that so that it's easier to read.
So import units from database schema. So ensure that the units.courseId equals user progress active course ID. So that's which units we are going to load. And then let's use the width attribute here to load the lessons. So usually we will simply write lessons true, but if we want to load the challenges within that lesson, we have to extend this and use width again.
So let's write challenges again. Oh, it looks like I have a misspell of challenges here. Let me go ahead and quickly fix that in my schema. There we go, I have challenges here. So let me change this to challenges so it's proper.
Make sure you don't have any typos as well. And let's see if we will be able to easily resolve this. So I'm going to shut down my app and I'm going to run npm run database push. Let's see how easy is this going to be or are we going to have to nuke our database. There we go, change is applied and let me just quickly check is my seed script working now?
Looks like my seed script is still okay. So inside of my seed I don't use that populated field challenge. There we go. So seed script is completely fine. Let's go back inside of my queries here and now let's use challenges, there we go, so with challenges and again with challenge progress, true, so I wanted all the way to challenge progress.
And now that I have this data, I'm going to go ahead and create normalized data. That's going to be data.map. We're going to get the individual unit, open a function, inside of here, we are going to create lessons with completed status so let's do units sorry individual unit lessons dot map let's get individual lesson here and this is all of course in one line like this and then let's do const all completed challenges to be a lesson dot challenges dot every challenge, open an arrow function here, and let's return challenge.challengeProgress and challenge.challengeProgress.length is at least one but also challenge.challengeProgress.every progress of that individual challenge progress is completed so progress.completed there we go So then now that we have all completed challenges here for an individual lesson, let's go ahead and return the existing lesson by spreading it. So simply spread the lesson And then completed is going to be all completed challenges. So this is going to return true or false.
So we are checking whether every single challenge in this lesson has a matching challenge progress with a status of completed. If that is true it means that we can mark this lesson as completed entirely. So this is going to help us on the front end so I want to do this heavy computation purposely on the back end. So then I can easily use the completed Boolean, true or false, on the front end. And I can change the state according to that.
This can of course be improved. You probably already see how this is, how this would increase the more fields we have, right? So feel free to modify this using Reduce, feel free to explore this using Drizzle. Remember Drizzle allows you to use practically SQL query builders itself. So if you know how to do this in SQL, go ahead and do it.
It's probably much, much better and faster to do it this way. But since this is our first tutorial with Drizzle, I want to go easy on us and use what we know how it works. Right? So I'm going to go ahead and do it this way for now. And now that I've returned the lessons here and the completed status, it means I have these lessons with completed status.
So what I have to do now is return the existing unit and append the new lessons which are going to be lessons with completed status. Lessons with completed status. So let me see if I did this correctly or not. My apologies, not here, but here. This is where I'm supposed to add them.
So make sure that they are in the correct scope. I'm gonna zoom out just a little bit so you can see this clearly. So we have normalized data. This is the scope and this is where exactly does it end normalized data why is it not yeah basically this is where it ends but my or maybe I forgot to add an end this now I'm a bit confused oh my apologies All right, so this is our getUnits function which ends right here. Let me add a semicolon here.
Then this is our normalized data map which opens this arrow function which ends right here. My apologies. So inside of this data.map we create lessons with completed status. In here we get a boolean whether all challenges are complete for an individual lesson. If they are, inside of this map iteration of the lessons, we return that very lesson but we introduce a new field called completed with a boolean true or false depending on whether all challenge progresses have the completed status.
And then I'm at the same time iterating over normalized data, so all the units here as well. So then what I do is I use these lessons with completed status and I spread the existing units here, but my apologies, I should not spread the existing units. I should spread the existing unit. So make sure you don't do the same mistake as I do because this is iterating over that specific unit. So we spread all the fields in that unit, which is the ID, title, description, course ID and order.
And we already have the lessons, but I'm going to replace those lessons now so that all of my lessons have a new field called completed. So then on my front end it's going to be very easy for me to just check in a dot map whether a lesson is completed or not. So that's what I'm doing that. So I reduce the workload on the front end and then what I have to do is return normalized data. There we go.
So that is my getUnits method. Perfect. Now that we have that we can go back inside of page and we can add units data so const units data is going to be get units and make sure you import get units from database queries right here and let's go ahead and let me in fact load getUserProgress first actually it won't matter since they're in a promise all but yeah let's go ahead and add our units from units data there we go now we have our units so let's go below the header here. And let's write units.map. Let's get the individual unit.
Let's create a div. Let's give it a key of unit.id, let's give it a class name of margin-bottom-10 so each unit has a spacing and in here I'm going to JSON stringify my unit. So let's try this out. So I'm gonna go and refresh this and I have to have my app running as well so let me click on terminal here and do npm run dev and now let's go ahead and see our there we go Spanish we have a title unit one and that unit one has lessons And that lesson is an array and you can see we have a title nouns and then in here we also have completed which is false because these challenges all have their challenge progress. But as you can see, challenge progress for us is an empty array because we never completed any challenge, meaning that there is no way this lesson could have been completed.
And this is very useful for us now that we have this completed because we can easily change the design on the front end now. And this is of course going to be cached so if we repeat this method across our app at the same time it's not going to make too many calls to the database. Again, I recommend you do this exactly as I'm doing it the first time you're developing, especially because it's a bit of a complicated normalization of the data here. So then later, if you want to, and I highly advise that you explore, can you do this with the Drizzle Query Builder. If you can do this with pure SQL that would be amazing and much much faster or perhaps you can do this with Reduce if you think that will be a more optimized loop instead of a loop within a loop, right?
So obviously this is not the fastest way of doing it but I think it's good enough for a tutorial. So in the next chapter we are gonna go ahead and turn this JSON stringification into an actual UI design. Great, great job!