Quickstart: Build a API for Table app with Node.js and Azure Cosmos DB

APPLIES TO: Table

In this quickstart, you create an Azure Cosmos DB for Table account, and use Data Explorer and a Node.js app cloned from GitHub to create tables and entities. Azure Cosmos DB is a multi-model database service that lets you quickly create and query document, table, key-value, and graph databases with multiple-region distribution and horizontal scale capabilities.

Prerequisites

Sample application

The sample application for this tutorial may be cloned or downloaded from the repository https://github.com/Azure-Samples/msdocs-azure-data-tables-sdk-js. Both a starter and completed app are included in the sample repository.

git clone https://github.com/Azure-Samples/msdocs-azure-data-tables-sdk-js

The sample application uses weather data as an example to demonstrate the capabilities of the API for Table. Objects representing weather observations are stored and retrieved using the API for Table, including storing objects with additional properties to demonstrate the schemaless capabilities of the API for Table.

A screenshot of the finished application showing data stored in an Azure Cosmos DB table using the API for Table.

1 - Create an Azure Cosmos DB account

You first need to create an Azure Cosmos DB Tables API account that will contain the table(s) used in your application. This can be done using the Azure portal, Azure CLI, or Azure PowerShell.

Log in to the Azure portal and follow these steps to create an Azure Cosmos DB account.

Instructions Screenshot
In the Azure portal:
  1. In the search bar at the top of the Azure portal, enter "cosmos db".
  2. On the menu that appears below the search bar, under Services, select the item labeled Azure Cosmos DB.
A screenshot showing how to use the search box in the top tool bar to find Azure Cosmos DB accounts in Azure.
On the Azure Cosmos DB page select +Create. A screenshot showing the Create button location on the Azure Cosmos DB accounts page in Azure.
On the Select API option page choose the Azure Table option. A screenshot showing the Azure Table option as the correct option to select.
On the Create Azure Cosmos DB Account - Azure Table page, fill out the form as follows.
  1. Create a new resource group for the storage account named rg-msdocs-tables-sdk-demo by selecting the Create new link under Resource group.
  2. Give your storage account a name of cosmos-msdocs-tables-sdk-demo-XYZ where XYZ are any three random characters to create a unique account name. Azure Cosmos DB account names must be between 3 and 44 characters in length and may contain only lowercase letters, numbers or the hyphen (-) character.
  3. Select the region for your storage account.
  4. Select Standard performance.
  5. Select Provisioned throughput for this example under Capacity mode.
  6. Select Apply under Apply Free Tier Discount for this example.
  7. Select the Review + create button at the bottom of the screen and then select "Create" on the summary screen to create your Azure Cosmos DB account. This process may take several minutes.
A screenshot showing how to fill out the fields on the Azure Cosmos DB Account creation page.

2 - Create a table

Next, you need to create a table within your Azure Cosmos DB account for your application to use. Unlike a traditional database, you only need to specify the name of the table, not the properties (columns) in the table. As data is loaded into your table, the properties (columns) will be automatically created as needed.

In the Azure portal, complete the following steps to create a table inside your Azure Cosmos DB account.

Instructions Screenshot
In the Azure portal, navigate to the overview page for the Azure Cosmos DB account. You can navigate to the overview page for your Azure Cosmos DB account by typing the name (cosmos-msdocs-tables-sdk-demo-XYZ) of your Azure Cosmos DB account in the top search bar and looking under the resources heading.Select the name of your Azure Cosmos DB account to go to the overview page. A screenshot showing how to use the search box in the top tool bar to find your Azure Cosmos DB account.
On the overview page, select +Add Table. The New Table dialog will slide out from the right side of the page. A screenshot showing the location of the Add Table button.
In the New Table dialog, fill out the form as follows.
  1. Enter the name WeatherData for the Table ID. This is the name of the table.
  2. Select Manual under Table throughput (autoscale) for this example.
  3. Use the default value of 400 under your estimated RU/s.
  4. Select the OK button to create the table.
A screenshot showing how to New Table dialog box for an Azure Cosmos DB table.

3 - Get Azure Cosmos DB connection string

To access your table(s) in Azure Cosmos DB, your app will need the table connection string for the CosmosDB Storage account. The connection string can be retrieved using the Azure portal, Azure CLI or Azure PowerShell.

