# Set Up the DB

Form.io requires a **MongoDB-**compatible database to run its platform. Refer to the process that matches the anticipate deployment stack:

* MongoDB Atlas
* Azure CosmosDB 

### MongoDB Atlas

Before deployment, create an account at [MongoDB.com/atlas](https://www.mongodb.com/atlas). Follow the steps provided by MongoDB to create the cluster while observing the guidelines described below.

#### Performance

When configuring the performance of the cluster, it may be necessary to review the **advanced settings**. The particular settings selected may depend on the planned deployment. The following baseline standard may be used when determining the application's needs:

1. For production environments, the **dedicated** tier may be required.
2. Consider enabling **multi-region** replication to create additional instances for greater availability.
3. Choose a sufficient **cluster tier** based on the expected usage.\
   **M10** is typically sufficient for development.\
   **M20** is the recommended baseline for production.

#### Security

When configuring the security settings in MongoDB Atlas after clicking **Create Cluster**, refer to the following guidelines:

1. Record the **username** and **password** for use in the connection string later.
2. Ensure the IP address of the Azure VM is added to the whitelist.

If the deployment requires additional security that would mandate a private endpoint, refer to the [MongoDB Documentation](https://www.mongodb.com/docs/atlas/security-cluster-private-endpoint/) regarding connecting cloud services to a private endpoint.

#### Connection String

From the MongoDB Atlas **Project Overview**, open the **Connect Menu** and generate a **legacy (standard**) **connection string**. It will appear similar to the following:

{% code overflow="wrap" %}

```
mongodb://formio:<db_password>@<node1>,<node2>,<node3>/<dbName>?replicaSet=<replicaSetName>&ssl=true&authSource=admin&retryWrites=true&w=majority&appName=<appName>
```

{% endcode %}

Note the following placeholders:

* **\<node1>...** : These will correspond to the individual instances of the DB.
* **\<replicaSetName>**:  The atlas-generated replica set name.
* **\<appName>:** Typically *formio*.
* **\<dbName>:** Typically *formio* (added by us).

Supplying the required info creates a connection string similar to the following, using the example password *badPassword*:

{% code overflow="wrap" %}

```
mongodb://formio:badPassword@formio-shard-00-00.qa5hm.mongodb.net:27017,formio-shard-00-01.qa5hm.mongodb.net:27017,formio-shard-00-02.qa5hm.mongodb.net:27017/formio?replicaSet=atlas-6fxuzs-shard-0&ssl=true&authSource=admin&retryWrites=true&w=majority&appName=formio
```

{% endcode %}

**Note that the database name `formio` is added so that the Form.io deployment will initialize with this database instead of the default `test` database.** \
\
Also observe that `?ssl=true` indicates a database connection over SSL,  `&retryWrites=true` indicates retryWrites is supported, and uses the `admin` database rather than IAM to authenticate.&#x20;

Record this connection string for later.

### Cosmos DB

{% hint style="info" %}
Before continuing please review the [Cosmos DB for MongoDB (vCore) compatibility doc](https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/vcore/compatibility).
{% endhint %}

Follow these instructions to setup a new database.

1. Go to Cosmos DB in your Azure portal
2. Create an **Azure Cosmos DB for MongoDB (vCore)**

{% hint style="warning" %}
We recommend (as does Azure) using a vCore-based DB since [it has more comprehensive support for native MongoDB features](https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/vcore/) and certain Form.io functionality may be impaired if using an RU-based DB. \
\
Here is documentation regarding [migrating from RU to vCore](https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/how-to-migrate-vcore).
{% endhint %}

3. Once the DB is created, copy the connection string for use in your Form.io deployment.&#x20;

   <pre data-overflow="wrap"><code>mongodb+srv://formiodbusername:&#x3C;password>@formio-cosmos-db.global.mongocluster.cosmos.azure.com/&#x3C;db_name>?tls=true&#x26;authMechanism=SCRAM-SHA-256&#x26;retrywrites=true&#x26;maxIdleTimeMS=120000
   </code></pre>
4. After copying it, you'll need to add your Form.io database name (`<db_name>` above) and the password. This name (`formio` for example) is placed in-between `formio-cosmos-db.global.mongocluster.cosmos.azure.com/` and `?tls=true`. The filled-in connection string should look like this:

   <pre data-overflow="wrap"><code>mongodb+srv://formiodbusername:supersecretpassword@formio-cosmos-db.global.mongocluster.cosmos.azure.com/formio?tls=true&#x26;authMechanism=SCRAM-SHA-256&#x26;retrywrites=true&#x26;maxIdleTimeMS=120000
   </code></pre>

<details>

<summary>Click here for RU-based documentation</summary>

{% hint style="info" %}
Before continuing please review the feature matrix and compatibility docs below.&#x20;
{% endhint %}

Currently, Form.io supports the latest version of Cosmos DB for Mongo DB (RU), which at the time of writing is 7.0.&#x20;

If you run into any issues as it relates version compatibility please consult the [Related Content](https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/upgrade-version#related-content) section of the Azure Documentation (or [here](https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/feature-support-70) for version 7.0) as well as this doc regarding how to [Upgrade the API version of your Azure Cosmos DB for MongoDB account](https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/upgrade-version).&#x20;

If you an incompatibility is found, please provide details by contacting [support@form.io](emailto:support@form.io).

{% hint style="warning" %}
When creating the DB, ensure that you don't limit the total RU throughput that can be provisioned.
{% endhint %}

As with the vCore connection string, you'll need to provide a Form.io database name (i.e. `formio`), which is placed in-between `formio-developer-cosmos-db.mongo.cosmos.azure.com:10255/` and `?ssl=true`.

{% code overflow="wrap" %}

```
mongodb://formio-developer-cosmos-db:[PASSWORD]@formio-developer-cosmos-db.mongo.cosmos.azure.com:10255/formio?ssl=true&replicaSet=globaldb&retrywrites=false&maxIdleTimeMS=120000&appName=@formio-developer-cosmos-db@
```

{% endcode %}

### Cosmos DB for MongoDB (RU) Notes

### Partial Support for $lookup

Cosmos DB supports basic `$lookup` operations for equality matches but does not support advanced features like uncorrelated subqueries. For complex join operations, consider restructuring your data model or using alternative methods.

{% hint style="warning" %}
The `$lookup` aggregation does not yet support the [uncorrelated subqueries](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#join-conditions-and-uncorrelated-sub-queries) feature that's introduced in server version 3.6. If you attempt to use the `$lookup` operator with the `let` and `pipeline` fields, an error message that indicates that *`let` is not supported* appears. This still applies in version 7.0.
{% endhint %}

{% hint style="warning" %}
In order to do a sort on a property **in version 3.6**, there must be an index set. This is different from how MongoDB works as MongoDB allows sorting on any property regardless of an index being set.&#x20;

To resolve this, a [**wildcard index**](https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/indexing#create-a-wildcard-index) can be added to the following collections:

```
db.submissions.createIndex( {"$**": 1 } );
db.forms.createIndex( {"$**": 1 } );
db.projects.createIndex( {"$**": 1 } );
db.actions.createIndex( {"$**": 1 } );
db.roles.createIndex( {"$**": 1 } );
db.tags.createIndex( {"$**": 1 } );
```

To do a sort on multiple properties, you must create a compound index. \
\
**While wildcard indexes are a good starting place to enable sorting, documents with many fields may have a high Request Unit (RU) charge for writes and updates. Therefore, if you have a write-heavy workload, you should opt to individually index paths as opposed to using wildcard indexes.**&#x20;
{% endhint %}

### [Cosmos Feature Support Matrix](https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/upgrade-version#related-content)

Refer to the [primary Microsoft documentation](https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/feature-support-70) for a complete list of Database Commands.

<table data-full-width="true"><thead><tr><th>Feature</th><th>Context</th><th width="100">3.6</th><th width="100">4.2</th><th width="100">5.0</th><th width="81.84375">6.0</th><th width="83.85546875">7.0</th></tr></thead><tbody><tr><td>find</td><td>Query and Write Operation</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>insert</td><td>Query and Write Operation</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>update</td><td>Query and Write Operation</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>delete</td><td>Query and Write Operation</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>aggregate</td><td>Aggregation Command</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>count</td><td>Aggregation Command</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>addFields</td><td>Aggregation Stage</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>count</td><td>Aggregation Stage</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>group</td><td>Aggregation Stage</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>limit</td><td>Aggregation Stage</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>lookup</td><td>Aggregation Stage</td><td>Partial</td><td>Partial</td><td>Partial</td><td>Partial</td><td>Partial</td></tr><tr><td>match</td><td>Aggregation Stage</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>project</td><td>Aggregation Stage</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>skip</td><td>Aggregation Stage</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>sort</td><td>Aggregation Stage</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>unwind</td><td>Aggregation Stage</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>and</td><td>Boolean Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>or</td><td>Boolean Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>setIntersection</td><td>Set Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>eq</td><td>Comparison Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>gt</td><td>Comparison Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>gte</td><td>Comparison Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>lt</td><td>Comparison Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>lte</td><td>Comparison Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>ne</td><td>Comparison Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>in</td><td>Comparison Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>nin</td><td>Comparison Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>concatArrays</td><td>Array Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>filter</td><td>Array Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>reduce</td><td>Array Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>size</td><td>Array Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>in</td><td>Array Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>sum</td><td>Accumulator Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>push</td><td>Accumulator Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>addToSet</td><td>Accumulator Expression</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>String</td><td>Type</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>Object</td><td>Type</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>Array</td><td>Type</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>ObjectId</td><td>Type</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>Boolean</td><td>Type</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>and</td><td>Logical Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>or</td><td>Logical Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>exists</td><td>Element Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>regex</td><td>Evaluation Query Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>all</td><td>Array Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>elemMatch</td><td>Array Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>size</td><td>Array Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>set</td><td>Update Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>rename</td><td>Update Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>unset</td><td>Update Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>addToSet</td><td>Array Update Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>pull</td><td>Array Update Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>push</td><td>Array Update Operator</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>sort</td><td>Update Modifier</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>Single Field Index</td><td>Indexes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>Compound Index</td><td>Indexes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>Multikey Index</td><td>Indexes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>Text Index</td><td>Indexes</td><td>No</td><td>No</td><td>No</td><td>No</td><td>No</td></tr><tr><td>2dsphere</td><td>Indexes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>2d Index</td><td>Indexes</td><td>No</td><td>No</td><td>No</td><td>No</td><td>No</td></tr><tr><td>Hashed Index</td><td>Indexes</td><td>No</td><td>No</td><td>No</td><td>No</td><td>No</td></tr><tr><td>TTL</td><td>Index Properties</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>Unique</td><td>Index Properties</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr><tr><td>Partial</td><td>Index Properties</td><td>No</td><td>Only unique indexes</td><td>Only unique indexes</td><td>Only unique indexes</td><td>Only unique indexes</td></tr><tr><td>Case Insensitive</td><td>Index Properties</td><td>No</td><td>No</td><td>No</td><td>No</td><td>No</td></tr><tr><td>Sparse</td><td>Index Properties</td><td>No</td><td>No</td><td>No</td><td>No</td><td>No</td></tr><tr><td>Background</td><td>Index Properties</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td><td>Yes</td></tr></tbody></table>

</details>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://help.form.io/deploy/cloud-deployment/azure-deployment/set-up-the-db.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.