Displaying Attendee Count

[00:00:00]
>> Scott Moss: Let's get in here and do some database stuff. So now we're going to make some attendees data, so what I'm gonna do here is, in utils I'll make another file, I'm gonna call it attendees.ts, I'll grab my imports. I'll talk a little bit about them since you will be using this database stuff, so, db from at db, that's the actual ORM from Drizzle that we'll be using.

[00:00:26]
Don't worry, you never use Drizzle, if you've used any ORM in your life, you'll be fine, if you've never used an ORM, it's an SDK for a SQLite database. So, it's pretty simple, it's type-checked. You'll figure it out, it's not too difficult, and you'll be falling along. These imports from the schema are the tables themselves, we can use them to reference different fields and things like that, we need them for our queries.

[00:00:51]
These imports are filters for our query, so eq stands for equal, sql is actually a function that lets you write raw SQL in our queries if we need to. And then delays the function I talked about, it's just going to delay a random time so we can simulate a delay from an actual database and things like that.

[00:01:10]
So, or an actual, we do have actual database, but from like a slower database. So we're gonna make a function called get attendees count for dashboard, so let's do that.
>> Scott Moss: Long name but at least you know what it does, right? This is async function, this is gonna take in a user ID, so I could take any user ID or I could actually just pass.

[00:01:40]
I could just call our function, get current user inside of here and actually had that at one point. But it completely changes the behavior of the whole app when I do that and I'll tell you why when you get to caching.
>> Scott Moss: So first, I'm just gonna simulate a delay here, obviously you won't do this in production
>> Scott Moss: Unless you wanna keep your job, so you had a delay.

[00:02:00]
And you're like, hey, I can speed it up, and you took away the delay. And they're like, wow, you're really good, you need to act twice as fast. But other than that, don't put that in [LAUGH] production. And then from here, we're going to, I'm just gonna copy this query.

[00:02:13]
This is not a database course, I don't wanna go on and write all this, but I will explain it for those who are curious. Basically what we are doing here is we are saying, we wanna select this field. We created a field called totalAttendees, which runs a SQL query that does a distinct, if you don't know what distinct means, it just means like dedupe, just dedupe all these attendees by their ID.

[00:02:38]
From the events table, the events table has a join on it for RSVPs. The RSVPs table has a join on it for attendees. And I just wanna find all the events who's created by ID equals this current user ID and group by events ID. So this is just gonna return something that looks like this, an object with, I'm sorry, an array with objects on it.

[00:03:04]
That's a totalAttendees with a number.
>> Scott Moss: And then from there, because my schema is not good and my querying skills aren't that good, we're gonna have to do some JavaScript to reduce that down to one number. Now, if you put this on a PR, I would approve it, I'd say, figure out how to do that with a SQL query and get that out of here, but we're not doing that right now.

[00:03:29]
So, here we go, we got the total. This is again just gonna reduce the array of objects with these numbers down to just one number. So we just have one number that says here's attendees you have. So we got that and then we can just return that total.

[00:03:52]
>> Scott Moss: Again, you can do whatever you want inside these server functions. So we have our attendees here.
>> Scott Moss: Now we want to go add that to the page that shows the attendees, and for us that's gonna be the home page, which is the children inside the layout. So, we're gonna go there, so let's go back to dashboard.

[00:04:14]
Let's go to page TSX on the root of dashboard, make sure you're on the root of dashboard. Go to that page, this is where the attendees count is gonna show, that's gonna be the bottom right. I mean, I guess I should just show, I should just go there and show you, it's gonna be the bottom right part of the dashboard, this part.

[00:04:34]
>> Scott Moss: Okay, so let's do that, so first get current user, so, user = wait getCurrentUser. There we go, and then we can get the data from wait getAttendeesCountForDashboard and we can pass in user ID. And I know for sure that line, if line six runs there is a user, because if there isn't a user, it would've redirected inside of this function.

[00:05:10]
>> Scott Moss: So now we have our data,
>> Scott Moss: And then I think I have some JSX for this, so let's go and get that.
>> Scott Moss: It's pretty simple, so, yeah, I was gonna return a big number in the middle of that cell. So nothing crazy, let's just get rid of this.

[00:05:37]
Oops.
>> Scott Moss: There we go, I'll just call this count like it is there. Okay, so now we have that you might not have anything here because you don't have anything in your database. That's totally fine, I thought of that. So in another tab, you could run the seed script that's going to put some stuff in your database.

