Velo: Integrate your Microsoft Azure Database with your Wix Site

Wix data gives you powerful data storage and retrieval capabilities and the ability to create and maintain your own data collections. But what if you have your own database already, or want to host your data on a relational database?

With the external database connections adaptor, you can use your MySQL database hosted on Azure and fully integrate it into your Wix site. You can use any of the wix-data and wix-dataset APIs with external database connections, as well as connect repeaters, tables, and other page elements to your MySQL data.

If you want to do this with AWS, see Integrate your AWS Databases with your Wix Site. For Google Cloud Platform, see Integrate your Google Cloud Platform Databases with your Wix Site.

Overview

This tutorial walks you through setting up a MySQL instance and database on Azure, setting up the Wix adaptor, and connecting it to your Wix site.

Here's what's involved:

1. Create a resource group.
2. Create a virtual network.
3. Create an Azure database server for MySQL.
4. Create a new database and table.
5. Create a web app.
6. Set up a key vault and secrets.
7. Test your adaptor.
8. Connect your Wix site.

Create a Resource Group

A resource group is a container that holds related resources for an Azure solution. The resource group contains the database, web app, and key vault which are created in the tutorial.

To create a resource group:

1. From the Azure portal, search for and select Resource groups.

   

2. Click Create, then select your Subscription, enter a name for your Resource group, and select a region.

3. Click Review and create.

4. On the following page, click Create.

   

Create a Virtual Network

Create a virtual network so that your database and web app can talk to each other.

1. From the Azure portal, search for and select Virtual networks, then click Create.

   

2. Select your Subscription and the Resource group that you created in the previous step.

3. Enter a Name for your virtual network, select a Region, and click Next : IP Addresses.

   

4. Click Add subnet.

5. Enter a Subnet name.

6. Enter a Subnet address range of 10.1.1.0/24.

7. Select Microsoft Sql in the Services dropdown and click Add.

8. Click Review + create, then click Create on the following page.

   

9. When the virtual network creation completes, click Go to resource.

10. Click Subnets, then click the subnet that you created.

    

11. In the SUBNET DELEGATION section, select Microsoft.Web/serverFarms from the Delegate subnet to a service dropdown, then click Save.

    

You now have the following:

• A resource group called myResourceGroup
• A virtual network called myVirtualNetworks
• A subnet called mySubnet

Create an Azure Database Server for MySQL

1. From the Azure portal, search for and select Azure Database for MySQL servers, then click Create Azure Database for MySQL Server.

   

2. Under Flexible server, click Create.

   

3. Select your Subscription and the Resource group that you created earlier.

4. Enter a Server name.

5. Select the same Region as you chose for your resource group.

6. Enter an Admin username, and Password.

7. Click Review + create and on the next page click Create.

8. When the deployment completes, click Go to resource to go to the server's overview page.

   

9. Click Connection security.

10. Set Allow access to Azure services to Yes.

11. Set Enforce SSL connection to DISABLED.

12. Under Firewall rules, click Add existing virtual network.

    

13. Enter a vnet rule Name.

14. Select your Subscription and the Virtual network and Subnet name that you created earlier.

15. Click OK.

16. After the changes have been processed, click Save.

    

Create a New Database and Table

You created a database server in the previous step. This section walks you through creating a new MySQL database and table.

To create a new database and table:

1. From the database server's Overview page, open the Azure Cloud Shell by clicking the button on the toolbar, next to the search bar.

2. If this is the first time you have opened the cloud shell, at the welcome prompt select Bash.

3. Select your Subscription and click Create storage.

   

4. Login to the database server using the following command, then enter your password.

   Copy

   Use your Server name for the --host parameter and your Server admin login name for the --user parameter.

   

5. After logging in, create a new database called mysqlTutorial using the following command:

   Copy

6. Create a new table called contacts as follows:

   Copy

7. Insert a row of data into your new table:

   Copy

You now have a new database called mysqlTutorial with a table called contacts containing a single row of data.

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. Add the virtual network to the web app.

3. Create a key vault and secrets to securely store your database credentials.

1. Search for App Services and select it from the Services list.

   

2. Click Create.

3. Select your Subscription and the Resource group that you created earlier.

4. Enter an instance Name.

5. Select Docker Container and Linux.

6. Select the same Region as your database.

7. Click Next: Docker.

8. Select Single Container, Docker Hub, and Public.

9. Enter veloex/velo-external-db:latest in the Image Name and Tag field.

10. Click Review + create then click Create.

11. When the create completes, click Go to resource.

    

    Now configure your web app to use the virtual network that you configured earlier, so that the database and the adaptor are on the same network.

12. Click on Networking then VNet integration

    

13. Click Add VNet.

14. Select the Virtual Network that you created earlier.

15. Click Select Existing and select the Subnet that you created earlier.

16. Click OK.

    

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 should not be visible. Use the Key Vault to store and access these variables securely.

1. Search for Key vaults and select it from the Services list.

2. Click Create.

   

3. Select your Subscription and the Resource group that you created earlier.

4. Enter a Key vault name. For the tutorial call it my-velo-DB-key-vault.

5. Click Review + create and then Create on the following page.

6. When the key vault creation completes, click Go to resource.

   

7. From the key vault Overview page, click Secrets.

   

   Define 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:

   Copy

   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.

8. For each of the 5 secrets, do the following:

   1. Click Generate/Import.

   2. Select Manual from the Upload options dropdown.

   3. Enter the secret Name and Value.

   4. Click Create.

   

When finished, your key vault looks as follows:

Create an Identity for the Web App

To give the web app access to the secrets, create an identity, then add that identity to the key vault's access policies.

1. From the web app overview page, click Identity.

2. Set the status to On and click Save.

   

3. An Object (principal) ID is created. Save the object ID value to use when creating an Access Policy for your key vault.

   

Add the Web App's Identity to the Key Vault's Access Policies

Now you need to give your web app access to your secrets. Do this by adding the web app's identity object Id to the access policies of the key vault.

1 From the key vault Overview page, click Access policies.

2. Click Add Access Policy.

   

3. Select Secret Management from the Configure from template dropdown.

4. Click None Selected.

5. Paste the Object ID from the Create an Identity for the Web App step, into the Principal search field.

6. Select the item that appears below the search field and click Select.

7. Click Add.

   

8. Click Save.

   

Configure Web App Environment Variables

Now we'll configure the environment variables for the adaptor. The variables are configured in the web app. The values for these variables are retrieved form the key vaults's secrets.

1. From the web app Overview page, click Configuration.

2. Click New application setting.

   

3. 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. The vault name we used for the tutorial is my-velo-DB-key-vault.

   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.

   

   Now set up 2 non-secret environment variables.

4. Click New application settings to add 2 more application settings as per the table below:

   Name	Value
   TYPE	mysql
   CLOUD_VENDOR	azure

5. Click Save and then Continue after entering the new application settings.

   

To test your web app configuration, click the URL from the overview page.

The following page displays DB Config Status and Connection Status in green. If you added a PERMISSIONS secret, the Permissions Config Status is also green. If you didn't, it's yellow.

If one of the statuses is red, check the configuration, secret values, and environment variables. Make any necessary changes and redeploy the service.

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.

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:

Connect to Your Wix Site

Now that we have a database and an adaptor service, we're ready to add the database as an external collection on our Wix site.

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.