Current section: Database Schema 3 exercises

Database with Prisma and SQLite


00:00 To get us started, let's run npx prisma init, and we're gonna provide the flag url so that it knows that we wanna make a SQLite database, and we wanna add file colon .slash data.db. That .slash is gonna be relative to the Prisma schema that it's gonna create for us,

00:16 which will live inside of Prisma, the directory. And so it's telling us, yeah, don't, like we already have a .gitignore file, don't forget to add the .env. You'll notice we actually don't have the .env in our gitignore, and the reason for that is because we're in a workshop and I want to commit that.

00:35 So in your actual apps, you definitely don't want to commit that .env because it will contain secret things. But it also created a new file for us, and in addition to creating that new file for us, it also added some stuff to this env file. That's the database url.

00:54 And so Prisma will use this environment variable to know where our database is. And because we're using SQLite, the url to our database is a file. If we take a look at the other file it created, let's look at the Prisma directory. We've got our schema.prisma file right here.

01:11 This is our Prisma schema file. And of course, you can learn more about it at those docs. We have a generator. So the client generator, this is our Prisma client JS, is the thing that's responsible for turning this schema into a thing that you can use. So there are actually a number of generators,

01:29 and you can even make a custom generator if you so choose. But yeah, we're only gonna use this client generator for creating our Prisma client. Then we also have our data source. We've got SQLite as our provider because of the url that we provided. It interpreted that as a SQLite provider.

01:48 And then our url is gonna be this env database url. Now, of course, you can provide a actual database. You could say data.db if you wanted to just provide that. But it is really nice, especially in a production environment, to be able to control where that database lives based on this environment variable,

02:06 which is why that exists. So the next thing that we need to do is add some stuff to our schema. So we are going to create ourselves a user schema, or a user model in our schema. And oh my gosh, look at this. Copilot decides that that's what we want. So we're gonna look through this though

02:27 because it's not gonna be quite right for what we want. And in fact, you know what? I'm gonna help Copilot a little bit here by copying all of the instructions and then Copilot will be able to know exactly what we want and I'll kind of walk through each one of these as we go. So here's our model and we've got our ID. We want that to be a string.

02:46 I have no idea why anybody in the world would want an int, but Copilot really wants our IDs to be integers. So just watch out. If you're using AI assistants, don't let them give your IDs integers. No, no, we don't want that. And then we have at ID. This indicates that it's the primary key for our model, which is exactly correct.

03:05 And we want to default to a CUID. That is a collision resistant universal identifier or unique identifier. And there are various others. You can do a UUID and these will look slightly different. Pretty much it's sixes on what you end up using.

03:24 I like using CUIDs because I like to deploy my app to multiple regions when I have multiple instances of my app running. And I just really liked the idea of collision resistant in that way, especially when two multiple systems can be creating IDs at the same time.

03:44 Now, the other thing that I like about CUIDs is they're a little shorter and I think they look a little nicer than a UUID. But you do you, I will do CUID. All right, so then we have our email for all of our users. That's gonna be unique. We can't have two users with the same email. That would be kind of confusing for most cases actually,

04:04 but our case for sure. Username is how our users are gonna log in. So that also needs to be unique and that's gonna be a string. And also it's required. All three of these fields are required. And then we are going to have a name, but this one is gonna be optional. Users don't have to supply their name. So we add that question mark after the string. And then created at.

04:23 So created at is pretty much a field that I add to most of my records, created at and updated at. They're just kind of metadata about the data that I have in my database. I not always do all tables necessarily need to have a created at and updated at,

04:41 but I find it pretty helpful for tables, especially those that are managed by the users and users can create their own records and things. So created at is a date time defaults to right now. So you don't ever have to supply the created at. And then updated at has its own directive here called updated at.

05:00 And so Prisma will automatically keep that updated at updated for us. So anytime there's an update to this particular user, then it will get an updated at. And that is our model. So the next thing that we need to do is get our model in the database. Now we don't actually have a database yet

05:20 in our file system. We're about to get one. So let's come over here and we'll say NPX Prisma db push. And that's going to take our schema, convert it into some SQL statements and then execute some SQL statements against our database. Given that our database didn't exist yet, it created a new one. Now I've got a cool extension

05:40 that is part of the extensions that are listed in the recommended ones for the workshop. And this is going to display all of the data that is currently in my database, of which there's none except for the existence of a table. This extension doesn't allow me to make any changes to the database,

05:59 but I do have something that will. NPX Prisma Studio. And this is gonna pop open a new tab. So let's come over here. There we go. That's a spoiler alert on the dad joke, sorry. No worries. Okay, so with this, we can open up the user.

06:19 We can add a record. And in my instructions, I told you to create a record of a user with the email Cody at kcd.dev. And then the username is lowercase Cody. And then the name we're gonna provide is uppercase Cody. Now with these italicized bits, that's just saying,

06:37 these will be automatically generated for you. You're welcome. So we'll save that change. And there it is. It's right there. We can refresh and we'll find that data is still there. It generated these values for us. If I come in here and open up the data to be again, then it's showing up in this extension as well. But we can take this even further.

06:56 So I have a tool, SQLite 3 installed on my machine. There are instructions on how to get that installed on your machine that you can take a look at if you wanna do this too. It's called SQLite 3, not 2. And we're gonna point it to our database at DataDB.

07:13 And we're gonna call the .dump command. This is gonna dump out all of our data into a SQL file. So we'll send that over to data.sql. And that shows up right here. So this is everything that it would take, all the SQL commands it would take to replicate our existing SQL database

07:33 as an entirely separate file. So it's kind of interesting. We have our begin transaction and then commit. And we create this table. If it doesn't exist, we're gonna insert this user into the database, this user data. And then we're creating some unique indexes.

07:51 So this is something that is actually created for you automatically by Prisma, based on the fact that we signified that these are unique fields. And with that, we are able to, like if we were to apply this or run this same SQL against another database file, then we would actually be able to create

08:11 our entire database from scratch. This can actually be kind of helpful if you want to seed a production database with some initial data or something like that. You can do it all locally and then push it up and import it into another database or something like that. But if at any time, as you're going through the workshop, you're like, what in the world is in my database?

08:30 You can always execute that command, which we executed right here. SQLite 3 Prisma db.dump, and then point it over to SQL or data.sql. And that will give you the SQL that is generated. SQL, to be frank, not my favorite thing

08:49 to spend a lot of time doing, but it is an extremely powerful language and quite declarative, I must say. So there you go. We have an environment variable set for our database URL. We've got our schema set to accept that environment variable to know where our database is.

09:06 We have our client to generate our client, which we'll talk about soon. And then we have our model and we're able to run the Prisma Studio so that we can look at things and actually make mutations in our database. And then we've got our output data SQL just to explore things.

09:26 So that is our first look into creating a new database with Prisma.