logo

Wix External Collections With Reshuffle

Connect systems with Reshuffle.
Cover Image for Wix External Collections With Reshuffle

Introduction

Wix is a great service that lets you develop your website quickly and securely. It provides a large arsenal of widgets and capabilities to make life easy and shorten development cycles.

The system is so good that businesses all over the world have developed their entire IT infrastructure on top of it - including content management systems, CRMs, and even online shopping and fulfillment.

Most of these capabilities store their data in a database, and while Wix offers data storage as an integrated part of its platform, this may not be the perfect solution in some cases.

Imagine, for example, that you have an existing database you’d like to use. Or perhaps your data has to be stored at a specific location, for legal reasons, regulatory, security, or privacy. How would you achieve that?

To address this need, Wix came up with the concept of external database collections.

As the name suggests, these are data stores external to Wix. You can configure your Wix website to use such an external data source to store and retrieve data.

In this article, we will use Wix and Reshuffle to connect an external database to your Wix website.

The code demonstrated in this article can be found on our Github Repository.

Under the hood

Wix Side

To connect your Wix website to an external collection, you’ll need some understanding of Wix’s SPI - which is the specification describing how Wix connects to external collections. The gist of it is:

  1. You provide Wix with connection details (an HTTP endpoint to hit)
  2. Wix connects to your external collection using the connection details
  3. Wix uses a set of predefined HTTP calls to exchange data with the external collection

Reshuffle Side

To connect Reshuffle to Wix, you’ll need to use Reshuffle’s Wix connector. The connector exposes the HTTP endpoint Wix requires to connect to the external collection (your data source).

The connector’s job is to convert the requests coming from Wix into events on the Reshuffle system. All that’s left is to develop the handlers (javascript code) to handle these events.

Hike List: Our Sample Application

The following is a Hike List example, which demonstrates the integration of Wix, Reshuffle, and a Postgres database.

The Hike List application is used to manage a list of hikes we want to complete in New Zealand. For each hike, the application will store the following details:

  1. Name
  2. Distance
  3. Date completed

Wix provides a web-based user interface to manage the data. Postgres provides a data store. Reshuffle provides the means to connect the two systems.

Step 1: Create the hike table:

The following SQL code can be used to create the table for this example:

CREATE TABLE IF NOT EXISTS hike (
  _id character varying(36) COLLATE pg_catalog."default",
  _owner character varying(36) COLLATE pg_catalog."default",
  name character varying(200) COLLATE pg_catalog."default",
  distance numeric(6,2) COLLATE pg_catalog."default",
  completed_date timestamp without time zone
);

By the end of this article, we will want Wix to create items in this database. Therefore, we will define the id and owner fields as 36 characters each. This matches the schema for the auto-generated Wix item ids and owners.

Step 2: Configure Reshuffle and the Reshuffle Wix Connector

const { Reshuffle } = require('reshuffle')
// Add the required connectors #1
const {
  WixConnector,
  parseFilter,
  wrapDates,
  unwrapDates,
} = require('reshuffle-wix-connector')
const { PgsqlConnector } = require('reshuffle-pgsql-connector')

const app = new Reshuffle()
// Configure a secret 'API key' for Wix #2
const wix = new WixConnector(app, {
  secret: 'tunnel',
})
// Connect Reshuffle to Postgres #3
const pg = new PgsqlConnector(app, {
  url: process.env.WIX_DB_URL,
})

// -------------------------------------
// Code listed further down the
// article is inserted here ---
// --------------------------------------

app.start()

The previous code is only a few lines long, but it sets up the plumbing we need to create a link between Wix and the database.

Comments in code:

  1. Reshuffle is a framework that uses connectors in order to standardize the programming paradigm across diversified systems. In this code, we use connectors for Wix and PostgreSQL. Both are available as Reshuffle npm packages. You’ll notice we also get some functions from the Wix connector. They will be used later on.

  2. When configuring Wix to use an external collection, you can provide a ‘secret key’ to pass along to your application. This is a means of securing the tunnel (alongside other attributes listed here). Reshuffle’s Wix connector expects each request to provide a secret inside the settings object it contains (See Step 3 below for details of Wix-side configuration). The Wix connector also accepts an optional parameter to define the path it listens to (and exposes as the endpoint to Wix itself). The default path is /webhooks/wix.

  3. Reshuffle’s Postgres connector requires only a database URL. It abstracts a connection to the database provided. If the database runs on your localhost, the url will resemble this:

postgresql://my_username:my_password@localhost/my_database

Step 3: Configuring the External Collection on Wix

Head over to Wix to create a new external collection. You’ll need to navigate into your site editor and click on the databases icon from the left-sidebar and then click on the small + icon next to ‘content collections’ to add an external collection.

