Building a full stack app with Remix & Drizzle ORM: Create & read records

Introduction

In this tutorial series, we'll explore building a full stack application using Remix and Drizzle ORM. 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, whose insightful tutorial series 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.

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 -

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 -

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 -

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 -

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 -

{receivedKudos.map(({ author, kudo }) => (
  <KudoCard
    key={kudo.id}
    userProfile={getUserProfile(author as User)}
    kudo={kudo}
  />
))}

Finally, under templates/RecentKudosPanel.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

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 -

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 -

 return (
   <Layout>
     <Outlet />
     <!-- other components --!>
   </Layout>
)

Under services/kudos.server.ts file add createKudo query -

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 -

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 -

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. Until next time PEACE!