> Portal Navigation:
>
> - Append `.md` to any URL under `https://dev.wix.com/docs/` to get its markdown version.
> - Pages are either content pages (article or reference text) or menu pages (a list of links to child pages).
> - To get a menu page, truncate any URL to a parent path and append `.md` (e.g. `https://dev.wix.com/docs/sdk.md`, `https://dev.wix.com/docs/sdk/core-modules.md`).
> - Top-level index of all portals: https://dev.wix.com/docs/llms.txt
> - Full concatenated docs: https://dev.wix.com/docs/llms-full.txt
## Resource: Integrate your Microsoft Azure Database with your Wix Site
## Article: Integrate your Microsoft Azure Database with your Wix Site
## Article Link: https://dev.wix.com/docs/develop-websites-sdk/code-your-site/work-with-data/external-databases/microsoft-azure/integrate-your-microsoft-azure-database-with-your-wix-site.md
## Article Content:
# Integrate Your Microsoft Azure Database with Your Wix Site
> **Note:** This feature is only available for sites with certain premium plans. If you need to use this feature, you can [upgrade your site](https://www.wix.com/upgrade/website). Learn more about [premium plans](https://dev.wix.com/docs/develop-websites-sdk/maintain-your-site/scale-your-site/about-premium-plans.md).
Wix allows users to connect an external database to Wix sites using an [external database adaptor](https://dev.wix.com/docs/develop-websites-sdk/code-your-site/work-with-data/external-databases/overview/about-integrating-external-databases-with-your-wix-site.md#external-database-adaptors). Once the connection is set up, users can interact with these databases and use them to populate site elements as though they were Wix CMS collections.
With an external database adaptor, you can use your MySQL database hosted on Azure and fully integrate it into your Wix site. This means your Azure data can be managed via the [Wix Data APIs](https://dev.wix.com/docs/rest/business-solutions/cms/introduction.md), and used (with or without [datasets](https://dev.wix.com/docs/velo/velo-only-apis/$w/dataset/introduction.md)) to populate Wix UI elements like [repeaters](https://dev.wix.com/docs/develop-websites-sdk/get-started/tutorials/data/tutorial-display-database-collection-content-in-a-repeater.md) and [tables](https://support.wix.com/en/article/cms-formerly-content-manager-displaying-collection-content-in-a-table).
## Overview
This article walks you through the following:
1. Creating a web app.
2. Setting up a key vault and secrets.
3. Testing your adaptor.
4. Connecting your Wix site.
This tutorial assumes you already have your own Azure database server for MySQL with a table containing some data. If you don't have this set up, refer to the [Azure Database for MySQL documentation](https://learn.microsoft.com/en-us/azure/mysql/) for instructions.
This tutorial uses a container image with all the functionality needed to interface between your database and your Wix site. If you want to look under the hood, you can have a look at the [service plugin specifications](https://www.wix.com/velo/reference/spis/external-database-collections) for external database collections, and an overview of what's involved in [building your own adaptor](https://support.wix.com/en/article/velo-working-with-external-database-collections).
**Prerequisites for Read-Write Access to Your Database Tables:**
If you want your table to be read-write on your Wix site, it must contain the following columns:
- `_id`
- `_createdDate`
- `_updatedDate`
- `_owner`
Tables without these columns will be read-only in your Wix site.
> **Note:** We don't provide specific instructions for any configuration in Azure, as Azure's UI and flows may change. For further details on any of the instructions below, see the [Azure Database for MySQL documentation](https://learn.microsoft.com/en-us/azure/mysql/).
## Step 1 | Create a Web App
A web app service is needed to run the adaptor and make it accessible to your Wix site.
To deploy the adaptor as a web app:
1. Create a web app service to run the container.
2. Use the following **Container Image URL**:
```bash
veloex/velo-external-db:latest
```
3. Add the virtual network to the web app.
## Step 2 | Set up a Key Vault and secrets
The external database adaptor uses environment variables to configure database credentials and connection details. Some of these variables are sensitive and shouldn't be visible. Use the Key Vault to store and access these variables securely.
1. Create a new Key Vault.
2. Define and then create the following 5 secrets:
* **HOST**: The host name of your database instance. It can be found on your instance's overview page as the **Server name**.
* **USER**: The username used to connect to your database instance. It can be found on your instance's overview page as the **Server admin login name** as shown below.
* **PASSWORD**: The server admin login password used to connect to your SQL instance.
* **DB**: The database name. When you created your database in the previous steps, you called the database **mysqlTutorial.** You can find this in the MySQL database server overview - under **Available Resources** at the bottom of the page.
* **SECRETKEY**: A secret key that's used when connecting your Wix site to the adaptor. To create a level of authentication between your site and the adaptor, each request your site sends to the adaptor contains this value in the payload. For the tutorial, use **myBigSecret** as the SECRETKEY value.
* **PERMISSIONS** (optional) A stringified JSON object that defines the read and write permissions for the tables in your database. When setting up permissions, note a couple of important things:
- If you don't set permissions, they default to admin. With admin permissions, only site admins can read or write to the external database from a Wix site. API calls or CMS connections to the database don't work for anyone who isn't a site admin. This means site code that communicates with the external database might not work for site visitors.
- When setting up permissions, make sure you use the ID of the table you want to read and write to, and not the database ID.
> **Note:**
> Customizing permissions for external databases is currently a developer preview feature, and may change.
> Changes to permission settings are not reflected in the editor.
Learn more about configuring database permissions
You can use the **PERMISSIONS** secret to customize the permission settings for each table in a database. The JSON object contains one key, **collectionPermissions**, whose value is an array of objects. Each object in this array contains the permissions settings for one of the collections in the database, using the following parameters:
* **id:** The collection ID.
* **read**: An array of strings representing which roles can read from the collection.
Options: 'Admin', 'Member', 'Visitor'
* **write**: An array of strings representing which roles can write to the collection.
Options: 'Admin', 'Member', 'Visitor'
Example **PERMISSIONS** value:
```json
{
"collectionPermissions": [
{
"id": "Contacts",
"read": ["Admin", "Member"],
"write": ["Admin"]
}
]
}
```
Note that you need to store the **PERMISSIONS** JSON object as a string. To do this, first write the object as usual and use a tool such as your browser’s developer tools console to stringify it.
3. Create an identity for the web app.
4. Add the web app's Identity to the Key Vault's access policies.
5. Configure Web App Environment Variables
For each of the secrets you created in the previous section, add an application setting as per the table below:
Substitute the value for "VaultName" with your own key vault name.
| Name | Value |
| ----------- | -------------------------------------------------------------------------- |
| USER | @Microsoft.KeyVault(VaultName=my-velo-DB-key-vault;SecretName=USER) |
| HOST | @Microsoft.KeyVault(VaultName=my-velo-DB-key-vault;SecretName=HOST) |
| DB | @Microsoft.KeyVault(VaultName=my-velo-DB-key-vault;SecretName=DB) |
| PASSWORD | @Microsoft.KeyVault(VaultName=my-velo-DB-key-vault;SecretName=PASSWORD) |
| SECRET_KEY | @Microsoft.KeyVault(VaultName=my-velo-DB-key-vault;SecretName=SECRETKEY) |
| PERMISSIONS | @Microsoft.KeyVault(VaultName=my-velo-DB-key-vault;SecretName=PERMISSIONS) |
> **Note:**
> When configuring the **SECRETKEY,** the application setting is **SECRET\_KEY** but the secret name is **SECRETKEY.** This is because you cant use an "\_" in a secret name in Azure.
6. Add 2 more application settings as per the table below:
| Name | Value |
| ------------ | ----- |
| TYPE | mysql |
| CLOUD_VENDOR | azure |
## Step 3 | Test your adaptor
You can test that your service is working by making a quick request using Curl.
Use the curl command below, replacing the URL with your URL from the web app overview page, and replacing the **secretKey** value with your secret key.
```bash
curl -L -X POST 'https://velo-external-db.azurewebsites.net/schemas/list' \
-H 'Content-Type: application/json' \
--data-raw '{
"requestContext": {
"settings": {
"secretKey": "myBigSecret"
},
"role": "OWNER"
}
}'
```
The output provides a list of tables and their columns from your database. If you have python installed, you can pipe the output to **python -m json.tool** and it will give you nicely formatted JSON.
The formatted output containing a list of tables and their columns:
```json
{
"schemas": [
{
"id": "contacts",
"displayName": "contacts",
"allowedOperations": [
"get",
"find",
"count",
"update",
"insert",
"remove"
],
"maxPageSize": 50,
"ttl": 3600,
"fields": {
"name": {
"displayName": "name",
"type": "text",
"queryOperators": [
"eq",
"lt",
"gt",
"hasSome",
"and",
"lte",
"gte",
"or",
"not",
"ne",
"startsWith",
"endsWith"
]
},
"_createddate": {
"displayName": "_createddate",
"type": "datetime",
"queryOperators": [
"eq",
"lt",
"gt",
"hasSome",
"and",
"lte",
"gte",
"or",
"not",
"ne",
"startsWith",
"endsWith"
]
},
"email": {
"displayName": "email",
"type": "text",
"queryOperators": [
"eq",
"lt",
"gt",
"hasSome",
"and",
"lte",
"gte",
"or",
"not",
"ne",
"startsWith",
"endsWith"
]
},
...
```
## Step 4 | Connect to your Wix site
Now that you have a database and an adaptor service, you're ready to add the database as an external collection on your site.
> **Note:** You can only add external collections to your site if you have a [premium plan](https://dev.wix.com/docs/develop-websites-sdk/maintain-your-site/scale-your-site/about-premium-plans.md).
1. In the editor, select **Databases** from the Code sidebar (Wix Editor) or Code sidebar (Wix Studio).
2. Click the  icon next to **External Databases** and select **Add external database**.
3. Choose **Azure** as the provider of the external collection being added, then click **Next**.
4. Enter a **name** for your external collection's namespace.
5. Copy and paste your adaptor service's URL into the **endpoint URL** field.
6. Enter your database adaptor's **secret key**.
7. Click **Connect**.
The Content Management System (CMS) displays the tables. If your table contains **_id**, **_createdDate**, **_updatedDate**, and **_owner** fields, you can add data to the table directly from the CMS.