[00:06:00]
You have to have a user in your database though, so make sure you sign up first. The C script will not work if you don't have a user in your database, so sign up with the sign up form just so a user gets created. Again, if you need to check if the user is created, you can go to the studio and you can see if the user is created.

[00:06:17]
Make sure there is a user signed up. As long as there's one user database the C script will work. So now once you have a user in your database, you can run npm. Wait, actually you have more than one user in a database, so I'm gonna [LAUGH] delete one of them right quick, cause it just picks the first one.

[00:06:32]
So give me a second while I do that, I'm a delete, I'm a delete this one. You know what? No, no, no, I'm going to delete the other one. This one already has so much on it, so I'll delete this one, and then I'm gonna delete. I'm deleting all the other stuff that already seeded in my database, you don't probably have any of this stuff, but I already did it so much.

[00:06:55]
So I'm just gonna start fresh, so we're on the same page. Okay, so all I have right now is one user in my database, this is probably where you are if you signed up. You only have it using your database and nothing else. Okay, if you are here, then you can do npm run db seed.

[00:07:14]
And that'll do this, you can see it created two attendees and some rsvps, and it also created some events for you. So if I go back to my studio, I refresh this, I'll see there's some events, there's some rsvps, there's some attendees.
>> Scott Moss: Yes.
>> Student: How do you add types to the data from the DB so that something like count.totalAttendees is not an unknown.

[00:07:43]
>> Scott Moss: That's a great question, so it is in fact already typed by default. I broke it because it can't infer this custom SQL statement that I made, but I'm pretty sure this custom SQL statement takes in, lemme see. Yes, it takes the, custom SQL statement here takes in a generic, so I guess I could just say number, and then now it's a number.

[00:08:12]
>> Scott Moss: So, but by default, it already is tight, but it just couldn't infer this string. It has no idea what the string is, it could be anything. So yeah.
>> Student: And what do you like about Drizzle over Prisma?
>> Scott Moss: It's a great question. I actually just started using Drizzle a couple weeks ago and I'm using it in production now.

[00:08:28]
And I didn't switch because I didn't like Prisma, actually I like Prisma. I switched because the database that we were using at the time, I don't remember which one it was, didn't have really good support for Prisma. So we had to switch and Drizzle was like the second best one.

[00:08:45]
I mean, that's all an opinion, but it was pretty good, so I was like, okay, I'll try it. And then once I started using it, I realized it's just so much better to have a schema in a JavaScript TypeScript file than it is in a non JavaScript TypeScript file.

[00:08:58]
Prisma is a Prisma file, which you can't do things like this, I can't make dynamic schemas. I can't have functions, it's static in nature, so that's one thing. And then with Prisma, you have to generate the client to output the TypeScript, whereas with Drizzle, it's just TypeScript, it's just a TypeScript file.

[00:09:19]
So the types are just there as soon as you hit save, you don't have to generate anything, it just knows. So, I do like that, it is a little learning curve to like use the querying language with Drizzle, but I've come to like prefer it over Prisma. Like Prisma seen, Prisma like, took a different approach of like, how do we make this really easy.

[00:09:38]
And for me, it became less powerful whereas Drizzle's like, we want it to be extremely powerful, but we still want it to be simple. We'll let you pick, so I prefer that, but also the biggest thing was migrations. Handling migrations with Prisma was cool when it came to schema migrations, but when it came to like backfilling columns from breaking changes, that was really difficult.

[00:10:00]
They had like no solution for that because the way that Prisma works, whenever you make a schema change, you gotta generate a new client. So if you have a new client and you're trying to back fill some data from a previous version of your schema, the client isn't aware of that because the types changed.

[00:10:15]
So [LAUGH] it was really hard and I don't think they really have a good solution for it. So, that was like the biggest push, was like we had a different database that didn't support it. Because it was a serverless database over HTTP and Prisma's support for that is like always like experimental usage on risk and migrations were just really tough with Prisma.

[00:10:34]
I'm sure there's a way to get around the use case that we had, but with Drizzle, the migrations are programmatic, you literally make a migration file. In TypeScript JavaScript and you run that, whereas with Prisma it's a CLI that you have to run and it was just a lot more complicated.

[00:10:49]
But I like them both, but Drizzle for now. Okay, so yeah, let's see if this actually shows something on a page. There we go, yeah, so it works.