Relation Definition

By defining relations between multiple sheets, you can retrieve related data with include and filter using relation conditions in where.

Example Sheets

The following sheets are used as examples throughout the relation documentation.

Users Sheet

id	name	email
1	Alice	[email protected]
2	Bob	[email protected]
3	Charlie	[email protected]

Posts Sheet

id	title	authorId	published
1	First Post	1	true
2	How to use GAS	1	true
3	Draft Article	2	false

Profiles Sheet

id	userId	bio
1	1	I'm an engineer
2	2	I'm a designer

Tags Sheet

id	name
1	GAS
2	JavaScript

PostTags Sheet (Junction Table)

postId	tagId
1	1
1	2
2	1

Basic Definition

When using the CLI, relations are written in schema.prisma with @relation. Running npx gassma generate injects them into the generated client.

model Users {
  id    Int     @id
  name  String
  posts Posts[]
}

model Posts {
  id       Int    @id
  title    String
  authorId Int

  author Users @relation(fields: [authorId], references: [id])
}

When using the GAS editor alone, pass a relations option to the GassmaClient constructor.

const gassma = new Gassma.GassmaClient({
  relations: {
    // Sheet name (must match the actual sheet name in the spreadsheet)
    Users: {
      // Relation name (any name you choose; becomes the key used in include and where)
      posts: {
        type: "oneToMany",
        to: "Posts",
        field: "id",
        reference: "authorId",
      },
    },
  },
});

When specifying a spreadsheet ID, pass id along with it:

const gassma = new Gassma.GassmaClient({
  id: "XXXXXXXXXXXXXXXXXXX",
  relations: {
    // ...
  },
});

The rest of this page uses the constructor form to describe what each relation type means. For how to write relations in the schema, see Schema.

caution

You can pass relations to the constructor only when using new Gassma.GassmaClient() directly. The GassmaClient generated by the CLI overrides it with the relations read from the schema, so passing it to the constructor is silently ignored (see Constructor Options).

Relation Definition Keys

Key	Description	Optional	Notes
type	Type of relation	Required	oneToMany / oneToOne / manyToOne / manyToMany
to	Target sheet name	Required
field	Column name on the source sheet	Required	FK or PK
reference	Column name on the target sheet	Required
through	Junction table settings	Optional	Required for manyToMany
onDelete	Action on delete	Optional	Cascade / SetNull / Restrict / NoAction
onUpdate	Action on PK update	Optional	Cascade / SetNull / Restrict / NoAction

Relation Types

oneToMany (One-to-Many)

A relationship where one parent record has multiple child records.

Example: One user has multiple posts

relations: {
  Users: {
    posts: {
      type: "oneToMany",
      to: "Posts",
      field: "id",          // Users PK
      reference: "authorId", // Posts FK
    },
  },
}

Returns an array when retrieved with include.

manyToOne (Many-to-One)

The reverse direction of oneToMany. Defines a reference from child records to the parent record.

Example: Retrieve the author (user) from a post

relations: {
  Posts: {
    author: {
      type: "manyToOne",
      to: "Users",
      field: "authorId",  // Posts FK
      reference: "id",    // Users PK
    },
  },
}

Returns a single object or null when retrieved with include.

manyToOne is the definition for the side holding the FK (the side that declares @relation(fields: ...) in a Prisma schema). Even in a one-to-one relationship, the FK-holding side is defined as manyToOne.

oneToOne (One-to-One)

A relationship where one record is linked to exactly one other record. oneToOne is reserved exclusively for the side of a one-to-one relationship that does not hold the FK.

Example: User and profile (the FK userId is held by the Profiles side)

relations: {
  Users: {
    profile: {
      type: "oneToOne",
      to: "Profiles",
      field: "id",       // Users PK
      reference: "userId", // Profiles FK
    },
  },
}

Returns a single object or null when retrieved with include. An error is thrown if multiple records have the same reference value.

note

When defining the reverse relation from the FK-holding side (Profiles in the example above), use manyToOne even for a one-to-one relationship.

relations: {
  Profiles: {
    user: {
      type: "manyToOne",
      to: "Users",
      field: "userId",  // Profiles FK
      reference: "id",  // Users PK
    },
  },
}

manyToMany (Many-to-Many)

Defines a many-to-many relationship through a junction table.

Example: Posts and tags

relations: {
  Posts: {
    tags: {
      type: "manyToMany",
      to: "Tags",
      field: "id",          // Posts PK
      reference: "id",      // Tags PK
      through: {
        sheet: "PostTags",    // Junction table sheet name
        field: "postId",      // FK for Posts in the junction table
        reference: "tagId",   // FK for Tags in the junction table
      },
    },
  },
}

Returns an array when retrieved with include.

Defining Multiple Relations

You can define multiple relations for a single sheet and across multiple sheets.

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

Validation

If there are errors in the relation definition, an error is thrown when creating the GassmaClient instance.

Error	Cause
RelationSheetNotFoundError	Sheet name specified in relations key, to, or through.sheet does not exist
RelationMissingPropertyError	type / to / field / reference is missing. through is missing for manyToMany
RelationInvalidPropertyTypeError	Property type is not string
RelationInvalidTypeError	type is not one of the 4 types
RelationInvalidOnDeleteError	onDelete is not one of the 4 types
RelationInvalidOnUpdateError	onUpdate is not one of the 4 types
RelationColumnNotFoundError	Column specified in field / reference does not exist in the sheet