IN THIS ARTICLE

Velo: Integrate Your Google Cloud Platform Databases 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. Learn more about premium plans.

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 External Database Collections, you can use your database hosted on Google Cloud Platform, and fully integrate it into your Wix site. You can use any of the wix-data and wix-dataset APIs with External Database Collections, as well as connect  repeaters, tables, and other page elements to your Google Cloud data.

If you want to do this with AWS see Integrate Your AWS Databases with Your Wix Site. For Microsoft Azure, see Integrate your Azure Databases with your Wix Site.

Overview

This tutorial walks you through setting up a database on Google Cloud Platform (GCP) and connecting it to your Wix site.

Supported Databases

Wix currently supports the following GCP databases:

If you don't already have your own database on GCP, we'll help you create one.

Here's what's involved:

  1. Set up a new project on Google Cloud Platform.
  2. Create one of the supported databases, if you don't already have one.
  3. Create a new collection (table) in your database.
  4. Create secrets to securely store your database credentials. 
  5. Create and deploy a new Cloud Run service to host the Wix database adaptor.
  6. Connect your Wix site to your database.

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 SPI specifications for external database collections, and an overview of what's involved in building your own adaptor.

Google Cloud Platform

This tutorial assumes a bit of knowledge about Google Cloud Platform. If you've never worked with GCP, have a look at this guide and click the Get Started For Free button.

Once you're ready to go, create a new project for this tutorial and call it velo-external-db.

Create and Configure a Database

This part of the tutorial walks you through setting up a supported Google Cloud Database and adding a table. If you already have your own database on GCP, you can skip this section. 

Note If you want read-write access to your database tables, they must contain the 4 columns described in the Create a New Database Table section.

Set Up a MySQL Database on GCP

Show me how

Once you have set up a GCP account and project, create a new database instance using the Cloud SQL Instances page.

  1. Select your project.

  2. Open the SQL dashboard listed under Databases in the Navigation Menu.

  3. Click Create Instance.

  4. Click Choose MySQL.

  5. Enter an Instance ID and a Password. For the tutorial, use velo-mysql-db for the instance name, and velo123 for the password. 

  6. Click Create Instance at the bottom of the page.

    This redirects you to the SQL Overview page for your instance. Once your instance is ready, create a database on the instance.

  7. Click Databases, then click Create Database.

  8. Enter a Database Name, then click Create. For the tutorial, use myDatabase.

You now have a MySQL database instance called velo-mysql-db, with a database called myDatabase.

Set Up a Postgres Database on GCP

Show me how

Once you have a set up your GCP account and project, create a new database instance using the Cloud SQL Instances page.

  1. Select your project.

  2. Click Create Instance.

  3. Click Choose PostgreSQL.

  4. Enter an Instance ID and a Password. For the tutorial use velo-postgres-db for the instance ID, and velo123 for the password.

  5. Click Create Instance.

    This redirects you to the SQL Overview page for your instance. Once your instance is ready, create a database on the instance.

  6. Click Databases, then click Create Database.

  7. Enter a Database Name, then click Create. For this tutorial we'll call the database myPostgresDB.

You now have a Postgres database instance called velo-postgres-db with a database called myPostgresDB.

Set Up a MsSQL Server Database on GCP

Show me how

Once you have a GCP account and project set up, create a new database instance with SQL.

  1. Select your project.

  2. Click Create Instance.

  3. Click Choose SQL Server

  4. Enter an Instance ID and a Password. For this tutorial we'll use velo-sqlserver-db as the instance ID, and velo123 as the password.

  5. Click Create Instance.

  6. From the SQL Instance Overview page, click Databases and then click Create Database.

  7. Enter a Database Name and click Create. For this tutorial we'll call the database myDatabase.

You now have an SQL Server Database instance called velo-sqlserver-db with a database called myDatabase.

Set Up a BigQuery Database on GCP

Show me how

Once you have a GCP account and project set up, create a new dataset with BigQuery.

  1. Select your project.

  2. Open the BigQuery page in the cloud console.

  3. In the Explorer panel, select the project where you want to create a dataset.

  4. Click View actions and select Create dataset.

  5. On the Create dataset page:

    1. For Dataset ID, enter a unique dataset name. For this tutorial, we set the Dataset ID to bigQuery_dataset.

    2. For Data location, choose a geographic location for the dataset. Note that after a dataset is created, the location can't be changed.

  6. Click Create Dataset.

