Working with Relationships

Learn how to define and query related data using foreign keys and the populate feature.

Overview

Relationships allow you to connect data across multiple tables, just like in a traditional relational database. Taruvi supports:

🔗 One-to-Many: One user has many posts
🔗 Many-to-One: Many posts belong to one user
🔗 Many-to-Many: Posts have many tags, tags have many posts

The populate parameter lets you fetch related data in a single request, eliminating the need for multiple round-trips.

Defining Relationships

Foreign Keys in Schema

Define relationships using the foreignKeys property in your table schema:

{
  "name": "posts",
  "schema": {
    "fields": [
      {
        "name": "id",
        "type": "integer",
        "constraints": {"required": true}
      },
      {
        "name": "author_id",
        "type": "integer",
        "constraints": {"required": true}
      },
      {
        "name": "title",
        "type": "string"
      }
    ],
    "primaryKey": ["id"],
    "foreignKeys": [
      {
        "fields": ["author_id"],
        "reference": {
          "resource": "users",
          "fields": ["id"]
        }
      }
    ]
  }
}

Relationship Types

One-to-Many (Has Many)

A user has many posts:

// users table
{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "username", "type": "string"}
  ],
  "primaryKey": ["id"]
}

// posts table
{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "author_id", "type": "integer"},
    {"name": "title", "type": "string"}
  ],
  "primaryKey": ["id"],
  "foreignKeys": [
    {
      "fields": ["author_id"],
      "reference": {
        "resource": "users",
        "fields": ["id"]
      }
    }
  ]
}

Many-to-One (Belongs To)

A post belongs to one user (inverse of above).

Many-to-Many

Posts have many tags through a join table:

// posts table
{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "title", "type": "string"}
  ],
  "primaryKey": ["id"]
}

// tags table
{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "name", "type": "string"}
  ],
  "primaryKey": ["id"]
}

// post_tags join table
{
  "fields": [
    {"name": "post_id", "type": "integer"},
    {"name": "tag_id", "type": "integer"}
  ],
  "primaryKey": ["post_id", "tag_id"],
  "foreignKeys": [
    {
      "fields": ["post_id"],
      "reference": {"resource": "posts", "fields": ["id"]}
    },
    {
      "fields": ["tag_id"],
      "reference": {"resource": "tags", "fields": ["id"]}
    }
  ]
}

Use the populate parameter to include related data in your response.

Basic Population

Forward Relationship (Many-to-One)

Get posts with their authors:

REST API
Python
JavaScript

GET /api/apps/blog-app/datatables/posts/data/?populate=author

# Get posts with author details populated
posts = auth_client.database.query('posts', app_slug='blog-app') \
    .populate('author') \
    .get()

for post in posts:
    author = post.get('author', {})
    print(f"{post['title']} by {author.get('username', 'Unknown')}")

// Get posts with author details populated
const posts = await client.database
    .from('posts')
    .populate(['author'])
    .execute()

posts.data.forEach(post => {
    const author = post.author || {}
    console.log(`${post.title} by ${author.username || 'Unknown'}`)
})

Response:

{
  "data": [
    {
      "id": 1,
      "author_id": 10,
      "title": "Getting Started with Taruvi",
      "author": {
        "id": 10,
        "username": "john_doe",
        "email": "john@example.com"
      }
    },
    {
      "id": 2,
      "author_id": 10,
      "title": "Advanced Features",
      "author": {
        "id": 10,
        "username": "john_doe",
        "email": "john@example.com"
      }
    }
  ],
  "total": 2
}

The author field is automatically populated based on the author_id foreign key.

Reverse Relationship (One-to-Many)

Get users with their posts:

REST API
Python
JavaScript

GET /api/apps/blog-app/datatables/users/data/?populate=posts

# Get users with their posts populated (reverse relationship)
users = auth_client.database.query('users', app_slug='blog-app') \
    .populate('posts') \
    .get()

for user in users:
    posts = user.get('posts', [])
    print(f"{user['username']} has {len(posts)} posts")
    for post in posts:
        print(f"  - {post['title']}")

// Get users with their posts populated (reverse relationship)
const users = await client.database
    .from('users')
    .populate(['posts'])
    .execute()

