Cart

Open-Source Database Connections

Table of Contents

Open-source databases are from the families of MySQL (MariaDB) and PostgreSQL (including Heroku). They come with 7-day free trial for full access to anything. Then you get limited visual SQL query design and SQL generation, unless you pay for a small monthly subscription.

The process described here is for Data Xtractor, but it’s valid for all our products as well, with few exceptions: ER models are supported and generated only in Data Xtractor and Model Xtractor, while queries are not supported or generated in Model Xtractor.

How to Connect to a MySQL Database

We support MySQL versions from 5.5 up. Your database must have been previously installed with a third-party tool. We recommend the free MySQL Workbench, included in the MySQL distribution kit.

MySQL-Chinook-Connection

  1. Click on Connect > Open-Source Databases > MySQL. This will open the previous New Database Connection popup. Return here anytime with the Properties context menu command on your database node.
  2. No need to install any data provider. MySQL Data Provider was included in the setup
  3. Enter your database Host Name and Port Number. Type localhost or your machine name for a local database. Default port number in MySQL is 3306. Beware MariaDB is using the same number, so you may have to change it if you host both on the same computer.
  4. Connect with your User Name and Password. Leave Save Password checked. Admin’s user name in MySQL is root by default.
  5. Connect through SSH if required. Follow the steps described separately below for SSH and come back to continue.
  6. Select the list entry with your Database name. It should appear in the Database list when expanded, if previous parameters are correct. Or type the database name manually.
  7. Leave Schema(s) blank. Or select the same schema name as your database. The list may have all schema or database names, with total number of tables in each.
  8. Leave the default values for Connection and Query Timeout. They are 15 ms by default, but increase them later if you use a slow connection, or if your queries do not have enough time to complete.
  9. Leave Other Properties as they are. Or pass additional values, like sslmode=none; or require.
  10. Click on Test. This will check the number of tables and server’s version, as in this example. Do not continue until your connection has been properly verified as successful.
  11. Click on OK. Wait for metadata to be collected. The popup will close and a database connection node will be created, with table child nodes. In Data Xtractor or Model Xtractor, some ER model diagrams will also be generated, and one will be opened on screen.
  12. Click on Queries > Demo Queries > add demo queries. If you are not in Model Xtractor and want to play with some built-in SQL samples, this will open a popup with the missing queries.
  13. Double-click on any model or query node. This will open it up on the right pane.

With SSH (Secure Shell), you no longer connect directly to a remote database, but rather to the remote machine that hosts it. While in the Database Connection popup, you may need extra steps if you are forced to use this type of connection.

SSH Dialog

  1. Switch to the SSH tab and check the Use SSH Tunneling option. The SSH Parameters section appears only when this option is checked.
  2. Enter your Tunnel’s Host and Port Number. This is the remote machine you try to connect to, not the database.
  3. Enter your Tunnel’s User Name and Password. Leave Save Password checked. This are as well credentials required to connect to the remote machine, not the database.
  4. Enter an optional Identity File. This is the path of a file previously saved on your computer.
  5. Enter your SSH Local Port. This is a local port number forwarded as the remote database connection port number. Leave empty to use the database default port number.
  6. Click on Test. This will check your connectivity with these parameters.
  7. Switch back to the Connection tab..Continue from step 5, as described there before.

How to Connect to a MariaDB Database

We support MariaDB versions from 5.5  up. Your database must have been previously installed with a third-party tool. We recommend the free HeidiSQL.

Database Connection MariaDB

  1. Click on Connect > Open-Source Databases > MariaDB. This will open the previous New Database Connection popup. Return here anytime with the Properties context menu command on your database node.
  2. No need to install any data provider. MySQL Data Provider – which you may use as well for MariaDB –  was included in the setup.
  3. Enter your database Host Name and Port Number. Type localhost or your machine name for a local database. Default port number in MariaDB is 3306. Beware MySQL is using the same number, so you may have to change it if you host both on the same computer.
  4. Connect with your User Name and Password. Leave Save Password checked. Admin’s user name in MariaDB is root by default.
  5. Connect through SSH if required. Follow the steps described separately below for SSH and come back to continue.
  6. Select the list entry with your Database name. It should appear in the Database list when expanded, if previous parameters are correct. Or type the database name manually.
  7. Leave Schema(s) blank. Or select the same schema name as your database. The list may have all schema or database names, with total number of tables in each.
  8. Leave the default values for Connection and Query Timeout. They are 15 ms by default, but increase them later if you use a slow connection, or if your queries do not have enough time to complete.
  9. Leave Other Properties as they are. Or pass additional values, like sslmode=none; or require.
  10. Click on Test. This will check the number of tables and server’s version, as in this example. Do not continue until your connection has been properly verified as successful.
  11. Click on OK. Wait for metadata to be collected. The popup will close and a database connection node will be created, with table child nodes. In Data Xtractor or Model Xtractor, some ER model diagrams will also be generated, and one will be opened on screen.
  12. Click on Queries > Demo Queries > add demo queries. If you are not in Model Xtractor and want to play with some built-in SQL samples, this will open a popup with the missing queries.
  13. Double-click on any model or query node. This will open it up on the right pane.

