HomeAPIAppsContact us

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.

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

Example result

Returns the ID of the new row.

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

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

Example result

Returns the ID of the updated row.

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

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

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

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

Example result

Returns true if deletion was successful.

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

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.

PreviousQueries (pull)NextSubscriptions (listen)

Last updated

Was this helpful?