users.data.forEach(user => {
    const posts = user.posts || []
    console.log(`${user.username} has ${posts.length} posts`)
    posts.forEach(post => {
        console.log(`  - ${post.title}`)
    })
})

Response:

{
  "data": [
    {
      "id": 10,
      "username": "john_doe",
      "email": "john@example.com",
      "posts": [
        {
          "id": 1,
          "author_id": 10,
          "title": "Getting Started with Taruvi"
        },
        {
          "id": 2,
          "author_id": 10,
          "title": "Advanced Features"
        }
      ]
    }
  ],
  "total": 1
}

Multiple Relationships

Populate multiple relationships at once:

REST API
Python
JavaScript

GET /api/apps/blog-app/datatables/posts/data/?populate=author,comments

# Populate multiple relationships
posts = auth_client.database.query('posts', app_slug='blog-app') \
    .populate('author', 'comments') \
    .get()

for post in posts:
    author = post.get('author', {})
    comments = post.get('comments', [])
    print(f"{post['title']} by {author.get('username')}")
    print(f"  {len(comments)} comments")

// Populate multiple relationships
const posts = await client.database
    .from('posts')
    .populate(['author', 'comments'])
    .execute()

posts.data.forEach(post => {
    const author = post.author || {}
    const comments = post.comments || []
    console.log(`${post.title} by ${author.username}`)
    console.log(`  ${comments.length} comments`)
})

Response:

{
  "data": [
    {
      "id": 1,
      "author_id": 10,
      "title": "Getting Started with Taruvi",
      "author": {
        "id": 10,
        "username": "john_doe"
      },
      "comments": [
        {
          "id": 101,
          "post_id": 1,
          "user_id": 20,
          "content": "Great post!"
        },
        {
          "id": 102,
          "post_id": 1,
          "user_id": 30,
          "content": "Very helpful, thanks!"
        }
      ]
    }
  ],
  "total": 1
}

Nested Population

Populate relationships of relationships (up to 3 levels deep by default):

REST API
Python
JavaScript

GET /api/apps/blog-app/datatables/posts/data/?populate=author,comments.user

# Nested population - populate comments and their users
posts = auth_client.database.query('posts', app_slug='blog-app') \
    .populate('author', 'comments.user') \
    .get()

for post in posts:
    author = post.get('author', {})
    comments = post.get('comments', [])
    print(f"{post['title']} by {author.get('username')}")
    for comment in comments:
        user = comment.get('user', {})
        print(f"  Comment by {user.get('username')}: {comment['content']}")

Note: Use dot notation (comments.user) for nested relationships.

// Nested population - populate comments and their users
const posts = await client.database
    .from('posts')
    .populate(['author', 'comments.user'])
    .execute()

posts.data.forEach(post => {
    const author = post.author || {}
    const comments = post.comments || []
    console.log(`${post.title} by ${author.username}`)
    comments.forEach(comment => {
        const user = comment.user || {}
        console.log(`  Comment by ${user.username}: ${comment.content}`)
    })
})

Note: Use dot notation (comments.user) for nested relationships.

Response:

{
  "data": [
    {
      "id": 1,
      "title": "Getting Started with Taruvi",
      "author": {
        "id": 10,
        "username": "john_doe"
      },
      "comments": [
        {
          "id": 101,
          "content": "Great post!",
          "user": {
            "id": 20,
            "username": "jane_smith"
          }
        },
        {
          "id": 102,
          "content": "Very helpful!",
          "user": {
            "id": 30,
            "username": "bob_jones"
          }
        }
      ]
    }
  ],
  "total": 1
}

Syntax: Use dot notation to populate nested relationships.

Advanced Population Parameters

Control how related data is fetched:

REST API
Python
JavaScript

GET /api/apps/blog-app/datatables/users/data/?\
  populate=posts&\
  posts_sort=created_at&\
  posts_order=desc&\
  posts_limit=5

This:

Gets users
Populates their posts
Sorts posts by created_at (newest first)
Limits to 5 most recent posts per user

Parameters:

{relationship}_sort: Field to sort by
{relationship}_order: asc or desc
{relationship}_limit: Maximum number of related records
{relationship}_offset: Skip first N related records

SDK Limitation

Advanced population parameters are REST API only. The Python SDK does not currently support posts_sort, posts_order, posts_limit, or posts_offset query parameters for controlling how related data is fetched.

