Build an AI-Powered Fullstack Next.js App, v3

Serverless Database with PlanetScale

Scott Moss

Scott Moss

Superfilter AI
Build an AI-Powered Fullstack Next.js App, v3

Check out a free preview of the full Build an AI-Powered Fullstack Next.js App, v3 course

The "Serverless Database with PlanetScale" Lesson is part of the full, Build an AI-Powered Fullstack Next.js App, v3 course featured in this preview video. Here's what you'd learn in this lesson:

Scott creates a database in PlanetScale, a database environment compatible with serverless applications. The database is provisioned, and the pscale CLI tool is installed, which allows the local application to connect to the database.

Preview
Close

Transcript from the "Serverless Database with PlanetScale" Lesson

[00:00:00]
>> Let's keep it moving then. So database, we're going to use a few things for our database. The first thing is something called PlanetScale. Anybody ever heard of that? It's a really cool database. So PlanetScale is a SQL database. But it's actually kind of cool how it works.

[00:00:24]
Basically, what PlanetScale is, it's a serverless SQL database. Serverless just means, I feel like everybody uses this word now. Basically, what it means is that it works in serverless environments. What does that actually mean? Well, in most serverless environments, which aren't long running environments that have connections open constantly, databases don't work that well because databases are expected to maintain long connections.

[00:00:50]
But in serverless environments, it spins up and shuts down every time you talk to it. How do you maintain a database connection? So, to avoid that people have created serverless databases which for better or for worse just means an HTTP database at the end of the day. That's basically what it comes down to, either they're doing some really cool polling logic and they're caching connection strings and they're scaling that out.

[00:01:13]
And or it's just an HTTP server in front of a database. It's one of the two or both. PlanetScale uses some combination of the two. I think they use something called Vitis. Yes, they use something called Vitis, which is something to help you scale MySQL databases in the cloud.

[00:01:34]
I'm not a database expert, so I have no idea what the hell this is, but I just know this is what they use. And it's really cool, but that's not the cool feature about PlanetScale. The really cool feature about PlanetScale is that it's treated like a repo. You can make branches on your database and it handles migrations for you automatically.

[00:01:51]
It determines what changes to your schema will cause a conflict, and write those migrations for you. And yeah, it's actually probably one of the most impressive databases I've ever seen. And there's a lot of databases like this now. NeonDB is the exact same thing, but for Postgres. There's some other ones too, but yeah, these are some of the newer databases that are coming out.

[00:02:11]
We're gonna use PlanetScale because it's just the one that I'm most familiar with. I think Neon Database is also really cool too, but I haven't had enough time with it as I have with PlanetScale. And I don't wanna fumble the bag up here, so I'm gonna use PlanetScale.

[00:02:24]
Okay, and the other thing we're gonna use is something called Prisma. If you've taken any of my courses that do full stack, you know I love Prisma. Prisma is just an ORM for a database. If you don't know what an ORM is, it's an SDK for your database.

[00:02:36]
It's how you interact with a database in your code. The sweet thing about Prisma is it doesn't really care what database you use. It's pretty much compatible with any database that's came out in the last 15, 20 years plus, so it works with anything, including PlanetScale, which is just a SQL database, so it'll work for that.

[00:02:53]
So let's get started with both of these. First thing we wanna do is let's create a PlanetScale database. So I already have a bunch of stuff here, but I'll make a new one. So the first thing you probably wanna do is make an account. Obviously, make a PlanetScale account, it's free.

[00:03:09]
You don't need a credit card for any of this stuff. They give you a free database for any organization that you make. So once you make an account, you can have an org. I don't know if an account is an org, if you've got to convert that to an org, but make an org or you can have an org up here.

[00:03:28]
I think it'll walk you through, I think when you make an account, it'll just walk you through making the database. So just follow that and actually I'll just do that right now. So what I'm gonna do is I'm just going to see if I can make a new org here.

[00:03:39]
See, how do I do that? Here we go. So I'm just gonna create a new organization and I'm just gonna call this frontendmasters organization here. I'm gonna create that org. And then you will probably see something like this if you sign up. I think it'll bring you to something, a flow that looks like this, where it's just walking you through all the really cool stuff.

[00:04:06]
And it's like, you can do branching, non-blocking schema changes, deploy request. This is actually really cool. Like a pull request, you can make a deploy request. I wanna deploy these changes to the database and someone has to look at them. So it's actually quite cool. Yeah, you look at all this stuff and then eventually you get to the end and you can create your first database.

[00:04:27]
So I'm gonna wait for it, there it is. I already created my org. So you can click right here, create your first database, kind of name it whatever you want. I'm going to call it mood, since that's what the name of our app is. Region, doesn't matter. Deploy wherever you want.

[00:04:40]
We're in the Midwest right now, so nothing's gonna be, I guess High/b will be the closest, but it doesn't matter. Click Create Database. It's gonna provision a database, takes about a minute for that to happen. While that is happening, we can go ahead and get started with everything.

[00:04:59]
So, the one thing you wanna do is install the CLI. I already have it installed. It is not an NPM package, you have to install it globally. And because I don't have Windows, I'm gonna make sure I look up the instructions on how to do that so everyone here can do it.

[00:05:17]
Cuz I know in Mac you can use Homebrew for it. So for Mac, it's just brew. So just brew install planet-scale slash tap slash pscale. Optionally, you can also install the MySQL client for certain commands. For Linux, you use something called Scoop. No, I'm sorry, that's Windows. I was gonna say Linux is probably the same as Mac almost.

[00:05:48]
But for Windows, you use something called Scoop. Cool, and then there's also a manual installation here, which seems to be really cool, but don't worry about the Docker stuff. Don't do the Docker stuff, that's just too much work. That's for developing this, I'm sure. Brew usually takes a minute, so I'll give everyone a second.

