Using PostgreSQL with `ngrok`

Test your API locally using ngrok and a PostgreSQL database.

Before you begin

Install ngrok on your machine, using the appropriate tab for your operating system.

About this task

The following steps are for using PostgreSQL on Mac. If you are using Windows, Chocolatey follows a similar installation method for PostgreSQL.

Procedure

Install PostgreSQL.

If you already have a PostgreSQL database running locally, skip to the next step.
1. (Optional) Install pgAdmin if you want to see your database outside of the CLI.
2. Install PostgreSQL using Homebrew:
```
brew install postgresql
```
3. Start the PostgreSQL server:
```
brew services start postgresql
```
4. Install psql so you can run PostgreSQL commands:
```
brew link --force libpq
```
5. Verify that PostgreSQL is installed:
```
psql --version
psql (PostgreSQL) 13.3
```
6. Access the PostgreSQL server:
```
psql postgres
```
7. Create a table in the new database by pasting the following content on the command line:
```
postgres-# 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.

Run the following command to determine the port that the PostgreSQL server is listening on:
```
postgres=# \conninfo
You are connected to database "postgres" as user "testuser" via socket in "/tmp" at port "5432".
```
In the example, the port is 5432.

Run the following command, changing the port to the value that you determine in the previous step:

ngrok tcp 5432 // change 5432 to your port number

The command generates the TCP endpoint that you will use later in the config.yaml 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:5432                                                                                                                                                                                    

Connections           ttl     opn     rt1     rt5     p50     p90                                                                                                                                                                                     
                      0       0       0.00    0.00    0.00    0.00

Connect PostgreSQL to API Connect Essentials

From the information collected earlier with 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.
1. Create the config.yaml configuration file with the following content:
```
configurationset:
  - configuration:
      name: pg_config
      uri: postgresql://username:password@2.tcp.ngrok.io:1234/postgres
```
  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.
  - uri contains your database user name and password, the TCP endpoint that was created when you ran the ngrok tcp 5432 command in the previous step, and the database name using the following format:
```
uri: postgresql://<db_user>:<db_password>@<tcp_endpoint>/<db_name>
```
    In the sample configuration file:
    uri: postgresql://your_username:your_password@2.tcp.ngrok.io:1234/postgres
    
    <db_user> is your_username
    
    <db_password> is your_assword
    
    <tcp_endpoint> is 2.tcp.ngrok.io:1234
    
    <db_name> is postgres
2. List the tables in the database by running the following command:
```
postgres=# \c postgres
You are now connected to database "postgres" as user "your_username".
postgres=# \dt
           List of relations
 Schema |  Name   | Type  |   Owner    
--------+---------+-------+------------
 public | weather | table | root
(1 row)
```
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: "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"
    )
}
```

Using PostgreSQL with ngrok

Before you begin

About this task

Procedure

Using PostgreSQL with `ngrok`