Exercise - Add JavaScript code to work with Cosmos DB

Completed

In this unit, you create and run scripts, which use SQL keywords such as LIKE, JOIN, and WHERE to find data with the Cosmos SDK.

Create the script to find products in the container

  1. In Visual Studio Code, on the File menu, select New Text File.

  2. On the File menu, select Save As. Save the new file with the name 2-contoso-products-find.js.

  3. Copy the following JavaScript and paste it into that file:

    import * as path from "path";
    import { promises as fs } from "fs";
    import { fileURLToPath } from "url";
    const __dirname = path.dirname(fileURLToPath(import.meta.url));
    
    // Get environment variables from .env
    import * as dotenv from "dotenv";
    dotenv.config();
    
    // Get Cosmos Client
    import { CosmosClient } from "@azure/cosmos";
    
    // Provide required connection from environment variables
    const cosmosSecret = process.env.COSMOS_CONNECTION_STRING;
    
    // Authenticate to Azure Cosmos DB
    const cosmosClient = new CosmosClient(cosmosSecret);
    
    // Set Database name and container name
    const databaseName = process.env.COSMOS_DATABASE_NAME;
    const containerName = process.env.COSMOS_CONTAINER_NAME;
    
    // Get Container
    const container = await cosmosClient
      .database(databaseName)
      .container(containerName);
    
    // Find all products that match a property with a value like `value`
    async function executeSqlFind(property, value) {
      // Build query
      const querySpec = {
        query: `select * from products p where p.${property} LIKE @propertyValue`,
        parameters: [
          {
            name: "@propertyValue",
            value: `${value}`,
          },
        ],
      };
    
      // Show query
      console.log(querySpec);
    
      // Get results
      const { resources } = await container.items.query(querySpec).fetchAll();
    
      let i = 0;
    
      // Show results of query
      for (const item of resources) {
        console.log(`${++i}: ${item.id}: ${item.name}, ${item.sku}`);
      }
    }
    
    // Find inventory of products with property/value and location
    async function executeSqlInventory(propertyName, propertyValue, locationPropertyName, locationPropertyValue) {
      // Build query
      const querySpec = {
        query: `select p.id, p.name, i.location, i.inventory from products p JOIN i IN p.inventory where p.${propertyName} LIKE @propertyValue AND i.${locationPropertyName}=@locationPropertyValue`,
    
        parameters: [
          {
            name: "@propertyValue",
            value: `${propertyValue}`,
          },
          { 
            name: "@locationPropertyValue", 
            value: `${locationPropertyValue}` },
        ],
      };
    
      // Show query
      console.log(querySpec);
    
      // Get results
      const { resources } = await container.items.query(querySpec).fetchAll();
    
      let i = 0;
    
      // Show results of query
      console.log(`Looking for ${propertyName}=${propertyValue}, ${locationPropertyName}=${locationPropertyValue}`);
      for (const item of resources) {
        console.log(
          `${++i}: ${item.id}: '${item.name}': current inventory = ${
            item.inventory
          }`
        );
      }
    }
    
    // Example queries
    /*
    
    // find all bikes based on partial match to property value
    
    node 2-contoso-products-find.js find categoryName '%Bikes%'
    node 2-contoso-products-find.js find name '%Blue%'
    
    // find inventory at location on partial match to property value and specific location
    
    node 2-contoso-products-find.js find-inventory categoryName '%Bikes%' location Seattle
    node 2-contoso-products-find.js find-inventory name '%Blue%' location Dallas
    
    */
    const args = process.argv;
    
    if (args && args[2] == "find") {
      await executeSqlFind(args[3], args[4]);
    } else if (args && args[2] == "find-inventory") {
      await executeSqlInventory(args[3], args[4], args[5], args[6]);
    } else {
      console.log("products: no args used");
    }
    
  4. In the Visual Studio Code terminal, execute the JavaScript file to find all bikes:

    node 2-contoso-products-find.js find categoryName '%Bikes%'
    

    The bikes term is wrapped with percent signs, %, indicating partial matching.

    The SQL query in the executeSqlFind method for the container uses the LIKE keyword and query parameters to find any items with a categoryName that includes Bikes.

  5. Run another query to find all products with the word Blue in the name.

    node 2-contoso-products-find.js find name '%Blue%'
    
  6. Run another query to find product inventory for bikes in Seattle.

    node 2-contoso-products-find.js find-inventory categoryName '%Bikes%' location Seattle
    
  7. Run another query to find inventory for all products with the word Blue in the name in Dallas.

    node 2-contoso-products-find.js find-inventory name '%Blue%' location Dallas
    

Create the script to upsert products to a container

  1. In Visual Studio Code, on the File menu, select New Text File.

  2. On the File menu, select Save As. Save the new file with the name 3-contoso-products-upsert.js.

  3. Copy the following JavaScript and paste it into that file:

    import * as path from "path";
    import { promises as fs } from "fs";
    import { fileURLToPath } from "url";
    const __dirname = path.dirname(fileURLToPath(import.meta.url));
    
    // Get environment variables from .env
    import * as dotenv from "dotenv";
    dotenv.config();
    
    // Get Cosmos Client
    import { CosmosClient } from "@azure/cosmos";
    
    // Provide required connection from environment variables
    const cosmosSecret = process.env.COSMOS_CONNECTION_STRING;
    
    // Authenticate to Azure Cosmos DB
    const cosmosClient = new CosmosClient(cosmosSecret);
    
    // Set Database name and container name
    const databaseName = process.env.COSMOS_DATABASE_NAME;
    const containerName = process.env.COSMOS_CONTAINER_NAME;
    
    // Get Container
    const container = await cosmosClient.database(databaseName).container(containerName);
    
    // Either insert or update item
    async function upsert(fileAndPathToJson, encoding='utf-8') {
    
      // Get item from file
      const data = JSON.parse(await fs.readFile(path.join(__dirname, fileAndPathToJson), encoding));
    
      // Process request
      // result.resource is the returned item
      const result = await container.items.upsert(data);
    
      if(result.statusCode===201){
        console.log("Inserted data");
      } else if (result.statusCode===200){
        console.log("Updated data");
      } else {
        console.log(`unexpected statusCode ${result.statusCode}`);
      }
    }
    
    // Insert data - statusCode = 201
    await upsert('./3-contoso-products-upsert-insert.json');
    
    // Update data - statusCode = 200
    await upsert('./3-contoso-products-upsert-update.json');
    
    // Get item from container and partition key
    const { resource } = await container.item("123", "xyz").read();
    
    // Show final item
    console.log(resource);
    
  4. Create a new file for the product, 3-contoso-products-upsert-insert.json, and paste the following JSON object.

    {
        "id": "123",
        "categoryName": "xyz",
        "name": "_a new item inserted"
    }
    

    Notice this object with ID 123 doesn't have any inventory.

  5. Create a new file for the product, 3-contoso-products-upsert-update.json, and paste the following JSON object.

    {
      "id": "123",
      "categoryName": "xyz",
      "name": "_a new item updated",
      "inventory": [
        {
          "location": "Dallas",
          "inventory": 100
        }
      ]
    }
    

    Notice this object does have inventory.

  6. In the Visual Studio Code terminal, execute the JavaScript file to upsert the new product.

    node 3-contoso-products-upsert.js
    

    Because the product with that ID doesn't exist, the product is inserted. Then the script updates the product with inventory. Both the insert and the update functionality use the same code to upsert.