Visualizing Compose PostgreSQL Data with Leftronic

At Compose, we use Leftronic for metrics at-a-glance. Leftronic is a real-time, browser-based data visualization service that makes it easy to create smart looking dashboards. We like it so much that we wanted to share how we're using it because we think you might like it, too.

Leftronic has many different integrations to services like Facebook, Google Analytics and Mail Chimp, to name a few, and they are continually adding new features. Features include spreadsheet upload, dashboard styling, dashboard collections, alerts per metric, and the ability to produce manual and scheduled reports. To make sure only we get to see our metrics, Leftronic also sports user management and IP whitelisting.

We started using Leftronic a couple years ago and have steadily escalated our usage to reach the top tier of their billing plan. We now have dozens of dashboards that display how different parts of our business are doing. In this article, we're going to walk through how we chart some of our PostgreSQL metrics in Leftronic.

Getting ready

If you don't have a Compose PostgreSQL deployment, you can either sign up to try it free for 30 days or, if you already have an account with us, click "Create Deployment" from within the Compose management console and select PostgreSQL to spin up an instance.

To follow along here, you'll also need to create a Leftronic account. They offer several different paid plans, but you can try out the service for 14 days free. Note that some of the features mentioned above come by default and some are plan-dependent so you'll want to check the different plans available to see which ones will meet your requirements. If you already have a Leftronic account, then let's dive right in for how you can use it with Compose PostgreSQL.

Creating a dashboard

Once you are logged into the Leftronic interface, the first thing you'll need to do is to create a dashboard. Just click on the little plus sign in the top right corner.

alt=

Then you'll need to select the kind of dashboard you want to create. We're going to generate a blank dashboard, but if you wanted a dashboard of all your Facebook stats or Google Analytics metrics, for example, you could take a shortcut to widget creation (which we'll review below), by selecting an integration-specific dashboard:

alt=

You'll be placed directly onto your new blank dashboard. In the far top right corner of the Lefronic interface, near where we originally clicked to create our dashboard, there is a gear icon for the settings related to all your dashboards. There are options for styling your dashboards using some presets or by applying your own custom CSS, for uploading data from a spreadsheet, for user and security management, and more. You'll want to read up on these on your own, but we'll cover a couple of these in this article:

alt=

For right now, we'll select "Dashboard Settings" to configure our new dashboard:

alt=

In our case, we are creating a dashboard for metrics having to do with usage in our app from MongoDB customers. You'll want to name yours according to the metrics you plan to display. By checking the box that says "Share link for current dashboard", you can generate a URL to share with viewers of the dashboard, which you can password protect as well. Note that the link shown here is for example purposes for the article - it isn't a link that you can follow.

Now that we have our dashboard set up, we need to populate it. We do that by creating a series of "widgets" - one per metric.

Creating widgets

Different integrations have different pre-set widgets (for example, Facebook has feed details and likes and Google Analytics has visits and ecommerce, among others) or you can create your own via API. There are also content widgets that support labels, images, and HTML as well as a calculator widget for math operations. Here we're going to choose the "Databases" service:

alt=

You'll notice the "Databases" icon is now displayed in the left nav to make it easy for us to select it again for each widget we want to generate. There are several different types of widgets available for databases:

alt=

You'll want to check the official documentation and do some exploration for how each type displays, but we'll run through a couple examples below.

Number widget

Let's have a look at the Number widgets available. To do that we'll go ahead and select "Number Query". You'll see we have even more options now:

alt=

Each of the graph options shown are for cases where the result of your database query will be a number. The simplest one is the single "Number" widget so let's start with that.

Once we click the widget type, an empty widget shows up on our dashboard. By clicking the gear icon in the top right corner of the widget, we can see the widget options available:

alt=

We're going to start by choosing to "Edit the widget settings". Once we click that option, a two-tabbed configuration box will appear. In the "Data Settings" tab is where we'll configure our Compose PostgreSQL connection and add our query.

Connecting to and querying Compose PostgreSQL

To configure the Leftronic connection to Compose PostgreSQL, you'll need your deployment connection string and credentials. You can get these from the deployment "Overview" page in the Compose management console. Copy each part of the connection string into the appropriate box (note that port is a separate box down below what's shown in this screen shot):

alt=

In this example, we used the credentials for the admin user. If you have other roles configured for your PostgreSQL database you could use one of those instead. The credentials you use just need to have the appropriate permissions to run the query you want to use.

If you want to use SSL, secure connections to Compose PostgreSQL are taken care of automatically by Leftronic.

Once your connection is configured, just copy your query into the query box:

alt=

Because this is a single number widget, our query returns one single number - the count of distinct account IDs that are running a MongoDB deployment.

