Datasource

sqlite

12min

With SQLite, you can write your datasources as SQL queries. You can use the queries in datasources to create a select statement and get the data from the tables you need to use in your file. You can use it directly in the file, where the UI component configuration is or you can use it as a global datasource.

If you are not familiar with datasources yet, see the data section.

Configuration options

When setting up a SQLite datasource, you use the datasource in the following ways:

  1. In your file as a locally configured datasource under the datasource section.
  2. In the global datasource file, that will allow you to use it across all the files. The global datasource files are located under the datasources folder in .

Examples and code snippets 

View common examples of using SQLite below, you can use these as a guideline to configure the same in your solutions.

Locally configured datasource
Locally configured datasource


Datasource with type sqlite to select name, and email from the employee's table and to display the list of employees.

Example: See the full example on GitHub.

sqlite-jig

Global configured datasource
Global configured datasource


Datasource with type sqlite as global datasource to select name, and email from the employee's table and to display the list of employees.

Example: See the full example on Github.



sqlite-global-datasource
list-jig.jigx


SQLite query cheatsheet

Count rows in the table

In this example a SQLite query is used to count the rows in the avatar table.

Count rows
Count rows

count-rows.sql
count-rows-dd.jigx


Converting dates

Here is an example of using a SQLite query to convert dates in a dynamic data table.

SQLite - Converting dates
SQLite - Converting dates

converting-dates.sql


Joining data from tables and using subquery

Using a SQLite query, one can join data from different tables by utilizing subqueries. This allows for the combination of information from multiple tables based on specified conditions. By employing subqueries, one can retrieve and manipulate data ensuring accurate and comprehensive results. Below is an example of joining tables and then using a subquery.

Steps table
Steps table

Strategy table
Strategy table

join-and-subquery.sql


Joining tables

Often you want to use data in your solution but the data is stored in different tables. Use a SQLite query to join the data from tables and extract the exact information you want to use. When joining two tables there must be the same identifier in both tables. In the example below both tables has a $.date column. Result: The result of the example below is  date: 5 finished_date: 5.11

mood-notes table
mood-notes table

dates table
dates table

join-table.sql


JSON array length

The query below provides the JSON array length from a table called battles.

Dynamic data table
Dynamic data table

json-array-length.sql