Getting Started with DbSchema on a Distributed SQL Database

If you’re a database developer, you know the time saving value of being able to visually design, document and query SQL and NoSQL databases from a single UI. DbSchema is a well-rounded, visual database tool that supports over 40 databases from a single interface. Because YugabyteDB is PostgreSQL compatible, getting DBSchema to work with a distributed SQL database is relatively simple.

What’s YugabyteDB? It is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. Yugabyte’s SQL API (YSQL) is PostgreSQL wire compatible.

In this post we’ll show you how to get DbSchema connected to a YugabyteDB cluster so you can start reverse-engineering schemas, edit ER diagrams, browse data, visually build queries and even sync schemas.

What is DbSchema?

DbSchema boasts many of the must-have features you’d expect from a database GUI tool including:

• Interactive Layouts – Divide complex schemas into separate layouts (groups of tables) for a better representation of the database. Use the layouts to browse and edit the schema visually, without having to write any SQL queries.
• Relational Data Browse – Use this simple tool to view data from multiple tables simultaneously, based on foreign keys. There is no limit to how many tables can be opened at once.
• Random Data Generator – Generate test data in your database using configurable, random and reverse regular expression patterns. A predefined repository of patterns will help you find the best data pattern for each column.
• Visual Query Builder – Build SQL Queries visually, without having to write any line of code. Add and edit tables with a simple click.
• Connect to any Database & Reverse Engineer the Schema – Start a new project by reverse engineering the schema. Save the schema structure to the project file and you can reopen it without database connection.
• Schema Synchronization – DbSchema is using it’s own image of the schema saved to the project file. This enables you to open and modify the schema offline, then synchronize it when you connect to the database.

Dbschema offers a 15 day free trial, so you try all the features before deciding if you’d like to sign on for a commercial license, which is very reasonably priced. Ok, let’s dive in and get DbSchema installed and connected to a YugabyteDB cluster!

Step 1: Install a Local YugabyteDB Cluster

Before we get into DbSchema, you’ll need to setup YugabyteDB and install a sample database. Although for the purposes of this blog post we’ll be installing everything on a macOS, both YugabyteDB and Dbschema support most major operating systems.

For complete instructions on how to get up and running on a variety of platforms including prerequisites, check out our Quickstart Guide. In the following section we’ll cover the basic steps for getting up and running in just a few minutes with a local 3 node cluster on your Mac laptop.

Download and Extract YugabyteDB

$ wget https://downloads.yugabyte.com/yugabyte-2.0.9.0-darwin.tar.gz
$ tar xvfz yugabyte-2.0.9.0-darwin.tar.gz && cd yugabyte-2.0.9.0/

Note: At the time of this writing, 2.0.9 is the latest release of YugabyteDB. Make sure to check the Quickstart Guide for the latest version.

Configure Loopback Addresses

Add a few loopback IP addresses for the various YugabyteDB processes to use.

$ sudo ifconfig lo0 alias 127.0.0.2
$ sudo ifconfig lo0 alias 127.0.0.3
$ sudo ifconfig lo0 alias 127.0.0.4
$ sudo ifconfig lo0 alias 127.0.0.5
$ sudo ifconfig lo0 alias 127.0.0.6
$ sudo ifconfig lo0 alias 127.0.0.7

Create a 3 Node Cluster

With the command below, create a 3 node cluster with a replication factor of 3.

$ ./bin/yb-ctl --rf 3 create

You can verify that your cluster is up and running by checking out the YugabyteDB Admin UI which is located at:

https://127.0.0.1:7000/

Step 2: Create the Northwind database

For the purposes of this blog post we’ll be using the northwind sample database. You can download the DDL and data scripts here. Once you have downloaded the files, building the northwind database on YugbyteDB is simple:

Open the YSQL shell and create the northwind database

$ ./bin/ysqlsh
CREATE DATABASE northwind;
\c northwind

You are now connected to database "northwind" as user "yugabyte".
northwind=#

Build the northwind tables and objects

\i share/northwind_ddl.sql

\i share/northwind_data.sql

Step 3: Download and Install DbSchema

DbSchema can be downloaded from their website here:

https://dbschema.com/download.html

As of this writing, version 8.2.5 is what we tested against YugabyteDB. The install is wizard-driven, so it’s very easy to get up and running.

Step 4: Connecting Dbschema to YugabyteDB

Next, you’ll want to configure a database connection when prompted. You’ll see a pop-up like this:

Configure the connection to YugabyteDB using the following values:

• Server Host or IP: localhost
• Port: 5433 (Note that PostgreSQL’s default port assignment is 5432, while YugabyteDB uses 5433)
• Database User: “yugabyte” …this is YugabyteDB’s default user
• Password: None (by default)
• Database: “northwind”

Click the “connect” button and you are now ready to start exploring DbSchema.

Step 5: Test Drive DBSchema with YugabyteDB

Once you are connected, you’ll get prompted to reverse engineer a database. Let’s make that happen with the northwind database using DbSchema’s wizard.

Check the appropriate boxes and click “Ok.” That’s it! As you can see from the screenshot below we now have an ER diagram of the northwind database running on a 3 node YuagbyteDB cluster.

You can learn more about DbSchema’s features here, supported databases and drivers, plus work through some of their tutorials.

What’s Next?

• Compare YugabyteDB in depth to databases like CockroachDB, Google Cloud Spanner and MongoDB.
• Get started with YugabyteDB on macOS, Linux, Docker, and Kubernetes.
• Contact us to learn more about licensing, pricing or to schedule a technical overview.