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

Google Cloud SQL

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 (recommended)—Your domain administrator has pre-configured Google Cloud SQL. Choose this option if your app needs a database that requires no set up and is easy to use.

  • Custom—Choose this option if:

    • 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 access the Google Cloud Console or to use other management tools.

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:
  • Google Cloud SQL is part of Google Cloud Platform—You can start a free trial; however, continued use will incur additional expenses for your organization (pricing here). The typical monthly price for most App Maker apps is negligible.
  • Connect your app to a new custom Google Cloud SQL database

    Google Cloud SQL is a powerful database option, but it takes time to set up a custom instance and integrate it with App Maker. Your app can use an existing custom 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 Cloud SQL chevron_right Cloud SQL Client 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 custom 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.

    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:

    1. If you don't already have a Google Cloud SQL instance, set one up.
    2. Open your app and click Settings settings chevron_right Database.
    3. 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.
    4. 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.

    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.