Subscriptions

For this chapter, I have decided to build subscriptions for a very simple reason, and that is that it looks almost exactly the same process as our last two chapters. So it just makes sense to go in this direction. In order to build descriptions, you're going to need to establish two users at minimum inside of your project. So in my another account here, I have logged in using Clerk and I went into studio and what I'm going to do is I'm just gonna go ahead and upload a video here and once I can go inside of this video I will just copy this URL from here and I will paste it in my another account here. Basically, the goal is that I can visit the video from this another user and that I can see the subscribe button.

Because if I just look at my own videos I'm going to have the edit button here. That's not what we want. We want the subscribe button. So let's go ahead and develop this now. So just make sure you do that.

Create a new account, upload the video from that account, copy the URL from here, and then paste it wherever you're logged in as the other user. You can use a single browser for this. No need for two of them to be logged in at the same time. Great, So let's start by creating the subscriptions schema. And yeah, one thing that we kind of left off in the last chapter was this, grouping.

I've tested with multiple accounts. I have tested with multiple likes and multiple views and dislikes and all of the combinations and nothing seems to break. So I, because of that, I'm led to believe that this group by of mine, which I have in my source code, but seemingly do not need now when I'm building it, is that this group by was either a premature optimization by me, or it was a solution at the time when I didn't use this kind of subqueries and perhaps used a different type of aggregate which then required groupby to be added. I will still have it here just in case I'm wrong but I'm 99% certain that was the case. If you understand grouping better than me, you probably already resolved this for yourself, right?

But I'm trying to understand as I go along. There are a lot of things here that I have to compare with my original source code with what I've changed for the video and things like that and I just want to make sure that you don't have any errors and that everything works fine. So for that reason I will leave it here commented out just in case but as I said I'm 99% certain that this was a premature optimization on my part and that this query works just fine and doesn't need any grouping because of the way these subqueries here work. I think that previously I was doing a different type of counting here which then required me to group by these specific types here. Let's go ahead and let's do the first task which we need for our subscriptions and that is building the subscriptions schema.

So let's do that after users and after users relations. So export const subscriptions will have bgtable subscriptions like this and let's go ahead and pass in the viewer ID which will be uuid viewer ID and references to users ID. On delete, cascade. Let's go ahead and now add the creator ID. Creator ID here and the same rules.

Let's add not null to both of them so they are both required and let's copy the timestamps. So one interesting thing about this query, I mean this schema, is that both my viewer ID and creator ID will reference to the user. Right. So that is a bit different than from anything we've had before, I believe. And this should already be enough for us to push to our database.

I believe nothing should go wrong at this part. Let's see, there we go. Change is applied. Nothing has broken. Great.

And if I go ahead and go inside of my Drizzle Studio here, Let me go to Studio. I should see my new subscriptions table right here and I should have the viewer ID, the creator ID, and then I should have these two, viewer ID, user ID, and Creator ID, User ID. So these are basically the relations, right? You can also see them here. And I believe that the reason they are named like this is either because they both reference to user.

So the application automatically differences them by adding these descriptive names or because we didn't yet add the relations here. I think it might be that. Yes, I'm almost certain it's that. So yes, basically you can see what this relations things does. It works in application level.

So now I think that after we create these relations for subscriptions, which as you just saw, not required to push to your database, but they will make the working with Drizzle a bit easier. So let's go ahead and do that now. So we're going to add export const subscription relations. Relations, subscriptions, and we're just gonna work with one. Viewer ID will be one users, fields, subscriptions, viewer ID, references, the user's ID, like this, and creator ID will do a similar thing here.

Well, not similar, literally the same thing, except it's going to target creator ID. But I think that just like this, this might throw an error. And I purposely want to push this so I can demonstrate that. So I'm going to push. And I think that we might get an error now even though this usually I'm not certain.

Looks like no changes detected. Looks like everything is okay. But I think that in my Drizzle Studio I'm not sure if this will immediately change this but how about in my subscriptions? So it still says viewer ID, user ID and creator ID, user ID. How about if I add a relation name here and name it subscriptions, viewer ID, oring key.

And here, this will be creator ID or in key. And we of course also have to do this. We have to go inside of our user relations here and we have to add subscriptions. Oh yes, I think this is the problem. All right, so I'm going to remove this too.

Purposely, I want to get an error because I want to show you how you can fix it and why we need those things which I just added. So you would now add, you know, user subscriptions and user subscribers in the same way. This is the issue now with the relational API. Remember, this part is not required for us to continue querying because we are using SQL like a builder, right? But for application level Drizzle to understand our relation, it would be nice for us to add this to user relations.

