Example Site
To see a live example, click here.
This package provides you with the ability to use a subset of the SQL programming language to interact with database collections on your site. The package converts SQL statements to the Wix Data API syntax.
There are 4 main operators supported in this package: SELECT, INSERT INTO, UPDATE, DELETE.
There are 4 additional operators supported in this package: WHERE, LIMIT, ORDER BY, JOIN.
To see an example site that demonstrates how to use this package, click here.
This package does not require any initial setup.
The following backend files are included in the package. Note that only exported functions that you can use in your site are listed here.
The code in this file contains a function for converting SQL statements to the Wix-Data API syntax.
To use the function below in your backend code, import it with the following syntax:
sql()
Receives an SQL statement string and converts it to Wix Data API syntax. Returns a JSON containing a payload that is specific to the type of operation performed.
This section demonstrates how you can work with the package, and the different operators for using the package function. You can also see a working example of this package here.
The first part of this section lists and explains how to use the operators supported in the package. The second part of this section gives an example of how you can implement some of these operations to create a To-Do-List.
There are 4 main operators supported in this package: SELECT, INSERT INTO, UPDATE, DELETE. There are 4 additional operators supported in this package: WHERE, LIMIT, ORDER BY, JOIN.
To demonstrate how to use the operators, we’ll refer to the following 2 database collections. Note that the first row bolded in each database collection are the fields in the collection.
Players
| number | name | team |
|---|---|---|
| 1 | Michael | Bulls |
| 2 | Kobe | Lakers |
Teams
| name | city | championships |
|---|---|---|
| Bulls | Chicago | 6 |
| Lakers | Los Angeles | 17 |
| Clippers | Los Angeles | 0 |
SELECT
Selects data from a specified database collection. Returned payload type is data.
Options:
All Columns
Selects all the columns from the specified database collection.
Syntax: SELECT * FROM <collection>
Example: sql('SELECT * FROM Players');
Example Output:
Specific Columns
Selects the specified columns from the specified database collection.
Syntax: SELECT [<column>] FROM <collection>
Example: sql('SELECT number, name FROM Players')
Example Output:
Distinct
Selects the distinct values in the specified column from the specified database collection.
Syntax: SELECT <column> FROM <collection>
Example: sql('SELECT DISTINCT city FROM Teams')
Example Output:
Functions
Selects the specified column from the specified database collection and executes a specified function on the selection.
Supported Functions:
Syntax:
Example: sql('SELECT MIN(championships), MAX(championships) FROM Teams')
Example Output:
INSERT INTO
Inserts a new record (row) into a specified database collection. Returned payload type is inserted.
Syntax:
Example: sql('INSERT INTO Players (number, name, team) VALUES (3, 'Bird', 'Celtics')')
Example Output: true
Players collection after insertion:
| number | name | team |
|---|---|---|
| 1 | Michael | Bulls |
| 2 | Kobe | Lakers |
| 3 | Bird | Celtics |
UPDATE
Updates existing records in a specified database collection. Returned payload type is updated.
Use SET to set the updated value of the record. Use WHERE to specify which record to update. Note that if you omit the WHERE clause, all records in the collection will be updated.
Syntax:
Example: sql('UPDATE Players SET name = 'Jordan' WHERE number = 1')
Example Output:
Players collection after update:
| number | name | team |
|---|---|---|
| 1 | Jordan | Bulls |
| 2 | Kobe | Lakers |
| 3 | Bird | Celtics |
DELETE
Deletes existing records from a specified database collection. Returned payload type is deleted.
Use WHERE to specify which record to delete. Note that if you omit the WHERE clause, all records in the collection will be deleted.
Syntax: DELETE FROM <collection> WHERE [<condition>]
Example: sql('DELETE FROM Players WHERE number = 3')
Example Output: 1 (Number of rows deleted)
JOIN
Joins two specified collections based on common values. Returned payload type is data.
Types:
Inner Join
Returns records that have matching values in both database collections.
Syntax:
Example: sql('SELECT Teams.city, Players.team, Players.name FROM Teams INNER JOIN Players ON Teams.name = Players.team')
Example Output:
Left Join
Returns all records from the left column of a specified database collection, and the matched records from the right column of another database collection.
Syntax:
Example: sql('SELECT Teams.city, Teams.name, Players.team, Players.name FROM Teams LEFT JOIN Players ON Teams.name = Players.team')
Example Output:
LIMIT
Limits the amount of records returned from an operation. Used in conjunction with the SELECT operator. Default is 50. Returned payload type is data.
Syntax: SELECT * FROM <collection> LIMIT <number>
Example: sql('SELECT number, name FROM Players LIMIT 1')
Example Output:
ORDER BY
Sorts records of specified database collection in ascending or descending order. Used in conjunction with the SELECT operator. Default is ascending order (ASC). Returned payload type is data.
Syntax:
Example: sql('SELECT name, championships FROM Teams ORDER BY championships ASC')
Example Output:
WHERE
Filters records using specific conditions. Returned payload type is data.
Single Condition
Syntax: … WHERE <condition>
Example: sql('SELECT * FROM Players WHERE name = 'Kobe'')
Example Output:
Supported operators:
Chained Conditions
You can chain 2 or more clauses. You can either chain ‘AND’ clauses or ‘OR’ clauses, but not both.
Syntax: … WHERE <condition> AND|OR <condition>
Example: sql('SELECT * FROM Teams WHERE city = 'Los Angeles' AND championships > 1')
Example Output:
Each operation returns a specific payload type:
data
inserted
true if the item successfully inserted or false if the item failed to insert).updated
deleted
The following describes how you can use this package to create a to-do list. We use the package functions to insert, update, get, and delete tasks from a database collection that we call MyTasks.
Import the sql() function from the backend package file to a web module. We’ll call our web module data.jsw.
To get, insert, update, and remove tasks from the to-do list, include the following code in the file:
Add the following page elements to your site:
Import the getAllTasks(), insertTask(), updateTask(),and removeTask()functions from the web module to your page code.
Import the local() module from wix-storage.
Declare variables for a font style and size and the visitor ID.
Initialize the repeater data and call the registerHandlers() and setVisitor() functions.
When the repeater’s data is assigned and ready, display the task text retrieved from the database collection in each of the repeater’s items. If the task is complete, label the button in the repeater with an ‘X’ and call the changeCompleteStatus() function with the item and itemData.
Call the createNewTask() function when the Add Task button is clicked or when a site visitor presses the Enter key in the task input.
Call the clearCompletedTasks() function when the Clear Marked Tasks button is clicked.
Include the following code to create a new task, change the complete button status, and clear the completed tasks.
Check whether the task was completed and format the task text accordingly (strike through the task text):
Get the visitor ID from Wix local storage. Call the fetchData() function to get all the tasks associated with the specified visitor ID and set the repeater’s data with the tasks.
1.0 Initial version.
SQL, wixData