Connect a Data Source

Description

MindsDB lets you connect to your favorite databases, data warehouses, data lakes, etc., via the CREATE DATABASE command.

The MindsDB SQL API supports creating connections to integrations by passing the connection parameters specific per integration. You can find more in the Supported Integrations chapter.

Syntax

Let’s review the syntax for the CREATE DATABASE command.

CREATE DATABASE [IF NOT EXISTS] datasource_name
[WITH] [ENGINE [=] engine_name] [,]
[PARAMETERS [=] {
  "key": "value",
  ...
}];

On execution, we get:

Query OK, 0 rows affected (x.xxx sec)

Where:

Name	Description
datasource_name	Identifier for the data source to be created.
engine_name	Engine to be selected depending on the database connection.
PARAMETERS	{"key": "value"} object with the connection parameters specific for each engine.

SQL Commands Resulting in the Same Output Please note that the keywords/statements enclosed within square brackets are optional. Also, by default, the engine is mindsdb if not provided otherwise. That yields the following SQL commands to result in the same output.

CREATE DATABASE db;
CREATE DATABASE db ENGINE 'mindsdb';
CREATE DATABASE db ENGINE = 'mindsdb';
CREATE DATABASE db WITH ENGINE 'mindsdb';
CREATE DATABASE db USING ENGINE = 'mindsdb';

What’s available on your installation

Here is how you can query for all the available data handlers used to create database connections.

SELECT *
FROM information_schema.handlers
WHERE type = 'data';

Or, alternatively:

SHOW HANDLERS
WHERE type = 'data';

And here is how you can query for all the connected databases:

SELECT *
FROM information_schema.databases;

Or, alternatively:

SHOW DATABASES;

SHOW FULL DATABASES;

Example

Connecting a Data Source

Here is an example of how to connect to a MySQL database.

CREATE DATABASE mysql_datasource
WITH ENGINE = 'mariadb',
PARAMETERS = {
  "user": "root",
  "port": 3307,
  "password": "password",
  "host": "127.0.0.1",
  "database": "my_database"
};

On execution, we get:

Query OK, 0 rows affected (8.878 sec)

Listing Linked Databases

You can list all the linked databases using the command below.

SHOW DATABASES;

On execution, we get:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mindsdb            |
| files              |
| mysql_datasource   |
+--------------------+

Making your Local Database Available to MindsDB

When connecting your local database to MindsDB Cloud, you should expose the local database server to be publicly accessible. It is easy to accomplish using Ngrok Tunnel. The free tier offers all you need to get started.

The installation instructions are easy to follow. Head over to the downloads page and choose your operating system. Follow the instructions for installation.

Then create a free account at Ngrok to get an auth token that you can use to configure your Ngrok instance.

Once installed and configured, run the following command to obtain the host and port for your localhost at port-number.

ngrok tcp port-number

Here is an example. Assuming that you run a PostgreSQL database at localhost:5432, use the following command:

ngrok tcp 5432

On execution, we get:

Session Status                online
Account                       myaccount (Plan: Free)
Version                       2.3.40
Region                        United States (us)
Web Interface                 http://127.0.0.1:4040
Forwarding                    tcp://4.tcp.ngrok.io:15093 -> localhost 5432

Now you can access your local database at 4.tcp.ngrok.io:15093 instead of localhost:5432.

So to connect your local database to the MindsDB GUI, use the Forwarding information. The host is 4.tcp.ngrok.io, and the port is 15093.

Proceed to create a database connection in the MindsDB GUI by executing the CREATE DATABASE statement with the host and port number obtained from Ngrok.

CREATE DATABASE psql_datasource
WITH ENGINE = 'postgres',
PARAMETERS = {
  "user": "postgres",
  "port": 15093,
  "password": "password",
  "host": "4.tcp.ngrok.io",
  "database": "postgres"
};

Please note that the Ngrok tunnel loses connection when stopped or canceled. To reconnect your local database to MindsDB, you should create an Ngrok tunnel again. In the free tier, Ngrok changes the host and port values each time you launch the program, so you need to reconnect your database in the MindsDB Cloud by passing the new host and port values obtained from Ngrok.

Before resetting the database connection, drop the previously connected data source using the DROP DATABASE statement.

DROP DATABASE psql_datasource;

After dropping the data source and reconnecting your local database, you can use the predictors that you trained using the previously connected data source. However, if you have to RETRAIN your predictors, please ensure the database connection has the same name you used when creating the predictor to avoid failing to retrain.

Supported Integrations

The list of databases supported by MindsDB keeps growing. Check out all our database integrations here.