Stay organized with collections Save and categorize content based on your preferences.

See the supported connectors for Application Integration.

Create integration with a MySQL connection

This tutorial shows you how to connect to a MySQL database instance from a sample integration and perform List, Get, Create, Update, and Delete operations on a MySQL database table.

To complete this tutorial, perform the following tasks:

Before you begin

  • Ensure that you have access to integrations.
  • Do the following in your Google Cloud project:

    • Grant the following roles to the service account that you want to use to create the connection:
      • roles/secretmanager.viewer
      • roles/secretmanager.secretAccessor
    • Enable the following services:
      • secretmanager.googleapis.com (Secret Manager API)
      • connectors.googleapis.com (Connectors API)

      If these services have not been enabled for your project previously, you are prompted to enable them when creating the connection in the Create Connection page.

  • Ensure that you have access to a MySQL server that you can use to create a database.

Set up a database and table in your MySQL server

Connect to your MySQL server and create a database and a table to use in this tutorial.
  1. To connect to your MySQL server, execute the following command from a system that has a MySQL client installed:
    mysql --host=MySQL server host name or IP address --port=MySQL server port number -uusername -ppassword
    In this example, replace:
    • MySQL server host name or IP address with the name or IP address of your MySQL server.
    • MySQL server port number with the port number for your MySQL server.
    • username with the username for your MySQL server.
    • password with the password for your MySQL server.
  2. To create a MySQL database to use in this tutorial, execute the following command from your MySQL client:
    CREATE DATABASE tutorialDB;
  3. To create a table to use in this tutorial, execute the following command from your MySQL client:
    create table employee
      (
      employee_id int auto_increment primary key,
      employee_first_name varchar(500) NOT null,
      employee_last_name varchar(500) NOT null,
      employee_emailID varchar(500)
      ); 
  4. To add rows to the employee table that you just created, execute the following command from your MySQL client:
    INSERT INTO employee (employee_first_name,employee_last_name,employee_emailID) values ("Peter","Dilliard","test-01@test.com");
    INSERT INTO employee (employee_first_name,employee_last_name,employee_emailID) values ("Meaghan","Webb","test-02@test.com");
    
  5. Verify that the table is created and rows are added by executing the following command:
    SELECT * FROM employee;
    The following table rows are displayed:
    +-------------+---------------------+--------------------+------------------+
    | employee_id | employee_first_name | employee_last_name | employee_emailID |
    +-------------+---------------------+--------------------+------------------+
    |           1 | Peter               | Dilliard           | test-01@test.com |
    |           2 | Meaghan             | Webb               | test-02@test.com |
    +-------------+---------------------+--------------------+------------------+
    

Create a MySQL connection

To allow an integration to connect to your MySQL database, create a new connection to your MySQL database.

  1. In the Cloud console page, select or create a Google Cloud project.
  2. Open the connections page .
  3. Click + CREATE NEW to open the Create Connection page.
  4. Configure the connection:
    1. In the Create Connection section, complete the following:
      • Connector: Select MySQL from the drop down list of available Connectors.
      • Connector version: Select the Connector version from the drop down list of available versions.
      • In the Connection Name field, enter a name for the Connection instance.

        Connection names must meet the following criteria:

        • Connection names can use letters, numbers, or hyphens.
        • Letters must be lower-case.
        • Connection names must begin with a letter and end with a letter or number.
        • Connection names cannot exceed 63 characters.
      • Optionally, add a Description of the connection instance.
      • Service Account: Select a service account that has the required roles.
      • Hostname: Enter the hostname of the MySQL server.
      • Port: Enter the port number on which the MySQL server is running.
      • Optionally, click + ADD LABEL to add a label to the Connection in the form of a key/value pair.
      • Click NEXT.
    2. Destinations: Enter details of the remote host (backend system) you want to connect.
      • Destination Type: You can specify the destination details either as a host address or a service attachment. Select any of the following destination types:
        • Host address: If you want to specify the hostname or IP address of the destination.
        • Service attachment: If you want to specify the private endpoint for your internal IP addresses. The service attachment, lets you hide your internal IP addresses from the external network. You can create a service attachment in Google Cloud, by using the Private Service Connect feature. For information on configuring a Private Service Connect, see Publish managed services.

        Based on your selection of destination type, enter the host address or the service attachment name.

      • To enter additional destinations, click +ADD DESTINATION.
      • Click NEXT.
    3. Location: Select a region from where the connection will run. Supported regions for connectors include:
        • asia-east1
        • asia-northeast1
        • asia-south1
        • asia-southeast1
        • australia-southeast1
        • europe-west1
        • europe-west2
        • europe-west3
        • europe-west4
        • europe-west6
        • northamerica-northeast1
        • southamerica-east1
        • us-central1
        • us-east1
        • us-east4
        • us-west1
        • us-west2
      • Click NEXT.
    4. Authentication: For MySQL connections, you can provide credentials in the form of a User and Password combination. To configure authentication using a User and Password combination, enter the following information:
      • Username: Enter the MySQL username for the connection.
      • Password: Enter the Secret Manager secret containing the password associated with the MySQL username.
        • If you have previously created a secret, and it is not available in the drop down list, select DON'T SEE YOUR SECRET? ENTER SECRET RESOURCE ID. In the Add a secret by resource ID dialog, copy and paste the resource ID from the Secret Manager.
          • To use the latest version, copy and paste the resource ID from the parent secret, in the format: "projects/project-number/secrets/secret-name"
          • To select a specific version, copy and paste the resource ID for that specific version, in the format "projects/project-number/secrets/secret-name/versions/1"

          Click ADD SECRET to add the secret and close the dialog.

        • If you have not created a secret for use with MySQL, click ADD A NEW SECRET. In the Create Secret dialog enter the following details:
          • Name: Enter the secret name.
          • Secret value: Enter the contents of the secret.
          • Optionally, click + ADD LABEL to add a label in the form of a key/value pair.
      • Secret version: Select the version of the Password secret from the list of available versions in the drop-down.
      • Click NEXT.
    5. Review: Review your connection's configuration details. In this section, the Connection details and Authentication details of the new connection are displayed for your review.
  5. Click Create.

