Connect a Remix Application to PlanetScale Through Prisma
You are at a place where you have an app that will persist data through a sqlite database. Sqlite is great for getting started but as you build out your application, you’ll want a database that can scale with your project.
PlanetScale is the database that you are looking for. PlanetScale is a database as a service that powers a mysql database called Vitess. Vitess is what YouTube is ran on so safe to say it will scale to all your needs.
Because you used Prisma to set up your database, switching to PlanetScale will be easy. You will create a PlanetScale project, sign in to the CLI, follow the Prisma Quickstart guide and learn about Database workflows with PlanetScale. Who would have thought it would be so easy to migrate your database service.
Ian Jones: [0:00] Here's where things get really fun. PlanetScale is a database as a service that wraps a MySQL database called Vitess. Vitess is the database that runs YouTube. When you use PlanetScale, you're giving yourself a lot of room to grow.
[0:14] PlanetScale is focused a lot on their developer experience, which is the reason it's being used for this application. It truly can take you from side project to production. They have a free tier, so it makes it a great choice for a side project.
[0:28] Head on over to their sign-up page to get started. Now that you have an account, create your first PlanetScale database. I'll name it after, Remix Social.
[0:39] The central piece to the PlanetScale workflow is the CLI they have created. You can see the command for creating a database from the CLI here.
[0:49] If you haven't already installed the PlanetScale CLI, check out the instructions on their GitHub page, and pick the instructions that suits your operating system.
[1:00] Head over to the terminal and log in there. You can type the command pscale auth login. It will open a web browser and authenticate with your account.
[1:10] To prove that you are logged in, you can list all the databases with pscale db list. You can look at the Prisma quickstart docs to get a sense for what you need to do to wire up Prisma to PlanetScale.
[1:26] Since Prisma is an ORM, it has abstracted the details of what kind of database you are using. This makes it easy to change your code and start using a different database.
[1:38] Head over to the schema.prisma file, and you'll see just how easy it is to switch to PlanetScale.
[1:45] This project has been using SQLite up until now. It has served its purpose in getting you going. The difference here is that now you're using MySQL as a provider. Notice you have to set the preview flag of referential integrity. This is required to get things going.
[2:04] Now that you have Prisma configured, head over to your .env file and update the database URL. This URL will point Prisma to the correct PlanetScale database and port to connect to. You will use the pscale command to open up port 3309 to talk with the PlanetScale service.
[2:27] Stepping back a little, PlanetScale uses a git-like system to track changes in the database. The main branch is what your live system will use. You can branch off of main and make updates to your branch.
[2:43] When you created deploy request, you can see a diff of changes you are about to make to the main branch.
[2:50] With all that said, create a new branch called initial setup. Now that you have a new branch, use pscale connect to open a new connection to your database. This is the connection to PlanetScale that the Prisma Client will use to access the database.
[3:09] In a new terminal, run the command npx prisma db push. You've used this command before with your SQLite database. Prisma is doing the same thing now, but it is pushing your schema up to a MySQL Database in the cloud.
[3:29] You can open up Prisma Studio and see the database. Prisma Studio is pointed to the PlanetScale connection. Since you haven't created any post yet, it's an empty database. Just like before, you can add data through this UI.
[3:47] Back in your terminal, start up the development server. You can see the post that you created in Prisma Studio. You can also create a post through the application UI. There is your second post.
[4:03] Awesome. The last thing to do, is to merge the initial setup database branch into the main branch. Before you can merge any changes, you have to promote the main branch to production.
[4:22] Then, head over to branches then initial setup. Now, click Create Deploy Request. This is like creating a pull request on GitHub. You can leave comments here and interact with your team around this database change.
[4:46] You can also see the Schema Changes tab. This will show you the diff of what's changed. You've only made editions, so everything is green. Go ahead and click Deploy Changes. Now, you can delete the initial setup branch.
[5:06] Back in your terminal, you can switch to the main branch and restart the server. You will notice that the data didn't carry over between branches. This process is a nice way to test out changes to the database.
[5:27] In review, you created a PlanetScale account. You downloaded the pscale CLI tool, and you created a new PlanetScale database and connected to it.
[5:39] You updated the Prisma schema as well as the database URL. Then you ran the CLI command npx prisma db push, which you've run before. Finally, you created a deploy request and merged it and then reconnected to the main branch.