


Saving new entries
Set up Prisma and SQLite to persist our dynamic data.
19 lessons · 5:00:25
- 1Scaffolding a new app12:31
- 2Creating an entry form22:49
- 3Saving new entries20:44
- Adding pending UI15:12
- Loading entries8:22
- Displaying entries by week24:09
- Creating the edit page13:43
- Editing entries9:47
- Making the entry form reusable10:20
- Deleting entries10:25
- Adding an authenticated state23:43
- Adding a logout button6:48
- Customizing the public UI5:01
- Securing our app12:58
- Customizing the error pages13:19
- Adding error messages to the login form13:37
- Mobile redesign41:57
- Desktop redesign18:46
- Deploying our app16:14
Video resources
Transcript
Now that we have our frontend form wired up to our backend action, we're ready to persist our new entry data. And to do that, we're going to be using Prisma. Prisma is an ORM that lets us easily connect to a database. It works with many databases, but in this app we are going to be using SQLite because it's super easy to get set up and wire up with Remix.
So if we take a look, right here is where we've got our data from our form, and this is where we're going to be calling out to Prisma. So to get Prisma set up, let's come over to prisma.io, and we're going to come down to their Quickstart. And again, these instructions could change over time, so it's always good to reference this document when you're getting started. But today we can follow the guide as it is as of this recording.
This quickstart starts with creating a new TypeScript project. We actually already have a project, so we can skip some of this and go directly to this step right here:
npm install prisma --save-dev
So let's grab this, come over to our server. We'll stop our dev server here, go ahead and install Prisma. And we can see the next step is to run prisma init
, and this quickstart shows us using a data source provider of SQLite, which is exactly what we want.
So we can copy that:
npx prisma init --datasource-provider sqlite
run that, and we're going to see that this created a schema file for us at prisma/schema.prisma
.And then it says the next step is to set the DATABASE_URL
in .env
. It went ahead and did that for us already. And then it says to run db pull
to turn your database schema into a Prisma schema, and then generate
to generate the client. We don't have an existing schema, we're just starting out, so let's come back to the guide over here.
Modeling our data
The next step is to model your data in the Prisma schema. So this is where we actually open up our Prisma schema file, and we can add different models like a User
or a Post
, or in our case, we want to add an Entry
for our work journal.
So let's open this schema.prisma
file. And the first thing I want to do is actually install the Prisma extension for VSCode, because that's going to help us out with this file. So let's go ahead and pull open our extensions tab. And we're going to search for Prisma, and we're going to see this first one is official, it's verified, this prisma one. This is the one that we want. Let's go ahead and click install. And then if we close this out, come back and find our schema.prisma
file, we're going to see this has nice syntax highlighting and it gives us some auto-complete as we start to build out this model.
So the way Prisma works, if you've never used it or used another ORM, is that we define our database structure in code right here, and then we use Prisma to apply those changes to our underlying database. And so right here in the example, we can see a User
has an id
, you know normally database tables have a unique ID that's auto-assigned to them. We can see that this is the syntax for that.
And then we can add things like an email, name, and even relationships. So let's just copy this to get us started, and right here in our schema.prisma
, we can paste this in. And now let's come back to our Remix app and take a look at our form.
So our entries have a date, they have this type right here, and they have this text field. So we can come over, we'll go ahead and leave the id
. But let's start by adding a new date
field, and now we need to give this a type. We can see there's different types that Prisma supports like Int
and String
. And the cool thing about this Prisma extension is if I press Control + Space right here, we're going to see all the types that are supported for us right in this menu.
And we can see there is a DateTime
type, so we can use that for our date. Now, our UI only lets us choose a date, that's all that really matters for our app. But if we look at the auto-complete here, we see that there isn't any just Date
field, by default we just have DateTime
. That's kind of what works out of the box with SQLite and Prisma, so we'll just use this for now and ignore the time.
Next we have the type
here, and we can pop back, open our index to see what we called these things. We called this date
, we called this type
, and then for the textarea we called this text
. So let's get the type in here next, type
. And you might be used to using something like an Enum for this if you've worked with databases before. Again, we don't have Enum support out of the box, so just to keep things simple for now, we'll just use a String
, and we'll just save this as "work" or "learning" or "interesting-thing". So that'll work fine for now. And then we want some text
, and that is also going to be a String
.
Now there's a lot of other cool features that Prisma supports. You can see things like @unique
or making things optional. But for this simple form for now, we don't need any of that. So we can go ahead and save this. And we're not making users, we're actually making, entries. So we call this Entry
, this is singular right here. And this should be everything we need to model our data.
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "sqlite"
url = env("DATABASE_URL")
}
model Entry {
id Int @id @default(autoincrement())
date DateTime
type String
text String
}
Prototyping with db push
So let's come back to the Prisma guide, and after we're done modeling our data in the schema, the next step is to run a migration to create your database tables.
Now, if you've worked with ORMs before, you might be familiar with migrations. They're a way to track changes to your database over time, and they're good for more mature projects or production environments where you're collaborating, and people need to keep their local databases in sync. But Prisma actually has a nice method for prototyping new projects where the schema is still being worked out, and it's a workflow that is just better supported for kind of local development. So we're going to switch over to that right now.
If we come up here and search the documentation for prototyping, we're going to see this guide right here called Schema prototyping with db push. And this is an alternative to migrations. You can click this and read more about it. I'll put a link to this down in the lesson notes.
But for now, we can follow this guide here. We have our own Entry model, so we're done with the modeling step. And we'll see the next step is to run
npx prisma db push
So if we copy this, come back to our terminal, and run this, we're going to see that this finishes successfully. SQLite database `dev.db was created at this file. So we can see SQLite is just a little file right here, again really easy to get started, and our database is now in sync with our schema.
Now we can't come and look at this file directly because it's a binary file, but Prisma has a really nice way for us to just make sure that our schema and our database are all working correctly.
We can run
npx prisma studio
and we're going to see this opens a new tab at localhost:5555
. And look at that – we have an Entry right here. We can see all of the fields, id
, date
, type
, and text
. And we can even create data right here. We can type in whatever we want. We can save it. Or we can come and discard this.
So this is pretty awesome just to kind of query our data and see that our database is actually working. And now that it is, we can talk about how we're actually going to get the data from our form into Prisma, into our SQLite database programmatically.
Creating entries with Prisma Client
So now that Prisma is working, let's go ahead and come back to the Prisma docs, and we're going to search for a guide called Install Prisma Client. So we're going to see some guides here on the Prisma client, which again is kind of the way that our app code is going to programmatically interact with and connect to our Prisma backend, our database. And we see there's a guide here for MongoDB and then one for relational databases. SQLite is relational, that's what we're using. So let's click on this.
And the first step is to install one more package, the Prisma client. So let's come here, we'll keep the studio running. I'll start a new tab where we can install Prisma Client:
npm install @prisma/client
And we actually don't need to run prisma generate
since we started from scratch with the other guide. But now we're actually ready to query and write to our database.
So if we come to this guide, this is actually pretty awesome how easy this is. We're going to see the first line here is where we import PrismaClient
from the package we just installed, and then we instantiate it, and this will give us our database. And this guide is going to show connecting and querying, but we want to actually write data. But let me just show you how cool this is.
So we're going to copy these two lines of code:
const { PrismaClient } = require("@prisma/client");
const prisma = new PrismaClient();
Come back to our Remix app, and let's come back to our index route. And I'm going to hide the terminal and the sidebar, I'm going to paste this in just like this.
Remix is set up to use ES6 module imports, so if you see require()
, this is a node convention, but we can actually just use the import
syntax that we're already using right here.
- const { PrismaClient } = require("@prisma/client");
+ import { PrismaClient } from "@prisma/client";
And now we're going to see we get a lot of really cool type information right here. We can go ahead right here, check out the auto-complete, and we're going to see a lot of cool methods as well as this entry
property. And this is coming from our actual Entry. So again, we have created a new Entry
table in our schema, pushed it to our database, and part of what db push
does is generate types for us, so we have awesome TypeScript auto-complete in our project.
But this is pretty neat. You're going to learn a lot of things about Prisma from the documentation that is dynamically generated for you based on your actual schema, and this is a really pleasant part of working with Prisma.
So let's go ahead and rename this to db
.
import { PrismaClient } from "@prisma/client";
let db = new PrismaClient();
And basically right here when we have our data from the previous lesson, now we want to create an entry.
So if we go db.entry
, we're going to see this gives us a bunch of cool methods. We can call create
. If we look at the IntelliSense, we see this is a method. This is a little bit noisy, but again, we have these really cool auto-generated docs, "Create an Entry", and here's an example of how to do it: entry.create()
and then we pass in data:
import { PrismaClient } from "@prisma/client";
let db = new PrismaClient();
export async function action({ request }: ActionArgs) {
let formData = await request.formData();
let data = Object.fromEntries(formData);
db.entry.create({
data: {
//
},
});
}
So data
is going to be an object, and right here, look at this auto-complete. These are the fields on our Entry model, and the id
is going to be taken care of for us. So we want the date
, we want the type
, and we want the text
.
So let's just save this, and I'm just going to put in some data right here – "work", text is going to be "some text", and date, let's use 2023-03-07
:
db.entry.create({
data: {
date: "2023-03-07",
type: "work",
text: "some text",
},
});
And let's just see if we have all this wired up correctly. I'm going to come back to my terminal here, looks like we haven't restarted our dev server so let's go ahead and npm run dev
. Everything seems good so far. And let's click save.
We don't see an error on our server. If we come over to our studio and refresh, we still see zero right here. So if that worked, I'd expect to see a record in our Entry table.
Let's pop open the console on the client. And it looks like we have an error. It says
Uncaught Error: PrismaClient is unable to be run in the browser.
So it looks like we are creating this database connection to our client, and this code is actually running in the browser. And Prisma is designed to run on the server because it is connecting directly to our database, which is running in the server as well.
Remember with Remix, we're sending the data over in a normal form submission so that our action, which is running in node, can connect directly to our database.
So, let me show you how to fix this. Basically this code should only ever run in node. And so if we move this right inside of our action:
import { PrismaClient } from "@prisma/client";
- let db = new PrismaClient();
export async function action({ request }: ActionArgs) {
+ let db = new PrismaClient();
let formData = await request.formData();
let data = Object.fromEntries(formData);
db.entry.create({
data: {
date: "2023-03-07",
type: "work",
text: "some text",
},
});
}
And save that, refresh, and click save, then we don't see this error anymore.
So this is kind of an interesting point about Remix. Remix gives us these really cool APIs where we get to write this node function handler right here, right above our frontend JavaScript code. And this is fully React, we can use state, we can use effects, we can do everything we need to do in this component, but we get this really convenient way to define service-side actions that run right alongside of it.
But there's some complexity under the hood in terms of how Remix is actually bundling our client code and shipping what should be run in the browser to the browser, versus using node modules that should only ever run on the server. And so as you can see, because it kind of blurs the lines here, it's just something you have to be aware of. Usually, you would end up moving this server-only code to another file, and you can even hint to Remix on how to make sure it never ends up in the client. But, I wanted you to see that error so that if you run into it yourself, you kind of understand what's going on.
But for now, this is going to be good for us. And so let's come and see, it looks like we didn't see any errors here, and we don't see any errors here. So let's come and see if we actually were able to create an Entry by coming back to Prisma Studio. And we'll go back to the homepage and refresh. And it looks like we still don't have any entries.
So right now we're not really looking at any errors that could potentially be coming back from Prisma. So let's take a look and see what create
responds with. We see the types up here, and if we scroll down to the example, we're actually going to see that the return value from create
is a Promise that's awaited, and that will give us the entry.
So first, let's come here and await this, because this is in fact a Promise.
import { PrismaClient } from "@prisma/client";
export async function action({ request }: ActionArgs) {
let db = new PrismaClient();
let formData = await request.formData();
let data = Object.fromEntries(formData);
await db.entry.create({
data: {
date: "2023-03-07",
type: "work",
text: "some text",
},
});
}
So let's save this and give this another shot. Okay, so now we're getting some more information.
So Remix is showing us that we had an error in our action. And if we read this, it says
Argument date: Got invalid value of "2023-03-07". Provided String, expected DateTime
So this is kind of the root cause of why we're not saving our entry. But I wanted to also show you this workflow here, because this is a pretty neat use of Remix's error system. As long as we actually await
this call, or anything asynchronous in our action, Remix is going to give us a lot of help here, and as we'll find out later in the course, we can do even more with customizing errors, how they show to us as a developer during development time, and also to our users when our application's actually running. But when you create an entry and you don't await
it, the error happened asynchronously, which is why we didn't see it the first time.
So again, now that we've awaited this, we see exactly what's going on. We provided a string here, but Prisma is actually expecting a JavaScript Date to map to its own DateTime
type. So let's just actually create a new Date, just like this:
import { PrismaClient } from "@prisma/client";
export async function action({ request }: ActionArgs) {
let db = new PrismaClient();
let formData = await request.formData();
let data = Object.fromEntries(formData);
await db.entry.create({
data: {
date: new Date("2023-03-07"),
type: "work",
text: "some text",
},
});
}
This is how we create new dates in JavaScript, and it works fine with this format right here.
So let's save this, come back and refresh, and let's go ahead and click save. Okay, that looked promising. Let's come over to our studio, refresh and check this out: we've got one entry in our database, type
is "work", text
is "some text", and date
is March 7th, 2023, and we can see the timestamp is 00:00:00, which is going to be just fine for our app.
So that's pretty awesome. Let's go ahead and delete this record so we can start out with a clean database here. But that means that this code is working. Now we can go ahead and close this, and replace this with the data from our form.
import { PrismaClient } from "@prisma/client";
export async function action({ request }: ActionArgs) {
let db = new PrismaClient();
let formData = await request.formData();
let data = Object.fromEntries(formData);
await db.entry.create({
data: {
date: new Date(data.date),
type: data.type,
text: data.text,
},
});
}
And let's save this, refresh. And let's use last Wednesday, so we'll say Wednesday the 1st of March. We'll put an interesting thing in and we'll say, "Chat GPT just got a lot faster!" Save that. Let's come over, take a look at our terminal. Looks like that made it through. And if we come to Prisma Studio and refresh, interesting thing, "Chat GPT just got a lot faster!" on March 1st.
So that's pretty neat! We're getting user-submitted data from our form into our database via this action code right here. And we've got some more work to do to tidy up this form, but we'll cover that in a future lesson.
Fixing TypeScript errors
For now, let's just button up this action so we don't have any TypeScript errors before moving on.
If we hover over this error, we're going to see that data
is an object here whose values are FormDataEntryValues
. And these could be any sort of type that comes from a frontend form. We see here, this is complaining because type File
could not be assignable to string, number, or date, which is what Prisma expects for our date parameter right here.
And so this potentially could be a file object, of course we're not actually submitting that, but this is something we need to account for because again this action, like we saw earlier in the course, is an API route that's exposed to the frontend. It could be hit with any sort of data, even though in our actual app it won't. But this is why TypeScript is telling us that we have to account for this. Because for our code to not behave in unexpected ways, we need to account for all those possible values that could come into this action.
And so what we want to do is just a little bit of light validation here. Let's go ahead and destructure the date, type, and text from our form. And right now we just want these all to be strings.
import { PrismaClient } from "@prisma/client";
export async function action({ request }: ActionArgs) {
let db = new PrismaClient();
let formData = await request.formData();
let data = Object.fromEntries(formData);
if (
typeof date !== "string" ||
typeof type !== "string" ||
typeof text !== "string"
) {
throw new Error("Bad request");
}
await db.entry.create({
data: {
date: new Date(date),
type: type,
text: text,
},
});
}
So now TypeScript is going to know it's working with strings, so we see those squigglies go away. And we should be able to come here, give this a refresh, and let's just make sure this works one more time. We'll say today, "I'm working on my first Remix feature". Save this, come check out studio, and there we see our second record.
So that is kind of the bones of getting data from our frontend, to our action, all the way into our database so it's persisted, and eventually we'll be able to query the data from this database and display it on the page. But before we do that, we want to button up this form a little bit, clear these values on submit, and add some validation for a bit nicer user experience.
Links



Buy Ship an app with Remix
Buy the course
$179one-time payment
Get everything in Ship an app with Remix.
- 5+ hours of video
- 19 lessons
- Private Discord
- Summaries with code
- Unlimited access to course materials
Lifetime membership
Lifetime access to all current and future premium Build UI courses, forever.
- Courses on Framer Motion, Tailwind, and Remix
- New videos added regularly
- Refactoring videos on React
- Private Discord
- Summaries with code
- Full access to all future Build UI courses
What's included
Stream or download every video
Watch every lesson directly on Build UI, or download them to watch offline at any time.
Live code demos
Access to a live demo of each lesson that runs directly in your browser.
Private Discord
Chat with Sam, Ryan and other Build UI members about the lessons – or anything else you're working on – in our private server.
Video summaries with code snippets
Quickly reference a lesson's material with text summaries and copyable code snippets.
Source code
Each lesson comes with a GitHub repo that includes a diff of the source code.
Invoices and receipts
Get reimbursed from your employer for becoming a better coder!