G Suite Business customers can preview App Maker. Ask your domain admin to apply for early access.

Google Cloud SQL

Your app can access tens of gigabytes of data stored in Google Cloud SQL, a MySQL relational database hosted on Google Cloud Platform. You can use Google Cloud SQL with existing data from other projects. You can also share data among multiple App Maker apps.

Before you start

There are some important factors you must understand before you use Google Cloud SQL with App Maker:

  • Data stored in Google Cloud Platform resides outside of your G Suite domain—Some organizations, especially government agencies, have strict requirements for storing sensitive data. Both Google Cloud Platform and G Suite meet rigorous privacy and security standards, but your use of each of these services may be subject to different terms. Learn more about how we comply with privacy and security standards: If your organization expects all of its data to be treated in accordance with G Suite terms, your app should use Google Drive Tables, which stores all data on G Suite infrastructure.
  • Google Cloud SQL is not included with App Maker—Google Cloud Platform is a separate service. You can start a free trial, however, continued use will incur additional expenses for your organization.
  • App Maker can only write to tables that have a single primary key field—If you have an existing Google Cloud SQL table with zero or multiple primary keys, you can still query it in App Maker, but you can't write to it.

Connect your app to a new Google Cloud SQL database

Google Cloud SQL is a powerful database option, but it takes time to set up an instance and to integrate it with App Maker. Your app can use an existing database, or you can create a new one.

Types of Google Cloud SQL

You can use App Maker with both First Generation and Second Generation Cloud SQL instances. Pricing and performance characteristics vary depending on the type of instance, so review capabilities before you create an instance.

Create a new Google Cloud SQL database for your app

First Generation

  1. If you don't already have a Google Cloud SQL instance, create one and choose MySQL. Choose us-central as the region for your new instance.
  2. Configure user accounts for this instance:
    • Change the password for the root account.
    • Create a user account your app can use to access the database.
  3. Create a new database.
  4. App Maker runs on App Engine. Set up a connection with App Engine by adding App Maker's application ID to your Cloud SQL instance:
    1. Go to the Cloud SQL Instances Page.
    2. Click the instance name to open its Overview page.
    3. In the Authorized App Engine applications section, click Add application ID and enter:
      appmaker-maestro
    4. Click Done to exit edit mode.
    5. Click Save to update the instance.
  5. Create a Cloud SQL model for your app.
    1. Hover over Data and click add to add a model.
    2. Click Connect an external database then choose Google Cloud SQL (new).
    3. Enter the address in the following format:
      projectName:instanceName/databaseName
    4. Enter the user name and password you created in Step 2.

Second Generation

  1. If you don't already have a Google Cloud SQL instance, create one and choose MySQL.
  2. Configure user accounts for this instance:
    1. Change the password for the root account.
    2. Create a user account your app can use to access the database.
  3. Create a new database.
  4. App Maker runs on App Engine. Set up a connection with App Engine by adding a service account to your Cloud project:
    1. Go to the IAM & Admin Projects Page.
    2. Select the project that contains your Cloud SQL instance.
    3. Click person_add Add.
    4. In the Add members dialog, enter:
      appmaker-maestro@appspot.gserviceaccount.com
      Choose Project chevron_right Editor as the role.
  5. Create a Cloud SQL model for your app:
    1. Hover over Data and click add to add a model.
    2. Click Connect an external database then choose Google Cloud SQL (new).
    3. Enter the address in the following format:
      projectName:regionName:instanceName/databaseName
    4. Enter the username and password you created in Step 2.

Connect your app to an existing Google Cloud SQL database

You can create a model from existing Google Cloud SQL data:

  1. Hover over Data and click add to add a model.
  2. Click Connect an external database then choose Google Cloud SQL (existing).
  3. Enter the address in the following format:

    • First generationprojectName:instanceName/databaseName
    • Second generationprojectName:regionName:instanceName/databaseName
    • Enter the user name and password for this database.
    • Select a table from the list on the next page and click Import to create your model. You can import any table that's not a join table.

App Maker automatically creates relations once you import both tables involved in a relation. App Maker recognizes relations in a Google Cloud SQL instances using join tables or foreign key constraints in imported tables. For example, if table A has a foreign key to a table B, App Maker automatically creates a many-to-one relation from A to B.

If the schema in the Google Cloud SQL changes, you can update your models:

  1. Click Settings settings chevron_right Cloud SQL Settings.
  2. Under Model and relation compatibility click the Check button. You have options to update all Google Cloud SQL models to match the database or to update the database to match the models.

If you update models, App Maker preserves changes to the model like modified field names, validation properties, and events. However, if a field is deleted from Google Cloud SQL, the corresponding field is also deleted from the model.

Update Cloud SQL settings for your app

You can go to Settings settings chevron_right Google Cloud SQL to:

  • Update the instance address.
  • Check model and relation compatibility.
  • Update the credentials your app uses to access your database during previews.

Work with Google Cloud SQL records

Each record in a Google Cloud SQL model represents a row in a table. If you create an App Maker record, your Cloud SQL instance adds a row to the corresponding table.

App Maker generally uses the table's primary key column as the key for its records, and it can't change primary key values after record creation. There are some cases when the keys don't match:

  • Records created on the server using newRecord() have a null key until they're saved, even if the primary key column is assigned.

  • Draft records on the client have synthetic keys that don't match the primary key column until they're saved.

  • Records created on the client in a datasource with manual save mode have synthetic keys that don't match the primary key column even after they're saved.

