Should I use Mongo DB API or Cosmos SQL API in Azure Cosmos DB?

It's a question I asked myself for a long time, so I've decided to answer it for myself and for someone else.

What is Cosmos DB?

Cosmos SQL is a data model within Cosmos DB that supports SQL-like queries against JSON data. It provides a way to store and query structured data in a distributed environment, and it can be a good choice for certain types of applications.

Features

1. Multi-Model Database. Cosmos DB supports multiple data models such as document, key-value, graph, and column-family, allowing developers to use the most appropriate data model for their specific use case.
2. Globally Distributed. Cosmos DB provides global distribution of data across multiple regions and zones, with automatic replication and failover capabilities. This allows applications to provide low latency and high availability for users around the world.
3. Automatic Scaling. Cosmos DB provides automatic scaling of throughput and storage, based on the needs of the application. This means that as the application grows, Cosmos DB can automatically scale to handle the increased workload.
4. Consistency Levels. Cosmos DB provides a range of consistency levels to choose from, allowing developers to balance between data consistency and availability.
5. Multi-API Support. Cosmos DB supports multiple APIs such as SQL, MongoDB, Cassandra, Azure Tables, and Gremlin, making it easy to use with existing applications and tools.
6. Security. Cosmos DB provides advanced security features such as encryption at rest and in transit, role-based access control, and virtual network integration.
7. Analytics and Visualization. Cosmos DB provides built-in analytics and visualization capabilities with Azure Synapse Link, allowing developers to perform real-time analytics on their data.
8. Developer Productivity. Cosmos DB provides a range of tools and SDKs to make it easy for developers to get started with the service, including Azure Portal, Azure CLI, Azure PowerShell, and various programming language SDKs.

Overall, Cosmos DB provides a comprehensive set of features for building modern, scalable, and highly available applications in the cloud.

Can I Mix and Match APIs?

Yes and No.

Yes

You can use both SQL and MongoDB APIs in Azure Cosmos DB at the same time. Azure Cosmos DB is designed to be a multi-model database service that supports multiple APIs, including SQL, MongoDB, Cassandra, Azure Table, and Gremlin. This means that you can store data using different data models, and access that data using the appropriate API for your application.

For example, you could store structured data in a SQL API container, and unstructured data in a MongoDB API container within the same Cosmos DB account. This provides flexibility in how you structure and access your data, while still taking advantage of the benefits of a globally distributed, multi-model database service.

No

You cannot use different APIs in the same container in Azure Cosmos DB. Each container in Cosmos DB is associated with a specific API and data model, and data stored in a container must conform to that data model.

For example, if you create a container using the MongoDB API, you can only store data in that container using the MongoDB data model and API. Similarly, if you create a container using the SQL API, you can only store data in that container using the SQL data model and API.

Summary

The short answer is no. See expanded answer above. An instance of data is tightly bound to API, and has to be set at the creation time. You cannot change API level later.

When you create a container in Cosmos DB, you choose a specific API and data model that is associated with that container. The data stored in that container must conform to the data model and API that was chosen.

If you need to use a different API for your data, you will need to create a new container with the appropriate API and data model, and then migrate your data to the new container. Depending on the amount of data and the complexity of your application, this migration process can be a complex task.

Which one is Better?

There are some scenarios where using MongoDB in Cosmos DB might be a better choice. One reason is that MongoDB has a more flexible data model than Cosmos SQL. MongoDB supports nested documents and arrays, which can be more difficult to model in Cosmos SQL. Additionally, MongoDB has a richer set of query operators and aggregation functions, which can make it easier to work with complex data.

Another reason to use MongoDB in Cosmos DB is if you're already using MongoDB in your application and want to take advantage of Cosmos DB's global distribution, low latency, and automatic scaling features. By using the MongoDB API in Cosmos DB, you can continue to use the same MongoDB drivers and tools that you're already familiar with, while also gaining the benefits of Cosmos DB's global distribution and scalability.

Ultimately, the choice between using Cosmos SQL or MongoDB in Cosmos DB depends on your specific application requirements and data model. You should evaluate both options to determine which one is the best fit for your needs.

In Layman's Terms, Which One Should I Go For?

Short Answer

MongoDB.

Long Answer

It depends :) See feature comparison table below.

	SQL API	MongoDB API
