The Wix Code Database and Data Modeling
This article was created in partnership with Wix. Thank you for supporting the partners who make SitePoint possible.
One of the cool features of Wix Code is the ability to separate your site’s design and layout from its content. This means you can create and maintain your information in a database and then have your pages dynamically retrieve and display this information in whatever way you like.
Let’s take an in-depth look at what you can do with the Wix Code database, including the types of information you can store, ways you can manipulate data with code, and how you can dynamically display the information on your site.
Throughout this article, we’ll use a simplified example of an art school that stores and displays information about its courses and teachers.
The Wix Code Database
Like all databases, the Wix Code database is made up of individual tables, which we call collections. In our example of the art school (see image below), we have two collections, one each for the courses and teachers.
You can create as many collections as you need and populate them with a near unending amount of data. A robust permissions model means you have complete control over who can access your information and what they can do with it.
You can work directly in your Live data, which is the information your visitors see when they view your pages. You can also work with Sandbox data, so you can try stuff out without affecting your live site. You can sync between them at any time.
You have several options for populating your collections. You can manually enter data directly in the Wix Content Manager, either to your Live data or your Sandbox data.
If you’re an Excel ace, you can do all the work in Excel (or whatever spreadsheet program you prefer), save your sheet as a CSV file, and then import it into the Wix Code database. In fact, you can create your entire collection this way, schema and all. You can import to your Live data or your Sandbox data.
You can also export your Wix data to CSV files. If you make sure to include the built-in ID system field, you will be able to modify your content in your spreadsheet and then re-import it into your Wix Code database so that each record, or what we call item, is updated.
A third option is to build a form to capture user input and store it in your database.
Using External Databases
If you already have a database somewhere, you might be thinking that you don’t want to recreate it in Wix. The good news is that you don’t have to. As long as your database exposes an API, you can access it from your Wix site.
You can also pair the wix-fetch module with another Wix module, wix-router, that lets you control the routing of incoming requests. Using the functionality provided by both of these modules, you can create SEO-friendly dynamic pages that show different data depending on the URLs used to reach them.
For example, you can design a single member profile page that can be used by all of your site’s members.
Using wix-router and wix-fetch you can write code that pulls information from incoming requests for the profile page, queries an external database to retrieve the information for the page, and then injects that data into the profile page. You can even add security to your page by using the wix-users module.
So if you create another page for users to update their profile pages, you can check who is trying to access it and only allow users to update their own profiles.
You can add hooks to actions on your collections using the wix-data API.
For example, in our Teachers collection, we have two separate fields: First name and Last name. To make displaying names on our pages easier, we also want to have one field that has both names together. To do this, we can add a beforeInsert hook to our Teachers collection that hooks into the insert action, reads the contents of the First name and Last name fields, and then concatenates them and populates the Full name field.
Modeling Your Data
Now that we’ve covered the database itself, let’s talk about modeling your data in the Wix Code database.
Like all databases, each collection has a schema to define its fields. All standard field types are supported, including text, image, boolean, number, date and time, and rich text.
There is also a field type specifically designed for URLs. It automatically formats the URL into clickable links that you can add to your pages. For example, teachers in your school could supply the URL of their portfolio website, and you could include that link on their dynamic page.
You can also use the document field type to store a wide range of file types. You can allow your users to download files stored in your collections (such as reading lists for each course) or to upload their own files.
ID Fields and Primary Fields
Each collection has an _ID field, which is the primary key for that table. Collections also have a primary field (indicated by a lock icon), which is the display key for each item.
When you create joins using reference fields (see the next section), the values come from the primary field. The reference itself uses the _ID field, of course. If you plan on using reference fields, it’s a good idea to make sure the data you store in the primary field is unique.
Reference fields create a connection between collections that is defined in the collection schema itself. This is similar to foreign keys in relational databases.
Each reference field points to a specific collection. The value that is displayed in the reference field in each item in the collection is taken from the value of the primary field of the referenced collection.
In our example, we created a reference field in our Courses collection that points to our Teachers collection so that we can indicate who teaches each class.
The advantage of reference fields is three-fold. First, they help maintain data integrity because their value is taken directly from the referenced collection. Second, they help eliminate data duplication, which we all know is the enemy of good database design. And third, when we create our page layouts, reference fields let us access information in the referenced collection as well as in the main collection we are using. This allows us to create master-detail pages, such as a list of all the courses taught by each teacher.
Creating Pages from Your Content
Of course, storing and maintaining data is nice, but the real point of having a website is displaying content to visitors. So let’s talk about how that works with Wix Code.
Back to our art school example. We have two different types of information: courses and teachers. So you could start by designing a page layout to display all the information about each of the courses. Then you might want to create a master-detail page that lists all of your teachers and the courses they teach.
Setting Up the Dynamic Page
When you create dynamic pages in Wix Code, you first define the URL that will control what content your page can display. Some URLs can specify a single item and others can specify an entire category of items (such as all courses of a certain level).
You set up the URL pattern by picking a field (or fields) from your collection. One URL pattern you could use to display each of your courses could be https://…/Courses/
Then you design your page layout in the Editor, putting different elements on the page and connecting the ones you want to use to display dynamic data to fields in your collection. You can use text elements, images, buttons, strips, and a variety of multi-item elements like repeaters, tables, and galleries. If you want some items to remain static, such as titles, just don’t connect them.
The image below is an example of what a dynamic page layout for our Courses page could look like in the Editor. The square brackets indicate that this content is dynamic.
The actual dynamic pages could look something like these.
Note how both pages have the same layout. However, some of the elements’ contents have been replaced with the information about the courses from our database. The page background is also different for each page. The container box even enlarged automatically to include the larger course description for the Art History course.
Note especially how the name and picture of each course’s teacher appear on the page, even though the details about each instructor are stored in a separate collection from the data about the course. This is because we connected the Courses and the Teachers collections using a reference field, which gave us access to information about the specific teacher for each course.
Finally, note how the page URLs are unique to each page. In essence, each of these pages is unique. And Wix Code creates them automatically for us. If we add a new course to our collection, a page for it is automatically created.
Another cool thing you can do with Wix Code is to create master-detail pages. For example, you could create a page to act as an index that lists all the teachers in your school and the courses each one teaches. This would require pulling information from more than one collection (Courses and Teachers) and then filtering the courses by their teacher so that only the relevant courses are displayed.
Our database collections are set up in a many-to-one structure; each teacher has many courses they teach. Whereas above we displayed each course and their individual teacher, now we are taking the opposite approach and displaying each teacher and all their courses.
Below is a sample of what an index page with master-detail information might look like using a repeater.
Because the repeater is connected to both our Teachers collection and our Courses collection, it can display the information from both collections dynamically. The embedded table element in each repeater item displays the list of courses each teacher teaches.
We’ve presented some high-level information about the Wix Code database and some of the capabilities it offers for storing your data, manipulating your data, and displaying your data dynamically to your visitors. We’ve also illustrated how the options available to you are controlled in part by the decisions you make when you create your collections and connect them. Before you get started creating your Wix Code database, it’s a good idea to spend some time thinking about what kind of information you have and how you want to display it so that you can most effectively model your data.