So now let's actually create a new table inside of our schema where we are going to store which course is the user currently taking. So besides the courses let's go ahead and write export const user progress. That's gonna be a pg table again with the name of user underscore progress. Let's give it a user ID which is going to be a text. We're gonna call it user underscore ID and that's gonna be our primary key because it's going to be unique for every user.
Let's also store the username here. That's going to be text user underscore name. Let's give it not null and default is going to be user. Let's give it user image source. Which is going to be text user image source not null.
And default is going to be slash mascot dot SVG. And then let's pass in the active course ID that's going to be an integer so let's import integer from drizzle or mpg core so make sure you add it from here not from mysql or something else it's going to be called active underscore course underscore ID and we're going to add a references to it open an arrow function and call courses dot ID from above And now in the last argument we can open an object and define how it behaves on the lead of the parent. In our case we want it to cascade. And then let's add the hearts which are going to be an integer again called hearts. They're not going to be null and the default is going to be 5.
And let's copy this two more times and let's call this our points. And actually we just need it once and let's give it a default points of zero. And now let's go ahead and do the following. So let's go ahead inside of the courses here and let's export const courses relations and let's add relations from drizzle or m courses as the first argument and let's extract the many from the arrow function here and in here actually let's wrap this in parentheses because we want an immediate object return and now let's write user progress to be many user progress like that and then so just make sure you imported the relations from Drizzle ORM. Then let's do the same thing here.
So let's write export const user progress relations, relations user progress, the structure one, and again open immediate object here, and write active course one courses, open an object fields user progress, dot active course ID, here and write ActiveCourse1Courses Open an object fields UserProgress.ActiveCourseID and then references Course.ID My apologies, Courses.ID There we go. So let me zoom out so you can see how this looks all in one line. So we defined a relation many to many for user progress and courses and we defined a one to many for user progress and an active course. So let's go ahead now and push this to our database. So I'm gonna go ahead and shut this down and I'm gonna write what is our method npm run database push.
So let's wait a second and see if this will be successful. So there we go it says changes applied. So let's quickly go ahead and visit our Drizzle studio here so Ambient Run Database Studio and let's see if anything has changed. There we go. So we now have a relation with the user progress and we have the user progress table.
So let's go ahead and actually create a method which will happen once the user clicks on one of these flags right here. Also, if at any point you get stuck with drizzle migrations, right? So if you have a feeling you wrote something wrong here before you did the database push, right? And it gets stuck. The way to resolve that is to very simply change your environment key.
So simply remove your database, create a new free database, and just add a new database URL and then with your new full schema do npm database push again. So I am gonna demonstrate that at one point in the app if we get stuck and if we have some migration issues. So Prisma has something called NPX Prisma Migrate Reset, which just resets the entire database and all migrations and everything. Drizzle does not have that. So you would actually have to do like a proper migration, which is of course the right thing to do.
But in development, I just find it quicker to delete my database and every single table, everything a lot of times rather than doing the migration. Right. So that's where you can always visit NEON database and remove your database from there and then just add a new Postgres URL to your database URL if you even get stuck, right? Great, so now that we have this I want to go ahead and head back inside of our app folder inside of main courses and specifically inside of this page.tsx and in here what we're gonna do is we're gonna change this so it actually loads the user progress as well. So let's go ahead and keep this open and prepare a query for that.
So open this, we already have getCourses and now let's add export const get user progress so that's gonna be cached as well it's gonna be an asynchronous method and let's go ahead and let's extract the user ID from await out from clerk next JS so I'm gonna move this with this import here if we don't have a user ID it means we cannot load that so I'm gonna return null there is no user progress for this user otherwise let's do const data and let's do await database.query.userProgress find first where equals let's import equals from drizzle ORM and let's pass in the user progress from dot slash schema so I'm going to go with s slash database schema so user progress dot user id and comma user id So those are the values which we are comparing. And let's add with active course true. Whoa. So we also populate the active course relation. And then let's return the data.
There we go. So now let's go inside of our courses page here. And what we are gonna do here is use that new query. So just below that, let's add const userProgress to be await getUserProgress from database queries like that. And then for this active course ID we're going to do user progress question mark active course ID And if I save this I'm going to get an error here because I have a type of number in the list here.
So this is the perfect opportunity for us to do the thing I was talking about and change this to be an exact type of that schema. So I'm going to use typeof here and I'm going to use the user progress import from database schema. I'm going to use infer select and I'm going to select the active course ID and then I'm just going to mark this as an optional prop and there we go no more errors here and now as you can see no language and no course is selected for me right Because I have not chosen any of the languages. So what I want to do now is I want to add a redirect from the Learn page if we don't have user progress. Also, if you want to, you can change this to be the following.
So if you want to use Promise All for this, you can do Courses Data here and Courses Data here, remove the await here, and then you can do const promise.all and passing the courses data and user progress data and then from here you can extract the courses and the user progress. So this should work exactly the same. Did I do something wrong here? Let me just quickly check. Oh, this is supposed to be await promise all.
There we go. So if you want to you can do that. I don't think this exactly helps you progress. I have a typo here. I don't think this exactly helps you with the waterfall because this is a server component, right?
But if you want to have them inside of a promise all, you can do it like that. I think this is recommended as a best practice in Next.js documentation. Again, I'm not entirely sure this resolves the waterfall issue because this is a server component right so the network tab is not exactly being blocked at least I think that's how I understood it right so but I am going to be using this practice throughout the tutorial for when I have a lot of these calls. So in here I removed await so the only thing I prepare actually is a promise. So by default I cannot do anything with this.
So perhaps data is not the best naming, we could call it promise. But you know, I know what I mean by data here. And then I pass that in the promise all and then I extract the value of courses and user progress and use it as I did a moment ago. So let's go ahead back inside of our learn page. So app folder, main, learn, page.tsx.
Let's mark this as an asynchronous page. And in here let's go ahead and prepare our user progress data to be our get user progress from database queries and let me add them here to the top. And then in here, I'm gonna go ahead and call await promise all user progress data. And I'm gonna extract user progress. So let me prepare the collapse of this because we are going to have multiple items in the future.
So very simply, I'm going to do, if there is no user progress or if there is no user progress active course, in that case, let's redirect using next navigation to slash courses. So import redirect from next slash navigation and let me just move that to the top here. There we go. So now that we have this right here, if you try and go to the learn page, you should be immediately redirected. So if you go from the home page here and click continue learning, there we go, you can see how I'm immediately redirected back to this page.
And you did see a little blank screen here for a second. Don't worry, we are gonna add the loading states. Perhaps we can already add the loading state. So let's consider the learn here and let's add a new loading.dsx here. Let's import the loader from Lucid React and let's do const loading.
Remember to prepare a default export. So loading in here. We're gonna do a very simple div, which takes the full width, the full height, and places the items in the center of the screen both vertically and horizontally. And inside we're going to use the loader component. We're going to give it a height of 6, a width of 6 and text muted foreground and animate spin.
Let's go ahead now and try this again. So you can also copy this loading and paste it in the courses. And there we go. So now if I refresh this, you can see how we have a nice little loader if I go to the learn page there we go so we no longer have that blank screen now there are actual loaders inside of our applications while we are being redirected. So what I want to do to wrap up the chapter is that when we click on this is we actually create a new user progress inside of our database.
So let's first prepare the queries which we are going to need while we create this database action. So inside of database, let's go inside of queries here and let's export const getCourseById. It's going to be cache asynchronous method which will accept the course ID which is a number. Of course if you're working with UUIDs yours is going to be a string. So in here let's do const data await database query courses find first where equals, we already have this imported, courses.id, course ID.
And let's import courses from database schema. So just make sure you have courses and user progress and you already have equals imported from Drizzle ORM. And let's return data. And I'm going to add a little to-do here. To-do.
Populate units and lessons. So we don't have these tables yet, so we cannot do that. But later, when I load a course by ID, I want all the lessons and units it has. So this is fine for now. What I want to do now is I want to go inside of my list component in the app main courses.
So inside of the list component here we mark this as use client because we're going to have some interactive on click here. So let's go ahead and do the following. Let's add the router from use router, which you can import from next navigation. Don't accidentally import it from next router. That is the old import.
Then let's extract pending and start a transition from use transition which you can import from react itself so this is going to help us to use a server action and its pending state And then let's do const on click to accept the ID, which is a number. Again, if you're using UUIDs or anything similar to that, it's gonna be a string for you. If we are pending, let's break the function. No need to allow it. If the user clicked on a course, which is currently the active course ID, no need to do the whole database update.
We are just gonna break the function with the return and we are also gonna redirect the user to slash learn. And then let's do start transition. So this is if user is selecting a new course. In here, we're gonna call our server action, which we don't have yet. So let's go ahead and create a server action.
So in the root of my application I will create a new folder which I'm gonna call actions and inside of here I'm gonna create the actions for the user progress entity. So I'm gonna call the file user-progress. Very important whenever you're writing server actions is to have useServer at the top of your file, otherwise it's not going to work. So let's write export const upsert user-progress. It's going to be an asynchronous method with a course ID and a number here.
And then let's extract the user ID from await out from clerk next JS and d is a lowercase. Let's also get the entire user from current user from clerk nextjs. If we don't have user ID or if the user is missing, we're going to throw a new error from this server action called unauthorized. So whenever you're working with server actions, behave like it's an API call, right? So you need to do authorization here as well.
Then let's attempt to fetch the course which user just selected. So await getCourseById from this database query which we just created a moment ago and pass in the course id. Then if there is no course we're gonna throw back a new error which will very simply say course not found. So this will be a 404 if this was an API route. And here's what I'm going to write.
If there is no course.units.length or if course.units.firstInTheArray.lessons.length. So if we don't have any lessons or units, I'm going to throw a new error here. Course is empty. And then I'm going to comment this entire thing out because we don't have units or lessons at the moment. So at the top I'm going to add one more comment to do enable once units and lessons are added.
So right now no need for this error but I don't want to forget it later. And then let's go ahead and do the following const existing user progress and let's do await get user progress from our database queries so we already have this method created and we've used it a couple of times already. Let me go back inside of my user progress server action here. So if I already have existing user progress this means that the user already has been active in some course. So all I have to do is await database from database drizzle so just make sure you add this dot update and in here I'm going to use the user progress schema so make sure you add that from database schema here and we're going to do dot set here and we are going to update the active course ID with course ID.
As simple as that. And just to improve our user experience here we can also update the user name here with user.firstname or let's go ahead and add user as the default or and we can also the user image source with user. And let's go we're using clerk so let's find where our image is here is it profile image url it says deprecated, so now it's image URL. User.imageURL, there we go. Or slash mascot.svg.
Like that. So we don't have to do these two fields, but since we are using an external out provider, when user changes their profile image using this manage account method right here, it's not going to be immediately reflected to the leaderboard, right? So we're going to do the following. Since we don't have any webhooks or any background jobs which keep track and synchronize the state of clerk and our database, every time the user changes a course we're gonna do a proactive update of their name and their image URL just in case they have changed it. And the reason I'm doing this pipes here is just in case these two fields fail, we're going to go ahead and fall back to what we have defined in our schema here where I use user as the default username and mascot.svg as the default user image source.
And this mascot.svg we are already using so you don't have to add anything. We already have that in our header and in our sidebar. So this is our logo. So let's head back inside of our user progress here. So that is if existing user progress exists and otherwise here's what we're gonna do.
We're gonna do await database.insert userprogress.values userid because we don't have that so we have to pass that the first time we are creating it. Active course ID is going to be the course ID that the user selected and then we can just copy and paste the user name and the user image source here. Great! But that is not enough. So this will definitely create a new user progress in our database.
But remember, All of our queries are cached, which means we need a way to revalidate this cache which we are using. And Next.js has a method of doing that. So we need to import the following. After we add a new record to the database. Let's do revalidate path from next slash cache.
So let me add that to the top here. Next slash cache. I'm going to revalidate the following path. Slash courses slash learn and from the server action I'm gonna redirect the user again we're gonna import this from next navigation I'm going to redirect the user to slash learn and let me copy this and do this also in the existing user progress here. So this will break the method right so once we hit this it will not go further.
So now that we have this set up let's actually go ahead and use this upcert user progress. So let's go inside of the list here and let's go ahead and add upcert user progress imported from actions user progress and let's pass in our ID and that should be it. So let me just go ahead and check this out and let's pass this on click right here. I think this should be enough. OnClick accepts the ID.
I think that should work because our card will pass it like this. Great. Let's try it out. So inside of my Drizzle Studio, let me refresh it here. I don't have any user progress here.
Once I click on Spanish, I believe, there we go. I am redirected. Now remember this is hardcoded to Spanish so this actually has no, there's no reason why this is a Spanish flag. We are going to do that later. But let me just refresh my user progress now.
There we go. We have an active course ID of one which resembles Spanish. We have five hearts, zero points and we have a full-on relation with the active course. Perfect. And we have the user image from Clerk and my username and my user ID, which is also my primary key.
If I go back to courses, there we go. Spanish is now selected. And now let me just go ahead and improve this by actually using this pending state. So if I go back to list here, I have hardcoded the disabled to be false. Well let me actually use pending now.
There we go. So Now if user tries to click on French, there we go, you can see how it's disabled while it's loading. Perfect! And French is selected. I can go Croatian and there we go.
And I can always go back to Spanish, but this is going to call upsert and not insert as it does with Italian, for example, this is inserting. Right now I have all of this. So now my user progresses. So of course, you only have one user progress for user, right? So we are only going to change the active course ID.
The actual progress is going to be stored in other additional tables, right? But we are going to know which table to load for which course thanks to this user progress which will tell us, all right, the last time user selected this course ID, so load the progress for that course ID. Great! So we officially have a course selection. One more thing I want to do before we wrap it up.
So I want to go ahead and I want to install Sonar. So let's do npx chat-cn-ui-at-latest-add-sonar. So this will add Sonar to our project. So let's go inside of our root app layout here and let me import toaster from components UI sonar and inside of the body here I'm gonna go ahead and simply add the toaster component. Self-closing tag, like that.
Now let's head back inside of our list component where we just added this start transition and upstart user progress. And let's add a very simple .catch here where I'm gonna go ahead and call the toast from sonar package so not from any add components from sonar directly so in layout we called components UI sonar but in here we call the sonar directly. And let's go ahead and do an error here. Something went wrong. And now if I go inside of upsert user progress and let's pretend that we have an error here.
So I'm going to write throw new error. Test. So let's say one of these goes wrong, right? Either course not found or unauthorized or in the future, maybe this. Let's see if that will log a nice toast message.
There we go. Something went wrong. Perfect. So just remove this test, we don't need it but now you know that you have a nice edge case covering here. Perfect, great, great job.
What we are gonna do next is we're actually gonna use the user progress to indicate to the user the actual active course. So if I click on creation we expect this to say creation and here to be a creation flag. We also want the points and the hearts to be an actual resemblance of what is in the database. We are also going to learn how to seed our database with a script and how to completely reset our database in case we run into any problems. Great, great job!