Mutations (push)

For when you want to push data to Pitchly

Endpoints

insertRow

Inserts a row/record into a database. The row can have any number of cols for each column. Only a fieldId and value must be provided for each column. value could look different depending on the data type of the column.

mutation insertRow($secretKey: String!, $databaseId: ID!, $row: DataRowInput!) {
  insertRow(secretKey: $secretKey, databaseId: $databaseId, row: $row) {
    _id
  }
}

Variables:

{
  "secretKey": <SECRET_KEY>,
  "databaseId": <DATABASE_ID>,
  "row": {
    "cols": [
      {
        "fieldId": <FIELD_ID>,
        "value": {
          "val": "Foo Bar"
        }
      }
    ]
  }
}

In the Body of the request:

{
  "operationName": "insertRow",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "databaseId": <DATABASE_ID>,
    "row": {
      "cols": [
        {
          "fieldId": <FIELD_ID>,
          "value": {
            "val": "Foo Bar"
          }
        }
      ]
    }
  },
  "query": "mutation insertRow($secretKey: String!, $databaseId: ID!, $row: DataRowInput!) {\n  insertRow(secretKey: $secretKey, databaseId: $databaseId, row: $row) {\n    _id\n  }\n}\n"
}

Example result

Returns the ID of the new row.

{
  "data": {
    "insertRow": {
      "_id": "bHBSESizM5jEmiRRn"
    }
  }
}

{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}

updateRow

Updates a specific row/record in a database. The new row can have any number of cols for each updated column. Only a fieldId and value must be provided for each column. value could look different depending on the data type of the column.

Note that only the cols included in the request will be updated. Any others not included in the request will stay the same and not be updated.

mutation updateRow($secretKey: String!, $databaseId: ID!, $rowId: ID!, $row: DataRowInput!) {
  updateRow(secretKey: $secretKey, databaseId: $databaseId, rowId: $rowId, row: $row) {
    _id
  }
}

Variables:

{
  "secretKey": <SECRET_KEY>,
  "databaseId": <DATABASE_ID>,
  "rowId": <ROW_ID>,
  "row": {
    "cols": [
      {
        "fieldId": <FIELD_ID>,
        "value": {
          "val": "Foo Bar"
        }
      }
    ]
  }
}

In the Body of the request:

{
  "operationName": "updateRow",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "databaseId": <DATABASE_ID>,
    "rowId": <ROW_ID>,
    "row": {
      "cols": [
        {
          "fieldId": <FIELD_ID>,
          "value": {
            "val": "Foo Bar"
          }
        }
      ]
    }
  },
  "query": "mutation updateRow($secretKey: String!, $databaseId: ID!, $rowId: ID!, $row: DataRowInput!) {\n  updateRow(secretKey: $secretKey, databaseId: $databaseId, rowId: $rowId, row: $row) {\n    _id\n  }\n}\n"
}

Example result

Returns the ID of the updated row.

{
  "data": {
    "updateRow": {
      "_id": "bHBSESizM5jEmiRRn"
    }
  }
}

{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}

upsertRow

Conditionally insert or update a row in a database, depending on whether the row already exists. Unlike insert and update operations, this endpoint requires a matchFields parameter, which is an array of field IDs you wish to match against.

If you provide row data that matches an existing row in the fields specified in matchFields, the row will be updated. If no rows match the values in the fields specified in matchFields, the row provided will be inserted into the database.

If more than one row matches, an error will be thrown instead of updating all of the matching rows. This behavior was chosen to limit the potential damage of a widespread update.

Note that matchFields must contain the IDs of the fields you wish to match against, and not their names.

Also note that any fields specified in matchFields must be present in the data and also cannot be empty.

mutation upsertRow($secretKey: String!, $databaseId: ID!, $row: DataRowInput!, $matchFields: [String]!) {
  upsertRow(secretKey: $secretKey, databaseId: $databaseId, row: $row, matchFields: $matchFields) {
    _id
  }
}

Variables:

{
  "secretKey": <SECRET_KEY>,
  "databaseId": <DATABASE_ID>,
  "row": {
    "cols": [
      {
        "fieldId": <FIELD_ID>,
        "value": {
          "val": "Foo Bar"
        }
      }
    ]
  },
  "matchFields": [<FIELD_ID1>, <FIELD_ID2>]
}