Configure an Integration to use the MySQL connection

To use the MySQL connection that you just created in an integration, add a Connectors task in an integration along with an API Trigger. The API Trigger is connected to the Connectors task using an Edge connection.

Create a new integration

  1. In the Google Cloud console, go to the Application Integration page.

    Go to Application Integration

  2. In the navigation menu, click Integrations.

    The Integrations List page appears.

  3. Click Create integration.
  4. Enter a name and (optionally) a description in the Create Integration dialog.
  5. Select a Region for the integration from the list of supported regions.
  6. Click Create to open the integration designer.

Add and configure an API trigger

To add and configure an API trigger to the integration, perform the following steps:

  1. In the integration designer, select Add a task/trigger > Triggers to display a list of available triggers.
  2. Drag the API Trigger element to the integration designer.

Add and configure a Connectors task

Perform the following steps to configure a Connectors task to list all the entities in the employee table:

  1. Select +Add a task/trigger > Tasks in the integration designer to display the list of available tasks.
  2. Drag the Connectors element to the integration designer.
  3. Click the Connectors task element on the designer to view the task configuration pane.
  4. Click Configure task.

    The Configure connector task dialog appears.

  5. In the Configure connector task dialog, perform the following steps:
    1. Select the connection region where you created your MySQL connection.
    2. Once a region is chosen, the Connection column appears. Select the MySQL connection that you just created from the list of available connections.
    3. Once a connection is chosen, the Type column appears. Select Entities and then employee from the list of available entities.
    4. Once a type is chosen, the Operation column appears. Select List.
    5. Click Done to complete the connection configuration and close the dialog.

Connect the API Trigger element to the Connectors task element

Next, add an edge connection to connect the API trigger to the Connectors task. An edge connection is a connection between any two elements in an integration. For more information on edges and edge conditions, see Edges.

To add the edge connection, click the Fork control point at the bottom of the API Trigger element. Drag and drop the edge connection at the Join control point at the top of the Connectors task element.

Test the Integration

To test the integration, perform the following steps:

  1. Click the Test button in the integration designer toolbar.
  2. Change the Execution Deadline (in minutes) value, if you like, and then click Test Integration.
  3. Once the integration completes successfully, the Test Integration dialog displays the message Integration execution succeeded. To view the logs, click View logs.
  4. In Response Parameters, under Connector output payload, the following output is displayed:
    [ {
        "employee_id": 1.0,
        "employee_first_name": "Peter",
        "employee_last_name": "Dilliard",
        "employee_emailID": "test-01@test.com"
      },
      {
        "employee_id": 2.0,
        "employee_first_name": "Meaghan",
        "employee_last_name": "Webb",
        "employee_emailID": "test-02@test.com"
      } ]

