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.
MindsDB doesn’t store or copy your data. Instead, it fetches data directly from your connected sources each time you make a query, ensuring that any changes to the data are instantly reflected. This means your data remains in its original location, and MindsDB always works with the most up-to-date information.
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.
Was this page helpful?