ForeignKey & JOINs

ForeignKey & JOINs

PyGo provides automatic foreign key relationship handling with compile-time JOIN generation. Foreign keys are defined in the .pgo DSL and the transpiler generates idiomatic Go and Python code with proper SQL JOIN support.

Defining Relationships

One-to-Many (ForeignKey)

model Author:
  id: UUID
  name: String
  email: Email

model Post:
  title: String
  content: String
  author: ForeignKey[User]
  author_id: UUID

Explicit Foreign Key Declaration

foreignKey user_id -> User

This generates a get_user() method that fetches the related User record.

Generated Code

Python (gen_py.py)

class Post(BaseModel):
    title: str
    content: str
    author_id: str
    author: User  # ForeignKey field

    def get_user(self) -> User:
        return User.find(self.author_id)

Go (gen_go.go)

type Post struct {
    Title    string
    Content  string
    AuthorID string
    Author   User
}

func (p *Post) GetUser() *User {
    return UserFind(p.AuthorID)
}

Querying with JOINs

PyGo automatically generates JOIN queries when you access related models:

# Get author for a post (single query, JOIN)
post = Post.find(id)
author = post.get_user()

Foreign Key Options

CASCADE Delete

model Post:
  title: String
  user: ForeignKey[User]  # Default: protect

Multiple Foreign Keys

model Comment:
  content: String
  author_id: UUID
  post_id: UUID
  author: ForeignKey[User]
  post: ForeignKey[Post]

Many-to-Many Relationships

model Tag:
  name: String

model Article:
  title: String
  content: String
  tags: Array[Tag]

Reverse Relations

Access reverse relationships automatically:

author = Author.find(id)
# Get all posts by this author
posts = Post.filter(author_id=author.id)

Database Schema

CREATE TABLE author (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

CREATE TABLE post (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author_id UUID NOT NULL,
    FOREIGN KEY (author_id) REFERENCES author(id) ON DELETE CASCADE
);

Performance Considerations

• Use filter() with foreign key fields for indexed lookups
• JOINs are generated automatically when using get_<relation>() methods
• Consider denormalization for frequently accessed relationships