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 in Python
Query related models using dictionary specifications passed to include:
# Fetch user with their posts
user = db.user.find_unique(
where={"id": "user-id"},
include={"posts": True}
)
# Filtered and ordered relation include
user = db.user.find_unique(
where={"id": "user-id"},
include={
"posts": {
"where": {"published": True},
"order_by": {"createdAt": "desc"},
"take": 5
}
}
)
Query keys are the schema field names, used verbatim — createdAt, not
created_at. The generated dataclasses are snake_case, but nothing rewrites a
key before it reaches the SQL builder, so a snake_case key compiles to
"created_at" and no such column exists.
Relation Filtering
The some / every / none shorthand is the TypeScript adapter’s. The Python
where parser never reads it, and a key it cannot parse lands in an empty
WHERE, which matches every row instead of failing — so filter from the child
side:
# Posts whose author is a given user
posts = db.post.find_many(
where={"authorId": "user-id"}
)
# Published posts, newest first
posts = db.post.find_many(
where={"published": True},
order_by={"createdAt": "desc"}
)
Nested Create
user = db.user.create({
"data": {
"email": "sarah@example.com",
"name": "Sarah",
"posts": {
"create": [
{"title": "First Post", "content": "Hello World"}
]
}
}
})
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