Building a full stack app with Remix & Drizzle ORM: Create & read records
Tags: react, remix, drizzle, typescript
Published: 2023-06-28 16:48:14

### Introduction
In this tutorial series, we'll explore building a full stack application using [Remix](https://remix.run/) and [Drizzle ORM](https://orm.drizzle.team/). In this tutorial, we'll focus on the home page and explore creating read and create queries using Drizzle ORM. We'll implement the users panel to list all users, fetch and display a list of kudos, and showcase the recent kudos in the recent kudos panel. You'll discover the simplicity of constructing SQL queries with Drizzle's powerful query building capabilities.

Credit for inspiring this tutorial series goes [Sabin Adams](https://twitter.com/sabinthedev), whose insightful [tutorial series](https://www.prisma.io/blog/fullstack-remix-prisma-mongodb-1-7D0BfTXBmB6r) served as a valuable source of inspiration for this project.

### Overview
Please note that this tutorial assumes a certain level of familiarity with React.js, Node.js, and working with ORMs. In this tutorial we will be - 
- Fetch all users for the users panel.
- Fetch all the kudos received by the logged-in user and recent kudos.
- Build the create kudo modal.
- Create kudos.
- Parallelize queries for efficient data fetching.

All the code for this tutorial can be found [here](https://github.com/yaldram/kudos-remix-drizzle/tree/feat/kudos-screens).

![home-screen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dypetm2al0e1vi8wrexf.png)

### Step 1: Users Panel Queries
In the users panel, we want to display avatars of all users excluding the logged-in user. Under `services/users.server.ts` paste -
```ts
export function getOtherUsers(loggedInUserId: string) {
  return db.select().from(users).where(ne(users.id, loggedInUserId));
}

export function getUserById(userId: string) {
  return db.select().from(users).where(eq(users.id, userId));
}
```

### Step 2: Kudos Queries
To fetch all the kudos received by the logged-in user and the top 3 most recent kudos in our system, we will create the respective queries under `services/kudos.server.ts` - 
```ts
import { desc, eq } from "drizzle-orm";

import { db } from "~/drizzle/config.db.server";
import { kudos } from "~/drizzle/schemas/kudos.db.server";
import { users } from "~/drizzle/schemas/users.db.server";

export function getReceivedKudos(loggedInUserId: string) {
  return db
    .select()
    .from(kudos)
    .leftJoin(users, eq(users.id, kudos.authorId))
    .where(eq(kudos.recipientId, loggedInUserId));
}

export function getRecentKudos() {
  return db
    .select()
    .from(kudos)
    .leftJoin(users, eq(users.id, kudos.recipientId))
    .limit(3)
    .orderBy(desc(kudos.createdAt));
}
```
- The `getReceivedKudos` method, retrieves data from the kudos table, performs a left join with the users table, to get the author information, because we want to show who wrote the kudo. It filters the results based on the recipient ID matching the logged-in user's ID.
- The `getRecentKudos` method, retrieves data from the kudos table, performs a left join with the users table based on recipient IDs, because we want to show who has received the kudo. It limits the results to 3 rows, and arranges them in descending order based on creation timestamps.

### Step 3: Display data in Panels
Now, we can proceed to work on our home page and display the fetched data in their respective panels, utilizing the templates from the templates folder. First, we need to fetch all the necessary data in the loader function. Under `routes/home.tsx` -
```tsx
import type { LoaderArgs } from "@remix-run/node";
import { json } from "@remix-run/node";
import { useLoaderData } from "@remix-run/react";

import { KudoCard } from "~/components/molecules";
import type { User } from "~/drizzle/schemas/users.db.server";
import { Layout } from "~/layouts/Layout";
import { getReceivedKudos, getRecentKudos } from "~/services/kudos.server";
import { requireUserLogin } from "~/services/sessions.server";
import { getOtherUsers, getUserById } from "~/services/users.server";
import { getUserProfile } from "~/utils/helpers";
import {
  RecentKudosPanel,
  SearchPanel,
  UsersPanel,
} from "~/components/templates";

export async function loader({ request }: LoaderArgs) {
  const loggedInUserId = await requireUserLogin(request);

  const [loggedInUser] = await getUserById(loggedInUserId);
  const users = await getOtherUsers(loggedInUserId);
  const receivedKudos = await getReceivedKudos(loggedInUserId);
  const recentKudos = await getRecentKudos();

  return json({ users, loggedInUser, receivedKudos, recentKudos });
}

export default function HomePage() {
  const { users, loggedInUser, receivedKudos, recentKudos } =
    useLoaderData<typeof loader>();

  return (
    <Layout>
      <Layout>
        <div className="h-full flex">
          <div className="w-1/6 bg-gray-200 flex flex-col">
            <UsersPanel users={users} />
          </div>
          <div className="flex-1 flex flex-col">
            <SearchPanel user={loggedInUser} />
            <div className="flex-1 flex">
              <div className="w-full p-10 flex flex-col gap-y-4">
                {receivedKudos.map(({ kudos: kudo, users: user }) => (
                  <KudoCard
                    key={kudo.id}
                    userProfile={getUserProfile(user as User)}
                    kudo={kudo}
                  />
                ))}
              </div>
              <RecentKudosPanel records={recentKudos} />
            </div>
          </div>
        </div>
      </Layout>
    </Layout>
  );
}
```
- Defining the loader function to fetch data from various services.
- Extracting the fetched data using the useLoaderData hook.
- Displaying the users panel, search panel, received kudos, and recent kudos panels.

We do have some small caveats - 
- When using the Drizzle query builder and performing a join, the related data is obtained separately. This means that, for example, the author information is not nested within the kudo object.
- It's worth noting that in the received kudos query, the names "users" and "kudos" are in plural form because they reflect the names of our models. To enhance clarity, we can improve it by adding aliases using Drizzle.
Under `services/kudos.server.ts` create aliases - 
```ts
import { desc, eq } from "drizzle-orm";
import { alias } from "drizzle-orm/pg-core";

import { db } from "~/drizzle/config.db.server";
import { kudos } from "~/drizzle/schemas/kudos.db.server";
import { users } from "~/drizzle/schemas/users.db.server";

const author = alias(users, "author");
const recipient = alias(users, "recipeint");
const kudo = alias(kudos, "kudo");

export function getReceivedKudos(loggedInUserId: string) {
  return db
    .select()
    .from(kudo)
    .leftJoin(author, eq(author.id, kudo.authorId))
    .where(eq(kudo.recipientId, loggedInUserId));
}

export function getRecentKudos() {
  return db
    .select()
    .from(kudo)
    .leftJoin(recipient, eq(recipient.id, kudo.recipientId))
    .limit(3)
    .orderBy(desc(kudo.createdAt));
}

export type RecentKudos = ReturnType<typeof getRecentKudos>;
```
Please note replace all `kudos` with `kudo` in the queries.

By incorporating aliases for our joins, we encounter TypeScript errors in the `home.tsx` file. This is a result of Drizzle's robust type system, which helps with intellisense and catches issues early on. Now under `routes/home.tsx` - 
```tsx
{receivedKudos.map(({ author, kudo }) => (
  <KudoCard
    key={kudo.id}
    userProfile={getUserProfile(author as User)}
    kudo={kudo}
  />
))}
```
Finally, under `templates/RecentKudosPanel.tsx` - 
```tsx
import type { RecentKudos } from "~/services/kudos.server";
import { getUserProfile } from "~/utils/helpers";
import { emojiMap } from "~/utils/constants";
import type { User } from "~/drizzle/schemas/users.db.server";
import { Avatar } from "../atoms";

type RecentBarProps = {
  records: Awaited<RecentKudos>;
};

export function RecentKudosPanel({ records }: RecentBarProps) {
  return (
    <div className="w-1/5 border-l-4 border-l-yellow-300 flex flex-col items-center">
      <h2 className="text-xl text-yellow-300 font-semibold my-6">
        Recent Kudos
      </h2>
      <div className="h-full flex flex-col gap-y-10 mt-10">
        {records.map(({ kudo, recipeint }) => (
          <div className="h-24 w-24 relative" key={kudo.id}>
            <Avatar
              userProfile={getUserProfile(recipeint as User)}
              className="w-20 h-20"
            />
            <div className="h-8 w-8 text-3xl bottom-2 right-4 rounded-full absolute flex justify-center items-center">
              {emojiMap[kudo.style.emoji]}
            </div>
          </div>
        ))}
      </div>
    </div>
  );
}
```
From the terminal run `npm run dev` and check the users panel and search panel you can see the user avatars, you won't see any kudos because we have none in our database.

### Step 4: Creating Kudos
![create-kudos](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9fh497knwbfccij4xx40.png)
When we click on the user avatar from the users panel on the left we want to open this modal and here we will create the kudo, basically select the user and kudo him. Under `templates/UsersPanel.tsx` - 
```tsx
const navigate = useNavigate();

<Avatar
   key={user.id}
   userProfile={getUserProfile(user)}
   className="h-24 w-24 mx-auto flex-shrink-0"
   onClick={() => navigate(`kudo/${user.id}`)}
/>
```
On clicking on the user avatar we will navigate to the "/home/kudo/:userId" we will be using nesting routing for this as we are showing the Modal on the home page itself. Under `home.tsx` add the outlet component - 
```tsx
 return (
   <Layout>
     <Outlet />
     <!-- other components --!>
   </Layout>
)
```
Under `services/kudos.server.ts` file add `createKudo` query -
```ts
export function createKudo(newKudo: NewKudo) {
  return db.insert(kudos).values(newKudo).returning();
}
```
We will follow a similar process to create the kudo form. Firstly, we define the schema for the kudo form. Then, in the loader, we retrieve the information of the logged-in user who acts as the author of the kudo. We obtain the recipient ID from the search parameters. Finally, we have the action function where we submit the kudo. Under `routes` folder create `home.kudo.$recipientId.tsx` and paste - 
```tsx
import { useState } from "react";
import type { ActionArgs, LoaderArgs } from "@remix-run/node";
import { redirect, json } from "@remix-run/node";
import { parse } from "@conform-to/zod";
import { conform, useForm } from "@conform-to/react";
import { z } from "zod";
import {
  useLoaderData,
  useActionData,
  useNavigation,
  Form,
  useNavigate,
} from "@remix-run/react";

import type { KudoStyle } from "~/utils/constants";
import { requireUserLogin } from "~/services/sessions.server";
import { getUserById } from "~/services/users.server";
import { createKudo } from "~/services/kudos.server";
import { KudoCard, Modal } from "~/components/molecules";
import { Avatar, Button, SelectField } from "~/components/atoms";
import { getUserProfile } from "~/utils/helpers";
import {
  backgroundColorMap,
  bgColorEnum,
  emojiEnum,
  emojiMap,
  textColorEnum,
  textColorMap,
} from "~/utils/constants";

const defaultTodo = {
  message: "",
  style: {
    backgroundColor: "blue",
    textColor: "white",
    emoji: "handsup",
  } as KudoStyle,
};

const getSelectOptions = (data: Record<string, string>) =>
  Object.keys(data).reduce((acc: { name: string; value: string }[], curr) => {
    acc.push({
      name: curr.charAt(0).toUpperCase() + curr.slice(1).toLowerCase(),
      value: curr,
    });
    return acc;
  }, []);

const schema = z.object({
  recipientId: z.string(),
  authorId: z.string(),
  message: z.string().min(1, "Message is required"),
  backgroundColor: z.enum(bgColorEnum),
  textColor: z.enum(textColorEnum),
  emoji: z.enum(emojiEnum),
});

export async function loader({ request, params }: LoaderArgs) {
  const { recipientId } = params;
  const userId = await requireUserLogin(request);

  if (typeof recipientId !== "string") return redirect("/home");

  const [[author], [recipient]] = await Promise.all([
    getUserById(userId),
    getUserById(recipientId),
  ]);

  return json({ author, recipient });
}

export async function action({ request }: ActionArgs) {
  const formData = await request.formData();
  const submission = parse(formData, { schema });

  if (!submission.value || submission.intent !== "submit") {
    return json(submission, { status: 400 });
  }

  await createKudo({
    message: submission.value.message,
    style: {
      backgroundColor: submission.value.backgroundColor,
      textColor: submission.value.textColor,
      emoji: submission.value.emoji,
    },
    authorId: submission.value.authorId,
    recipientId: submission.value.recipientId,
  });

  return redirect("/home");
}

export default function CreateKudoModal() {
  const { author, recipient } = useLoaderData<typeof loader>();
  const lastSubmission = useActionData<typeof action>();
  const navigation = useNavigation();
  const navigate = useNavigate();
  const [kudo, setKudo] = useState(defaultTodo);

  const [
    form,
    { recipientId, authorId, message, backgroundColor, textColor, emoji },
  ] = useForm({
    id: "kudo",
    lastSubmission,
    defaultValue: {
      message: defaultTodo.message,
      ...defaultTodo.style,
      recipientId: recipient.id,
      authorId: author.id,
    },
    shouldRevalidate: "onInput",
    onValidate({ formData }) {
      return parse(formData, { schema });
    },
  });

  const onFormChange = (event: React.FormEvent<HTMLFormElement>) => {
    // @ts-expect-error: Type mismatch
    const { name, value } = event.target;
    if (name === "message") {
      return setKudo((kudo) => ({
        ...kudo,
        message: value,
      }));
    }

    // Handle the styles update
    setKudo((kudo) => ({
      ...kudo,
      style: {
        ...kudo.style,
        [name]: value,
      },
    }));
  };

  return (
    <Modal
      onOutsideClick={() => {
        navigate("/home");
      }}
      isOpen
      className="w-2/3 p-10"
    >
      <Form {...form.props} onChange={onFormChange} method="post">
        <input
          {...conform.input(recipientId, {
            hidden: true,
          })}
        />
        <input
          {...conform.input(authorId, {
            hidden: true,
          })}
        />
        <div className="flex flex-col md:flex-row gap-y-2 md:gap-y-0">
          <div className="text-center flex flex-col items-center gap-y-2 pr-8">
            <Avatar
              userProfile={getUserProfile(recipient)}
              className="h-24 w-24"
            />
            <p className="text-blue-300">
              {recipient.firstName} {recipient.lastName}
            </p>
          </div>
          <div className="flex-1 flex flex-col gap-y-4">
            <textarea
              {...conform.input(message, { ariaAttributes: true })}
              className="w-full rounded-xl h-40 p-4"
              placeholder={`Say something nice about ${recipient.firstName}...`}
            />
            <div
              id={message.errorId}
              className="text-xs font-semibold text-center tracking-wide text-red-500 w-full"
            >
              {message.error}
            </div>
            <div className="flex flex-col items-center md:flex-row md:justify-start gap-x-4">
              <SelectField
                options={getSelectOptions(backgroundColorMap)}
                {...conform.select(backgroundColor, { ariaAttributes: true })}
                label="Background Color"
                containerClassName="w-36"
                className="w-full rounded-xl px-3 py-2 text-gray-400"
                error={backgroundColor.error}
                errorId={backgroundColor.errorId}
              />
              <SelectField
                options={getSelectOptions(textColorMap)}
                {...conform.select(textColor, { ariaAttributes: true })}
                label="Text Color"
                containerClassName="w-36"
                className="w-full rounded-xl px-3 py-2 text-gray-400"
                error={textColor.error}
                errorId={textColor.errorId}
              />
              <SelectField
                options={getSelectOptions(emojiMap)}
                {...conform.select(emoji, { ariaAttributes: true })}
                label="Emoji"
                containerClassName="w-36"
                className="w-full rounded-xl px-3 py-2 text-gray-400"
                error={emoji.error}
                errorId={emoji.errorId}
              />
            </div>
          </div>
        </div>
        <br />
        <p className="text-blue-600 font-semibold mb-2">Preview</p>
        <div className="flex flex-col items-center md:flex-row gap-x-24 gap-y-2 md:gap-y-0">
          <KudoCard userProfile={getUserProfile(author)} kudo={kudo} />
          <div className="flex-1" />
          <Button
            disabled={
              navigation.state === "submitting" ||
              navigation.state === "loading"
            }
            type="submit"
          >
            Send
          </Button>
        </div>
      </Form>
    </Modal>
  );
}
```
- The `loader` function retrieves the logged-in user's information and the recipient's information based on the provided parameters, we are running both the queries in parallel.
- We extract the `recipientId` from the filename wildcard using params object in the `loader` function.
- The `action` function handles form submission, validates the form data using the schema, and creates a new kudo using the `createKudo` function.

Since the conform library does not provide the value of input fields on input change, we need to create a custom `onFormChange` event handler. Here's a simplified explanation:

- We use the `useState` hook to create a state variable called kudo. The initial value of kudo is set to the default kudo object.
- In the `onFormChange` event handler, we update the kudo state based on the changes in the form. If the name of the input field is "message", we update the message property of the kudo object. Otherwise, we update the style property by merging the current style object with the new value.
- The kudo preview section uses the kudo state to render the preview of the kudo, the way recipient will see it.
- Whenever the form inputs change, the `onFormChange` event handler is called, updating the kudo state and triggering a re-render of the kudo preview.

From the terminal run `npm run dev`, and create kudos, you will see them appearing in the recent kudos section on successful creation of the kudo, this is because when we create a Kudo and redirect to the home route, Remix executes the loaders before rendering the page.

### Step 5: Parallelize Queries
Under `routes/home.tsx` in the loaders function - 
```ts
export async function loader({ request }: LoaderArgs) {
  const recentKudosPromise = getRecentKudos();
  const loggedInUserId = await requireUserLogin(request);

  const [[loggedInUser], users, receivedKudos, recentKudos] = await Promise.all(
    [
      getUserById(loggedInUserId),
      getOtherUsers(loggedInUserId),
      getReceivedKudos(loggedInUserId),
      recentKudosPromise,
    ]
  );

  return json({ users, loggedInUser, receivedKudos, recentKudos });
}
```
By performing these parallel queries, the loader function efficiently retrieves all the necessary data for the application, minimizing the overall loading time and providing a smooth user experience.

### Conclusion
We explored how to fetch and display user data, retrieve received and recent Kudos, and create Kudos using forms. Throughout the tutorial, we leveraged the power of Drizzle's query building capabilities and benefited from its strong type system. In the next tutorial, we will work on user profile section and upload user profile to cloudinary. All the code for this tutorial can be found [here](https://github.com/yaldram/kudos-remix-drizzle/tree/feat/kudos-screens). Until next time PEACE!