In the Body of the request:

{
  "operationName": "upsertRow",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "databaseId": <DATABASE_ID>,
    "row": {
      "cols": [
        {
          "fieldId": <FIELD_ID>,
          "value": {
            "val": "Foo Bar"
          }
        }
      ]
    },
    "matchFields": [<FIELD_ID1>, <FIELD_ID2>]
  },
  "query": "mutation upsertRow($secretKey: String!, $databaseId: ID!, $row: DataRowInput!, $matchFields: [String]!) {\n  upsertRow(secretKey: $secretKey, databaseId: $databaseId, row: $row, matchFields: $matchFields) {\n    _id\n  }\n}\n"
}

Example result

Returns the ID of the new row (if inserted), or the ID of the existing row that was updated.

{
  "data": {
    "upsertRow": {
      "_id": "bHBSESizM5jEmiRRn"
    }
  }
}

{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}

deleteRow

Deletes a specific row/record from a database.

mutation deleteRow($secretKey: String!, $databaseId: ID!, $rowId: ID!) {
  deleteRow(secretKey: $secretKey, databaseId: $databaseId, rowId: $rowId)
}

Variables:

{
  "secretKey": <SECRET_KEY>,
  "databaseId": <DATABASE_ID>,
  "rowId": <ROW_ID>
}

In the Body of the request:

{
  "operationName": "deleteRow",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "databaseId": <DATABASE_ID>,
    "rowId": <ROW_ID>
  },
  "query": "mutation deleteRow($secretKey: String!, $databaseId: ID!, $rowId: ID!) {\n  deleteRow(secretKey: $secretKey, databaseId: $databaseId, rowId: $rowId)\n}\n"
}

Example result

Returns true if deletion was successful.

{
  "data": {
    "deleteRow": true
  }
}

{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}

Reference

Data types

When inserting or updating data rows, the value object that you will set for each column of data will largely be the same as the output you receive when querying rows of data. But there are some minor differences, mostly with attachment, ref, and refMultiple fields. Below is a list of all field types and their equivalent expected value object examples when inserting or updating row values.

Empty values

To make a value "intentionally empty," set val in the value object to null:

{ val: null }

When inserting a row, you can also make a value "not set" by simply excluding that field from the row. When updating a row, any field that is not included will not be updated. And empty strings or empty arrays (for fields that accept strings or arrays) will automatically be converted to null.

String values are also trimmed of excess whitespace at the beginning and end of the string.

Required fields

Any data type can be set to empty, except fields that are "required." Fields can be made required via the Pitchly interface when editing a database. Required fields must have a non-empty value and are enforced whenever inserting or updating rows. Required fields are only enforced on fields specified in an insert or update. Fields that are not included in the request will not be enforced.

Note that there are still several situations where a row may contain an empty value in a field that is required. Here are some scenarios:

The value was empty prior to the field becoming required
The value was inserted during a bulk CSV import (to prevent partial failures)
The field was not specified during insertion
The field was created after the rows were already inserted
A row that is referenced by another field is deleted, causing the referencing field to turn empty

For these reasons, it is not completely safe to assume that all values in a field will not be empty if the field is required, since required fields must strike a balance between utility and ease-of-use. We think of "required" as a flag that informs users and apps when a field should have a non-empty value. The field will be reasonably validated to ensure the value isn't intentionally empty at time of input, but it should be used more as a visual cue than a strict persistent enforcement scheme.

Accepted date formats

Date values in the following formats will be accepted:

2020-01-23T00:00:00.000Z (ISO 8601 format - time will be stripped)
M-D-YYYY
M-D-YY
YYYY-M-D
M/D/YYYY
M/D/YY
YYYY/M/D
M.D.YYYY
M.D.YY
YYYY.M.D

Single M and D represent month and day, respectively, with or without leading zeros.

Regardless of the date format entered, dates will always be returned in ISO 8601 format with a zeroed out UTC time when queried.

Accepted currency types

Pitchly currently supports the following currency types by code.

If you see a mistake in this list or would like to request additional currencies, please contact us.

PreviousQueries (pull)NextSubscriptions (listen)

Last updated 1 year ago