Instructions Screenshot
On the left hand side of the Azure Cosmos DB account page, locate the menu item named Connection String under the Settings header and select it. You will be taken to a page where you can retrieve the connection string for the Azure Cosmos DB account. A screenshot showing the location of the connection strings link on the Azure Cosmos DB page.
Copy the PRIMARY CONNECTION STRING value to use in your application. A screenshot showing which connection string to select and use in your application.

4 - Install the Azure Data Tables SDK for JS

To access the Azure Cosmos DB for Table from a nodejs application, install the Azure Data Tables SDK package.

  npm install @azure/data-tables

5 - Configure the Table client in env.js file

Copy your Azure Cosmos DB or Storage account connection string from the Azure portal, and create a TableServiceClient object using your copied connection string. Switch to folder 1-strater-app or 2-completed-app. Then, add the value of the corresponding environment variables in configure/env.js file.

const env = {
  connectionString:"A connection string to an Azure Storage or Azure Cosmos DB account.",
  tableName: "WeatherData",
};

The Azure SDK communicates with Azure using client objects to execute different operations against Azure. The TableClient class is the class used to communicate with the Azure Cosmos DB for Table. An application will typically create a single serviceClient object per table to be used throughout the application.

const { TableClient } = require("@azure/data-tables");
const env = require("../configure/env");
const serviceClient = TableClient.fromConnectionString(
  env.connectionString,
  env.tableName
);

6 - Implement Azure Cosmos DB table operations

All Azure Cosmos DB table operations for the sample app are implemented in the serviceClient object located in tableClient.js file under service directory.

const { TableClient } = require("@azure/data-tables");
const env = require("../configure/env");
const serviceClient = TableClient.fromConnectionString(
  env.connectionString,
  env.tableName
);

Get rows from a table

The serviceClient object contains a method named listEntities which allows you to select rows from the table. In this example, since no parameters are being passed to the method, all rows will be selected from the table.

const allRowsEntities = serviceClient.listEntities();

Filter rows returned from a table

To filter the rows returned from a table, you can pass an OData style filter string to the listEntities method. For example, if you wanted to get all of the weather readings for Shanghai between midnight July 1, 2021 and midnight July 2, 2021 (inclusive) you would pass in the following filter string.

PartitionKey eq 'Shanghai' and RowKey ge '2021-07-01 12:00' and RowKey le '2021-07-02 12:00'

You can view all OData filter operators on the OData website in the section Filter System Query Option.

When request.args parameter is passed to the listEntities method in the serviceClient class, it creates a filter string for each non-null property value. It then creates a combined filter string by joining all of the values together with an "and" clause. This combined filter string is passed to the listEntities method on the serviceClient object and only rows matching the filter string will be returned. You can use a similar method in your code to construct suitable filter strings as required by your application.

const filterEntities = async function (option) {
  /*
    You can query data according to existing fields
    option provides some conditions to query,eg partitionKey, rowKeyDateTimeStart, rowKeyDateTimeEnd
    minTemperature, maxTemperature, minPrecipitation, maxPrecipitation
  */
  const filterEntitiesArray = [];
  const filters = [];
  if (option.partitionKey) {
    filters.push(`PartitionKey eq '${option.partitionKey}'`);
  }
  if (option.rowKeyDateTimeStart) {
    filters.push(`RowKey ge '${option.rowKeyDateTimeStart}'`);
  }
  if (option.rowKeyDateTimeEnd) {
    filters.push(`RowKey le '${option.rowKeyDateTimeEnd}'`);
  }
  if (option.minTemperature !== null) {
    filters.push(`Temperature ge ${option.minTemperature}`);
  }
  if (option.maxTemperature !== null) {
    filters.push(`Temperature le ${option.maxTemperature}`);
  }
  if (option.minPrecipitation !== null) {
    filters.push(`Precipitation ge ${option.minPrecipitation}`);
  }
  if (option.maxPrecipitation !== null) {
    filters.push(`Precipitation le ${option.maxPrecipitation}`);
  }
  const res = serviceClient.listEntities({
    queryOptions: {
      filter: filters.join(" and "),
    },
  });
  for await (const entity of res) {
    filterEntitiesArray.push(entity);
  }

  return filterEntitiesArray;
};

Insert data using a TableEntity object

