DEV Community

Aaron K Saunders
Aaron K Saunders

Posted on • Originally published at clearlyinnovative.com

Working With Remix, Prisma, and SQLite To Save Data Using Forms

Overview

A simple application tutorial with Prisma Setup, creating objects, saving objects, and saving objects with relationships. We also show a Remix page with multiple Forms on one page that can support multiple actions from the form submission

Remix - Remix is a full-stack web framework that lets you focus on the user interface and work back through web fundamentals to deliver a fast, slick, and resilient user experience. People are gonna love using your stuff.

Prisma - Next-generation Node.js and TypeScript ORM

Prisma helps app developers build faster and make fewer errors with an open-source database toolkit for PostgreSQL, MySQL, SQL Server, SQLite, and MongoDB

Video

Create Remix Project

npx create-remix@latest
Enter fullscreen mode Exit fullscreen mode

Prisma Stuff

Install Prisma specific packages

npm install prisma@latest typescript ts-node @types/node --save-dev
npm install @prisma/client@latest
npx prisma init --datasource-provider sqlite
Enter fullscreen mode Exit fullscreen mode

Create the database models for the CMS

// prisma/schema.prisma

// 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")
}

// NEW STUFF !!
// ------------
model User {
  // We set an `id` variable
  // - @id (because it's an ID)
  // - @default(autoincrement()) (default value is auto-incremented)
  id Int @id @default(autoincrement())

  // - @unique (because we want the user to be unique
  // based on the email - two users can't have the same)
  email String @unique

  username String

  // With a `BookMark[]` type (one-to-many relationship)
  // Because each user can have between 0 and an infinite number of bookmarks
  bookmarks BookMark[]
}

model BookMark {
  id Int @id @default(autoincrement())

  createdAt DateTime @default(now())

  // note about the bookmark
  text String

  url String @default("")

  // It will link the `id` of the `User` model
  userId Int

  // We set a `user` variable
  // With a `User` type (many-to-one relationship)
  // Because each bookmark is associated with a user
  // 
  // We link the `User` to a `Bookmark` based on:
  // - the `userId` in the `Bookmark` model
  // - the `id` in the `User` model
  user User @relation(fields: [userId], references: [id])
}
Enter fullscreen mode Exit fullscreen mode

run command to migrate the changes into the database and create the prisma client

npx prisma migrate dev --name initialize_db
Enter fullscreen mode Exit fullscreen mode

view content in the database using Prisma Studio client

npx prisma studio
Enter fullscreen mode Exit fullscreen mode

Remix Stuff

// index.tsx

import { PrismaClient, User } from "@prisma/client";
import { Form, Link, useLoaderData, useTransition } from "remix";

export async function loader() {
  const prisma = new PrismaClient();
  const allUsers = await prisma.user.findMany();
  console.log(allUsers);
  await prisma.$disconnect();
  return allUsers;
}

export async function action({ request }) {
  const form = await request.formData();

  const prisma = new PrismaClient();
  const allUsers = await prisma.user.create({
    data: { email: form.get("email"), username: form.get("username") },
  });
  console.log(allUsers);
  await prisma.$disconnect();
  return true;
}

export default function Index() {
  const users = useLoaderData();
  const { state } = useTransition();
  const busy = state === "submitting";

  return (
    <div
      style={{
        fontFamily: "system-ui, sans-serif",
        lineHeight: "1.4",
        width: 600,
        margin: "auto",
      }}
    >
      <h2>Users and Bookmarks: Sample App Tutorial</h2>
      <h4>Remix with Prisma and SQLite</h4>
      <Form method="post">
        <div>
          <input name="email" placeholder="Email" size={30} />
        </div>
        <div>
          <input name="username" placeholder="User Name" size={30} />
        </div>
        <button type="submit" disabled={busy}>
          {busy ? "Creating..." : "Create New User"}
        </button>
      </Form>

      {users.map((user: User) => (
        <div style={{ border: "1px solid grey", padding: 6, margin: 8 }}>
          <div>{user.username}</div>
          <div>{user.email}</div>
          <div>
            <Link to={`/bookmarks/${user.id}`}>
              <button>View Details</button>
            </Link>
          </div>

        </div>
      ))}
    </div>
  );
}
Enter fullscreen mode Exit fullscreen mode
// bookmarks/$id.tsx
import { BookMark, PrismaClient, User } from "@prisma/client";
import { Form, Link, useLoaderData, useParams, useTransition } from "remix";

/**
 *
 * @param param0
 * @returns
 */
export async function loader({ params }) {
  const prisma = new PrismaClient();

  // get the user
  const user = await prisma.user.findUnique({
    where: {
      id: parseInt(params?.id),
    },
  });
  console.log("user with id = " + params?.id + " ", user);

  // get the bookmarks
  const usersBookmarks = await prisma.bookMark.findMany({
    where: {
      userId: parseInt(params?.id),
    },
    // include : {
    //     user : true
    // }
  });
  console.log("usersBookmarks with id = " + params?.id + " ", usersBookmarks);
  await prisma.$disconnect();
  return { bookmarks: usersBookmarks, user };
}

/**
 *
 * @param param0
 * @returns
 */
export async function action({ request }) {
  const form = await request.formData();

  const prisma = new PrismaClient();

  // HANDLE CREATION
  if (request.method === "POST") {
    const bookmark = await prisma.bookMark.create({
      data: {
        text: form.get("text"),
        url: form.get("url"),
        user: {
          connect: {
            id: parseInt(form.get("id")),
          },
        },
      },
    });
    console.log("created bookmark ", bookmark);
  }

  // HANDLE DELETE
  if (request.method === "DELETE") {
    const delResponse = await prisma.bookMark.delete({
      where: {
        id: parseInt(form.get("id")),
      },
    });
    console.log(delResponse);
  }
  await prisma.$disconnect();
  return true;
}

/**
 *
 * @returns
 */
export default function BookMarksById() {
  const { bookmarks, user } = useLoaderData();
  const { state } = useTransition();
  const busy = state === "submitting";

  const { id } = useParams();

  return (
    <div
      style={{
        fontFamily: "system-ui, sans-serif",
        lineHeight: "1.4",
        width: 600,
        margin: "auto",
      }}
    >
      <div>
        <Link to={`/`}>
          <button>HOME</button>
        </Link>
      </div>
      <h2>Manage Book Marks for {user?.username}</h2>
      <Form method="post">
        <div>
          <input name="text" placeholder="description" size={30} />
          <input name="url" placeholder="url" size={30} />
          <input type={"hidden"} value={id} name="id" />
        </div>
        <button type="submit" disabled={busy}>
          {busy ? "Creating..." : "Create New Bookmark"}
        </button>
      </Form>

      {bookmarks?.map((bookmark: BookMark) => (
        <div
          style={{ border: "1px solid grey", padding: 6, margin: 8 }}
          key={bookmark.id}
        >
          <div>{bookmark.createdAt}</div>
          <div>{bookmark.text}</div>
          <div>{bookmark.url}</div>

          <Form method="delete" >
            <input type={"hidden"} value={bookmark.id} name="id" />
            <button type="submit">DELETE</button>
          </Form>
        </div>
      ))}
    </div>
  );
}
Enter fullscreen mode Exit fullscreen mode

Links

Discussion (3)

Collapse
maxfindel profile image
Max F. Findel

Great series of articles Aaron, thanks for putting in the work ๐Ÿ’ช

Collapse
kellvembarbosa profile image
Kellvem Barbosa

Thank you so much for the posts <3

Collapse
aaronksaunders profile image
Aaron K Saunders Author

you are welcome, glad you enjoyed