Build a Fullstack Music App with Next.js Setup Prisma & PostgreSQL on Heroku

Transcript from the "Setup Prisma & PostgreSQL on Heroku" Lesson

[00:00:00]
>> So, next is the fun part. I start doing back end stuff using some pretty cool tools, in my opinion, that I think are really awesome, and make things a lot simpler. So what we're gonna do is we need to, one, figure out how we're gonna do the database stuff, and talk about where we're gonna use it.

[00:00:18] Because the next is there's many places to use database, you can actually use it through an API like you typically do. But you can also use it directly in a component, which is kind of weird at the same time. So it depends on how you want to get that data and what you're doing.

[00:00:36] So we're gonna talk about that. We also need to talk about the schema, what is going to go into the schema and then what I typically do is I like to write like a seed script to actually see the database with some mock data. And I like to do that versus like hard coding data.

[00:00:51] Because if you just hard code data, yeah, you can go on and you can build things faster. But because you're not going over the network, to get that data you kind of miss out on like the error handling and the loading handling of making an API call. Because you're just reading from memory directly hard coded.

[00:01:08] So you're always writing a UI that's 100% guaranteed to work. And I think that's where a lot of bugs come in where you're like, we didn't show a loading spinner. Or we didn't handle that error because you forgot to do it because you marked all the data out, and then you went back to replace it with an API.

[00:01:25] But you never had an error handling or the loading state handling in there and initially because you never needed it. So I typically just like to mock the database out with C data and that way I can I can pull from the API. And that way I can get the best of both worlds.

[00:01:39] I don't need to worry about having real data in the database, but I also get the benefit of actually coming over the wire and handling all the, asynchronous things like error handling. In loading, handling and things like that. So for database, what we're going to be using is Postgres or PSQL.

[00:01:56] If you've never heard that before, it's just a version of SQL that's really popular right now. I'm by no means a database expert. So I'm not gonna be able to tell you the big differences with between Postgres and SQL, it's just that they both use SQL and Postgres has some different features on top of them, we'll be using Postgres.

[00:02:14] If you never use Postgres, that's totally fine because the ORM that we're going to use is going to abstract away the database almost completely. Like you're literally never even gonna interact with the database won't even matter what database are using because ORM actually works the same no matter what database you use.

[00:02:29] So it's a complete abstraction around the database entirely. You only really need to know Postgres enough to set it up. And we're doing that on a remote hosted platform that you don't even have to download. So really don't need to know much about Postgres at all to follow along.

[00:02:44] And we're gonna be using something called Prisma, so Prisma it's an ORM plus a app that allows you to explore your database plus autocomplete types for all your data. It kinda literally serves everything that you need for a database. It comes with great like querying functionality, searching functionality, all this stuff built into an ORM that you wish you had.

[00:03:14] And then it also generates a client that you can use anywhere in your app that has types generated based off your schema. So you get autocomplete for all your data. Which is really cool. And then it also handles migrations on the database. So you don't have to write those sequel migration files, which can be, if you've ever had to write those before it's not fun.

[00:03:34] It's so much easier with, Prisma they'll even detect if you need a migration or not, and write it for you. He was pretty amazing so we're gonna use that and it's free it's open source and it works pretty well with next JS. They have like some paid thing I've never used it before but the open source ones pretty cool so we'll be using that.

[00:03:52] If you haven't used it before don't worry it's actually really intuitive really easy to use I know everyone says that like, they says everything. They say Graph QL is easy like no, this is actually really easy and simple to use. Yes Mark.
>> Does Prisma work for SQL and no SQL databases at the same time.

[00:04:10]
>> I do know Prisma supports both of those types of databases. They have support for Mongodb for SQL and for Prisma. We're gonna be using it in a serverless environment. Because for sells API's servers and Prisma is not always running so it's slightly different so you're really not using both at the same time anyway but on a Redis server yeah I don't know.

[00:04:32] Will we have to look into that but I don't see why you couldn't unless there's some limitation maybe that's a paid feature I don't know but good question. Okay, so let's get started with Prisma so the first thing we need to do is need to create some databases if you have Postgres locally and you know how to use Postgres.

