Datasources

App Maker Widgets and the client API access data through datasources. Datasources define:

  • how your application queries data in your models
  • how your UI accesses this information

Datasources have an item property that points to the selected item in a datasource. In your UI, widgets use the item property to display or edit information about the currently selected record in the datasource. Client scripts can also access and modify records through this property.

In this article, you'll learn about:

  1. Types of datasources
  2. The datasource lifecycle
  3. How to create a query datasource, includes how to build a query and configure other datasource settings
  4. How to refine a query datasource with client scripts
  5. How to work with relation datasources to access associated records
  6. How to use create-mode datasources to create draft records from user input

Types of datasources

App Maker has three types of datasources:

  1. Query datasource
  2. Relation datasource
  3. Create-mode datasource

A query datasource stores:

  • information about a query of a single model
  • the records from the last execution of the query

A query datasource is created for each model by default, with the same name as the model. You can customize the query datasource and create more query datasources for a model. You might want more query datasources when you want to have different options selected for a single model, such as filtered results, different sort orders, or different page sizes. You might also use multiple datasources when you want to have different records selected simultaneously, such as to compare two datasource.item values. Use UI widgets and client-side scripts to change and re-execute the query. The results of the last query can be bound to widget properties and accessed in client-side scripts.

When you develop your UI, you typically associate a query datasource with the top-level panels of pages or composite widgets. Then all child widgets of the panel automatically inherit that datasource and you can associate widget properties with fields in the datasource records.

A relation datasource allows you to display data about associated records. You create a relation datasource from a query datasource or another relation datasource, for a particular relation end. Relation datasources display the records in a relation end that are associated with the parent datasource's currently selected record.

A common use of relation datasources is to implement a master-detail view. For example, you could build a UI to display a customer order and its related items. You'd have two models, Orders and Items, and create a one-to-many relation between them. For more information about how to use relation datasources in the UI editor, go to binding data to your UI.

Create-mode datasources create items in a parent query or relation datasource.

Datasource life cycle

Datasources can be in one of four states:

  1. Unloaded
  2. Unloaded + Loading
  3. Loaded
  4. Loaded + Loading

A datasource that is unloaded doesn't have any data; the items of the datasource are an empty array and the currently selected item is null. A datasource that is loading is in the process of getting data. A datasource that is loaded has data, although the data might be empty.

For a datasource to be in one of the loading states, a load must be triggered. Loads can be triggered in the following ways:

  1. By default, a load is triggered when a widget that uses that datasource is attached to a page. Generally, a widget is attached when a user opens the widget's parent page.
  2. By default, a load is triggered when a widget that has a binding to a datasource's data properties is attached. For example, if you have a label widget in a page Orders that is bound to a a field in the Customers model, then the Customers datasource is loaded when the Orders page is shown.
  3. A load is triggered by a client script that calls the load, loadPage, prevPage, or nextPage methods on datasource.

You can disable automatically loading data for query datasources, which prevents the first two load triggers in the list. To unload a datasource, call the unload method on the datasource.

Query datasources have an onLoad event that is triggered when a datasource transitions to the loaded state. The onLoad event is useful when you want to run a script that performs an action after a data source has loaded data. For example, you could enter the following script in the onLoad code editor to make a widget visible once the data is available:

var widget = app.pages.MyPage.descendants.MyWidget;
widget.visible = true;

Query datasources

When you create a model, a query datasource is automatically created. You can customize query datasources and create more query datasources for a model.

To edit or create a query datasource:

  1. Click the model in the navigation pane and go to its Datasources tab.
  2. To edit a datasource, click the datasource. To create a datasource, click Add datasource.
  3. Configure the datasource. Changes are saved automatically.

Datasources have the following configuration options:

Query server script

Datasources load all records from the model by default. The query script property allows you to override the default query behavior when you specify custom server-side logic, such as filters. For calculated models, the query script allows you to completely define the query behavior of a datasource and create records in the datasource.

Query script features and requirements:

  • You can call a server script function from the query script editor, which is useful if your query script is long.
  • You can pass parameters from the client to the query script with custom parameters.
  • The query script must return an array of records that belong to the datasource model.

