SQL API vs MongoDB API in Azure Cosmos DB: Key Differences and Usage

Azure Cosmos DB's SQL API uses a JSON document model with SQL-like queries, ideal for relational-style querying. The MongoDB API offers compatibility with MongoDB drivers and query syntax, making it easier to migrate or build apps using MongoDB tools.

Quick Comparison

This table summarizes the main differences between Azure Cosmos DB's SQL API and MongoDB API.

Feature	SQL API	MongoDB API
Data Model	JSON documents with SQL-like schema	JSON documents compatible with MongoDB BSON format
Query Language	SQL-like syntax with rich JSON support	MongoDB query language and operators
Driver Support	Native Cosmos DB SDKs	MongoDB drivers (Node.js, Python, etc.)
Use Case	Apps needing SQL-style queries on JSON	Apps migrating from or using MongoDB ecosystem
Indexing	Automatic indexing with custom policies	Automatic indexing similar to MongoDB
Compatibility	Native Cosmos DB features	Supports MongoDB wire protocol for compatibility

Key Differences

The SQL API in Cosmos DB is designed for querying JSON documents using a familiar SQL-like language. It supports rich JSON querying features such as JOINs, aggregates, and nested queries, making it suitable for applications that require complex querying on hierarchical data.

In contrast, the MongoDB API provides wire protocol compatibility with MongoDB. This means you can use existing MongoDB drivers and tools to interact with Cosmos DB without changing your application code. It supports MongoDB query operators and commands, which is ideal for developers familiar with MongoDB or migrating existing MongoDB workloads.

While both APIs store data as JSON documents, the SQL API offers more native Cosmos DB features and query flexibility, whereas the MongoDB API focuses on compatibility and ease of migration from MongoDB environments.

Code Comparison

Here is how you insert and query a document using the SQL API in Cosmos DB.

javascript

const { CosmosClient } = require("@azure/cosmos");

const endpoint = "<your-cosmos-endpoint>";
const key = "<your-cosmos-key>";
const client = new CosmosClient({ endpoint, key });
const database = client.database("MyDatabase");
const container = database.container("MyContainer");

async function run() {
  // Insert a document
  const newItem = { id: "1", name: "Alice", age: 30 };
  await container.items.create(newItem);

  // Query documents with SQL
  const querySpec = {
    query: "SELECT * FROM c WHERE c.age > @age",
    parameters: [{ name: "@age", value: 25 }]
  };

  const { resources: results } = await container.items.query(querySpec).fetchAll();
  console.log(results);
}

run();

Output

[ { id: '1', name: 'Alice', age: 30 } ]

MongoDB API Equivalent

Here is how you insert and query a document using the MongoDB API in Cosmos DB.

javascript

const { MongoClient } = require("mongodb");

const uri = "mongodb://<your-cosmos-account>.mongo.cosmos.azure.com:10255/?ssl=true&replicaSet=globaldb";
const client = new MongoClient(uri, {
  auth: { user: "<username>", password: "<password>" },
  useUnifiedTopology: true
});

async function run() {
  await client.connect();
  const database = client.db("MyDatabase");
  const collection = database.collection("MyContainer");

  // Insert a document
  await collection.insertOne({ _id: "1", name: "Alice", age: 30 });

  // Query documents
  const results = await collection.find({ age: { $gt: 25 } }).toArray();
  console.log(results);

  await client.close();
}

run();

Output

[ { _id: '1', name: 'Alice', age: 30 } ]

When to Use Which

Choose the SQL API when your application benefits from SQL-like querying on JSON documents and you want to leverage Cosmos DB's native features like rich indexing and multi-model support.

Choose the MongoDB API if you are migrating an existing MongoDB application or want to use MongoDB drivers and tools without rewriting your code. It simplifies migration and integration with MongoDB ecosystems.

In summary, use SQL API for new apps needing flexible JSON queries, and MongoDB API for compatibility and migration ease.

Key Takeaways

SQL API offers rich SQL-like queries on JSON documents with native Cosmos DB features.

MongoDB API provides compatibility with MongoDB drivers and query syntax for easy migration.

Use SQL API for new apps needing complex JSON queries and MongoDB API for existing MongoDB workloads.

Both APIs store JSON documents but differ in query language and ecosystem support.

Choosing depends on your app's query needs and existing technology stack.