Database Relations

Relational databases shine when connecting disparate sets of data. In @hedystia/db, relationships between tables are handled gracefully via foreign key definitions.

Defining Relationships

You define a relationship on a column using the .references() modifier. The modifier takes a callback returning the referenced column from another table.

import { table, integer, varchar } from "@hedystia/db";

export const users = table("users", {
  id: integer().primaryKey().autoIncrement(),
  name: varchar(255).notNull(),
});

export const posts = table("posts", {
  id: integer().primaryKey().autoIncrement(),
  title: varchar(255).notNull(),
  
  // Create a foreign key linking back to `users.id`
  userId: integer().references(() => users.id, { onDelete: "CASCADE" }),
});

Here, the userId in posts references users.id. When you initialize your database with { users, posts }, @hedystia/db automatically registers this relation "both ways". posts belongs to users, and users has many posts.

Cascade Deletion

You can pass an optional object to references() to define the behavior upon deletion or updates:

• { onDelete: "CASCADE" }: If the parent user is deleted, all their posts are heavily deleted.
• { onDelete: "SET NULL" }: If the parent user is deleted, the post's userId column is set to null.

Eager Loading (The with Query)

Once relationships are established, eager loading them is as simple as adding the with configuration inside any find() or findFirst() operations.

// Eagerly load all the posts associated with Alice
const alice = await db.users.findFirst({
  where: { name: "Alice" },
  with: { posts: true },
});

console.log(alice.posts); // Array of post objects!

TIP

Always ensure you are using Object Schemas schemas: { users, posts } so that relations are eagerly loaded with the exact alias key you expect ("posts").

Nested Relationship Loading

You can even provide query options into the relationship objects to filter, restrict, or sort your eager-loaded relational joins!

const latestFeed = await db.users.find({
  with: {
    posts: {
      take: 5,               // Eagerly load only the 5 most recent posts per user
      orderBy: { id: "desc" }
    }
  }
});

Reverse Relationships

When posts registers a reference to users.id, @hedystia/db automatically registers a reverse mapping. This allows you to eagerly load the users object from a query on posts:

const blogPost = await db.posts.findFirst({
  where: { id: 1 },
  // Eager load the user who authored this post
  with: { users: true },
});

console.log(blogPost.users); // Returns the parent user object