Relations
an5 ORM supports relations between models using foreign keys and declarative schemas.
Googlesheets note: The Google Sheets adapter resolves relations by cross-referencing matching ID columns across individual sheet tabs in memory.
Defining Relations
One-to-Many
model User {
id NVARCHAR(1000) @id @default(uuid())
email NVARCHAR(255) @unique
posts Post[] // One user has many posts
}
model Post {
id NVARCHAR(1000) @id @default(uuid())
title NVARCHAR(255)
content TEXT
published BOOLEAN @default(false)
authorId NVARCHAR(1000)
createdAt DATETIME @default(now())
author User @relation(fields: [authorId], references: [id])
}
One-to-One
model User {
id NVARCHAR(1000) @id @default(uuid())
email NVARCHAR(255) @unique
profile Profile?
}
model Profile {
id NVARCHAR(1000) @id @default(uuid())
bio TEXT
userId NVARCHAR(1000) @unique
user User @relation(fields: [userId], references: [id])
}
Many-to-Many
Implicit many-to-many join tables are not generated automatically. Model many-to-many as an explicit join model with two foreign keys:
model PostTag {
id NVARCHAR(1000) @id @default(uuid())
postId NVARCHAR(1000)
tagId NVARCHAR(1000)
post Post @relation(fields: [postId], references: [id])
tag Tag @relation(fields: [tagId], references: [id])
@@map("post_tags")
}
Querying Relations
Include Relations
// Include all posts for a user
const user = await db.user.findUnique({
where: { id: 'user-id' },
include: { posts: true }
});
// Include with filtering, ordering, pagination, and projection
const user = await db.user.findUnique({
where: { id: 'user-id' },
include: {
posts: {
select: { id: true, title: true },
where: { published: true },
orderBy: { createdAt: 'desc' },
take: 5
}
}
});
Relation Filters
To-many filters (some, none, every) and to-one filters (is, isNot) compile to EXISTS / NOT EXISTS subqueries:
// Find users with at least one published post
const users = await db.user.findMany({
where: {
posts: {
some: { published: true }
}
}
});
// Find users where all posts are published
const users = await db.user.findMany({
where: {
posts: {
every: { published: true }
}
}
});
// To-one: posts whose author is active
const posts = await db.post.findMany({
where: {
author: {
is: { isActive: true }
}
}
});
Nested Create & Connect
const user = await db.user.create({
data: {
email: 'john@example.com',
name: 'John',
posts: {
create: [
{ title: 'Post 1', content: 'Content 1' },
{ title: 'Post 2', content: 'Content 2' }
]
}
}
});
Cascade Operations
Configure cascade delete behavior in your .an5 schema using @cascade:
model User {
id NVARCHAR(1000) @id @default(uuid())
posts Post[] @cascade
@@map("users")
}
model Post {
id NVARCHAR(1000) @id @default(uuid())
authorId NVARCHAR(1000)
author User @relation(fields: [authorId], references: [id])
@@map("posts")
}
Next Steps
- Advanced Queries - Complex filtering, aggregation and raw SQL
- Transactions - Atomic multi-table updates