The simplest way to add data to a table is by using a TableEntity object. In this example, data is mapped from an input model object to a TableEntity object. The properties on the input object representing the weather station name and observation date/time are mapped to the PartitionKey and RowKey properties respectively which together form a unique key for the row in the table. Then the additional properties on the input model object are mapped to dictionary properties on the TableEntity object. Finally, the createEntity method on the serviceClient object is used to insert data into the table.

Modify the insertEntity function in the example application to contain the following code.

const insertEntity = async function (entity) {

  await serviceClient.createEntity(entity);

};

Upsert data using a TableEntity object

If you try to insert a row into a table with a partition key/row key combination that already exists in that table, you will receive an error. For this reason, it is often preferable to use the upsertEntity instead of the createEntity method when adding rows to a table. If the given partition key/row key combination already exists in the table, the upsertEntity method will update the existing row. Otherwise, the row will be added to the table.

const upsertEntity = async function (entity) {

  await serviceClient.upsertEntity(entity, "Merge");

};

Insert or upsert data with variable properties

One of the advantages of using the Azure Cosmos DB for Table is that if an object being loaded to a table contains any new properties then those properties are automatically added to the table and the values stored in Azure Cosmos DB. There is no need to run DDL statements like ALTER TABLE to add columns as in a traditional database.

This model gives your application flexibility when dealing with data sources that may add or modify what data needs to be captured over time or when different inputs provide different data to your application. In the sample application, we can simulate a weather station that sends not just the base weather data but also some additional values. When an object with these new properties is stored in the table for the first time, the corresponding properties (columns) will be automatically added to the table.

To insert or upsert such an object using the API for Table, map the properties of the expandable object into a TableEntity object and use the createEntity or upsertEntity methods on the serviceClient object as appropriate.

In the sample application, the upsertEntity function can also implement the function of insert or upsert data with variable properties

const insertEntity = async function (entity) {
  await serviceClient.createEntity(entity);
};

const upsertEntity = async function (entity) {
  await serviceClient.upsertEntity(entity, "Merge");
};

Update an entity

Entities can be updated by calling the updateEntity method on the serviceClient object.

In the sample app, this object is passed to the upsertEntity method in the serviceClient object. It updates that entity object and uses the upsertEntity method save the updates to the database.

const updateEntity = async function (entity) {
  await serviceClient.updateEntity(entity, "Replace");
};

7 - Run the code

Run the sample application to interact with the Azure Cosmos DB for Table. The first time you run the application, there will be no data because the table is empty. Use any of the buttons at the top of application to add data to the table.

A screenshot of the application showing the location of the buttons used to insert data into Azure Cosmos DB using the Table API.

Selecting the Insert using Table Entity button opens a dialog allowing you to insert or upsert a new row using a TableEntity object.

A screenshot of the application showing the dialog box used to insert data using a TableEntity object.

Selecting the Insert using Expandable Data button brings up a dialog that enables you to insert an object with custom properties, demonstrating how the Azure Cosmos DB for Table automatically adds properties (columns) to the table when needed. Use the Add Custom Field button to add one or more new properties and demonstrate this capability.

A screenshot of the application showing the dialog box used to insert data using an object with custom fields.

Use the Insert Sample Data button to load some sample data into your Azure Cosmos DB Table.

A screenshot of the application showing the location of the sample data insert button.

Select the Filter Results item in the top menu to be taken to the Filter Results page. On this page, fill out the filter criteria to demonstrate how a filter clause can be built and passed to the Azure Cosmos DB for Table.

A screenshot of the application showing filter results page and highlighting the menu item used to navigate to the page.

Clean up resources

When you are finished with the sample application, you should remove all Azure resources related to this article from your Azure account. You can do this by deleting the resource group.

A resource group can be deleted using the Azure portal by doing the following.

Instructions Screenshot
To go to the resource group, in the search bar, type the name of the resource group. Then on the Resource Groups tab, select the name of the resource group. A screenshot showing how to search for a resource group.
Select Delete resource group from the toolbar at the top of the resource group page. A screenshot showing the location of the Delete resource group button.
A dialog will pop out from the right of the screen asking you to confirm deletion of the resource group.
  1. Type the full name of the resource group in the text box to confirm deletion as instructed.
  2. Select the Delete button at the bottom of the page.
A screenshot showing the confirmation dialog for deleting a resource group.

Next steps

In this quickstart, you've learned how to create an Azure Cosmos DB account, create a table using the Data Explorer, and run an app. Now you can query your data using the API for Table.