Add external collection on Wix

You’ll need to enter the following details into the configuration pop-up:

  1. A name for your collection
  2. The endpoint url - this is your Reshuffle’s runtime URL, with the Wix’s webhook path appended to it. In our example above, we left the webhook path to the default, and it will hence be /webhooks/wix.
  3. The secret ‘API key’ to use in order to authenticate to the Wix connector. This needs to be the same key and value pair configured when the connector is instantiated.

Configure external collection on Wix

If you click Add right now, Wix will complain:

Wix error message: No connection

This is because the code we currently have does not comply with the specification. Remember - it only sets the plumbing to create the link between Wix and your database. It doesn’t really do anything. Yet.

Step 4: Implementing Wix SPI Requirements

Wix SPI is the specification detailing how Wix communicates with external collections. It is a database-agnostic specification, which the provider of an external collection needs to implement.

So far we saw how Reshuffle uses connectors to connect to various systems. Connectors in Reshuffle provide events and actions to let the developer program the flows and integrations they need.

Connectors trigger events when something of interest occurs in the external system they connect to. They also expose actions to let the developer make requests to these systems.

Viewing Reshuffle as an event-based system allows us, as developers, to focus on the business logic we need to address specific events.

The Wix connector listens to HTTP Post requests arriving from Wix and exposes all of them as events. The list of requests Wix expects an external collection endpoint to serve can be found in the documentation.

Let’s begin by writing scripts to respond to the six mandatory requests:

// - Code to be inserted just before app.start()

const COLLECTION = 'hike'

// #1
const HIKE_SCHEMA = '..'

// #2
wix.on({ action: 'provision' }, async (event, app) => {
   event.response.sendStatus(200)
 }
)

// #3
wix.on({ action: 'schemas/list'}, async (event, app) => {
 event.response.status(200).json(HIKE_SCHEMA)
})

// #4
wix.on({ action: 'schemas/find' }, async (event, app) => {
 if (event.request.body.schemaIds.includes(COLLECTION)) {
   event.response.status(200).json(HIKE_SCHEMA)
 }
})

// #5
wix.on({ action: 'data/count' }, async (event, app) => {
 const { collectionName, filter } = event.request.body
 if (collectionName === COLLECTION) {
   const _filter = parseFilter(filter)
   const hikes = await pg.query(`SELECT * from ${COLLECTION} ${_filter}`)
   event.response.status(200).json({ totalCount: hikes.rowCount })
 }
})

// #6
wix.on({ action: 'data/find' }, async (event, app) => {
 const { collectionName, sort, filter, limit, skip } = event
 const _filter = parseFilter(filter)
 if (collectionName === COLLECTION) {
   const _limit = limit ? 'LIMIT ' + limit : ''
   const _skip = skip ? 'OFFSET  ' + skip : ''
   const _sort = sort ? `ORDER BY ${sort[0].fieldName} ${sort[0].direction}` : ''
   const hikes = await pg.query(`SELECT * from ${COLLECTION} ${_filter} ${sort} ${_limit} ${_skip}`)

   // #7
   const items = hikes.rows.map(hike =>
    wrapDates(convertItem(hike))
   )
   event.response.status(200)
     .json({items, totalCount: hikes.rowCount })
   }
})