For advanced relationship control, use the REST API directly or fetch all related records and filter/sort client-side.

# Basic population (all related records)
users = auth_client.database.query('users', app_slug='blog-app') \
    .populate('posts') \
    .get()

# Client-side sorting and limiting (workaround)
for user in users:
    posts = user.get('posts', [])
    # Sort by created_at descending
    sorted_posts = sorted(posts, key=lambda p: p.get('created_at', ''), reverse=True)
    # Limit to 5
    recent_posts = sorted_posts[:5]
    print(f"{user['username']}: {len(recent_posts)} recent posts")

SDK Limitation

Advanced population parameters are REST API only. The JavaScript SDK does not currently support posts_sort, posts_order, posts_limit, or posts_offset query parameters for controlling how related data is fetched.

For advanced relationship control, use the REST API directly or fetch all related records and filter/sort client-side.

// Basic population (all related records)
const users = await client.database
    .from('users')
    .populate(['posts'])
    .execute()

// Client-side sorting and limiting (workaround)
users.data.forEach(user => {
    const posts = user.posts || []
    // Sort by created_at descending
    const sortedPosts = posts.sort((a, b) =>
        new Date(b.created_at) - new Date(a.created_at)
    )
    // Limit to 5
    const recentPosts = sortedPosts.slice(0, 5)
    console.log(`${user.username}: ${recentPosts.length} recent posts`)
})

Real-World Examples

Blog Post with Full Details

Get a post with author and comments (including comment authors):

REST API
Python
JavaScript

GET /api/apps/blog-app/datatables/posts/data/1/?populate=author,comments.user

# Get single post with nested relationships
post = auth_client.database.get('posts', 1, app_slug='blog-app', populate=['author', 'comments.user'])

# Display post details
author = post.get('author', {})
comments = post.get('comments', [])

print(f"Title: {post['title']}")
print(f"Author: {author.get('username')}")
print(f"Bio: {author.get('bio')}")
print(f"\nComments ({len(comments)}):")

for comment in comments:
    user = comment.get('user', {})
    print(f"  {user.get('username')}: {comment['content']}")
    print(f"  Posted: {comment.get('created_at')}")

// Get single post with nested relationships
const post = await client.database
    .from('posts')
    .get('1')
    .populate(['author', 'comments.user'])
    .execute()

// Display post details
const author = post.author || {}
const comments = post.comments || []

console.log(`Title: ${post.title}`)
console.log(`Author: ${author.username}`)
console.log(`Bio: ${author.bio}`)
console.log(`\nComments (${comments.length}):`)

comments.forEach(comment => {
    const user = comment.user || {}
    console.log(`  ${user.username}: ${comment.content}`)
    console.log(`  Posted: ${comment.created_at}`)
})

Response:

{
  "data": {
    "id": 1,
    "title": "Getting Started with Taruvi",
    "content": "In this post, we'll explore...",
    "created_at": "2024-01-15T10:30:00Z",
    "author": {
      "id": 10,
      "username": "john_doe",
      "bio": "Software developer"
    },
    "comments": [
      {
        "id": 101,
        "content": "Great post!",
        "created_at": "2024-01-15T14:20:00Z",
        "user": {
          "id": 20,
          "username": "jane_smith"
        }
      },
      {
        "id": 102,
        "content": "Very helpful, thanks!",
        "created_at": "2024-01-15T16:45:00Z",
        "user": {
          "id": 30,
          "username": "bob_jones"
        }
      }
    ]
  }
}

E-commerce Order

Get an order with customer details and order items (including products):

REST API
Python
JavaScript

GET /api/apps/shop-app/datatables/orders/data/42/?\
  populate=customer,items.product

# Get order with customer and items (with products)
order = auth_client.database.get('orders', 42, app_slug='shop-app', populate=['customer', 'items.product'])

# Display order details
customer = order.get('customer', {})
items = order.get('items', [])

print(f"Order #{order['order_number']}")
print(f"Status: {order['status']}")
print(f"Customer: {customer.get('name')} ({customer.get('email')})")
print(f"\nItems:")