[00:06:04]
Let's go check back on the provisioning of it. And then once it's provisioned, you'll see that we have, it shows this graph here. This is actually new. This looks cool, I just don't know what it's doing right now. I think it'll show, I don't know what it does, but it looks cool.

[00:06:23]
But the important thing is if you click on connect to your database, you can create a user, and a password, and stuff like that to connect your database. We don't need to do this right now because we're actually just gonna connect locally. And what it's gonna do is it's gonna proxy the MySQL database that's on our computer into this, so we don't have to really do anything.

[00:06:40]
We just got to connect to it. If you click on get connection strings, never mind, it's still gonna ask you for that. We don't have to worry about that. Don't worry about that. We actually don't need a connection string. The CLI is gonna handle everything for us. Okay, while that's working, I am, yes.

[00:07:00]
>> Would Prisma be similar to what Mongoose is for MongoDB?
>> Yeah, exactly. Prisma is exactly what Mongoose is from MongoDB. It's an ORM or I think in Mongoose, this case, it's called a ODM, an Object Document Mapper or something like that. But yeah, it's just an old term for something that's literally just describing the SDK for your database.

[00:07:26]
If you didn't have that, the only way to interact with the database would be with the database's native language. So in this case, you'd have to write raw SQL queries to interact with the database. If you don't like the idea of writing raw SQL queries, and instead like writing functions, then you would want an ORM.

[00:07:43]
Prisma is an ORM. That is exactly the same no matter what database you use. So you can use Mongo, you can use MySQL that's on PlanetScale, MySQL, that's on your computer, Postgres, that's on Neon, Postgres that's on Amazon. Any other database they support, and the SDK doesn't change, sir At all.

[00:08:01]
It's the same SDK. It's the same schema language. Everything's exactly the same. And better yet, it's type checked, so the SDK is generated for you based off of your schema. So if you and I had two different schemas, your SDK would be different than my SDK, even though we both downloaded the same one.

[00:08:19]
That's because it gets generated every single time you change your schema. It's kind of cool how it happens. So yeah,
>> With PlanetScale, do we have to have MySQL installed?
>> With PlanetScale, the way we're gonna connect, I believe you do need to have MySQL installed, but I think it does that for you, at least on Mac, I think it does.

[00:08:44]
On Windows, I have no clue if it does that for you, so you might have to have installed. Let's just run it to see if you get an error [LAUGH], and you might have to. It's doing so much magic in the background, I don't actually know. But if that's the case, we can connect directly to the hosted version and you can bypass all of that, if that's the case.

[00:09:02]
So, yes, if you wanna connect locally, I think you do? Yes, I think it does it for you. If you can't do that, then you can just connect to the hosted version. Cool, I think mine is done. It was a spinning, it just like aired it out. Okay, so now what I wanna do, if you have P scale, what you wanna do is you wanna do P scale.

[00:09:24]
Just to make sure you have it in your terminal. Then you can say, auth login. And this is gonna log you into the CLI. And what you should see on your screen is a bunch of characters like this. And if you go to your terminal, you'll see the characters here.

[00:09:40]
Just make sure they match. So and if they do match, you can go back to the browser and hit confirm, and now you're logged in Cool. So once you're logged in, for me at least, becauseI have so many organizations, I have to switch organizations. So I need to do that, need to see the command for that.

[00:10:01]
What is that? Let me see, to switch orgs for me, yeah, so it would be pscale org, let me see what the command for that is. Switch, okay, so pscale org switch and then frontendmasters. Okay, cool. So I switched to the frontendmasters org. I don't think you have to do that if you only have one org, it just signs you into that org, so you should be good.

[00:10:36]
Okay, and then the next thing you wanna do is create a new branch. So right now, you're just on the main branch of your database. And you can stay there, that's totally fine, but realistically you wanna create a def branch because we haven't even made like a schema yet.

[00:10:53]
So let's do that. So we can say pscale, and then we can say branch, and then create, and then the name of your database that you created on PlanetScale, in my case it's called mood. Remember the name of the database would be, you find it here. So here's my org, here's my database.

[00:11:16]
And then the name of the branch you wanna create. I'm just gonna call it Dev. So create a new branch called Dev, give me a link to where I can go look at it. The sweet thing about a branch is that not only kind of have its own version of a schema, it can have its own data too.

[00:11:32]
So you put your own data in there and it's completely isolated. And then you can merge these branches and promote them, and things like that. And we'll get to that when we deploy later. And the last thing we wanna do, we might as well do it now, although we don't need to have it now, but we'll do it.

[00:11:46]
We're just going to, We're going to start this. So if we say pscale connect, and then the name of your database, and then the name of your branch, and then to connect locally you do --port. And then just default to 3309, which is the default port for most of my SQL databases.

[00:12:14]
It's telling me that I need to upgrade to, I'm sorry, my branch isn't ready yet. I just made the branch, so I have to let it provision. You can actually check the status of it if you go to the dashboard, click on branches, and you can kinda see here.

[00:12:30]
So, I think it might be ready just now, so let's see. I'm gonna try to do that again, and boom. So now your database is on, it's ready to be connected to from your app. So you have to connect to it first in your terminal like this, have it running, and then your app can connect to this.

[00:12:56]
Does that make sense? So you're connecting a local instance to the hosted instance, and then we're gonna connect our app to this. The only problems with this sometimes is if you leave it, like I'll just leave this running for days. Sometimes it'll just time out and your app will just break, and you're like, why is my app broken?

[00:13:14]
Just stop this and start it again.

Learn Straight from the Experts Who Shape the Modern Web

  • In-depth Courses
  • Industry Leading Experts
  • Learning Paths
  • Live Interactive Workshops
Get Unlimited Access Now