Local Secondary Index (LSI) concept in DynamoDB - Mini Project: Build & Apply

Create and Query a Local Secondary Index (LSI) in DynamoDB

📖 Scenario: You are building a simple product catalog for a small online store. Each product has a unique ProductID and belongs to a Category. You want to store product details and be able to quickly find products by category and sort them by price.

🎯 Goal: Create a DynamoDB table with a Local Secondary Index (LSI) to allow querying products by Category and sorting them by Price. Then write a query to get all products in a specific category sorted by price.

📋 What You'll Learn

Create a DynamoDB table named Products with Category as the partition key and ProductID as the sort key.

Add a Local Secondary Index named CategoryPriceIndex with Category as the partition key and Price as the sort key.

Write a query to get all products in the category Electronics sorted by Price using the LSI.

💡 Why This Matters

🌍 Real World

Online stores often need to find products by category and sort them by price or rating quickly. Using LSIs in DynamoDB helps achieve this efficiently.

💼 Career

Understanding LSIs is important for backend developers and database engineers working with DynamoDB to optimize queries and data access patterns.

1

Create the DynamoDB table with primary keys

Create a DynamoDB table named Products with Category as the partition key and ProductID as the sort key. Define the attribute definitions for Category and ProductID.

DynamoDB

# Your code here to create the Products table with Category as partition key and ProductID as sort key

Need a hint?

Use AttributeDefinitions to define Category and ProductID. Set Category as the partition key (HASH) and ProductID as the sort key (RANGE).

2

Add a Local Secondary Index (LSI) for sorting by Price

Add a Local Secondary Index named CategoryPriceIndex to the Products table. Use Category as the partition key and Price as the sort key. Define the attribute Price as a number type in the attribute definitions.

DynamoDB

CreateTable {
  TableName: "Products",
  AttributeDefinitions: [
    { AttributeName: "Category", AttributeType: "S" },
    { AttributeName: "ProductID", AttributeType: "S" },
    { AttributeName: "Price", AttributeType: "N" }  # Add Price attribute definition
  ],
  KeySchema: [
    { AttributeName: "Category", KeyType: "HASH" },
    { AttributeName: "ProductID", KeyType: "RANGE" }
  ],
  LocalSecondaryIndexes: [  # Add LSI here
    {
      IndexName: "CategoryPriceIndex",
      KeySchema: [
        { AttributeName: "Category", KeyType: "HASH" },
        { AttributeName: "Price", KeyType: "RANGE" }
      ],
      Projection: { ProjectionType: "ALL" }
    }
  ],
  BillingMode: "PROVISIONED",
  ProvisionedThroughput: {  # Required for LSI
    ReadCapacityUnits: 5,
    WriteCapacityUnits: 5
  }
}

Need a hint?

Remember that the LSI uses the same partition key Category but a different sort key Price. Add Price to AttributeDefinitions as type N (number).

3

Write a query to get products in Electronics category sorted by Price

Write a DynamoDB query to get all items from the Products table where Category equals Electronics. Use the CategoryPriceIndex Local Secondary Index to sort the results by Price in ascending order.

DynamoDB

query_params = {
  TableName: "Products",
  IndexName: "CategoryPriceIndex",  # Use the LSI
  KeyConditionExpression: "Category = :cat",  # Filter by Category
  ExpressionAttributeValues: {
    ":cat": {"S": "Electronics"}  # Set category value
  },
  ScanIndexForward: True  # Sort ascending by Price
}
# Your code here to define the query parameters

Need a hint?

Use IndexName to specify the LSI. Use KeyConditionExpression to filter by Category. Set ScanIndexForward to True to sort ascending by Price.

4

Complete the query execution setup

Add the final code line to execute the query using a DynamoDB client method called query with the query_params. Assign the result to a variable named response.

DynamoDB

response = dynamodb.query(query_params)  # Execute the query
# Your code here to assign the query result

Need a hint?

Use the DynamoDB client method query with query_params and assign the result to response.