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

Aaron K Saunders - Feb 15 '22 - - Dev Community

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

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player