Saving new entries

Saving new entries

Set up Prisma and SQLite to persist our dynamic data.

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

$249
access all coursesone-time payment

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!