Set Up a Spanner Database on GCP

Show me how

Once you have a GCP account and project set up, create a new database instance with Spanner.

  1. Select your project.

  2. Open the Spanner page in the cloud console. 

  3. Click Create a Provisioned Instance.

    Note: If this isn't your first time working with spanner databases, click Create Instance.

  4. Enter an Instance Name and an Instance ID. 

  5. Under Choose a Configuration, select Regional then select your configuration. 

  6. Click Create.

Create a New Database Table

This section walks you through creating a new table in MySQL and Postgres. If you know what you're doing, or you already have tables and don't want write access, you can skip this section.

Prerequisites for Read-Write Access to Your Database Table: 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.

Create a New MySQL Table

Show me how

  1. From the SQL Instance Overview page, click Open Cloud Shell.

    If you are prompted to Authorize Cloud Shell, click Authorize.

  2. A command line console opens with the connect command at the prompt. Press Enter.

    If Allowlisting your IP for incoming connection for 5 minutes is displayed in the console, wait a few moments, then enter your database instance's password when prompted. The instance password for this tutorial is velo123.

  3. At the mysql> prompt, enter the code below to create a table.

    If you are using a previously existing database, use that database's name instead of "myDatabase" in the CREATE TABLE statement.

    Copy Code
    CREATE TABLE myDatabase.Contacts(
      _id varchar(50) NOT NULL,
      _createdDate timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      _updatedDate timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      _owner varchar(50) DEFAULT NULL,
      name varchar(30),
      email varchar(30),
      phone varchar(15),
      PRIMARY KEY (_id)
    );

  4. Check that the command worked by entering the following at the mysql> prompt:

    Copy Code
    DESCRIBE myDatabase.Contacts;

    If you are using a previously existing database, use that database's name instead of "myDatabase".

The following output means that you have successfully created the Contacts table:

Create a New Postgres Table

Show me how

  1. From the SQL Instance Overview page, click Open Cloud Shell.

    If you are prompted to Authorize Cloud Shell, click Authorize.

  2. A command line console opens with the connect command at the prompt. Press Enter.

    If Allowlisting your IP for incoming connection for 5 minutes is displayed, wait a few moments, then enter your database instance's password when prompted.

  3. At the postgres=> prompt, enter the following code to change to the myPostgresDB database:

    Copy Code
    \\c myPostgresDB;

  4. Enter the password when prompted.

  5. At the myPostgresDB=> prompt, enter the code below to create a table:

    Copy Code
    CREATE TABLE Contacts(
      _id varchar(50) NOT NULL,
      _createdDate timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      _updatedDate timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      _owner varchar(50) DEFAULT NULL,
      name varchar(30),
      email varchar(30),
      phone varchar(15),
      PRIMARY KEY (_id)
    );

  6. Check that the table was successfully created with the following command:

    Copy Code
    SELECT * FROM Contacts;

The following output means that you have successfully created the Contacts table.

Create a New BigQuery Table

Show me how

  1. In the Explorer panel, click View actions and select Create table.

  2. Under Create table from, select Empty table.

  3. Under Table, enter a name for your table. For this tutorial, we set the Table name to Contacts.

  4. Make sure your table is located in the correct dataset and project.

  5. Under Schema, click Add Field.

  6. Add the following fields to your table:

    Note: The Max length field is optional.

  7. Click Create Table.

  8. Check that the table was created. In the Explorer panel, expand your dataset. There should now be a table called Contacts.

You can also create a new BigQuery table using Google's Standard SQL:

  1. In the BigQuery workspace, click Compose New Query.

  2. In the Editor, enter the code below to create the table.

    If you are using a previously existing database, use that database's name instead of 'bigQuery_dataset'.

    Copy Code
    CREATE TABLE bigQuery_dataset.Contacts(
      _id STRING(50) NOT NULL,
      _createdDate timestamp,
      _updatedDate timestamp,
      _owner STRING(50),
      name STRING(30),
      email STRING(30),
      phone STRING(15),
    );

  3. Click Run.

  4. Check that the table was created. In the Explorer panel, expand your dataset. There should now be a table called Contacts.