[00:04:54] Then you can just not worry about what I'm about to show you now you can just start with Postgres database and get some URLs that way. I didn't want anyone to have to download all of that and do that because it can be pretty tedious. And then you got to like know how to use Postgres and I just didn't feel like doing all that.

[00:05:09] So what we're gonna do instead is use a hosted one, the quickest one that I found to use was Heroku. If you've never been to Heroku make an account right quick. It's free you don't need a credit card. You don't need anything to make an account. Should be good to go and then once you get there you can just make your first app.

[00:05:25] So we need to make an app even though we're not gonna be deploying to Heroku we still got to make an app to add a database to it. So once you go to Heroku you log in and you make an account you can go here says new and you can create a new app.

[00:05:40] And click create a new app give your app a name this is a globally unique name. So it has to be unique just like a domain will have to be unique so you can't just type in like some random name it has to be globally unique which is kind of weird.

[00:05:54] But, yeah, give it a name, I'm gonna call this scott-trax-dev. I guess someone already has that, probably me.
>> [LAUGH]
>> I'm gonna say Front End Masters. There we go Scott tracks front end masters. And then he's gonna create app make sure it's on the United States region.

[00:06:16] Once you do that, you can go over to resources, click on resources tab, and you can type in resources add ons. There's a search box, you can just type in PSQL. Or Postgres and it says Heroku Postgres right there with a purple elephant you can click that and you're gonna do the hobby dev free all other ones cost money.

[00:06:44] So, you don't need the ones that cost money. You just need the free one. Click submit order form. And you gonna make that and then you're gonna do the same thing again, we gonna make two of them, and I'm gonna tell you why you gonna make two of them.

[00:06:56] So you literally go back to the search box, you gonna make Postgres again and you can do the same thing. So in order for me to make this two of them. Okay, so you should have two of them here. You'll see that they both say attached as and then they'll have the environment variable name.

[00:07:16] That's not that important what you need to know that but we do need to have the databases only because of Heroku and I'll walk through that when we set up Prisma. Okay, again, if you have Postgres on your machine already running, this doesn't matter, you'll use those URLs when we need to connect.

[00:07:30] But everyone else this, I looked at all the hosting providers, this was the simplest one to set up all the other ones were just like, way too much involved. This was like click done. So simplest one. As far as Postgres goes, I think it's the one that Prisma recommends in their documentation as well to just get started.

[00:07:46] So it's pretty simple. Okay so next we're gonna do is we're going to go to our terminal make sure you're in the directory of your project which I am you're gonna type in MPX Prisma in it. And you can see when you run that, it's gonna do a couple things, it's gonna say your Prisma schema was created at prisma/schema.prisma.prisma.

[00:08:15] You can now open it in your favorite editor and then it says next steps set your database URL the .env file set the provider the data source block and schema prison to match your database Postgres MySQL SQL lite. Run prisma DB pool to turn your database schema into a prisma schema.

[00:08:33] We don't have a database schema, we just made a new one. And then Prisma generate the client. So there's a lot of stuff here. We're not gonna do all these things in this step because we don't need to. So just follow along, but everyone should have this. So let's check out what was actually happening underneath the hood.

[00:08:49] So the first thing is you'll notice that we've got this new folder called Prisma. Inside of it, there's a schema dot Prisma. If you're using VS code, there should be a plug in you can download to get the nice colors and it actually does a lot of formatting and autocomplete for you.

[00:09:05] I highly recommend downloading the Prisma extension in the marketplace if you're on VS code. It does so much. It's actually really cool. The team behind it did a really good job. Literally one of the best language tools I've seen in VS code. So, highly recommend downloading that if you're looking at addition like wow I don't know this language it's fine is literally just like filling out JSON it's not hard at all.

[00:09:26] It's not like some new crazy DSL you'll be able to do this no problem I promise you. The next thing is if you go look at the .env file you can see that we have a database URL, that we need to fill out and this is where we're going to add our database URL that we set up on Heroku.

[00:09:48] So in order to get that, what you need to do is actually go to the Heroku dashboard that we just created. So let's go back there, we'll go to the Heroku dashboard. Let's click on either one of them. It doesn't really matter which one you can click right here, the name of it.