// #8
wix.on({ action: 'data/get' }, async (event, app) => {
 const { collectionName, itemId } = event.request.body
 if (collectionName === COLLECTION) {
   const res = await pg.query(`SELECT * from ${COLLECTION} where id = '${itemId}';`)
   if (res.rowCount == 1)
     event.response.status(200).json({ item: wrapDates(res.rows[0])
  }
})

// A helper method to convert item from PG connector's data to the
// schema Wix expects
const convertItem = (i) => i.distance ? Number(i.distance) : i
// ... This is where we get back to the code in the first listing -
app.start()

Comments in code:

  1. Wix expects a schema description here. It is too long to insert here, but you can head over to the GitHub repository of this article to view the schema for the table created in Step 1.

  2. This is the handler we use for the provision call. With other implementations, you may want to ensure that you can reach the database and that the relevant table exists. But for brevity’s sake - we just return a 200 Http Status here, signaling to Wix that the database is provisioned and ready.

You’ll note that the event object received by the handler (which is the piece of code a developer writes to handle an event) contains the HTTP response object - which is required because you’ll want to use it to return to Wix.

  1. This is the call Wix makes to figure out what schemas the external collection exposes. As we only have one table, return the schema itself.

  2. The find schemas call is only interested in specific schemas. It lists these schemas on the HTTP request body (which, like the response object, is found on the event itself). When constructing the response - the code first tests whether the Hike schema was requested, and will only return it when asked.

  3. The count call is used to, well, return the count of items in the external collection. If a filter is provided - the result will reflect that and only count items matching the filter.

Note: The filter provided by Wix is database agnostic. When using Postgres, the filter needs to be translated into a SELECT statement. This is achieved by the parseFilter function provided by the Wix Connector.

  1. The find items call is used by Wix to retrieve a number of items from the external collection. The request may apply a filter and may use limit and skip to request a specific page of results. Note that Wix expects dates to be wrapped in a specific way - we can use the wrapDates function from the Wix Connector to achieve that.

  2. Because the data returned from the PG connector is pure json, we will need to convert some fields. In this case - we only convert the numeric field ‘distance’ to a Number, as per the schema we provide Wix.

  3. The last mandatory call is the get item call. Wix uses this to retrieve an item by id from a specific collection. Note that, again, we use wrapDates to ensure Wix receives date objects in the format it likes.

General note: Error handling code was stripped away to keep the listing brief. The full code can be found on Github.

Halfway Recap

If you only want Wix to display your data, then that is really all you need.

To test this out, you’ll need your Reshuffle instance running with the code above, and you need Wix to be able to POST to it through an HTTPS endpoint (See here or here for how to expose your localhost over SSL if you need to).

Once you provide Wix with the public endpoint (as described in step 3 above), Wix will make a series of calls and create a rudimentary (but powerful!) Data viewing grid. If you insert some data into your hike table and refresh the Wix page - you’ll see your data inside this grid:

Wix displaying our hikes data in a grid

Notice the ‘read-only’ tag near the collection name at the top of the screenshot above. This is due to the allowedOperations entry in our schema:

const HIKE_SCHEMA = {
 "schemas": [
 {
   "displayName": "hike",
   "id": "hike",
   "allowedOperations": [
     "get",
     "find",
     "count"
   ],

Step 5: Closing the loop

So far we only saw how to let Wix read your external collection. We will now close the loop and develop the code that allows Wix to create, update, and remove items as well. We will need to implement three more actions, to insert, update, and delete items.

To start with, you’ll need to add these operations to the HIKE_SCHEMA above:

const HIKE_SCHEMA = {
 "schemas": [
 {
   "displayName": "hike",
   "id": "hike",
   "allowedOperations": [
     "get",
     "find",
     "count",
     "update",
     "insert",
     "remove"
   ],

This will tell Wix that your external collection allows for more operations.

The code that deals with inserting, updating and deleting an item from the database can be found in the Github repository and follows the same logic as the code we have already seen.

It is worth explaining the logic behind converting between the json Wix produces and expects - and the SQL used.

const hikeFields = () =>
  Object.keys(
    HIKE_SCHEMA.schemas.find((s) => s.displayName === COLLECTION).fields
  ) // #1

// #2
const createUpdateSQL = (item) => {
  const fieldsInSchema = hikeFields()
  delete fieldsInSchema._id
  return Object.keys(item)
    .filter((i) => fieldsInSchema.includes(i))
    .map((k) => `${k} = ${getValue(item, k)}`)
    .join(', ')
}

// #3
const getValue = (item, k) => {
  switch (
    HIKE_SCHEMA.schemas.find((s) => s.displayName === COLLECTION).fields[k].type
  ) {
    case 'datetime':
      return item[k] == null ? null : `'${item[k]}'`
    case 'text':
      return `'${item[k]}'`
    case 'number':
      return Number(item[k])
  }
}

Comments in code:

  1. Wix sends extra fields when it creates or updates an item. Namely, these are the _createdDate and _updatedDate fields. These are not listed in the documentation but are found as Hidden Fields in the User Interface automatically generated by Wix:

Wix hidden fields display

Extracting the names of the fields from the schema ensures that we only deal with the fields we expose in the database. This lets us write concise code (as can be seen in #2 below)

  1. When updating the table, we are interested in all fields that are defined in the schema, sans the _id field (as we would not like to update the _id field itself under any case). This code creates a valid SQL Update command, by filtering the fields sent from Wix to only use the fields that our database actually provides. By limiting the fields to those exposed by the schema - the code can use filter() and map() rather than address each field individually.

  2. In SQL, numeric values should appear as-is, text values should have quotes surrounding them, and date values should be either null or quoted. The getValue function deals with that.

Conclusion

The article and the accompanying Github repository provide a good starting point for using Reshuffle to connect a data store of any kind to Wix. Using Reshuffle’s Wix connector and the events it exposes, allows you to connect Wix to any external data source you control. This can be a database or Redis, but even a file system or a different service altogether.