Create a New Spanner Table

Show me how

  1. Click Create Database in the Spanner Instance Overview page.

  2. Enter a name for your database.

  3. Choose Google Standard SQL as the databse dialect.

  4. Define your schema using SQL. Enter the code below:

    Copy Code
    CREATE TABLE Contacts(
      x_id STRING(50) NOT NULL,
      x_createdDate TIMESTAMP,
      x_updatedDate TIMESTAMP,
      x_owner STRING(50),
      name STRING(30),
      email STRING(30),
      phone STRING(15),
    )  
    PRIMARY KEY (x_id);

  5. Click Create.

Set Up Secrets

The external database adaptor requires you to set some environment variables. Some of these variables, like the DB credentials, are sensitive and should not be visible. We'll use the GCP Secret Manager to store and access these variables securely.

Configure Secrets

We need to store the following secret values for all databases:

  • SECRET_KEY

A secret key that is 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. 

  • PERMISSIONS (optional)

A JSON object that defines the read and write permissions for the tables in your database.

Learn more about configuring database permissions

By default, only site Admins can read and write to external databases on Wix sites. 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 Code
{
  "collectionPermissions": [
    {
      "id": "Contacts",
      "read": ["Admin", "Member"],
      "write": ["Admin"]
    }
  ]
}

Note that you can save this object as a .json file and upload it to GCP as the value for the PERMISSIONS secret.

Customizing permissions for external databases is currently a developer preview feature, and may change. Changes to permissions settings are not reflected in the editor.

Database-Specific Secrets Configuration

In addition to the secrets above, each database requires its own specific secret configurations: 

MySQL, Postgres, and MsSQL secret names and values
  • USER:  The username used to connect to your database instance. For Postgres instances, the default user name is postgres. For MySQL instances, the default user name is root.
  • PASSWORD:  The password used to connect to your SQL instance. When we created our database in the previous steps we used the password velo123.
  • DB:  The database name to connect to. When we created our database in the previous steps, we called the database myDatabase for mySQL and myPostgresDB for Postgres.
  • Cloud_SQL_CONNECTION_NAME:  The connection name for your DB instance. It can be found on your instance's overview page as shown in the image below.
BigQuery secret names and values
  • DATABASE_ID:  The BigQuery dataset ID. When we created our database in the previous steps, we set the dataset ID to bigQuery_dataset. 
  • PROJECT_ID:  Your Google Cloud Project ID.  You can find this on the Explorer panel.
Spanner secret names and values
  • INSTANCE_ID:  The Spanner instance ID. In the previous steps, we called our spanner instance ID velo-spanner-db.
  • PROJECT_ID:  Your Google Cloud Project ID.  You can find this on the Explorer panel.

Make sure to create all of the required secrets for your database. All databases must include a SECRET_KEY.

How to Create Secrets

  1. To create the secrets, go to the GCP Secret Manger page.

  2. Click Create Secret.

  3. Enter the secret Name and Secret Value and click Create Secret.

  4. Repeat steps 2 and 3 for the rest of the secrets needed for your database.

    DatabaseSecret Configurations
    MySQL, Postgres, and MsSQLSECRET_KEY
    USER
    PASSWORD
    DB
    CLOUD_SQL_CONNECTION_NAME
    BigQuerySECRET_KEY
    DATABASE_ID
    PROJECT_ID
    SpannerSECRET_KEY
    INSTANCE_ID
    DATABASE_ID
    PROJECT_ID

  5. To create a PERMISSIONS secret, instead of entering a Secret value, click Browse and select the .json file that contains your permission settings.

Deploy the Adaptor to Cloud Run

Next, we'll deploy the external database adaptor as a service on Cloud Run. We create the service using a prebuilt container, configured secrets, and environment variables. Then we configure the database connections.

Create a Service

  1. Go to the Cloud Run Instances page and select your project.

  2. Click Create Service.

  3. Enter gcr.io/wix-velo-api/velo-external-db in the Container Image URL field. The Service name will default to velo-external-db.

  4. Select All under Autoscaling, and select Allow unauthenticated invocations under Authentication.

  5. Click Create.

You now have a service called velo-external-db up and running.