Note that you'll want to have tested the query before adding it here to make sure it returns the results you expect. If, for some reason, there is a problem with the query, you'll see that the widget will display as "Error" once you save the widget settings. By clicking into the widget settings again and looking at the "Data Settings" tab, you'll be able to get the details of the error.

Before you leave the "Data Settings" tab, choose how frequently Leftronic should run the query to update the metric in the widget. You can select between 10 minutes and 24 hours. If the data in your database will not be changing frequently, then it is recommended that you choose one of the longer intervals. Each widget will create a new connection to your PostgreSQL database. Depending on how many apps are connecting to your PostgreSQL deployment and how frequently, you may need to increase the number of connections you allow.

When you're finished configuring the data settings, click to the "Widget Settings" tab.

Configuring the widget display

In the "Widget Settings" tab is where you'll set the display options for the widget:

alt=

Different kinds of widgets have different options. In this case, we've given our widget the title "Accts Paying for MongoDB" and we've set the widget to display the full number. You'll want to experiment with which display settings make the most sense for your data in each of the widget types you use.

Finally, we'll hit "Save" and we can now see our result in the widget on the dashboard:

alt=

We can move the widget around anywhere on the dashboard via drag-and-drop and we can also expand or minimize it by dragging the lower right corner out or down. Once you have a few widgets configured you'll use these features to configure the layout of your dashboard.

Now that you have a handle on the basics, let's do one more example - a multi-bar widget.

Multi-Bar widget

In the left navigation, click on the "databases" icon that was placed there previously. This time, however, click on "Multi-bar Query". You'll see one widget type available, the "Multi-Bar Graph":

alt=

Once the widget is placed on the dashboard, click on the gear icon in the top right corner of the widget to display the options and select "Edit widget settings" again. This will place you on the "Data Settings" tab to configure the widget.

Widget settings

If you already have a connection set up, like we do, then that will appear as the default. If you have more than one connection set up already, you can click on the arrow to choose the connection you want to use for this widget or you can select "Use another account" to set up a new connection. For our example here, we're going to use the same connection.

We'll copy in our query and select the frequency the query will be run. In this case, because this is a multi-bar widget, we're going to have multiple rows returned with a maximum of 3 columns that are ordered by what we want to display on the X axis, the pivot values, and then the Y axis. Our columns are Month, Event, and the count of unique events relating to usage of the MongoDB backups feature provided by Compose.

We'll then click over to the "Widget Settings" tab to set the display options. As you can see, because this is a different widget type, there are different options available. Again, you'll want to experiment with which settings work best for your data. We're going to entitle our widget "MongoDB Backups", have the widget display as a stacked bar chart, enable the display of X and Y axis labels, and also we're going to limit the number of bars to just 6 (an option lower down than what this screenshot shows) because our query returns only the previous 6 months of results:

alt=

Once we "Save", we'll see our results in the widget on the dashboard:

alt=

And that's how we build our dashboard - one widget at a time for each of the metrics we want to display.

When you're at the point where you have multiple dashboards, you may want to keep them organized by adding them to a collection and potentially allowing them to be set on a rotating display cycle for viewers.

Dashboard collections

Selecting the "Settings" gear icon in the top right again and clicking on "Dashboard Collections" will open a configuration box for creating and managing dashboard collections:

alt=

As you can see from ours, we have a handful of collections already configured: Key Metrics, Content, and App Usage. You can create a collection, by clicking on the large blue "Create New" button down in the far right corner. Since we already have some collections, we've selected the App Usage one to add our new "App Usage - MongoDB" dashboard.

Here is where you name the collection, choose which dashboards you want to add (and in which order you want them to display), and configure other options such as a shareable link (may also be password protected) and the cycling speed for display. Clicking on the "Add Dashboard" button let's you select from a dropdown of all the dashboards you have created. Once you've completed the configurations for the collection, scroll down and hit "Save", then click "Close" in the far lower left corner of the box.

Before we wrap up, we wanted to review one last key feature related to connecting to Compose PostgreSQL... that's the "Authentications" option in the "Settings" menu.

Managing authentications

Now that we have a connection set up to Compose PostgreSQL, we can manage it by using the "Authentications" settings option. To do this, select the gear icon in the far top right of the Leftronic interface again. Select "Authentications". Here you'll see your Compose PostgreSQL connection. If you have created widgets using other integrations, like Facebook or Google Analytics, you'll see those here, too:

alt=

By clicking on the Compose PostgreSQL connection, you can select "Edit Authentication" to alter the connection configuration. This can be useful when you want to change the database used or the credentials. You can also delete the connection entirely by clicking on the "Delete Authentication" option. Note, however, that new connections can only be created via the widget settings, which we described above.

Wrapping up

Now you know how to pair Leftronic with Compose PostgreSQL to visualize your data. Follow our lead. Go ahead and try out all the different types of widgets and displays in Leftronic to create a rich dashboard that will bring your metrics to life.