But this will obviously cause problems because how does it know which one of these two is supposed to target the creator ID and which one is supposed to target the viewer ID? So I think that this is what I was trying to demonstrate. I think that if I try and do drizzle kit push now, I think that this is maybe where I get the error. Or maybe will it still not detect? It looks like it's still not detecting.

How about if I refresh this? I'm desperately trying to get an error because When I was developing, I got an error. But right now, this seems to be working with absolutely no problem. And it's weird how it's working. But one thing that we are missing in the subscription, it's very important, the primary key.

Let's go ahead and just do that before we try anything else. Maybe now we're gonna get some errors. So let's add primary key here, name, subscriptions primary key, columns, viewer ID, and creator ID. Very important thing I forgot. So make sure you've added the primary key.

And for this, you will have to push. So let's see. Hopefully, this works. If not, well, we will go into debugging. But yeah, there we go.

It looks like this works just fine. Let me refresh my studio here to see if we are getting any errors. No, everything still seems to be working just fine. But we are still seeing this named like this. So this is what I'm going to try and do to distinguish between them.

I'm going to go ahead inside of subscription relations here, and I will add a relation name, subscriptions, viewer ID, or in key. So you saw me do this a few seconds ago, but didn't understand why. Well, I'm trying to rename these columns in Drizzle Studio to be exactly like this. And I think that the reason it cannot do that is because of this, right? It doesn't know which one is which.

So I'm trying to make it clearer for it. So let's use the creator ID here. All right. And now let's go in the user relations here. And in the subscriptions, let's go ahead and extend this by adding the relation name.

And let's copy the viewer ID foreign key like this. And in here, I'm going to copy the creator ID foreign key. So my subscribers will look for this and my subscribers will look where I'm the creator and my subscriptions will look at where I'm the viewer. So let me go ahead and do a push here just in case and I will also restart my Drizzle Studio. So I will do both of those things.

Let's first push to ensure no errors here. Okay, pulling schema, no changes detected. And let's try Bonnex Drizzle Kit Studio here. And let me just refresh in case we're still seeing exactly the same thing. There we go!

Subscriptions and subscribers. So we can now differentiate between the two thanks to the foreign keys which we have created like this and then we were able to specifically target for which key here are we looking at. So that's what I wanted to achieve since we're already building this relations API things, which again not required for any of the joins that we are going to do, any queries not required at all, but on the application level it will give us a much better understanding. So subscriptions and subscribers. Do you agree this looks better than what it was before?

Right, I think this is now clearer. Great, So let's go ahead now and let's build the procedure to subscribe and unsubscribe. So for that we're gonna go inside of modules and I think we already have subscriptions UI so we're just gonna extend by opening our server procedures.ts right here. We're going to do a very simple procedure in a form of create and remove. And to save some time, let's go inside of video reactions and just copy the entire procedure and just paste it in subscriptions procedures here.

Let's rename this to subscriptions router and let's remove this and just use subscriptions here. And this will be create like this and we can remove the dislike one because yeah let's just focus on the create one. So it's going to be a protected procedure and it's going to accept a user ID that we want to subscribe to. So let's destructure the user ID from here, from the input. And I'm just going to not destructure the context, so I don't override the user ID because in this case the input is the important one.

And this is what I'm going to do. So how about a... Go ahead and let me just remove this entire thing here. I think it's just going to be easier like this So I can focus. First thing I want to check if the user ID is identical to context user ID this means that the user is trying to subscribe to themselves.

We're going to throw a new TRPC error here. Code bad request. And I've imported this from TRPC server. Then what I'm going to do is simply create a new subscription. So const subscription will be await database, insert into subscriptions, Values, viewer ID is going to be the user which owns this protected procedure.

And the creator ID will be the user ID from the input, meaning they chose to subscribe to this user. So they are the creator. And let's do returning here. So we can return subscription. Make sure you don't accidentally use subscription inside of here, right?

So maybe you can use beta here or created subscription. So we are explicit, there we go. So this will create the subscription. And now what we have to do is we have to create a remove method. I like to call it remove instead of delete because remove is a reserved keyword.

So the same thing, user ID. Same thing we can check here, right? So you shouldn't be able to do anything to yourself really. So we can always break that. And then this will be the deleted subscription.

So I'm okay with using deleted in this part because it's followed by subscription. So it will not override the reserved keyword deleted. Instead of insert, it will be delete. And we're just gonna do where. And let's add two equals here.

The first one will be if subscriptions viewer ID is context user ID and subscriptions creator ID is the user ID from the input. So the exact same logic that we developed here, we are now using it for querying. There we go. So that's our API for creating subscriptions. Let's go ahead and add it to our routers here.

So subscriptions. Let's import the subscriptions router, this. And now let's go ahead and use the subscriptions before we can build the UI parts we need information on whether we are subscribed to the author of this video or not. Which means we have to revisit our modules, videos, and we need to revisit this one, the getOne method from here. So we're going to create another common table expression here, similar to viewer reactions.