Perform other operations on your MySQL database table

When you configure a Connectors task in your integration, you can select any one of the following operations:

  • List
  • Get
  • Create
  • Update
  • Delete

You've already used the List operation to view all the rows in the employee table. In the following sections of this tutorial, you'll use the Get, Create, Update, and Delete operations to add, modify or remove rows from the employee table.

Get a row from a table

If you know the entity ID (or primary key) of the row that you want to fetch, provide that value as input to a Get operation in your integration. The details returned by the Get operation are similar to the details returned by the List operation. However, while the List operation fetches details of all the table rows that match the specified query, the Get operation fetches details of only the table row that matches the specified entity ID.

Remember that, while the List operation by default returns all the rows in the table, the Get operation requires an entity ID as a search parameter. So, to use the Get operation, you must know the entity ID of the row that you want to fetch, or provide a default value for the entity ID.

To get details of a specified row, perform the following steps to configure the Connectors task that you created earlier:

  1. Click the Connectors task element on the designer to view the task configuration pane.
  2. Click Configure task.

    The Configure connector task dialog appears.

  3. In the Configure connector task dialog, in the Operation column, select Get and then click Done.
  4. In the task configuration pane, under Task Input click Entity ID.
  5. In the Configure Variable dialog, select Use as an input to integration and click Save.
  6. Click the Test button in the integration designer toolbar.
  7. Change the Execution Deadline (in minutes) value, if you like.
  8. Enter the entity ID of the entity for which you want to fetch details. Enter 2.
  9. Click Test Integration.
  10. Once the integration completes successfully, the Test Integration dialog displays the message Integration execution succeeded. To view the logs, click View logs.
  11. In Response Parameters, under Connector output payload, the following output is displayed:
    [ {
        "employee_id": 2.0,
        "employee_first_name": "Meaghan",
        "employee_last_name": "Webb",
        "employee_emailID": "test-02@test.com"
      } ]
  12. You can verify that the information displayed here matches the information in the corresponding row in your MySQL table. To view this information in your MySQL table, execute the following command from your MySQL client:
    SELECT * FROM employee WHERE employee_id=2;
    The following table row is displayed:
    +-------------+---------------------+--------------------+------------------+
    | employee_id | employee_first_name | employee_last_name | employee_emailID |
    +-------------+---------------------+--------------------+------------------+
    |           2 | Meaghan             | Webb               | test-02@test.com |
    +-------------+---------------------+--------------------+------------------+
        

Add a row to a table

The Create operation allows you to add a row in a table. When you use the Create operation, you must provide all the values for the entity in the connector input payload.

To add a row using the Create operation, perform the following steps to configure the Connectors task that you created earlier:

  1. Click the Connectors task element on the designer to view the task configuration pane.
  2. Click Configure task.

    The Configure connector task dialog appears.

  3. In the Configure connector task dialog, in the Operation column, select Create and then click Done.
  4. In the task configuration pane, under Task Input click Connector input payload.
  5. In the Configure Variable dialog, select Use as an input to integration and click Save.
  6. Click the Test button in the integration designer toolbar.
  7. Change the Execution Deadline (in minutes) value, if you like.
  8. Enter the details for the entity that you want to create. For example, to add a new employee in the employee table, enter the following JSON:
    {
      "employee_first_name": "Mary",
      "employee_last_name": "Smith",
      "employee_emailID": "test-03@test.com"
    }
  9. Click Test Integration.
  10. Once the integration completes successfully, the Test Integration dialog displays the message Integration execution succeeded. To view the logs, click View logs.
  11. In Response Parameters, under Connector input payload, the payload that you provided is displayed:
    {
      "employee_first_name": "Mary",
      "employee_last_name": "Smith",
      "employee_emailID": "test-03@test.com"
    }
    Under Connector output payload, the entity ID is displayed as output:
    {
      "employee_id": 3.0
    }
  12. You can verify that the row is added in your MySQL table. To view this information in your MySQL table, execute the following command from your MySQL client:
    SELECT * FROM employee;
    The following table rows are displayed:
    +-------------+---------------------+--------------------+------------------+
    | employee_id | employee_first_name | employee_last_name | employee_emailID |
    +-------------+---------------------+--------------------+------------------+
    |           1 | Peter               | Dilliard           | test-01@test.com |
    |           2 | Meaghan             | Webb               | test-02@test.com |
    |           3 | Mary                | Smith              | test-03@test.com |
    +-------------+---------------------+--------------------+------------------+
        

