SQL Package

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.

Setup

This package does not require any initial setup.

Package Content

The following backend files are included in the package. Note that only exported functions that you can use in your site are listed here.

exposer.js

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:

1

• 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.

  Copy

  1
  
  

How to Use the Package

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.

Operators

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

Teams

Main Operators

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:

  Copy

  1
  
  

• 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:

  Copy

  1
  
  

• 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:

  Copy

  1
  
  

• Functions

  Selects the specified column from the specified database collection and executes a specified function on the selection.

  Supported Functions:

  • MIN: Minimum value
  • MAX: Maximum value
  • SUM: Sum of all values. For numeric fields only.
  • AVG: Average of all values. For numeric fields only.
  • COUNT: Number of records.

  Syntax:

  Copy

  1
  
  

  Example: sql('SELECT MIN(championships), MAX(championships) FROM Teams')

  Example Output:

  Copy

  1
  
  

INSERT INTO

Inserts a new record (row) into a specified database collection. Returned payload type is inserted.

Syntax:

1

Example: sql('INSERT INTO Players (number, name, team) VALUES (3, 'Bird', 'Celtics')')

Example Output: true

Players collection after insertion:

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:

1

Example: sql('UPDATE Players SET name = 'Jordan' WHERE number = 1')

Example Output:

Players collection after update:

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)

Additional Operators

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:

  Copy

  1
  
  

  Example: sql('SELECT Teams.city, Players.team, Players.name FROM Teams INNER JOIN Players ON Teams.name = Players.team')

  Example Output:

  Copy

  1
  
  

• 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:

  Copy

  1
  
  

  Example: sql('SELECT Teams.city, Teams.name, Players.team, Players.name FROM Teams LEFT JOIN Players ON Teams.name = Players.team')

  Example Output:

  Copy

  1
  
  

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:

1

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:

1

Example: sql('SELECT name, championships FROM Teams ORDER BY championships ASC')

Example Output:

1

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:

  Copy

  1
  
  

  Supported operators:

  • = equal
  • != not equal
  • <> not equal
  • < less than
  • <= less than or equal
  • > larger than
  • >= larger than or equal
  • LIKE search for a pattern using wildcards. For example:
    • Begins with: WHERE value LIKE ‘prefix%’
    • Contains: WHERE value LIKE ‘%infix%’
    • Ends with: WHERE value LIKE ‘%postfix’

• 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:

  Copy

  1
  
  

Payload Types

Each operation returns a specific payload type:

data

• path: result.payload.data
• type: Object with 2 keys:
  • columns: Array of Strings representing the collection’s fields (column names)
  • rows: Array of Objects representing the collection’s items (entries). Contains a key-value mapping representing the relationship between a collection’s column (field) and the specific entry’s (item’s) value for that column.
• returned by: SELECT, JOIN

inserted

• path: result.payload.inserted
• type: Boolean (true if the item successfully inserted or false if the item failed to insert).
• returned by: INSERT INTO

updated

• path: result.payload.updated
• type: Number (number of entries updated).
• returned by: UPDATED

deleted

• path: result.payload.deleted
• type: Number (number of entries deleted).
• returned by: DELETE

Example: Create a To-Do List

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.

1. Import the sql() function from the backend package file to a web module. We’ll call our web module data.jsw.

   Copy

   1
   
   

2. To get, insert, update, and remove tasks from the to-do list, include the following code in the file:

   Copy

   1
   
   

3. Add the following page elements to your site:

   • Text input for adding a task.
   • Button to trigger the addition.
   • Button to clear marked tasks.
   • Repeater with a task complete button and text for displaying the task.

4. Import the getAllTasks(), insertTask(), updateTask(),and removeTask()functions from the web module to your page code.

   Copy

   1
   
   

5. Import the local() module from wix-storage.

   Copy

   1
   
   

6. Declare variables for a font style and size and the visitor ID.

   Copy

   1
   
   

7. Initialize the repeater data and call the registerHandlers() and setVisitor() functions.

   Copy

   1
   
   

8. 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.

   Copy

   1
   
   

9. Call the createNewTask() function when the Add Task button is clicked or when a site visitor presses the Enter key in the task input.

   Copy

   1
   
   

10. Call the clearCompletedTasks() function when the Clear Marked Tasks button is clicked.

    Copy

    1
    
    

11. Include the following code to create a new task, change the complete button status, and clear the completed tasks.

    Copy

    1
    
    

12. Check whether the task was completed and format the task text accordingly (strike through the task text):

    Copy

    1
    
    

13. 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.

    Copy

    1
    
    

Release Notes

1.0 Initial version.

Tags

SQL, wixData