Serverless Databases With PlanetScale and Next.js

hello everyone and welcome to another episode of learn with jason today on the show we have taylor barnett taylor how you doing i'm good it's been a very long week but uh i've survived i made it yeah do you want to give us just the the highlights of everything you have done this week hopefully i don't forget any um so monday we released a new nexus starter app that can be deployed to netlify by the one click and a few extra database setup steps uh tuesday we announced uh that planet scale is out of beta we're now ga and that we also raised a series c around wednesday uh we announced data imports which i feel is probably one of our most like technically awesome features but is probably going to be slightly overshadowed a tad but the reason why it's awesome is you can actually like import your data from any mysql database like cloud database and not only does it just like make sure that it's like kept up like so say new rights happen to the old database until you like turn and disconnected and fully switch over to playing skill planning skill brings all of those new rights in you can even read the data you can like test it out and play around with it because you know like changing database that's like a scary thing um like that sounds like downtime and all these things but like it's done in a way that it would be no downtime no data loss nothing um and so that's really cool um and then thursday you can now create a plan and scale database inside the new prisma data platform um it's one of two different database options in there and then you can instantly like query it and explore the data and different stuff at cloud.prisma.io and then today um you know here but also uh we announced archetype it's a mysql gui like a mac app it looks beautiful it's like if you've ever used a my sequel like gui um sometimes you feel like you're like in 1995 occasionally um this does not look that way it looks totally different um and it also supports branching so you can actually like use your plain scale branches in there which has historically been hard because you have to like create a different connection for every branch most of the time if you're using any other kind of like local sql gui um and so this is like the first one that supports that and it it has a awesome design which we're big fans of too so yeah so that's well yeah no big deal yeah and while you were launching all of that you were also showing up at events because you were at prisma serverless yesterday yeah um yeah i mean you you've you've been you just hired two new teammates yeah and i was on vacation last week partially too oh yeah so like that bad trip planning timing of things but yeah i imagine uh you got a maybe got a nap planned for yeah yeah definitely a nap and i'm really excited for a long thanksgiving weekend uh next week too me too i yeah we um a co-worker of mine my uh a friend and co-worker lauren just sent me a pecan pie from texas yeah and i am planning to eat the whole thing that's my thanksgiving dinner actually tues tuesday i'm gonna make a chocolate caramel pecan pie um it's super easy to make it's basically tastes like a candy bar um but that is my contribution to thanksgiving uh it like takes less than an hour so any pilot takes less than an hour sign me up um partially because you don't have to do any dough because the crust is pecans just like very finely chopped up and then it's just like melted caramel with like some cream and then a chocolate layer on the top it's basically a candy bar but uh i would like that recipe please yeah yeah yeah i need to find it i actually need to find it i need to text my mom to remind me where i store that recipe like i i know that what you i know you probably mean something different than what i just pictured but i i picture her on the phone with you feeling okay you remember that one cupboard in your kitchen like go there open it up it's in the back in a box full of recipe cards it's probably more like her like looking up in her proper recipe book and then like you know taking a picture and sending it over which i have to do every 12 months because you know i don't like store recipes like you know probably i should but i get it i i have i feel like every dev who cooks has this this back of the head ambition like i'm gonna build that recipe app i'm finally gonna do it and then i'm gonna keep my recipes in it i'm like nah i can't even be bothered to put my recipe photos into a folder like there's zero chance i'm gonna keep the app updated ironically that is actually one of the projects i think i did in college like i was in a software engineering course i think my senior year and we had to create an api and so my team came up with like a food api or something i love it and uh yeah that was that's what that's what we did it definitely uh i think i still have the repo from it that's like six years ago but um but yeah maybe i should pull that back out it was written in python flask needs we need that pecan pie recipe okay so for folks who are not familiar with your work uh baking and otherwise can you yeah do you want to give us a little bit of a background on yourself yeah yeah totally um so i've been doing developer relations about six seven years now um i was doing a little bit of a full stack development before that at like a startup um i studied computer science at university of texas and i was doing a lot of this was like the first phase of like university hackathons you know cassidy probably uh we were around the same time of growing up in these like hackathons and um you know i i think i realized like i didn't want to write code all day every day i still love writing code but i i saw it as a tool to something bigger i saw as a tool to like connect with people to like relate to help people rather than just like write web apps for you know one of the startups i worked at was like commercial real estate startup like stuff like that i was just like this is just not my my cup of tea um and so i joined a startup called keynio it's a data analytics api company that was really big a few years ago it got i left when i got aqua hired um but i was yeah i did deaf role there uh with uh someone named tim falls who was like the first devil person at sun grid so really you know right when deveral was really starting to take off and had a great team there at keane that i really got to learn how awesome like this kind of this that kind of work can be and all the different possibilities that you could do with it and i just i was sold i was like i'm not going back to software engineering i did have one point where i remember i did do a take a couple software engineering interviews because i was like debating going back um but but yeah so i stuck with it so data analytics api then i went to an api tooling company to help uh developers actually build their apis like doing the design and the documentation the testing of it um then i went to a more general like api um not management as much as like integration platform uh where i was doing more it was it was kind of start of me going into the serverless world because in a way you could write actual code on the platform and it was delivered in kind of a serverless kind of way um with a few layers on top of it to help people integrate different apis together in their data um and and then now i am at planetscale since the end of july so i've my world has been apis and data but maybe not necessarily databases so back when i was doing software engineering that was like the part that i like did not want to touch the most because it was scary you know you just touched on something that i feel like is uh especially for folks who are front-end specialized is that when you start bringing up databases it's very they can feel very intimidating because like one of the things that you hear as like the core problem like every time there's a security problem oh the database got breached or like data loss somebody accidentally deleted the entire database or something you know it's always it's it always feels like when there's a big problem that really gets a company into a lot of trouble it's it's the database like something happened to the database and it might be something around the database let somebody get to the database but that doesn't make me any less nervous about actually getting into data so yeah what uh what what caused you to make that jump what caused you to dive in with databases yeah i mean i this was around when uh planet scale just come out of beta back in may when i started looking at it i had some friends that had been working there and um it it just was so easy to get started like creating the database took just seconds when if you've ever opened up like an aws console or even on some of the better experiences like you have to make decisions about sizing of stuff and like all these different decisions and you're like well what happens if i want to change my mind like will i be able to like scale like is this going to like be a if i don't make the right decision right now is that going to cause like technical debt down the road in a year that's gonna cause like a month of my life again like i have no idea and so i was just i was always historically just like scared to touch just databases in general i wasn't scared of data i mean i was i was like working at a data unless api company that had a really nice api and it was focused on event data so it was very flexible kind of schemas and stuff kind of right in that wave of when people are starting to get into nosql and stuff um that just felt more welcoming and it felt more like i can make changes i mean you should spend time on the data mall but like i could make changes and stuff like that and so you know this idea that a database the databases have been ignored for so long like the developer experience of them you know we push them off to dvas or other database experts and it's like some kind of black magic that only they know that no one else knows that like we as software engine developers and engineers like just kind of were like having to constantly consult them or being scared when we had to do a migration or oh no we need to add a column to a table that could be scary like you know what's going to happen is that going to cause downtime like we've all seen those status pages where some service was like unavailable because between the hours of 9 00 pm and 12 because they had to do some kind of database update like it doesn't have it it started to make me think that it doesn't have to be that way and yeah the team is so smart it's like basically the the largest collection of like modern tooling around my sequel you could find at any company as that planet skill and i was like well if they can't figure it out then we're really screwed and so yeah so you know just that the strong developer experience focus was something that that really spoke to me it's been something i've cared about for years whether that been just like small projects i was doing on my own that i couldn't always like filter into the product itself like documentation or tutorials and things like that um getting started experiences um and i felt like i could actually like have a lot of impact and and make developers lives better in this thing that is not great for many of them right now definitely feels to me like there is a bit of a renaissance in the database space i i feel like there have always been database players and um as we've seen the the jam stack approach to websites take off where front-ends are decoupled and everything is kind of built of multiple composable apis databases are like okay well how can we be part of that and we started seeing things pop up so we've seen tools uh you know like made a whole bunch of of effort to make themselves more like we can be serverless friendly and uh you know fauna db came up and cockroach db and uh they're they're all doing a lot of work to be like this this kind of of accessible thing um and so planet scale is is i would say part of that wave and i have found planet scale to be fascinating because you all are doing some stuff that i'm not seeing in other areas of the space one of the things that i find most interesting is that you've you've introduced the concept of branching to databases and yeah i don't know if you're the only ones who do it you're the only ones i've heard of and so can you talk a little bit about what that is because i feel like this is your killer feature yeah yeah so branching you know many of us are familiar with branching because of get you know in github like that really brought in that what is the get flow and like how do we work and have our code in these kind of separate environments in some ways the thing was it just never came over fully to the full stack it never came over to the back end um you know our front end can be branched but why why not the data and the database you know there's there's some people have done it you know internally some tooling exists out there uh but not like an actual like commercial off like someone that's like a legit product like building it um and part of it is because every branch is a database when you think about it it's like isolated it's a fully isolated environment right um and then because of that like you have to handle like how do we merge how do we handle all of these more difficult things um and so one part of that is possible because plan skills but on built on top of this open source project called the tess that was started at youtube many years ago it's it's a cncf open source project it's the first graduated i think database project out of cncf like it's it's legit like slack stores all of their data in the tests for example when we're talking about scale like github's using vitas um uh youtube moved on because they changed some things but like you know it was created to help youtube scale and like youtube had some ridiculous scale like we it's hard to now we just like assume it's like this big thing but it went from like zero to 100 really really quickly back like what 10 more years ago now i i definitely remember a time before youtube it's hard to imagine a world without youtube now but i i do remember yeah like the moment when it started being one of the competing services that you would watch videos on um but yeah it could have been youtube or maybe it was going to be ebomb's world who knew like you know and all that was my sequel like i mean some of the biggest sites on the planet are all all the data is on my sequel um and so so back to the branching thing so like the test is the the underlying thing that that makes a lot of plain scale possible the branching piece you know so having to have you know your own isolated environment not just for production and dev but maybe you you know you're branching off for p you know separate prs and all different things like being able to do that and actually test out the schema alongside your code and you know having a test database and then be able to merge that back in and the other cool thing about the merging thing is like especially for larger databases that could be a pretty tricky thing to do like emerging changing schema while the app is still running and not having any downtime and so that's where we talked about like the non-blocking schema change so we're not blocking the app from you know uh connections and like doing queries and all that stuff uh we do it in a very safe way where it can still be running and all that going on while that merge is happening so you can you can see sometimes those merge requests like take different amount of times um for a really large database you know for something that has very little data it's a few seconds most of the time which is pretty awesome yeah that's really really cool yeah so uh so this is like branching i think is one of the the really really cool features one of the other things that's cool about uh about planetscale is that it's it calls itself a serverless database and i think that there are um there are a couple entries in this space but but i feel like planetscale is doing something pretty interesting here so i guess the the first question would be like if i use an api if i go hit my headless cms it kind of doesn't matter if i hit it in a server or a serverless function or you know from the browser so why why does one need a serverless database versus a regular database yeah yeah i'll first say like the term server list five years ago i was kind of like giggled at it a little bit um coming from a back end kind more focused engineering uh background you know it's just you know there's so many servers it's just that you're not managing the infrastructure that they're running on so there's that piece to it the other piece is you know in a serverless runtime environments with like serverless functions becoming more and more popular you know many different offerings now of them um every function is going to create a like in a traditional database is going to create a connection to the database you're very quickly going to max out connections right you know some of these other databases that have traditionally been used uh we're talking a few hundred can max out and like what if you had thousands of functions running at once you're gonna start dropping connections requests are gonna fail like you're gonna have run into these issues this is like when uh you know back in the day when everybody was running php right everybody had the it was the lamp stack you had a mysql database and a php server and then you build a website on top of that and every time someone came to your website it would have to hit the server and the server would have to open a mysql connection and then it would have to get some data make that data into like a template assembly call and then return some markup and so you would see like you would max out your connection pool and you would get weird errors if a website was really busy like somebody popular would talk about a website oprah would mention it on her show and then people would try to go to it and they would get like a database error or they would get a server error right and it was it was because these tools are built for a finite number of connections so what so what you're talking about here is basically by going serverless we're exacerbating that problem because each serverless function like they don't share that connection yeah totally okay and and uh the test is what is actually able to handle all these kinds of um high number of connections underneath the hood so again like it's what really makes plans feel special um but one of the things is um historically i think where things were kind of going is like oh maybe we can't use my sql databases in this like traditional way anymore because of these connection issues plan skill takes a different look we're like no you still can use a mysql compatible database you just need a little different infrastructure on what it's running on top of and so that's why we've we've leaned really hard into my sequel because that is what uh is scale so well with the tests like the test wouldn't easily support other um and the test is i mean when you think about the work that was initially done through youtube and has been done by many other companies now we are standing on the shoulders of lots of open source work that's been done um we have a team at planetscale that are made that also help maintain the tests too um it's kind of a separate team in some ways like they are just they're working open source basically um which is pretty awesome that we we can have that but uh they're also the experts that help teach the rest of us and planet scale how to like build products on top of a test and like actually utilize some of the features that it has um for example that data imports feature that i mentioned that released on wednesday um that would have not been possible without some of the features of a test that handle like replication of like tablets and stuff of very complex database things but um yeah and so yeah the connections piece so a serverless database needs to be able to handle high number of connections um high availability um needs to be able to scale without you know you having to go into a console and tell it to scale it like just needs to do it right um you know we've if anybody you know remembers like when we were first building on like heroku having to like spin up dinos and the dinos like spin down kind of thing like it can't you can't have that it just your database needs to be available like right now right um and so so serverless database there's just different pieces about it that that is a little bit different it's it's not necessarily a revolutionary idea like serverless ideas have been around for a while but it's a piecing together technologies that have taken a few years to kind of come together nice well that's very cool so um i mean this is uh i could i could i have so many questions but i think it might be easier at this point now that we've got a kind of a groundwork laid to actually start seeing this in action so um let me switch us over to the desktop view here and we will first and foremost uh for anybody who would like to watch this show with captions they are available on the home page of learnwithjason.dev we've got white coat captioning here today uh writing down everything for us thank you very much for that and that is made possible through the support of our sponsors we've got netify fauna auth0 all kicking in to make the show more accessible to more people so thank you very much for that uh we've also got taylor on the show so make sure you head over to twitter and uh and follow taylor we're gonna be working with planet scale today so this is uh this is the website if you wanna go and check there we're gonna do more with that and then um i think we're going to work with some more tools but we'll introduce those as they come up so how about we do this if i want to get started i want to try out planet scale what is step one for me yeah sign up get started uh you might have an account already i don't have an account so i'm gonna sign in i used github to do it yeah makes it easier um and also for people when they sign in the first time we have a new like onboarding flow that actually explains the idea of branching and deploy requests and all that stuff so for someone who would be new they would see that it's awesome to go through that because these ideas are ideas we know but like piecing it together with databases sometimes like you're having to like make mental connections but yeah um so okay so we've got your plans for account um let's also make sure that we have the planet scale cli it's called p scale um if you go to plan scale backslash cli um we have a whole page download the latest i think we'll give you the snippet that you need to brew install it all right so let's get that going in the background and just keep fingers crossed that i i switched computers and uh and that's why i don't have the cli and so we're just gonna find out whether or not this computer is going to mute me on me yeah did it work i think no oh ended a little early yeah oh dear you're cli you clt does not oh geez what have i done what what what that's a new that's a new one for me is that something from home peru uh what did what are you are you don't oh no when's the last time you homebrew installed something i have no idea uh computer it is a mutiny i mean so one thing i will say we can still try to fix this about but um everything that you can do in the cli you can do in the ui so that's one of the things that's like big for us is that you should be able to be all cli all ui or like some combination of both the cli though is helpful for those local connections so you don't have to we're gonna need it for the local connections right yeah um i can't do that without that yeah it would be it would be a little bit harder we we well we could put the actual connection string with the password and everything in there um but then um just you can still do it technically but okay let's let's just try this and see what happens okay um you hackers you you dirty hackers okay did i type my password right i did oh boy here we go this is is this the one that's like a billion a billion oh 27 hours 28 hours is gonna go great okay um oh no it should it's speeding up five minutes [Music] look everyone i should have done this beforehand lesson learned i yeah here i was thinking to myself this is going to go great it's not going to be a problem at all and as usual i was wrong so um while we are waiting for this to happen which looks like it is going a lot faster than i thought it would so um yeah i will say my favorite feature of of mac's installation tools is its completely nonsensical concepts of time hey i mean it's just setting expectations so that you know you don't get disappointed as much i know it just it makes me laugh so much that that uh usually when you start installing anything it's like this is gonna take more than a day like maybe just walk away just yeah yeah uh and yeah time to get a coffee um but so okay so let's let's look at a few things that we're gonna need to do i have one question which is i think if i remember correctly the free plan only covers one database is that right yeah that is that is a that is a thing um so let's see can i can bump you up a little bit right now if you want to not delete your one from prisma we might if you don't mind uh i have the power now everyone's gonna dm me for free databases um yeah let me i need to log in one second i'll get it all right all right here we go we're doing the doing the ola did you put a dolphin and a wolf into the into the chat is what is a dolphin wolf i want to know what i now i'm very curious what is a dolphin wolf um okay how's this how's this going 12 minutes you said four minutes it was 10 minutes five minutes ago um all right so this this is gonna be uh potentially problematic but we can get pretty far on setting things up and like set up some of our front end while we get the the tools installed to run things locally um about random remaining yeah i think i think it might be that uh this at least seems like it's somewhat it is going faster than than what it says so i'm happy about that at least um i need okay you should be able one you should be able to create more databases now okay let's see hey it's not yelling at me hooray uh okay so i have a question because i think i did this wrong yes yesterday at prisma serverless when i'm choosing a region yeah if i'm using serverless functions i should not choose the region close to me i should choose the region close to my serverless functions uh yes so uh two things one branches can be in different regions which is kind of cool so for like developers on a distributed team maybe because they're going to be using something locally they would want something close to them because they're local but where your production database lives you would want it wherever the closest region to where your serverless function is going to run so those two are you know latency is a little bit uh less with those two got it but yeah we we do have dubs who like will make a branch closest to whatever region is to them because they're the idea so you have to think you know is this going to be running locally the most or is this going to be running you know somewhere production yeah and and so in and what's interesting about this is like if you're using serverless functions through just about any abstraction service um a lot of them are going to be based in amazon's us east so i'm going to choose us east because i think that's going to make this ridiculously fast so i'm going to i'm going to create here and and what got me clued into this was um chris biscardi is doing uh rust adventures yep and wait where's rust adventures uh yeah i'm not sure those actuarial i did it wrong let's see if i can find it adventure not plural um but i just saw that chris was putting up some like execution times and it was like under 20 milliseconds to execute a rust function that also hit the database and like it's so fast that it seems broken [Laughter] yeah and we're definitely always like releasing more regions we we did regions first with i think like three and i think we're now oh i actually don't know six seven something like that um oh that's wild it takes it we've the engineering team's gotten pretty good at deploying them in different places sometimes there's regulatory things that we have to go through for certain regions over others um stuff like that but we're always if anybody has requests support is always down to here and make note of those so when we're trying to decide where we go next we can do that great okay so we're chugging along here it looks like at the the rate that this is ticking down we are actually about two minutes away so fingers crossed um although i guess this is the the countdown bar that runs before it opens the next countdown bar well i'm only installing now next i have to actually run the installed software [Laughter] so we'll see how all that goes but uh okay so i've created a database though i created a database we're calling this one lwj and thank you tony um and on this i can see that i currently have one branch and there's a bunch of information here like number of tables number of branches number of connections yeah um do you want to give us a bit of a tour of of kind of what we're looking at here yeah yeah so this is your database and like as soon as you created this like within seconds you were if you had tried it could have made like a connection which is amazing if you've ever tested creating a database and some other stuff um and so yeah your main branch um so we we give it the name main the idea is eventually that is like your production branch um that later so right now it hasn't been promoted to production production branches have a few different things about them like you can't just like drop a table on a production branch we won't allow it um you'll get like a warning or just like an error that tells you hey you can't do that which is pretty nice um things like that uh and so then you know most people when they're get first getting started out they might because they know that mains eventually going to be their production and it's empty they want to create a new branch um uh in the ui or in the cli to uh start doing some some work um and yeah and then you also have the connect um yeah let's see if p scale will run the whole it should should work oh what's is it uh yeah yeah there it is yeah okay so we've got the the latest p scale cli yeah and i am now ready to well i'm not quite ready because i have to log in right yeah if you do uh p scale off login i i think i'm horrible about remembering cool yeah and so then it just wants to make sure that that matches what's in the there just and uh if those two match you can confirm it um so that we know and this works because you're already logged in in the browser so we know we don't have to have you do a separate login that is very that's handy that that yeah you know yeah it's nice to know you have to do a bunch of manual stuff typing your password in a cli is always weird i think yeah yeah uh so yeah so so we're locked in with p scale um and yeah and so now everything that you do for the most part i guess minus the doing the fancy connect proxy thing that we'll talk about later um can also be done in the ui or in the cli um yeah okay so we have a database our database currently has no no tables no data so if we want to get to that point like we uh let's say for this one let's track um i don't know what do you think maybe some favorite songs okay do like do like a song title and a spotify link or something yeah i think um my favorite way to model data and plan skills actually using prisma okay that's like my top favorite way partially because i love writing prisma schema you know orem said their pros and their cons but prisma is a pretty special or um i hadn't used principle until like two three months ago and now i'm like sort of obsessed probably um i've used other orms and them just being so tightly i think you might have had a nikolas from prisma on in the past but uh he probably gave like the whole history of of prisma and how they avoided the orm term but now they've like gone full steam ahead with that but yeah we can we can create a data model in our prisma schema great so what i'm doing right now is i'm just setting up a repo here so we've got a repo that's all up and running and so now if anybody wants to go look you can you can follow along with this repo but uh what i can do in the meantime is i'll open this thing up and let's see if i need to change any of the settings here because like i said this is a this is a new where's my oh it was off screen that's why it was weird okay so here is our empty project and i'm ready to write some am i ready to write some prism scheme or should i like initialize this a bit um i mean we could do a lot of times i do what is it mpx prisma in it or whatever to give me like the initial i think that's the correct one first oh yeah yeah okay i think in my head i'm imagining the development environment you used during christmas serverless conference and so i'm making some bad assumptions um so we have uh just looking at this file here we just initialized an empty project so this is uh it's a default npm init that has whatever we need in it so now we can um and you're gonna need the prisma cli too npm install prisma uh let's do that and now i can do npx prisma init and that happened real fast so here is our prisma and then we've got a schema okay yeah yeah so thingy i'm gonna install this actually uh yeah that's what gives you well later uh when you're actually trying to query your database or make add data that's what you get the typescript um uh all the hints and all that stuff nicely yeah and this this syntax highlighting is nice too because just like plain plain text is so hard to parse sometimes when you're you're trying to scan a file actually in my so i gave a talk for next js conf the other week i misspelled something in my prisma schema um it still worked with it misspelled but only because that thing did not it just didn't work the referential integrity thing that i'll explain in just a second and i respect referential somebody messaged me to let me know um they actually thought it was a bug and prism and i was like nope that was just me like typing live incorrectly um but yeah so it'll give you a little bit of um help in your schema.prismo file too which is which is helpful um so a couple of things so one um we need to change the provider to my sql since we're going to be using my sql database um [Music] let me pull up so my sequel yeah okay and then um url is good and that's what it should be then we need to add a the shadow database url um and i this is gonna auto complete right now that we've installed yes i think yeah yeah it should and then we just want to update yeah that okay um i was just looking at my prisma code so i make sure i don't forget anything once i'm sorry um and then up in the oh there's one more uh what am i forgetting one second i think we need to turn on the referential uh it's like preview features right yeah so when the client is preview features yeah referential integrity um but then actually turn it on yeah then we have to do reference checker equals prism got it okay yeah so there we go and then to talk through this a little bit the the shadow database um what what is this one doing yeah yeah so so prisma migration tool kind of like tracks how there's been drifts in your schema and uh it does that with this idea of a shadow database um okay i think let me uh it's like this it's a temporary database that you don't actually need on production i know sometimes people ask like oh do i need that environment variable in production you can literally like not add it when you're deploying a nut will fire whatever um for production because we only look to the database url the only time the shadow database url is used is when you run prisma migrate and it helps it track those like diffs in in changes in your prisma schema um i recommend just going to read also the christmas shadow database page yeah i can see anthony's posting quotes in the in the chat right now it goes a lot deeper about how it works um but the the most important thing is like you need it to do prisma migrate if you're never gonna do a prisma migrate then you don't actually need it but and so generally speaking like if we if we think about it not necessarily exactly what it's doing but what it's for we prisma gives you the ability to like make a change to your schema and then you run prisma migrate and when you do that it's gonna update the client and it's gonna go update the the database upstream and and do a whole bunch of like housekeeping and accounting work to make sure that things work yeah and my from the way they describe it it kind of sounds like the shadow database url is a way to keep partial work so that you can do a thing compared to the other thing make sure that it's not different and then like when they're all done they're like okay clean up empty the database and you would never do that work in production like nobody's going to be on your website clicking a button that's like hey change the production database um so then okay so we so that's what the shadow database url is and then referential integrity yeah this is interesting because i don't quite know what this means yeah and i also find it very amusing that we have to set a prisma setting to prisma that just makes me smile i think it used to actually be called it was slightly different a few versions ago in prison now i already forgot what it was called um it might even did it have plenty of scale and name uh because we were working with the prisma team partially to like make this workflow work because initially there was some blockers to to using prisma with planet scale and we've like worked through a lot of them and are continuing to work through to make even like can we make the shadow database experience better you know things like that um so refresh referential integrity it's a big word i was misspelled referential a lot um uh has to do with this idea of having um consistency of data between relations in your database um plan scale doesn't support foreign key constraints um it's a trade-off for us being able to have things like the branching and the schema changes and all these like my non-blocking schema changes and like all these migration nice developer experience features um so what prisma actually does is when that's turned on they like um it knows not to try to make any foreign keys and it passes that job or that work off to the prisma client and then the prisma client can handle that so you could actually technically um have foreign key like relationships in your schema um which if you just used as raw schema on planet scale wouldn't work you'd you'd get some plain scale would give you some error messages but but in a prisma schema file you can and the client then will handle it so uh it's just telling the personal client like hey don't try to to do these foreign keys in the migration and the client will handle it now i've been actually wanting to dig into like the very deeper technical thing of like how the client actually handles it more but uh that's that's something i i think soon to learn yeah and and speaking for myself and and speaking for who i know a lot of the folks watching uh how much we do with databases yeah for most of us this isn't a feature that we're gonna miss because it's not something that we're using yeah i i think that they're they're certainly like somebody who's heavy into databases and has done a lot of my sequel might come in and go wait i can't use this thing that i use all the time yeah and that is a common thing i've heard um i think sometimes we actually are doing for like some of those those relationships um and don't always realize it in our prisma schema because you know it is a layer on top of the actual sequel so it's kind of hidden away that what is actually going on um but what's was really cool was like i took a prisma schema from an example of uh something else and it had those in there and i was all worried that i was gonna have to remove them and change the data model but then i just ran prisma migrate with the referential integrity stuff turned on and it just like worked and i was like okay cool like i can just move on um you know and we also have some really good docs about how to operate without foreign keys so if somebody's really like concerned about that we explain why we made the decision and kind of how to to do data modeling without it too cool okay um well let's uh we'll leave any deeper exploration on that since it's it it's something that for me as a friend and developer i i will care about it when i need to care about it this is this is generally how i approach a lot of development these days is like i'm i'm here to solve a problem if i hit something that that requires me to do further research i will uh but for now i'm going to trust the tools to know what they're doing so so we've got this configured is there anything additional we have to configure in here to get uh no no yeah uh well if we wanted yeah besides just starting to put in the model itself okay so let's let's build in a model and then the key word for that is the keyword model okay so i have a model the model and then the name and then curly braces and so we want this to be uh songs or just saw is it is a singular song oh yeah we can make a singular song so it's not confusing and then it'll need some kind of an id so i'm just going to map out fields here so it'll have some kind of an id it'll have a title and let's give it a a url anything else you want to track on these that sounds good for me okay yeah all right so then we need to do more i assume because it's mad at me yeah you need to know what the actual types are okay um so id uh you know that could be a string um so you can and i think actually somebody i didn't realize this when you save it and it will automatically format everything i noticed that did you see it lining these things up yeah i did not know that and looked like a dork probably fixing my spacing uh while i was recording for next js conference but um look if you're not if if you're live coding and you don't look like a dork are you even live coding yeah exactly i was like oh and here let me like make my lines all pretty you know um yeah so uh you know what could the id could be a a string okay and is that capitalized or lowercase capitalize yeah okay and then um and then do space and then the at id so that it knows like that's that's and then um you space again and what is we wanted to automatically create these ids for us um i in default um is a great way of doing that and then inside of there put cu id yep right there so then it's like these it's you know we're not going to have collision of ids it's not just like auto incrementing it so someone could just like go one two it's a little bit of an api security issue sometimes to just have things being in like one two three four five and i just realized something yeah because this is all like prisma's generated stuff i can hit control space here and i'm getting a list of things that are supported which is really cool and it knows that based on my sequel too because different providers are going to have different types oh that's cool if you're doing like a sequel light or something like it's going to have different types than a my sequel kind of thing yeah and like look you can do this too like oh okay i'll make it unique yeah like i love this i it's so nice to be able to do stuff like that okay so this is our this is our database we and and i just saved and it did the auto formatty lining uppy thing yeah that's the technical term for it i believe cool yeah yeah it was it was and the funny thing was like every time i wrote i would try to like space it out ahead of time but then i the next word i would type like title was like longer and i was like dang it i gotta have one more tab yeah yeah okay spacing so so this is a pretty simple database but it uh it would grow over time like maybe we want upvote features maybe we want user accounts that'll all come later for now we want our proof of concept can i store a list of my favorite songs so yes am i done like what what happens next yeah so we you know our goal is that we could one we want to get the schema up on planet scale but then we also want to like deploy i mean i guess we could i don't usually do it where i don't have like the actual application code usually first but i mean a prisma migrate would work in the situation given we do a couple of more steps that i will share but um we could go ahead and do the prism migrate just so that we can see our schema up on planet scale and everything so in order for prisma migrate to work we need um our environment variables those two environment variables um and prisma does the nice thing of going ahead and creating the environment file for us um oh do hold on i think i have a plug-in here that um that like it's called what is it called cloak i need to turn this off because we're not gonna need it today okay so then when i open up the dot n file oh is that why i was hiding the or no you are supposed to be off get out of here nope cloak can't spell i guess we're gonna uninstall it then does it usually work pretty good i'm just curious because i was looking for something recently my issue that i've run into with cloak why it's hard to turn on and off uh it's sometimes that's also just the weird one if this is gonna no the vs code extensions are weird sometimes when you yeah i'm not sure why that's why that's fighting me but um this is saved let me just quit and open it again and see if that fixes it it's not the end of the world if it if it doesn't want to yeah okay i don't see this is this is why we can't have nice things um so the way that that works is it it basically just finds whatever the the environment variable value is and it just hides it so i will get these set up and then we'll put these in here and then if i type it just it just masks all that um it's a really cool thing but the problem with it is sometimes every once in a while it flashes and when it's on video even one frame is too many so i i've just largely not used it what go go away uh not now oh i think that's what's going on is this needs to update and it's probably keeping old settings until it updates so i'll deal with that at a later date today's not the day so yeah so um for for for everyone to understand we are going to run uh a proxy to our plain scale database locally um and so we want to tell prisma where is the database um and so it is going to actually be like it's local but it's going to proxy out to like a database actually on the cloud right and so in that database url we're going to need i'm going to type it down here and then copy paste so people can see it yeah so it'll be my sequel colon are you serious yeah we're gonna do it here there we go get out did it just disappear again yeah oh my god you're the worst okay i'm doing it in a different i'm gonna do it in a dif here we go my sequel call um backslash backslash root at 127. oh 127. oh you could also do a local host too i there's just one that's just the way i i do it uh uh colon and then we need to tell it what port let's do 3309 and is this like can i just choose any available port yeah i don't i kind of like the prisma one we just kind of like started doing that and then we just we don't want to make someone decide like what necessarily like i would probably be paralyzed by decision at that point i struggled with that myself and then just the database name okay and then this is gonna be the database url what about the shadow database is it the same same thing with just a different port so i do three three ten most the time um yeah all right so let's take the first one here and i'm going to go back in and put it in my database url and then we'll take the second one and that's going to be our shadow database url okay now these don't work yet let me make sure i didn't add anything yeah good that's all gone all right um so these are set yeah but these are not yet running correct um so go over to our terminal then and let's get connected to them so that will be pscale connect do i need to create the branches first or can i just like connect oh yeah we didn't wait we didn't create the shadow branch but the main branch will be we can just use our main as like our our because it's we're going to eventually get it as the production but you could also do a third one and have a dev i mean it really so most of the time when i'm doing like that first one i just let maine and then i promote maine to be the production branch later because i'm there's nothing on there so i'm like man it doesn't hurt now if it was in a situation where you had an existing main branch that you didn't want to touch you would want to create some kind of dove branch and still have the shadow branch okay i got you i gotcha so we can we can use main since it's empty as our our first database and then we need to create a shadow branch yeah so p spell p scale branch uh is it then the database nope uh hold on one second i always forget the ordering um actually you can just uh do dash help i think and you should be able to take a look p scale branch create and then you'll have to wait let me make sure create a branch from a database it's gonna have to have the database name in there and i think it's source database then branch name okay yeah so p scale branch create ljw shadow okay so now we've got that so we've got uh we've got our main database which will be our our database url we've got a shadow database which will be our shadow url and now we just need to connect to both of those correct yeah so that's p scale connect and i i'm also going to need so i need to use like separate terminals for this right yeah so ends up having three tabs usually at that point um and is this the right command it's like p scale connect the database dash import and then and then same we said 3309 correct so that should connect okay and that connects and there's that 127 at 3309 now i'm gonna do the same thing p scale connect lw shadow port 3310 no you did it wrong i do that and then i run prisma migrate and it's like you don't have a shadow database and like yeah i do how dare you tell me like i created it hey thanks for the raid arffy live welcome everybody we're uh we're playing with databases today we're uh we're using planet scale using prisma and uh we're eventually going to get into some next.js so all right i have now um i've got two database branches we're connected locally it's set up in the environment we've got a schema prisma i can migrate yeah i think but for one second let me just say like what what is going on with those connects we're basically proxying into our plain scale database using the auth that you've logged in um through the p through the cli so then you don't have to handle like the past creating a password for doing these like really only want to do the password stuff if i'm you know actually in production so then i don't have to constantly create more passwords and like you know more passwords potentially higher risk of you know one of them leaking or something or just it makes it so much cleaner you know just like connect just like do it for me well in a couple things uh also that if i understand correctly i can't connect to like a production connection string and modify my schema like i i'm i'm pretty sure that was what i tried first and i got permissions errors if you have turned on that as your production branch like you've perm what we call promoting to production okay oh so i was just doing everything wrong i uh i definitely like did not read the docs i just started trying to write code i will say and you shouldn't really necessarily need to read the docs i will say the onboarding steps are pretty good and from there i would hope most people who are somewhat familiar with databases could run with it but um i know we actually put and that was my mistake not somewhat familiar with databases i mean i yeah i i used to run into a few i think um that error that you got if you pasted that into the docs we literally have a page to tell you what's going on right there because we know we know that that is a thing that happens um and we explain like hey we did this for your own good um okay so let's let's get our database set up so that we can actually start loading and reading some data here yeah um so i do do i just run like prisma migrate um migrate prism mpx prism migrate dev i think is the actual command i could uh yep so it's pulling in from dot end it's gonna ask you schema from prisma data source database it's using learn with json it's got our our main branch at 3309 and it's gonna probably ask for a name yep so we'll call it init because why not yeah that's what i usually do and so it created a migrations thing so we can kind of if we want to look we can go see what happened and it just created the table for us hey look at that the raw sequel that you did not just have to write yes because while i could have googled how to do this i probably would have screwed it up so uh this is this is a little bit nicer for me because this looks like you know i've written graphql i've i've defined what a rest api looks like i've written typescript types this this doesn't feel too far from that right um and i think one one other just really cool thing about the the schema file is i to me it's very portable it's like it's really leaning into this idea i call it databases as code just like how configuration is code has been really popular over the years in the infrastructure space or infrastructure as code it i can literally take this and just go like create another database with it mm-hmm like it's not nothing about that file is specific to the database that we have here it's other environment variables and you know what i like about that is that you know we're we're moving into this world where a lot of times what people want is a quick starting point and so being able to put this right into your your starter template and say yeah so you've already got this so if you have a database available and you update your environment variables just run this command we'll bootstrap your whole database and i i assume there's even a way that i could put like seed data in as well yeah here's your first 15 entries so you can see how this whole thing works together yeah prisma has a way to write like seed scripts basically cool um well one thing let's let's go back to the plan scale web app and we can actually see the schema inside our main branch too um oh neat so that will you show that that person migrate actually well we might have to you'll have to go if you scroll up um all the way top and go to branches branches and then click on that main and then the schema at the top you might have to click the refresh button because sometimes it has to pull down there we go cool also i see you making fun of me dom don't think i missed that i'm coming for you later sequel pros are actually the name of a gui of a i think of a uh sequel gui sql pro seems like the thing that they try to get you to pay an extra 40 for and all it actually is is like a little badge on the thing that it's the exact same product but it's got like the gold badge yeah it it's a sequel sequel pro has had a fork off of it called sequel ace that is much better because sequel pro i don't know if it's still being maintained and it also doesn't know if it i don't think it supports ssl connections which is uh that's the thing that is actually required for playing skills so plants will not make a connection without ssl we will only make secure database connections we like table stakes for us on security um but actually that schema just reminded me something we should go do while we're like in here um it had that migrations table yes i know you talked about it some at personal serverless comp like why do we have this kind of um it really only matters you know if we're like you know have a uh you know we're making a lot of migrations and changes for our little hacky thing it's not going to break anything if we hadn't turned that on but um yeah so then that way the prisma migration center tracked on your main branch and so that we can see what migrations have happened over time so if anything needs to be done with oh so this gives you visual like you can you can kind of see things through time and space here we don't i mean we are rendering it super like in a pretty way i mean if you look at the schema for that migrations table that's automatically created by prisma um if you go back to branches and then main and then schema [Music] this is automatically created by by prisma yeah kind of correlated a little bit and and i want to come back here when we actually make changes because this is something i thought was extremely cool when you show a comparison of of uh one game set yeah later we can make a change we can add add a column or something and yeah we can do that totally yeah okay so the prisma migrations thing i just wanted to say the prisma migrations thing it's something all a lot of orms do like being able to track migrations and so that's why we have it where you select prisma you know i think it'd be really cool in the future if we could like auto detect some of that but uh now it's you just go tell us which which one you're using or if you have a custom table name or whatever like uh laravel will have its own and then like i think type rm or like whatever you're using yeah yeah cool so our schema is now on our main branch it's in there yep all right so from that we should theoretically speaking be able to connect and write to our database so is there like a fast way for us to try that out and make sure it works yeah um i mean do we want to initialize like a next project and then just like add a page to do an api route to do that so we've got roughly 15 minutes left in the episode so what we might want to do is let's seed some data in let's let's write to the database yeah and then maybe we can stand up a next api route to read it out and that might be all we have time for yeah so uh the prisma studio we can add the data um npx christmas studio i think we'll do it cool yeah okay so we say there's our song yeah and we can add a record okay and so the first one uh chat i'm gonna put about five songs in so drop in your favorite songs i'm gonna open up spotify and look for some look for some goodins let's see let's see what's in here what have i liked lately this is gonna be a good um uh here's a great one i don't know if you've seen the video for this song yet but it is stellar it's so so so good um all right so smoking out the window probably should have included artists in here but hey we don't have time for that anymore i feel like we got trolled by nickelback dom why wait why didn't that work oh no okay oh wait no it did work it just happened yeah some okay sometimes there's a little if you don't hit yeah if you don't hit enter after you do the url sometimes it like doesn't save the url um i am going to uh so if for everybody who is not aware dom works at netlify i like how it's not even the first one this is going on his permanent record this will be reflected in your next review okay what else you got what else you got chat hit us with songs we need we need at least a couple more taylor what's your current favorite song man that's quick um man i don't even know yeah this is weird um oh i've been listening on tv on the radio it's like my hype up music uh when i'm like trying to be on the radio that's fine i'm trying to get done um but the funny thing is like i don't actually know any of the song names i just like play it on auto and it just goes just whatever the first one is when you okay so let's let's get tv on the radio and let's uh wolf like me yeah that works all right so we're gonna get this one i think that's a good amount after that yeah i think that's about all we have time for yeah a wolf like me all right and save that we've got data yep um and so we want to pull out that data create a create a route and pull out that data i think that fairly quick all right so if i want to create next app we want to use that to to get a sample or something or if you have a preference is it npm init create next app or what's the what is the right let's see i did it just like three weeks ago how last time i ran that command now i'm getting started they have npx create next yeah at latest yeah that works there we go uh this was what did we call it song api or something i don't know i'm just gonna copy this yeah copy that right oh is this gonna do it's gonna put it inside of it probably is this gonna break because it doesn't wanna oh no okay all right here's what we're gonna do we're going to um just create an app folder and that's gonna be fine and yeah so so nextgs does the the file based routing so then we can just go into the pages um [Music] that it creates api and just create a yep okay so we've got access to songs here and we want to get so like our to do here is connect to the db and then in here we want to uh load songs right so we can literally do well um yeah so connect to the dvd um i usually but we can we'll skip that but just i usually create like uh a liv uh prisma.js so that i have like one client that i'm like um pulling from every time instead of creating new clients for every single request gotcha i mean i can do that that's that's fast let's go live prisma dot js because we use javascript and then do i need to install the prismaclient uh pres when you installed the cli i can't remember if they're separate or not i think i think we need this one yeah yeah i think so for the for the client okay and then how do we get the prisma client to know about our schema how do we get the prisma client it just is when we ran prism migrate it created a client and oh we don't need to run like the don't they have like a generate command or something uh generate command ran i think it ran when you ran prismaclient oh dope i didn't realize um it would have been in the in the thing but i could be wrong i'd have to look back real quick uh let's see a little bit higher um wait uh where were we it was you you just passed you passed it so next step set the database url we did that oh no not there when you ran prisma migrates a little bit further down [Music] because it when you run prism and migrate it is creating a client based on your schema and so i understand so it did the it did the thing for us it ran generated i understand yeah oh there it is generated prismaclient there we got it yeah okay so it did create it yeah got it hopefully i didn't break it when i ran this um okay so then we can i paste it into dm's uh if you want to make it a little bit faster what the code that i use okay uh for client uh i'm on two computers here so i'm gonna have to oh okay so fast but that's fine so we'll go the prisma is going to be either global.prisma or it'll be a new prismaclient correct okay and then if process dot m dot node n is development we will set global.prisma to prisma all right and then export that okay okay so we've got that and then in here in songs we can import prisma from uh go up one more to lib prisma dot do we need the js i'm gonna leave it in and then in here if i remember correctly this is just gonna auto complete right so we can like prisma dot song dot fi find many and if i just call it that's uh that's everything right like we can just do it like this and that'll give us all of our songs yep yeah i usually put it throw it into a try catch but i mean that today we're gonna yell we're living on the edge yes so i'm gonna just pass this in as songs and i think we can try it so i'm going to network oh wait hold on oh no uh i'm on a new version of oh no no if i cli at latest this should take just a second here and then what this will let us do is run like a local serverless proxy and pull our environment variables if we needed to but this will autoload the dot end and all that stuff so that run here what do you mean it's not we literally just installed it i guess we'll just do this for now there it goes i don't know why i did that um so we don't have one of these but we should have one of these and nothing is working because no app server detected do we not have it oh oh oh because we're not in the app folder oh yeah cause yeah okay try that again so there it goes and this should work it doesn't like i think it changed pages api schema.prisma ah crap let me look we did something i think i need to move all this to the top level oh because i think it's trying to find shoot what if i okay so what if i just move this one out nope nope come on come back how do i get you to come back there we go i'll move this one out and then i'm gonna go yes so theoretically speaking this should already be fixed so i'm going to look at pages api song and it goes up yes that's fine let's do this try it again oh this is different no such it still doesn't okay let's try that one more time now that it is still doesn't find our schema hmm something let me think uh maybe oh oh no look at the this this import is weird oh yeah you see i did i must have auto completed that or something okay so there are songs hey and so we're now pulling our songs from playing a scale from planet scale using a next api route and i want to see if in five minutes we can deploy this dang thing let's uh let's try it so i'm going to do we still not have okay it's here so let's now if i log in and this will take um i'm already logged in great so let's nutlify init i'm going to create and configure a new site we'll put it on my team we're going to call this one here okay and it's going to be did we not push to this repo um is it because should be okay here um we let's let's get at everything and make sure we didn't just double commit anything and this should all be good i think we're gonna i think what we're gonna get into trouble with here is we've got this like double package on i think i need to to refactor this so maybe we're not going to be able to to deploy it because what i need to do or maybe we can i don't know let's see if i take these dependencies out and i move them over into this package.json and then i take these dev dependencies and i move them into this package json and then i can delete the other one delete this i can move and delete this right and then um oh you know what i needed though was the the command to actually i was about to say yeah does it have the next the next related so we can just run uh it's just next build right yeah is that right yes npm install uh i so eco i could have used a base url but here's the problem because it wouldn't have it would have moved into the app and then not done any of the prisma stuff and we need that prisma client to exist and all those things so um so let's npm run build just make sure that does what we want couldn't find a pages directory because we need to set the okay i'm just dragging all of this to the tone like we're just gonna let this be um we're gonna move all of this up to the i don't need the i don't need that part so let's move this to the top level we're going to move it all get ignore probably don't need to worry about that too much because they should be pretty similar okay so let's get at oh wait no no we do need to echo dot end into our get ignore and then that should be gone so uh let's also echo netlify into the gitignore and i think that should be everything so let's get add everything and just check it to make sure i didn't accidentally commit anything i shouldn't migrations are supposed to go in right yes okay so i am going to uh go fix that api route import because we just changed the location so that should be just one one less layer up i believe right one yeah you

Channel: Learn With Jason

Views: 1,126

Keywords: databases on the Jamstack, Taylor Barnet, Planetscale, Netlify, DB-powered Next.js app, serverless database, Learn with Jason

Id: IUn2gdrPxeE

Length: 88min 34sec (5314 seconds)

Published: Mon Nov 22 2021