for item in items:
    product = item.get('product', {})
    print(f"  {product.get('name')} (SKU: {product.get('sku')})")
    print(f"    Quantity: {item['quantity']} × ${item['price']} = ${item['quantity'] * item['price']}")

print(f"\nTotal: ${order['total']}")

// Get order with customer and items (with products)
const order = await client.database
    .from('orders')
    .get('42')
    .populate(['customer', 'items.product'])
    .execute()

// Display order details
const customer = order.customer || {}
const items = order.items || []

console.log(`Order #${order.order_number}`)
console.log(`Status: ${order.status}`)
console.log(`Customer: ${customer.name} (${customer.email})`)
console.log(`\nItems:`)

items.forEach(item => {
    const product = item.product || {}
    console.log(`  ${product.name} (SKU: ${product.sku})`)
    console.log(`    Quantity: ${item.quantity} × $${item.price} = $${item.quantity * item.price}`)
})

console.log(`\nTotal: $${order.total}`)

Response:

{
  "data": {
    "id": 42,
    "order_number": "ORD-2024-00042",
    "total": 129.98,
    "status": "shipped",
    "customer": {
      "id": 100,
      "name": "Alice Johnson",
      "email": "alice@example.com"
    },
    "items": [
      {
        "id": 201,
        "quantity": 2,
        "price": 49.99,
        "product": {
          "id": 50,
          "name": "Widget Pro",
          "sku": "WGT-PRO-001"
        }
      },
      {
        "id": 202,
        "quantity": 1,
        "price": 29.99,
        "product": {
          "id": 51,
          "name": "Gadget Lite",
          "sku": "GDG-LIT-002"
        }
      }
    ]
  }
}

User Dashboard

Get user with their recent posts and latest comments:

REST API
Python
JavaScript

GET /api/apps/blog-app/datatables/users/data/10/?\
  populate=posts,comments&\
  posts_sort=created_at&\
  posts_order=desc&\
  posts_limit=5&\
  comments_sort=created_at&\
  comments_order=desc&\
  comments_limit=10

# Get user with posts and comments (sorted/limited client-side)
user = auth_client.database.get('users', 10, app_slug='blog-app', populate=['posts', 'comments'])

# Sort and limit posts client-side
posts = user.get('posts', [])
recent_posts = sorted(posts, key=lambda p: p.get('created_at', ''), reverse=True)[:5]

# Sort and limit comments client-side
comments = user.get('comments', [])
latest_comments = sorted(comments, key=lambda c: c.get('created_at', ''), reverse=True)[:10]

print(f"User: {user['username']}")
print(f"\nRecent Posts ({len(recent_posts)}):")
for post in recent_posts:
    print(f"  - {post['title']} ({post.get('created_at')})")

print(f"\nLatest Comments ({len(latest_comments)}):")
for comment in latest_comments:
    print(f"  - {comment['content'][:50]}... ({comment.get('created_at')})")

Note: Advanced population parameters (posts_sort, posts_limit, etc.) are not supported in the SDK. Use client-side sorting/limiting or REST API directly.

// Get user with posts and comments (sorted/limited client-side)
const user = await client.database
    .from('users')
    .get('10')
    .populate(['posts', 'comments'])
    .execute()

// Sort and limit posts client-side
const posts = user.posts || []
const recentPosts = posts
    .sort((a, b) => new Date(b.created_at) - new Date(a.created_at))
    .slice(0, 5)

// Sort and limit comments client-side
const comments = user.comments || []
const latestComments = comments
    .sort((a, b) => new Date(b.created_at) - new Date(a.created_at))
    .slice(0, 10)

console.log(`User: ${user.username}`)
console.log(`\nRecent Posts (${recentPosts.length}):`)
recentPosts.forEach(post => {
    console.log(`  - ${post.title} (${post.created_at})`)
})

console.log(`\nLatest Comments (${latestComments.length}):`)
latestComments.forEach(comment => {
    console.log(`  - ${comment.content.substring(0, 50)}... (${comment.created_at})`)
})

Note: Advanced population parameters (posts_sort, posts_limit, etc.) are not supported in the SDK. Use client-side sorting/limiting or REST API directly.

Performance Considerations

Single Query Execution

The populate feature uses optimized SQL joins (LATERAL joins for PostgreSQL) to fetch all data in a single database query, which is much faster than making separate requests:

