Cloud SQL

App Maker apps usually store data in Cloud SQL, a Google Cloud Platform (GCP) service that delivers fully managed SQL databases in the cloud. Learn more about the benefits of Cloud SQL.

As an app developer, you can choose from two types of Cloud SQL instances:

• Default—a G Suite administrator can set up a Cloud SQL instance that is shared among App Maker apps in an organization. When the default instance is set up, 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 setup. Organizations typically use a default instance while developers prototype and test an app, then switch the app to a custom instance when the app is ready for deployment as a production app.

• Custom—Once your admin 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.

Note: You can't add a custom Cloud SQL instance until a G Suite admin sets up a default Cloud SQL instance for your organization.

Cloud SQL security

Whether your app uses the Default or a Custom Cloud SQL instance, the credentials are always encrypted 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.

Before you start

Before you use Cloud SQL with App Maker, review the following security and cost considerations:

• Data stored in GCP resides outside of your G Suite organization—Some organizations, especially government agencies, have strict requirements for storing sensitive data. Both GCP 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:

  • Google Cloud Platform
  • G Suite

• Cloud SQL is part of GCP—You can start a free trial if you want to try a custom instance. However, continued use incurs expenses for your organization. Learn more about pricing). The typical monthly price for most App Maker apps is negligible.

Use a new custom Cloud SQL instance for your app

If you don't want to use the default Cloud SQL instance, you can use a custom Cloud SQL instance instead. To use a custom Cloud SQL instance, a G Suite admin must still set up a default instance first. Your app can use an existing custom database, or you can create a new one. If you create a new custom instance, it takes some time to set it up and integrate it with App Maker.

Note: You can't connect your app to a database in a custom Cloud SQL instance unless your G Suite admin sets up a default Cloud SQL instance.

We recommend that you use a Second Generation Cloud SQL instance if you're setting up a new one. App Maker also supports First Generation instances. Pricing and performance characteristics vary depending on the type of instance, so review capabilities before you create an instance.

Note: App Maker supports only MySQL databases. You can't use PostgreSQL.

The following instructions require that you have:

• Access to the GCP Console.
• Permissions to create Cloud SQL instances and users.

Second Generation

1. If you haven't already, create a Second Generation Cloud SQL for MySQL instance in the us-central region.
   
   Note: When you configure the instance:

   • You must select MySQL as the database type.
   • We recommend that you use the us-central1 region for your new instance, regardless of your geographic location. You can select a different region, but app performance might not be satisfactory because apps are run in us-central.
2. In the GCP Console, create a MySQL user account for your app to use to access the database.

   Record the username and password so that you can enter them when you connect your app to the database. To prevent unauthorized access through user accounts, we recommend that you set strong passwords for all accounts on the instance.

3. In the GCP Console, 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 (in the GCP Console, click Menu IAM & Admin).
   2. Select the project that contains your Cloud SQL instance.
   3. Click Add.
   4. In the New members dialog, enter:
      [e-mail]
      Choose Cloud SQLCloud SQL Client as the role.
   5. Click Save.
   6. If other developers will edit or deploy this app, add their Google accounts as new members and assign the Cloud SQL Client role.
5. Copy the details of your instance:
   1. Open the Cloud SQL instances page (in the GCP Console, click Menu SQL).
   2. Click your instance and look for the Instance connection name field.
   3. In the Instance connection name box, click Copy .
6. Set your app to use the custom Cloud SQL database:
   
   1. In App Maker, open your app and click Settings Database.
   2. Click Switch to a custom Cloud SQL database.
   3. Paste the instance details, and then add the name of the database you created in Step 3. Use the following format:
      projectName:regionName:instanceName/databaseName
      
   4. Enter the username and password for the database user account, then click Continue.

First Generation

App Maker supports First Generation instances. However, if you're creating a new instance, we recommend that you create a Second Generation instance instead.

1. If you haven't already, create a First Generation instance.
   
   Note: When you configure the instance:

   • You must select MySQL as the database type.
   • We recommend that you use the us-central1 region for your new instance, regardless of your geographic location. You can select a different region, but app performance might not be satisfactory because apps are run in us-central.
2. Configure user accounts for this instance:
   • Change the password for the root account. To prevent unauthorized access through the App Engine connection (created in the following steps), we recommend that you set a strong password for the account.
   • Create a user account that your app can use to access the database. When you create the database user:
     • Specify localhost as the Host Name.
     • Set a strong password for the account to prevent unauthorized access.

     Record the username and password so that you can enter them when you connect your app to the database.

3. In the GCP Console, 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 (in the GCP Console, click Menu SQL).
   2. Click the instance name to open its Overview page, and then click the Authorization tab.
   3. Click Add project ID and enter:
      appmaker-maestro
   4. Click Done to exit edit mode.
   5. Click Save to update the instance.
5. Add the custom Cloud SQL instance to your app.
   
   1. Open your app and click Settings Database
   2. Click Switch to a custom Cloud SQL database.
   3. Enter the address in the following format:
      projectName:instanceName/databaseName
      
   4. Enter the username and password for the database user account, then click Continue.

Use an existing Cloud SQL database for your app

If you already have data in Cloud SQL, you can create a model in your app that uses it:

1. Add the instance details to your app:
   1. Open the Cloud SQL instances page (in the GCP Console, click Menu SQL).
   2. Click your instance and look for the Instance connection name field.
   3. In the Instance connection name box, click Copy .
   4. In App Maker, open your app and click Settings Database.
   5. Click Switch to a custom Cloud SQL database.
   6. Paste the instance details, and then add the name of the existing database. Use the following format: projectName:regionName:instanceName/databaseName
   7. Enter the username and password for the database user account, then click Continue.
