Using MySQL with ngrok

Test your API locally using ngrok and a MySQL database.

1. Install MySQL.

   If you already have a MySQL database running locally, skip to the next step.

   1. Install MySQL:

      brew install mysql

   2. Verify that MySQL installed successfully:

      mysql --version
      mysql  Ver 8.0.25 for macos10.15 on x86_64 (Homebrew)

   3. Start the MySQL server:

      brew services start mysql

   4. Access the MySQL server:

      mysql -u root -p password

   5. Create a new database and user:

      mysql> create database tutorialdb;
      Query OK, 1 row affected (0.00 sec)
      
      mysql> create user 'tutorialuser'@'localhost' identified by 'tutorialuserpassword';
      Query OK, 0 rows affected (0.01 sec)
      
      mysql> grant all on tutorialdb.* to 'tutorialuser'@'localhost';
      Query OK, 0 rows affected (0.01 sec)

   6. Exit the MySQL server and log in as the new user:

      mysql> exit
      
      desktop % mysql -u tutorialuser -p tutorialdb
      Enter password: ************ // tutorialuserpassword

   7. Create a table in the new database:

      mysql> CREATE TABLE weather (
          city            varchar(80),
          temp_lo         int,           -- low temperature
          temp_hi         int,           -- high temperature
          prcp            real,          -- precipitation
          date            date
      );

2. Point ngrok to your local MySQL server.
   1. Determine where your MySQL database is running locally.

      There are many different ways to check where a program is running on a local machine; for example, by using grep or netstat.

   2. Determine which port number MySQL is using:

      mysql> SHOW VARIABLES WHERE Variable_name = 'port';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | port          | 3306  |
      +---------------+-------+
      1 row in set (0.00 sec)

      The following are alternative commands to check for port on a local machine:

      mysql> SHOW GLOBAL VARIABLES LIKE 'port';

      $ netstat -tlnp

   3. Set the port for ngrok to MySQL's port number (for example, port 3306):

      ngrok tcp 3306 // change 3306 to your port number

      This command generates the TCP endpoint that you will use in the config.yaml configuration file:

      
      Session Status        online                                                                                                                                                                                                                          
      Account                Test Account                                                                                                                                                                                                         
      Version                2.3.40                                                                                                                                                                                                                          
      Region                 United States (us)                                                                                                                                                                                                              
      Web Interface          http://127.0.0.1:1234                                                                                                                                                                                                           
      Forwarding             tcp://2.tcp.ngrok.io:1234 -> localhost:3306                                                                                                                                                                                    
      
      Connections            ttl     opn     rt1     rt5     p50     p90                                                                                                                                                                                     
                             0       0       0.00    0.00    0.00    0.00 

3. Connect MySQL to API Connect Essentials

   From the information collected earlier with the \port and ngrok tcp 3306 commands, add the MySQL TCP endpoint along with the username and password from the database, to config.yaml for the schema.

   1. Create the config.yaml configuration file with the following content:

      configurationset:
          - configuration:
            name: mysql_config
            dsn: tutorialuser:tutorialuserpassword@tcp(2.tcp.ngrok.io:1234)/tutorialdb

      where:

      • name is a name for this configuration. If you are adding this configuration to an existing GraphQL schema, change the name of the configuration to match the name in your schema.
      • dsn contains your database user name and password, the TCP endpoint that was created when you ran the ngrok tcp 3306 command in the previous step, and the database name using the following format:

        dsn: <db_user>:<db_password>@tcp(<tcp_endpoint>)/<db_name>

        In the sample configuration file:

        dsn: tutorialuser:tutorialuserpassword@tcp(2.tcp.ngrok.io:1234)/tutorialdb

        • <db_user> is tutorialuser
        • <db_password> is tutorialuserpassword
        • <tcp_endpoint> is 2.tcp.ngrok.io:1234
        • <db_name> is tutorialdb
   2. List the tables in the database by running the following command:

      mysql> show tables;
      +----------------------+
      | Tables_in_tutorialdb |
      +----------------------+
      | weather              |
      +----------------------+
      1 row in set (0.00 sec)

      In this example, there is one table called "weather". You created the table earlier in this example:

      mysql> CREATE TABLE weather (
          city            varchar(80),
          temp_lo         int,           -- low temperature
          temp_hi         int,           -- high temperature
          prcp            real,          -- precipitation
          date            date
      );

   3. Create the following GraphQL schema to execute one mutation and one query to the tutorialdb database:
      • The createWeather mutation creates new rows in the weather table.
      • The weather query retrieves all of the existing rows in the weather table.

      type Weather {
        city: String
        temp_lo: Int
        temp_hi: Int
        prcp: Float
        date: Date
      }
      
      type Query {
        weather: [Weather]
          @dbquery(
            type: "mysql"
            query: "select * from weather"
            configuration: "mysql_config"
          )
      }
      
      type Mutation {
        createWeather(city: String!, temp_lo: Int!, temp_hi: Int!, prcp: Float!, date: Date!): Weather
          @dbquery(
            type: "mysql"
            table: "weather"
            dml: INSERT
            configuration: "mysql_config"
          )
      }