Filter example

For example, the default Employees datasource query returns all records in the Employees model. Let's say that you want to only return employees whose age is an even number and greater than a minimum age specified in the query. To apply this query to the datasource, enter the following code in the Query Script code editor:

var employeeResult = [];
// Modify passed query to also filter by minimum age.
query.filters.Age._greaterThan = 20;
var employeesAboveMinimumAge = query.run();
for (var i = 0; i < employeesAboveMinimumAge.length; i++) {
  if (employeesAboveMinimumAge[i].age % 2 == 0) {
    employeeResult.push(employeesAboveMinimumAge[i]);
  }
}
return employeeResult;

Calculated record creation example

Calculated models are used to represent script results as records so that you can use them in the UI. For example, you want to query to your Employees model to create a calculated model that generates records of how many employees are in each location. Because the actual EmployeesByLocation records don't exist in the database, your query script needs to create records by calling newRecord(). The following script calculates the number of employees in each location and creates a record for each location in the calculated model:

var calculatedModelRecords = [];
var recordsByLocation = {};
var allEmployees = app.models.Employees.newQuery().run();
for (var i = 0; i < allEmployees.length; i++) {
  var employee = allEmployees[i];
  if (!recordsByLocation[employee.location]) {
    var calculatedModelRecord = app.models.EmployeesByLocation.newRecord();
    calculatedModelRecord.numberOfEmployees = 1;
    calculatedModelRecord.location = employee.location;
    calculatedModelRecords.push(calculatedModelRecord);
    recordsByLocation[employee.location] = calculatedModelRecord;
  } else {
    recordsByLocation[employee.location].numberOfEmployees++;
  }
}
return calculatedModelRecords;

Custom parameters

A query script can also use parameters that the database can't provide. For example, you might want to filter data based on the current stock price for a company, which you get from a web service. You can pass parameters from the client to your query script on the server with custom parameters.

To add a parameter:

  1. In the Datasources tab, click Add Parameter.
  2. Select the type of the parameter.
  3. Enter a name for the parameter.

Access and bind to the parameter under the parameters property of the datasource query. For example, you can bind a text field widget's value property to the query parameter with bindings:

textField.value <-> datasource.query.parameters.MyParam

Query scripts can access the parameter as a query parameter. To apply this approach to the earlier example, we can add the following to the query script. With this addition, the minimum age filter is added only if a boolean custom parameter called RestrictAge is true:

// Modify query to also filter by minimum age if client enables the option.
if (query.parameters.RestrictAge) {
  query.filters.Age._greaterThan = 20;
}
var employees = query.run();

Query builder

Query builder is available for Cloud SQL datasources. It's a simplified analog of the MySQL WHERE clause, which supports filtering on related models and makes queries against null easier. Use query builder to run complex queries based on information submitted by app users.

For example, you could build an app that lets users search an HR database for employees who qualify for different retirement plans. Query builder helps you create the logical expressions that your app needs to find data. This way, you don't have to write a custom script to interpret user input and apply it to a Cloud SQL database search. Query builder also supports code completion (press Ctrl+Space).

To implement a query with user input parameters:

  1. Construct your query expression in query builder, and create the user input parameters as you do.
  2. Bind widget properties to the user input parameters.

Constructing a query

Query builder processes queries according to the following rules:

  • Query expressions are evaluated from left to right.
  • A parameter that is entered by the user is distinguished from a model field by preceding it with a colon (:).
  • The and operator takes precedence over the or operator.
  • Subquery expressions within parentheses take precedence over and and or operators.
  • The "!" operator negates the boolean value of an expression.
  • The "?" modifier for leaf operators considers a null value for the right-hand side of the expression to be true. You can't negate expressions that use the "?" modifier.

For our HR database example, the app needs to return employees who are within a given age range or who started work after a specified date. The query is the following:

