App Maker apps store data in Google Cloud SQL, a service that delivers fully managed SQL databases in the cloud. You can learn more about the benefits of Cloud SQL here.
As an app developer, you can choose from two types of Google Cloud SQL instances:
Default—a G Suite administrator can set up a Google Cloud SQL instance that is shared among App Maker apps in an organization. When this is enabled, a new database is automatically created for your app when you add at least one Cloud SQL data model. Choose this option if your app needs a database that is easy to use and requires no set up.
Custom—Once your administrator sets up a default Cloud SQL instance, you can also set up your own Cloud SQL instance. Choose this option when:
- your application serves a large number of users or stores a large amount of data.
- the database must be shared with other applications.
- you need to manage the database or retain control of the Cloud SQL instance.
App Maker and Cloud SQL security
Whether your app uses the Default or a Custom Cloud SQL instance, the credentials are always encrypted both in transit and at rest:
Default Cloud SQL–credentials are stored on the App Maker server. They're never accessible to app developers or end users.
Custom Cloud SQL–once a developer submits Cloud SQL credentials to App Maker, they're never again visible to the app's developers. App Maker stores the credentials in the browser while a developer works on an app in the editor. The credentials are removed when the developer reloads or closes the browser tab.
When a developer deploys an app with a Custom Cloud SQL model, the credentials are stored on Google servers with the deployed app. They're never accessible to end users.
Connect your app to an existing custom Google Cloud SQL database
You can create a model from existing Google Cloud SQL data:
- Add the instance details to your app as shown above.
- Hover over Data and click add to add a model.
- Click Google Cloud SQL (existing).
Enter the address in the following format:
- First generation—
- Second generation—
- First generation—
Enter the username 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
If the schema in the Google Cloud SQL changes, you can update your models:
- Click Settings Database.
- 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 SettingsDatabase to:
- Switch to the default SQL instance.
- Update the instance address.
- Check model and relation compatibility.
Switch between default and custom Google Cloud SQL databases
For domains that have set up default Google Cloud SQL instance, new models use this instance. The default Google Cloud SQL database is ideal if your app needs a database that requires no set up and is easy to use.
If you find the default database doesn't meet your needs, you can convert it to a custom database:
- If you don't already have a Google Cloud SQL instance, set one up. Choose
us-centralas the region for your new instance.
- Open your app and click Settings Database.
- Click the Check button to verify model and relationship compatibility. If App Maker reports incompatibilities, you may not be able to proceed without deleting data from your database.
- When you're ready to proceed, click the Switch to a custom Cloud SQL database button.
After switching to a custom database, you have the option of switching back to a default database.
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.
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.
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
- 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
To display the filtered data, the app uses a calculated model
EmployeeCount with two fields:
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
PossibleNames parameter is translated into
(?, ?, ?, ...), and the
elements of the parameter value are used for the SQL placeholders.
For time-based parameters, you need to set the time zone.
Read more about relation filtering in Relations.
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
- Open the Fields tab for a model.
- 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
- Open the Datasources tab for a calculated SQL model.
- Click a datasource, then click Add Parameter.
- 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 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.
To search by keyword, create a custom query that simulates a full-text search using
containsoperators. For example, for a model
Department, use the query:
(Name contains? :Keywords) OR (Department contains? :Keywords)and bind the
Keywordsparameter to a text field's