DbSchema for Elasticsearch Database

DbSchema is a powerful database management and design tool for Elasticsearch. It offers features such as visual schema design, team collaboration with GIT, schema deployment and HTML schema documentation.

How to Connect to Elasticsearch Database

  1. Access the Connection Dialog

    Choose "Connect to the database" or "New Model Connected to the Database" will let you select your database and open the Connection Dialog.

    DbSchema automatically downloads the JDBC driver to connect to your database.

  2. Configure the Connection

    3. In the Connection Dialog window, select the JDBC URL you want to connect with. If your database is running in the cloud, choose Edit Manually and insert the JDBC URL from the cloud console.

    Enter the host name, the authentication details, select your database as described in the Connection Dialog page.

  3. Troubleshoot Firewall Issues
    4. If you encounter connection issues, it may be due to firewall settings. Read how to enable Firewall Connections.
Remember to enable Windows Firewalls Firewalls & anti-viruses may block database connections.

Connecting to Elasticsearch may require to import first the Server HTTPS certificate into the Java carcerts file. During our tests we runned Elasticsearch in a docker container called elasticsearch created using:

docker run --name elasticsearch -p 9200:9200 -p 9300:9300 -e "discovery.type=single-node" -it docker.elastic.co/elasticsearch/elasticsearch:8.8.2

Install the HTTPS Server Certificate in Java

Check the Java Location

Check the Java location in DbSchema in the Help / About Dialog.

Open a Command Line

Open a command line and change the folder to the Java location above \lib\security. A file named carcerts should exists in that folder.

Copy the HTTPS Certificate from Docker Container

Copy the http_ca.crt file from the Elasticsearch server in the current folder.

If you are using docker containers, copy it from the docker image using :

docker cp elasticsearch:/usr/share/elasticsearch/config/certs/http_ca.crt .

Import the Certificate

Import the certificate in the Java carcerts using this command:

..\..\bin\keytool -import -trustcacerts -keystore cacerts -storepass changeit -noprompt -alias elasticsearch -file http_ca.crt

Connect using DbSchema

Connecting using DbSchema usually require the elasticsearch password. When using docker containers, this is printed out when first creating the docker container.

How to connect to Elasticsearch

You can also choose to edit the JDBC URL and enter parameters like in the Elasticsearch documentation.

Connect to Elasticsearch using URL

The typical URL for HTTPS connections is: 

jdbc:es://https://localhost:9200

Useful Elasticsearch Commands

To create an index in Elasticsearch, I used:

curl -k --user "elastic:iSSBS=-sjfdjEqmIvpfZ" -H "Content-Type: application/json"  -XPUT "https://localhost:9200/testindex?pretty" -d "{\"settings\" : {\"index\" : {\"number_of_shards\" : 3, \"number_of_replicas\" : 0 }}}"

To save a document, use

curl -k -H "Content-Type: application/json" --user "elastic:iSSBS=-sjfdjEqmIvpfZ" -XPOST "https://localhost:9200/testindex/_doc"  -d"{\"timestamp\": \"2018-01-24 13:34:56\",\"message\": \"User Clicked Button\",\"user_id\": 4,\"admin\": false}"

To list the indexes:

curl -k --user "elastic:iSSBS=-sjfdjEqmIvpfZ" "https://localhost:9200/_cat/indices"

Your Feedback

Please help us to improve the Elasticsearch support by writing us your feedback, using Help / Technical Support from the DbSchema menu.