Upsert

Example Setup

Table Definition

{
  "TableName": "electro",
  "KeySchema": [
    {
      "AttributeName": "pk",
      "KeyType": "HASH"
    },
    {
      "AttributeName": "sk",
      "KeyType": "RANGE"
    }
  ],
  "AttributeDefinitions": [
    {
      "AttributeName": "pk",
      "AttributeType": "S"
    },
    {
      "AttributeName": "sk",
      "AttributeType": "S"
    },
    {
      "AttributeName": "gsi1pk",
      "AttributeType": "S"
    },
    {
      "AttributeName": "gsi1sk",
      "AttributeType": "S"
    }
  ],
  "GlobalSecondaryIndexes": [
    {
      "IndexName": "gsi1pk-gsi1sk-index",
      "KeySchema": [
        {
          "AttributeName": "gsi1pk",
          "KeyType": "HASH"
        },
        {
          "AttributeName": "gsi1sk",
          "KeyType": "RANGE"
        }
      ],
      "Projection": {
        "ProjectionType": "ALL"
      }
    },
    {
      "IndexName": "gsi2pk-gsi2sk-index",
      "KeySchema": [
        {
          "AttributeName": "gsi2pk",
          "KeyType": "HASH"
        },
        {
          "AttributeName": "gsi2sk",
          "KeyType": "RANGE"
        }
      ],
      "Projection": {
        "ProjectionType": "ALL"
      }
    }
  ],
  "BillingMode": "PAY_PER_REQUEST"
}

Example Entity

import DynamoDB from "aws-sdk/clients/dynamodb";
import { Entity } from "electrodb";

const client = new DynamoDB.DocumentClient();

const table = "electro";

const StoreLocations = new Entity(
  {
    model: {
      service: "MallStoreDirectory",
      entity: "MallStore",
      version: "1",
    },
    attributes: {
      cityId: {
        type: "string",
        required: true,
      },
      mallId: {
        type: "string",
        required: true,
      },
      storeId: {
        type: "string",
        required: true,
      },
      buildingId: {
        type: "string",
        required: true,
      },
      unitId: {
        type: "string",
        required: true,
      },
      category: {
        type: [
          "spite store",
          "food/coffee",
          "food/meal",
          "clothing",
          "electronics",
          "department",
          "misc",
        ],
        required: true,
      },
      leaseEndDate: {
        type: "string",
        required: true,
      },
      rent: {
        type: "string",
        required: true,
        validate: /^(\d+\.\d{2})$/,
      },
      discount: {
        type: "string",
        required: false,
        default: "0.00",
        validate: /^(\d+\.\d{2})$/,
      },
      tenants: {
        type: "set",
        items: "string",
      },
      warnings: {
        type: "number",
        default: 0,
      },
      deposit: {
        type: "number",
      },
      contact: {
        type: "set",
        items: "string",
      },
      rentalAgreement: {
        type: "list",
        items: {
          type: "map",
          properties: {
            type: {
              type: "string",
            },
            detail: {
              type: "string",
            },
          },
        },
      },
      petFee: {
        type: "number",
      },
      fees: {
        type: "number",
      },
      tags: {
        type: "set",
        items: "string",
      },
    },
    indexes: {
      stores: {
        pk: {
          field: "pk",
          composite: ["cityId", "mallId"],
        },
        sk: {
          field: "sk",
          composite: ["buildingId", "storeId"],
        },
      },
      units: {
        index: "gsi1pk-gsi1sk-index",
        pk: {
          field: "gsi1pk",
          composite: ["mallId"],
        },
        sk: {
          field: "gsi1sk",
          composite: ["buildingId", "unitId"],
        },
      },
      leases: {
        index: "gsi2pk-gsi2sk-index",
        pk: {
          field: "gsi2pk",
          composite: ["storeId"],
        },
        sk: {
          field: "gsi2sk",
          composite: ["leaseEndDate"],
        },
      },
    },
  },
  { table, client },
);

(Example code on this page that references the entity StoreLocations uses the following Entity and Table Definition found below)

The upsert method is another ElectroDB exclusive method. Upsert is similar to the put-method in that it will create a record if one does not exist. Unlike the put method, however, upsert perform an update if that record already exists.

Note: upsert will apply a dynamodb set operation on all attributes provided. If an attribute is configured as readOnly, ElectroDB will apply a if_not_exists condition to prevent overwriting the existing value. The exception case is if the attribute is a composite of an index, in which case it will NOT be wrapped with if_not_exists()

await StoreLocations.upsert({
  cityId: "Atlanta1",
  storeId: "LatteLarrys",
  mallId: "EastPointe",
  buildingId: "BuildingA1",
  unitId: "B47",
  category: "food/coffee",
  leaseEndDate: "2020-03-22",
  rent: "4500.00",
}).go();

Default Response Format

Note: Use the Execution Option response to impact the response type

