Nested Write (create)

Used to simultaneously create and associate records in relation targets within the create method.

Requires a prior relation definition.

Example Sheets

Uses the sheet examples from relation definition.

Available Operations

Operation	Description
create	Create a new related record and associate it
createMany	Create multiple new related records and associate them
connect	Associate an existing related record
connectOrCreate	Associate if existing record found, otherwise create and associate

Compatibility by Relation Type

Operation	manyToOne	oneToOne	oneToMany	manyToMany
create	Single only	Single only	Single/Array	Single/Array
createMany	-	-	Supported	-
connect	Supported	Supported	Single/Array	Single/Array
connectOrCreate	Supported	Supported	Single/Array	Single/Array

note

The behavior of to-one relations differs depending on where the FK lives.

• manyToOne (FK-holding side): the value is set on the own record's FK
• oneToOne (non-FK side): the FK of the related record holding the FK is rewritten. The own record is not modified

oneToOne is reserved for the non-FK side of a one-to-one relationship (see relation definition).

Behavior of oneToOne (Non-FK Side)

Operation	Behavior	When the target record does not exist
create	Creates the related record with the FK automatically set	-
connect	Replaces (nulls the FK of the currently connected record, then sets the target record's FK to the parent)	NestedWriteConnectNotFoundError
connectOrCreate	Same replacement as connect if found, otherwise creates with the FK automatically set	-

Specifying createMany or array forms results in NestedWriteInvalidOperationError.

create

create with manyToOne

Example of creating a post while also creating the associated author. In manyToOne, this is used in reverse (creating the author from the post side).

const result = gassma.Posts.create({
  data: {
    id: 4,
    title: "New Article",
    published: true,
    author: {
      create: {
        id: 4,
        name: "Dave",
        email: "[email protected]",
      },
    },
  },
});

Executing the above performs the following:

1. Dave is created in the Users sheet
2. Dave's id (= 4) is automatically set as the Posts authorId
3. The new article is created in the Posts sheet

The return value has the following format:

{
  id: 4,
  title: "New Article",
  authorId: 4,
  published: true,
}

create with oneToOne

Create a profile simultaneously when creating a user. With oneToOne (non-FK side), the parent's value is automatically set as the FK of the related record:

const result = gassma.Users.create({
  data: {
    id: 4,
    name: "Dave",
    email: "[email protected]",
    profile: {
      create: { id: 3, bio: "I just joined" },
    },
  },
});

Executing the above performs the following:

1. Dave is created in the Users sheet
2. { id: 3, userId: 4, bio: "I just joined" } is created in the Profiles sheet (userId is automatically set to Dave's id = 4)

create with oneToMany

Create posts simultaneously when creating a user:

const result = gassma.Users.create({
  data: {
    id: 4,
    name: "Dave",
    email: "[email protected]",
    posts: {
      create: [
        { id: 4, title: "Dave's Article 1", published: true },
        { id: 5, title: "Dave's Article 2", published: false },
      ],
    },
  },
});

Executing the above performs the following:

1. Dave is created in the Users sheet
2. 2 articles are created in the Posts sheet (authorId is automatically set to Dave's id = 4)

You can also create a single record with an object instead of an array:

posts: {
  create: { id: 4, title: "Dave's Article", published: true },
}

create with manyToMany

Create tags simultaneously when creating a post, and associate them in the junction table:

const result = gassma.Posts.create({
  data: {
    id: 4,
    title: "New Article",
    authorId: 1,
    published: true,
    tags: {
      create: { id: 3, name: "TypeScript" },
    },
  },
});

Executing the above performs the following:

1. The new article is created in the Posts sheet
2. The "TypeScript" tag is created in the Tags sheet
3. { postId: 4, tagId: 3 } is created in the PostTags sheet

createMany

Bulk create multiple child records with oneToMany:

const result = gassma.Users.create({
  data: {
    id: 4,
    name: "Dave",
    email: "[email protected]",
    posts: {
      createMany: {
        data: [
          { id: 4, title: "Article 1", published: true },
          { id: 5, title: "Article 2", published: false },
        ],
      },
    },
  },
});

Dave's id is automatically set as authorId for each record.

connect

Associates existing records. Specify the target record with where conditions.

connect with manyToOne

Create a post linked to an existing user:

const result = gassma.Posts.create({
  data: {
    id: 4,
    title: "New Article",
    published: true,
    author: {
      connect: { name: "Alice" },
    },
  },
});

Executing the above performs the following:

1. Search for a record with name: "Alice" in the Users sheet
2. Alice's id (= 1) is automatically set as the Posts authorId
3. The new article is created in the Posts sheet

caution

If no record matching the condition is found, NestedWriteConnectNotFoundError is thrown.

connect with oneToOne

Create a user and simultaneously link an existing profile:

const result = gassma.Users.create({
  data: {
    id: 4,
    name: "Dave",
    email: "[email protected]",
    profile: {
      connect: { id: 1 },
    },
  },
});

The above updates the userId of id: 1 in the Profiles sheet to Dave's id (= 4).

connect on oneToOne behaves as a replacement. If a related record is already connected to the parent, its FK is set to null before the target record's FK is set to the parent.

caution

If no record matching the condition is found, NestedWriteConnectNotFoundError is thrown.

connect with oneToMany

Create a user and simultaneously link existing posts:

const result = gassma.Users.create({
  data: {
    id: 4,
    name: "Dave",
    email: "[email protected]",
    posts: {
      connect: [
        { title: "Draft Article" },
      ],
    },
  },
});

The above updates the authorId of "Draft Article" in the Posts sheet to Dave's id (= 4).

connect with manyToMany

Associate existing tags with a post:

const result = gassma.Posts.create({
  data: {
    id: 4,
    title: "New Article",
    authorId: 1,
    published: true,
    tags: {
      connect: [
        { name: "GAS" },
        { name: "JavaScript" },
      ],
    },
  },
});

Executing the above performs the following:

1. The new article is created in the Posts sheet
2. { postId: 4, tagId: 1 } and { postId: 4, tagId: 2 } are created in the PostTags sheet

The records in the Tags sheet are not modified.

connectOrCreate

Associates if an existing record is found, otherwise creates a new one and associates it:

const result = gassma.Posts.create({
  data: {
    id: 4,
    title: "New Article",
    published: true,
    author: {
      connectOrCreate: {
        where: { name: "Alice" },
        create: {
          id: 4,
          name: "Alice",
          email: "[email protected]",
        },
      },
    },
  },
});

In the above case, since Alice exists in the Users sheet, it behaves the same as connect. If she doesn't exist, a new record is created with the create data.

Likewise for oneToOne (non-FK side): if the record is found, it behaves as the same replacement as connect; if not found, the related record is created with the FK automatically set.

For oneToMany / manyToMany, you can specify multiple with an array:

tags: {
  connectOrCreate: [
    {
      where: { name: "GAS" },
      create: { id: 3, name: "GAS" },
    },
    {
      where: { name: "New Tag" },
      create: { id: 4, name: "New Tag" },
    },
  ],
}

Deep Nesting

Nested write is processed recursively, so you can create deep relation hierarchies at once.

For example, creating User → Posts → Tags at once:

const gassma = new Gassma.GassmaClient({
  relations: {
    Users: {
      posts: {
        type: "oneToMany",
        to: "Posts",
        field: "id",
        reference: "authorId",
      },
    },
    Posts: {
      tags: {
        type: "manyToMany",
        to: "Tags",
        field: "id",
        reference: "id",
        through: {
          sheet: "PostTags",
          field: "postId",
          reference: "tagId",
        },
      },
    },
  },
});

const result = gassma.Users.create({
  data: {
    id: 4,
    name: "Dave",
    email: "[email protected]",
    posts: {
      create: {
        id: 4,
        title: "Dave's Article",
        published: true,
        tags: {
          create: { id: 3, name: "TypeScript" },
        },
      },
    },
  },
});

The above is processed in the following order:

1. Dave is created in the Users sheet
2. The article is created in the Posts sheet (authorId: 4 is automatically set)
3. "TypeScript" is created in the Tags sheet
4. A relation row is created in the PostTags sheet

Notes

• Nested write is only available in the create method. It cannot be used in createMany / updateMany, etc.
• FK is automatically set, but PK (id, etc.) must be explicitly specified. There is no auto-increment feature.
• If no record matching the where condition in connect is found, NestedWriteConnectNotFoundError is thrown.
• A nested write writes to multiple sheets, but if it fails partway through, not a single row is written to any of them. See Write Atomicity and Concurrency for details.

Validation

Error	Cause
NestedWriteWithoutRelationsError	Used nested write syntax without relation definitions
NestedWriteConnectNotFoundError	Record not found with connect / connectOrCreate where condition
NestedWriteInvalidOperationError	Specified an operation not supported for the relation type (e.g., createMany or array forms on oneToOne)