# ❌ N+1 Query Problem (multiple requests)
GET /api/apps/blog-app/datatables/posts/data/
GET /api/apps/blog-app/datatables/users/data/10/
GET /api/apps/blog-app/datatables/users/data/20/
GET /api/apps/blog-app/datatables/users/data/30/
# ... one request per author

# ✅ Single Request with Populate
GET /api/apps/blog-app/datatables/posts/data/?populate=author
# Returns all posts with authors in one query!

Depth Limits

To prevent performance issues, nesting is limited to 3 levels by default:

# ✅ Allowed: 2 levels
?populate=author,comments.user

# ✅ Allowed: 3 levels
?populate=posts.comments.user

# ❌ Too deep: 4+ levels
?populate=posts.comments.user.posts

Contact your administrator if you need deeper nesting.

When populating one-to-many relationships, limit the results:

# ✅ Good: Limit comments per post
?populate=comments&comments_limit=10

# ❌ Bad: Could return thousands of comments
?populate=comments

Client Integration

JavaScript SDK Example

import { TaruviClient } from '@taruvi/sdk'

const client = new TaruviClient({
    baseUrl: 'https://your-domain.com',
    jwtToken: 'YOUR_JWT_TOKEN'
})

async function fetchPostWithDetails(postId: number) {
  const post = await client.database
      .from('posts')
      .get(postId.toString())
      .populate(['author', 'comments.user'])
      .execute()

  // Access nested data
  const author = post.author || {}
  const comments = post.comments || []

  console.log(`Post by ${author.username}`)
  console.log(`${comments.length} comments`)

  comments.forEach(comment => {
    const user = comment.user || {}
    console.log(`- ${user.username}: ${comment.content}`)
  })

  return post
}

React Component with SDK

import { useEffect, useState } from 'react'
import { TaruviClient } from '@taruvi/sdk'

const client = new TaruviClient({
    baseUrl: 'https://your-domain.com',
    jwtToken: 'YOUR_JWT_TOKEN'
})

interface Post {
  id: number
  title: string
  content: string
  author?: { username: string }
  comments?: Array<{ id: number; content: string; user?: { username: string } }>
}

function PostDetail({ postId }: { postId: number }) {
  const [post, setPost] = useState<Post | null>(null)
  const [loading, setLoading] = useState(true)

  useEffect(() => {
    client.database
      .from('posts')
      .get(postId.toString())
      .populate(['author', 'comments.user'])
      .execute()
      .then(data => {
        setPost(data)
        setLoading(false)
      })
      .catch(error => {
        console.error('Failed to fetch post:', error)
        setLoading(false)
      })
  }, [postId])

  if (loading) return <div>Loading...</div>
  if (!post) return <div>Post not found</div>

  const author = post.author || {}
  const comments = post.comments || []

  return (
    <article>
      <h1>{post.title}</h1>
      <p>By {author.username}</p>
      <div>{post.content}</div>

      <h2>Comments ({comments.length})</h2>
      {comments.map(comment => {
        const user = comment.user || {}
        return (
          <div key={comment.id}>
            <strong>{user.username}</strong>: {comment.content}
          </div>
        )
      })}
    </article>
  )
}

Python SDK Example

from taruvi import TaruviClient

# Initialize client
auth_client = TaruviClient(
    base_url="https://your-domain.com",
    jwt_token="YOUR_JWT_TOKEN"
)

def fetch_user_with_posts(user_id: int) -> dict:
    # Get user with posts and comments populated
    user = auth_client.database.get(
        'users',
        user_id,
        app_slug='blog-app',
        populate=['posts', 'comments']
    )

    # Sort and limit posts client-side (SDK doesn't support posts_sort/posts_limit)
    posts = user.get('posts', [])
    recent_posts = sorted(posts, key=lambda p: p.get('created_at', ''), reverse=True)[:10]

    comments = user.get('comments', [])

    print(f"User: {user['username']}")
    print(f"Recent Posts: {len(recent_posts)}")
    print(f"Comments: {len(comments)}")

    for post in recent_posts:
        print(f"  - {post['title']} ({post.get('created_at')})")

    return user

Troubleshooting

"Relationship 'X' not found"

Cause: The relationship name doesn't match any foreign key.