{
  data: EntityIdentifiers<typeof StoreLocations>;
}

Equivalent Parameters

{
  "TableName": "StoreDirectory",
  "UpdateExpression": "SET #__edb_e__ = :__edb_e___u0, #__edb_v__ = :__edb_v___u0, #cityId = :cityId_u0, #mallId = :mallId_u0, #storeId = :storeId_u0, #buildingId = :buildingId_u0, #unitId = :unitId_u0, #category = :category_u0, #leaseEndDate = :leaseEndDate_u0, #rent = :rent_u0, #discount = :discount_u0, #gsi1pk = :gsi1pk_u0, #gsi1sk = :gsi1sk_u0, #gsi2pk = :gsi2pk_u0, #gsi2sk = :gsi2sk_u0",
  "ExpressionAttributeNames": {
    "#__edb_e__": "__edb_e__",
    "#__edb_v__": "__edb_v__",
    "#cityId": "cityId",
    "#mallId": "mallId",
    "#storeId": "storeId",
    "#buildingId": "buildingId",
    "#unitId": "unitId",
    "#category": "category",
    "#leaseEndDate": "leaseEndDate",
    "#rent": "rent",
    "#discount": "discount",
    "#gsi1pk": "gsi1pk",
    "#gsi1sk": "gsi1sk",
    "#gsi2pk": "gsi2pk",
    "#gsi2sk": "gsi2sk"
  },
  "ExpressionAttributeValues": {
    ":__edb_e___u0": "MallStore",
    ":__edb_v___u0": "1",
    ":cityId_u0": "Atlanta1",
    ":mallId_u0": "EastPointe",
    ":storeId_u0": "LatteLarrys",
    ":buildingId_u0": "BuildingA1",
    ":unitId_u0": "B47",
    ":category_u0": "food/coffee",
    ":leaseEndDate_u0": "2020-03-22",
    ":rent_u0": "4500.00",
    ":discount_u0": "0.00",
    ":gsi1pk_u0": "$mallstoredirectory#mallid_eastpointe",
    ":gsi1sk_u0": "$mallstore_1#buildingid_buildinga1#unitid_b47",
    ":gsi2pk_u0": "$mallstoredirectory#storeid_lattelarrys",
    ":gsi2sk_u0": "$mallstore_1#leaseenddate_2020-03-22"
  },
  "Key": {
    "pk": "$mallstoredirectory#cityid_atlanta1#mallid_eastpointe",
    "sk": "$mallstore_1#buildingid_buildinga1#storeid_lattelarrys"
  }
}

Add, Subtract, and Append

With upsert, you can also perform add, subtract, ifNotExists, set, and append operations. These options exist as chain methods off upsert (similar to update and patch). In the event an upsert results in a new row, the add and subtract methods will be applied against a 0 value.

Note: The typing for upsert will track provided values across multiple chain methods and only allow .go() and .params() to be called if all required values have been provided to at least one operation

Example

await StoreLocations.upsert({
  cityId: "Atlanta1",
  storeId: "LatteLarrys",
  mallId: "EastPointe",
  buildingId: "BuildingA1",
  unitId: "B47",
  category: "food/coffee",
  leaseEndDate: "2020-03-22",
  rent: "4500.00",
})
  .add({ deposit: 100, tenants: ["Larry David"] })
  .ifNotExists({ warnings: 0 })
  .subtract({ petFee: 250 })
  .append({
    rentalAgreement: [
      {
        type: "amendment",
        detail: "Larry David accepts coffee liability",
      },
    ],
  })
  .go();

Equivalent Parameters