(Age >= :AgeMin and Age < :AgeMax) or StartDate > :StartDate
  • Age—A field in the app’s model. It contains the age for each employee. The app asks users to enter minimum (:AgeMin) and maximum (:AgeMax) ages. The query returns employees who fall within that range.
  • Colon (:)—Colons identify parameters that get their values through the app’s UI.
  • Parentheses—Identify a subquery. App Maker evaluates the subquery before it moves on to :StartDate.
  • and—An operator. It tells the app to return employees who meet both criteria.
  • or—An operator. It tells the app to return employees who meet either criteria.
  • StartDate—A field that contains the date an employee started work. Users enter a :StartDate, and the app returns employees who started after that date.

Binding a query expression to a UI

As you add parameters (preceded with colons) to your expression, query builder automatically recognizes them and creates query parameters in the area below query builder. They also appear in the data-binding UI, which you use to bind the parameters to widgets in your app.

To use the HR database example, the next steps are:

  1. Open Widgets widgets and add two text boxes text_fields to a page.
  2. Bind the Text Box value properties to:
    • @datasource.query.parameters.AgeMin
    • @datasource.query.parameters.AgeMax
  3. Add a Date Box widget date_range to the page.
  4. Bind the Date Box widget value property to:
    • @datasource.query.parameters.StartDate
  5. Change the onValueChange event to Reload Datasource for the input widget.

More query builder examples

Query Returns
Age >= :AgeMin and Age <= :AgeMax and Active = :IsActive All active employees between the minimum age and maximum age.
(Status = :Pending or Status = :Active) and Name startsWith :NamePrefix All pending or active employees whose names start with NamePrefix
!(Status = :Pending or Status = :Active) All employees who aren't pending or active.
Age >=? :AgeMin

If the query parameter AgeMin is null, then returns all employees. Otherwise, returns the employees older than the minimum age.

This operator can be useful when you bind the query parameter AgeMin in a UI. For example, if the AgeMin query parameter is bound to a text field, initially all employees are returned. The records are filtered once the user enters a value for AgeMin. If the user clears the text field, all employees are displayed again.

If the query uses the >= operator instead of >=?, then no employees are returned initially or when the user clears the text field.

Role notIn :Engineering or :Marketing

All employees who aren’t in Engineering or Marketing roles.

in and notIn work well with the multi select widget , which allows users to choose more than one of several parameters that you’ve defined.

Computer startsWith :Chromebook and notContains :Pixel All employees who have Chromebooks, regardless of specific model, and excludes employees with Chromebook Pixels.

SQL Query for Calculated SQL

For more information, go to Calculated SQL models.

Other query datasource properties

Page size

The page size setting controls the number of records that the datasource returns for a single query. Use this setting to control the number of results that are returned in the UI and to improve performance. If the server has 10,000 records, to load them all at once is slow and unmanageable in the UI. Instead, set a page size and create a Next button to call the nextPage method on the datasource.

To show all records, set the page size to zero.

Page size is a property of the datasource, so you can:

  • bind widget properties to it
  • access and modify page size in client-side scripts

For example, you can let the user control how many records are shown by binding a slider widget's value property to the datasource pageSize property.

Sorting

You can sort records by any sortable field in ascending or descending order. To sort by more than one field, use a client query script.

Manual save mode

When the value of a datasource item is changed in App Maker, the change is automatically saved to the server right away by default. For example, if a user changes the value in a Text Field widget, the new value is automatically saved to the record field that it is bound to.

To disable this autosave behavior, you can set the datasource in manual save mode. On the Datasources tab in the Model editor, select the Manual save mode checkbox.

In manual save mode, changes must be explicitly saved in the UI or in your script. In the UI, your input fields and forms behave like traditional web forms and require user action. A user must click a Save button for which the onClick action is Save Changes to Datasource.

In a client-side script, you can use the following methods for change management:

  • saveChanges—Saves the current changes to the server.
  • clearChanges—Abandons any changes that were made after the last call to saveChanges.
  • hasChanges—Returns true if the datasource has unsaved changes.

Changes made to records that were accessed through the datasource item and items properties, including records accessed through relation properties, are stored locally on the client and can be saved or rolled back.

Automatically load data

By default, datasources automatically load their data whenever a widget is bound to data on the datasource. When you clear the Automatically load data checkbox, the datasource only load data when you call load, loadPage, nextPage, or prevPage methods.