Data Model	Tabular, column-family	Document-based
Query Language	SQL-like	Document-based
Indexing	Supports indexing on specific columns	Supports indexing on fields within documents
Transactions	Supports multi-document transactions	Supports single-document transactions
Consistency	Provides strong consistency by default	Provides eventual consistency by default
Scalability	Highly scalable, distributed database	Highly scalable, distributed database
Programming Model	Similar to traditional relational databases	Optimized for working with JSON documents

Tools

Cosmos SQL

1. Azure Portal. The Azure Portal provides a web-based interface for managing and querying data in Cosmos DB. However, it honestly feels clunky and is only suitable for very simple ad-hoc queries.
2. Cosmos DB SQL Studio. Very simple unofficial extension for VS Code.
3. Cosmos DB Explorer. A promising Windows Desktop application, however I found it crashing often and in general really unstable. And it's Windows-only.

As a developer who works with Cosmos DB, I have faced many challenges and frustrations with the lack of proper tools for this database service. It's worth noting I have tried really hard to find a good tool for Cosmos DB. There are none, even official ones. And it's been a while since it exists.

These limitations are not insurmountable, but they require extra work and code to overcome. And this is where a good tool for Cosmos DB would come in handy.

Unfortunately, such a tool does not exist yet. The official tools provided by Microsoft are either outdated, incomplete, or buggy. For example, the Azure portal only supports the SQL API and has a limited UI for browsing and querying data. The Azure Data Studio extension only supports the MongoDB API and has a poor performance and user experience. The Azure Cosmos DB Emulator only works on Windows and does not support all the features of the service.

Therefore, I think there is a huge gap in the market for a good tool for Cosmos DB. A tool that would make the life of developers easier and more productive. A tool that would leverage the full potential of Cosmos DB and help to create better applications. A tool that would be updated regularly and supported by a dedicated team.

If you are a developer who works with Cosmos DB, or if you are interested in creating such a tool, please let me know. I would love to hear your thoughts and feedback on this topic.

Mongo

There are hundreds of developer tools available for working with MongoDB, depending on your preferences and development environment. Here are some popular ones:

1. MongoDB Compass. A graphical user interface for MongoDB. https://www.mongodb.com/products/compass
2. Robo 3T. A lightweight, open-source MongoDB GUI client. https://robomongo.org/
3. Studio 3T. A comprehensive MongoDB GUI client with advanced features such as SQL support, aggregation pipeline builders, and query autocompletion. https://studio3t.com/
4. NoSQLBooster for MongoDB. A smart GUI client for MongoDB with features such as IntelliShell, visual query builder, and schema analyzer. https://nosqlbooster.com/
5. Aqua Data Studio: A multi-platform database IDE that supports MongoDB, with features such as visual analytics, data import/export, and SQL debugging. From the maker of Cosmos DB but with no Cosmos SQL support :) https://www.aquadatastudio.com/
6. Navicat for MongoDB. A GUI tool for managing MongoDB databases with features such as data modeling, data synchronization, and query building. https://www.navicat.com/en/products/navicat-for-mongodb
7. Datagrip. A multi-platform database IDE that supports MongoDB, with features such as code completion, code analysis, and schema editor. https://www.jetbrains.com/datagrip/

These are just a few examples of the many developer tools available for working with MongoDB.

Pricing

It's difficult to compare the average prices of Cosmos DB with SQL API and Mongo API directly because the pricing model for Cosmos DB is based on several factors including the choice of API, data storage, throughput, and regions. However, here are some factors to consider:

1. Data storage: Both SQL API and Mongo API use the same pricing model for data storage in Cosmos DB, which is based on the amount of data stored per month. The price per GB of data stored is the same regardless of which API is used.
2. Request units: Request units (RUs) are a measure of the throughput capacity of a Cosmos DB database. Both SQL API and Mongo API use the same pricing model for RUs. The cost of RUs varies depending on the performance level chosen, which is based on the number of RUs per second required for your application.
3. Regions: The cost of using Cosmos DB also depends on the number of regions selected for your deployment. Each region has its own pricing, so the more regions you select, the higher the cost.

In general, the cost of using SQL API and Mongo API in Cosmos DB will depend on the specific requirements of your application and the amount of data storage and throughput required. It's important to carefully consider your application's needs and compare pricing options before choosing an API.

Verdict - mostly equal.