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 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:

question nextjs react July 11, 2024 How to set environment variable in a Nextjs App and when to use NEXT_PUBLIC prefix?

Setting environment variables in a Next.js app is straightforward. Next.js supports loading environment variables from .env files. Here’s a step-by-step guide on how to set and use environment variables in your Next.js application:

question nextjs react February 9, 2024 Main advantages of using the Link component in Next.js over Anchors and Buttons

The main advantage of using the Link component in Next.js for client-side navigation is its optimized prefetching behavior, which enhances the performance and user experience of your web application. Here’s a breakdown of the key advantages: