Test Locally with ngrok

Add ngrok to Your Local Database for the @dbquery Directive

Install ngrok on your machine by following the instructions in the ngrok dashboard, or run the following brew installation command:

brew cask install ngrok

If you choose to use the brew command, there is a forum here, that can support you with any errors you may run into while installing it.

Create the StepZen Schema

If you already have a StepZen schema, skip to PostgreSQL or MySQL, depending on the database you are testing.

Complete the following steps to create a StepZen schema:

1. Generate the StepZen schema:

   Mac

   mkdir StepZen;
   cd StepZen;
   touch config.yaml;
   touch postgres.graphql;
   touch index.graphql
   

   Windows

   mkdir StepZen;
   cd StepZen;
   type nul >> "config.yaml";
   type nul >> "postgres.graphql";
   type nul >> "index.graphql"
   

2. Open index.graphql in your code editor, and add database.graphql to your GraphQL Schema Definition Language (SDL):

   schema
     @sdl(
       files: [
         "database.graphql"
       ]
     ) {
     query: Query
   }
   

Install PostgreSQL

Complete the following steps to install PostgreSQL locally and test it using ngrok:

Note:

• Ensure you have ngrok installed on your machine before continuing.
• If you already have a PostgreSQL database running locally, skip to Point ngrok to your Local PostgreSQL Server.
• The following steps are for macOS. If you are using Windows, Chocolatey follows a similar installation method.

1. (Optional) Install pgAdmin if you want to see your database outside of the CLI.

2. Install PostgreSQL on macOS using HomeBrew:

   brew install postgresql
   

3. Start the PostgreSQL server:

   brew services start postgresql
   

4. Install psql to run PostgreSQL commands:

   brew link --force libpq
   

5. Verify that PostgreSQL is installed:

   Your-MacBook-Pro:sz-ngrok yourname$ psql --version
   psql (PostgreSQL) 13.3
   

6. Access your PostgreSQL server in the CLI:

   psql postgres
   

7. Create a table in the database by pasting the following in front of postgres-#, which is now running in your command line:

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

Point ngrok to your Local PostgreSQL Server

The PostgreSQL server is now running locally in your CLI.

Complete the following steps to point ngrok to your local PostgreSQL server:

1. Run \conninfo in the PostgreSQL server to obtain details of the localhost session:

   postgres=# \conninfo
   You are connected to database "postgres" as user "testuser" via socket in "/tmp" at port "5432".
   

2. Run the following command, changing the port to your unique generated port number. The PostgreSQL example above generated port 5432:

   ngrok tcp 5432 // change 5432 to your port number
   

   This command generates the TCP endpoint you will use later in config.yaml:

   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:5432                                                                                                                                                                                    
                                                                                                                                                                                                                                                             
   Connections                   ttl     opn     rt1     rt5     p50     p90                                                                                                                                                                                     
                                 0       0       0.00    0.00    0.00    0.00 
   

Connect PostgreSQL to StepZen

From the information collected earlier via the \conninfo and ngrok tcp 5432 commands, add the PostgreSQL TCP endpoint, along with the username and password from the database, to config.yaml for the schema.

Complete the following steps to connect PostgreSQL to StepZen:

1. Copy the following configuration and add in your unique username, password, and TCP endpoint. If you are adding this configuration to an existing GraphQL schema, change the name of the configuration to match the name in your schema:

   configurationset:
     - configuration:
         name: pg_config
         uri: postgresql://username:password@2.tcp.ngrok.io:1234/postgres
   

   The endpoint in this uri configuration was generated by the ngrok command (ngrok tcp 5432) you ran earlier.

   The username and password in the URI are the same login credentials you use to access the database.

   In this example, the following endpoint is generated by ngrok:

   2.tcp.ngrok.io:1234
   

2. Append the database name to this TCP endpoint. In this example, the database is postgres:

   2.tcp.ngrok.io:1234/postgres
   

   With config.yaml properly configured, open the GraphQL schema file (e.g. database.graphql). In this example schema, there is one table in the database named weather:

   postgres=# \c postgres
   You are now connected to database "postgres" as user "root".
   postgres=# \dt
              List of relations
    Schema |  Name   | Type  |   Owner    
   --------+---------+-------+------------
    public | weather | table | root
   (1 row)
   

The following GraphQL schema executes one mutation and one query to the PostgreSQL database:

1. The createWeather mutation creates new rows in the weather table.

2. 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: "postgresql"
         query: "select * from weather"
         configuration: "pg_config"
       )
   }
   
   type Mutation {
     createWeather(city: String!, temp_lo: Int!, temp_hi: Int!, prcp: Float!, date: Date!): Weather
       @dbquery(
         type: "postgresql"
         table: "weather"
         dml: INSERT
         configuration: "pg_config"
       )
   }
   

Install MySQL

Complete the following steps to install MySQL locally and test it using ngrok:

Notes:

• If you already have a MySQL database running locally, skip to Point ngrok to your Local MySQL Server.
• The following steps are for macOS. If you are using Windows, Chocolatey follows a similar installation method.

1. Install MySQL:

   brew install mysql
   

2. Verify that MySQL is installed:

   Your-MacBook-Pro:sz-ngrok username$ mysql --version
   mysql  Ver 8.0.25 for macos10.15 on x86_64 (Homebrew)
   

3. Start MySQL:

   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 created in the previous step (e.g. tutorialuser:tutorialuserpassword):

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

7. Create a table in the database:

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

Point ngrok to your Local MySQL Server

Complete the following steps to 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 by using grep, netstat, etc.

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. The MySQL example from above uses port 3306:

   ngrok tcp 3306 // change 3306 to your port number
   

   This command generates the TCP endpoint you will use later in config.yaml:

   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 
   

Connect MySQL to StepZen

From the information collected earlier via 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.

Complete the following steps to connect MySQL to StepZen:

1. Copy the following configuration and add in your unique username, password, and TCP endpoint. If you are adding this configuration to an existing GraphQL schema, change the name of the configuration to match the name in your schema:

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

   The endpoint in this dsn configuration was generated by the ngrok command (ngrok tcp 3306) you ran earlier.

   The username and password are the same login credentials you use to access your database.

   In this example, the following endpoint is generated by ngrok:

   2.tcp.ngrok.io:1234
   

2. Append the database name to this TCP endpoint. In this example, the database is tutorialdb:

   2.tcp.ngrok.io:1234/tutorialdb
   

   With config.yaml properly configured, open the GraphQL schema file (e.g. database.graphql). In this example, there is one table in my database named weather:

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

The following GraphQL schema executes one mutation and one query to the tutorialdb database:

1. The createWeather mutation creates new rows in the weather table.
2. 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"
    )
}