Configure Access Roles, Secrets, and Environment Variables

Now we configure the secrets that we defined earlier as environment variables for the service. To do this we'll perform the following steps:

  1. Create a new service account that will run the Cloud Run service.

  2. Grant access roles to the new account.

  3. Create environment variables and assign the secrets' values.

  4. On the Service details page, click Edit & Deploy New Revision.

  5. Select the Security tab and then select Create New Service Account from the Service account dropdown.

  6. Set the Service account name. It is recommended to set the Service account name to match the Cloud Run instance name so that you can easily know which account is used by which instance. 

  7. Click Create.

  8. Grant the following roles to the service account according to your database:

    All Databases:

    • Secret Manager Secret Accessor

    MySQL, Postgres, MsSQL:

    • Cloud SQL Editor

    BigQuery:

    • BigQueryAdmin

    Spanner:

    • Cloud Spanner Database User

    You can paste the roles above into the dropdown filter.

  9. Click Done.

  10. Select the Container tab and scroll down to the Environment Variables section.

    Click Add Variable.

  11. Define 2 environment variables according to your database.

    The names and values for the variables are as follows:

    NAMEVALUE
    TYPEMySQL database: **mysql
    **Postgres database: postgres
    MsSQL server database: mssql
    BigQuery database: bigquery
    Spanner database: spanner
    CLOUD_VENDORGoogle Cloud Platform: gcp

  12. In the Container tab, click Reference a Secret, then define environment variables and assign their values from the secrets that we defined earlier in the Create Secrets section.

  13. Select a secret from the Secrets dropdown and choose Exposed as environment variable from the Reference method dropdown. 

  14. Assign the environment variable the same name as the secret.

  15. Select latest from the Version dropdown, and click Done.

    Repeat this process for all of the secrets that you defined in the Create Secrets section.

If you get a warning that the service account does not have permissions to access the selected secret, you are no longer configuring the service account that you set up in Step 7 above.

In this case, click the Security tab, select your service account from the dropdown, then return to the Container tab.

Once you have configured all of the secrets and environment variables, your configuration will look like this:

Configure the Database Connection

If you're using a BigQuery database, start with step 3 below.

  1. In the same Container tab, click Add Connection under Cloud SQL Connection.

  2. In the Cloud SQL Instance1 dropdown, select the SQL instance that you created.

  3. Click Deploy.

    Once deployed, check that your setup is working.

  4. From the Cloud Run Service details page, click the service's URL. This URL is used to connect to your database adaptor service and to configure the external collection on your Wix site.

    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.

Note: If you change the value of a secret, you must redeploy the service for the new value to take effect.

Test Your Service

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 Step 2 above, and replacing the  secretKey value with your secret.

Copy Code
curl -L -X POST 'https://velo-postgres-db-cacacapca-uc.a.run.app/schemas/list' \
-H 'Content-Type: application/json' \
--data-raw '{
  "requestContext": {
    "settings": {
      "secretKey": "ourLittleSecret"
    },
    "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 contains the list of tables and their columns from your database.

Copy Code
{
    "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"
                    ]
                }
            }
        }
    ]
}

Connect to Your Wix Site

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

Note: You can only add external collections to your site if you have a premium plan.

  1. Go to the Databases section of the Velo sidebar (Wix Editor) or the Code sidebar (Wix Studio).

  2. Click the icon next to External Databases and select Add external database.

  3. Choose Google Cloud as the cloud 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 collection displays the tables. If your table contains the _id, _createdDate, _updatedDate, and _owner fields, you can add data to the table directly from the CMS.

Important: The CMS does not currently support deleting from external collections. You can delete records from your collection using wix-data.remove() or an HTTP REST call.

The external connection and its collections are displayed under External Databases.

You can now use any of the wix-data and wix-dataset APIs with this collection as well as connect repeaters and tables to your MySQL, Postgres, MsSQL, BigQuery, and Spanner databases.

Try the following Velo code to query your external data:

Copy Code
import wixData from 'wix-data';


export function externalQuery() {
    return wixData.query("mysql_collections/Contacts")
        .find()
        .then((results) => {
            return results;
        })
        .catch((err) => {
            let errorMsg = err;
            return errorMsg;
        });
}
Was this helpful?
Yes
No