What I want to do now is wrap up our entire schema. So far inside of our database folder, schema here, we have defined the courses and we have defined the user progress. We have also defined the relations between the two using courses relations and using user progress relations. So what I want to do now is wrap up my entire schema with everything else that needs to be added in here. The reason I want to do that is because for the next queries which we need to load, which in here are going to display the current unit, the current lesson, and all the subsequent other lessons as well as their status, whether they are locked, finished, or unlocked, we have to have all the necessary fields because the only way we can create such complex queries is by querying those relations in the database.
So let's go ahead and do the following. The first thing I want to add is units. So let me add that just below the courses here. So export const units is going to be a pg table of units. It's going to have an id, which is going to be a serial of id and a primary key.
We're gonna have a title of each unit which is gonna be a text title and it's gonna be required meaning not null and then we're also gonna have a description. Let's give that a text of description and not null as well. So the title would be something like unit one and the description will be learn the basics of Spanish. For example, then we would have the course ID. So we know for what course is this unit for, right?
So if our description is going to be learn the basics of Spanish, we have to have a matching relation with the Spanish course. So let's go ahead and make this an integer. Let's do it like this, course underscore ID and let's add a reference here. So I'm gonna write dot references and I'm gonna open an arrow method and I'm gonna provide it with course dot ID, courses dot ID, so we read from this constant above and then we're gonna go ahead and define onDelete cascade and let's add .notNull at the end So it should all be in one line like this. Great.
And besides the course ID, we are also going to have an order of this unit. So we know how to render it and how to display it on the learn page. So I'm going to pass this to be order and not null as well. Perfect. Now let's go ahead and let's create a relation with the units.
So I'm gonna write const unit relations, relations, units. Let's extract many and let's extract one because we are going to need both. But for the first one we just need one. So let's write course to be one, courses, fields. Let's go ahead and use the units.courseId from above and let's reference that to courses.id field right here.
That's all we can do for now. So now what we have to do is we have to create lessons. So we have unit relations here. Let's go below here and let's write const. My apologies, I forgot that we also have to add units to the courses relations here.
And let me just move the courses relations right below the courses like this. So I'm going to go ahead and also add units here. Many units like this. So make sure inside of your courses relations you have added units, many units. So courses can have many units but each unit can only have one course relation.
So make sure you have that setup working. You can of course always visit my schema from the GitHub source code and I recommend that you always do that so you confirm that you didn't write anything wrong. Great, so we have defined the unit and unit relations. Now what I want to do is write my lessons. So let's write lessons to be a pg table of lessons.
Let's write id, serial id and primary key. Now let's go ahead and give our lesson a title, which is going to be a title text and a title and not null. Now let's go ahead and give it a unit ID so it has a relation so that's going to be an integer unit underscore ID dot references we're going to pass in units dot ID on delete cascade and not null perfect And we are also going to have an order of a lesson as well. So order is going to be an integer, order and not null. We could technically use the serial or integer incrementing ID for the order and that was what I did initially, but later I found that that's probably not the most reliable thing to do and it's better to have something else to keep track of how we are going to order our lessons and display them to the user here, right?
So lessons are going to be those little islands which you saw in the beginning that user will be able to click on and the units are going to be the big kind of like chapters which hold multiple lessons inside. So now that we have defined our lessons here, we have defined that each lesson needs to have one unit ID. So let's go ahead and create the relations for that. So I'm going to go ahead below this and write export const lessons relations. So let's write relations lessons.
Let's extract one and many. And we return an immediate object here like that. And let's give unit a relation of one units fields is going to be lessons.unitid and references are going to be units.id just like that. So That's all we can do for now. But now that we have lessons defined, we can go back inside of the units relation right here and let's call this units relations.
So if our model name is units, well, let's call them units relations as well and lessons lessons relations so we are consistent so I prepared the many extract here but we didn't have anything to create a many-to-many relation So let's say that units can have many lessons inside. My apologies. Lessons, many lessons. Like that. There we go.
So, each unit can have many lessons. But individual lesson can only have one unit. And something is missing from the units relations and that is that I export this constant. Perhaps you have done this. I have forgotten.
There we go. Now we have lessons and we have lessons relations. But each lesson is going to have something else. Each lesson is going to have a challenge. So let's go ahead and create that as well.
We can copy this in fact. So let's just copy lessons now so we save some time and let's rename this to challenges. And let them remove everything besides the ID. So we're gonna have an ID which is primary key, we're gonna have a lesson ID which is going to be an integer, lesson underscore ID, references, lessons dot ID, on delete is gonna be cascade and again not null. All in one line like this.
Besides the lesson ID, we're going to have a type of a challenge. So to create a type of a challenge it's best to use enums. Let's do export const challenges enum and let's use pgenum from drizzle-orm slash pgcore. So make sure that you have imported pgenium as I did right here from drizzle.orm.pgcore. So just where you have your pgtable, serial and text already.
Inside of the pgenium here we're going to define a type And the possible types are going to be Select and Assist. So in the future, if you want to add a different type of challenge, like a Voice or Listen or something like that, you can just add them here, and that will solve the backend part for it. So inside of the challenges here, we now have to define a type. So we're going to use a type of challenges enum like this and let me just be consistent so challenges with an e and in here I use challenges so let me just fix that little typo challenges enum and we're going to store this in the database as type and again not null so this is required. And then we're gonna have a question which we are going to ask the user so that's gonna be a text of question and not null.
Perhaps question is a bit too specific because I am going to reuse that in two different ways. In one type of challenge I'm going to reuse it to for example which of this is an apple and then the user will have to select the proper answer. But in the assist it's going to be different. The question is simply going to say apple and then the user will have to select the proper answer. But in the Assist, it's going to be different.
The question is simply going to say, Apple, and then the user will have to select the translation. Right? So it's going to be a bit different. So if you want to, you can change this to maybe label, but I'm going to stay true to my original source code, so I'm gonna leave question, just so I don't create any problems. And I'm also gonna give a useful order field here, so in case you want to store your challenges by hardest or by some other arbitrary value, you can always do that.
So they are not, you know, added to the user in a random order. You will have control which challenge will appear to the user first in a given lesson. So now that we have defined this we have to create challenges relations. So let me copy the lessons relations from here. Let's rename them to challenges relations.
Make sure that you change the first parameter here to be challenges, the structure one and many and let me clear this inside. So we're going to have a lesson which is going to be one lessons with two S, Fields is going to be an array of challenges. Lesson ID and references is going to be lessons.id. And now we have to go back inside of our lessons relations and we can now use the many property. So write challenges, many, challenges with an E.
There we go. So perfect, we now have back to back relations with challenges and with lessons and with units and with the courses in the end. But some stuff is still missing. So let's go ahead below the challenges relations and we have to create two more. So we have to create challenge options and we have to create challenge progress.
So let's go ahead and copy the challenges tab right here and let's go ahead and create challenge options. Let's rename the pg table to challenge options and let me see do I have challenges misspelled anywhere. I do have again challenge options it should be challenge options make sure you don't have this typos so this needs to be consistent right so I just copied and pasted this from challenges itself right so that's where we left off we renamed the constant and we renamed the PG table so what I'm gonna do now is I'm gonna go ahead and change this from lesson ID to be a challenge ID I'm gonna change the integer to be and again I wrote challenge ID challenge underscore ID which is gonna use challenges oops challenges dot ID on delete cascade is correct. Instead of type well we're not gonna have type in the challenges options so you can remove that. The question is not going to exist instead it's just going to be text and we're going to store that as text in the database.
The order is not going to exist for challenge options. If you want to control challenge options, you can do that as well to nudge the user in the right direction maybe, but these are basically answers which users can choose between, right? So I don't have the need to order them. So in here I'm going to give them a correct option which is going to be a boolean correct and not null. And let's also import boolean from pgcore.
So just make sure you are using this imports from PG core. I just added Boolean. Make sure you didn't accidentally import it from MySQL, for example. Great, so we have the challenge ID relation with challenges.id on the lit cascade. We added the text, we added correct.
And now we have to add an image source which is going to be a text of image underscore source which is not going to be defined which is not going to be required because if the challenge type is assist in that case we don't have images to show the user and we're also gonna have audio source which I also want to add I also want to make optional because it is very useful and we are gonna make it have sounds but in case it doesn't it should not exactly break the app right In case you don't have an audio for something, you still see the text and the image if it exists. So audio is not exactly something we should rely on. Great, so we have the challenge options ready here. Now we have to copy and paste these challenges relations and create challenge options relations. So let me change this to be challenge options relations.
And let's go ahead and use the challenge options column from above. Now let's extract just one. We don't need many at all. And instead of having a relation with the lesson, we're going to have a lesson with the challenge. So we're going to use challenges.
The fields is going to be challenge options dot challenge ID. So this one which we defined here and the reference is going to be to challenges.id itself. There we go. So we created challengeOptions relations and now we have to go back to our challenges relations where we have prepared the many extract so besides lesson challenge is also going to have a challenge options in a form of a many relation. So let's add challenge options and there we go.
Now, each challenge can have a lot of challenge options, but each individual challenge option can only have one challenge. Great. Now that we have defined that, there is one last thing that we need to add which is the challenge progress. So let me copy and paste this thing again and let me just... Okay let's keep the user progress here at the end.
Instead let's copy the challenge options relations and the challenge options And let's rename the copied challenge options and the PG table challenge options to be challenge progress. Like this. And let's leave the ID and let's go ahead and add a user ID which is simply going to be a text of user underscore ID like that and let's leave the challenge ID to be exactly as it is. So challenge ID, challenge ID, challenge.id, onDeleteCascade and not null. And very simple thing we're going to do is add one additional field, completed, boolean, completed, dot not null, default, false.
So this is how we are going to track which challenge has user completed. And then we're going to use the challenge progress to later calculate the percentage completion of an individual lesson. So every time the user selects a correct answer, we are going to upsert or create a new challenge progress with completed true in the database. And it's going to have a relation to the challenge ID like that. And let's also add, let's make this required.
So if we don't have the user ID, we can't exactly do anything with the challenge progress after all. So let's make this not null. I'm going to add a to-do confirm this doesn't break. It shouldn't but I notice I don't have this in my original source code but in here I'm noticing that it should probably be required. So I have this little to do here and now let's go ahead and let's create challenge progress relations like that.
So let's go ahead and use the challenge progress constant as the first argument here make sure you change that and it's going to have a relation to the challenge using the challenge progress dot challenge id and the reference is going to be the same so this can stay exactly as it is and now Let's go back to our challenges relations. So above challenges options, in here we have the challenges and the challenges relations. We have defined a new challenge options, many. Now let's do challenge progress, many challenge progress. There we go.
We have wrapped up our entire schema. There is one thing missing here, which is the user subscription column. But I want to leave that last when we are actually going to work with user subscriptions, because if we write it now, we're gonna kind of forget what we've written. But everything that we've written now is what we're gonna build next. So we do need these relations to work as they should.
So let's go ahead and check if we have everything in order. So I'm gonna go ahead and do the following. I'm going to npm run database studio because I won't see my studio here. So let me open that up and there we go. We have some problems.
Relation challenges doesn't exist. So let me close the Drizzle Studio and let me try running npm database push. Let's see if that is going to break the app. Will this work as smoothly as I expected? And there we go!
I just got changes applied. So this should mean that everything runs pretty smooth. If you for any reason did not get changes applied here, you can do what I did in the last chapter, which is completely nuke your database. So don't just run database seed, which will simply remove the fields. No, go inside of your neon or wherever you have your database running and completely delete the database so you purge all the existing tables and relations and then try running npm run database push again.
So I just run that first time. Now I'm running it the second time. No need for that, but I just want to see what happens. There we go. It still says changes applied.
So if I go and run database studio now, let's go ahead and refresh this to see do we have any errors now we do not have any errors but what we do have is all the necessary fields so challenge options, challenge progress, challenges, courses, lessons, units and user progress. Also if there was a problem with relations here, Drizzle would have thrown an error, but it didn't. This means that we have coded everything correctly. So if you're having any relation problems here, I highly advise that you take a look at the GitHub source code or ask a question in the Discord, but please search for it first, perhaps it has already solved, and then just go ahead and copy and paste each individual table until you notice one that you have written incorrectly and then you can see what is it that you have missed. Great!
So one thing I want to do is go into Neon and confirm that I have those tables in there as well. And there we go. So I am inside of my lingo project inside of tables here. I have challenge options, challenge progress, challenges, courses, lessons, units and user progress. Perfect.
Exactly what we need. And let me just, oh, I just noticed something. So challenge progress and challenge options should not be camel case, they should be with an underscore in order to stay consistent. I don't think it will break the app but let's stay consistent and let's use this ability which we have from Drizzle here. So let's find the pgtable challenge options and let's change them to be underscore challenge options and then let's do the same thing for the progress.
So I'm gonna do challenge underscore progress like this. So we should not have any camel case in the definition like this. So this camel case is fine but inside of the definition that goes into the database it should be with an underscore. So here's what I'm pretty sure I'm gonna have to nuke my database. Now, of course, in production, you would do a migration for this.
So I have to emphasize that, right? What I'm doing is just something I want to speed up for development. So if I try running npm run database push, Let's see if perhaps this can be resolved easily. We'll see. So NPM running and there we go.
So now Drizzle is asking me, is challenge options created or renamed? So I'm going to select using the arrow keys to rename the last option from Challenge Options CamelCase to Challenge Underscore Options. So I'm going to select that. And then it's asking the same thing for Challenge Progress. So it's not created, it is renamed.
So let me select the last one, challenge progress to challenge underscore progress rename table. Let's see if that will be, there we go, all table conflicts resolved, changes applied. Let's try this one more time. So now if I refresh my neon here, let's see what happens, there we go, challenge underscore options and challenge underscore progress and to wrap it all up let's also confirm that inside of my studio here so I'm just gonna go ahead and refresh this and there we go challenge underscore options and challenge underscore progress again if you're having any errors here you can go inside of your databases here and go ahead and click delete and remove it and then click new database and try DB push again. Great, great job!