Natural Primary Keys

If a Google Cloud SQL table uses a natural primary key, the field that corresponds to that key is required, and a value must be specified before saving a record.

Surrogate Primary Keys

If a Google Cloud SQL table uses a surrogate key, you can set the primary key before the record key. Alternatively, if the database can provide the primary key, you can leave the record key null and let App Maker update it automatically. This method doesn't work for records created on the client with manual save mode datasources.

Auto-increment Primary Keys

For integer primary keys, Google Cloud SQL can automatically assign a sequence number as the primary key value when the record is created. In this case, the primary key field value does not have to be provided by the user or the application. You can set this option in the Advanced section of the field editor for numeric primary key fields.

Unique ID Primary Keys

Optionally, App Maker can set a unique ID for a primary key field when a record is created. In this case, the primary key field value does not have to be provided by the user or the application. You can set this option in the Advanced section of the field editor for string primary key fields. This unique ID is 12 characters long and is randomly generated. Usually, auto-increment primary keys are used in preference to unique ID primary keys because they are shorter, however, unique ID primary keys can be preferable in some cases, because they do not give information on the order or the size of the records in a table to a user who only has access to particular records.

Relations

Fields that correspond to foreign key fields can be read and modified directly using server scripts. On the client, the values of the foreign key fields and their corresponding associations can be modified as relations. For more information on modifying relations, see Relations.

Views

You can import Google Cloud SQL views as read-only data models. Models based on views can take advantage of SQL queries that involve joins, aggregations, and sub-selects. For example, the following schema uses a view to aggregate data from a table for easy display in App Maker:

CREATE TABLE Orders (FruitName varchar(128), int Amount);
CREATE VIEW FruitOrders AS SELECT FruitName, sum(Amount) AS Amount FROM
    Orders GROUP BY FruitName ORDER BY FruitName;

SQL Calculated Model

A SQL calculated model uses a Google Cloud SQL query to retrieve data. Like other calculated datasources, a SQL datasource can't create, delete, or update records. Add the SQL datasource (or use a default one) in the model's Datasources tab, and enter a single query in the SQL Query field. The SQL query must have the following properties:

  • The query shouldn't have a limit or offset clause. Instead, App Maker adds limit and offset clauses according to the datasource's pageSize and pageIndex.
  • The SQL query output column names and types must match the names and types of the calculated model.
  • The SQL query can only contain parameters prefaced by a colon, like :Param. Those parameters must match the name and type of a custom property on the datasource.

For example, an HR app needs to display the number of employees in each department above a given age. It has a Google Cloud SQL model Employee with Age and Dept fields.

To display the filtered data, the app uses a calculated model EmployeeCount with two fields: DeptName and Count. The calculated model has a SQL datasource with a number custom property called MinAge and uses the following query:

SELECT Dept AS DeptName, COUNT(*) AS Count FROM Employee WHERE Age > :MinAge GROUP BY Dept

List parameters are translated into a parenthesized parameter list in SQL. For example to write a query to select the IDs of departments with names that match an element in a parameter list, you can write something like:

SELECT id from Departments WHERE Name in :PossibleNames

The PossibleNames parameter is translated into (?, ?, ?, ...), and the elements of the parameter value are used for the SQL placeholders.

Relation filtering

Cloud SQL is the only model type that can be filtered by a field in a related model in Data Binding and Server Scripting.

Read more about relation filtering in Relations.

Time zones

In some situations, a Cloud SQL instance and App Maker are in different time zones. This can cause incorrect dates and times to be written to the database. To ensure proper handling of time zones, you need to change the data type as follows:

Cloud SQL models

  1. Open the Fields tab for a model.
  2. Click a field, then click Advanced to set the data type:
    • TIMESTAMP—for fields that have a time zone. App Maker converts the time to PT (UTC−7 or UTC−8) before writing it to the database.
    • DATE or DATETIME—for fields that don't have a time zone. App Maker reads and writes data as PT (UTC−7 or UTC−8).

Calculated SQL datasource query parameters

  1. Open the Datasources tab for a calculated SQL model.
  2. Click a datasource, then click Add Parameter.
  3. For date fields, choose an SQL Type from the dropdown:
    • TIMESTAMP—for parameters that have a time zone. App Maker converts the time to UTC before writing it to the database.
    • DATE or DATETIME—for parameters that don't have a time zone. App Maker writes data as entered by the user.

Restrictions and limitations

Google Cloud SQL models have a few limitations:

  • App Maker can't modify or make relations for Google Cloud SQL tables that have zero or multiple primary key columns.

  • App Maker represents BIGINT values as strings, because the number range represented by BIGINT is too large to be represented with JavaScript numbers.

  • App Maker can't import some Google Cloud SQL column types like BLOB, CLOB, and other binary types. You can still import models with those column types, but these columns won't be readable or writable. If these columns are marked non-null, then all other columns in the entire data model will be read-only.

  • App Maker can't express queries against Google Cloud SQL models that involve joins, aggregations, or sub-selects. Instead, import a SQL view as a model or use a SQL datasource in a calculated model.

  • App Maker's Google Cloud SQL back end doesn't support the keywords property for queries. Instead, create a custom query that simulates a full-text search using contains operators. For example, for a model Employee with fields Name and Department, use the query: (Name contains? :Keywords) OR (Department contains? :Keywords) and bind the Keywords parameter to a text field's value property.