Database as Code with PlanetScale and Prisma - Taylor Barnett - (Next.js Conf 2021)

Hi I'm Taylor Barnett and I'm here in  Austin, Texas and I want to talk to you about   this idea of practicing databases  as code with PlanetScale and Prisma   So first what is PlanetScale? PlanetScale is a MySQL compatible serverless database   platform that aims to get you using  relational databases as fast as possible   you can start PlanetScale in seconds and  it scales with you indefinitely without having   to worry about things like connection pooling  or connection management PlanetScale also allows   you to branch your database schema just like  your code you can experiment in an isolated   database branch and then merge back into your  production branch with non-blocking schema changes   It's all about the good parts of a relational  database without the harder database operational   challenges now when using PlanetScale you'll  need a way to access your database from your code   which is where Prisma comes in. Prisma is a next  generation ORM for JavaScript and TypeScript   So Prisma is a different kind of ORM so  traditionally with ORMs they provide an   object-oriented way of mapping uh when working  with relational databases by mapping tables to   their model classes in your programming language  and while Prisma has you define your models   instead in a declarative Prisma schema where  you also define things like database connections   and you generate the Prisma client we'll  also be using the Prisma client in the demo   I'll do a little bit after this but the Prisma  client is a query builder that is both type safe   and allows you to write the SQL when working with  data models you'll also need to be able to handle   migrations for example which is where Prisma  migrate comes in we'll also use it in our example   Prisma migrate automatically generates  schema based on the changes in your schema file   and it can detect things like drift and help you  resolve those problems and lastly Prisma studio   the third part of it is a lightweight interface  for your data in your database which we'll also   briefly use today and so one of the reasons  why I think Prisma fits so nicely with Next.js   is because of all the different ways we can  access our data within a Next.js application   you can do it at build time with getStaticProps  you can do it at request time with getServerSideProps   or you can even build it  into an api route which we will do today   and lastly you can even separate the back  end out and use it as a standalone server   The last thing I want to cover before we jump  into some code is why does the stack fit so nicely   with this idea of databases as code so first  when deploying our Next.js applications today   you're likely using a tool that is very git  centric like Vercel. Vercel pulls the   repository to create deploy previews for example  Core to this concept of git is branching but often   that has not been extended to other areas of our  infrastructure like databases for example which is   possible with PlanetScale. Being able to branch out  databases is crucial for this idea of databases   as code we need to be able to have these isolated  environments to experiment and alongside our code   and then when we're done we create a deploy  request similar to a pull request for our code   Now having these models as declarative schema  also makes them not only more portable but   also makes things like version control and  automation with our databases more possible   If you're familiar with configuration as code too  then you're familiar with this idea of having this   like configuration file and in our case a schema  file that you know we can declare certain things   so that we know this is like our single source  of truth. So what are we going to build today? I'm   going to start with a brand new create next  app and then we're going to add both Prisma   and then PlanetScale and then we'll make an API  route using both of them. So let's get started!  I'm going to switch over to my code editor and  then we are going to create our first database So the first thing we're going to do the obvious  create database we're going to give it the name   "star app" and we're going to select create database  one of the awesome things about PlanetScale   is that it can track your migrations table so  when you do Prisma migrate it will track that   on the main branch we're going to save that  and then we're going to go ahead and create   our two branches so the first branch  is going to be called "initial setup"   and then we're also going to  create another branch called "shadow" the shadow branch again is for when we run   prisma migrate we'll talk a  little bit about that in a minute So I want to start with the brand new  Next.js project so I'm going to do "npx create-next-app@latest" I prefer using  npm so I'm going to give it that flag   and then I want to call this star app keep things  simple that we also named our database after it and while that is building I'm going to talk  about what our next step is the next step is   that we're going to install the Prisma client we  talked about Prisma earlier and that the client   is one of three parts main parts of Prisma  so once this is done I'll cd into the project   and I'll run "npm install @prisma/client" now this doesn't totally get us set up  with Prisma that but we can see that   you know our package.json has been updated   uh we're going to do "npx prisma init" and this  is going to create a few different files for us we're going to go into that schema.prisma  file and start editing the file   so this is very kind of like config based  um feel to it we're gonna change that to   MySQL because PlanetScale is MySQL compatible  and then we're also going to add the shadow   url I'm going to add an environment variable  for that just call it shadow database url and then also we have a special thing that we need to add when we use PlanetScale with Prisma, this referential integrity variable we're going to set that to  Prisma and also so this is a preview   feature you need to also make sure that  your client also gets generated with it so since we have these environment  variables let's go ahead and update them   so we have a .env file that was created  for us by Prisma and I'm going to pull over   a string that we're going to use to connect  locally to the database in a few minutes then   we also need a shadow database url that  we will use when we run prisma migrate   and then we're gonna use it on a  different port it'll be on port 3310 and after this let's go ahead and create our  star model that we're going to use for our API so   in the schema.prisma file we're going to do  "model Star" we're just going to put a few   things in it just to get started the basics an  id which will be an int that will auto increment then "createdAt" date time that'll be when we insert it into the database and then as well as a updatedAt. Clean this up a bit now I don't want to make this model too  complicated just for this quick demo   but we'll do a name for our star which will be a  string be a varchar and we'll limit that to 255 and then also what constellation it  is in also a string the same varchar and so there you have it uh after this part right  here we have our model we have our Prisma client   and we have our database connection  all set up and looks nice and neat   now I want to connect to the database locally I'm  going to be using the pscale CLI PlanetScale CLI   I've already installed it by the way so uh you  might need to install that if you haven't already we're going to open a few different  terminals for that so this is going to use   those environment variables that we set up um it's  when we run the prisma migrate but first we need   the actual connection and so it's pscale is  handling that connection to PlanetScale for you   so you don't have to mess with the connection  string when you're developing locally and then   I'm also going to do one for the shadow database  which again is used when you do prisma migrate   and then that is uh to the corresponding port  that we set up for that which was 3310 so now we have those connected and we're ready to  do our first prisma migrate so we're gonna do   "npx prisma migrate dev" and we'll  just give it a name "init" takes usually a few seconds to run especially  on the first time when it's creating everything   but after it runs I want us to take  a quick look at the migrations folder and so you can see in this migrations  folder what it actually created in SQL   and then you can also see  this in the branch itself   that we're working in the initial setup branch  in PlanetScale too you can see that schema is on that branch which is awesome  because this you know lets us test   out potential different schemas before we  merge them into our main production branch so the next thing I want to do we didn't have   a lib folder I'm going to go ahead and create one  and then I'm going to create a prisma.js file in   here now it's important to note that PlanetScale  can handle thousands and thousands of connections   but you know since we're dealing  with the serverless application   we still want to make sure that not every single  time we're creating a new Prisma client and   so part of why we're going to create this  prisma.js is so that we can reuse the client and I just pasted the code over to save some  time but pretty straightforward it's just a   Prisma client using the library so the next thing  I want to do is go to pages and then in api you   can see there's that hello.js that was there  when you do the create-next-app we're going to   create a new stars.js we're going to do really  basic route I'm going to go ahead and copy and   paste that into there you can see we're using that  Prisma client that we just created it's only going   to respond to a GET method it's only allowed and  it's going to find all of the star records and   return that in JSON with a 200 anything else will  give us a 500, some kind of error fetching those So now that we have created that the last  thing to do is actually give some data to our   database so I'm going to use the Prisma studio  which you can do "npx prisma studio" and it is a   kind of looks like a spreadsheet or something uh   you know I'm gonna say Polaris also known as  the north star although there are multiple   north stars which is in the Ursa Minor  constellation I'm going to save that and then we're ready to start seeing some results   uh in the close out of Prisma studio  and I'm going to run "npm run dev" now when I load this up you're familiar with the  standard Next app index page we're going to go to   the "api/stars" and as you can see here's our data  that's being pulled from PlanetScale just to   prove that this is actually  in PlanetScale we will even   close this out and then I will show  you from the PlanetScale console   within the PlanetScale web app that you can  also see the data that is in your star table Now this point we feel comfortable it does what  we expect it to do it gives us returns as results   and so I want to create a deploy request to our  main production branch to merge this schema change   You know we're feeling comfortable we did our  experiment we've tested it out in the branch   and there were no conflicts  so it's able to be deployed   and now when we look at our main production branch we can view the schema that is on that branch and   here's the same schema that we had in our  initial setup that was not there before now at this point we're ready to deploy our  application to Vercel, I'm not going to go   through step by step because it's much like how  you deploy other Next.js applications Vercel   but I will include some details in the project's  repository that you can see here on the screen   hopefully this gives you a good idea of the few  steps you need to do to implement PlanetScale   and Prisma into your next Next.js application  and also the this idea of how to use branching   as part of databases as code thank you so much for  watching and let me know if you have any feedback   on PlanetScale, on this workflow like how does  it work for you how is it not working for you   what can we do to make it better I'd love to  hear and have a great rest of your day thanks bye!

Channel: Vercel

Views: 1,582

Keywords:

Id: 5JpKZfPx-1k

Length: 16min 49sec (1009 seconds)

Published: Wed Oct 27 2021