Read from the Database

[00:00:00]
>> Brian Holt: So we have a database. We have rows in our database. Now we're able to go, if we go look, let's see our npm run dev again. If we go look here, whenever this decides to load, back to the home page. These are all just random things, right? These users aren't correct, they're random timestamps, like this is all just hard-coded data. We want to actually make it read from our database.

[00:00:40]
So let's go do that. We're going to do some selects first, so let's go to source, lib data articles. So, on an organizational note, I went back and forth so many times of like, do I just keep this like articles.ts in the db folder because it is coming from the database, or do I put it into like a libs function? And I actually went as far as to totally refactor to have everything in the db folder and I realized like, this isn't necessarily hooked to the database, like it could come from cache, it could come from something else.

[00:01:21]
Like there's a bunch of stuff that this is doing that isn't necessarily database specific, so I pulled it back out and put it into a helper. I'm always conflicted about like how to organize these things, particularly in like in a new project. I think this is the right pattern though, you can disagree with me. But that's what we're going with for now. OK. So, here in articles, this is a helper function to just pull records out of the database and show that like, and give it back to the UI.

[00:01:53]
So you can see I've already hooked this up, so like the, this data, as long as it's correctly shaped will show in the UI, right, markdown guide, workflow, all that kind of stuff. So let's go ahead and do that. We're going to import db from at slash db slash index. We're going to import articles from at db slash schema. Import eq from Drizzle ORM and we're going to import users sync from Drizzle ORM Neon.

[00:02:46]
Good so far. Let's just delete this. OK, and we're going to say const. This needs to be an async function, by the way, async. Constant response equals await db. Select and we're going to say what we want out of the database. And this again, this looks very sequel-esque. Article. There should be articles, sorry. Title. ID, articles.id. Created at, articles.created_at. Content, as you may expect, is articles.content and author is users sync, because that's where that's going to come from, name.

[00:04:02]
OK, and then we're going to say, where does it come from? It comes from articles. So, that's the kind of interesting thing here is you use like the whatever the export of the schema is as like the table name, right? So I'm not saying articles here, right, you actually use the object that comes out of what you declare the schema as, use that as like the in place of the table.

[00:04:32]
OK, and then you're going to, I want to do a left join with user sync. And we do that by saying, where equals articles.author_id is equal to user sync.id. And then I just return the response. OK, we'll fix the get article by ID here in just a second. But now if we go back over here, we've seen everything has now turned into Lorem Ipsum. And everything is offered by me because I am the only user in the database.

[00:05:12]
And this one was authored in the future, so that's pretty cool. Yeah, one thing I was realizing like, we could totally go make it so that the created and the update stamp, updated at stamps like are reasonable. Right now they're not reasonable, they're just random dates and random times. I didn't care enough for us to try and figure that out because it's unimportant for our use case here.

[00:05:46]
It's enough that they're just both valid dates. But there you go, that is 25 very interesting articles. So, yeah, that's, any questions about this? I mean, again, if you've seen SQL before, this should feel very SQL. I like that they don't try and change it, right? They just try and make it easier. This does not paginate, so that would be, that's another exercise for you to do after this is to go figure out pagination for this.

[00:06:20]
Yeah, you just do this with like limit and offset. All right, let's go fill out articles by ID. It's going to look really, really similar. In fact, it's kind of to the point where you can just mostly copy this. At this point, we're retrieving directly from the database, but we're not like caching anything of that answer on the front end. It's just pure go grab the data and just present it, right?

[00:06:49]
You got it. So you might be thinking, huh, seems like that could be a scaling problem, huh? We will fix that momentarily. But key thing here, this is a server action. So this is not happening on the front end. This is happening on the back end, there's like no worry about this like getting out or anything like that. You know, honestly, that's a big complaint I have about like Next and this kind of mushing together the server and the client.

[00:07:12]
It feels like, where am I doing good things and where am I doing bad things, right? Like sometimes it's hard to tell where something is executing. Now here, it couldn't be executing anywhere else, right? This actually wouldn't work because the database URL wouldn't make it there. For like several reasons, like one of the big ones is that Next won't ship anything down that doesn't have Next Public in it, like that, right there, right?

[00:07:37]
So those two keys will get shipped to the client. But like database URL does not have that in, so Next will never send that down to the client. There's also something that we could do here called tainting, which we're not going to, but you can tell React to never send it to the client as well by tainting the object, and then it'll never include that in a bundle. It's a little overkill in this particular case.

[00:08:07]
And in our particular case here, it wouldn't really matter because this wouldn't come with the, I mean, I guess it might, I don't know, very likely wouldn't matter because it wouldn't come with the database URL, it would just not work, right? That would be the worst thing that happens. OK, so we were fixing this. We, await. We want title, ID, created at, we need the content, which we did grab.

[00:08:51]
Author, we need the image URL, which is going to be articles.image_url. Title, ID, created at, content, author, image URL. OK, that looks good. From articles, left join blah, that's correct as well, but we need one more here. Underneath from we're going to put a where equals articles.id and we want that to be equal to this ID, right? So you're just going to put ID. Like that.

