# GASsma Documentation (Full Content)
A machine-readable version of the entire GASsma documentation bundled into a single file.
Index: https://gassma.io/en/llms.txt
---
# What is GASsma (https://gassma.io/en/docs/intro)
Overview and motivation: why plain GAS spreadsheet code is hard to maintain (getRange counting mistakes, formula injection) and how GASsma solves it
GASsma is a library that lets you manipulate Google Spreadsheet tables like "Prisma", one of the popular ORM libraries for Node.js.
By operating spreadsheets like an ORM, it aims to make writing GoogleAppsScript (GAS):
- More manageable
- Less error-prone
- More secure
## Getting Started
Create a new spreadsheet and enter the following data. (Name the sheet "sheet1")
Then open `Extensions` > `Apps Script` to launch the GAS editor.
Once open, follow [this page](./installation) to install GASsma.
Now let's consider how to extract and format data from the sheet we just created:
1. Extract rows where age is 25 or older
2. Sort the extracted rows in ascending order by name
3. Convert to an associative array keyed by column names
Let's write this with GASsma. Write the following code and run `myFunction`:
```ts
const gassma = new Gassma.GassmaClient();
function myFunction() {
const result = gassma.sheet1.findMany({
where: {
age: {
gte: 25,
},
},
orderBy: {
name: "asc",
},
});
console.log(result);
}
```
That's it.
**Simply create an instance and call the findMany method to extract data.** The library automatically reads column names for you.
## Why is GASsma Needed
There are mainly 3 difficulties when manipulating spreadsheets with existing GAS:
### 1. Complexity of Code Management
Writing the above example in standard GAS would look like this:
```ts
function myFunction() {
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const hogeSheet = sheet.getSheetByName("sheet1");
const rowLength = hogeSheet.getLastRow() - 1;
if (rowLength === 0) {
console.log([]);
return;
}
// Extract data from specified range
const data = hogeSheet.getRange(2, 1, rowLength, 4).getValues();
// Filter rows where age is 25 or older
const gte25Data = data.filter((row) => row[1] >= 25);
// Sort
const gte25DataSorted = gte25Data.sort((a, b) => (a[0] >= b[0] ? 1 : -1));
// Convert to associative array
const gte25DataSortedDict = gte25DataSorted.map((row) => {
return {
name: row[0],
age: row[1],
pref: row[2],
postNumber: row[3],
};
});
console.log(gte25DataSortedDict);
}
```
However, this is still relatively short code with simple logic.
If additional conditions like:
**"If names are the same, sort by age in ascending order"**
keep increasing, or if there are complex requirements like:
**"Find the average age of people aged 25-60 whose prefecture is Tokyo"**
the code becomes complex and difficult to manage.
### 2. Error-Prone Nature of Code
GAS spreadsheet operations are inherently error-prone. When extracting data from a specified range in a spreadsheet, you use `getRange()`. `getRange()` is a method that extracts cells from a specified range by taking row and column numbers as arguments.
In other words, every time you use `getRange()`, you need to check the row and column numbers of cells on the spreadsheet. This means there's a risk of miscounting. The more you use `getRange()` in your code, the higher this risk becomes.
### 3. Security Awareness Required
Consider inserting data submitted from a Google Form into a spreadsheet. For example, the following code has a problem. Can you spot it?
```ts
function myFunction(e) {
// Get values submitted from Google Form
const values = e.namedValues;
const newValues = [values["名前"], values["年齢"], values["都道府県"], values["郵便番号"]];
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const hogeSheet = sheet.getSheetByName("シート名");
const newRow = hogeSheet.getLastRow() + 1;
// Insert into sheet
hogeSheet.getRange(newRow, 1, 1, 4).setValues([newValues]);
}
```
The answer is: if a malicious user enters a spreadsheet formula like `=C1` in the form response field, arbitrary unauthorized operations can be executed. (Formula Injection)
The library that solves these problems is **"GASsma"**.
Note that formula injection is prevented by automatic escaping at write time. If you intentionally want to write a formula, see [raw](/docs/reference/raw).
# Installation (https://gassma.io/en/docs/installation)
How to install GASsma in the GAS script editor or via npm for local development
First, after opening Apps Script, click the "+" button in the Libraries section.
A dialog like the one below will appear. Enter the following in the "Script ID" field and press the search button.
```
1ZVuWMUYs4hVKDCcP3nVw74AY48VqLm50wRceKIQLFKL0wf4Hyou-FIBH
```
The following screen will appear. Press the "Add" button.
If "Gassma" appears in the Libraries section, you're all set!
## CLI Tool Installation
When developing GoogleAppsScript locally using tools like clasp, you can install GASsma's TypeScript type file auto-generation tool with the following command.
For detailed usage, see [here](./reference/type-generation)
```bash
npm i gassma
```
# Basic (https://gassma.io/en/docs/reference/basic)
Initializing GassmaClient, accessing sheets, constructor options, and the common query options (where, select, omit, orderBy, take, skip)
## Creating an Instance
If you have created a GAS on a specific spreadsheet and want to work with that spreadsheet, you can create an instance as follows:
```ts
const gassma = new Gassma.GassmaClient();
```
Alternatively, if you have created a GAS in a location other than a spreadsheet, or if you want to work with a spreadsheet located elsewhere, you can create an instance by passing the target spreadsheet's ID as an argument:
```ts
const gassma = new Gassma.GassmaClient("XXXXXXXXXXXXXXXXXXX");
```
### Initialization with an Options Object
When you need advanced configuration such as relation definitions or global omit, pass an options object:
```ts
const gassma = new Gassma.GassmaClient({
id: "XXXXXXXXXXXXXXXXXXX", // Optional
relations: {
// Relation definitions (see the relation definition reference for details)
},
omit: {
// Global omit settings (see the global omit reference for details)
Users: { password: true },
},
});
```
| Option | Description | Reference |
| --- | --- | --- |
| `id` | Spreadsheet ID (uses the active spreadsheet when omitted) | - |
| `relations` | Relation definitions | [Relation Definition](/docs/reference/relation/definition) |
| `omit` | Global omit settings | [Global omit](/docs/reference/config/global-omit) |
| `defaults` | Default values for fields | [defaults](/docs/reference/config/defaults) |
| `updatedAt` | Auto-update timestamps | [updatedAt](/docs/reference/config/updated-at) |
| `ignore` | Field-level exclusion | [ignore](/docs/reference/config/ignore) |
| `ignoreSheets` | Sheet-level exclusion | [ignore](/docs/reference/config/ignore) |
| `map` | Field name mapping | [map](/docs/reference/config/map) |
| `mapSheets` | Sheet name mapping | [map](/docs/reference/config/map) |
| `autoincrement` | Auto-increment | [autoincrement](/docs/reference/config/autoincrement) |
| `strictUndefinedChecks` | Turns explicit `undefined` in query inputs into runtime errors | [strictUndefinedChecks / Gassma.skip](/docs/reference/config/strict-undefined-checks) |
## Checking Date Values
GASsma runs as a GAS library in a script context separate from the calling script. As a result, checking a `Date` value returned by GASsma with `instanceof Date` evaluates to `false`. Use `Object.prototype.toString` instead:
```ts
const user = gassma.Users.findFirst({ where: { id: 1 } });
user.createdAt instanceof Date;
// => false (a Date crossing the library boundary cannot be checked with instanceof)
Object.prototype.toString.call(user.createdAt) === "[object Date]";
// => true
```
This limitation applies to `instanceof` on **built-in types** such as `Date`. GASsma's exported error classes (e.g., `Gassma.GassmaMissingArgumentError`) are referenced via the `Gassma` namespace (the library's global), so they can be checked with `instanceof`. For details, see the [error list](/docs/reference/errors).
# create() (https://gassma.io/en/docs/reference/crud/create/create)
Create a single record; supports select/omit/include and nested writes
Used to add a new single row to the target sheet.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| data | Specifies the data to register | Required | |
| select | Display settings for return value columns | Optional | Cannot be used with `omit` / `include` |
| omit | Exclusion settings for return value columns | Optional | Cannot be used with `select` |
| include | Retrieve related records | Optional | [Details here](/docs/reference/relation/include) |
`data` is required. Omitting it throws `GassmaMissingArgumentError` (message: Argument `data` is missing.).
`data` values must be scalar values a cell can hold (string, number, boolean, `null`, `Date`). Passing an object other than `Date` — `Map` / `Set` / `RegExp` / a class instance / a wrapper object such as `new String("x")` — throws a `GassmaInvalidValueError`.
```ts
gassma.sheet1.create({ data: { name: new Map() } });
// => Invalid value for argument `name`. Expected a scalar value, but received a Map.
gassma.sheet1.create({ data: { name: new Point(1, 2) } });
// => Invalid value for argument `name`. Expected a scalar value, but received an object.
```
`Gassma.raw` (see [raw](/docs/reference/raw)) and `fields` (see [fields](/docs/reference/fields)) can be passed as-is when writing. For the other affected values, see the [error list](/docs/reference/errors#values-a-cell-cannot-hold).
## Example Sheet
## Description
Suppose you want to add the following row to the above example:
- name => **Shibata**
- age => **23**
- pref => **Shimane**
- postNumber => **690-8540**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.create
const result = gassma.sheet1.create({
data: {
name: "Shibata",
age: 23,
pref: "Shimane",
postNumber: "690-8540",
},
});
```
The return value has the following format:
```ts
{
name: 'Shibata',
age: 23,
pref: 'Shimane',
postNumber: '690-8540'
}
```
The data of the created row is returned.
Also, if you omit the age as follows, the `age` column of that row will be empty. Passing `undefined` as the value is treated the same as omission:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.create
gassma.sheet1.create({
data: {
name: "Shibata",
pref: "Shimane",
postNumber: "690-8540",
},
});
```
The return value has the following format:
```ts
{
name: 'Shibata',
age: null,
pref: 'Shimane',
postNumber: '690-8540'
}
```
## Nested Write
When relation definitions exist, you can describe operations to simultaneously create and associate records in relation targets within `data`.
For details, see the [Nested Write reference](/docs/reference/relation/nested-write).
# createMany() (https://gassma.io/en/docs/reference/crud/create/createMany)
Create multiple records at once and get the created count
Used to add multiple rows to the target sheet simultaneously.
## Available Keys
| Key | Description | Optional |
| --- | --- | --- |
| data | Specifies the data to register | Required |
`data` is required. Omitting it throws `GassmaMissingArgumentError` (message: Argument `data` is missing.).
## Example Sheet
## Description
Suppose you want to add the following rows to the above example:
- Row 1
- name => **Shibata**
- age => **23**
- pref => **Shimane**
- postNumber => **690-8540**
- Row 2
- name => **Suzuhara**
- age => **25**
- pref => **Tottori**
- postNumber => **680-8571**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.createMany
const result = gassma.sheet1.createMany({
data: [
{
name: "Shibata",
age: 23,
pref: "Shimane",
postNumber: "690-8540",
},
{
name: "Suzuhara",
age: 25,
pref: "Tottori",
postNumber: "680-8571",
},
],
});
```
The return value has the following format:
```ts
{
count: 1;
}
```
The number of created rows is returned.
`createMany` does not support [Nested Write](/docs/reference/relation/nested-write). Use `create` if you need to operate on related records simultaneously.
# createManyAndReturn() (https://gassma.io/en/docs/reference/crud/create/createManyAndReturn)
Create multiple records and return the created records
Used to add multiple rows to the target sheet simultaneously and retrieve all created records as an array.
Performs the same write operation as `createMany`, but differs in the return value.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| data | Specifies the data to register | Required | |
| select | Display settings for return value columns | Optional | Cannot be used with `omit` / `include` |
| omit | Exclusion settings for return value columns | Optional | Cannot be used with `select` |
| include | Retrieve related records | Optional | [Details here](/docs/reference/relation/include) |
`data` is required. Omitting it throws `GassmaMissingArgumentError` (message: Argument `data` is missing.).
## Example Sheet
## Description
Suppose you want to add the following rows to the above example:
- Row 1
- name => **Shibata**
- age => **23**
- pref => **Shimane**
- postNumber => **690-8540**
- Row 2
- name => **Suzuhara**
- age => **25**
- pref => **Tottori**
- postNumber => **680-8571**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.createManyAndReturn
const result = gassma.sheet1.createManyAndReturn({
data: [
{
name: "Shibata",
age: 23,
pref: "Shimane",
postNumber: "690-8540",
},
{
name: "Suzuhara",
age: 25,
pref: "Tottori",
postNumber: "680-8571",
},
],
});
```
The return value has the following format:
```ts
[
{ name: "Shibata", age: 23, pref: "Shimane", postNumber: "690-8540" },
{ name: "Suzuhara", age: 25, pref: "Tottori", postNumber: "680-8571" },
];
```
All created records are returned as an array.
## Differences from createMany
| Method | Return Value |
| --- | --- |
| `createMany` | `{ count: number }` |
| `createManyAndReturn` | Array of created records |
The behavior also differs when passing an empty array:
```ts
// createMany
gassma.sheet1.createMany({ data: [] });
// => { count: 0 }
// createManyAndReturn
gassma.sheet1.createManyAndReturn({ data: [] });
// => []
```
Fields not specified in data are returned as `null`:
```ts
const result = gassma.sheet1.createManyAndReturn({
data: [{ name: "Shibata" }],
});
// => [{ name: "Shibata", age: null, pref: null, postNumber: null }]
```
`createManyAndReturn` does not support [Nested Write](/docs/reference/relation/nested-write). Use `create` if you need to operate on related records simultaneously.
# findMany() (https://gassma.io/en/docs/reference/crud/read/findMany)
Get multiple records with where, orderBy, take/skip, cursor pagination, and distinct
Used to retrieve all rows matching specific conditions.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| where | Specifies query conditions | Optional | Retrieves all rows if omitted or when `where: {}` is passed |
| select | Display settings for columns | Optional | Cannot be used with `omit` / `include`. Supports relation field options |
| omit | Exclusion settings for columns | Optional | Cannot be used with `select` |
| include | Retrieve related records | Optional | [Details here](/docs/reference/relation/include) |
| orderBy | Sort settings | Optional | Array can be omitted when specifying a single column |
| take | Limit number of records | Optional | Negative values fetch from the end |
| skip | Number of records to skip | Optional | Negative values cause an error |
| distinct | Deduplication settings | Optional | Array can be omitted when specifying a single column |
| cursor | Cursor position | Optional | Cursor-based pagination |
## Example Sheet
## Description
Suppose you want to retrieve rows from the above example with the following condition:
- pref => **Tokyo**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
pref: "Tokyo",
},
});
```
The return value has the following format:
```ts
[
{ name: "sato", age: 31, pref: "Tokyo", postNumber: "160-0023" },
{ name: "endo", age: 55, pref: "Tokyo", postNumber: "160-0023" },
];
```
To specify multiple conditions:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
pref: "Tokyo",
年齢: 31,
},
});
```
## Operators and Partial Matching
Conditional searches using greater than/less than and partial matching are also possible. For example, to retrieve rows with the following conditions:
- age => **20 or older**
- age => **30 or younger**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
age: {
gte: 20,
lte: 30,
},
},
});
```
The keys related to conditional searches are as follows:
| Key | Description | Example |
| --- | --- | --- |
| equals | Equal to | equals: 20 |
| not | Not equal to | not: 20 |
| in | Contained in the specified list | in: [20, 21, 22] |
| notIn | Not contained in the specified list | notIn: [23, 24, 25] |
| lt | Less than | lt: 30 |
| lte | Less than or equal to | lte: 30 |
| gt | Greater than | gt: 20 |
| gte | Greater than or equal to | gte: 20 |
| contains | Whether the target data contains the specified string | contains: "AB" |
| startsWith | Whether the target data starts with the specified string | startsWith: "AB" |
| endsWith | Whether the target data ends with the specified string | endsWith: "YZ" |
| mode | Case sensitivity settings | mode: "insensitive" |
In addition to fixed values, you can use the `fields` property to specify a value from another column in the same row. For details, see the [fields reference](/docs/reference/fields).
### mode: "insensitive"
By specifying `mode: "insensitive"` with `equals`, `not`, `contains`, `startsWith`, or `endsWith`, you can compare without case sensitivity.
```ts
const gassma = new Gassma.GassmaClient();
// Matches "alice", "Alice", "ALICE", etc.
const result = gassma.sheet1.findMany({
where: {
name: {
equals: "alice",
mode: "insensitive",
},
},
});
```
It can also be used with `contains`, `startsWith`, and `endsWith`:
```ts
// Matches "Hello World", "HELLO WORLD", etc.
const result = gassma.sheet1.findMany({
where: {
title: {
contains: "hello",
mode: "insensitive",
},
},
});
```
If `mode` is not specified or set to the default `mode: "default"`, case is distinguished.
`where` values cannot be `NaN` / `Infinity` / `-Infinity`, invalid Dates (Invalid Date), arrays (except the arrays of `in` / `notIn`), functions, Symbols, or BigInts. Passing one throws a `GassmaInvalidValueError` (the same applies to `cursor` / `having`). `Gassma.raw` cannot be used in `where` either (see [raw](/docs/reference/raw)).
Objects a cell cannot hold are rejected as well. Every object other than `Date` and `fields` (FieldRef) — `Map` / `Set` / `RegExp` / `Error` / class instances / wrapper objects such as `new String("x")` — throws a `GassmaInvalidValueError`.
```ts
gassma.sheet1.findMany({ where: { name: new Map() } });
// => Invalid value for argument `name`. Expected a scalar value, but received a Map.
gassma.sheet1.findMany({ where: { name: new Point(1, 2) } });
// => Invalid value for argument `name`. Expected a scalar value, but received an object.
```
Conditions whose value is `undefined` are treated as "not specified". For details, see [strictUndefinedChecks / Gassma.skip](/docs/reference/config/strict-undefined-checks).
## AND, OR, NOT
Searches with multiple conditions are also possible.
### AND
For example, to retrieve rows with the following conditions:
- age => **22**
- pref => **Ibaraki**
Using AND:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
AND: [
{
age: 22,
},
{
pref: "Ibaraki",
},
],
},
});
```
### OR
For example, to retrieve rows with the following condition:
- age => **22 or 40**
Using OR:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
OR: [
{
age: 22,
},
{
age: 40,
},
],
},
});
```
### NOT
For example, to retrieve rows with the following conditions:
- age => **not 22**
- age => **not 40**
Using NOT:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
NOT: [
{
age: 22,
},
{
age: 40,
},
],
},
});
```
### Nesting AND, OR, NOT
You can nest OR or NOT inside AND, for example. This nesting structure can be infinitely deep as long as the GAS call stack allows.
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
NOT: {
AND: [
{
name: "akahoshi",
},
{
age: 22,
},
],
},
},
});
```
### Empty AND, OR, NOT and Branches with No Conditions
An empty `AND` / `NOT` (`[]` or `{}`) is treated as **always true** (matches every row), and an empty `OR: []` is treated as **always false** (matches nothing) — same as Prisma.
Branches that generate no conditions at all (`{}`, or objects whose values are only empty objects / `undefined`) are removed from the `AND` / `OR` / `NOT` arrays. If the `OR` array becomes empty as a result, no rows match.
```ts
gassma.sheet1.findMany({ where: { NOT: {} } }); // => every row
gassma.sheet1.findMany({ where: { AND: [] } }); // => every row
gassma.sheet1.findMany({ where: { OR: [] } }); // => []
gassma.sheet1.findMany({ where: { OR: [{ age: {} }] } }); // => [] (the condition-less branch is removed, leaving an empty OR)
gassma.sheet1.findMany({ where: { OR: [{}, { name: "akahoshi" }] } }); // => only the rows where name is "akahoshi"
gassma.sheet1.findMany({ where: { NOT: [{ age: {} }] } }); // => every row
gassma.sheet1.findMany({ where: { AND: [{ OR: [] }] } }); // => every row
```
`OR` only accepts an array. Passing a non-array such as `OR: {}` throws a `GassmaInvalidValueError`.
### Relation Filters in where
When relation definitions exist, you can filter using conditions on related records within `where` (`some`, `every`, `none`, `is`, `isNot`).
For details, see the [where relation filter reference](/docs/reference/relation/where-relation-filter).
## Handling of null
Whether `null` is accepted depends on whether it sits in a value position or a structural position.
### `null` in a value position (valid)
Passing `null` as a column value is a legitimate specification and finds rows whose cell is empty.
```ts
gassma.sheet1.findMany({ where: { age: null } });
gassma.sheet1.findMany({ where: { age: { equals: null } } });
gassma.sheet1.findMany({ where: { age: { not: null } } });
```
Passing `null` to `is` / `isNot` on a to-one relation (manyToOne / oneToOne) is valid in the same way (see [where relation filters](/docs/reference/relation/where-relation-filter)). Column values in `having`, and column values in `data` when writing, also accept `null`.
### `null` in a structural position (error)
Passing `null` to an argument that expects an object or an array throws a `GassmaInvalidValueError`.
```ts
gassma.sheet1.findMany({ where: null });
// => GassmaInvalidValueError:
// Invalid value for argument `where`. Expected an object, but received null.
gassma.sheet1.findMany({ where: { AND: null } });
// => Invalid value for argument `AND`. Expected an object or an array, but received null.
gassma.sheet1.findMany({ where: { name: { contains: null } } });
// => Invalid value for argument `contains`. Expected a string, but received null.
```
This covers top-level arguments (`where` / `orderBy` / `cursor` / `distinct` / `by` / `having` / `data` / `create` / `update`), `AND` / `OR` / `NOT`, to-many relation filters (`some` / `every` / `none`), nested write verbs (`create` / `connect` / `connectOrCreate` / `set` / `disconnect` / `delete` / `update` / `deleteMany` / `updateMany` / `createMany`), and the string and number operators (`contains` / `startsWith` / `endsWith` / `gt` / `gte` / `lt` / `lte` / `increment` / `decrement` / `multiply` / `divide`). Putting `null` in an array element raises the same error.
For the `{expected}` wording of each argument, see the [error list](/docs/reference/errors#null-where-an-argument-expects-a-structure).
`cursor` is the one exception: **its column values cannot be `null` either**. Because `cursor` identifies a single record, a `null` value throws a `GassmaInvalidValueError` (Invalid value for argument \`id\`. Expected a scalar value, but received null.). Here `{argumentName}` is the column name.
A `null` written directly under `select` / `include` / `omit` is still ignored (that field is treated as not specified).
## select
You can limit the data returned in the response.
For example, to retrieve only `age` and `pref`:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
age: {
gte: 20,
},
},
select: {
name: true,
pref: true,
},
});
```
The return value would be:
```ts
[
{ name: "akahoshi", pref: "Ibaraki" },
{ name: "sato", pref: "Tokyo" },
{ name: "suzuki", pref: "Osaka" },
{ name: "yamamoto", pref: "Aichi" },
{ name: "ono", pref: "Shiga" },
{ name: "kudo", pref: "Kyoto" },
{ name: "kondo", pref: "Tottori" },
{ name: "endo", pref: "Tokyo" },
{ name: "murakami", pref: "Fukuoka" },
];
```
A `select` with no selected fields at all, such as `select: {}`, throws a `GassmaInvalidValueError` (Invalid value for argument `select`. Expected at least one selected field.). The same applies when all keys become empty through `undefined`.
### Relation Options within select
When relation definitions exist, you can specify options similar to `include` for relation fields within `select`. Instead of specifying `include` separately, you can control related data retrieval within `select`.
```ts
const gassma = new Gassma.GassmaClient({
relations: {
Users: {
posts: { type: "oneToMany", to: "Posts", field: "id", reference: "authorId" },
},
},
});
const result = gassma.Users.findMany({
select: {
id: true,
name: true,
posts: {
select: { id: true, title: true },
where: { published: true },
orderBy: { id: "desc" },
},
_count: true,
},
});
```
The options available for relation fields are the same as [include options](/docs/reference/relation/include) (`select`, `where`, `orderBy`, `include`, `omit`, `take`, `skip`).
Deep nesting is also supported:
```ts
const result = gassma.Users.findMany({
select: {
id: true,
posts: {
select: {
id: true,
comments: {
select: { id: true, text: true },
},
},
},
},
});
```
Relation fields can also be specified with `true`, which retrieves all scalar columns of the related model (this works at any depth, just like the top-level `select`):
```ts
const result = gassma.Users.findMany({
select: {
posts: {
select: {
title: true,
comments: true, // retrieves all scalar columns of comments
},
},
},
});
```
Top-level `select` and `include` cannot be used simultaneously. If you need related data, specify relation options within `select` or use `include` alone.
## orderBy
You can sort the retrieved rows.
For example, to sort by `age` in ascending order:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
age: {
gte: 20,
},
},
orderBy: {
age: "asc",
},
});
```
The available values are:
| Key | Meaning |
| --- | --- |
| asc | Ascending order |
| desc | Descending order |
### Null Value Sort Order Control
You can control the position of null values by specifying the `nulls` option in object format:
```ts
const gassma = new Gassma.GassmaClient();
// Place null values at the end
const result = gassma.sheet1.findMany({
orderBy: {
age: { sort: "asc", nulls: "last" },
},
});
// => [20, 22, 31, 40, 55, null, null]
```
| nulls value | Behavior |
| --- | --- |
| `"first"` | Place null values at the beginning |
| `"last"` | Place null values at the end |
When `nulls` is not specified, null values are placed at the beginning for `asc` and at the end for `desc`.
`NaN` and invalid Dates (Invalid Date) are also treated as "missing values" like null, and are placed in the same position as null (they are also subject to the `nulls` option).
You can also specify multiple sort conditions. For example, to:
- Sort by `age` in ascending order
- If `age` values are the same, sort those rows by `name` in ascending order
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
age: {
gte: 20,
},
},
orderBy: [{ age: "asc" }, { name: "asc" }],
});
```
*Sort priority follows the order of index numbers (lower index = higher priority).
An empty `orderBy` such as `orderBy: {}` is ignored (no sorting is performed). If an entry in the array becomes empty after removing `undefined`, only that entry is ignored and the remaining entries are used for sorting.
### Sorting by Relation Fields
When relation definitions exist, you can sort by manyToOne / oneToOne relation target fields:
```ts
const gassma = new Gassma.GassmaClient({
relations: {
Posts: {
author: {
type: "manyToOne",
to: "Users",
field: "authorId",
reference: "id",
},
},
},
});
// Sort posts by author name in ascending order
const result = gassma.Posts.findMany({
orderBy: { author: { name: "asc" } },
});
```
Records with null FK are placed at the beginning for `asc` and at the end for `desc`.
Field sorting is not available for oneToMany / manyToMany relations. `RelationOrderByUnsupportedTypeError` will be thrown.
Passing a non-object to a relation name throws a `GassmaInvalidValueError`.
```ts
gassma.Posts.findMany({ orderBy: { author: new Date() } });
// => Invalid value for argument `author`. Expected a relation orderBy object.
```
To sort by a field of the related record, pass an object such as `orderBy: { author: { name: "asc" } }`.
### Sorting by _count
You can sort by the number of records in oneToMany / manyToMany relations:
```ts
// Sort users by number of posts in descending order
const result = gassma.Users.findMany({
orderBy: { posts: { _count: "desc" } },
});
```
It can also be combined with scalar sorting:
```ts
// Sort by post count descending → then by name ascending for ties
const result = gassma.Users.findMany({
orderBy: [
{ posts: { _count: "desc" } },
{ name: "asc" },
],
});
```
`_count` sorting is not available for manyToOne / oneToOne relations. `RelationOrderByCountUnsupportedTypeError` will be thrown.
## take
You can specify the number of records to retrieve. Records are taken from the top of the sheet.
For example, to get the top 2 rows from matching records:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
age: {
gte: 20,
},
},
take: 2,
});
```
### Negative take Values
Specifying a negative value for `take` retrieves N records from the end:
```ts
// Get the last 2 records matching the condition
const result = gassma.sheet1.findMany({
where: {
age: { gte: 20 },
},
take: -2,
});
```
When `take` is negative, the direction of `skip` is also reversed. `skip` becomes the number of records to exclude from the end:
```ts
// After excluding the last 1 record, get the remaining last 2 records
const result = gassma.sheet1.findMany({
take: -2,
skip: 1,
});
```
## skip
You can skip specific rows from the retrieved results.
For example, to skip the first matching row:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
age: {
gte: 20,
},
},
skip: 1,
});
```
Specifying a finite negative value for `skip` throws `GassmaSkipNegativeError`.
### Invalid take / skip values
Passing `NaN` / `Infinity` / `-Infinity` / `null` to `take` / `skip` throws a `GassmaInvalidValueError`.
```ts
gassma.sheet1.findMany({ take: NaN });
// => Invalid value for argument `take`. Expected a finite number, but received NaN.
gassma.sheet1.findMany({ skip: null });
// => Invalid value for argument `skip`. Expected a number, but received null.
```
| Value | Behaviour |
| --- | --- |
| `NaN` / `Infinity` / `-Infinity` | `GassmaInvalidValueError` (`Expected a finite number, but received ...`) |
| `null` | `GassmaInvalidValueError` (`Expected a number, but received null.`) |
| A finite negative number | `take` reads from the end; `skip` throws `GassmaSkipNegativeError` |
| `undefined` | Treated as "not specified" and ignored |
`skip: -Infinity` used to throw `GassmaSkipNegativeError`, but the finiteness check now runs first, so it throws `GassmaInvalidValueError`. `GassmaSkipNegativeError` is thrown only for **finite** negative numbers.
The same validation applies to `take` / `skip` in `count` / `aggregate` / `groupBy`. The `take` of `findFirst` has [a different restriction](./findFirst#take).
## cursor
Enables cursor-based pagination. Specify an object that uniquely identifies a record in `cursor` to use that record as the starting point:
```ts
const gassma = new Gassma.GassmaClient();
// Starting from the record with id: 3, retrieve 5 records
const result = gassma.sheet1.findMany({
cursor: { id: 3 },
take: 5,
});
```
When `take` is positive, records are retrieved toward the end from the cursor position. When `take` is negative, records from the beginning up to the cursor position are retrieved:
```ts
// Starting from id: 3, retrieve records toward the beginning
const result = gassma.sheet1.findMany({
cursor: { id: 3 },
take: -5,
});
```
Combined with `skip`, you can skip further from the cursor position:
```ts
// Starting from id: 3, skip 1 record and retrieve 5 records
const result = gassma.sheet1.findMany({
cursor: { id: 3 },
skip: 1,
take: 5,
});
```
If the record specified in cursor is not found, an empty array is returned.
A `cursor` with no columns at all, such as `cursor: {}`, throws a `GassmaInvalidValueError` (Invalid value for argument `cursor`. Expected at least one column.). The same applies when all keys become empty through `undefined`. Passing an incomparable value such as `NaN` or an invalid Date (Invalid Date) as a `cursor` value also throws a `GassmaInvalidValueError`.
### Processing Order
The execution order when combining `where`, `orderBy`, `cursor`, `distinct`, `skip`, and `take`:
1. `where` - Filter
2. `orderBy` - Sort
3. Reverse the order when `take` is negative
4. `cursor` - Slice at cursor position (inclusive of the cursor itself)
5. `distinct` - Deduplication
6. `skip` - Skip
7. `take` - Limit (when negative, takes the absolute number of records, then restores the order to normal at the end)
8. `select` / `omit` - Field shaping
`distinct` is applied **after** `cursor`. Duplicates are removed within the range sliced by the cursor.
## omit
You can exclude specific columns from the return value. This is the inverse of `select`.
For example, to exclude `postNumber`:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
pref: "Tokyo",
},
omit: {
postNumber: true,
},
});
```
The return value would be:
```ts
[
{ name: "sato", age: 31, pref: "Tokyo" },
{ name: "endo", age: 55, pref: "Tokyo" },
];
```
`select` and `omit` cannot be used simultaneously. Specifying both throws `GassmaFindSelectOmitConflictError`.
When [global omit](/docs/reference/config/global-omit) is configured, you can override it with `{ field: false }` in the query's `omit`. For details, see [overriding global omit with query omit](/docs/reference/config/global-omit#overriding-global-omit-with-query-omit).
## distinct
Specify column names to remove rows with duplicate values. When duplicates exist, data from the upper row takes priority.
For example, to remove `age` duplicates:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findMany
const result = gassma.sheet1.findMany({
where: {
age: {
gte: 20,
},
},
distinct: ["age"],
});
```
Because `distinct` is applied after `cursor`, duplicates are removed within the range sliced by the cursor (see "Processing Order" above).
When `take` is negative, deduplication runs on the reversed order, so the remaining "first occurrence" is the record on the tail side. The final output is restored to normal order.
Duplicates are detected using normalized keys.
- Dates with the same time are considered the same value even if they are different instances. A Date and an ISO string of the same time are different values.
- The number `1` and the string `"1"` are different values.
- `NaN` values collapse into one, and invalid Dates (Invalid Date) collapse into one. `NaN`, `null`, and Invalid Date are all distinct from each other.
## include
When relation definitions exist, you can retrieve related data together.
For details, see the [include reference](/docs/reference/relation/include).
# findFirst() (https://gassma.io/en/docs/reference/crud/read/findFirst)
Get the first record matching a where filter, or null if none matches
Used to retrieve the first row matching specific conditions.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| where | Specifies query conditions | Optional | Retrieves all rows if omitted |
| select | Display settings for columns | Optional | Cannot be used with `omit` / `include`. Supports relation field options |
| omit | Exclusion settings for columns | Optional | Cannot be used with `select` |
| include | Retrieve related records | Optional | [Details here](/docs/reference/relation/include) |
| orderBy | Sort settings | Optional | Array can be omitted when specifying a single column |
| take | Limit number of records | Optional | Only `1` or `-1` can be specified. See below |
| skip | Number of records to skip | Optional | Negative values cause an error |
| distinct | Deduplication settings | Optional | Array can be omitted when specifying a single column |
| cursor | Cursor-based pagination | Optional | See [findMany cursor](/docs/reference/crud/read/findMany#cursor) for details |
## Example Sheet
## Description
Suppose you want to retrieve a row from the above example with the following condition:
- age => **20 or older**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findFirst
const result = gassma.sheet1.findFirst({
where: {
age: {
gte: 20,
},
},
});
```
The return value has the following format:
```ts
{
name: 'akahoshi',
age: 22,
pref: 'Ibaraki',
postNumber: '310-8555'
}
```
## take
For `findFirst`, `take` can only be **`1` or `-1`**. Specifying any other value throws `GassmaFindFirstTakeError`.
- `1`: Retrieves the first record in the current order (same behavior as when omitted).
- `-1`: Reverses the order and then retrieves the first record, i.e., the record at the end.
```ts
// Retrieve the record at the end (highest age) after sorting age ascending
const result = gassma.sheet1.findFirst({
orderBy: { age: "asc" },
take: -1,
});
```
Specifying anything other than `1` / `-1` throws `GassmaFindFirstTakeError`. Unlike `findMany`'s `take`, you cannot specify a number of records. `NaN` / `Infinity` / `-Infinity` are also values other than `1` / `-1`, so they throw `GassmaFindFirstTakeError` too (a different error class from `findMany`'s `take`).
Only `take: null` throws a `GassmaInvalidValueError` (Invalid value for argument \`take\`. Expected a number, but received null.).
## skip
Retrieves the first record after skipping `skip` records from the beginning. If no records remain after skipping, `null` is returned.
```ts
// From matching rows, skip the first 2 and retrieve the next 1
const result = gassma.sheet1.findFirst({
where: { age: { gte: 20 } },
skip: 2,
});
```
Specifying a finite negative value for `skip` throws `GassmaSkipNegativeError`. `NaN` / `Infinity` / `-Infinity` / `null` throw `GassmaInvalidValueError` instead (see [Invalid take / skip values in findMany](./findMany#invalid-take--skip-values)).
## distinct
Retrieves the first record after excluding rows with duplicate values in the specified columns. Usage is the same as [findMany's distinct](./findMany#distinct).
## Processing Order
`findFirst` is processed in the following order, ultimately returning the first record (or `null` if none matches):
1. `where` - Filter
2. `orderBy` - Sort
3. `take` - Reverses the order when `-1`
4. `cursor` - Slice at cursor position (inclusive of the cursor itself)
5. `distinct` - Deduplication
6. `skip` - Skip the specified number of records
7. Take the first record
8. `select` / `omit` - Field shaping
For key options and other specifications, see [findMany()](./findMany).
# findFirstOrThrow() (https://gassma.io/en/docs/reference/crud/read/findFirstOrThrow)
Like findFirst but throws NotFoundError when no record matches
Used to retrieve the first row matching specific conditions. Works the same as `findFirst`, but throws an error instead of returning `null` when no record is found.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| where | Specifies query conditions | Optional | Retrieves all rows if omitted |
| select | Display settings for columns | Optional | Cannot be used with `omit` / `include` |
| omit | Exclusion settings for columns | Optional | Cannot be used with `select` |
| include | Retrieve related records | Optional | [Details here](/docs/reference/relation/include) |
| orderBy | Sort settings | Optional | Array can be omitted when specifying a single column |
| take | Limit number of records | Optional | |
| skip | Number of records to skip | Optional | |
| distinct | Deduplication settings | Optional | Array can be omitted when specifying a single column |
## Example Sheet
## Description
Suppose you want to retrieve a row from the above example with the following condition:
- age => **20 or older**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.findFirstOrThrow
const result = gassma.sheet1.findFirstOrThrow({
where: {
age: {
gte: 20,
},
},
});
```
The return value has the following format:
```ts
{
name: 'akahoshi',
age: 22,
pref: 'Ibaraki',
postNumber: '310-8555'
}
```
## Differences from findFirst
The behavior differs when no record is found:
```ts
// findFirst → returns null
const result = gassma.sheet1.findFirst({
where: { name: "nonexistent" },
});
// => null
// findFirstOrThrow → throws NotFoundError
const result = gassma.sheet1.findFirstOrThrow({
where: { name: "nonexistent" },
});
// => NotFoundError: No record found
```
For other specifications, see [findMany()](./findMany).
# update() (https://gassma.io/en/docs/reference/crud/update/update)
Update a single record; supports atomic number operations (increment/decrement/multiply/divide) and nested writes
Updates the **first row** matching the specified conditions and retrieves the updated record. Returns `null` if no matching record is found.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| where | Specifies query conditions | Required | If multiple rows match, only the first row is updated |
| data | Data to update | Required | |
| select | Display settings for return value columns | Optional | Cannot be used with `omit` / `include` |
| omit | Exclusion settings for return value columns | Optional | Cannot be used with `select` |
| include | Retrieve related records | Optional | [Details here](/docs/reference/relation/include) |
`where` and `data` are required. Omitting either throws `GassmaMissingArgumentError` (e.g., Argument `where` is missing.). A `where` with no conditions at all (`where: {}`) throws `GassmaInvalidValueError` (Invalid value for argument `where`. Expected at least one condition.). The same applies when `where` becomes empty after removing `undefined` / `Gassma.skip`.
## Example Sheet
## Description
Suppose you want to perform the following operation on the above example:
- Set the age of the row where name is **akahoshi** to **23**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.update
const result = gassma.sheet1.update({
where: {
name: "akahoshi",
},
data: {
age: 23,
},
});
```
The return value has the following format:
```ts
{
name: 'akahoshi',
age: 23,
pref: 'Ibaraki',
postNumber: '310-8555'
}
```
The updated record is returned. Fields that were not updated retain their original values.
Fields whose `data` value is `undefined` are treated as "not specified" and are not updated.
```ts
const result = gassma.sheet1.update({
where: { name: "akahoshi" },
data: { name: undefined, age: 23 },
});
// => name keeps its original value; only age is updated to 23
```
If no matching record is found, `null` is returned:
```ts
const result = gassma.sheet1.update({
where: { name: "nonexistent" },
data: { age: 99 },
});
// => null
```
The `where` specification follows [findMany()](../read/findMany). However, unlike `findMany`, a `where` with no conditions at all (`where: {}`) throws an error (see the note above).
## Atomic Number Operations
By specifying `increment` / `decrement` / `multiply` / `divide` in `data`, you can perform operations on the current value:
```ts
// Increment age by 1
const result = gassma.sheet1.update({
where: { name: "akahoshi" },
data: {
age: { increment: 1 },
},
});
// age: 22 → 23
```
| Operation | Behavior | Example |
| --- | --- | --- |
| increment | Addition | `{ increment: 5 }` → current value + 5 |
| decrement | Subtraction | `{ decrement: 3 }` → current value - 3 |
| multiply | Multiplication | `{ multiply: 2 }` → current value × 2 |
| divide | Division | `{ divide: 4 }` → current value ÷ 4 |
If the current value is not a number, `0` is used as the base for calculations.
### The value passed to an operator
Passing `NaN` / `Infinity` / `-Infinity` as the argument of an operator such as `increment` throws a `GassmaInvalidValueError`. Here `{argumentName}` is the **operator key**.
```ts
gassma.sheet1.update({ where: { name: "akahoshi" }, data: { age: { increment: NaN } } });
// => Invalid value for argument `increment`. Expected a finite number, but received NaN.
```
### The result of the operation
If the **result** of the operation is `NaN` / `Infinity` / `-Infinity`, a `GassmaInvalidValueError` is thrown as well. Here `{argumentName}` is the **column name**, not the operator key.
```ts
gassma.sheet1.update({ where: { name: "akahoshi" }, data: { age: { divide: 0 } } });
// => Invalid value for argument `age`. Expected a finite number, but received Infinity.
```
If the current value is `0` and you specify `divide: 0`, the result is `0 / 0`, which is `NaN`.
```ts
// against a row whose age is 0
data: { age: { divide: 0 } };
// => Invalid value for argument `age`. Expected a finite number, but received NaN.
```
Overflow is covered too. If the result exceeds the representable range of a number it becomes `Infinity` / `-Infinity`, which is an error.
```ts
// against a row whose age is 20
data: { age: { multiply: 1e308 } };
// => Invalid value for argument `age`. Expected a finite number, but received Infinity.
```
If the result is a finite number, the update proceeds as before. When the error is thrown, no row is rewritten.
This validation runs in `update` / `updateMany` / `updateManyAndReturn`, in the update branch of `upsert`, and in the `update` of [Nested Write (update)](/docs/reference/relation/nested-write-update).
You can also combine it with regular value assignments:
```ts
const result = gassma.sheet1.update({
where: { name: "akahoshi" },
data: {
age: { increment: 1 },
pref: "Tokyo",
},
});
```
Number operations can only be used on **numeric columns**. Specifying `increment` and the like on a string column results in a type error. Columns made into a composite type that includes a number via `@gassma.addType` (e.g., `number | string`) are also eligible for number operations.
Number operations can be used not only in `update` but also in `updateMany` / `updateManyAndReturn`, the `update` of `upsert`, and the `data` of the `update` operation in [Nested Write (update)](/docs/reference/relation/nested-write-update).
## Nested Write
When relation definitions exist, you can describe operations on related records within `data`.
In addition to `create`'s Nested Write, `update` / `delete` / `deleteMany` / `disconnect` / `set` operations are available.
For details, see the [Nested Write (update) reference](/docs/reference/relation/nested-write-update).
# updateMany() (https://gassma.io/en/docs/reference/crud/update/updateMany)
Update all matching records and get the updated count; supports limit
Updates all rows matching the specified conditions to the given values.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| where | Specifies update conditions | Optional | Targets all rows if omitted |
| data | Data to update | Required | |
| limit | Maximum number of records to update | Optional | Negative values cause an error |
`data` is required. Omitting it throws `GassmaMissingArgumentError` (message: Argument `data` is missing.). `where` is optional; if omitted, all rows are targeted. The same applies to `where: {}` and to a `where` that became empty because its conditions were all `undefined`.
Fields whose `data` value is `undefined` are treated as "not specified" and are not updated.
## Example Sheet
## Description
Suppose you want to perform the following operation on the above example:
- age => **Change 20 to 21**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.updateMany
const result = gassma.sheet1.updateMany({
where: {
age: 20,
},
data: {
age: 21,
},
});
```
The return value has the following format:
```ts
{
count: 1;
}
```
The number of updated rows is returned.
The `where` specification follows [findMany()](../read/findMany).
## limit
You can specify the maximum number of records to update:
```ts
// Update at most 2 records
const result = gassma.sheet1.updateMany({
where: {
pref: "Tokyo",
},
data: {
age: 99,
},
limit: 2,
});
```
Specifying `limit: 0` results in 0 updates (nothing is updated).
Specifying a finite negative value for `limit` throws `GassmaLimitNegativeError`.
`NaN` / `Infinity` / `-Infinity` / `null` throw `GassmaInvalidValueError` instead. In that case no rows are updated at all.
```ts
gassma.sheet1.updateMany({ data: { age: 1 }, limit: NaN });
// => Invalid value for argument `limit`. Expected a finite number, but received NaN.
gassma.sheet1.updateMany({ data: { age: 1 }, limit: null });
// => Invalid value for argument `limit`. Expected a number, but received null.
```
`limit: -Infinity` used to throw `GassmaLimitNegativeError`, but the finiteness check now runs first, so it throws `GassmaInvalidValueError`. `undefined` is still ignored (no upper bound).
## Atomic Number Operations
By specifying `increment` / `decrement` / `multiply` / `divide` in `data`, you can perform operations on the current value:
```ts
// Increment everyone's age by 1
const result = gassma.sheet1.updateMany({
data: {
age: { increment: 1 },
},
});
```
| Operation | Behavior | Example |
| --- | --- | --- |
| increment | Addition | `{ increment: 5 }` → current value + 5 |
| decrement | Subtraction | `{ decrement: 3 }` → current value - 3 |
| multiply | Multiplication | `{ multiply: 2 }` → current value × 2 |
| divide | Division | `{ divide: 4 }` → current value ÷ 4 |
If the current value is not a number, `0` is used as the base for calculations. For details, see [update()](/docs/reference/crud/update/update).
# updateManyAndReturn() (https://gassma.io/en/docs/reference/crud/update/updateManyAndReturn)
Update all matching records and return the updated records
Updates all rows matching the specified conditions and retrieves the updated records as an array.
Performs the same update operation as `updateMany`, but differs in the return value.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| where | Specifies update conditions | Optional | Targets all rows if omitted |
| data | Data to update | Required | |
| limit | Maximum number of records to update | Optional | Negative values cause an error |
`data` is required. Omitting it throws `GassmaMissingArgumentError` (message: Argument `data` is missing.). `where` is optional; if omitted, all rows are targeted.
## Example Sheet
## Description
Suppose you want to perform the following operation on the above example:
- Set the age of rows where pref is **Tokyo** to **99**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.updateManyAndReturn
const result = gassma.sheet1.updateManyAndReturn({
where: {
pref: "Tokyo",
},
data: {
age: 99,
},
});
```
The return value has the following format:
```ts
[
{ name: "sato", age: 99, pref: "Tokyo", postNumber: "160-0023" },
{ name: "endo", age: 99, pref: "Tokyo", postNumber: "160-0023" },
];
```
All updated records are returned as an array. Fields that were not updated retain their original values.
## Differences from updateMany
| Method | Return Value |
| --- | --- |
| `updateMany` | `{ count: number }` |
| `updateManyAndReturn` | Array of updated records |
An empty array is returned if no matching records are found:
```ts
const result = gassma.sheet1.updateManyAndReturn({
where: { name: "nonexistent" },
data: { age: 99 },
});
// => []
```
Omitting `where` targets all rows and returns all records:
```ts
const result = gassma.sheet1.updateManyAndReturn({
data: { age: 99 },
});
// => All records returned with age: 99
```
The `where` specification follows [findMany()](../read/findMany).
## limit
You can specify the maximum number of records to update. For details, see [updateMany()](/docs/reference/crud/update/updateMany). `NaN` / `Infinity` / `-Infinity` / `null` are handled the same way as in `updateMany`.
## Atomic Number Operations
You can specify `increment` / `decrement` / `multiply` / `divide` in `data`. For details, see [update()](/docs/reference/crud/update/update).
# upsert() (https://gassma.io/en/docs/reference/crud/update/upsert)
Update a record if it exists, otherwise create it
Updates a record if it matches the specified conditions, or creates a new one if it doesn't exist. Returns the resulting record.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| where | Specifies search conditions | Required | |
| create | Data for creation when not found | Required | |
| update | Data for update when found | Required | |
| select | Display settings for return value columns | Optional | Cannot be used with `include` |
| omit | Exclusion settings for return value columns | Optional | Cannot be used with `select` |
| include | Retrieve related records | Optional | [Details here](/docs/reference/relation/include) |
`where` / `create` / `update` are all required. Omitting any of them throws `GassmaMissingArgumentError` (e.g., Argument `create` is missing.). A `where` with no conditions at all (`where: {}`) throws `GassmaInvalidValueError` (Invalid value for argument `where`. Expected at least one condition.). The same applies when `where` becomes empty after removing `undefined` / `Gassma.skip`.
## Example Sheet
## Description
Suppose you want to perform the following operation on the above example:
- Set the age of **akahoshi** to **23**
- Create a new record if it doesn't exist
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.upsert
const result = gassma.sheet1.upsert({
where: {
name: "akahoshi",
},
update: {
age: 23,
},
create: {
name: "akahoshi",
age: 23,
pref: "Ibaraki",
postNumber: "310-8555",
},
});
```
When the record exists, the updated record is returned:
```ts
{
name: 'akahoshi',
age: 23,
pref: 'Ibaraki',
postNumber: '310-8555'
}
```
When the record doesn't exist, it's created with the `create` data and the created record is returned:
```ts
const result = gassma.sheet1.upsert({
where: { name: "newuser" },
update: { age: 30 },
create: {
name: "newuser",
age: 30,
pref: "Tokyo",
postNumber: "100-0001",
},
});
// => { name: "newuser", age: 30, pref: "Tokyo", postNumber: "100-0001" }
```
## Nested Write
When relation definitions exist, Nested Write can be used within `create` / `update`:
- On `create`: Equivalent to [create's Nested Write](/docs/reference/relation/nested-write)
- On `update`: Equivalent to [update's Nested Write](/docs/reference/relation/nested-write-update)
The `where` specification follows [findMany()](../read/findMany). However, unlike `findMany`, a `where` with no conditions at all (`where: {}`) throws an error (see the note above).
# delete() (https://gassma.io/en/docs/reference/crud/delete/delete)
Delete a single record and return it
Deletes the **first row** matching the specified conditions and retrieves the deleted record. Returns `null` if no matching record is found.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| where | Specifies deletion conditions | Required | |
| select | Display settings for return value columns | Optional | Cannot be used with `include` |
| omit | Exclusion settings for return value columns | Optional | Cannot be used with `select` |
| include | Retrieve related records | Optional | [Details here](/docs/reference/relation/include) |
`where` is required. Omitting it throws `GassmaMissingArgumentError` (message: Argument `where` is missing.) and **never deletes all rows implicitly**. A `where` with no conditions at all (`where: {}`) throws `GassmaInvalidValueError` (Invalid value for argument `where`. Expected at least one condition.) and no row is deleted. The same applies when `where` becomes empty after removing `undefined` / `Gassma.skip`.
## Example Sheet
## Description
Suppose you want to perform the following operation on the above example:
- Delete the row where name is **akahoshi**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.delete
const result = gassma.sheet1.delete({
where: {
name: "akahoshi",
},
});
```
The return value has the following format:
```ts
{
name: 'akahoshi',
age: 22,
pref: 'Ibaraki',
postNumber: '310-8555'
}
```
The deleted record is returned.
If no matching record is found, `null` is returned:
```ts
const result = gassma.sheet1.delete({
where: { name: "nonexistent" },
});
// => null
```
Even if multiple records match the condition, **only the first one** is deleted.
## select / omit
You can control the fields in the return value:
```ts
const result = gassma.sheet1.delete({
where: { name: "akahoshi" },
select: { name: true, age: true },
});
// => { name: "akahoshi", age: 22 }
```
## onDelete
When `onDelete` is configured in relation definitions, the referential action is also executed on `delete`.
For details, see the [onDelete reference](/docs/reference/relation/on-delete).
The `where` specification follows [findMany()](../read/findMany). However, unlike `findMany`, a `where` with no conditions at all (`where: {}`) throws an error (see the caution above).
# deleteMany() (https://gassma.io/en/docs/reference/crud/delete/deleteMany)
Delete all matching records and get the deleted count; supports limit
Used to delete all rows matching the specified conditions.
## Available Keys
| Key | Description | Optional | Notes |
| --- | --- | --- | --- |
| where | Specifies deletion conditions | Optional | Targets all rows if omitted |
| limit | Maximum number of records to delete | Optional | Negative values cause an error |
`where: {}`, and a `where` that became empty because its conditions were only `undefined` / `Gassma.skip`, also target **every row for deletion**. To catch unintended `undefined` values, enable [strictUndefinedChecks](/docs/reference/config/strict-undefined-checks).
## Example Sheet
## Description
Suppose you want to perform the following operation on the above example:
- age => **Delete rows with value 20**
The code would be:
```ts
const gassma = new Gassma.GassmaClient();
// gassma.{{TARGET_SHEET_NAME}}.deleteMany
const result = gassma.sheet1.deleteMany({
where: {
age: 20,
},
});
```
The return value has the following format:
```ts
{
count: 1;
}
```
The number of deleted rows is returned.
## limit
You can specify the maximum number of records to delete:
```ts
// Delete at most 3 records
const result = gassma.sheet1.deleteMany({
where: {
pref: "Tokyo",
},
limit: 3,
});
```
Specifying `limit: 0` results in 0 deletions (nothing is deleted).
Specifying a finite negative value for `limit` throws `GassmaLimitNegativeError`.
`NaN` / `Infinity` / `-Infinity` / `null` throw `GassmaInvalidValueError` instead. In that case no rows are deleted at all.
```ts
gassma.sheet1.deleteMany({ limit: NaN });
// => Invalid value for argument `limit`. Expected a finite number, but received NaN.
gassma.sheet1.deleteMany({ limit: null });
// => Invalid value for argument `limit`. Expected a number, but received null.
```
`limit: -Infinity` used to throw `GassmaLimitNegativeError`, but the finiteness check now runs first, so it throws `GassmaInvalidValueError`. `undefined` is still ignored (no upper bound).
The `where` specification follows [findMany()](../read/findMany).
# aggregate() (https://gassma.io/en/docs/reference/statistics/aggregate)
Compute aggregations such as _avg, _sum, _min, _max, and _count
Use this when you want to perform statistical calculations such as averages and maximum values.
## Available Keys
| Key Name | Description | Optional | Notes |
| -------- | -------------------------------- | -------- | ----------------------------------------------------------------------------- |
| where | Specify retrieval conditions | Yes | If omitted, all rows are retrieved |
| orderBy | Sort settings | Yes | If specifying only one column, the array can be omitted |
| take | Set the number of records to retrieve | Yes | |
| skip | Set the number of records to skip | Yes | |
| cursor | Cursor-based pagination | Yes | See [findMany cursor](/docs/reference/crud/read/findMany#cursor) for details |
| \_avg | Average display settings | Yes | |
| \_count | Hit count display settings | Yes | `_all` and the `true` shorthand are also available. See [\_count](#_count) for details |
| \_max | Maximum value display settings | Yes | |
| \_min | Minimum value display settings | Yes | |
| \_sum | Sum display settings | Yes | |
In `where`, you can also use [relation filters](/docs/reference/relation/where-relation-filter) (`some` / `every` / `none` / `is` / `isNot`).
## Example Sheet
## Explanation
Suppose you want to perform the following operations from the example above.
- age => **Calculate the average**
- age => **Calculate the maximum value**
- age => **Calculate the minimum value**
The code would be as follows.
```ts
// gassma.{{TARGET_SHEET_NAME}}.aggregate
const result = gassma.sheet1.aggregate({
_avg: {
age: true,
},
_max: {
age: true,
},
_min: {
age: true,
},
});
```
The return value is in the following format.
```ts
{
_avg: { age: 33.333333333333336 },
_max: { age: 55 },
_min: { age: 20 }
}
```
In `_avg` / `_sum` / `_max` / `_min`, `NaN` / invalid Dates (Invalid Date) are excluded from aggregation as missing values, just like null. If every aggregated value is missing, the result is null.
## _count
Use this when you want to get the number of matching rows.
### Counting a Specific Column
If you specify a column name in `_count`, only rows whose value in that column is not a missing value — null (an empty cell), `NaN`, or an invalid Date (Invalid Date) — are counted.
```ts
// gassma.{{TARGET_SHEET_NAME}}.aggregate
const result = gassma.sheet1.aggregate({
_count: {
age: true,
},
});
```
The return value is in the following format.
```ts
{
_count: { age: 9 }
}
```
### Counting All Rows with _all
If you specify `_all: true`, all rows are counted, including null.
```ts
// gassma.{{TARGET_SHEET_NAME}}.aggregate
const result = gassma.sheet1.aggregate({
_count: {
_all: true,
postNumber: true,
},
});
```
The return value is in the following format.
```ts
{
_count: { _all: 9, postNumber: 9 }
}
```
Since column counts skip null rows, on a sheet where, for example, two rows have an empty postNumber, the results would differ like `{ _all: 9, postNumber: 7 }`.
### The true Shorthand
If you specify `_count: true`, the total number of rows is returned directly as a number.
```ts
// gassma.{{TARGET_SHEET_NAME}}.aggregate
const result = gassma.sheet1.aggregate({
_count: true,
});
```
The return value is in the following format.
```ts
{
_count: 9
}
```
`_all` and the `true` shorthand are exclusive to `_count` and cannot be used with `_avg` / `_max` / `_min` / `_sum`. `_count` counts rows, so "all rows including null" is meaningful, while the other aggregations target the values of a specific column.
# count() (https://gassma.io/en/docs/reference/statistics/count)
Count records matching a filter
Use this when you want to get the number of matching records.
## Available Keys
| Key Name | Description | Optional | Notes |
| -------- | -------------------------------- | -------- | ----------------------------------------------------------------------------- |
| where | Specify retrieval conditions | Yes | If omitted, all rows are retrieved |
| orderBy | Sort settings | Yes | If specifying only one column, the array can be omitted |
| take | Set the number of records to retrieve | Yes | |
| skip | Set the number of records to skip | Yes | |
| cursor | Cursor-based pagination | Yes | See [findMany cursor](/docs/reference/crud/read/findMany#cursor) for details |
In `where`, you can also use [relation filters](/docs/reference/relation/where-relation-filter) (`some` / `every` / `none` / `is` / `isNot`).
## Example Sheet
## Explanation
Suppose you want to perform the following operation from the example above.
- age => **20 or greater**
The code would be as follows.
```ts
// gassma.{{TARGET_SHEET_NAME}}.count
const result = gassma.sheet1.count({
where: {
age: {
gte: 20,
},
},
});
```
The return value is in the following format.
```
9
```
# groupBy() (https://gassma.io/en/docs/reference/statistics/groupBy)
Group records by fields, aggregate per group, and filter groups with having
Use this when you want to group data.
## Available Keys
| Key Name | Description | Optional | Notes |
| -------- | --------------------------------------------- | -------- | ----------------------------------------------------------------------------- |
| where | Specify retrieval conditions | Yes | If omitted, all rows are retrieved |
| orderBy | Sort settings | Yes | If specifying only one column, the array can be omitted |
| take | Set the number of records to retrieve | Yes | |
| skip | Set the number of records to skip | Yes | |
| \_avg | Average display settings | Yes | |
| \_count | Hit count display settings | Yes | `_all` and the `true` shorthand are also available. See [\_count](#_count) for details |
| \_max | Maximum value display settings | Yes | |
| \_min | Minimum value display settings | Yes | |
| \_sum | Sum display settings | Yes | |
| by | Specify grouping conditions | No | |
| having | Specify conditions after grouping | Yes | If omitted, all data is retrieved |
`by` is required. Omitting it throws `GassmaMissingArgumentError` (message: Argument `by` is missing.).
In `where`, you can also use [relation filters](/docs/reference/relation/where-relation-filter) (`some` / `every` / `none` / `is` / `isNot`).
## Example Sheet
## Explanation
Suppose you want to perform the following operation from the example above.
- Group by pref
The code would be as follows.
```ts
// gassma.{{TARGET_SHEET_NAME}}.groupBy
const result = gassma.sheet1.groupBy({
by: "pref",
});
```
The return value is in the following format.
```ts
[
{ pref: "Ibaraki" },
{ pref: "Tokyo" },
{ pref: "Osaka" },
{ pref: "Aichi" },
{ pref: "Shiga" },
{ pref: "Kyoto" },
{ pref: "Tottori" },
{ pref: "Fukuoka" },
];
```
You can also specify multiple fields. Suppose you want to perform the following operations.
- Group by pref
- Additionally group by age
The code would be as follows.
```ts
// gassma.{{TARGET_SHEET_NAME}}.groupBy
const result = gassma.sheet1.groupBy({
by: ["pref", "age"],
});
```
The return value is in the following format.
```ts
[
{ pref: "Ibaraki", age: 22 },
{ pref: "Tokyo", age: 31 },
{ pref: "Tokyo", age: 55 },
{ pref: "Osaka", age: 20 },
{ pref: "Aichi", age: 40 },
{ pref: "Shiga", age: 25 },
{ pref: "Kyoto", age: 45 },
{ pref: "Tottori", age: 29 },
{ pref: "Fukuoka", age: 33 },
];
```
### Missing Values as Group Keys (null / NaN / Invalid Date)
Rows whose value in a `by` column is null / `NaN` / an invalid Date (Invalid Date) are also grouped rather than dropped.
- Rows with `NaN` collapse into a single group.
- Rows with Invalid Date also collapse into a single group, even across different instances.
- `NaN`, null, and Invalid Date form **separate groups** from each other.
### having
Use this when you want to extract data that meets specific conditions from grouped data.
For example, suppose you want to extract data with the following conditions.
- Group by pref
- (After grouping) age => **average is 30 or less**
The code would be as follows.
```ts
// gassma.{{TARGET_SHEET_NAME}}.groupBy
const result = gassma.sheet1.groupBy({
by: ["pref"],
having: {
age: {
_avg: {
lte: 30,
},
},
},
});
```
The return value is in the following format.
```ts
[
{ pref: "Ibaraki" },
{ pref: "Osaka" },
{ pref: "Shiga" },
{ pref: "Tottori" },
];
```
### AND, OR, NOT in having
You can also use AND, OR, and NOT.
For example, suppose you want to perform the following operation.
- Group by pref
- (After grouping) age => **average is NOT 30 or less**
The code would be as follows.
```ts
// gassma.{{TARGET_SHEET_NAME}}.groupBy
const result = gassma.sheet1.groupBy({
by: ["pref"],
having: {
NOT: {
age: {
_avg: {
lte: 30,
},
},
},
},
});
```
The return value would be as follows.
```ts
[{ pref: "Tokyo" }, { pref: "Aichi" }, { pref: "Kyoto" }, { pref: "Fukuoka" }];
```
Also, just like `where`, you can nest AND inside NOT and create other nested combinations.
Passing an incomparable value such as `NaN` or an invalid Date (Invalid Date) as a `having` value throws a `GassmaInvalidValueError` (same as `where`).
### Displaying Statistics
You can also display statistics such as averages, just like with aggregate.
For example, suppose you want to perform the following operations.
- Group by pref
- Display the average of age
The code would be as follows.
```ts
// gassma.{{TARGET_SHEET_NAME}}.groupBy
const result = gassma.sheet1.groupBy({
by: ["pref"],
_avg: { age: true },
});
```
The return value is in the following format.
```ts
[
{ pref: "Ibaraki", _avg: { age: 22 } },
{ pref: "Tokyo", _avg: { age: 43 } },
{ pref: "Osaka", _avg: { age: 20 } },
{ pref: "Aichi", _avg: { age: 40 } },
{ pref: "Shiga", _avg: { age: 25 } },
{ pref: "Kyoto", _avg: { age: 45 } },
{ pref: "Tottori", _avg: { age: 29 } },
{ pref: "Fukuoka", _avg: { age: 33 } },
];
```
In `_avg` / `_sum` / `_max` / `_min` and column-specified `_count`, `NaN` / invalid Dates (Invalid Date) are excluded from aggregation as missing values, just like null. If every aggregated value is missing, the result is null. `_count: { _all: true }` still counts those rows.
### _count
With `_count`, you can count the number of rows in each group. If you specify a column name, only rows whose value in that column is not a missing value — null (an empty cell), `NaN`, or an invalid Date (Invalid Date) — are counted, while `_all: true` counts all rows, including missing values.
For example, suppose you want to perform the following operations.
- Group by pref
- Display the number of rows in each group
The code would be as follows.
```ts
// gassma.{{TARGET_SHEET_NAME}}.groupBy
const result = gassma.sheet1.groupBy({
by: ["pref"],
_count: { _all: true },
});
```
The return value is in the following format.
```ts
[
{ pref: "Ibaraki", _count: { _all: 1 } },
{ pref: "Tokyo", _count: { _all: 2 } },
{ pref: "Osaka", _count: { _all: 1 } },
{ pref: "Aichi", _count: { _all: 1 } },
{ pref: "Shiga", _count: { _all: 1 } },
{ pref: "Kyoto", _count: { _all: 1 } },
{ pref: "Tottori", _count: { _all: 1 } },
{ pref: "Fukuoka", _count: { _all: 1 } },
];
```
If you use the `_count: true` shorthand, the row count is returned directly as a number.
```ts
// gassma.{{TARGET_SHEET_NAME}}.groupBy
const result = gassma.sheet1.groupBy({
by: ["pref"],
_count: true,
});
```
The return value is in the following format.
```ts
[
{ pref: "Ibaraki", _count: 1 },
{ pref: "Tokyo", _count: 2 },
{ pref: "Osaka", _count: 1 },
{ pref: "Aichi", _count: 1 },
{ pref: "Shiga", _count: 1 },
{ pref: "Kyoto", _count: 1 },
{ pref: "Tottori", _count: 1 },
{ pref: "Fukuoka", _count: 1 },
];
```
`_all` and the `true` shorthand are exclusive to `_count` and cannot be used with `_avg` / `_max` / `_min` / `_sum`. See [\_count in aggregate](/docs/reference/statistics/aggregate#_count) for details.
# Relation Definition (https://gassma.io/en/docs/reference/relation/definition)
Defining oneToMany, oneToOne, manyToOne, and manyToMany relations between sheets
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 | alice@example.com |
| 2 | Bob | bob@example.com |
| 3 | Charlie | charlie@example.com |
### 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
You can define relations between sheets by passing a `relations` option to the `GassmaClient` constructor.
```ts
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:
```ts
const gassma = new Gassma.GassmaClient({
id: "XXXXXXXXXXXXXXXXXXX",
relations: {
// ...
},
});
```
## 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
```ts
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
```ts
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)
```ts
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.
When defining the reverse relation from the FK-holding side (Profiles in the example above), use `manyToOne` even for a one-to-one relationship.
```ts
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
```ts
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.
```ts
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 |
# include (https://gassma.io/en/docs/reference/relation/include)
Fetch related records with where/orderBy/select options, nested include, and relation _count
Used to retrieve related data together with `findMany` / `findFirst`.
Requires a prior [relation definition](/docs/reference/relation/definition).
## Example Sheets
Uses the sheet examples from [relation definition](/docs/reference/relation/definition).
## Basic Usage
Specify a relation name in `include` with a value of `true` to retrieve all related data.
```ts
const result = gassma.Users.findMany({
include: {
posts: true,
},
});
```
The return value has the following format:
```ts
[
{
id: 1,
name: "Alice",
email: "alice@example.com",
posts: [
{ id: 1, title: "First Post", authorId: 1, published: true },
{ id: 2, title: "How to use GAS", authorId: 1, published: true },
],
},
{
id: 2,
name: "Bob",
email: "bob@example.com",
posts: [
{ id: 3, title: "Draft Article", authorId: 2, published: false },
],
},
{
id: 3,
name: "Charlie",
email: "charlie@example.com",
posts: [],
},
];
```
The returned shape differs by relation type:
| Relation Type | Returned Shape |
| --- | --- |
| oneToMany | Array |
| manyToMany | Array |
| oneToOne | Single object or `null` |
| manyToOne | Single object or `null` |
### manyToOne Example
```ts
const result = gassma.Posts.findMany({
include: {
author: true,
},
});
```
The return value has the following format:
```ts
[
{
id: 1,
title: "First Post",
authorId: 1,
published: true,
author: { id: 1, name: "Alice", email: "alice@example.com" },
},
{
id: 3,
title: "Draft Article",
authorId: 2,
published: false,
author: { id: 2, name: "Bob", email: "bob@example.com" },
},
// ...
];
```
## include Options
Instead of `true`, you can pass an object to apply conditions to the related data.
### Available Keys
| Key | Description | Optional |
| --- | --- | --- |
| where | Query conditions for related data | Optional |
| orderBy | Sort order for related data | Optional |
| skip | Number of related records to skip | Optional |
| take | Number of related records to retrieve | Optional |
| select | Display settings for related columns | Optional |
| omit | Exclusion settings for related columns | Optional |
| include | Retrieve deeper nested relations | Optional |
### where
Apply conditions to filter related data:
```ts
const result = gassma.Users.findMany({
include: {
posts: {
where: { published: true },
},
},
});
```
The return value has the following format:
```ts
[
{
id: 1,
name: "Alice",
email: "alice@example.com",
posts: [
{ id: 1, title: "First Post", authorId: 1, published: true },
{ id: 2, title: "How to use GAS", authorId: 1, published: true },
],
},
{
id: 2,
name: "Bob",
email: "bob@example.com",
posts: [], // published: false articles are filtered out
},
// ...
];
```
### orderBy
Sort related data:
```ts
const result = gassma.Users.findMany({
include: {
posts: {
orderBy: { title: "desc" },
},
},
});
```
### skip / take
Paginate related data using `skip` and `take` together:
```ts
const result = gassma.Users.findMany({
include: {
posts: {
orderBy: { id: "asc" },
skip: 1,
take: 1,
},
},
});
```
The above example orders each user's posts by id ascending, skips the first one, and retrieves only the next one.
`skip` / `take` are available for oneToMany and manyToMany. They are not applicable to oneToOne / manyToOne as they return a single record.
Passing a non-number to `skip` / `take` in `include` throws an `IncludeInvalidOptionTypeError`. The message differs depending on the value.
```ts
gassma.Users.findMany({ include: { posts: { take: NaN } } });
// => IncludeInvalidOptionTypeError:
// Include "posts": option "take" must be a finite number
gassma.Users.findMany({ include: { posts: { take: null } } });
// => IncludeInvalidOptionTypeError:
// Include "posts": option "take" must be a number
```
| Value | Message |
| --- | --- |
| `NaN` / `Infinity` / `-Infinity` | `must be a finite number` |
| `null` or any non-number | `must be a number` |
| `undefined` | Treated as "not specified" and ignored |
Finite negative numbers are not errors: `take` reads from the end, and a negative `skip` throws `GassmaSkipNegativeError`. Options of nested `include` receive the same validation.
### select
Specify which columns to retrieve from related data:
```ts
const result = gassma.Users.findMany({
include: {
posts: {
select: { title: true },
},
},
});
```
The return value has the following format:
```ts
[
{
id: 1,
name: "Alice",
email: "alice@example.com",
posts: [
{ title: "First Post" },
{ title: "How to use GAS" },
],
},
// ...
];
```
### omit
Exclude specific columns from related data:
```ts
const result = gassma.Users.findMany({
include: {
posts: {
omit: { authorId: true },
},
},
});
```
`omit` is merged with the related model's [global omit](/docs/reference/config/global-omit). As with the top-level `omit`, specifying `false` re-includes a field hidden by the global omit for this fetch only:
```ts
// With a global omit excluding Posts.content
const result = gassma.Users.findMany({
include: {
posts: {
omit: { content: false }, // content is returned (overrides the global omit)
},
},
});
```
`select` and `omit` cannot be specified simultaneously.
### Nested include
You can specify `include` within `include` to retrieve deep relation hierarchies.
For example, you can retrieve Users → Posts → Tags in a single query:
```ts
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.findMany({
include: {
posts: {
include: {
tags: true,
},
},
},
});
```
The return value has the following format:
```ts
[
{
id: 1,
name: "Alice",
email: "alice@example.com",
posts: [
{
id: 1,
title: "First Post",
authorId: 1,
published: true,
tags: [
{ id: 1, name: "GAS" },
{ id: 2, name: "JavaScript" },
],
},
{
id: 2,
title: "How to use GAS",
authorId: 1,
published: true,
tags: [
{ id: 1, name: "GAS" },
],
},
],
},
// ...
];
```
`select` and `include` cannot be specified simultaneously.
## _count
Retrieve the count of related records.
### Count All Relations
Specify `_count: true` to get the record count for all defined relations:
```ts
const result = gassma.Users.findMany({
include: {
_count: true,
},
});
```
The return value has the following format:
```ts
[
{
id: 1,
name: "Alice",
email: "alice@example.com",
_count: { posts: 2, profile: 1 },
},
{
id: 2,
name: "Bob",
email: "bob@example.com",
_count: { posts: 1, profile: 0 },
},
// ...
];
```
### Count Specific Relations
Use `_count: { select: { ... } }` to specify which relations to count:
```ts
const result = gassma.Users.findMany({
include: {
_count: {
select: { posts: true },
},
},
});
```
### Count with where Filter
You can also add conditions to the count target:
```ts
const result = gassma.Users.findMany({
include: {
_count: {
select: {
posts: {
where: { published: true },
},
},
},
},
});
```
The return value has the following format:
```ts
[
{
id: 1,
name: "Alice",
email: "alice@example.com",
_count: { posts: 2 }, // Only published: true posts are counted
},
// ...
];
```
### Combining select and _count
You can combine top-level `select` with `_count`:
```ts
const result = gassma.Users.findMany({
select: {
name: true,
_count: {
select: { posts: true },
},
},
});
```
The return value has the following format:
```ts
[
{ name: "Alice", _count: { posts: 2 } },
{ name: "Bob", _count: { posts: 1 } },
// ...
];
```
`_count` supports all relation types (oneToMany / oneToOne / manyToOne / manyToMany).
## Retrieving Multiple Relations Simultaneously
You can retrieve multiple relations in a single query:
```ts
const result = gassma.Users.findMany({
include: {
posts: true,
profile: true,
},
});
```
## Restrictions on include and select
**Top-level** `select` and `include` cannot be used simultaneously.
```ts
// This will throw an error
gassma.Users.findMany({
select: { name: true },
include: { posts: true },
});
```
## Validation
| Error | Cause |
| --- | --- |
| `IncludeWithoutRelationsError` | Used `include` without relation definitions |
| `GassmaIncludeSelectConflictError` | Used `include` and `select` simultaneously at the top level |
| `IncludeSelectOmitConflictError` | Used `select` and `omit` simultaneously within include |
| `IncludeSelectIncludeConflictError` | Used `select` and `include` simultaneously within include |
| `IncludeInvalidOptionTypeError` | Invalid type for include value or option |
| `GassmaRelationNotFoundError` | Specified relation name is not defined |
# where Relation Filter (https://gassma.io/en/docs/reference/relation/where-relation-filter)
Filter by related records using some/every/none (list relations) and is/isNot (single relations)
Used to filter based on related data within `where` conditions.
Requires a prior [relation definition](/docs/reference/relation/definition).
## Example Sheets
Uses the sheet examples from [relation definition](/docs/reference/relation/definition).
## Supported Methods
where relation filters can be used with all the following methods:
- `findMany` / `findFirst`
- `update` / `updateMany` / `deleteMany`
- `aggregate` / `count` / `groupBy`
## Filter Types
Available filters differ by relation type:
| Filter | oneToMany | manyToMany | oneToOne | manyToOne |
| --- | --- | --- | --- | --- |
| some | Available | Available | - | - |
| every | Available | Available | - | - |
| none | Available | Available | - | - |
| is | - | - | Available | Available |
| isNot | - | - | Available | Available |
## List Relation Filters (oneToMany / manyToMany)
### some
Retrieves records where **at least one** related record matches the condition.
Example: Get users who have at least one published post
```ts
const result = gassma.Users.findMany({
where: {
posts: {
some: { published: true },
},
},
});
```
The return value has the following format:
```ts
[
{ id: 1, name: "Alice", email: "alice@example.com" },
];
```
Alice is retrieved because she has published posts. Bob only has `published: false` posts, and Charlie has no posts, so they are excluded.
### every
Retrieves records where **all** related records match the condition. Records with 0 related records are also treated as matching.
Example: Get users whose all posts are published
```ts
const result = gassma.Users.findMany({
where: {
posts: {
every: { published: true },
},
},
});
```
The return value has the following format:
```ts
[
{ id: 1, name: "Alice", email: "alice@example.com" },
{ id: 3, name: "Charlie", email: "charlie@example.com" },
];
```
Alice is retrieved because all her posts are `published: true`. Charlie is retrieved because he has 0 posts (= all satisfy the condition).
### none
Retrieves records where **not a single** related record matches the condition.
Example: Get users who have no published posts
```ts
const result = gassma.Users.findMany({
where: {
posts: {
none: { published: true },
},
},
});
```
The return value has the following format:
```ts
[
{ id: 2, name: "Bob", email: "bob@example.com" },
{ id: 3, name: "Charlie", email: "charlie@example.com" },
];
```
When the parent record's own join key (the column specified as `reference` in the relation definition) is null, its related records are treated as 0. Such parents are therefore included in the results of `every` (all records satisfy the condition) and `none` (no record matches), and excluded from `some` (same as Prisma).
## Single Relation Filters (oneToOne / manyToOne)
### is
Retrieves records where the related record matches the condition. Specifying `null` retrieves records where no related record exists.
Example: Get posts whose author name is "Alice"
```ts
const result = gassma.Posts.findMany({
where: {
author: {
is: { name: "Alice" },
},
},
});
```
The return value has the following format:
```ts
[
{ id: 1, title: "First Post", authorId: 1, published: true },
{ id: 2, title: "How to use GAS", authorId: 1, published: true },
];
```
### is: null
Retrieves records where no related record exists. For `manyToOne` (the FK-holding side), this matches records whose FK is null; for `oneToOne` (the side without the FK), this matches records for which **no related record exists**.
Example: Get posts whose author (the FK `authorId`) is null
```ts
const result = gassma.Posts.findMany({
where: {
author: {
is: null,
},
},
});
```
Example: Get users who have no profile (`oneToOne` on the non-FK side)
```ts
const result = gassma.Users.findMany({
where: {
profile: {
is: null,
},
},
});
```
The return value has the following format:
```ts
[
{ id: 3, name: "Charlie", email: "charlie@example.com" },
];
```
For `oneToOne` (the side without the FK), records whose own join key (the column specified as `reference`) is null are also included in the `is: null` results as "no related record exists".
### isNot
Retrieves records where the related record does **not** match the condition. Specifying `null` retrieves records where a related record exists.
Example: Get posts whose author is not "Alice"
```ts
const result = gassma.Posts.findMany({
where: {
author: {
isNot: { name: "Alice" },
},
},
});
```
The return value has the following format:
```ts
[
{ id: 3, title: "Draft Article", authorId: 2, published: false },
];
```
Rows without a related record (for `manyToOne`, rows whose FK is null; for `oneToOne`, rows with no related record) are also included in the results of `isNot: `. Since no related record exists, they count as "having no related record that matches the condition" (same as Prisma).
### isNot: null
Retrieves records where a related record exists. For `manyToOne` (the FK-holding side), this matches records whose FK is not null; for `oneToOne` (the side without the FK), this matches records for which **a related record exists**.
```ts
const result = gassma.Posts.findMany({
where: {
author: {
isNot: null,
},
},
});
```
## Combining with AND / OR / NOT
Relation filters can be freely combined with AND / OR / NOT.
### Combining with OR
Example: Get users who have published posts OR whose name is "Charlie"
```ts
const result = gassma.Users.findMany({
where: {
OR: [
{ posts: { some: { published: true } } },
{ name: "Charlie" },
],
},
});
```
### Combining with NOT
Example: Get users who don't have any draft articles
```ts
const result = gassma.Users.findMany({
where: {
NOT: {
posts: { some: { published: false } },
},
},
});
```
## Usage Examples Beyond findMany / findFirst
### updateMany
Update email addresses of users who have published posts:
```ts
gassma.Users.updateMany({
where: {
posts: { some: { published: true } },
},
data: {
email: "updated@example.com",
},
});
```
### count
Count users who have published posts:
```ts
const count = gassma.Users.count({
where: {
posts: { some: { published: true } },
},
});
```
## Validation
| Error | Cause |
| --- | --- |
| `WhereRelationWithoutContextError` | Used relation filter syntax without relation definitions |
| `WhereRelationInvalidFilterError` | Used list filters (some/every/none) on oneToOne/manyToOne, or used single filters (is/isNot) on oneToMany/manyToMany |
# onDelete (https://gassma.io/en/docs/reference/relation/on-delete)
Referential actions on delete (Cascade, SetNull, Restrict, NoAction)
Defines how related records should be handled when records are deleted with `deleteMany`.
## Example Sheets
Uses the sheet examples from [relation definition](/docs/reference/relation/definition).
## Basic Usage
Specify `onDelete` in the [relation definition](/docs/reference/relation/definition):
```ts
const gassma = new Gassma.GassmaClient({
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onDelete: "Cascade",
},
},
},
});
```
onDelete does not fire on relation definitions of the FK-holding side (`manyToOne`). Specify it on the referenced side (`oneToMany` / the non-FK side `oneToOne` / `manyToMany`).
## Action Types
| Action | Behavior |
| --- | --- |
| Cascade | Delete related records together |
| SetNull | Set the FK of related records to null |
| Restrict | Prevent deletion with an error if related records exist |
| NoAction | Do nothing (default) |
## Cascade
When a parent record is deleted, related child records are automatically deleted as well. This writes to multiple sheets, but if it fails partway through, none of the sheets are modified (see [Write Atomicity and Concurrency](/docs/reference/write-atomicity)):
```ts
const gassma = new Gassma.GassmaClient({
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onDelete: "Cascade",
},
},
},
});
// Deleting Alice also deletes all of Alice's posts
gassma.Users.deleteMany({
where: { name: "Alice" },
});
```
Executing the above deletes Alice from the Users sheet, and also deletes all records in the Posts sheet with `authorId: 1`.
### manyToMany Case
When Cascade is specified for manyToMany, records in the **junction table** are deleted. Records in the target table (relation destination) are not deleted.
```ts
relations: {
Posts: {
tags: {
type: "manyToMany",
to: "Tags",
field: "id",
reference: "id",
through: {
sheet: "PostTags",
field: "postId",
reference: "tagId",
},
onDelete: "Cascade",
},
},
}
// Deleting a post also deletes related rows in PostTags (Tags remain)
gassma.Posts.deleteMany({
where: { id: 1 },
});
```
## SetNull
When a parent record is deleted, the FK of related child records is updated to `null`:
```ts
const gassma = new Gassma.GassmaClient({
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onDelete: "SetNull",
},
},
},
});
// Deleting Alice sets the authorId of Alice's posts to null
gassma.Users.deleteMany({
where: { name: "Alice" },
});
```
The Posts sheet after execution:
| id | title | authorId | published |
| --- | --- | --- | --- |
| 1 | First Post | null | true |
| 2 | How to use GAS | null | true |
| 3 | Draft Article | 2 | false |
When SetNull is specified for manyToMany, nothing happens (because setting the junction table FK to null is meaningless).
## Restrict
If even one related record exists, the deletion is rejected and an error is thrown:
```ts
const gassma = new Gassma.GassmaClient({
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onDelete: "Restrict",
},
},
},
});
// Alice has posts, so an error is thrown
gassma.Users.deleteMany({
where: { name: "Alice" },
});
// => RelationOnDeleteRestrictError
// Charlie has no posts, so deletion proceeds normally
gassma.Users.deleteMany({
where: { name: "Charlie" },
});
```
Restrict checks are performed **first** for all relations. Therefore, even if an error occurs, side effects (Cascade of other relations, etc.) are not executed.
## NoAction
Does nothing. Same behavior as not specifying `onDelete`:
```ts
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onDelete: "NoAction", // Same as not specifying
},
},
}
```
## onDelete with Multiple Relations
You can define multiple relations for a single sheet with different onDelete settings:
```ts
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onDelete: "Cascade", // Posts are deleted together
},
profile: {
type: "oneToOne",
to: "Profiles",
field: "id",
reference: "userId",
onDelete: "SetNull", // Profile FK is set to null
},
},
}
```
# Nested Write (create) (https://gassma.io/en/docs/reference/relation/nested-write)
Write related records inside create using create/connect/connectOrCreate
Used to simultaneously create and associate records in relation targets within the `create` method.
Requires a prior [relation definition](/docs/reference/relation/definition).
## Example Sheets
Uses the sheet examples from [relation definition](/docs/reference/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 |
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](/docs/reference/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).
```ts
const result = gassma.Posts.create({
data: {
id: 4,
title: "New Article",
published: true,
author: {
create: {
id: 4,
name: "Dave",
email: "dave@example.com",
},
},
},
});
```
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:
```ts
{
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:
```ts
const result = gassma.Users.create({
data: {
id: 4,
name: "Dave",
email: "dave@example.com",
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:
```ts
const result = gassma.Users.create({
data: {
id: 4,
name: "Dave",
email: "dave@example.com",
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:
```ts
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:
```ts
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:
```ts
const result = gassma.Users.create({
data: {
id: 4,
name: "Dave",
email: "dave@example.com",
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:
```ts
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
If no record matching the condition is found, `NestedWriteConnectNotFoundError` is thrown.
### connect with oneToOne
Create a user and simultaneously link an existing profile:
```ts
const result = gassma.Users.create({
data: {
id: 4,
name: "Dave",
email: "dave@example.com",
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.
If no record matching the condition is found, `NestedWriteConnectNotFoundError` is thrown.
### connect with oneToMany
Create a user and simultaneously link existing posts:
```ts
const result = gassma.Users.create({
data: {
id: 4,
name: "Dave",
email: "dave@example.com",
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:
```ts
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:
```ts
const result = gassma.Posts.create({
data: {
id: 4,
title: "New Article",
published: true,
author: {
connectOrCreate: {
where: { name: "Alice" },
create: {
id: 4,
name: "Alice",
email: "alice-new@example.com",
},
},
},
},
});
```
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:
```ts
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:
```ts
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: "dave@example.com",
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](/docs/reference/write-atomicity) 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) |
# onUpdate (https://gassma.io/en/docs/reference/relation/on-update)
Referential actions on update (Cascade, SetNull, Restrict, NoAction)
Defines how related records should be handled when a record's PK (primary key) is changed with `update` / `updateMany` / `updateManyAndReturn`.
## Example Sheets
Uses the sheet examples from [relation definition](/docs/reference/relation/definition).
## Basic Usage
Specify `onUpdate` in the [relation definition](/docs/reference/relation/definition):
```ts
const gassma = new Gassma.GassmaClient({
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onUpdate: "Cascade",
},
},
},
});
```
onUpdate does not fire on relation definitions of the FK-holding side (`manyToOne`). Specify it on the referenced side (`oneToMany` / the non-FK side `oneToOne` / `manyToMany`).
## Action Types
| Action | Behavior |
| --- | --- |
| Cascade | Automatically update the FK of related records to the new value |
| SetNull | Set the FK of related records to null |
| Restrict | Prevent the update with an error if related records exist |
| NoAction | Do nothing (default) |
## Cascade
When a parent record's PK is changed, the FK of related child records is automatically updated to the new value. This writes to multiple sheets, but if it fails partway through, none of the sheets are modified (see [Write Atomicity and Concurrency](/docs/reference/write-atomicity)):
```ts
const gassma = new Gassma.GassmaClient({
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onUpdate: "Cascade",
},
},
},
});
// Changing Alice's id from 1 to 10 also updates Posts authorId: 1 to authorId: 10
gassma.Users.updateMany({
where: { name: "Alice" },
data: { id: 10 },
});
```
The Posts sheet after update:
| id | title | authorId | published |
| --- | --- | --- | --- |
| 1 | First Post | 10 | true |
| 2 | How to use GAS | 10 | true |
| 3 | Draft Article | 2 | false |
### manyToMany Case
When Cascade is specified for manyToMany, the corresponding column in the **junction table** is updated to the new value:
```ts
relations: {
Posts: {
tags: {
type: "manyToMany",
to: "Tags",
field: "id",
reference: "id",
through: {
sheet: "PostTags",
field: "postId",
reference: "tagId",
},
onUpdate: "Cascade",
},
},
}
// Changing post id from 1 to 100 also updates PostTags postId: 1 to postId: 100
gassma.Posts.updateMany({
where: { id: 1 },
data: { id: 100 },
});
```
## SetNull
When a parent record's PK is changed, the FK of related child records is updated to `null`:
```ts
const gassma = new Gassma.GassmaClient({
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onUpdate: "SetNull",
},
},
},
});
// Changing Alice's id sets the authorId of Alice's posts to null
gassma.Users.updateMany({
where: { name: "Alice" },
data: { id: 10 },
});
```
The Posts sheet after update:
| id | title | authorId | published |
| --- | --- | --- | --- |
| 1 | First Post | null | true |
| 2 | How to use GAS | null | true |
| 3 | Draft Article | 2 | false |
When SetNull is specified for manyToMany, nothing happens (because setting the junction table FK to null is meaningless).
## Restrict
If even one related record exists, the update is rejected and an error is thrown:
```ts
const gassma = new Gassma.GassmaClient({
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onUpdate: "Restrict",
},
},
},
});
// Alice has posts, so changing the id causes an error
gassma.Users.updateMany({
where: { name: "Alice" },
data: { id: 10 },
});
// => RelationOnUpdateRestrictError
// Charlie has no posts, so the update proceeds normally
gassma.Users.updateMany({
where: { name: "Charlie" },
data: { id: 10 },
});
```
Restrict checks are performed **first** for all relations. Therefore, even if an error occurs, side effects (Cascade of other relations, etc.) are not executed.
## NoAction
Does nothing. Same behavior as not specifying `onUpdate`:
```ts
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onUpdate: "NoAction", // Same as not specifying
},
},
}
```
## Combining with onDelete
`onDelete` and `onUpdate` can be specified simultaneously in the same relation definition:
```ts
relations: {
Users: {
posts: {
type: "oneToMany",
to: "Posts",
field: "id",
reference: "authorId",
onDelete: "Cascade", // On delete: Posts are also deleted
onUpdate: "Cascade", // On PK update: Posts FK is also updated
},
},
}
```
# Nested Write (update) (https://gassma.io/en/docs/reference/relation/nested-write-update)
Modify related records inside update using update/delete/deleteMany/disconnect/set
You can simultaneously operate on related records within the `data` of the `update` method.
In addition to the operations available in [create's Nested Write](/docs/reference/relation/nested-write), `update` / `delete` / `deleteMany` / `disconnect` / `set` operations are available.
These operations write to multiple sheets, but if one fails partway through, not a single row is written to any of them. See [Write Atomicity and Concurrency](/docs/reference/write-atomicity) for details.
## Example Sheets
Uses the sheet examples from [relation definition](/docs/reference/relation/definition).
## Available Operations
| Operation | manyToOne / oneToOne | oneToMany | manyToMany |
| --- | --- | --- | --- |
| create | Single only | Single / Array | Single / Array |
| createMany | - | Supported | - |
| connect | Supported | Single / Array | Single / Array |
| connectOrCreate | Supported | Single / Array | Single / Array |
| update | Supported | Single / Array | - |
| delete | Supported | Single / Array | - |
| deleteMany | - | Single / Array | - |
| disconnect | Supported | Single / Array | Single / Array |
| set | - | Supported | Supported |
manyToOne and oneToOne accept the same operation shapes but behave differently. manyToOne (FK-holding side) operates on the **own record's FK**, while oneToOne (non-FK side) operates on the **related record holding the FK** (see [relation definition](/docs/reference/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 | - |
| update | Updates the related record (specify data directly) | `NestedWriteTargetNotFoundError` |
| disconnect: true | Nulls the FK of the related record | Does nothing |
| delete: true | Deletes the related record | `NestedWriteTargetNotFoundError` |
Specifying `set` / `deleteMany` / `createMany` / array forms results in `NestedWriteInvalidOperationError`.
## create
Creates a new related record and associates it. Same behavior as [create's Nested Write](/docs/reference/relation/nested-write).
```ts
// oneToMany: Create a new post when updating a user
gassma.Users.update({
where: { name: "Alice" },
data: {
posts: {
create: { id: 4, title: "New Post", published: true },
},
},
});
```
## connect
Associates an existing related record:
```ts
// manyToOne: Change the post's author to an existing user
gassma.Posts.update({
where: { id: 1 },
data: {
author: {
connect: { name: "Bob" },
},
},
});
```
With oneToOne (non-FK side), connect behaves as a **replacement**. The FK of the currently connected related record is set to `null`, then the target record's FK is set to the parent:
```ts
// oneToOne: Replace the user's profile with another profile
gassma.Users.update({
where: { name: "Alice" },
data: {
profile: {
connect: { id: 2 },
},
},
});
// => After the userId of Profiles id: 1 (currently connected) becomes null,
// the userId of id: 2 is updated to Alice's id (= 1)
```
## connectOrCreate
Associates if existing record found, otherwise creates and associates:
```ts
// manyToOne: Connect to author if exists, otherwise create
gassma.Posts.update({
where: { id: 1 },
data: {
author: {
connectOrCreate: {
where: { name: "Dave" },
create: { id: 4, name: "Dave", email: "dave@example.com" },
},
},
},
});
```
## update
Updates related records.
### manyToOne (FK-Holding Side)
Specify the update data directly. The record referenced by the own record's FK is updated:
```ts
// manyToOne: Update the post's author name
gassma.Posts.update({
where: { id: 1 },
data: {
author: {
update: { name: "Alice Updated" },
},
},
});
```
### oneToOne (Non-FK Side)
Likewise, specify the update data directly. The related record referencing the parent is updated:
```ts
// oneToOne: Update the user's profile
gassma.Users.update({
where: { name: "Alice" },
data: {
profile: {
update: { bio: "Updated bio" },
},
},
});
```
If no connected related record exists, `NestedWriteTargetNotFoundError` is thrown.
### oneToMany
Specify `where` and `data` to narrow down the update target. Multiple specifications with arrays are also possible:
```ts
// oneToMany: Update a specific post
gassma.Users.update({
where: { name: "Alice" },
data: {
posts: {
update: {
where: { id: 1 },
data: { title: "Updated Title" },
},
},
},
});
// Update multiple posts simultaneously
gassma.Users.update({
where: { name: "Alice" },
data: {
posts: {
update: [
{ where: { id: 1 }, data: { title: "Title A" } },
{ where: { id: 2 }, data: { title: "Title B" } },
],
},
},
});
```
## delete
Deletes related records.
### manyToOne (FK-Holding Side)
Specify `delete: true` to delete the related record and set the own FK to `null`:
```ts
// manyToOne: Delete the post's author (post's authorId becomes null)
gassma.Posts.update({
where: { id: 1 },
data: {
author: { delete: true },
},
});
```
### oneToOne (Non-FK Side)
Specify `delete: true` to delete the related record referencing the parent. The own record is not modified:
```ts
// oneToOne: Delete the user's profile
gassma.Users.update({
where: { name: "Alice" },
data: {
profile: { delete: true },
},
});
// => The record with userId: 1 in Profiles is deleted
```
If no connected related record exists, `NestedWriteTargetNotFoundError` is thrown.
### oneToMany
Specify `where` conditions to narrow down deletion targets. Multiple specifications with arrays are also possible:
```ts
// oneToMany: Delete a specific post
gassma.Users.update({
where: { name: "Alice" },
data: {
posts: {
delete: { id: 3 },
},
},
});
// Delete multiple
gassma.Users.update({
where: { name: "Alice" },
data: {
posts: {
delete: [{ id: 2 }, { id: 3 }],
},
},
});
```
## deleteMany
Bulk deletes related records matching conditions. Only available for oneToMany:
```ts
// oneToMany: Delete all unpublished posts
gassma.Users.update({
where: { name: "Alice" },
data: {
posts: {
deleteMany: { published: false },
},
},
});
// Delete with multiple conditions
gassma.Users.update({
where: { name: "Alice" },
data: {
posts: {
deleteMany: [
{ published: false },
{ title: "Draft" },
],
},
},
});
```
## disconnect
Removes the relation association. The record itself is not deleted.
### manyToOne (FK-Holding Side)
Specify `disconnect: true` to set the own FK to `null`:
```ts
// manyToOne: Remove the association between post and author
gassma.Posts.update({
where: { id: 1 },
data: {
author: { disconnect: true },
},
});
// => Posts authorId becomes null
```
### oneToOne (Non-FK Side)
Specify `disconnect: true` to set the FK of the related record referencing the parent to `null`:
```ts
// oneToOne: Remove the association between user and profile
gassma.Users.update({
where: { name: "Alice" },
data: {
profile: { disconnect: true },
},
});
// => The userId: 1 in Profiles becomes null
```
If no connected record exists, nothing happens (no error is thrown).
### oneToMany
Specify `where` conditions to set the FK of related records to `null`:
```ts
// oneToMany: Remove association for specific posts
gassma.Users.update({
where: { name: "Alice" },
data: {
posts: {
disconnect: { id: 1 },
},
},
});
// => Posts id: 1 authorId becomes null
// Remove multiple associations
gassma.Users.update({
where: { name: "Alice" },
data: {
posts: {
disconnect: [{ id: 1 }, { id: 2 }],
},
},
});
```
### manyToMany
Deletes the junction table records:
```ts
// manyToMany: Remove tag association
gassma.Posts.update({
where: { id: 1 },
data: {
tags: {
disconnect: { id: 3 },
},
},
});
// => The corresponding record is deleted from the PostTags table
```
## set
Replaces all relation associations. Only available for oneToMany and manyToMany.
### oneToMany
Sets all child records' FK to `null`, then sets the FK of specified records to the parent:
```ts
// oneToMany: Replace Alice's posts with only id: 1 and id: 2
gassma.Users.update({
where: { name: "Alice" },
data: {
posts: {
set: [{ id: 1 }, { id: 2 }],
},
},
});
// => All existing posts' authorId becomes null, then
// id: 1 and id: 2's authorId is set to Alice's id
```
### manyToMany
Deletes all junction table records, then creates new associations with specified records:
```ts
// manyToMany: Completely replace post tags
gassma.Posts.update({
where: { id: 1 },
data: {
tags: {
set: [{ id: 10 }, { id: 11 }],
},
},
});
// => All records for post id: 1 are deleted from PostTags, then
// new association records are created
```
## Combining Multiple Operations
You can combine multiple relation operations within a single update:
```ts
gassma.Users.update({
where: { name: "Alice" },
data: {
name: "Alice Updated",
posts: {
create: { id: 5, title: "New Article", published: true },
update: { where: { id: 1 }, data: { title: "Updated" } },
delete: { id: 3 },
},
},
});
```
## Errors
| Error | Cause |
| --- | --- |
| `NestedWriteWithoutRelationsError` | Executed Nested Write without relation definitions |
| `NestedWriteInvalidOperationError` | Specified an operation not supported for the relation type |
| `NestedWriteConnectNotFoundError` | Target record not found for connect / connectOrCreate |
| `NestedWriteTargetNotFoundError` | No related record exists for update / delete on the non-FK side of a oneToOne |
# changeSettings() (https://gassma.io/en/docs/reference/settings/changeSettings)
Configure the data range of a sheet (start row and column range)
Use this when you want to configure the reading range of the spreadsheet.
## Arguments
| Argument Name | Description | Type | Notes |
| ---------------- | --------------------------------------------------- | ------------------ | -------------------------------------------------------- |
| startRowNumber | Specify the row number where column names are written | `number` |
| startColumnValue | Specify the column where the first column name is | `number \| string` | Specify either the column number or the column letter |
| endColumnValue | Specify the column where the last column name is | `number \| string` | Specify either the column number or the column letter |
## Specifying Columns
For `startColumnValue` / `endColumnValue`, you can specify either a **column number (`number`)** or a **column letter (`string`)**.
Letters are case-insensitive and interpreted with the same base-26 scheme as spreadsheet column headers. `"A"` through `"Z"` are 1 through 26, and `"AA"` rolls over into two characters.
| Value | Column Number |
| ------ | ------------- |
| `"A"` | 1 |
| `"Z"` | 26 |
| `"AA"` | 27 |
| `"AZ"` | 52 |
| `"BA"` | 53 |
```ts
// The following two are equivalent
gassma.sheet1.changeSettings(1, "B", "E");
gassma.sheet1.changeSettings(1, 2, 5);
```
Specifying a string that contains non-letter characters (digits, symbols, empty string, etc.) throws `GassmaInValidColumnValueError`. To specify by number, pass a `number` rather than a string.
## When to Use
If your spreadsheet is in any of the following states, you **must** call `changeSettings()` before performing any operations on the sheet.
### 1. When the Table Is Not in the Top-Left Corner
For a table like the one above, you can read the table correctly by writing the following code.
```ts
const gassma = new Gassma.GassmaClient();
// Must be called before any sheet operations
gassma.sheet1.changeSettings(4, "B", "E");
const result = gassma.sheet1.findMany({});
```
### 2. When There Is Other Data (e.g., Notes) to the Right
```ts
const gassma = new Gassma.GassmaClient();
// Must be called before any sheet operations
gassma.sheet1.changeSettings(1, "A", "D");
const result = gassma.sheet1.findMany({});
```
# Global omit (https://gassma.io/en/docs/reference/config/global-omit)
Exclude fields from results by default per sheet
By specifying `omit` in the `GassmaClient` constructor, default field exclusion is applied to all queries for the specified sheets.
## Basic Usage
```ts
const gassma = new Gassma.GassmaClient({
omit: {
Users: {
password: true,
secret: true,
},
Posts: {
internalNotes: true,
},
},
});
// When fetching from the Users sheet, password and secret are automatically excluded
const users = gassma.Users.findMany({});
// => [{ id: 1, name: "Alice", email: "alice@example.com" }, ...]
// password and secret fields are not included
```
## Priority
Global omit, query-level `omit`, and `select` have the following priority order.
| Priority | Condition | Behavior |
| --- | --- | --- |
| 1 (highest) | `select` is specified | Ignores both global omit and query omit |
| 2 | Query `omit` is specified | Merged with global omit |
| 3 | Neither is specified | Global omit is applied as-is |
### Overriding Global omit with select
When `select` is specified, global omit is not applied.
```ts
// Global omit: { password: true }
const result = gassma.Users.findMany({
select: { name: true, password: true },
});
// => [{ name: "Alice", password: "secret123" }]
// select takes highest priority, so password is also retrieved
```
### Overriding Global omit with Query omit
You can disable global omit on a per-field basis by specifying `false` in the query-level `omit`.
```ts
// Global omit: { password: true, secret: true }
const result = gassma.Users.findMany({
omit: { password: false },
});
// => [{ id: 1, name: "Alice", password: "secret123" }]
// Global omit for password is disabled; only secret is excluded
```
You can specify additional fields to exclude by setting `true` in the query `omit`.
```ts
// Global omit: { password: true }
const result = gassma.Users.findMany({
omit: { email: true },
});
// => [{ id: 1, name: "Alice" }]
// Both password (global) and email (query) are excluded
```
## Supported Methods
Global omit is applied to the following methods.
| Method | Applied |
| --- | --- |
| `findMany` | ✅ |
| `findFirst` / `findFirstOrThrow` | ✅ |
| `create` | ✅ |
| `update` | ✅ |
| `upsert` | ✅ |
| `delete` | ✅ |
| `createManyAndReturn` | ✅ |
| `updateManyAndReturn` | ✅ |
`createMany`, `updateMany`, and `deleteMany` return `{ count: number }`, so they are not affected by global omit.
## Combining with Relations
```ts
const gassma = new Gassma.GassmaClient({
omit: {
Users: { password: true },
},
relations: {
Users: {
posts: { type: "oneToMany", to: "Posts", field: "id", reference: "authorId" },
},
},
});
// Can be used together with relations
const result = gassma.Users.findMany({
include: { posts: true },
});
// => [{ id: 1, name: "Alice", posts: [...] }]
// password is excluded while relation data is also retrieved
```
## Validation
| Error | Cause |
| --- | --- |
| `GassmaFindSelectOmitConflictError` | `select` and `omit` are specified simultaneously at the query level |
# defaults (@default) (https://gassma.io/en/docs/reference/config/defaults)
Auto-set field values on create with static values or functions (@default)
This is the equivalent of Prisma's `@default()`. It automatically sets default values for fields during `create` operations.
## Basic Usage
```ts
const gassma = new Gassma.GassmaClient({
defaults: {
Users: {
role: "USER",
createdAt: () => new Date(),
},
},
});
// Default values are automatically applied during create
gassma.Users.create({
data: { name: "Alice" },
});
// => { name: "Alice", role: "USER", createdAt: 2026-03-14T... }
```
## Static Values and Functions
You can specify both fixed values and functions as default values.
| Type | Example | Behavior |
| --- | --- | --- |
| Static value | `role: "USER"` | Sets the same value every time |
| Function | `createdAt: () => new Date()` | Evaluated on each invocation |
## Applicable Methods
| Method | Applied |
| --- | --- |
| `create` | ✅ |
| `createMany` / `createManyAndReturn` | ✅ |
| `upsert` (create part only) | ✅ |
## Behavior with Explicit Values
If a field is explicitly specified (including `null`), the default value is not applied.
```ts
gassma.Users.create({
data: { name: "Alice", role: "ADMIN" },
});
// => role is "ADMIN" (default value "USER" is not applied)
```
## Validation of Default Values
Values produced as defaults go through the same validation as values written directly in `data`. If a default returns something a cell cannot hold, a `GassmaInvalidValueError` is thrown and no rows are written.
```ts
const gassma = new Gassma.GassmaClient({
defaults: {
Users: { age: () => NaN },
},
});
gassma.Users.createMany({ data: [{ name: "Alice" }] });
// => Invalid value for argument `age`. Expected a finite number, but received NaN.
```
Here `{argumentName}` is the column name. `NaN` / `Infinity` / `-Infinity`, invalid Dates (Invalid Date) and objects other than `Date` are covered. For details, see the [error list](/docs/reference/errors#values-a-cell-cannot-hold).
# updatedAt (@updatedAt) (https://gassma.io/en/docs/reference/config/updated-at)
Auto-set timestamps on create/update (@updatedAt)
This is the equivalent of Prisma's `@updatedAt`. It automatically sets the current timestamp on specified columns when a record is created or updated.
## Basic Usage
```ts
const gassma = new Gassma.GassmaClient({
updatedAt: {
Users: "updatedAt",
},
});
// Current timestamp is automatically set during create / update
gassma.Users.create({
data: { name: "Alice" },
});
// => { name: "Alice", updatedAt: 2026-03-14T... }
gassma.Users.update({
where: { name: "Alice" },
data: { name: "Bob" },
});
// => { name: "Bob", updatedAt: 2026-03-14T... } (automatically updated)
```
## Multiple Columns
You can specify multiple columns using an array.
```ts
const gassma = new Gassma.GassmaClient({
updatedAt: {
Posts: ["updatedAt", "lastModified"],
},
});
```
## Applicable Methods
| Method | Applied |
| --- | --- |
| `create` / `createMany` / `createManyAndReturn` | ✅ |
| `update` / `updateMany` / `updateManyAndReturn` | ✅ |
| `upsert` (both create and update) | ✅ |
## Behavior with Explicit Values
If a value is explicitly specified by the user, the explicit value takes priority.
## Notes
`updatedAt` is not applied during cascading updates from `onDelete` / `onUpdate` (same behavior as Prisma).
# ignore / ignoreSheets (@ignore / @@ignore) (https://gassma.io/en/docs/reference/config/ignore)
Exclude fields or whole sheets from all operations (@ignore / @@ignore)
This is the equivalent of Prisma's `@ignore` (field-level) and `@@ignore` (model-level).
## ignore (Field-Level)
Completely excludes specified fields from all operations.
```ts
const gassma = new Gassma.GassmaClient({
ignore: {
Users: ["secretColumn", "internalData"],
},
});
// Excluded from read results
gassma.Users.findMany({});
// => [{ id: 1, name: "Alice" }] (secretColumn, internalData are not included)
// Also excluded from write data
gassma.Users.create({
data: { name: "Alice", secretColumn: "xxx" },
});
// => secretColumn is ignored
```
For a single column, you can specify it as a string.
```ts
ignore: {
Users: "secretColumn",
}
```
### Where Exclusion Applies
- **Read results**: Return values of find / create / update / delete / upsert
- **Write data**: data of create / createMany / upsert
- **where conditions**: Also excluded from where clauses
### Difference from Global omit
| | `ignore` | Global `omit` |
| --- | --- | --- |
| Override | Not possible | Can be disabled with `omit: \{field: false\}` |
| Write exclusion | ✅ | ❌ (read only) |
| where exclusion | ✅ | ❌ |
## ignoreSheets (Model-Level)
Completely excludes specified sheets from the client.
```ts
const gassma = new Gassma.GassmaClient({
ignoreSheets: ["Logs", "Temp"],
});
// gassma.Logs → undefined (excluded)
// gassma.Users → available as usual
```
For a single sheet, you can specify it as a string.
```ts
ignoreSheets: "Logs",
```
# map / mapSheets (@map / @@map) (https://gassma.io/en/docs/reference/config/map)
Map code-side names to spreadsheet headers and sheet names (@map / @@map)
This is the equivalent of Prisma's `@map("name")` (field-level) and `@@map("name")` (model-level). It maps names in your code to names in the spreadsheet.
## map (Field-Level)
Maps field names in your code to different header names in the spreadsheet.
```ts
const gassma = new Gassma.GassmaClient({
map: {
Users: {
firstName: "名前",
lastName: "名字",
},
},
});
// Use English names in your code
gassma.Users.create({
data: { firstName: "Alice", lastName: "Smith" },
});
// → Written to the "名前" and "名字" columns in the spreadsheet
gassma.Users.findFirst({
where: { firstName: "Alice" },
});
// => \{ firstName: "Alice", lastName: "Smith" \}
```
### Where Conversion Applies
- **Write data**: Converts code names to header names in create / createMany / update / updateMany / upsert
- **Read results**: Converts header names to code names in return values of find / create / update / upsert
- **where conditions**: Converts code names to header names before filtering
## mapSheets (Model-Level)
Maps model names in your code to sheet names in the spreadsheet.
```ts
const gassma = new Gassma.GassmaClient({
mapSheets: {
Users: "ユーザー一覧",
Posts: "投稿データ",
},
});
// Access using English names in your code
gassma.Users.findMany({});
// → Internally operates on the sheet named "ユーザー一覧"
```
### Combining with Other Options
When `mapSheets` is specified, other options (`omit`, `defaults`, `updatedAt`, `ignore`, `map`, etc.) should use the code names.
```ts
const gassma = new Gassma.GassmaClient({
mapSheets: {
Users: "ユーザー一覧",
},
defaults: {
Users: { role: "USER" }, // ← Use "Users", not "ユーザー一覧"
},
});
```
# autoincrement (https://gassma.io/en/docs/reference/config/autoincrement)
Auto-increment fields using LockService + PropertiesService (GAS only)
This is the equivalent of Prisma's `autoincrement()`. It automatically assigns unique, monotonically increasing values during `create` operations.
## Basic Usage
```ts
const gassma = new Gassma.GassmaClient({
autoincrement: {
Users: "id",
},
});
// id is automatically assigned during create
gassma.Users.create({
data: { name: "Alice" },
});
// => \{ id: 1, name: "Alice" \}
gassma.Users.create({
data: { name: "Bob" },
});
// => \{ id: 2, name: "Bob" \}
```
## Multiple Columns
You can specify multiple columns using an array.
```ts
autoincrement: {
Users: ["id", "seq"],
}
```
## Behavior with createMany
With `createMany`, counters for all rows are reserved at once before being assigned to each row.
```ts
gassma.Users.createMany({
data: [{ name: "Alice" }, { name: "Bob" }],
});
// => id: 1, 2 are assigned respectively
```
## How It Works
1. Exclusive control via `LockService.getScriptLock().waitLock(10000)`
2. Read the counter from `PropertiesService.getScriptProperties()`
3. Increment by +1 (+N for createMany) and write back
4. Release the lock
This feature only works in the GAS environment because it uses GAS's `LockService` and `PropertiesService`.
## Behavior with Explicit Values
If a value is explicitly specified for a field, auto-increment is skipped.
```ts
gassma.Users.create({
data: { id: 100, name: "Alice" },
});
// => id is 100 (auto-increment is not applied)
```
# strictUndefinedChecks / Gassma.skip (https://gassma.io/en/docs/reference/config/strict-undefined-checks)
This feature corresponds to Prisma's strictUndefinedChecks (Preview feature) and Prisma.skip. It detects unintended undefined values that slip into query inputs as runtime errors, and lets you explicitly omit a field with Gassma.skip.
This feature corresponds to Prisma's `strictUndefinedChecks` (Preview feature) and `Prisma.skip`. It detects unintended `undefined` values that slip into query inputs as runtime errors, and lets you explicitly omit a field with `Gassma.skip`.
## Gassma.skip
`Gassma.skip` is a symbol that, when passed as a field value in a query, treats that field as "not specified".
```ts
const search: string | undefined = getSearchWord();
const users = gassma.Users.findMany({
where: {
// If search is missing, the name condition itself is omitted
name: search ?? Gassma.skip,
},
});
```
`Gassma.skip` can always be used regardless of whether `strictUndefinedChecks` is enabled. It is available anywhere in query inputs, including `where` / `data` / `create` / `update` / `select` / `omit` / `orderBy`.
## Enabling strictUndefinedChecks
`strictUndefinedChecks` is an opt-in feature. There are two ways to enable it.
### Enable via previewFeatures (with CLI)
If you are doing [local development with a Prisma schema](/docs/reference/type-generation), add `previewFeatures` to the `generator` block in `schema.prisma` (same syntax as Prisma).
```prisma
generator client {
provider = "prisma-client-js"
output = "./generated/gassma"
previewFeatures = ["strictUndefinedChecks"]
}
```
When you run `npx gassma generate`, `strictUndefinedChecks: true` is automatically embedded into the generated client. The generated type definitions also accept `Gassma.skip` (via `Gassma.SkipValue`).
### Enable via the constructor (without CLI)
In a setup without the CLI, specify it in the `GassmaClient` constructor.
```ts
const gassma = new Gassma.GassmaClient({
strictUndefinedChecks: true,
});
```
## Behavior When Enabled
When enabled, a `GassmaUndefinedValueError` is thrown if a query input contains an **explicit `undefined`**. Nested inputs (Nested Writes, `select` inside `include`, etc.) are checked recursively.
```ts
const userName = undefined;
gassma.Users.deleteMany({
where: { name: userName },
});
// => GassmaUndefinedValueError:
// Invalid value for argument `where.name`: explicitly `undefined` values are not allowed.
```
When disabled (the default), `undefined` is treated as "this field was not specified", as described below. An unintended `undefined` slipping into a query can silently drop a condition and widen the affected rows (in the example above, the `deleteMany` would delete every row). With this feature enabled, such bugs are detected immediately at runtime.
If you want to omit a field, use `Gassma.skip` instead of `undefined`.
```ts
gassma.Users.deleteMany({
where: { name: userName ?? Gassma.skip },
});
// Executed with the name condition omitted
```
## Behavior When Disabled (Default)
When `strictUndefinedChecks` is disabled, an `undefined` in a query input is treated as **"this field was not specified"**, same as Prisma. This applies everywhere in query inputs: `where` conditions, inside operators (`equals` / `gt` / `in`, etc.), inside `AND` / `OR` / `NOT`, relation filters, `orderBy`, `select` keys, and so on.
```ts
// The age condition is treated as "not specified", so every row is returned
gassma.Users.findMany({
where: { age: undefined },
});
```
Likewise, passing `undefined` in `update`'s `data` means **that field is not updated** (the cell value is preserved).
```ts
gassma.Users.update({
where: { id: 1 },
data: { name: undefined, age: 21 },
});
// => name keeps its original value; only age is updated to 21
```
If the `where` conditions become empty because they consisted only of `undefined` (or `Gassma.skip`), `findMany` / `updateMany` / `deleteMany` and similar operations target **every row**. For the single-row operations `update` / `delete` / `upsert`, an empty `where` throws a `GassmaInvalidValueError` (see [update](/docs/reference/crud/update/update)).
## Recommendation: exactOptionalPropertyTypes
To fully forbid assigning `undefined` at the type level as well, we recommend enabling `exactOptionalPropertyTypes` in your project's `tsconfig.json` (same as Prisma).
```json
{
"compilerOptions": {
"exactOptionalPropertyTypes": true
}
}
```
With this setting, code that explicitly passes `undefined` to an optional field becomes a compile error.
## Not Usable Inside Arrays
`Gassma.skip` cannot be passed as an array element. A `GassmaSkipInArrayError` is thrown regardless of whether `strictUndefinedChecks` is enabled. Use `null` or filter it out of the array beforehand.
```ts
gassma.Users.findMany({
where: {
id: { in: [1, Gassma.skip, 3] },
},
});
// => GassmaSkipInArrayError:
// Invalid value for argument `where.id.in[1]`: Can not use `Gassma.skip` value
// within array. Use `null` or filter out `Gassma.skip` values.
```
The same applies to `undefined` as an array element. If an array such as `in` / `notIn` / `AND` / `OR` / `NOT` / `orderBy` / `distinct` contains an `undefined` element, a `GassmaUndefinedValueError` is thrown regardless of whether `strictUndefinedChecks` is enabled (same behavior as Prisma).
```ts
gassma.Users.findMany({
where: {
id: { in: [1, undefined, 3] },
},
});
// => GassmaUndefinedValueError:
// Invalid value for argument `where.id.in[1]`: explicitly `undefined` values are not allowed.
```
## Validation
| Error | Cause |
| --- | --- |
| `GassmaUndefinedValueError` | An explicit `undefined` is specified in a query input while `strictUndefinedChecks` is enabled. For array elements, `undefined` throws whether enabled or not |
| `GassmaSkipInArrayError` | `Gassma.skip` is specified as an array element (occurs whether enabled or not) |
| `GassmaInvalidValueError` | The `where` of `update` / `delete` / `upsert` became empty after removing `undefined` / `Gassma.skip` |
# $extends (query) (https://gassma.io/en/docs/reference/client-extensions/query)
The query component of $extends lets you register query hooks that intercept the execution of each operation. It corresponds to Prisma's client extensions (the query component of $extends). Inside a hook you can rewrite args, transform the result, or short-circuit without running the actual operation.
The `query` component of `$extends` lets you register query hooks that intercept the execution of each operation. It corresponds to Prisma's client extensions (the `query` component of `$extends`). Inside a hook you can rewrite `args`, transform the result, or short-circuit without running the actual operation.
To add computed fields instead, see [$extends (result)](/docs/reference/client-extensions/result).
## Basic Usage
Calling `gassma.$extends({ query: {...} })` returns a **new client** with the hooks applied. The original `gassma` is left unchanged.
```ts
const extended = gassma.$extends({
query: {
Users: {
findMany({ model, operation, args, query }) {
// You can modify args before running the actual query
return query(args);
},
},
},
});
const users = extended.Users.findMany();
```
## Hook Shape
A hook is a function that receives `{ model, operation, args, query }`.
| Property | Description |
| --- | --- |
| `model` | The model name of the operation (the sheet's code name) |
| `operation` | The operation name (such as `findMany`) |
| `args` | The arguments passed to the operation |
| `query` | A function that runs the actual operation (or the next hook) |
Calling `query(args)` runs the actual operation with the given `args` and returns its result. Before calling `query`, you can rewrite `args`; you can also transform the return value of `query`, or return your own value without calling `query` to short-circuit.
```ts
const extended = gassma.$extends({
query: {
Users: {
findMany({ args, query }) {
const result = query(args); // Runs the actual findMany
return result;
},
},
},
});
```
## Target Operations
Query hooks can be registered for the following 15 operations.
| Category | Operations |
| --- | --- |
| Read | `findFirst` / `findFirstOrThrow` / `findMany` |
| Create | `create` / `createMany` / `createManyAndReturn` |
| Update | `update` / `updateMany` / `updateManyAndReturn` |
| Upsert | `upsert` |
| Delete | `delete` / `deleteMany` |
| Aggregation | `count` / `aggregate` / `groupBy` |
## Structure
`query` is specified in the form "model name → operation name → hook". In addition to a specific model and operation, you can use `$allOperations` to target all operations within a model, and `$allModels` to target all models.
```ts
const extended = gassma.$extends({
query: {
// A specific operation on a specific model
Users: {
findMany({ args, query }) {
return query(args);
},
// All operations within the model
$allOperations({ operation, args, query }) {
return query(args);
},
},
// All models
$allModels: {
// A specific operation across all models
findMany({ model, args, query }) {
return query(args);
},
// All operations across all models
$allOperations({ model, operation, args, query }) {
return query(args);
},
},
},
});
```
## Composition Order
When multiple hooks match a single operation, they are not overwritten; instead, **all of them are chained**. The order of the chain is as follows:
- **The earlier an extension is applied, the more outer it is** (the later an extension is applied, the more inner it is, closer to the actual operation).
- Within a single extension, **the more specific a hook is, the more outer it is**. The priority is `model.operation` > `model.$allOperations` > `$allModels.operation` > `$allModels.$allOperations`.
When an outer hook calls `query(args)`, the next inner hook runs, and when the innermost hook calls `query(args)`, the actual operation runs.
```ts
const extended = gassma
.$extends({
query: {
Users: {
findMany({ args, query }) {
Logger.log("A: outer");
return query(args);
},
},
},
})
.$extends({
query: {
Users: {
findMany({ args, query }) {
Logger.log("B: inner");
return query(args);
},
},
},
});
extended.Users.findMany();
// Log output: "A: outer" → "B: inner" → the actual findMany
```
## Runs Synchronously
Because it runs on GAS, query hooks are executed **synchronously**. `query(args)` returns the result itself synchronously, not a Promise. No `async` / `await` is needed.
```ts
const extended = gassma.$extends({
query: {
Users: {
findMany({ args, query }) {
const result = query(args); // The result is returned synchronously
return result;
},
},
},
});
```
## args Is Passed by Reference
`args` is passed to the hook **by reference** (it is not deep cloned). If you mutate `args` destructively, it also affects the object held by the caller. If you want to modify `args`, we recommend building a new object and passing it to `query`.
```ts
const extended = gassma.$extends({
query: {
Users: {
findMany({ args, query }) {
// Bad: mutating the passed args directly
// args.where = { ...args.where, deleted: false };
// Good: build a new object and pass it
const nextArgs = Object.assign({}, args, {
where: Object.assign({}, args.where, { deleted: false }),
});
return query(nextArgs);
},
},
},
});
```
## Chainable
`$extends` can be called repeatedly. Each call returns a new client.
```ts
const extended = gassma.$extends(extensionA).$extends(extensionB);
```
## Internal Relations of include Do Not Go Through Hooks
Query hooks only target the **operation invoked at the top level**. The retrieval of related records resolved internally by `include` does not go through the hooks.
```ts
const extended = gassma.$extends({
query: {
Posts: {
findMany({ args, query }) {
// For include: { posts: true } on Users.findMany,
// this Posts.findMany hook is not called
return query(args);
},
},
},
});
```
## Practical Examples
### Soft Delete
Inject a default condition into the `where` of `findMany` to exclude deleted records by default.
```ts
const extended = gassma.$extends({
query: {
Users: {
findMany({ args, query }) {
const nextArgs = Object.assign({}, args, {
where: Object.assign({ deleted: false }, args.where),
});
return query(nextArgs);
},
},
},
});
```
### Audit Log
Use `$allOperations` under `$allModels` to record logs before and after every operation on every model.
```ts
const extended = gassma.$extends({
query: {
$allModels: {
$allOperations({ model, operation, args, query }) {
Logger.log(`${model}.${operation} start`);
const result = query(args);
Logger.log(`${model}.${operation} done`);
return result;
},
},
},
});
```
# $extends (result) (https://gassma.io/en/docs/reference/client-extensions/result)
The result component of $extends lets you add computed fields to the records in query results. It corresponds to Prisma's client extensions (the result component of $extends). You can compute new fields from existing scalar fields and include them in the result.
The `result` component of `$extends` lets you add **computed fields** to the records in query results. It corresponds to Prisma's client extensions (the `result` component of `$extends`). You can compute new fields from existing scalar fields and include them in the result.
To intercept the query execution itself, see [$extends (query)](/docs/reference/client-extensions/query).
## Basic Usage
Calling `gassma.$extends({ result: {...} })` returns a **new client with a new result type** that includes the computed fields. The original `gassma` is left unchanged.
A computed field is defined as a pair of `needs` (the scalar fields required for the computation) and `compute` (the computation function).
```ts
const extended = gassma.$extends({
result: {
Users: {
greeting: {
needs: { name: true },
compute(user) {
return `Hi ${user.name}`;
},
},
},
},
});
const user = extended.Users.findFirst({ where: { id: 1 } });
user.greeting; // "Hi Alice"
```
## needs and compute
| Key | Description |
| --- | --- |
| `needs` | Declares the **scalar fields** required for the computation, as `{ fieldName: true }` |
| `compute` | Receives a record containing only the fields declared in `needs`, and returns the computed value |
The record passed to `compute` contains only the fields declared in `needs`, and its **type is derived from `needs` as well**. No type annotation is needed.
```ts
const extended = gassma.$extends({
result: {
Users: {
greeting: {
needs: { name: true },
// user is typed as { name: string }
compute(user) {
return `Hi ${user.name}`;
},
},
},
},
});
```
Only **scalar fields** can be specified in `needs`. Relations cannot be specified.
## Added as Plain Properties
Computed fields are not getters; they are added to the result as **plain properties computed on the spot**. As a result, they behave stably with `Logger.log` / `JSON.stringify` / the spread syntax.
```ts
const user = extended.Users.findFirst({ where: { id: 1 } });
Logger.log(user.greeting); // "Hi Alice"
JSON.stringify(user); // includes greeting
const copy = { ...user }; // copy.greeting is preserved
```
## Operations That Get Computed Fields
Computed fields are added to the results of **operations that return records**.
| Added | Not added |
| --- | --- |
| `findFirst` / `findFirstOrThrow` / `findMany` | `count` / `aggregate` / `groupBy` |
| `create` / `createManyAndReturn` | `createMany` |
| `update` / `updateManyAndReturn` | `updateMany` |
| `upsert` / `delete` | `deleteMany` |
They are not added to `createMany` / `updateMany` / `deleteMany`, which return only counts, nor to the aggregations `count` / `aggregate` / `groupBy`.
## Overriding Existing Fields
If you define a computed field with the same name as an existing field, you can **override** its value.
```ts
const extended = gassma.$extends({
result: {
Users: {
// Replace the existing name with an uppercase version
name: {
needs: { name: true },
compute(user) {
return user.name.toUpperCase();
},
},
},
},
});
```
## Dependencies Between Computed Fields
A computed field can depend on another computed field. Put the dependency in `needs`.
```ts
const extended = gassma
.$extends({
result: {
Users: {
fullName: {
needs: { firstName: true, lastName: true },
compute(user) {
return `${user.firstName} ${user.lastName}`;
},
},
},
},
})
.$extends({
result: {
Users: {
greeting: {
needs: { fullName: true }, // Depends on another computed field
compute(user) {
return `Hi ${user.fullName}`;
},
},
},
},
});
```
For a dependency **across chained `$extends` (a separate `$extends` call)**, the type of the dependency's value is fully applied as well.
On the other hand, if you make computed fields depend on each other **within the same `$extends` call**, it works at runtime, but if the dependency's `compute` has no parameter annotation, the type of the dependency value is **not applied** (it becomes `never`). If you also need the type, add a parameter annotation to the dependency's `compute`, or split them into chained `$extends`. This is a TypeScript limitation, and Prisma behaves the same way.
## Adding to All Models with $allModels
With `$allModels`, you can add a computed field common to all models. If a computed field with the same name is also defined for a specific model, the model-specific one takes precedence.
```ts
const extended = gassma.$extends({
result: {
$allModels: {
fetchedAt: {
compute() {
return new Date();
},
},
},
},
});
```
## Working with select / omit
If you specify `select`, only the **selected computed fields** are included in the result. If you do not specify `select`, all computed fields are included.
```ts
const user = extended.Users.findFirst({
where: { id: 1 },
select: { greeting: true }, // Only greeting is returned
});
```
You can also exclude computed fields with `omit`.
```ts
const user = extended.Users.findFirst({
where: { id: 1 },
omit: { greeting: true }, // Excludes greeting
});
```
The scalar fields specified in `needs` are read internally for `compute` even if they are not selected with `select` or are excluded with `omit` (compute still works correctly).
## Added to Nested include As Well
Computed fields are also added to related records retrieved with `include`. When nested deeply, they are added to the records at each level.
```ts
const result = extended.Users.findMany({
include: {
posts: true, // Each post also gets the computed fields of Posts
},
});
```
For details on `include`, see [include](/docs/reference/relation/include).
## Combining with query
`query` and `result` can be specified together.
```ts
const extended = gassma.$extends({
query: {
Users: {
findMany({ args, query }) {
return query(args);
},
},
},
result: {
Users: {
greeting: {
needs: { name: true },
compute(user) {
return `Hi ${user.name}`;
},
},
},
},
});
```
## Limitations
Computed fields cannot be used in `where` / `orderBy` / aggregations (`count` / `aggregate` / `groupBy`). Also, only scalar fields can be specified in `needs` (relations are not allowed).
## Practical Examples
### fullName
Add a `fullName` that joins `firstName` and `lastName` (when the Users sheet has `firstName` / `lastName` columns).
```ts
const extended = gassma.$extends({
result: {
Users: {
fullName: {
needs: { firstName: true, lastName: true },
compute(user) {
return `${user.firstName} ${user.lastName}`;
},
},
},
},
});
const user = extended.Users.findFirst({ where: { id: 1 } });
user.fullName; // "Alice Smith"
```
### Derived Field
Add a value derived from an existing field.
```ts
const extended = gassma.$extends({
result: {
Posts: {
excerpt: {
needs: { content: true },
compute(post) {
return post.content.slice(0, 20);
},
},
},
},
});
```
# $transaction (Transactions) (https://gassma.io/en/docs/reference/transaction)
Commit all writes inside a callback at once with $transaction. On error, not a single cell is written. maxWait / timeout / rollback options
`$transaction` lets you run multiple operations together as a single transaction. It corresponds to Prisma's interactive transactions (the callback form of `$transaction`).
Write operations inside the callback are not applied to the sheets immediately; they are **written to the sheets all at once when the callback finishes successfully** (commit). If the callback throws, **not a single cell is written** to the sheets.
## Basic Usage
Call it as `gassma.$transaction((tx) => {...})`. The callback receives a transaction client `tx`, and the callback's return value is returned as the return value of `$transaction`.
```ts
const user = gassma.$transaction((tx) => {
const created = tx.Users.create({
data: { id: 1, name: "Tanaka" },
});
tx.Posts.create({
data: { id: 10, title: "Hello", authorId: created.id },
});
return created;
});
// Users and Posts are written together once the callback finishes successfully
```
Because it runs on GAS, unlike Prisma, `$transaction` executes **synchronously**. Neither the callback nor the return value of `$transaction` is a Promise. No `async` / `await` is needed.
## Cancel Everything with throw
If an error is thrown in the middle of the callback, all writes so far are discarded and nothing is applied to the sheets.
```ts
try {
gassma.$transaction((tx) => {
tx.Users.create({ data: { id: 1, name: "Tanaka" } });
tx.Posts.create({ data: { id: 10, title: "Hello", authorId: 1 } });
throw new Error("cancel");
});
} catch (e) {
// Not a single row has been written to Users or Posts
}
```
## The tx Client
`tx` exposes the same models (sheets) as the regular client. `$extends` is also available, letting you build an extended client that applies only within the transaction.
```ts
gassma.$transaction((tx) => {
const extended = tx.$extends({
query: {
Users: {
findMany({ args, query }) {
return query(args);
},
},
},
});
return extended.Users.findMany();
});
```
On the other hand, `tx` has no `$transaction` (it does not exist on the type either). Nested transactions are not supported; calling `$transaction` inside a transaction throws `GassmaNestedTransactionError`.
### Reads Inside a Transaction (read-your-writes)
Reads inside a transaction (`findMany` / `include` / relation filters, etc.) **see the uncommitted changes**. Clients outside the transaction, on the other hand, do not see the changes until they are committed.
```ts
gassma.$transaction((tx) => {
tx.Users.create({ data: { id: 1, name: "Tanaka" } });
// Reads inside tx see the uncommitted changes
const found = tx.Users.findFirst({ where: { id: 1 } }); // found
// Clients outside tx do not see them until commit
const outside = gassma.Users.findFirst({ where: { id: 1 } }); // null
});
```
## Options
Options can be passed as the second argument.
```ts
gassma.$transaction(
(tx) => {
// ...
},
{ maxWait: 10000, timeout: 120000, rollback: false },
);
```
| Option | Default | Description |
| --- | --- | --- |
| `maxWait` | `20000` (ms) | Maximum time to wait for the transaction to start (lock acquisition). Throws `GassmaTransactionLockTimeoutError` when exceeded |
| `timeout` | `60000` (ms) | Maximum execution time for the whole transaction. Throws `GassmaTransactionTimeoutError` when exceeded |
| `rollback` | `true` | Whether to enable the commit-time backup and automatic restore on failure (see [below](#rollback)) |
### maxWait
A transaction starts only after acquiring a script lock. If another execution holds the same lock (such as another running `$transaction`), it waits up to `maxWait` milliseconds for the lock to be released, and throws `GassmaTransactionLockTimeoutError` if it still cannot be acquired.
### timeout
When the elapsed time since the transaction started exceeds `timeout` milliseconds, `GassmaTransactionTimeoutError` is thrown. The check is cooperative: the elapsed time is checked **when each tx operation is called** and **right before commit**.
Because the check is cooperative, an overrun cannot be detected in the middle of code that does not call tx methods (such as a long computation loop). Even in that case, it is detected at the next tx operation or at the check right before commit.
### rollback
With `rollback: true` (the default), a commit proceeds as follows.
1. Duplicate the sheets to be written into temporary backups (hidden sheets named `_gassma_tx_...`)
2. Write to the sheets
3. On success, automatically delete the backups
If the write fails partway through, the sheets are **automatically restored from the backups** and the original error is re-thrown. The sheets are restored on the spot, and formula cells are preserved. If even the restore fails, `GassmaTransactionRollbackError` is thrown **with the backup sheets left in place**. The error's `backupSheetNames` property contains the list of remaining backup sheet names, from which you can recover manually.
Specifying `{ rollback: false }` skips this mechanism entirely (faster). However, if the write fails partway through, the sheets may be left in a partially written state.
With `rollback` enabled, the cost of duplicating the backups (`copyTo`) is **proportional to the size of the sheets being written**, and it also consumes GAS's 6-minute execution limit. For transactions on large sheets or high-frequency transactions, consider `rollback: false`.
## Limitations
- The lock only serializes work that goes through GASsma. It cannot stop manual edits to the spreadsheet or changes from other scripts that do not use GASsma. See [Write Atomicity and Concurrency](/docs/reference/write-atomicity) for what can happen under concurrent access.
- **The lock belongs to the GASsma library, not to your project.** GASsma runs as a library, so the lock `$transaction` takes is the library's own and is shared with every script project that uses GASsma. If a transaction is running in someone else's project, yours waits for it.
- If the execution is forcibly terminated (such as by GAS's 6-minute execution limit), backup sheets may be left behind. A warning is logged the next time `$transaction` runs. Leftover `_gassma_tx_...` sheets can be deleted manually after checking their contents.
- Only cell values and formulas are restored (formatting and the like are not).
- Settings changed at runtime with [changeSettings](/docs/reference/settings/changeSettings) are not carried over into the transaction.
- The form that takes an array of operations (Prisma's sequential operations) is not supported. Only the callback form is available.
- `isolationLevel` is not supported (execution is always serialized by the lock).
## Related Errors
For details on `GassmaTransactionLockTimeoutError` / `GassmaTransactionTimeoutError` / `GassmaNestedTransactionError` / `GassmaTransactionRollbackError`, see the [Error List](/docs/reference/errors).
# Local Development with Prisma Schema (https://gassma.io/en/docs/reference/type-generation)
Generate a type-safe client from a Prisma-format schema with the gassma CLI (generate/init/validate/format) and gassma.config.ts
GASsma provides the ability to auto-generate type-safe client code from Prisma-format schema files when developing GAS locally using TypeScript with tools such as clasp+esbuild.
By using this feature, settings such as relation definitions, defaults, and map are auto-generated from the schema, eliminating the need to manually write GASsma-specific constructor options (`relations`, `defaults`, `updatedAt`, `ignore`, `map`, etc.). As long as you know Prisma's schema syntax, you can start developing with the same workflow as Prisma.
**Without CLI (manual configuration):**
```ts
import { Gassma } from "gassma";
const gassma = new Gassma.GassmaClient({
id: "SPREAD_SHEET_ID",
relations: {
User: {
posts: { type: "oneToMany", to: "Post", field: "id", reference: "authorId", onDelete: "Cascade" },
},
Post: {
author: { type: "manyToOne", to: "User", field: "authorId", reference: "id" },
},
},
defaults: { User: { role: "USER" } },
updatedAt: { Post: "updatedAt" },
map: { User: { firstName: "名前" } },
});
```
**With CLI (auto-generated from schema):**
```ts
import { GassmaClient } from "./generated/gassma/schemaClient";
// Relations, defaults, updatedAt, map, etc. are all auto-injected
const gassma = new GassmaClient();
```
## Prerequisites
Install the GASsma CLI tool with the following command:
```
$ npm i gassma
```
## Creating a Schema File
Create a `.prisma` file in your project. By default, the `./gassma` directory is searched.
```
my-project/
├── gassma/
│ └── schema.prisma ← Write your schema here
├── package.json
└── ...
```
### Basic Syntax
Define models using Prisma's syntax. Specify the output directory in the `generator` block's `output` field.
```prisma
generator client {
provider = "prisma-client-js"
output = "./generated/gassma"
}
model User {
id Int @id
name String
email String?
age Int
}
```
### previewFeatures
You can enable opt-in features by specifying `previewFeatures` in the `generator` block (same syntax as Prisma's `previewFeatures`).
```prisma
generator client {
provider = "prisma-client-js"
output = "./generated/gassma"
previewFeatures = ["strictUndefinedChecks"]
}
```
Currently supported features:
| Feature | Description | Reference |
| --- | --- | --- |
| `strictUndefinedChecks` | Turns explicit `undefined` in query inputs into runtime errors | [strictUndefinedChecks / Gassma.skip](/docs/reference/config/strict-undefined-checks) |
When enabled, `strictUndefinedChecks: true` is embedded into the generated client JS, and the generated type definitions also accept `Gassma.skip`.
### Type Mapping
Prisma types are converted to the following TypeScript types:
| Prisma Type | TypeScript Type |
| --- | --- |
| `Int` | `number` |
| `Float` | `number` |
| `Decimal` | `number` |
| `BigInt` | `number` |
| `String` | `string` |
| `Boolean` | `boolean` |
| `DateTime` | `Date` |
| `Json` | `string` |
| `Bytes` | `string` |
Adding `?` makes the field optional (`null` is allowed).
### Relation Definitions
Using Prisma's `@relation` attribute, relation information is automatically extracted and injected into the generated client.
```prisma
generator client {
provider = "prisma-client-js"
output = "./generated/gassma"
}
model User {
id Int @id
name String
posts Post[]
}
model Post {
id Int @id
title String
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
authorId Int
}
```
The following relation settings are auto-generated from the above definition:
- `User.posts`: oneToMany (User -> Post)
- `Post.author`: manyToOne (Post -> User, onDelete: Cascade)
#### Implicit Many-to-Many
When bidirectional array references exist, an implicit Many-to-Many relation is automatically detected.
```prisma
model Post {
id Int @id
tags Tag[]
}
model Tag {
id Int @id
name String
posts Post[]
}
```
In the generated client, the junction table name is automatically resolved as `_PostToTag` (model names in alphabetical order). If you name the relation like `@relation("PostTags")`, the relation name is used as the junction table name (`_PostTags`) — the same rule as Prisma.
The junction table (sheet) itself can be created automatically with [migrate / db push](/docs/reference/migrate) (you can also prepare a sheet with the same name manually in the spreadsheet).
If you want to change the junction table name, give the relation a name with `@relation`.
### enum
Literal union types are auto-generated from Prisma's `enum` definitions.
```prisma
enum Role {
ADMIN
USER
MODERATOR
}
model User {
id Int @id
role Role
}
```
Generated type:
```ts
"role": "ADMIN" | "USER" | "MODERATOR"
```
#### enum @map
Adding `@map` to enum members allows you to map between the name used in code and the value stored in the spreadsheet.
```prisma
enum Role {
admin @map("ADMIN")
user @map("USER")
moderator @map("MODERATOR")
}
```
Generated constant:
```ts
const Role = {
admin: "ADMIN",
user: "USER",
moderator: "MODERATOR",
} as const;
```
The `@map` values are used in the type definition:
```ts
"role": "ADMIN" | "USER" | "MODERATOR"
```
### @gassma.addType
By writing `@gassma.addType` in a Prisma field comment (`///`), you can add union types to the field's type.
```prisma
model User {
/// @gassma.addType string
id Int @id // Generated type: number | string
/// @gassma.addType string, boolean
score Int // Generated type: number | string | boolean
name String // Generated type: string (normal when no comment)
}
```
### @gassma.replaceType
While `@gassma.addType` creates a union with the base type, `@gassma.replaceType` replaces the base type and generates only the specified types.
```prisma
model User {
/// @gassma.replaceType "admin", "user", "moderator"
role String
}
```
Generated type:
```ts
"role": "admin" | "user" | "moderator" // Does not include string
```
Priority: enum > replaceType > addType. When an enum exists, replaceType / addType are ignored.
### @default
Fields with `@default()` become optional (`?`) in the generated Create input type.
```prisma
model User {
id Int @id @default(autoincrement())
name String
isActive Boolean @default(true)
createdAt DateTime @default(now())
}
```
Generated type:
```ts
"isActive"?: boolean // @default(true) -> Optional
"createdAt"?: Date // @default(now()) -> Optional
```
The defaults settings are automatically embedded in the generated client JS.
| `@default()` | Generated JS |
| --- | --- |
| `@default(true)` / `@default(false)` | `true` / `false` |
| `@default(0)` (number) | `0` |
| `@default("USER")` (string) | `"USER"` |
| `@default(ADMIN)` (enum value) | `"ADMIN"` |
| `@default(active)` (enum value with `active @map("ACTIVE")`) | `"ACTIVE"` (the `@map`-applied value) |
| `@default(now())` | `() => new Date()` |
| `@default(uuid())` | `() => Utilities.getUuid()` |
| `@default(autoincrement())` | Generated separately as an autoincrement setting |
### @updatedAt
Fields with `@updatedAt` become optional in the Create input type, and the updatedAt setting is embedded in the generated client JS.
```prisma
model Post {
id Int @id
title String
updatedAt DateTime @updatedAt
}
```
### @ignore
Fields with `@ignore` are completely excluded from the type definition, and the ignore setting is embedded in the generated client JS.
```prisma
model User {
id Int @id
name String
secret String @ignore // Not included in the type definition
}
```
### @map
`@map("name")` defines a field name mapping. The map setting is embedded in the generated client JS.
```prisma
model User {
id Int @id
firstName String @map("名前")
lastName String @map("名字")
}
```
In code, you work with `firstName` / `lastName`, which correspond to the columns named "名前" and "名字" in the spreadsheet.
### @@ignore
The model-level `@@ignore` excludes an entire sheet. The ignoreSheets setting is embedded in the generated client JS.
```prisma
model Logs {
id Int @id
message String
@@ignore
}
```
### @@map
The model-level `@@map("name")` maps a sheet name.
```prisma
model Users {
id Int @id
name String
@@map("ユーザー一覧")
}
```
In code, you access it as `Users`, which corresponds to the sheet named "ユーザー一覧" in the spreadsheet.
## CLI Commands
### gassma generate
Generates type files and client code.
```
$ npx gassma generate
```
By default, `.prisma` files in the `./gassma` directory are searched. You can specify a particular schema file or directory using the `--schema` option (equivalent to Prisma's `prisma generate --schema`).
```
$ npx gassma generate --schema gassma/user.prisma
$ npx gassma generate --schema ./schemas
```
The `--watch` option monitors schema file changes and automatically regenerates.
```
$ npx gassma generate --watch
```
It can also be combined with `--schema`.
The `--config` option lets you explicitly specify the path to the config file (equivalent to Prisma's `--config`).
```
$ npx gassma generate --config configs/gassma.config.ts
```
If the specified file does not exist, a `ConfigFileNotFoundError` is thrown. When omitted, the default locations are searched as before (see "Config File Search Rules" below).
### gassma init
Initializes a project and auto-generates a schema file and configuration file.
```
$ npx gassma init
```
The following files are generated:
- `gassma/schema.prisma` -- Initial schema
- `gassma.config.ts` -- Configuration file
| Option | Description |
| --- | --- |
| `--output ` | Customize the output path |
| `--with-model` | Generate a schema with a sample User model |
If `schema.prisma` already exists, it safely stops with an error.
### gassma validate
Performs syntax checking and consistency checking of the schema file (equivalent to Prisma's `prisma validate`).
```
$ npx gassma validate
```
```
$ npx gassma validate --schema gassma/test.prisma
```
You can also specify the path to the config file with the `--config` option.
Check items:
- Syntax errors (parser error detection)
- `generator` block existence check
- `output` field required check
- At least one model is defined
On success, the following is output:
```
The schema at /path/to/gassma/test.prisma is valid 🚀
```
### gassma format
Formats `.prisma` files with the same formatting as the official Prisma formatter (uses `@prisma/internals`' `formatSchema`).
```
$ npx gassma format
```
| Option | Description |
| --- | --- |
| `--schema ` | Format only a specific file |
| `--config ` | Specify the path to the config file |
| `--check` | Check if already formatted (for CI; exits with code 1 if unformatted) |
### gassma studio
Opens the spreadsheet configured in `datasource` in your OS's default browser.
```
$ npx gassma studio
```
| Option | Description |
| --- | --- |
| `--config ` | Specify the path to the config file |
The URL is resolved in the following order:
1. The `url` of the `datasource` block in the schema
2. `datasource.url` in `gassma.config.ts`
If `url` is a full URL (`https://...`), it is opened as-is; if it is a spreadsheet ID, `https://docs.google.com/spreadsheets/d//edit` is constructed and opened. If neither has a URL set, a `NoDatasourceUrlError` occurs.
### gassma version
Displays the GASsma CLI version.
```
$ npx gassma version
```
You can also check with the `--version` / `-V` flag.
| Option | Description |
| --- | --- |
| `--json` | Output version information as JSON |
With `--json`, the version is output as JSON (`{"gassma":""}`).
```
$ npx gassma version --json
{"gassma":"1.2.3"}
```
### Generated Files
The following files are generated based on the schema file name. For example, for `schema.prisma`:
| File | Content |
| --- | --- |
| `schema.d.ts` | Type definitions (model types, query types, common types) |
| `schemaClient.js` | Client implementation (with auto-injected relation definitions) |
| `schemaClient.d.ts` | Client type definitions |
The output directory is the directory specified by the `generator` block's `output`.
## Using the Generated Client
Import `GassmaClient` from the generated client file and use it directly. Relation definitions are auto-injected.
```ts
import { GassmaClient } from "./generated/gassma/schemaClient";
const gassma = new GassmaClient();
// Access sheets with type safety
const users = gassma.User.findMany({
where: { age: { gte: 20 } },
select: { name: true, email: true },
});
```
You can instantiate it using the same pattern as Prisma.
```ts
// Prisma
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
// GASsma (same pattern)
import { GassmaClient } from "./generated/gassma/schemaClient";
const gassma = new GassmaClient();
```
### Initialization with Options
```ts
// Specify spreadsheet ID
const gassma = new GassmaClient("SPREAD_SHEET_ID");
// Options object
const gassma = new GassmaClient({
id: "SPREAD_SHEET_ID",
omit: {
User: { password: true },
},
});
```
## Configuration File (gassma.config.ts)
By placing `gassma.config.ts` at the project root, you can centrally manage CLI settings (equivalent to Prisma's `prisma.config.ts`). Extensions other than TypeScript (`.js` / `.mjs` / `.cjs` / `.mts` / `.cts`) and placement in the `.config/` directory are also supported (see "Config File Search Rules" below).
### Configuration Interface
There are two ways to write the configuration file.
**1. Using the `defineConfig` helper (recommended):**
```ts
import { defineConfig } from "gassma/config";
export default defineConfig({
schema: "gassma/schema.prisma",
datasource: {
url: "https://docs.google.com/spreadsheets/d/XXXXX/edit",
},
});
```
**2. Using the `satisfies` operator:**
```ts
import type { GassmaConfig } from "gassma";
export default {
schema: "gassma/schema.prisma",
datasource: {
url: "https://docs.google.com/spreadsheets/d/XXXXX/edit",
},
} satisfies GassmaConfig;
```
The `GassmaConfig` type can be imported from the root of the `gassma` package.
### Configuration Options
| Option | Type | Required | Description |
| --- | --- | --- | --- |
| `schema` | `string` | No | Path to the schema file or directory (default: `./gassma`) |
| `datasource.url` | `string` | No | Spreadsheet URL or ID |
### Config File Search Rules
The config file is searched in the following order, and the **first file found** is used:
1. `gassma.config.js`
2. `gassma.config.ts`
3. `gassma.config.mjs`
4. `gassma.config.cjs`
5. `gassma.config.mts`
6. `gassma.config.cts`
7. `.config/gassma.js`
8. `.config/gassma.ts`
9. `.config/gassma.mjs`
10. `.config/gassma.cjs`
11. `.config/gassma.mts`
12. `.config/gassma.cts`
All extensions of `gassma.config.*` directly under the project root are searched first, followed by `gassma.*` in the `.config/` directory. This is the same order as Prisma's config file search, including `.js` taking precedence over `.ts`.
### --config Option
The `generate` (including `--watch`), `validate`, `format`, and `studio` commands accept the `--config` option to explicitly specify the path to the config file (equivalent to Prisma's `--config`).
```
$ npx gassma generate --config configs/gassma.config.ts
```
- Relative paths are resolved from the current working directory.
- If the specified file does not exist, a `ConfigFileNotFoundError` is thrown.
- When omitted, the default locations are searched according to the search rules above.
### Load Behavior
When running `gassma generate`, the following is displayed when the config file is loaded successfully:
```
⚙️ Loaded config from gassma.config.ts
```
- If the config file has a syntax or runtime error, or if a known key (`schema` / `datasource.url`) has an invalid type, a `GassmaConfigLoadError` is thrown.
- If the config contains unknown keys, a warning is displayed and those keys are ignored (no error is thrown).
```
Warning: Unknown property `outut` in /path/to/gassma.config.ts. Known properties are: schema, datasource. It will be ignored.
```
### env() Helper
Using the `env()` function, you can retrieve the spreadsheet URL from an environment variable (equivalent to Prisma's `env()`).
```ts
import "dotenv/config";
import { defineConfig, env } from "gassma/config";
export default defineConfig({
schema: "gassma",
datasource: {
url: env("SPREADSHEET_URL"),
},
});
```
It can also be used with the `satisfies` pattern.
```ts
import "dotenv/config";
import type { GassmaConfig } from "gassma";
import { env } from "gassma/config";
export default {
schema: "gassma",
datasource: {
url: env("SPREADSHEET_URL"),
},
} satisfies GassmaConfig;
```
#### Typed env()
By passing an interface of your environment variables as a type argument, the names you can pass to `env()` are restricted to its keys, and you get autocompletion.
```ts
import "dotenv/config";
import { defineConfig, env } from "gassma/config";
interface Env {
SPREADSHEET_URL: string;
}
export default defineConfig({
schema: "gassma",
datasource: {
url: env("SPREADSHEET_URL"),
},
});
```
Only keys whose values are of type `string` (or `string | undefined`) can be specified. Specifying a nonexistent key results in a compile error.
`env()` throws a `GassmaConfigEnvError` if the environment variable is not set or is an empty string. For optional environment variables, use `process.env` directly.
### datasource.url
When you specify a spreadsheet URL or ID in `datasource.url`, the `id` is automatically embedded in the generated client JS. This allows you to connect to the target spreadsheet with just `new GassmaClient()`.
Both full URLs and spreadsheet IDs are supported.
```ts
// Full URL
datasource: {
url: "https://docs.google.com/spreadsheets/d/XXXXX/edit",
}
// Direct ID specification
datasource: {
url: "XXXXX",
}
```
### datasource Block in Schema
You can also specify the URL by writing a `datasource` block in the schema file.
```prisma
datasource db {
provider = "google-spreadsheet"
url = "https://docs.google.com/spreadsheets/d/XXXXX/edit"
}
```
#### URL Resolution Priority
1. `datasource` block in the schema (highest priority)
2. `datasource.url` in `gassma.config.ts`
### Schema Resolution Priority
1. `--schema` option (highest priority)
2. `schema` setting in `gassma.config.ts`
3. Default `./gassma` directory
Relative paths are resolved from different base directories: the `--schema` option is resolved from the current working directory, while `schema` in the config file is resolved **relative to the location of the config file** (same as Prisma).
Running `gassma init` also auto-generates `gassma.config.ts`.
## Multi-file Schema
When you place multiple `.prisma` files in the same directory (and subdirectories), they are automatically **merged into a single schema**. This is equivalent to Prisma's [Multi-file schema](https://www.prisma.io/docs/orm/prisma-schema/overview/location#multi-file-prisma-schema) feature.
```
gassma/
├── schema.prisma ← Write the generator block here
├── models/
│ ├── user.prisma ← User, Profile models
│ └── post.prisma ← Post, Comment models
```
The `generator` block only needs to be written in one file and is shared across all files. All models are consolidated into a single client output.
## Multiple Schemas (Multiple Spreadsheets)
When working with different spreadsheets, separate schemas into different directories and generate them individually. Type names are prefixed with the schema name, so there are no conflicts even with models of the same name.
```
schemas/
├── user/
│ └── schema.prisma → userClient.js, user.d.ts
└── order/
└── schema.prisma → orderClient.js, order.d.ts
```
```ts
import { GassmaClient as UserClient } from "./generated/user/schemaClient";
import { GassmaClient as OrderClient } from "./generated/order/schemaClient";
const userGassma = new UserClient();
const orderGassma = new OrderClient();
```
## Overview of Generated Types
The generated `.d.ts` includes the following types:
- **Model types**: Type definitions for each field (`GassmaUserUse`, etc.)
- **Query types**: `FindData`, `CreateData`, `UpdateData`, `DeleteData`, `UpsertData`, etc.
- **Select / Omit types**: Types for field selection and exclusion
- **Filter types**: `WhereUse`, `FilterConditions` (including `FieldRef` support)
- **OrderBy types**: Sort conditions (relation sort, `_count` sort, nulls control included)
- **Include types**: Types for relation fetching (including `_count`)
- **Nested Write types**: Create, connect, update, and delete operations for related records
- **Numeric operation types**: `NumberOperation` (increment / decrement / multiply / divide)
- **Common types**: `FieldRef`, `GassmaClientOptions`, error class group
- **Configuration types**: `DefaultsConfig`, `UpdatedAtConfig`, `IgnoreConfig`, `AutoincrementConfig`, `MapConfig`, etc.
- **Controller types**: Argument and return value types for all methods
# Write Atomicity and Concurrency (https://gassma.io/en/docs/reference/write-atomicity)
Nested writes and cascades buffer their writes across multiple sheets; on error, not a single row is written. Cases this guarantee does not cover (API failures, concurrent edits) and how $transaction helps
[Nested writes](/docs/reference/relation/nested-write) (such as `posts: { create: [...] }` inside a `create`) and `Cascade` in [onDelete](/docs/reference/relation/on-delete) / [onUpdate](/docs/reference/relation/on-update) **write to multiple sheets** in a single operation. This page explains what these operations guarantee — and what they do not.
## Nothing Is Written on Error
Operations that write to multiple sheets buffer their writes internally and **apply them to the sheets all at once when the whole operation succeeds**. If an error occurs partway through, **not a single row is written**.
```ts
// The child create fails
gassma.Users.create({
data: {
id: 4,
name: "Dave",
posts: {
create: [{ id: 4, titel: "..." }], // error: misspelled column
},
},
});
// → Error. Nothing is written to Users or Posts
```
This is the same guarantee Prisma provides by wrapping nested writes in an implicit transaction.
## Cases This Guarantee Does Not Cover
### The Spreadsheet API Fails During the Write
If the Google Sheets API fails while the buffered writes are being flushed to the sheets, **the writes made up to that point remain on the sheets**.
Using [$transaction](/docs/reference/transaction) with `rollback: true` (the default) restores the sheets from a backup taken before the write (see [rollback](/docs/reference/transaction#rollback) for details).
### Another Process or Person Modifies the Sheet Mid-Operation
GASsma identifies the rows it updates or deletes **by position (row number)**. If someone else **inserts or deletes rows** between the moment GASsma reads the target rows and the moment it writes, GASsma may **write to a different row than intended**.
```
When GASsma reads: row 1 Alice / row 2 Bob / row 3 Carol
→ "update Carol on row 3"
Someone deletes row 1: row 1 Bob / row 2 Carol
When GASsma writes: writes to row 3 → now empty, or a different row
```
Because multi-sheet operations buffer their writes, **the window between reading and writing is longer** for them. Keep this in mind in environments where writes can happen concurrently.
## Wrap Writes in $transaction When Concurrent Writes Are Possible
In a system where writes can happen concurrently, wrap them in [$transaction](/docs/reference/transaction).
```ts
gassma.$transaction((tx) => {
tx.Users.create({
data: {
id: 4,
name: "Dave",
posts: {
create: [{ id: 4, title: "Dave's post", published: true }],
},
},
});
});
```
`$transaction` acquires a lock, so **writes that go through GASsma are serialized with each other**. The read-then-write window described above can no longer be interrupted by another GASsma write.
This lock only serializes **operations that use GASsma**. It has no effect on:
- **a person editing the spreadsheet by hand**
- **another script writing to the sheet without GASsma**
GASsma cannot do anything about these. If the sheets may be edited by hand while your operations run, the protection has to come from the design side — for example, **arranging things so the sheets are simply not touched concurrently**, or **running your operations during hours when edits are not accepted**.
# bootstrap (Local Development Environment Setup) (https://gassma.io/en/docs/reference/bootstrap)
Set up a local development environment with clasp + esbuild + TypeScript + GASsma in one shot with npx gassma bootstrap
`npx gassma bootstrap` is a command that sets up a local GAS development environment (clasp + esbuild + TypeScript + GASsma library) for a new project in one shot.
```
$ npx gassma bootstrap my-app # create my-app/ and set up inside it
$ npx gassma bootstrap # ask for a directory first (default: gassma-project)
$ npx gassma bootstrap . # set up in the current directory
```
Since it runs via `npx`, no prior installation is required. When executed, it walks you through interactive questions and completes everything from creating the target directory and the Apps Script project to build configuration, schema file generation, and dependency installation.
The target directory is created by the command itself, so you do not need to prepare one in advance. Passing `.` sets up in the existing current directory. Existing files are skipped or merged instead of overwritten (see "Directory Handling" and "Idempotency" below).
## Prerequisites
[clasp](https://github.com/google/clasp) must be installed and you must be logged in.
```
$ npm install -g @google/clasp
$ clasp login
```
Also, enable the Apps Script API on the [Apps Script API settings page](https://script.google.com/home/usersettings) (clasp needs it to create projects).
If clasp is not found, the command shows the following message and exits safely (no files are modified).
```
clasp is required but was not found in your PATH.
Install it with: npm install -g @google/clasp
Then log in with: clasp login
```
## Interactive Flow
You will be asked the following questions in order (the prompts are the actual wording).
### 1. Project directory?
The directory to set up the project in. Defaults to `gassma-project`; entering `.` sets up in the current directory. A directory that does not exist is created, and if it exists and is not empty, a confirmation is asked before continuing (see "Directory Handling" below).
If you pass a directory as a command argument (`npx gassma bootstrap my-app` or `npx gassma bootstrap .`), this question is skipped.
### 2. Project title?
The title of the Apps Script project to create. Defaults to the target directory name (the name of the directory given as the argument or in question 1).
### 3. Create a new spreadsheet as well?
With **Yes** (the default), a new spreadsheet is created together with a container-bound script attached to it (`clasp create-script --type sheets`). With **No**, a standalone script is created (`--type standalone`).
After this, `clasp create-script` runs and generates `.clasp.json` (with `rootDir` set to `./dist`). Then the GASsma library dependency and other settings are automatically applied to `dist/appsscript.json` (see "What Gets Generated" below).
If `.clasp.json` already exists, this question and `clasp create-script` are skipped (`Found an existing .clasp.json. Skipping clasp create-script.`).
### 4. Function exposure style?
Choose how functions are exposed to GAS. Sample code for each style is shown before the choices.
**export (recommended)** — Uses `@gassma/gas-esbuild-plugin`; exported functions become GAS global functions as-is.
```ts
export const main = () => console.log("Hello GAS!");
```
**global** (esbuild-gas-plugin style) — Uses `esbuild-gas-plugin`; functions are exposed by assigning to the `global` object.
```ts
const main = () => console.log("Hello GAS!");
interface Global {
main: typeof main;
}
declare const global: Global;
global.main = main;
```
The plugin in the generated `esbuild.mjs` and the devDependencies in `package.json` are switched according to the selected style.
### 5. Linter and formatter setup?
Choose the linter and formatter setup.
| Choice | Description |
| --- | --- |
| `oxlint + oxfmt` | recommended (the default) |
| `eslint + prettier` | ESLint (typescript-eslint) + Prettier |
| `none` | No linter or formatter |
With `--yes`, the default `oxlint + oxfmt` is selected. The devDependencies in `package.json`, the `lint` / `lint:fix` / `format` / `format:check` scripts, and the generated config files change according to the choice (see "Linter and Formatter Choice" below).
### 6. Generate a sample src/index.ts?
With **Yes** (the default), sample code for the selected style is generated as `src/index.ts`.
### 7. Install dependencies now?
With **Yes** (the default), dependencies are installed with the detected package manager (e.g. `Install dependencies now? (npm install)`). This question is not asked when `--skip-install` is specified.
## What Gets Generated
| File | Description |
| --- | --- |
| `.clasp.json` | Generated by `clasp create-script` (`rootDir: ./dist`) |
| `dist/appsscript.json` | GASsma library dependency, `timeZone`, `exceptionLogging: STACKDRIVER`, and `runtimeVersion: V8` are set automatically |
| `package.json` | `build` / `push` / `open` / `deploy` scripts and dependencies (plus lint / format scripts depending on question 5) |
| `esbuild.mjs` | Build configuration for the selected style |
| `tsconfig.json` | TypeScript configuration for GAS (`@types/google-apps-script`) |
| `.gitignore` | `.clasp.json` / `.clasprc.json` / `.env` / `node_modules/` / `dist/*` (except `dist/appsscript.json`) |
| `.oxlintrc.json` | oxlint configuration (only if you chose `oxlint + oxfmt` in question 5) |
| `eslint.config.mjs` / `.prettierrc` | ESLint / Prettier configuration (only if you chose `eslint + prettier` in question 5) |
| `src/index.ts` | Sample code (only if you answered Yes to question 6) |
| `AGENTS.md` | Project guidance for coding agents (commands, development flow, constraints, and a pointer to the GASsma reference) |
| `gassma/schema.prisma` / `gassma.config.ts` | Equivalent to `gassma init` (schema with a sample User model and the config file) |
### dist/appsscript.json
The `timeZone` is detected automatically from your environment (falls back to `America/New_York` if detection fails). The GASsma library is added as the following entry.
```json
{
"userSymbol": "Gassma",
"libraryId": "1ZVuWMUYs4hVKDCcP3nVw74AY48VqLm50wRceKIQLFKL0wf4Hyou-FIBH",
"version": "",
"developmentMode": false
}
```
If the existing manifest already has `exceptionLogging` or `runtimeVersion` set, those values are preserved.
### package.json Scripts
| Script | Description |
| --- | --- |
| `build` | `node esbuild.mjs` (bundles `src/index.ts` into `dist/index.js`) |
| `push` | `clasp push` |
| `open` | `clasp open-script` |
| `deploy` | `npm run build && npm run push` |
If you chose `oxlint + oxfmt` or `eslint + prettier` in question 5, `lint` / `lint:fix` / `format` / `format:check` are added on top of these (see the next section for their contents).
Note that in a newly generated `package.json`, `devDependencies` are written out sorted alphabetically (formatter checks expect them to be sorted).
### Linter and Formatter Choice
The devDependencies, npm scripts, and config files that get added depend on your answer to question 5.
#### oxlint + oxfmt (recommended)
`oxlint@^1.76.0` and `oxfmt@^0.61.0` are added to devDependencies.
| Script | Description |
| --- | --- |
| `lint` | `oxlint` |
| `lint:fix` | `oxlint --fix` |
| `format` | `oxfmt` |
| `format:check` | `oxfmt --check` |
`.oxlintrc.json` is generated.
```json
{
"plugins": ["typescript"],
"categories": { "correctness": "error" },
"ignorePatterns": ["dist/**", "src/generated/**"]
}
```
#### eslint + prettier
`eslint@^10.8.0` / `eslint-config-prettier@^10.1.8` / `prettier@^3.9.6` / `typescript-eslint@^8.65.0` are added to devDependencies.
| Script | Description |
| --- | --- |
| `lint` | `eslint .` |
| `lint:fix` | `eslint . --fix` |
| `format` | `prettier --write .` |
| `format:check` | `prettier --check .` |
`eslint.config.mjs` and `.prettierrc` are generated.
```js
import { defineConfig, globalIgnores } from "eslint/config";
import prettier from "eslint-config-prettier/flat";
import tseslint from "typescript-eslint";
export default defineConfig([
globalIgnores(["dist/**", "src/generated/**"]),
{
files: ["**/*.ts"],
extends: [tseslint.configs.recommended, prettier],
},
]);
```
```json
{
"semi": true,
"singleQuote": false,
"trailingComma": "all"
}
```
#### none
No devDependencies, scripts, or config files are added.
The above is all bootstrap sets up. The following are **not** included (add them yourself if you need them).
- pre-commit hooks (husky / lint-staged and the like)
- oxlint type-aware linting (`oxlint-tsgolint`)
- an oxfmt config file (it is used with its defaults)
### About .gitignore
`.clasp.json` and `.clasprc.json` are gitignored following the official clasp CI guide (they contain credentials and the script ID). When sharing the project with your team, restore them from your team's secret store. The same guidance is shown when setup completes.
```
Note: .clasp.json is gitignored. Restore it from your team's secret store when sharing this project.
```
## Arguments and Options
| Argument | Description |
| --- | --- |
| `[directory]` | Directory to set up the project in (`.` for the current directory). Omitting it prompts for one |
| Option | Description |
| --- | --- |
| `--yes` | Answer all prompts with their default values (non-interactive mode). Without an argument, `./gassma-project` is created and set up, and the non-empty directory confirmation is answered with continue |
| `--skip-install` | Skip dependency installation |
| `--dry-run` | Show planned actions without writing files, creating directories, or running commands (directory creation is also shown as a plan entry like `create directory my-app`) |
In a non-interactive terminal (such as CI), `--yes` is required. Without it, the command exits with
`An interactive terminal is required. Run with --yes for non-interactive mode.`
## Behavior Details
### Directory Handling
- If the specified directory does not exist, it is created and the project is set up inside it.
- If it exists and is not empty, `Directory "my-app" is not empty. Continue?` (default **No**) is asked. Choosing No exits safely with `Bootstrap cancelled.` without changing anything. With `--yes`, the command continues automatically.
- Because of this, an interrupted setup can be resumed simply by running the same command again and answering Yes to the confirmation (already generated files are skipped/merged as described in "Idempotency" below).
- If a **non-directory file** with the same name already exists, the command exits with the error `"my-app" already exists and is not a directory.`
### Idempotency
The command is designed to be safe to re-run.
- If `.clasp.json` exists, `clasp create-script` is skipped.
- `esbuild.mjs` / `tsconfig.json` / `src/index.ts` / `gassma/schema.prisma` / `AGENTS.md` are skipped if they already exist.
- The linter / formatter config files (`.oxlintrc.json` / `eslint.config.mjs` / `.prettierrc`, generated only for the choice made in question 5) are likewise skipped if they already exist.
- If `package.json` already exists, the bootstrap settings are **merged** into it (existing values win).
- If `.gitignore` already exists, only the missing entries are appended.
- If `dist/appsscript.json` already has the GASsma library entry, it is not added twice.
### GASsma Library Version Resolution
The latest GASsma library version written to `dist/appsscript.json` is resolved automatically at run time.
1. Fetch the latest library version number via `clasp list-versions`
2. If that fails, fetch it from GASsma's `package.json` on GitHub
If both fail (e.g. offline), adding the library entry is skipped and manual instructions for adding it in the Apps Script editor (including the script ID) are shown. Re-running `gassma bootstrap` while online adds the entry automatically.
### Package Manager Auto-Detection
npm / pnpm / yarn / bun are detected automatically (falling back to npm), and the install command and the completion guidance reflect the detected package manager.
## Next Steps After Setup
When setup completes, the following steps are shown.
1. Edit `gassma/schema.prisma` to define your models
2. Run `npx gassma generate` to generate the typed client
3. Run `npm run deploy` to build and push to Apps Script
4. Run `npm run open` to open the project in the Apps Script editor
For how to write schemas and the details of `gassma generate`, see [Local Development with Prisma Schema](/docs/reference/type-generation).
# fields (Column Comparison) (https://gassma.io/en/docs/reference/fields)
Compare columns within the same row in where filters using FieldRef
Use the `fields` property within `where` conditions when you want to compare against **the value of another column in the same row** instead of a fixed value.
## Basic Usage
Obtain a `FieldRef` from the `fields` property of each sheet controller and pass it as a value in filter conditions.
```ts
const gassma = new Gassma.GassmaClient();
const userSheet = gassma.Users;
// Search for users where firstName equals lastName
const result = userSheet.findMany({
where: {
firstName: { equals: userSheet.fields.lastName },
},
});
```
The above example compares `firstName` and `lastName` values for each row, returning only rows where they match.
## Available Operators
`FieldRef` can be used with the following operators:
| Operator | Description | Example |
| --- | --- | --- |
| equals | Equal to | `{ equals: sheet.fields.otherColumn }` |
| lt | Less than | `{ lt: sheet.fields.maxValue }` |
| lte | Less than or equal to | `{ lte: sheet.fields.maxValue }` |
| gt | Greater than | `{ gt: sheet.fields.minValue }` |
| gte | Greater than or equal to | `{ gte: sheet.fields.minValue }` |
| contains | Contains the string | `{ contains: sheet.fields.keyword }` |
| startsWith | Starts with the string | `{ startsWith: sheet.fields.prefix }` |
| endsWith | Ends with the string | `{ endsWith: sheet.fields.suffix }` |
`FieldRef` cannot be used with `not`, `in`, or `notIn`.
## Numeric Comparison
```ts
// Search for users where age is less than maxAge
const result = userSheet.findMany({
where: {
age: { lt: userSheet.fields.maxAge },
},
});
```
## String Comparison
```ts
// Search for records where fullName contains the firstName value
const result = userSheet.findMany({
where: {
fullName: { contains: userSheet.fields.firstName },
},
});
```
## Combining with mode: "insensitive"
`FieldRef` can be combined with `mode: "insensitive"` for case-insensitive comparison:
```ts
const result = userSheet.findMany({
where: {
firstName: {
equals: userSheet.fields.lastName,
mode: "insensitive",
},
},
});
```
## Usage with AND / OR / NOT
`FieldRef` can also be used within logical operators:
```ts
const result = userSheet.findMany({
where: {
OR: [
{ firstName: { equals: userSheet.fields.lastName } },
{ age: { gt: userSheet.fields.minAge } },
],
},
});
```
## Supported Methods
`fields` can be used with all methods that support `where`:
- `findMany` / `findFirst` / `findFirstOrThrow`
- `update` / `updateMany` / `updateManyAndReturn`
- `delete` / `deleteMany`
- `upsert`
- `count` / `aggregate` / `groupBy`
## When Referenced Column Doesn't Exist
If the column name specified in `FieldRef` does not exist in the sheet, the condition will not match (no error is thrown).
# migrate / db push (Syncing Sheets) (https://gassma.io/en/docs/reference/migrate)
Generate a GAS function that syncs your spreadsheet's sheets and columns with the schema using npx gassma migrate / npx gassma db push
`npx gassma migrate` and `npx gassma db push` are commands that generate a GAS function that syncs your spreadsheet's sheets and columns with the Prisma schema (the counterparts of Prisma's `prisma migrate dev` / `prisma db push`).
```
$ npx gassma migrate # generate and record a trail (migrations/)
$ npx gassma migrate --name add_tags # name the trail entry
$ npx gassma db push # generate without recording a trail
```
The only difference between the two commands is **whether a trail (the `migrations/` directory) is recorded**. `migrate` leaves a `migration.js` under `migrations/` every time the schema changes, while `db push` never touches `migrations/`. The generated runnable stub is identical for both. Use `db push` when you do not need a migration history.
The commands themselves do not access the spreadsheet. The sheets are synced the moment you run the generated `gassmaMigrate` function once on the Apps Script side. `clasp push` is not run automatically either (see "After Generating" below).
## What Gets Generated
A runnable stub `gassma-migration.js` (plain JS) is generated in the output directory. Its content is a single `gassmaMigrate` function that calls the GASsma library's `migrateSheets`, with the sheet and column names extracted from the schema embedded in it.
```prisma
model User {
id Int @id
name String
posts Post[]
}
model Post {
id Int @id
title String
author User @relation(fields: [authorId], references: [id])
authorId Int
}
```
The schema above generates the following.
```js
function gassmaMigrate() {
Gassma.migrateSheets({
spreadsheetId: "XXXXX",
models: [
{ name: "User", columns: ["id", "name"] },
{ name: "Post", columns: ["id", "title", "authorId"] }
]
});
}
```
- The spreadsheet ID is resolved from the `datasource` block in the schema first, then from `datasource.url` in `gassma.config.ts`. If neither is set, it is not embedded and the spreadsheet bound to the script is targeted at runtime.
- Running the function requires the GASsma library to be registered in the GAS project (an environment set up with [bootstrap](/docs/reference/bootstrap) works as-is). The call symbol (the `Gassma.` part in the example) is automatically resolved from the `userSymbol` of the library registered in `appsscript.json` in the output directory (falling back to `Gassma` when not found).
### Output Directory Resolution
1. `--output ` (highest priority)
2. `rootDir` in `.clasp.json` in the current directory
If neither is available, a `MigrateOutputDirError` is raised.
### Sheet and Column Extraction Rules
- One sheet per model. When `@@map` / `@map` are used, the mapped physical names are used.
- Only scalar fields become columns. Relation fields (`posts` / `author` in the example above) do not become columns, while foreign key columns (`authorId`) do.
- Fields and models with `@ignore` / `@@ignore` are **also included as creation targets**. As in Prisma, they are only excluded from the client and still exist physically in the spreadsheet.
- Junction sheets for [implicit Many-to-Many](/docs/reference/type-generation#implicit-many-to-many) relations are also created (e.g. `_PostToTag`, with columns `postId`, `tagId` in alphabetical order of the model names).
## After Generating
Run the two Next steps shown on success, and the sheets get synced.
```
✅ Migration generated
Next steps:
1. Run "clasp push" (or "npm run push") to upload gassma-migration.js
2. In the Apps Script editor, run the "gassmaMigrate" function once
```
Push with `clasp push` directly (or `npm run push`, which only calls `clasp push`). A full clean build (such as `npm run deploy` generated by bootstrap) may wipe the output directory and delete `gassma-migration.js` before it is pushed.
## Sync Rules
The sync performed by `gassmaMigrate` (`Gassma.migrateSheets`) is idempotent and safe to run any number of times.
- Sheets that are in the schema but not in the spreadsheet are created, with the headers written to row 1.
- On existing sheets, only the missing columns are appended to the right end of the header row. **Existing columns are never reordered**, and data rows are never written to.
- Columns and sheets that are not in the schema are not deleted by default; a warning is logged and they are left untouched.
```
Gassma.migrateSheets: column "legacy" on sheet "User" is not in the schema. It is left untouched.
```
## Deleting Data (--accept-data-loss)
Pass `--accept-data-loss` to delete columns and sheets that are not in the schema. `acceptDataLoss: true` is embedded in the stub, and the deletion is performed when `gassmaMigrate` runs.
Columns and sheets that still contain data are deleted after a warning that reports how much is left (the number of non-empty cells for a column, the number of data rows for a sheet). Empty ones are deleted without a warning.
```
Gassma.migrateSheets: You are about to drop the column "legacy" on the sheet "User", which still contains 12 non-empty values.
```
## Migration Trail (migrations/)
`migrate` creates `[_name]/migration.js` under the `migrations/` directory next to the schema. Its content is identical to the runnable stub.
```
gassma/
├── schema.prisma
└── migrations/
├── 20260801120000_init/
│ └── migration.js
└── 20260802093000_add_tags/
└── migration.js
```
- If the content is the same as the latest trail entry, no new entry is created (`Already in sync, no schema change or pending migration was found.` is shown). The runnable stub itself is rewritten every time.
- `--name` names the trail entry. The name is sanitized by splitting camelCase, lowercasing, and replacing runs of non-alphanumeric characters with `_` (e.g. `--name "Add UserRole!"` → `20260801120000_add_user_role`).
## Options
### migrate
| Option | Description |
| --- | --- |
| `--name ` | Name for the new migration |
| `--output ` | Directory to write `gassma-migration.js` (defaults to `rootDir` in `.clasp.json`) |
| `--schema ` | Path to a specific `.prisma` file to migrate from |
| `--config ` | Custom path to your GASsma config file |
| `--accept-data-loss` | Delete sheets and columns that are not in the schema |
### db push
| Option | Description |
| --- | --- |
| `--output ` | Directory to write `gassma-migration.js` (defaults to `rootDir` in `.clasp.json`) |
| `--schema ` | Path to a specific `.prisma` file to push from |
| `--config ` | Custom path to your GASsma config file |
| `--accept-data-loss` | Delete sheets and columns that are not in the schema |
## Limitations
- The header row is assumed to be **row 1 starting at column A** on every sheet. Header positions moved via [changeSettings](/docs/reference/settings/changeSettings) are not supported.
- Since a spreadsheet must contain at least one sheet, the last remaining sheet is never deleted even with `--accept-data-loss`; only a warning is logged.
## Gassma.migrateSheets (Library API)
`Gassma.migrateSheets`, which the stub calls, is a public GASsma API. You can also call it directly with the sheets and columns you want to sync, without using the CLI.
```ts
Gassma.migrateSheets({
spreadsheetId: "SPREAD_SHEET_ID", // defaults to the bound spreadsheet
models: [{ name: "User", columns: ["id", "name"] }],
acceptDataLoss: false,
});
```
`models` is required; omitting it raises a `GassmaMissingArgumentError`. The sync rules and limitations are the same as when going through the CLI.
# raw (Writing Formulas) (https://gassma.io/en/docs/reference/raw)
Use Gassma.raw to opt out of formula-injection escaping per cell and write a formula as-is
`Gassma.raw(value)` is a helper that skips the automatic formula-injection escaping for that cell only and writes the value to the sheet **as-is**. When you pass a string starting with `=`, the cell holds a **live spreadsheet formula**.
## Default Protection (Automatic Escaping)
When writing, GASsma escapes any string starting with `=`, `+`, `-`, or `@` by prepending a `'` (single quote). This prevents strings like `=IMPORTRANGE(...)` slipped into form input from being executed as formulas (formula injection).
- Only **strings** are escaped. Numbers, booleans, and Dates are written unchanged. However, `NaN` / `Infinity` / `-Infinity` and invalid Dates (Invalid Date) are rejected with a `GassmaInvalidValueError` before escaping — the write itself fails.
- The `'` does not appear in the spreadsheet's display or when reading, so reading an escaped cell back through GASsma returns the original string (e.g. `"=1+2"`).
Since this protection is always on, it gets in the way when you intentionally want to write an aggregation formula. `Gassma.raw` is the opt-out for that.
## Basic Usage
Wrap a `data` value in `Gassma.raw()` and that cell alone skips escaping. The main use case is writing an aggregation formula into a number column.
```ts
const gassma = new Gassma.GassmaClient();
gassma.Report.create({
data: {
title: userInput, // escaped as usual
total: Gassma.raw("=SUM(B2:B10)"), // this cell alone is written as a formula
},
});
```
Within the same write, columns that do not use `Gassma.raw()` (like `title` above) remain protected as before.
It can be used with all `create` and `update` methods (`create` / `createMany` / `createManyAndReturn` / `update` / `updateMany` / `updateManyAndReturn` / `upsert`), and with nested write `create` / `createMany` / `connectOrCreate`'s `create`.
## The Return Value Is Not the Computed Result
The return value of `create` / `update` etc. is an **echo of what was written**. GASsma does not re-read the sheet after writing, so the **computed result of the formula is not returned**. If you write a formula into a number column, the return value is the formula **string**.
```ts
const created = gassma.FormulaCell.create({
data: { id: 4, label: "delta", amount: 60, total: Gassma.raw("=C5*2") },
});
created.total; // "=C5*2" (the formula string, not the computed result)
```
If you need the computed result, read the record back after writing.
```ts
const readBack = gassma.FormulaCell.findFirstOrThrow({ where: { id: 4 } });
readBack.total; // 120 (the result computed on the cell)
```
## The Effect of raw Is Limited to That One Cell
The effect of `Gassma.raw()` is limited to **the exact cell** it was passed for.
- Other columns in the same row are escaped as usual.
- Other writes referencing a raw cell — such as FK values handed down to child rows in nested writes — are never treated as raw either.
```ts
gassma.FormulaCell.create({
data: {
id: 4,
label: "=1+2", // escaped; stays the literal string "=1+2"
amount: 60,
total: Gassma.raw("=C5*2"), // written as a formula and computed on the cell
},
});
```
## Combining with $transaction
It can also be used inside [$transaction](/docs/reference/transaction). Raw values are written to the sheet together at commit time, and become formulas at that point.
Reads inside the tx before commit (read-your-writes) still see the **formula string**, because nothing has been written to the sheet yet.
```ts
gassma.$transaction((tx) => {
tx.FormulaCell.create({
data: { id: 4, label: "delta", amount: 60, total: Gassma.raw("=C5*2") },
});
const buffered = tx.FormulaCell.findFirstOrThrow({ where: { id: 4 } });
buffered.total; // "=C5*2" (not computed yet — before commit)
});
// After commit the cell holds a live formula and the computed result can be read
const readBack = gassma.FormulaCell.findFirstOrThrow({ where: { id: 4 } });
readBack.total; // 120
```
## Types
`Gassma.raw(value: string)` returns a `Gassma.RawValue`.
- In the generated types, **every column** in `data` accepts `Gassma.RawValue`. Since a formula can produce a value of any type on the cell, you can pass it to number, boolean, and Date columns as well.
- It cannot be used in `where`. It is write-only. Passing it as a `where` value throws a `GassmaInvalidValueError` (Expected a scalar value, but received a Gassma.raw value.).
## Never Use It with Untrusted Input
**Never pass user input** to `Gassma.raw()`. Because it bypasses the default protection, it makes you vulnerable to formula injection.
```ts
// DANGEROUS: passing form input straight into raw
function onFormSubmit(e) {
const gassma = new Gassma.GassmaClient();
gassma.Answers.create({
data: {
// If a malicious user enters "=IMPORTRANGE(...)",
// it will be executed as a formula
name: Gassma.raw(e.namedValues["名前"][0]),
},
});
}
```
Use `Gassma.raw()` only with **trusted values**, such as fixed formulas you wrote yourself. Pass user input as-is without wrapping it in `Gassma.raw()`, and the default protection applies.
# Error List (https://gassma.io/en/docs/reference/errors)
List of error classes thrown by GASsma and when they occur
A list of error classes that can occur in GASsma.
## Catching Errors
GASsma's error classes are exported from the `Gassma` namespace. You can catch them with `try` / `catch` and determine the error type with `instanceof`.
```ts
try {
gassma.sheet1.findFirst({ take: 5 });
} catch (e) {
if (e instanceof Gassma.GassmaFindFirstTakeError) {
// Handle an invalid take for findFirst
}
}
```
There are 51 exported error classes; together with `GassmaClient` / `GassmaController` / `FieldRef` / `skip`, the `Gassma` namespace exposes 55 public entities.
This `instanceof` limitation applies to built-in types such as `Date`, which evaluate to `false` across the library boundary (see [Basic](/docs/reference/basic)). GASsma's error classes, on the other hand, are referenced via the `Gassma` namespace (the library's global), so they can be checked correctly with `instanceof`.
## Search / Query Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `GassmaFindSelectOmitConflictError` | Cannot use both select and omit in the same query | `select` and `omit` are specified at the same time |
| `NotFoundError` | An operation failed because it depends on one or more records that were required but not found. | No record found with `findFirstOrThrow` |
| `GassmaSkipNegativeError` | Invalid value for skip argument: Value can only be positive, found: \{value\} | A **finite** negative number is specified for `skip` (also applies to `skip` in `include`). `NaN` / `Infinity` / `-Infinity` / `null` raise `GassmaInvalidValueError` instead |
| `GassmaLimitNegativeError` | Invalid value for limit argument: Value can only be positive, found: \{value\} | A **finite** negative number is specified for `limit`. `NaN` / `Infinity` / `-Infinity` / `null` raise `GassmaInvalidValueError` instead |
| `GassmaFindFirstTakeError` | The 'findFirst' operation cannot be used with a 'take' argument that isn't 1 or -1 | A value other than 1 / -1 is specified for `take` in `findFirst` (including `NaN` / `Infinity` / `-Infinity`). Only `take: null` raises `GassmaInvalidValueError` |
## strictUndefinedChecks / Gassma.skip Errors
For details, see [strictUndefinedChecks / Gassma.skip](/docs/reference/config/strict-undefined-checks).
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `GassmaUndefinedValueError` | Invalid value for argument \`\{path\}\`: explicitly \`undefined\` values are not allowed. | An explicit `undefined` is specified in a query input while `strictUndefinedChecks` is enabled. For array elements (`in` / `AND` / `OR` / `orderBy`, etc.), `undefined` throws whether enabled or not |
| `GassmaSkipInArrayError` | Invalid value for argument \`\{path\}\`: Can not use \`Gassma.skip\` value within array. Use \`null\` or filter out \`Gassma.skip\` values. | `Gassma.skip` is specified as an array element (occurs whether `strictUndefinedChecks` is enabled or not) |
## orderBy Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `RelationOrderByUnsupportedTypeError` | Cannot use orderBy on "\{relationName\}" (type: \{relationType\}). Only manyToOne and oneToOne are supported. | Field sort is used on a oneToMany / manyToMany relation |
| `RelationOrderByCountUnsupportedTypeError` | Cannot use \_count orderBy on "\{relationName\}" (type: \{relationType\}). Only oneToMany and manyToMany are supported. | `_count` sort is used on a manyToOne / oneToOne relation |
## Aggregation Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `GassmaAggregateMaxError` | Cannot produce a maximum value of more than one type. | Mixed types in `_max` |
| `GassmaAggregateMinError` | Cannot produce a maximum value of more than one type. | Mixed types in `_min` |
| `GassmaAggregateSumError` | Cannot produce a maximum value of more than one type. | Non-numeric types mixed in `_sum` |
| `GassmaAggregateAvgError` | Cannot produce a maximum value of more than one type. | Non-numeric types mixed in `_avg` |
| `GassmaAggregateTypeError` | Only "number", "string", "boolean", and "Date" types are supported. | Unsupported type in `_max` / `_min` |
| `GassmaAggregateSumTypeError` | Only "number" type is supported. | Non-numeric type in `_sum` |
| `GassmaAggregateAvgTypeError` | Only "number" type is supported. | Non-numeric type in `_avg` |
| `GassmaAggregateSelectionRequiredError` | At least one aggregation is required: specify \`_avg\`, \`_count\`, \`_max\`, \`_min\`, or \`_sum\` with at least one field. | `aggregate` is called without any of `_avg` / `_count` / `_max` / `_min` / `_sum` pointing at a field (including calls with only `where` / `orderBy` / `take`, or empty selections such as `_count: {}`) |
`GassmaAggregateMinError` / `GassmaAggregateSumError` / `GassmaAggregateAvgError` extend `GassmaAggregateMaxError`, and `GassmaAggregateAvgTypeError` extends `GassmaAggregateSumTypeError`. As a result, an `instanceof` check against a base class also catches its subclasses.
## groupBy Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `GassmaGroupByHavingDontWriteByError` | When using "having" other than "\_avg", "\_count", "\_max", "\_min", and "\_sum", column names can be used only if they are written in the "by" field. | A column not included in `by` is used in `having` |
## Configuration Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `GassmaInValidColumnValueError` | startColumnValue and endColumnValue can only use number, \[a-z\] and \[A-Z\]. | An invalid column value is specified in `changeSettings` |
## Argument Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `GassmaMissingArgumentError` | Argument \`\{argumentName\}\` is missing. | A required argument (`data` / `where` / `create` / `update` / `by`, etc.) is omitted |
| `GassmaUnknownArgumentError` | Unknown argument \`\{argumentName\}\`. Did you mean \`\{suggestion\}\`? Available: \{availableArguments\} | An unknown key is used in a query input (a top-level argument, a column name in `where` / `data` / `select` / `omit` / `orderBy`, a filter operator, an update operator such as `increment`, etc.). Also thrown when a `GassmaClient` option (`map` / `defaults` / `updatedAt` / `autoincrement` / `ignore` / `omit`) refers to a column that does not exist |
| `GassmaInvalidValueError` | Invalid value for argument \`\{argumentName\}\`. Expected \{expected\}. | An argument value has an unacceptable shape. There are many trigger conditions, so they are collected [below](#trigger-conditions-for-gassmainvalidvalueerror) |
In the `GassmaUnknownArgumentError` message, `Did you mean ...?` only appears when a close match is found, and `Available: ...` only appears when the list of candidates is non-empty.
### Trigger Conditions for GassmaInvalidValueError
The message always has the form Invalid value for argument \`\{argumentName\}\`. Expected \{expected\}.. The tables below show the `{expected}` part.
#### Arguments with an invalid shape
| Condition | `{argumentName}` | `{expected}` |
| --- | --- | --- |
| A non-array is given to `OR` / `AND` / `NOT` | `OR`, etc. | an array |
| The `orderBy` value is not `"asc"` / `"desc"` (including when an array is passed) | `orderBy` | "asc" \| "desc" |
| The `sort` in `orderBy` is not `"asc"` / `"desc"` | `sort` | "asc" \| "desc" |
| The `nulls` in `orderBy` is not `"first"` / `"last"` | `nulls` | "first" \| "last" |
| A non-object is given to a relation key in `orderBy` | the relation name | a relation orderBy object |
| `select` selects no fields at all | `select` | at least one selected field |
| `cursor` has no columns at all | `cursor` | at least one column |
| The `where` of a single-row operation (`update` / `delete` / `upsert`) has no conditions at all | `where` | at least one condition |
#### Invalid pagination values (`take` / `skip` / `limit`)
| Value | `{expected}` |
| --- | --- |
| `NaN` / `Infinity` / `-Infinity` | a finite number, but received NaN |
| `null` | a number, but received null |
`take` / `skip` apply to `findMany` / `findFirst` / `count` / `aggregate` / `groupBy`, and `limit` applies to `updateMany` / `updateManyAndReturn` / `deleteMany`. `undefined` is still treated as "not specified" and ignored.
For `take` in `findFirst`, the `1` / `-1` check runs first, so `NaN` / `Infinity` / `-Infinity` raise `GassmaFindFirstTakeError` (only `take: null` raises `GassmaInvalidValueError`). `take` / `skip` inside `include` raise `IncludeInvalidOptionTypeError`.
#### `null` where an argument expects a structure
`, but received null` is appended to `{expected}` (for example, Invalid value for argument \`where\`. Expected an object, but received null.).
| `{argumentName}` | `{expected}` |
| --- | --- |
| `where` / `cursor` / `having` / `some` / `every` / `none` / `createMany` | an object |
| `data` / `create` / `update` / `connect` / `connectOrCreate` / `set` / `deleteMany` / `updateMany` / `AND` / `OR` / `NOT` | an object or an array |
| `orderBy` | an object or an array |
| `distinct` / `by` | a field name or an array of field names |
| `disconnect` / `delete` | a boolean or an object |
| `contains` / `startsWith` / `endsWith` | a string |
| `gt` / `gte` / `lt` / `lte` | a comparable value |
| `increment` / `decrement` / `multiply` / `divide` | a number |
| A column value in `cursor` | a scalar value |
Putting `null` in an array element raises the same error (`AND: [null]`, `distinct: [null]`, `data: [null]` in `createMany`, and so on). For details, see [Handling of null](/docs/reference/crud/read/findMany#handling-of-null).
#### Values a cell cannot hold
This applies to write (`data`) and query (`where` / `cursor` / `having`) values.
| Value | `{expected}` |
| --- | --- |
| `NaN` / `Infinity` / `-Infinity` | a finite number, but received NaN |
| Invalid Date | a valid Date, but the provided Date object is invalid |
| An array | a scalar value, but received an array |
| A function | a scalar value, but received a function |
| A Symbol | a scalar value, but received a symbol |
| A BigInt | a scalar value, but received a bigint |
| Built-in objects such as `Map` / `Set` / `RegExp` / `Error` / `Promise` | a scalar value, but received a Map |
| Any other object, such as a class instance | a scalar value, but received an object |
| `Gassma.raw` (in `where` / `cursor` / `having` only) | a scalar value, but received a Gassma.raw value |
`Date`, `Gassma.raw` (in `data` only) and `FieldRef` are objects but can be passed as-is. For built-in objects, the name in the message is the internal type of the value (`Set` produces `but received a Set.`).
#### Arithmetic operation results
Raised when the **result** of `increment` / `decrement` / `multiply` / `divide` is `NaN` / `Infinity` / `-Infinity`. Here `{argumentName}` is the **column name**.
| `{expected}` |
| --- |
| a finite number, but received Infinity |
For details, see [update()](/docs/reference/crud/update/update#atomic-number-operations).
## Relation Definition Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `RelationSheetNotFoundError` | Sheet "\{sheetName\}" is not found in the spreadsheet | The sheet specified in the relation definition does not exist |
| `RelationMissingPropertyError` | Relation "\{relationName\}" on sheet "\{sheetName\}" is missing required property "\{property\}" | A required property is missing in the relation definition |
| `RelationInvalidPropertyTypeError` | Relation "\{relationName\}" on sheet "\{sheetName\}": property "\{property\}" must be a \{expectedType\} | The property type in the relation definition is invalid |
| `RelationInvalidTypeError` | Relation "\{relationName\}" on sheet "\{sheetName\}": type "\{value\}" is not valid. Must be one of: oneToMany, oneToOne, manyToOne, manyToMany | The relation `type` is invalid |
| `RelationColumnNotFoundError` | Column "\{columnName\}" is not found in sheet "\{sheetName\}" | The column specified in `field` / `reference` of the relation definition does not exist |
| `RelationInvalidOnDeleteError` | Relation "\{relationName\}" on sheet "\{sheetName\}": onDelete "\{value\}" is not valid. Must be one of: Cascade, SetNull, Restrict, NoAction | The `onDelete` value is invalid |
| `RelationInvalidOnUpdateError` | Relation "\{relationName\}" on sheet "\{sheetName\}": onUpdate "\{value\}" is not valid. Must be one of: Cascade, SetNull, Restrict, NoAction | The `onUpdate` value is invalid |
| `RelationIgnoredColumnError` | Relation "\{relationName\}" on sheet "\{sheetName\}": column "\{columnName\}" is ignored on sheet "\{ignoredSheetName\}". Ignored columns are stripped from where conditions, so relation processing (onDelete/onUpdate/nested writes) could modify all rows in sheet "\{ignoredSheetName\}". Remove "\{columnName\}" from the ignore option or remove this relation | The relation's `field` / `reference` (or `through.field` / `through.reference` for manyToMany) refers to a column listed in the `ignore` option (detected at client initialization) |
## Relation Operation Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `GassmaRelationNotFoundError` | Relation "\{relationName\}" is not defined for sheet "\{sheetName\}" | An undefined relation name is specified in `include` |
| `GassmaRelationDuplicateError` | Duplicate value "\{value\}" found in "\{sheetName\}.\{field\}" for a unique relation | Duplicate values exist in the target of a oneToOne / manyToOne relation |
| `GassmaThroughRequiredError` | Relation "\{relationName\}" is manyToMany but "through" is not defined | `through` (junction table) is not defined for a manyToMany relation |
| `RelationOnDeleteRestrictError` | Cannot delete: related records exist for relation "\{relationName\}" (onDelete: Restrict) | Attempting to delete when related records exist with `onDelete: "Restrict"` |
| `RelationOnUpdateRestrictError` | Cannot update: related records exist for relation "\{relationName\}" (onUpdate: Restrict) | Attempting to update a PK when related records exist with `onUpdate: "Restrict"` |
## include Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `IncludeWithoutRelationsError` | Cannot use include without defining relations in GassmaClient | `include` is used without defining relations |
| `GassmaIncludeSelectConflictError` | Cannot use both include and select in the same query | `include` and `select` are used simultaneously at the top level |
| `IncludeInvalidOptionTypeError` | Include "\{relationName\}": option "\{option\}" must be \{expectedType\} | The option value type in `include` is invalid |
| `IncludeSelectOmitConflictError` | Include "\{relationName\}": cannot use both select and omit at the same time | `select` and `omit` are specified simultaneously within `include` |
| `IncludeSelectIncludeConflictError` | Include "\{relationName\}": cannot use both select and include at the same time | `select` and `include` are specified simultaneously within `include` |
## where Relation Filter Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `WhereRelationInvalidFilterError` | Filter "\{filterType\}" cannot be used on relation "\{relationName\}" of type "\{relationType\}" | An inappropriate filter is used for the relation type (e.g., using `is` on a oneToMany relation) |
| `WhereRelationWithoutContextError` | Cannot use relation filters in where clause without defining relations | Relation filters are used without defining relations |
## Nested Write Errors
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `NestedWriteWithoutRelationsError` | Cannot use nested write operations without defining relations in GassmaClient | Nested Write is used without defining relations |
| `NestedWriteConnectNotFoundError` | Nested write connect failed: no record found in "\{sheetName\}" | The target record is not found with `connect` / `connectOrCreate` |
| `NestedWriteRelationNotFoundError` | Nested write failed: "\{fieldName\}" is not a defined relation | An undefined relation name is used in Nested Write |
| `NestedWriteInvalidOperationError` | Nested write: operation "\{operation\}" is not valid for relation "\{relationName\}" of type "\{relationType\}" | An unsupported operation is used for the relation type (e.g., using `delete` on a manyToMany relation) |
| `NestedWriteTargetNotFoundError` | Nested write \{operation\} failed: no record found in "\{sheetName\}" | No related record exists for a nested `update` / `delete` on the non-FK side of a oneToOne relation |
## Transaction Errors
For details, see [$transaction](/docs/reference/transaction).
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `GassmaTransactionLockTimeoutError` | Transaction API error: Unable to start a transaction in the given time. The maxWait for this transaction was \{maxWaitMs\} ms. | The script lock could not be acquired within `maxWait` when starting `$transaction` |
| `GassmaTransactionTimeoutError` | Transaction API error: A \{phase\} cannot be executed on an expired transaction. The timeout for this transaction was \{timeoutMs\} ms, however \{elapsedMs\} ms passed since the start of the transaction. Consider increasing the transaction timeout or doing less work in the transaction. | The elapsed time since the transaction started exceeds `timeout` (detected when a tx operation is called or right before commit) |
| `GassmaNestedTransactionError` | Transaction API error: Nested transactions are not supported. Do not call $transaction inside an active transaction. | `$transaction` is called inside a transaction |
| `GassmaTransactionRollbackError` | Transaction API error: The transaction failed during commit and automatic rollback also failed. The affected sheets may be in an inconsistent state. Backup sheets are preserved for manual recovery: \{backupSheetNames\} | The automatic restore from the backups also failed after a write failure during commit (the `backupSheetNames` property holds the list of remaining backup sheet names) |
## CLI Configuration File Errors
Errors that occur when running CLI commands (such as `gassma generate`).
| Error | Message | Trigger Condition |
| --- | --- | --- |
| `ConfigFileNotFoundError` | GASsmaConfigFileNotFoundError: config file not found at \{configPath\} | The config file specified with `--config` does not exist |
| `GassmaConfigLoadError` | GASsmaConfigLoadError: Failed to load config file at \{configPath\}. \{detail\} | A syntax or runtime error in the config file, an invalid type for a known key (`schema` / `datasource.url`), or the config file does not export a config object |
| `GassmaConfigEnvError` | Cannot resolve environment variable: \{name\}. | The environment variable referenced by `env()` is not set or is an empty string |