2. In the App Maker UI, next to Data click Add.
3. Select Google Cloud SQL (existing).

4. Enter the address in the following format:

   • First generation—projectName:instanceName/databaseName
   • Second generation—projectName:regionName:instanceName/databaseName

5. Enter the username and password for the database user account.

6. Select a table from the list and click Import. 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 Cloud SQL database 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 Cloud SQL database changes, you can update your models:

1. Click Settings Database.
2. Under Model and relation compatibility click the Check button. You have options to update all 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 Cloud SQL, the corresponding field is also deleted from the model.

Update Cloud SQL settings for your app

You can go to Settings Database to:

• Switch to the default SQL instance.
• Update the instance address.
• Check model and relation compatibility.

Switch between default and custom Cloud SQL databases

For organizations that have set up a default Cloud SQL instance, new models use this instance. The default 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:

1. If you haven't already, set up a custom Cloud SQL instance. Choose us-central as the region for your new instance.
2. Open your app and click Settings Database.
3. Click the Check button to verify model and relationship compatibility. If App Maker reports incompatibilities, you might not be able to proceed without deleting data from your database.
4. When you're ready to proceed, click the Switch to a custom Cloud SQL database button.

5. Enter the address in the following format:

   • First generation—projectName:instanceName/databaseName
   • Second generation—projectName:regionName:instanceName/databaseName

6. Enter the username and password for the database user account, then click Continue.

After switching to a custom database, you have the option of switching back to a default database.

Work with Cloud SQL records

Each record in a 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 manual-save-mode datasource have synthetic keys that don't match the primary key column even after they're saved.

You have several options for handling primary keys in App Maker:

Cloud SQL primary keys
Natural primary keys	When a Cloud SQL table uses natural primary keys, the field in the app model that corresponds to that key is required, and a value must be specified before saving a record. For more information about handling required fields, go to Validate data.
Surrogate primary keys	If a 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, 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 app. To set this option in App Maker: In the Data section of the navigation pane, click the data model. In the Fields tab, click the numeric field that you want to auto-increment. Click Advanced and select Auto Increment.
Unique ID primary keys	App Maker can set a unique ID for a primary key field when a record is created. The unique ID is 12 characters long and is randomly generated; it isn't provided by the user or the application. Use unique ID primary keys when you don't want the key to give informaton about the order or total number of records in a table, such as when users have access to only some records. To create a unique ID primary key in an App Maker model that already has a numeric primary key: In the Data section of the navigation pane, click the data model or create a new model. In the Fields tab, click the Id field. Click Advanced and clear the Auto increment checkbox. Click Add fieldString. Click Set as primary key. Enter a name for the field, such as "uniqueID". Click Advanced and select Auto unique identifier. (Optional) Delete the Id field.

Relations

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

Views

You can import 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;

Calculated SQL Models

A SQL calculated model uses a Cloud SQL query to retrieve data. Like other calculated datasources, a SQL datasource can't create, delete, or update records.

To create a SQL calculated model:

1. In App Maker, next to Data click Add .
2. Select Calculated SQL and click Next.
3. Name the model and click Create.

4. Go to the Datasources tab. Add the SQL datasource (or use a default one), 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 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 select the IDs of departments with names that match an element in a parameter list, you can write a query like the following:

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.

For time-based parameters, you need to set the time zone.

Note: Calculated SQL models don't inherit or respect the security restrictions set for the queried models. Set security restrictions for the calculated SQL model itself in its Security tab.

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.

Learn more about relation filtering.

Time zones

In some situations, a Cloud SQL instance and App Maker are in different time zones. This can cause undesired dates and times to be written to the database. Review the following table and ensure that you set your date field types appropriately.

Time zone handling for date field types
Cloud SQL models	Set the date field type as required: Open the Fields tab for a model. Click a date field, then click Advanced to set the date type: DATE or DATETIME—Use for fields that don't have a time zone. App Maker converts the time to the time zone of the App Maker server (as set in App Settings) before writing it to the database. TIMESTAMP—Use for fields that have a time zone. App Maker saves the user's time zone and the date is written in Cloud SQL relative to the Unix epoch. For both date types, App Maker converts the date to the browser's time zone when it displays the data.
Calculated SQL models	When you add a Date-type field to a calculated SQL model, it is added as a DATETIME type. Note: We recommend that you don't set your datasource to query a TIMESTAMP field in a regular cloud SQL model for date values. The date is read correctly only if the app and the database are in the same time zone.
Calculated SQL datasource query parameters	Set the date field type as required: Open the Datasources tab for the calculated SQL model. Click the datasource, then click Add Parameter. For date parameters, click the Data SQL Type drop-down list and select a type: DATE or DATETIME—Use for parameters that don't have a time zone. When the query uses a DATE or DATETIME field, the App Maker server converts the value to the server's time zone from the app user's time zone before it is used in the query. For example, if a user in PST (UTC–8) queries for records that were created before January 1, 2018 at 9 am and the App Maker server is in CT (UTC–6), then the query searches for records that were created before January 1, 2018 at 11 am. TIMESTAMP—Use for parameters that have a time zone. App Maker converts the time to UTC before it is used in the query. We recommend that you don't use TIMESTAMP-type query parameters because the date is handled correctly only if the app and the database are in the same time zone.

Restrictions and limitations

• If your Cloud SQL tables have zero or multiple primary key columns, App Maker can't modify or make relations for them.

• 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 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 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 doesn't support searches by keyword. As a workaround, 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.