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—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.

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 if you want to use a custom instance. 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 custom 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

    We recommend you use a Second Generation Cloud SQL instance if you are 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.

    Create a custom Google Cloud SQL database for your app

    Second Generation

    1. If you don't already have a Google Cloud SQL instance, create one and choose MySQL.
    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 New members dialog, enter:
        appmaker-maestro@appspot.gserviceaccount.com
        Choose Cloud SQL chevron_right Cloud 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 instances page.
      2. Open your instance and look for the Instance connection name field.
      3. Click the copy button.
    6. Create a Cloud SQL model for your app:
      1. Open your app and click Settings settings chevron_rightDatabase
      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
      1. Hover over Data and click add to add a model:
        1. Click Connect an external database then choose Google Cloud SQL (existing).
        2. Enter the username and password you created in Step 2.

    First Generation

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

    1. If you don't already have a Google Cloud SQL instance, create one and choose MySQL.
    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, 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 settings chevron_rightDatabase
      2. Click Switch to a custom Cloud SQL database.
      3. Enter the address in the following format:
        projectName:instanceName/databaseName
    6. Hover over Data and click add to create a new model.
    7. Give the model a name and click Create.
    8. Enter the username and password you created for your app in Step 2.
    9. Click Continue to create your model.

    Connect your app to an existing custom Google Cloud SQL database

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

    1. Add the instance details to your app as shown above.
    2. Hover over Data and click add to add a model.
    3. Click Google Cloud SQL (existing).
    4. Enter the address in the following format:

      • First generationprojectName:instanceName/databaseName
      • Second generationprojectName:regionName:instanceName/databaseName
    5. Enter the username and password for this database.

    6. 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 Database.
    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 Database 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:

    1. If you don't already have a Google Cloud SQL instance, set one up. Choose us-central as the region for your new instance.
    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.