[00:10:03] So I'm gonna click on for me and click on the second one. It's gonna say not found for a minute, then it'll load If you go to settings and you go to database credentials you click on view credentials. And then down here where it says URI you want to copy that whole thing.

[00:10:28] So we're gonna copy that whole thing there, you're gonna go back to your code and you're going to end your .env file where it says database. URL you're gonna replace that one with the one that you just copied the next thing we need to do is we got to add a another database to this.

[00:10:46] And that's because when Prisma does its migrations it does something called shadow databases where like makes another database. But on Heroku, for the free Postgres that they give you, the user that's assigned to that does not have the permissions to create a database. So the workaround is to make a another database until Prisma to use this database of the shadow database, and this database is the real database to get around the permission issue on Heroku.

[00:11:11] So that's the only drawback of using Heroku but I still think it's much simpler. To click then to like, get some Docker thing and deploy to like elastic or EC to or wherever Digital Ocean is so much easier. So what we're looking for is going to be called shadow database URL like that.

[00:11:29] And you're going to set that equal to env and then whatever env you set in your env file, so go back to the Heroku grab the other database URL. Click on settings, click on view credentials, grab the second URL. Once you copy that, go back to your code, go back to your env file.

[00:11:53] Make another env. I'm just gonna call this SHADOW_DATABASE. _URL and I'm gonna set that equal to that URL. I'm gonna copy this EMV variable name SHADOW_DATABASE_URL. We're gonna go back to the schema Prisma and paste it here. Look, they ignored .env.local, .env.development, .env test but not .env I guess no one uses .env anymore because that's some old school thing if you've got the scope DMV files now that's that's cool.

[00:12:33] Walk through the database setup again once you run Prisma net, you should have a schema file here inside the Prisma folder. And you should have an .nv file on the root what you need to do is go to Heroku make a new app on Heroku. Once you make that app you need to go to add ons and add a Postgres database twice, two Postgres database get the free one.

[00:13:04] And then for each one of those databases we're gonna add them as environment variables to this .env file. You can name these environment tables, whatever you want. I recommend just keeping the first one DATABASE_URL and then naming the second one SHADOW_DATABASE_URL. So make sure these are the two URLs that you got from Heroku.

[00:13:22]
>> And then what is the need for that shadow one?
>> The need for the shadow one is. Without looking it up exactly because there was an issue for this is Heroku is free Postgres database. The user that Heroku creates for that database does not have a permission to create another database on that Postgres instance.

[00:13:48] Prisma needs the ability to create a database on the fly when it runs a migration because it uses that for transactions. But because Heroku doesn't give you permission based off this URL here, Prisma fells. So in order to get around that Prisma allows us to have one database for where we store the data and another database for when they make those shadow databases.

[00:14:08] So that way they don't have to make a new one which Heroku will not allow. So we went ahead and made the new one for them. And then we say use this for the shadow uses for the regular. Therefore you never need the permission to make one yourself.

[00:14:20] So that's the workaround. That's only because we're using Heroku that doesn't have permissions. If you go use it locally on your computer, just make sure your user on Postgres has permissions to create a database. So admin permission or if you deploy it somewhere else, make sure that database has admin permissions.

[00:14:34] And you only need one URL. But I tried to make this as simple as possible by not having to log into a Postgres shell and try to create an admin user and do all that stuff, which is tricky if you've never done it. So just clicking two buttons on a Heroku seem to be a lot simpler.

[00:14:50] And then the relationship between Prisma and Heroku is really there's no relation Prisma doesn't even know you're talking to Heroku. Prisma just needs a database to connect to. All Prisma is asking is like hey, you wanna. Connect to a Postgres database, because we say providers Postgres, well, what's the URL to that database?

[00:15:11] Well, it's this environment variable. Okay, you have an optional shadow database URL. Yep, it's this environment variable. It just needs a database to connect to if you're running Postgres locally, these would be your local Postgres URLs and not URLs on Heroku. Or anywhere you deploy the Postgres database.

[00:15:26] So there actually is no relationship between the two is that's just where our database. It's on Heroku that's the URL that we have Prisma, just like any other ORM, like sequel lights, sequel lies, bookshelf mongoose. They all need to know the URL for the database. This is how Prisma asks for it.