Inputting Data

[long sluice]

How can I create and run a function that will input data into two tables. one table has a foreign key relationship with the other table.

[cloud dawn]

Hi @long sluice. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

To create and run a function that inputs data into two tables with a foreign key relationship in Convex, you'll need to create a mutation function that handles both insertions while maintaining the relationship. Here's how to approach this:

Creating a Mutation Function for Related Tables

When you have two tables where one references the other via a foreign key, you typically want to:

1. Insert the parent record first
2. Get the ID of that record
3. Insert the child record with a reference to the parent's ID

Here's an example of how to implement this:

import { mutation } from "./_generated/server";
import { v } from "convex/values";

export const createRelatedRecords = mutation({
  args: {
    parentData: v.object({
      // Define fields for parent table
      name: v.string(),
      // other fields...
    }),
    childData: v.object({
      // Define fields for child table (excluding the foreign key)
      description: v.string(),
      // other fields...
    })
  },
  handler: async (ctx, args) => {
    // 1. Insert into parent table first
    const parentId = await ctx.db.insert("parentTable", args.parentData);
    
    // 2. Insert into child table with reference to parent
    const childId = await ctx.db.insert("childTable", {
      ...args.childData,
      parentId: parentId, // This is the foreign key reference
    });
    
    // 3. Return both IDs if needed
    return { parentId, childId };
  },
});

Running Your Function

You can run this function from your client code using the Convex client:

// From your client code
const { parentId, childId } = await convex.mutation("path/to/createRelatedRecords", {
  parentData: {
    name: "Parent Record Name",
    // other fields...
  },
  childData: {
    description: "Child Record Description",
    // other fields...
  }
});

Schema Definition Example

For context, here's how you might define the schema for these related tables:

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  parentTable: defineTable({
    name: v.string(),
    // other fields...
  }),
  childTable: defineTable({
    description: v.string(),
    parentId: v.id("parentTable"), // Foreign key reference
    // other fields...
  }).index("by_parentId", ["parentId"]), // Index for efficient lookups
});

This approach ensures that your data is inserted atomically within a single transaction, maintaining referential integrity between your tables.

For more information on structuring relationships in Convex, check out Relationship Structures: Let's Talk About Schemas.

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:

@long sluice Kapa.ai is still learning and improving, please let me know how I did by reacting below.