# 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](https://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>