upsert(filter: FullFilter, newData: Partial, params?: SelectParams): Promise<UpdateReturnType<P, T, S> | undefined>

Inserts or updates a record in the table.

• If a record matching the filter exists, it updates the record.
• If no matching record exists, it inserts a new record.

Parameters

• filter required FullFilter

  Data filter

  • { status: 'live' }
  • { $or: [{ id: 1 }, { status: 'live' }] }
  • { $existsJoined: { referencedTable: { id: 1 } } }
  • { $filter: [ { $age: ["created_at"] }, "<", '1 year' ] }

• newData required Partial

  Make all properties in T optional

• params optional SelectParams

  • limit optional number | null | undefined

    Max number of rows to return. Defaults to 1000

    • On client publish rules can affect this behaviour: cannot request more than the maxLimit (if present)

  • offset optional number

    Number of rows to skip

  • groupBy optional boolean

    Will group by all non aggregated fields specified in select (or all fields by default)

  • returnType optional “row” | “value” | “values” | “statement” | “statement-no-rls” | “statement-where” | undefined

    Result data structure/type:

    • row: the first row as an object
    • value: the first value from of first field
    • values: array of values from the selected field
    • statement: sql statement
    • statement-no-rls: sql statement without row level security
    • statement-where: sql statement where condition

  • select optional Select

    Fields/expressions/linked data to select

    • "*" or empty will return all fields
    • { field: 0 } - all fields except the specified field will be selected
    • { field: 1 } - only the specified field will be selected
    • { field: { $funcName: [args] } } - the field will be selected with the specified function applied
    • { field: 1, referencedTable: "*" } - field together with all fields from referencedTable will be selected
    • { linkedData: { referencedTable: { field: 1 } } } - linkedData will contain the linked/joined records from referencedTable

  • orderBy optional OrderBy

    Order by options

    • Order is maintained in arrays
    • [{ key: "field", asc: true, nulls: "last" }]

  • having optional FullFilter

    Filter applied after any aggregations (group by)

Return type

UpdateReturnType<P, T, S> | undefined