Update a row in a table

Use the Update operation to make changes to the values in a table row. For example, you can use this operation to update the email ID of an employee in the employee table. To specify the entity that you want to update, you can provide the entity ID as you did for the Get operation. Alternatively, you can use the Filter clause parameter to pass values on which to filter the table rows. This is useful if you want to make the same change in multiple rows based on specific search criteria.

To update a table row, perform the following steps to configure the Connectors task that you created earlier:

  1. Click the Connectors task element on the designer to view the task configuration pane.
  2. Click Configure task.

    The Configure connector task dialog appears.

  3. In the Configure connector task dialog, in the Operation column, select Update and then click Done.
  4. In the task configuration pane, under Task Input click Connector input payload.
  5. In the Configure Variable dialog, select Use as an input to integration and click Save.
  6. Next, in the task configuration pane, under Task Input click Filter clause.
  7. In the Configure Variable dialog, select Use as an input to integration and click Save.
  8. Click the Test button in the integration designer toolbar.
  9. Change the Execution Deadline (in minutes) value, if you like.
  10. Enter the criteria that you want to filter by. For example, to find an employee with the email ID test-03@test.com, enter:
    employee_emailID="test-03@test.com"
  11. Enter the values that you want to update. For example, to update the email ID of all the employees in the employee table whose current email ID matches the filter clause test-03@test.com, enter the following JSON:
    {
      "employee_emailID": "msmith@test.com"
    }
  12. Click Test Integration.
  13. Once the integration completes successfully, the Test Integration dialog displays the message Integration execution succeeded. To validate that the entity was updated, use the Get operation to get the details of the specified entity.
  14. You can verify that the row is updated in your MySQL table. To view this information in your MySQL table, execute the following command from your MySQL client:
    SELECT * FROM employee;
    The following table rows are displayed:
    +-------------+---------------------+--------------------+------------------+
    | employee_id | employee_first_name | employee_last_name | employee_emailID |
    +-------------+---------------------+--------------------+------------------+
    |           1 | Peter               | Dilliard           | test-01@test.com |
    |           2 | Meaghan             | Webb               | test-02@test.com |
    |           3 | Mary                | Smith              | msmith@test.com  |
    +-------------+---------------------+--------------------+------------------+
          

Delete a row in a table

You can use the Delete operation to delete one or more table rows. Provide the entity ID or use the filter clause to specify the rows that you want to delete. Remember that, if you use the filter clause to specify criteria for rows that you want to delete, it's possible to delete multiple rows that match the given filter. If you want to delete only one specific row, use the entity ID.

To delete a table row using the entity ID, perform the following steps to configure the Connectors task that you created earlier:

  1. Click the Connectors task element on the designer to view the task configuration pane.
  2. Click Configure task.

    The Configure connector task dialog appears.

  3. In the Configure connector task dialog, in the Operation column, select Delete and then click Done.
  4. In the task configuration pane, under Task Input click Entity ID.
  5. In the Configure Variable dialog, select Use as an input to integration and click Save.
  6. Click the Test button in the integration designer toolbar.
  7. Change the Execution Deadline (in minutes) value, if you like.
  8. Enter the entity ID of the table row that you want to delete. Enter 3.
  9. Click Test Integration.
  10. Once the integration completes successfully, the Test Integration dialog displays the message Integration execution succeeded. To validate that the table row was deleted, use the Get operation to get the details of the row that you just deleted. This should return an error for the specified entity ID.

    You can also use the List operation and optionally provide a filter clause for the table row (or rows) that you just deleted. This returns the list of rows remaining, which could be an empty list.

    For example, if you deleted the table row with entity ID 3, then if you specify the filter clause employee_emailID="msmith@test.com" or the entity ID 3 for the List operation, the response parameters in the logs display [].

  11. You can verify that the row is deleted in your MySQL table. To verify this in your MySQL table, execute the following command from your MySQL client:
    SELECT * FROM employee;
    The following table rows are displayed:
    +-------------+---------------------+--------------------+------------------+
    | employee_id | employee_first_name | employee_last_name | employee_emailID |
    +-------------+---------------------+--------------------+------------------+
    |           1 | Peter               | Dilliard           | test-01@test.com |
    |           2 | Meaghan             | Webb               | test-02@test.com |
    +-------------+---------------------+--------------------+------------------+
            

What's next

Try building integrations with other connectors. For the list of all the supported connectors, see Connector reference.