Mutations (push)

For when you want to push data to Pitchly

Endpoints

Endpoint	Description
insertRow	Insert a row into a database.
updateRow	Update an existing row in a database.
upsertRow	Insert or update a row in a database.
deleteRow	Delete an existing row in a database.

Queries (pull)Subscriptions (listen)

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.

GraphQL

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"
        }
      }
    ]
  }
}

REST

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.

Success

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

Error

{
  "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.

GraphQL

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"
        }
      }
    ]
  }
}

REST

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.

Success

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

Error

{
  "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.

GraphQL

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>]
}

REST

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.

Success

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

Error

{
  "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.

GraphQL

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>
}

REST

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.

Success

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

Error

{
  "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.

Field type	User-facing name	Description & example
string	Single-line text	Short single-line text { val: "foo" }
textBlock	Multi-line text	Long multi-line text { val: "foo\nbar" }
number	Number	Number w/ possible decimal { val: 12.5 }
boolean	Yes/No	Binary true/false value { val: false }
date	Date	Date in one of these acceptable formats { val: "2020-01-23" }
currency	Currency	Currency amount & 3-char currency code (see list) { val: 50, currency: "USD" }
attachment	Attachment	File of any type. Pass in the URL path to any file on the Internet or the Base64 encoded contents of an image file. If the file is a url, it will be copied to Pitchly's file storage system, and subsequent queries will return the path to our hosted version. If the Base64 contents are provided, then a file will be created in Pitchly's file storage system with the contents, and subsequent queries will return the path to our hosted version. { val: "https://pitchly.com/images/pitchly-logo.png" }. or { val: " R0lGODlh+gD6APcAAAAAAB0cHhcUHA4NGyEZHjAeEx8kHxw2GSglHjcpGCc " }.
enum	Dropdown	A single value selected from a dropdown of predefined values. This value must be present in the field's restrict array. { val: "Las Vegas" }
enumTags	Dropdown multiple	Multiple values selected from a dropdown of predefined values. Each value must be present in the field's restrict array. { val: ["Los Angeles", "Las Vegas", "San Francisco"] }
ref	Reference	Refers to a row from another database, by row ID { val: "TG9PmpmHFiWhe7qDE" }
refMultiple	Reference multiple	Refers to multiple rows from another database, by row ID { val: ["FhGmrYNGxjB8JnjJr", "L6QqTQv5JbJ4aQMLB"] }

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.

Code	Symbol	Name
USD	$	U.S. Dollars
GBP	£	British Pounds
EUR	€	Euros
AUD	$	Australian Dollars
BRL	R$	Brazilian Real
CAD	$	Canadian Dollars
CZK	Kč	Czech koruny
DKK	kr	Danish Kroner
HKD	$	Hong Kong Dollars
HUF	Ft	Hungarian Forints
ILS	₪	Israeli Shekels
INR	₹	Indian Rupee
JPY	¥	Japanese Yen
MYR	RM	Malaysian Ringgits
MXN	$	Mexican Pesos
NZD	$	New Zealand Dollars
NOK	kr	Norwegian Kroner
PHP	Php	Philippine Pesos
PLN	zł	Polish zloty
SGD	$	Singapore Dollars
SEK	kr	Swedish Kronor
CHF	CHF	Swiss Francs
TWD	$	Taiwan New Dollars
THB	฿	Thai Baht
TRY	TL	Turkish Liras

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