Building a full stack app with Remix & Drizzle ORM: Drizzle Relations & Deployment

Introduction

In this tutorial series, we'll explore building a full stack application using Remix and Drizzle ORM. In this final tutorial of our series, we will explore how Drizzle, handles relationships between data entities. We will delve into concepts like associations and querying related data in a concise and efficient manner. Additionally, we will conclude our tutorial series by deploying our application to Vercel. By the end of this tutorial, we will have a fully functional and deployable app.

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 -

• Explore relationships with Drizzle ORM
• Deploy our app to Vercel

All the code for this tutorial series can be found here.

Step 1: Drizzle ORM Relationships

In the previous tutorials, we used Drizzle as a query builder to interact with the database, where we had to manually write SQL joins to retrieve related data. However, with the introduction of the Drizzle relations API, querying related data becomes much simpler and more readable using JavaScript. The Drizzle relations API allows us to define relationships between database tables/models using familiar JavaScript syntax, such as defining a one-to-one, one-to-many, or many-to-many relationship. Once the relationships are defined, we can easily access related data using intuitive methods and properties. This simplifies the code and improves readability, as we can directly work with the related data as JavaScript objects, rather than dealing with complex join statements. The Drizzle relations API abstracts away the underlying SQL complexity and allows us to focus on the logic and structure of our data relationships. Under drizzle/schemas/users.db.server.ts -

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  firstName: varchar("first_name").notNull(),
  lastName: varchar("last_name").notNull(),
  profileUrl: varchar("profile_url"),
  email: varchar("email").notNull(),
  password: text("password").notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  authored_kudos: many(kudos),
  recieved_kudos: many(kudos),
}));

Similarly under drizzle/schemas/kudos.db.server.ts -

export const kudos = pgTable("kudos", {
  id: uuid("id").primaryKey().defaultRandom(),
  message: text("message").notNull(),
  style: json("style").notNull().default(defaultStyle).$type<KudoStyle>(),
  createdAt: timestamp("created_at", { mode: "string" }).defaultNow(),
  updatedAt: timestamp("updated_at", { mode: "string" }).defaultNow(),
  authorId: uuid("author_id"),
  recipientId: uuid("recipient_id"),
});

export const kudosRelations = relations(kudos, ({ one }) => ({
  author: one(users, {
    fields: [kudos.authorId],
    references: [users.id],
  }),
  recipient: one(users, {
    fields: [kudos.recipientId],
    references: [users.id],
  }),
}));

Finally, under drizzle/schemas/config.server.ts -

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

import * as usersSchema from "./schemas/users.db.server";
import * as kudosSchema from "./schemas/kudos.db.server";

if (!process.env.DATABASE_URL) {
  throw new Error("environment variable: DATABASE_URL is missing.");
}

const queryClient = postgres(process.env.DATABASE_URL);
export const db = drizzle(queryClient, {
  schema: {
    ...usersSchema,
    ...kudosSchema,
  },
});

• Added usersRelations to define relations for users, specifying that they have many authored kudos and many received kudos.
• Edited the authorId and recipientId fields to remove the references and cascade onDelete options.
• Added kudosRelations to define relations for kudos, specifying that they have one author and one recipient from the users table.
• Modified the creation of the Drizzle client (db) to include the schemas and relations for both users and kudos.

Now under services/kudos.server.ts we can modify our queries -

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

export function getRecentKudos() {
  return db.query.kudos.findMany({
    limit: 3,
    orderBy: desc(kudos.createdAt),
    with: {
      recipient: true,
    },
  });
  // return db
  //   .select()
  //   .from(kudo)
  //   .leftJoin(recipient, eq(recipient.id, kudo.recipientId))
  //   .limit(3)
  //   .orderBy(desc(kudo.createdAt));
}

The functions now utilize the Drizzle relations API to automatically fetch the related data (author and recipient) along with the main data (kudos). This eliminates the need for manual joins and simplifies the querying process. The with parameter specifies that the author / recipient relation should be included in the result.

With the previous query builder approach, when fetching kudos, you would receive separate objects for kudos, authors / recipients. However, with the relations API, the related author and recipient objects are included as part of the kudo object itself. When querying kudos using the relations API, the author and recipient data will be automatically included in the kudo object. We will update your components to leverage this new structure of kudos

Under routes/home.tsx -

<div className="w-full p-10 flex flex-col gap-y-4">
 {receivedKudos.map((kudo) => (
   <KudoCard
     key={kudo.id}
     userProfile={getUserProfile(kudo.author as User)}
     kudo={kudo}
   />
 ))}
</div>

Under templates/RecentKudosPanel.tsx -

{records.map((kudo) => (
  <div className="h-24 w-24 relative" key={kudo.id}>
    <Avatar
      userProfile={getUserProfile(kudo.recipient 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>
))}

With the integration of Drizzle's relations API, working with data in our components has become much easier and more intuitive, providing full type support and enabling seamless access to related data.

Step 2: Deploying to Vercel

• Login to Vercel.
• Select your repository for deployment.
• Vercel will automatically detect that it is a Remix project.
• Scroll down and paste your .env file, which contains environment variables.
• Click on the "Deploy" button to initiate the deployment process. That easy.

Conclusion

• In this series we saw, how to set up a Remix project, create routes, and handle server-side rendering and client-side navigation.
• We implemented features such as form handling, file uploads, and working with relational data using Drizzle and its relations API.
• Drizzle, provided a flexible and intuitive way to interact with the database, allowing us to easily query and manipulate data while maintaining strong typing and type safety.
• Drizzle not only offered a powerful query building capability but also simplified the process of generating and managing migrations, making it effortless to create and apply database schema changes.
• Conform provided us with an intuitive API for handling form state, field validation, and error handling, focus management making it easier to build robust and accessible forms.
• Finally, we saw how easy it is to deploy our Remix application to Vercel, allowing us to share our web app with the world. All the code for this tutorial series can be found here. Until next time PEACE.