We're going to do const viewer subscriptions. So let's go ahead and create this temporary table, viewer subscriptions as database, select from subscriptions from database schema. So make sure you have added subscriptions from the schema, where in array subscriptions viewer ID, we're going to check if the currently logged in user is anywhere in that query. So this is our little hack to handle both unauthorized users and authorized users. So basically for the user that is loading this video, we are trying to find their user ID inside of a viewer ID.

And we're going to query it even further by adding it here inside of .with, that's important. And then we are going to go ahead and do a join on them the same way we did with reactions. So let's go ahead and multiply the left join and let's add the viewer subscriptions like this. So again viewer subscriptions instead of video ID here This will be querying by the creator ID and making sure that they are users.id and we can only query by users ID thanks to the inner join that we do first. So it's crucial that this is an inner join because it will depend on another join that we do here below.

And this user will always be the author of the video because that's the query here. So now we should be able to go back inside of our select here and we should have the exact information if the viewer is subscribed or not. And I wanna put that in the user section because here is where we hold all the information about the user. So what I'm going to do is I'm going to pass in is the viewer subscribed? And this is what we are going to do here.

The way I found we can do this is by using is not null from drizzle ORM. And then passing viewer subscriptions dot viewer ID. So if we found a single record, that would mean that the viewer is subscribed. But I think that the problem is that the type of this is unknown. So what we can do is we can add map width and pass in the Boolean inside.

And I think that now it will give a proper Boolean back. So this is a bit of an odd query. If you want to, if you know SQL, you can also use the SQL operator from drizzle ORM. And you can just do the aggregate here. If you know SQL, you can go ahead and type in, you know, where blah, blah, blah.

But for this tutorial, I will do my best to utilize Drizzle ORM builder to the max, simply because I feel like we are showcasing everything it can do. There will be one place where I really did not find any other way than just using the SQL operator, but for everything else I was so impressed that Drizzle always has a helper that can help us do something. Besides checking if the viewer who is loading this video is subscribed to the author, we also have to add the subscriber count, which is quite easy. We already know how to do it. Database count subscriptions and then subscriptions creator ID equals users ID so we are basically looking at all subscription records where the creator ID is the author of this video.

So now let's go ahead and check out when I hover over here, instead of user, I have the subscriber count and I have viewer subscribed, which is a Boolean. Great, So let's go ahead and try this. I'm gonna go ahead and go inside of my app, inside of home. Actually, yeah, let me go ahead and go inside of procedures here. Just to check one more thing.

So I just want to show you where I found this solution and give you some reassurance as to whether you can do this or not. So this is reported as a bug, right? The bug is that conditional helper functions, such as isNull, isNotNull, exists and not exist, cannot be used within a query select without returning an unknown type. So isNotNull, this ends up being an unknown type. But if you use the SQL operator directly and assign it a Boolean, then it's going to be a Boolean type.

So we would expect this to automatically give us a Boolean type, given that it is a custom helper function. This is the expected behavior, which makes sense, and they have marked it as an improvement. And here in the bottom I found this very useful comment which gives us a couple of solutions. One is to use the SQL Boolean and the other one is is not null and then map with Boolean which is exactly what we did. So if for whatever reason this stops working by the time you're watching this video, try this solution.

It should be the exact same thing. Or you can also try this solution here. SQL boolean and this table.name would be viewer subscriptions.viewerid is not now written in SQL. So just want to give you that tip in case this solution ends up being, you know, deprecated. But given that they have written right here that they will mark it as an improvement, I will assume that this is the correct usage.

It's just a bug on the types. Great! So we have this because you can see that primarily they give examples of using is not null inside of where. They don't really give examples of using it inside of select. So that's why I wasn't sure if we can use it this way or not, but I didn't have any problems and it looks like other people are doing it as well.

So I will do it like that. Excellent. So now we have everything we need from existing video and we can go back inside of our top row here, inside of our video owner, which now has the user with all of that new information. And now we can go ahead and pass in a user subscription count here for subscribers. And now it still says zero, but this time we actually load that as zero.

So our job now is to implement the methods here, which will allow us to... Which will allow us to... What's the name? To subscribe and unsubscribe. All right.

So This is how I want to do that. I want to do that by creating a common hook called use subscription. And the reason I want to do a common hook is because we will be able to subscribe the user from many different places. So that's why I want to encapsulate the logic so it's easier for us. So let's go inside of subscriptions here, inside of the subscriptions module, and let's create the hooks.