Solution: Check your schema's foreignKeys definition. The relationship name should match the field name (without _id suffix) or the referenced resource name.

"Maximum nesting depth exceeded"

Cause: You're trying to populate more than 3 levels deep.

Solution: Reduce nesting or make separate requests for deeply nested data.

Slow Performance

Cause: Populating too many related records or too deeply nested.

Solution:

Use {relationship}_limit to restrict results
Only populate relationships you actually need
Consider making separate requests for large datasets

Best Practices

1. Only Populate What You Need

# ❌ Bad: Populates everything
?populate=author,comments,tags,categories,related_posts

# ✅ Good: Only what you'll display
?populate=author,comments

2. Limit Collection Sizes

# ✅ Always limit one-to-many relationships
?populate=posts&posts_limit=10

3. Use Specific Fields (Future Feature)

# Coming soon: Select specific fields from related data
?populate=author&author_fields=id,username

4. Cache Populated Data

Popular posts with comments don't change frequently—cache them:

const cache = new Map();

async function fetchWithCache(url, ttl = 60000) {
  if (cache.has(url)) {
    const { data, timestamp } = cache.get(url);
    if (Date.now() - timestamp < ttl) {
      return data;
    }
  }

  const response = await fetch(url);
  const data = await response.json();

  cache.set(url, { data, timestamp: Date.now() });
  return data;
}

Common Patterns

Blog Homepage

GET /api/apps/blog-app/datatables/posts/data/?\
  status=published&\
  populate=author&\
  _sort=published_at&\
  _order=desc&\
  _start=0&\
  _end=10

User Profile

GET /api/apps/blog-app/datatables/users/data/10/?\
  populate=posts,comments&\
  posts_limit=5&\
  comments_limit=10

Product Page

GET /api/apps/shop-app/datatables/products/data/42/?\
  populate=category,reviews.user,related_products

Activity Feed

GET /api/apps/social-app/datatables/activities/data/?\
  user_id=10&\
  populate=actor,target&\
  _sort=created_at&\
  _order=desc&\
  _start=0&\
  _end=20

Next Steps

CRUD Operations: Insert, update, and delete records with relationships
Querying Data: Master filtering and sorting
Schema Guide: Define foreign keys properly

Overview​

Defining Relationships​

Foreign Keys in Schema​

Relationship Types​

One-to-Many (Has Many)​

Many-to-One (Belongs To)​

Many-to-Many​

Populating Related Data​

Basic Population​

Forward Relationship (Many-to-One)​

Reverse Relationship (One-to-Many)​

Multiple Relationships​

Nested Population​

Advanced Population Parameters​

Real-World Examples​

Blog Post with Full Details​

E-commerce Order​

User Dashboard​

Performance Considerations​

Single Query Execution​

Depth Limits​

Limit Related Records​

Client Integration​

JavaScript SDK Example​

React Component with SDK​

Python SDK Example​

Troubleshooting​

"Relationship 'X' not found"​

"Maximum nesting depth exceeded"​

Slow Performance​

Best Practices​

1. Only Populate What You Need​

2. Limit Collection Sizes​

3. Use Specific Fields (Future Feature)​

4. Cache Populated Data​

Common Patterns​

Blog Homepage​

User Profile​

Product Page​

Activity Feed​

Next Steps​

Overview

Defining Relationships

Foreign Keys in Schema

Relationship Types

One-to-Many (Has Many)

Many-to-One (Belongs To)

Many-to-Many

Populating Related Data

Basic Population

Forward Relationship (Many-to-One)

Reverse Relationship (One-to-Many)

Multiple Relationships

Nested Population

Advanced Population Parameters

Real-World Examples

Blog Post with Full Details

E-commerce Order

User Dashboard

Performance Considerations

Single Query Execution

Depth Limits

Limit Related Records

Client Integration

JavaScript SDK Example

React Component with SDK

Python SDK Example

Troubleshooting

"Relationship 'X' not found"

"Maximum nesting depth exceeded"

Slow Performance

Best Practices

1. Only Populate What You Need

2. Limit Collection Sizes

3. Use Specific Fields (Future Feature)

4. Cache Populated Data

Common Patterns

Blog Homepage

User Profile

Product Page

Activity Feed

Next Steps