Delay data load when you want the user to provide the value of a query filter before you load data.

OnLoad action

To run client-side JavaScript whenever the datasource loads, enter the script in the code box. The onLoad action is triggered each time the results of a query are returned from the server to the client. Find more information about loading in the datasource life cycle section.

On item change action

To run client-side JavaScript whenever the current item changes, enter the script in the code box.

The current item can change for the following reasons:

  • The datasource reloads its data and the current item isn't in the returned data.
  • A client-side script calls the next or prev methods on the data source.
  • A client-side script or binding assigns a value to the item property on the datasource.
  • A client-side script calls the selectIndex or selectKey methods on the datasource.
  • Some widgets automatically change the current item for the datasource. For example, the list widget changes the current item of its datasource whenever the user clicks a row.

Prefetch

Prefetch is used to load associated records from a relation end efficiently when your client script or UI uses them. If your client scripts use records from relations, you must explicitly load the associated records from the server because the associated records from the other relation end aren't automatically loaded. One way to load associated records is to enable prefetch, which tells App Maker to load related records when it loads the query results.

Prefetch can improve UI performance when the datasource has many records and you need to load the associated records for all of them. For example, you have an Employees model with a Manager relation to itself with two relation ends: Manager and TeamMembers. Your UI displays a list of Employees records based on a query datasource, and the Manager for each employee is displayed.

Without prefetch, App Maker requests the associated manager record for each employee record in the datasource. This process is potentially slow because it requires many requests to the server. With prefetch enabled, App Maker loads the associated records with the query results and the UI works faster after the initial load.

Learn about other ways of loading associated records to the client in Modify associations with a client script.

Refine the datasource query on the client

A query datasource contains properties and methods that you can use to modify the query with a client script. The query object represents the query that is sent to the server when a datasource is loaded. It contains properties that specify the query conditions and the page of results to return.

When you trigger a load on a query datasource:

  1. The query datasource sends its current query to the server.
  2. The query datasource updates its data with the results of the query.
  3. The records on the client are also updated with the results of the query.

When you load a query datasource, you might get changes to record values on the client even if records in the query result don't change. This occurs when a server-side script or another client changed the record.

Filters

The query object's filters property holds the query conditions on the fields of a model. Specify a filter with the following syntax:

datasource.query.filters.field-id.filter-operator = value

Where:

  • field-id is a field in the model that can be used for filtering
  • filter-operator is the filter action, such as equals or contains. Which filter operators are supported depends on the field type and the database. Learn more about filter operators in the Client API query documentation.
  • value is the parameter used for the field and operator in the query. The type of the value must match the type of the field, except for the in and notIn operators. For the in and notIn operators, the parameter values must be arrays of the field type.

For a list field, one or more items in the list must satisfy the query filter. If you assign multiple values to properties under filters, then records must match all the filters to be loaded.

The query object for Cloud SQL model datasources also contains relation filter properties. These properties let you filter records by an associated record, its field, or its key. Filtering is supported only for one-to-one and many-to-one relations.

Client query examples:

  • Single filter—The following code adds a query filter to a widget datasource that returns records that have the name "John Smith":

    var datasource = widget.datasource;
    datasource.query.filters.Name._equals = 'John Smith';

  • More than one filter—The following code adds query filters that match records that start with the name "John" and whose age is more than 18.

    var datasource = widget.datasource;
    datasource.query.filters.Name._startsWith = 'John';
    datasource.query.filters.Age._greaterThan = 18;

  • List field matching—The following query loads the record record.Emails = ['smith.j@example.com', 'john@example.com'] because one of the items in the Emails list field starts with john@:

    datasource.query.filters.Emails._startsWith = 'john@';

  • in operator—The following code uses the in operator in a query filter to load records where the favoriteColor field value is blue or green:

    var datasource = widget.datasource;
    var colors = ['blue', 'green'];
    datasource.query.filters.FavoriteColor._in = colors;

  • Relation field filter—If the model Employee has a one-to-many relation to itself that represents managers and their team members, you can:

    • load records of team members that are assigned to manager1:

      var datasource = widget.datasource;
      datasource.query.filters.Manager._equals = 'manager1';

    • filter by manager's record keys:

      datasource.query.filters.Manager._key._in = ['manager1RecordKey', 'manager2RecordKey'];

    • filter by manager's manager name:

      datasource.query.filters.Manager.Manager.Name._equals = 'John Doe';

    As with other field filters, if you set the relation filter property value to null, the filter isn't applied.

