Loading...

How to define schemas, foreign keys, relations, and query data by performing joins in a Nextjs app using Supabase and Drizzle?

question nextjs front-end
Ram Patra Published on November 12, 2024

Drizzle is an ORM that makes it easy to work with db, data migrations, etc. Here’s how you can define foreign keys, relations, and perform table joins using Drizzle ORM in a Next.js app with Supabase:


1. Define Schema with Foreign Keys and Relations

In your schema.ts file, define the tables and their relationships:

import { pgTable, serial, text, integer, primaryKey } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const UsersTable = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
});

export const PostsTable = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
  userId: integer("user_id").notNull(), // Foreign key
});

// Define relationships
export const UsersRelations = relations(UsersTable, ({ one, many }) => ({
  posts: many(PostsTable), // A user can have many posts
}));

export const PostsRelations = relations(PostsTable, ({ one }) => ({
  user: one(UsersTable, {
    fields: [PostsTable.userId], // Foreign key in `posts`
    references: [UsersTable.id], // Primary key in `users`
  }),
}));

2. Setting Up Drizzle in Your Next.js App

Configure Drizzle to connect with Supabase:

import { drizzle } from "drizzle-orm/supabase";
import { createClient } from "@supabase/supabase-js";

const supabase = createClient("YOUR_SUPABASE_URL", "YOUR_SUPABASE_KEY");
export const db = drizzle(supabase);

3. Performing Table Joins

To query data with joins, you can use the Drizzle query builder. Here’s an example:

Join users and posts Tables

import { db } from "./db";
import { UsersTable, PostsTable } from "./schema";

async function getUsersWithPosts() {
  const result = await db
    .select({
      userId: UsersTable.id,
      userName: UsersTable.name,
      postId: PostsTable.id,
      postTitle: PostsTable.title,
    })
    .from(UsersTable)
    .leftJoin(PostsTable, PostsTable.userId.eq(UsersTable.id)); // Join condition

  return result;
}

// Usage
getUsersWithPosts().then(console.log);

Result Example:

[
  { userId: 1, userName: "Alice", postId: 1, postTitle: "First Post" },
  { userId: 1, userName: "Alice", postId: 2, postTitle: "Second Post" },
  { userId: 2, userName: "Bob", postId: null, postTitle: null }
]

4. Insert Data with Relations

Insert a User:

await db.insert(UsersTable).values({ name: "Alice" });

Insert a Post for a User:

await db.insert(PostsTable).values({
  title: "First Post",
  content: "Hello World",
  userId: 1, // Foreign key
});

5. Query with Relations

To query related data more easily, use the relations helpers:

import { db } from "./db";
import { UsersTable, UsersRelations } from "./schema";

async function getUsersWithPostsUsingRelations() {
  const usersWithPosts = await db.query.UsersTable.findMany({
    with: {
      posts: true, // Automatically fetch related posts
    },
  });

  return usersWithPosts;
}

// Usage
getUsersWithPostsUsingRelations().then(console.log);

Result Example:

[
  {
    id: 1,
    name: "Alice",
    posts: [
      { id: 1, title: "First Post", content: "Hello World", userId: 1 },
      { id: 2, title: "Second Post", content: "Another Post", userId: 1 }
    ]
  },
  {
    id: 2,
    name: "Bob",
    posts: []
  }
]

Key Notes

  1. Relations in Drizzle:
    • Use relations() to define relationships between tables, simplifying queries involving joins.
  2. Foreign Key Constraints:
    • Drizzle’s schema generation ensures constraints are respected, aligning well with PostgreSQL’s relational model.
  3. Supabase Compatibility:
    • Drizzle works seamlessly with Supabase as both support PostgreSQL features.
Presentify

Take your presentation to the next level.

FaceScreen

Put your face and name on your screen.

ToDoBar

Your to-dos on your menu bar.

Ram Patra Published on November 12, 2024
Image placeholder

Keep reading

If this article was helpful, others might be too

question typescript react September 28, 2024 How to add a custom element to a Next.js/Typescript project?

Let’s say I have a custom element setapp-badge that I want to use in my tsx files. If I go ahead and use it, the compiler will throw an error and Next.js will fail to build. It seems the problem might be a combination of how Next.js, TypeScript, and custom elements work together. Therefore, let’s try an approach that avoids the namespace/module issues while ensuring custom elements are recognized in a Next.js/TypeScript project.

question nextjs react July 18, 2024 How to programmatically navigate to a page in Next.js?

In Next.js, you can programmatically navigate to different pages using the useRouter hook from the next/navigation module. This hook provides a convenient way to navigate between pages within your Next.js application. Below is an example demonstrating how to use the useRouter hook to programmatically navigate to another page.

question eslint nextjs November 14, 2024 How to disable ESLint in a Nextjs project?

Although it is highly advisable to enable ESLint at all times, if you do not want ESLint to run during next build, you can set the eslint.ignoreDuringBuilds option in next.config.js to true like below:

Like my work?

Please, feel free to reach out. I would be more than happy to chat.