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
- Relations in Drizzle:
- Use
relations()to define relationships between tables, simplifying queries involving joins.
- Use
- Foreign Key Constraints:
- Drizzle’s schema generation ensures constraints are respected, aligning well with PostgreSQL’s relational model.
- Supabase Compatibility:
- Drizzle works seamlessly with Supabase as both support PostgreSQL features.
