Use a local database as a source or destination

If you want to use a database running in your local environment as a data source or destination for synthetic data, you can use ngrok to expose the local database for external connections over the Internet and then add the ngrok forwarding address and port in a new MOSTLY AI connector.

Task 1: Install ngrok

ngrok (opens in a new tab) makes it possible to expose a locally running server to the Internet. To do so, you need an ngrok account and an ngrok authentication token.

Steps

1. Go to https://dashboard.ngrok.com/signup (opens in a new tab) and sign up for an ngrok account.

2. Install ngrok with the steps for your operating system from https://dashboard.ngrok.com/get-started/setup (opens in a new tab).

3. Copy your authentication token from https://dashboard.ngrok.com/get-started/your-authtoken (opens in a new tab).

   

4. Add the authentication token to your local environment.

   ngrok config add-authtoken TOKEN

Result

ngrok can now use the token to connect to your account.

Task 2: Run a local database

With Docker Compose (opens in a new tab), you can quickly run a database locally as a Docker container just by defining a new service in a YAML file.

The steps below show how to run a PostgreSQL database.

Steps

1. Create a new YAML file with the name mostly-ai-postgresql-compose.yaml and paste the contents below.
   mostly-ai-postgresql-compose.yaml

   version: '3.7'
   services:
       mostly_ai_sd_db:
           image: postgres
           environment:
               POSTGRES_USER: postgres
               POSTGRES_PASSWORD: changeme
               POSTGRES_DB: my_synthetic_db
           ports:
               - "5432:5432"
           volumes:
               - mostly_ai_sd_db:/var/lib/postgresql/data
   volumes:
       mostly_ai_sd_db:
2. Set the credentials and database name in the lines highlighted below.
   mostly-ai-postgresql-compose.yaml

   ...
               POSTGRES_USER: postgres
               POSTGRES_PASSWORD: changeme
               POSTGRES_DB: my_synthetic_db
   ...
3. Run docker compose to start the service defined in the YAML file.

   docker compose -f ./mostly-ai-postgresql-compose.yaml logs

Result

The PostgreSQL database is now running locally.

Task 3: Expose your database with ngrok

To expose your database from your local environment, you need to know the port at which the database service is listening. Check your database configuration for that. For example, the default port for PostgreSQL is 5432, for MySQL - 3306.

Steps

1. Run ngrok to expose your database port.

   ngrok tcp PORT
   Step result: ngrok randomly assigns a hostname and a forwarding port for your database.
2. Copy the forwarding address that is listed in the ngrok command status.

Task 4: Create a connector

You can use your local database as a data source, data destination, or both. In either case, you need a separate connector (a source or a destination) in MOSTLY AI.

Steps

1. From the Connectors page, click + New connector.
2. Select the connector type for your local database.
3. Provide the connector details.
   1. For Access type, select Source or Destination.
   2. For Host, paste the ngrok forwarding address.
   3. For Port, paste the ngrok forwarding port number.
      💡

      You can find the forwarding address and port in the output of the ngrok command from Task 3.

   4. For Username, Password, and Database, set the database credentials and the database name.
      💡

      If you plan to use the same database as a source and destination, create a separate database for the destination connector to prevent potential loss of original data.

      
4. Click Save.

What's next

To use the local database as a source, see Use as a data source.

To use the local database as a destination, see Use as a data destination.