With SSH (Secure Shell), you no longer connect directly to a remote database, but rather to the remote machine that hosts it. While in the Database Connection popup, you may need extra steps if you are forced to use this type of connection.

Database Connection MariaDB SSH

  1. Switch to the SSH tab and check the Use SSH Tunneling option. The SSH Parameters section appears only when this option is checked.
  2. Enter your Tunnel’s Host and Port Number. This is the remote machine you try to connect to, not the database.
  3. Enter your Tunnel’s User Name and Password. Leave Save Password checked. This are as well credentials required to connect to the remote machine, not the database.
  4. Enter an optional Identity File. This is the path of a file previously saved on your computer.
  5. Enter your SSH Local Port. This is a local port number forwarded as the remote database connection port number. Leave empty to use the database default port number.
  6. Click on Test. This will check your connectivity with these parameters.
  7. Switch back to the Connection tab..Continue from step 5, as described there before.

How to Connect to a PostgreSQL Database

We support PostgreSQL versions from 8.4 up, including Heroku (which at this moment is on 9.5). Your database must have been previously installed with a third-party tool. We recommend the free pgAdmin, included in the PostgreSQL distribution kit.

Database Connection PostgreSQL

  1. Click on Connect > Open-Source Databases > PostgreSQL. This will open the previous New Database Connection popup. Return here anytime with the Properties context menu command on your database node.
  2. No need to install any data provider. Npgsql Data Provider was included in the setup.
  3. Enter your database Host Name and Port Number. Type localhost or your machine name for a local database. Default port number in PostgreSQL is 5432.
  4. Connect with your User Name and Password. Leave Save Password checked. Admin’s user name in PostgreSQL is postgres by default.
  5. Connect through SSH if required. Follow the steps described separately below for SSH and come back to continue.
  6. Select the list entry with your Database name. It should appear in the Database list when expanded, if previous parameters are correct. Or type the database name manually.
  7. Select public Schema. The list may have all schema or database names, with total number of tables in each.
  8. Leave the default values for Connection and Query Timeout. They are 15 ms by default, but increase them later if you use a slow connection, or if your queries do not have enough time to complete.
  9. Leave Other Properties as they are. Or pass additional values, like pooling=False; (to disable connection pooling).
  10. Click on Test. This will check the number of tables and server’s version, as in the example. Do not continue until your connection has been properly verified as successful.
  11. Click on OK. Wait for metadata to be collected. The popup will close and a database connection node will be created, with table child nodes. In Data Xtractor or Model Xtractor, some ER model diagrams will also be generated, and one will be opened on screen.
  12. Click on Queries > Demo Queries > add demo queries. If you are not in Model Xtractor and want to play with some built-in SQL samples, this will open a popup with the missing queries.
  13. Double-click on any model or query node. This will open it up on the right pane.

With SSH (Secure Shell), you no longer connect directly to a remote database, but rather to the remote machine that hosts it. While in the Database Connection popup, you may need extra steps if you are forced to use this type of connection.

Database Connection PostgreSQL SSH

  1. Switch to the SSH tab and check the Use SSH Tunneling option. The SSH Parameters section appears only when this option is checked.
  2. Enter your Tunnel’s Host and Port Number. This is the remote machine you try to connect to, not the database.
  3. Enter your Tunnel’s User Name and Password. Leave Save Password checked. This are as well credentials required to connect to the remote machine, not the database.
  4. Enter an optional Identity File. This is the path of a file previously saved on your computer.
  5. Enter your SSH Local Port. This is a local port number forwarded as the remote database connection port number. Leave empty to use the database default port number.
  6. Click on Test. This will check your connectivity with these parameters.
  7. Switch back to the Connection tab..Continue from step 5, as described there before.