{
  "TableName": "electro",
  "UpdateExpression": "SET #__edb_e__ = :__edb_e___u0, #__edb_v__ = :__edb_v___u0, #cityId = :cityId_u0, #mallId = :mallId_u0, #storeId = :storeId_u0, #buildingId = :buildingId_u0, #unitId = :unitId_u0, #category = :category_u0, #leaseEndDate = :leaseEndDate_u0, #rent = :rent_u0, #discount = :discount_u0, #gsi1pk = :gsi1pk_u0, #gsi1sk = :gsi1sk_u0, #gsi2pk = :gsi2pk_u0, #gsi2sk = :gsi2sk_u0, #warnings = if_not_exists(#warnings, :warnings_u0), #petFee = (if_not_exists(#petFee, :petFee_default_value_u0) - :petFee_u0), #rentalAgreement = list_append(if_not_exists(#rentalAgreement, :rentalAgreement_default_value_u0), :rentalAgreement_u0) ADD #deposit :deposit_u0, #tenants :tenants_u0",
  "ExpressionAttributeNames": {
    "#__edb_e__": "__edb_e__",
    "#__edb_v__": "__edb_v__",
    "#cityId": "cityId",
    "#mallId": "mallId",
    "#storeId": "storeId",
    "#buildingId": "buildingId",
    "#unitId": "unitId",
    "#category": "category",
    "#leaseEndDate": "leaseEndDate",
    "#rent": "rent",
    "#discount": "discount",
    "#gsi1pk": "gsi1pk",
    "#gsi1sk": "gsi1sk",
    "#gsi2pk": "gsi2pk",
    "#gsi2sk": "gsi2sk",
    "#deposit": "deposit",
    "#tenants": "tenants",
    "#warnings": "warnings",
    "#petFee": "petFee",
    "#rentalAgreement": "rentalAgreement"
  },
  "ExpressionAttributeValues": {
    ":__edb_e___u0": "MallStore",
    ":__edb_v___u0": "1",
    ":cityId_u0": "Atlanta",
    ":mallId_u0": "EastPointe",
    ":storeId_u0": "LatteLarrys",
    ":buildingId_u0": "BuildingA1",
    ":unitId_u0": "B47",
    ":category_u0": "food/coffee",
    ":leaseEndDate_u0": "2020-03-22",
    ":rent_u0": "4500.00",
    ":discount_u0": "0.00",
    ":gsi1pk_u0": "$mallstoredirectory#mallid_eastpointe",
    ":gsi1sk_u0": "$mallstore_1#buildingid_buildinga1#unitid_b47",
    ":gsi2pk_u0": "$mallstoredirectory#storeid_lattelarrys",
    ":gsi2sk_u0": "$mallstore_1#leaseenddate_2020-03-22",
    ":deposit_u0": 100,
    ":tenants_u0": ["Larry David"],
    ":warnings_u0": 0,
    ":petFee_u0": 250,
    ":petFee_default_value_u0": 0,
    ":rentalAgreement_u0": [
      {
        "type": "amendment",
        "detail": "Larry David accepts coffee liability"
      }
    ],
    ":rentalAgreement_default_value_u0": []
  },
  "Key": {
    "pk": "$mallstoredirectory#cityid_atlanta#mallid_eastpointe",
    "sk": "$mallstore_1#buildingid_buildinga1#storeid_lattelarrys"
  }
}

Execution Options

Execution options can be provided to the .params() and .go() terminal functions to change query behavior or add customer parameters to a query.

By default, ElectroDB enables you to work with records as the names and properties defined in the model. Additionally, it removes the need to deal directly with the docClient parameters which can be complex for a team without as much experience with DynamoDB. The Query Options object can be passed to both the .params() and .go() methods when building you query. Below are the options available:

{
  params?: object;
  table?: string;
  data?: 'raw' | 'includeKeys' | 'attributes';
  originalErr?: boolean;
  concurrent?: number;
  unprocessed?: "raw" | "item";
  response?: "default" | "none" | "all_old" | "updated_old" | "all_new" | "updated_new";
  ignoreOwnership?: boolean;
  logger?: (event) => void;
  listeners Array<(event) => void>;
  attributes?: string[];
}

Option	Default	Description
params	`{}`	Properties added to this object will be merged onto the params sent to the document client. Any conflicts with ElectroDB will favor the params specified here.
table	(from constructor)	Use a different table than the one defined when creating your Entity or Service.
attributes	(all attributes)	The `attributes` query option allows you to specify ProjectionExpression Attributes for your `get` or `query` operation. As of `1.11.0` only root attributes are allowed to be specified.
data	`"attributes"`	Accepts the values `'raw'`, `'includeKeys'`, `'attributes'` or `undefined`. Use `'raw'` to return query results as they were returned by the docClient. Use `'includeKeys'` to include item partition and sort key values in your return object. By default, ElectroDB does not return partition, sort, or global keys in its response.
originalErr	`false`	By default, ElectroDB alters the stacktrace of any exceptions thrown by the DynamoDB client to give better visibility to the developer. Set this value equal to `true` to turn off this functionality and return the error unchanged.
concurrent	`1`	When performing batch operations, how many requests (1 batch operation == 1 request) to DynamoDB should ElectroDB make at one time. Be mindful of your DynamoDB throughput configurations.
unprocessed	`"item"`	Used in batch processing to override ElectroDBs default behaviour to break apart DynamoDBs `Unprocessed` records into composite attributes. See more detail about this in the sections for BatchGet, BatchDelete, and BatchPut.
response	`"default"`	Used as a convenience for applying the DynamoDB parameter `ReturnValues`. The options here are the same as the parameter values for the DocumentClient except lowercase. The `"none"` option will cause the method to return null and will bypass ElectroDB’s response formatting — useful if formatting performance is a concern.
ignoreOwnership	`false`	By default, ElectroDB interrogates items returned from a query for the presence of matching entity “identifiers”. This helps to ensure other entities, or other versions of an entity, are filtered from your results. If you are using ElectroDB with an existing table/dataset you can turn off this feature by setting this property to `true`.
listeners	`[]`	An array of callbacks that are invoked when internal ElectroDB events occur.
logger	none	A convenience option for a single event listener that semantically can be used for logging.