I’ve been recently migrating some legacy data sets into Cosmos DB and had a requirement to include an project_id field that increments rather than using Cosmos DB’s default document id.
To accomplish this, I first approached it client side. I quickly abandoned that approach because it was very susceptible to race conditions where two users claim the same project_id. My final solution was to update the ‘Add Project’ post endpoint of the API to query for the latest value just before saving.
As an example, here is a console app version written in C# that does the same thing:
using System;
using Microsoft.Azure.Cosmos;
namespace CosmosDBProject
{
class Program
{
private static readonly string endpoint = "<your-cosmos-db-endpoint>";
private static readonly string key = "<your-cosmos-db-key>";
private static readonly string databaseId = "<your-cosmos-db-database-id>";
private static readonly string containerId = "<your-cosmos-db-container-id>";
static async System.Threading.Tasks.Task Main(string[] args)
{
using (CosmosClient client = new CosmosClient(endpoint, key))
{
// Get a reference to the container
Container container = client.GetContainer(databaseId, containerId);
// Get the current max value of the project_id field
int currentMaxId = await GetCurrentMaxProjectId(container);
// Increment the value of the project_id field for the new project
int newProjectId = currentMaxId + 1;
// Save the new project to the database
await SaveProject(container, newProjectId);
Console.WriteLine("New project saved with id: " + newProjectId);
}
}
private static async System.Threading.Tasks.Task<int> GetCurrentMaxProjectId(Container container)
{
QueryDefinition query = new QueryDefinition("SELECT VALUE MAX(p.project_id) FROM p");
FeedIterator<int> resultSet = container.GetItemQueryIterator<int>(query);
if (resultSet.HasMoreResults)
{
FeedResponse<int> response = await resultSet.ReadNextAsync();
return response.FirstOrDefault();
}
return 0;
}
private static async System.Threading.Tasks.Task SaveProject(Container container, int projectId)
{
// Define the new project object
dynamic newProject = new
{
project_id = projectId,
name = "Project " + projectId,
start_date = DateTime.UtcNow
};
// Save the new project to the database
await container.CreateItemAsync<dynamic>(newProject, new PartitionKey(projectId.ToString()));
}
}
}
Before adding a new item, we get the max project id using the following query:
SELECT VALUE MAX(p.project_id) FROM p
Once we have that, we can increment the value, set it as the project_id, and save the new project to the Cosmos DB container.