Sorting

You can override the sorting that is configured for the datasource in a client script. Sort the data by one or more sortable fields of the datasource's model. For Cloud SQL model datasources, you can also sort by fields in related records, as long as the relations ends are one-to-one or many-to-one. For example, if you have a many-to-one Cities-State relation, you can sort cities by their state.

Learn more about the Query sorting property.

Page Index

The page index property specifies the page number to load. By default, page 1 of the query results is loaded. You can change this property to any positive number; the records for that page number are loaded the next time the query datasource is loaded.

Page Size

The page size property specifies the number of records to return from the server for a query result.

Relation datasources

Relation datasources allow you to easily display a record's associated records from the datasource of a relation end.

You can set a relation datasource for a widget in the Property editor. The datasource dialog shows you a relation datasource for each relation that is configured for the parent model. Learn about how to select relation datasources in the UI in Binding data to your UI. Work through an example in the Connect data models tutorial.

Load associated records

When the widget that is configured with a relation datasource is loaded, the records that are associated with the current record are automatically loaded. Associated records are also loaded when you trigger a load on a relation datasource.

Often, you want to make sure that associated records are loaded before the app uses the data. To run a script or be notified when associations are loaded for a record, use the onDataLoaded event for a widget. A common approach to use associated records in your app is to:

  1. Create an invisible panel that uses a relation datasource to ensure that related data is loaded
  2. Use the onDataLoaded event on the invisible panel to perform some logic that uses the related data.

To reload associated records from the server, call load on a relation datasource. You might want to reload the associated records when they're changed by a server script rather than by client-side scripts or bindings.

You can also use the _reload method on the current record to reload the associated records.

Create-mode datasources

Use a create-mode datasource to create items in a parent query datasource or relation datasource that aren't automatically saved to the database. Instead, data is stored in a draft record on the client-side until the user triggers an action to save the record. The draft record can also provide a template for new records. The create-mode datasource belongs to the same model as its parent datasource.

With create-mode datasource, you can:

  • Bind UI elements to fields in the create-mode datasource. These fields will populate the draft record with values.
  • Set default field values for the datasource record. With this approach, required fields always have values. After a create-mode datasource saves a record to the database, the field values are reset to their default values. You can set default values with a script or in the UI.

You can work with create-mode datasources with the app UI and with a client script.

Create records with a form

Values in input-type forms are bound to fields in the create-mode datasource by default. As a user fills in the form, App Maker saves the values to the draft record. The create-mode datasource is @datasource.parent-datasource.modes.create, which the UI reports as parent-datasource (create). Each field in the form is bound to datasource.item.field, which correspond to the fields in the draft record.

As a user fills in the form, the record is saved on the client until the user clicks Submit. The Submit button's onClick action is datasource.item.createItem(), which creates the item and adds it to the parent datasource. The Clear button's onClick action is widget.datasource.clearChanges(), which clears the create datasource's record.

Learn more in Data widgets: Form and the Work with Data tutorial.

Create records with a script

Client scripts can add records to create-mode datasources. Add records this way when you want to:

  • automatically add session data, such as the username and date of record creation
  • process data from a form before you create the record so that it matches a field in the datasource, such as to concatenate first and last names

For example, you have a model People with a DateAdded field. If you set the DateAdded property of the create-mode datasource's record to new Date();, then a record created through that datasource has the current date of the user's timezone to start:

var createDatasource = app.datasources.people.modes.create;
var draft = createDatasource.item;
draft.DateAdded = new Date();
createDatasource.createItem(function(createdRecord) {
 alert('Created record on ' + createdRecord.DateAdded)
});

Learn more about CreateDataSource.