[00:09:31]
And now this is going to get one article. And we're just going to say responses.0, and if this does exist, then we're going to return response 0, otherwise we'll just return null. Right, if someone asks, and this needs to be an async function. If someone asks for an article that doesn't exist, we'll just give them back null, right? OK, and I think if we go back over here and we go to wiki one, it'll blow up.

[00:10:14]
Oh yeah, you know what I didn't fix on this, I don't think. Yeah, so in the db seed here, you might have to run your db seed again here in just a second. I was trying to give it placeholder images and I realized that it wasn't going to work well with the Next link or Next images. So come back over here to db to seed. And I have these like 404 images here. We're just going to make these null.

[00:11:03]
Which is like way less fun, but it's what otherwise it doesn't work very well. This is going to be the exact same what we did for. Can you even just leave it off? So we're going to, because we don't want to like modify our Next setup to accept images from anywhere because we're using Next image which will do like automatic image optimization for us. We're going to modify this, so that the image URL is just going to come to be default.

[00:11:40]
Well, and we'll just put it here, image URL text, default, and default value to be null, which is less exciting, but eventually will allow you to upload images, you can, you know, go crazy with that. And now, if you rerun your seed, npm run db:seed, it'll drop everything in the database, it'll recreate it, and it'll refresh here and it'll look for something like this. One more thing that's worth mentioning up here is I pinned the seed here to, it just has to be a number, so obviously I picked the best one, 1337.

[00:12:13]
This will make it so it's deterministic so that it gets the, it creates the same rows every single time. If you didn't put this in here, it would just create random rows, so it would change every single time that you ran db seed, but when you're doing this for like testing and stuff like that, the determinism is nice that it's always going to be the same things. But you can also modify how many rows it creates, you can change the seed if you want to see like if different sets of data work, that's totally up to you.

[00:12:59]
OK. So now if we go back to our wiki masters here, we have to run the app or else it doesn't work. Dev. Now, we have an article, we have content. I asked Claude to generate for me some Jack Handy style deep thoughts about web development. Some of them are pretty funny. I entered a room once and the whiteboard asked for my opinion on the architecture. I drew a smiley face and wrote microservices under it because the smiley was clearly decoupled.

[00:13:22]
The next day we replaced the smiley with the service and everything worked, but the coffee machine stopped responding. So I, yeah, I was impressed. I thought they were pretty funny. And like at the end of the day, like going back to why I put this separately in the helper, is that allows you to maintain like the database connection and the actual querying of the data to return to the user separately.

[00:14:06]
That's ultimately what I decided to do. Someone might reasonably ask if we go back to our action, not our action, but our helper, which is in lib, data articles. Is there a way that like you could like reuse these? And I would say don't, it's possible, right? But like you, like wouldn't typically try to like reuse the same SQL statement and like try and modify them in different ways.

[00:14:26]
And so I would say here, you kind of treat this the same way that you would treat like a SQL statement. Like don't, you don't try and dry your SQL, just doesn't make any sense to me. So, in which case, you're like, these are separate queries doing separate things and you want to maintain them separately. Therefore, trying to make reuse out of like these, despite the fact that like at the moment they're quite similar, that wouldn't necessarily always be the case.

[00:15:02]
So, as opposed to having dry helpers, I prefer to have wet helpers. Which is just the most uncomfortable way to phrase that, which is why I like doing it. Write everything twice, wet. I'm a wet developer. Do the user and article live in different schemas, and if yes, the sync knows how to find and sync the users? Yep, it's a good question. We have our table here. So we have one that lives in, we have three schemas now, so typically, I'm going to say most of you whenever you're working with Postgres, will use one schema, and you don't really care about other schemas.

[00:15:36]
I can emphatically tell you that because I can see the data of Neon users and most people just use the public schema. And they really only use multiple schemas, and you can just think of schemas as like sets of tables, right? Like it's a grouping of tables, and then a grouping of schemas is a database. Most people just read and write directly to the public, and they have a bunch of different tables in there.

[00:15:58]
We create Neon Auth for you, which is another schema. We keep it separate so that you know like, this is different, you shouldn't nor will we let you modify it. And it's the same thing with Drizzle. They keep their own schema separate from you so that you know, like, please don't touch this. If you touch this, it's going to break Drizzle, right? So the, I think the person over there is asking is like, articles is maintained over here, but we're getting the username out of it.

[00:16:32]
How do we do that? And it's because we have this author ID table, which has a foreign key, which means that this key here is the same as, if we go over to Neon Auth, this key right here. Right, so those two correlate to each other or like they connect to each other. So for example, like you can't delete this user until the other one has been deleted, so it actually maintains like the referential integrity there for you.

[00:17:00]
So the way that it knows that is we defined that in code here with this where statement. So you say that the where the articles.id. That's how we did this here for like querying by the ID. This one is just doing it by the join, right? So it's saying where the articles.author_id is equal to the user sync.id. That's the actual part that actually does the connection of the two things, is that where statement here.