How To Work with External Database

When you enable Velo you also automatically get Wix Data, which lets you work with our built-in databases on your site. You may also want to work with data that you maintain in an external database. Velo lets you connect your site to an external database and then work with that database collection in your site just as you would with our built-in collections.

That means that you can display data from an external collection in Editor elements, use the data to create dynamic pages and connect it to user input elements.

You can learn about adding an external database to your site here. To work with external database collections you need to create an external
database adapter. This adapter implements a Service Provider Interface
(SPI) that enables your site to communicate with your external database
collection. This is the basic structure of the system:

Let's look at each step in more detail:

1. Site data request. A data request is generated by your Wix site and sent to your adapter. A data request can be the result of any visitor activity that relates to your collections or code you add that calls the wix-data API. This request is in the form of a REST API call. The request payload includes specific information about the request.
2. Adapter translation of data request. The adapter receives the request. It converts that request into an API request that your external database can receive, based on the external database's API.
3. Adapter data request. The data request is sent to your external database using your external database's API.
4. External database data response. Your external database responds to the request, based on its API. 
5. Adapter translation of data response. The adapter receives the response. It converts this response into a response to the REST API request it received from the site.
6. Adapter data response. The adapter sends the response to your site, including response payload information, and your site automatically handles the response and its payload.

Adapter Implementations

Wix has created the following prototype adapters on GitHub that you can experiment with and adapt to your needs:

• Adapter for MySQL databases deployed to Google AppEngine
• Adapter for Google Cloud Firestore with Cloud Run or AppEngine

Follow the steps in the README file to deploy and test the prototype adaptor.

Building the Adapter

The adapter takes the request from your Wix site and converts it into
something your external database can receive. It also takes the response from the external database and converts it into something your Wix site can receive.

This is where you come in. You need to build the adapter that implements the SPI and translates requests and responses between the two systems.

To create the code in your adapter you'll need to understand the structure of the API requests that your site can send to your adapter, and the structure of their associated payloads. You can find that information here.

You'll also need to understand your external database's API. You'll need to understand the format that it expects as a data request as well as the format it uses in its responses.

Your Wix site and your external database will handle their parts. The critical steps in the process, and the ones you need to manage, are steps 2 and 5.

You can see an example implementation of an adapter here.

Step 2 - Adapter translation of data requests from your Wix site to the external database

Your Wix site sends an API request, but it is not in a format that your external database can work with.

You need to add code in your adapter to:

1. Take the request and its payload from the Wix site and convert it into a request that the external database can receive.
2. Send that requests to the external database in a form it can receive.

Step 5 - Adapter translation of data requests from the external database to your Wix site

Your external database will receive the request from your adapter and send a response back. This response will be in a format native to your
database, but your Wix site can't work with that response.

You need the code in your adapter to:

1. Receive the response from the external database.
2. Convert that response into a response and payload that your Wix site can receive and send it back to your Wix site.

Important Information for Building Your Adapter

The following is information you need to consider when building your adapter and managing your database.

_id

Each table or collection must contain an _id column or attribute of type string.

The Wix Data backend will generate a unique 36-character UUID for each item created in your site.

The `_id` can be any length, but if items are created by your Wix site, it must be at least 36 characters long.

_owner (optional)

The Wix Data backend will send an owner ID when creating a new data item. This is a 36-character UUID identifying the user who created the data.
Use the _owner field to sort or filter data by user.

Dates

All dates must be an ISO string encapsulated in an object. For example: "myDate" :{“$date”:”2001-02-11T00:00:00.000Z”}

Authentication

The External Database Collections SPI uses the request context object to
manage authentication and authorization. HTTP authentication is not
implemented. The settings object within the request context object can be used to send a key for authentication purposes. In the prototype, the secretKey property is used and used to authenticate each request.

The settings object is defined in the Wix Editor when adding the external collection, and sent by the Wix Data backend with every request.

Permissions for external databases

Velo's built in collections include a permissions model, which you can use with your external collections as well. You can also implement your external collection's permissions model, based on how you implement your adapter. Using the context object's members, you can set permissions based on any of the following:

• instanceId - the Wix site making the request.
• installationId - the external collection defined in your site making the request.
• memberId - the id of the site visitor making the request.
• role - whether the visitor is the owner, a member or a guest.

You can also control access to your external collections based on which
endpoints you implement in your adapter. See below for a description of
the required and optional endpoints.

The datasets on your site recognize which endpoints were implemented in
the adapter. For example, if you only implement endpoints that read data, the dataset mode will be read-only.

You can view a read-only version of your collection's permissions in the Site Structure sidebar.

Required and optional endpoints

The following endpoints are mandatory and must be implemented in your adapter. All other endpoints are optional.

• Find Schemas

Discover the database tables or collections that are visible to your database user.

• List Schemas

List the details of a given list of database tables or collections.

• Get Item

Get a specific database row or item based on the item's _id field.

• Find Items

Get a list of items based on a filter.

• Count

Get the number of rows or items in a table or collection.

• Provision

Establish the connection between your site and your adapter.

Working with reference fields

You cannot work with multiple item reference fields in external database collections. You can only use single references in external databases, with the following implementation rules:

• The reference must point to an _id field.
• The reference field must be of type string.
• To allow references from other collections to the external collection the _id field needs to accept the hasSome filter.

Working with dynamic pages

To use Dynamic Pages with external collections at least one of the fields
needs to accept the urlized filter. That field will be used for generating dynamic page links.

Previously published at https://support.wix.com/en/article/velo-working-with-external-database-collections