And inside create use subscription.ts like this. Let's go ahead and import toast from Sonar, useClerk from ClerkNext.js and trpc from client. Let's create an interface useSubscriptionProps to get the user ID, isSubscribed which is a required boolean and an optional from video ID. This is because not always are we going to subscribe from this page. We will also have the user page where there will be a subscribe button and if you continue developing maybe some other pages as well.

And this will be useful for us so we know should we re-invalidate this entire video. Because after you hit subscribe to this user, it will be cool if the number of subscribers immediately increased. And you can only do that if you re-invalidate our video procedure get one, right? You need to re-invalidate this one because we do a very complex query here and a very optimized query where we load the subscriptions all at once. So the subscriber count would need to be refetched when we click on subscribe.

Whereas when we are on the user page, we would not be doing that. But we need a way to distinguish. Are we coming from a video or not. If we are, we need an optional video ID. So I named it from video ID rather than just video ID, just to make it a bit more descriptive as to what it should serve as.

So let's export const use subscription here. Let's assign use subscription props. Let me add it here. Let's go ahead and structure all the props. So userId is subscribed and romVideoId.

Let's go ahead and let's set clerk and utils here. Now let's go ahead and let's build our subscribe method. It will be trpc subscriptions create use mutation like this and below this we are going to have unsubscribe which will call subscriptions remove like this And we can then create a common is pending, which will check if subscribe is pending, or if unsubscribe is pending. So if either of those are pending, we're just going to store it in one unified is pending here. And let's now create an onClick method, which will check if is subscribed, do unsubscribe, mutate user ID, else do subscribe, mutate user ID.

So basically we're encapsulating this logic once, so we don't have to write it anywhere once again, because you can see that it's gonna be a bit longer than the other ones. And finally, our hooks will return isPending and onClick. Great. And now let's go ahead and just improve the success and the failure of this hooks. So on success, what we are going to do, if we successfully subscribe, first, let's go ahead and show that with a toast saying subscribe.

And then let's go ahead and do, if from video ID, utils, videos, get one, invalidate. So we can immediately refresh the number of subscribers. But for that, we need the video ID from where we came. And in here, I'm gonna add to do, re-invalidate, subscriptions, get many, and users, get one. This is something that we don't have yet, but later on we will, so we will have to re-invalidate that as well.

Let's add on error here, which will do postError, Something went wrong. If error data code is unauthorized, let's of course get the error. Unauthorized, like this and let me just see unauthorized there we go make sure you don't misspell it let's go ahead and do clerk open sign in, like this. Great. So now I'm going to copy these two methods and add it to the unsubscribe like this.

And I will just slightly modify this. Actually, I don't think you need to modify because everything will be exactly the same, right? We still need to re-invalidate getMany and getOne and videos getOne. So yeah, I think this is it. We now have used subscription.

So we can now go back to our subscription button here. Let's see, actually let's not do subscription button, let's do video owner component where we render the subscription button conditionally here. So what we are going to do is we are going to get use use subscription here. Make sure you have imported it from module subscriptions hooks use subscription and pass in the user ID which we are trying to subscribe to to be user.id from our props. IsSubscribed will be user viewer subscribed and from video ID will be our video ID.

So we have all the info needed and then we have our isPending and onClick here. There we go And now we can go to the subscription button and we can go ahead and pass in on click. We can disable the button if we are pending and is subscribed will be user viewer subscribed. The same state that we passed to the hook here. And I think that this should now work.

So if I click subscribe here, there we go. One subscribers. And if I go inside of my Drizzle Studio, if it's still running, Let's go. Looks like it is. Subscriptions.

There we go. Viewer ID, Creator ID. And I can see exactly this is John Doe, my other user, and this is the creator, my new user that I created today. Great. Perfect.

So this seems to be working just fine. Let's see if unsubscribe is working. Unsubscribe is working as well. So I should be able to go here and my records should now be completely empty for the subscriptions. Perfect!

So we've wrapped up the subscriptions and just one thing that I think we can improve here is also add is loaded here simply so we can disable this button even if it the ALF did not load yet. So for a, let's see, okay, is loaded, we need to do if not is loaded. Make sure you add is not, there we go. So now this is how it should look like. So for a brief second, you can see that when I do a hard refresh, for a brief second it's disabled.

That's basically the outloading. So we don't even want to allow the user from the UI to make a request, which is obviously going to fail. But at the same time, we don't care because if I go ahead and sign out from here and go back here, and let me just refresh here again, if I click subscribe, there we go. Something went wrong, something went wrong. All of these things trigger the model thanks to our logic, right?

And now that I think of it, it doesn't even matter in this case, right? Except, well, actually no, I would leave it here because after we load, we at least know the state, right? So I would leave it like this because the user might be logged in, then they might click very fast and then they might get this and they will just get confused as to why they got that if they're logged in. So better to leave it as disabled. That's the problem with our app being fast.