mbiermann/corvid-external-db-mysql-adapter

Corvid by Wix Example: External Database Collection Adapter

Corvid by Wix DB Connectors: External Database Adapter for MySQL

This project is a Node.js based adapter that lets you integrate an external MySQL database with your Corvid enabled Wix site.

You can use this project as a basis for deploying your own adapter to the Google Cloud AppEngine. We also provide a ready to deploy public Docker Image which can be hosted on Google Cloud Run, Amazon Fargate or Microsoft Azure App Services. This project contains a basic implementation of the external database collection SPI that has filtering, authorization, and error handling support.

Learn more about working with external database collections in Corvid here.

See our SPI documentation.

Getting started

This project assumes you have a Google Cloud Project with billing enabled. If you don't have one, follow this free trial guide.

Create a Cloud SQL instance and database

Go to https://cloud.google.com/sql/docs/mysql/quickstart and create a database and tables with your required schema.

Configuration

The configuration values are exposed via environment variables. There are three required configuration values:

• SECRET_KEY: The secret key that you will use when connecting the adapter in the Wix Editor. Each request to your adapter will contain this value as secretKey in the requestContext key inside the payload.
• ALLOWED_OPERATIONS: The list of all the operations that this adapter will be allowed to perform. For example, if you want to create an adapter that allows read-only access, you can limit these operations to ["get", "find", "count"].
• SQL_CONFIG: The configuration that will be used to connect to your SQL instance. This is JSON string that will be passed the mysql.createConnection() function. All available configuration options are documented in the mysqljs/mysql driver repository. SQL_CONFIG variable must be in form of {"socketPath":"/cloudsql/[YOUR_SQL_INSTANCE]", "user": "[YOUR_USER]", "password":"[YOUR_PASSWORD]", "database":"DATABASE_YOU_CREATED"} Cloud SQL instance can be obtained as described here.

Deploying to Cloud Run using Cloud Console

Follow the instructions in the Google Cloud Run Quickstart Guide.

In the Create service form use the following settings:

1. Container Image: Use gcr.io/corvid-api/mysql-connector-node.
2. Deployment Platform: Select Cloud Run (fully managed).
3. Location: Select us-east1 region.
4. Authentication: Select Allow unauthenticated invocations. This enables access to the connector from Corvid.
5. Show Optional Settings: Add an Environment variables as described in the Configuration section.
6. Click Create to deploy the image to Google Cloud Run and wait for the deployment to finish.

Click the displayed URL link to test the deployed connector. In the browser you should see the following, which indicates that the connector is running:

{"message":"Missing request context"}

Copy the service URL. You will need it to connect MySQL to your Corvid enabled Wix site.

Deploying to Google Cloud Run using the gcloud CLI

1. Install the Google Cloud SDK for your operating system.

2. Acquire new user credentials to use for Application Default Credentials:

   gcloud auth application-default login

3. Set your project as the default:

   gcloud config set project PROJECTID

4. Deploy the Corvid connector container:

   gcloud run deploy --image gcr.io/corvid-api/mysql-connector-node --platform managed --region us-east1 --set-env-vars SECRET_KEY=[YOUR SECRET KEY],SQL_CONFIG={YOUR MYSQL CONNECTION STRING},ALLOWED_OPERATIONS=["get", "find", "count", ...]

   a. At the prompt: Allow unauthenticated invocations to [mysql-connector-node] (Y/N)? Choose "Y".

   b. When deployment is successful, the command line output will contain something similar to this:

   Service [mysql-connector-node] revision [mysql-connector-node-00001-nep] has been deployed and is serving 100 percent of traffic at https://mysql-connector-node-[autogenerated].run.app

Copy the service URL. You will need it to connect MySQL to your Corvid enabled Wix site.

Deploying to Google App Engine using gcloud CLI

1. Check out the source code:

 git clone https://github.com/wix/corvid-external-db-mysql-adapter.git
 cd corvid-external-db-mysql-adapter

2. Deploy the local code and/or configuration of your app to App Engine:

gcloud app deploy --project <your project id>

3. After deployment, access your service at https://<project id>.appspot.com/

Instance Size

The default AppEngine instance class is F1. It works well for small tables of several gigabytes. If your application requires a larger capacity and executing complex and large queries, you can adjust the instance size in the app.yaml file located at the root of the project. Please follow the instructions here. Also, check the pricing in the Google AppEngine Pricing page.

Connecting MySQL to your Corvid enabled Wix site

Follow the instructions here.

In the connection Dialog settings use the following:

• Add an endpoint URL: Use the connector service URL from steps above.
• Configuration: Use: {"secretKey":<Your secret key from the deployment step>}

You should now see MySQL tables as collections in the Databases sections of the sidebar in your site.

Developing and extending the adapter

The following steps describe setting up the environment for developing the adapter for Google Cloud SQL.

1. Check out the source code:

 git clone https://github.com/wix/corvid-external-db-mysql-adapter.git
 cd corvid-external-db-mysql-adapter

2. Create a GCP Service account:

gcloud iam service-accounts create corvid --description Corvid Dev account --display-name corvid-dev

3. Run the following command and copy its output, which you will use in step 4:

gcloud iam service-accounts list

4. Generate the Service Account key and save it in gcp-sa-key.json:

gcloud iam service-accounts keys create gcp-sa-key.json --iam-account <Service account name from Step 3>

5. Set an environment variable for GCP client libraries:

export GOOGLE_APPLICATION_CREDENTIALS=$PWD/gcp-sa-key.json

6. Set up local MySQL proxy as described here

7. Use the following command to start the Connector. It is a NodeJS express application:

npm install

npm start

Extensions

Schemas

The schemas are loaded dynamically from the configured database.

Currently, the driver supports these basic MySQL datatypes:

• varchar,
• text,
• decimal,
• bigint,
• int,
• tinyint,
• time,
• date,
• datetime,
• json.

For all other datatypes, it defaults to the Wix Data object datatype.

This support can be extended by implementing additional handlers for required datatypes in the service/support/table-converter module.

Authentication

Currently, the driver has authentication in the form of a secret key (described in Configuration, above).

The secret key gets deployed together with the adapter to Google AppEngine. Every request made to the adapter is then verified against the secret key.

The authentication functionality can be further extended by modifying the utils/auth-middleware module.