I wanna show you a little bit behind the scenes of the very platform that you're watching this video on. We do have light and dark mode videos, so if you look up in the corner, you can change the website to dark mode and the videos will actually change as well. Kind of crazy but totally awesome. There are however, some videos that don't have dark mode and that would be some of the bonus interviews at the end.

I wanna show you how I'm using generated columns and null coalescing to make that easier on me on the application side, so I don't really have to worry about that. Here's a simplified version of the table that holds all of our CloudFlare videos. All of our videos are hosted on Cloud Flares R2, which is like their version of Amazon's S3. We put all of the videos in there and then we sync it down every 30 minutes to see what videos are available. Then we have this videos table. This videos table has a bunch of information including all of like the transcripts, the links that were mentioned in the video, the title, all of that kind of stuff.

Here, I've just slimmed it down to have title, but then we have a CloudFlare light ID, which references CloudFlare videos and a CloudFlare dark ID, which references CloudFlare videos. Every video in our domain model, every video has two underlying CloudFlare videos, a light one and a dark one. Yes, that's a lot of effort, but hopefully it's worth it. Let me show you, we already have it there. Let me show you what these videos look like just as an example. This is what the videos look like. We've got the first video, which is the introduction to the course, and it has CloudFlare light ID of one and dark ID of two.

Then the interview with the founders of Xata, which is a very good interview. You should go listen to it. The interview with the founders of Xata, it only has a light mode because I'm not gonna invert actual real life video. So let's look at CloudFlare videos and we see we've got the first video in the first module, light and dark. We have just the mastering Postgres Xata interview. Here's the situation on the application side, I don't wanna have a bunch of checks in a bunch of different places. Honestly, I don't even wanna have the checks in the model itself to see if there's a dark video and if there's not, serve the light video. I just want there to always be a dark video and a light video. That's just something I want to believe. I just want to count on that and I can use the database to make that happen such that I never, ever, ever have to think about it again.

Let's use a generated column. Looking at this table, we've got select * from videos and we're gonna do alter table videos, add column, and then let's do, we need to give it a name, let's call it CloudFlare safe dark ID, big int generated always as, and we're gonna open something up here and then put stored. What we're gonna say is let's make some space. We'll do add column on a new line generated always. We'll open some parentheses and what we're gonna put in here is we are going to favor the dark ID, should it exist, otherwise we're gonna fall back to the light ID and we can use one of those null functions, which is coalesce. We're gonna coalesce and we're gonna say, "All right, by default, let's go with the dark ID, because if it exists, we should use it." But if not, let's just go with the light ID stored.

That's fine, so if we run that and then we select * back from videos, now I have this interview with Xata. Here's the light one, here's the dark one, and the introduction to the course. The light and the dark are actually different because we do in fact have a dark ID, should we change that and reselect it, the safe dark ID would update. Of course, if we save that and select it again, the safe dark ID updates again.

Here we've wrapped up a few different concepts, and that's what I love about this part of the course is these things start to build on each other. We've wrapped up the idea of coalescing a null into something else. The coalesce function returns the first non null argument, and then we put it in a generated stored column that we can use on the application side. On my application side, my relationship is pointed to CloudFlare safe dark ID. If there is a light video, I am guaranteed that there is a dark video, even if it is a copy, and that makes me feel really good.