Connect your SQL database to Geckoboard

In this article:

Applicable plans:
TrialBasicTeamCompany

In this article, we'll show you how to create a widget powered by your SQL database for your Geckoboard dashboard. We currently support MySQL, PostgreSQL, and Amazon Redshift databases.We support MySQL, PostgreSQL, and Amazon Redshift

Before you start

Run through this checklist to prepare your database for connecting to Geckoboard:

  • Verify that your database is running correctly.
  • Ensure your database is publicly accessible via the internet.
  • We do not allow connections by superusers.
  • Your database must have TLS encryption enabled.
  • Ensure that you have accepted incoming connections from these Geckoboard IP addresses:

    23.23.205.140, 50.16.244.16, 54.204.39.96, 54.243.225.101, 54.243.235.136, 54.243.235.173

Otherwise, let us know.

Add a new Database widget

To add a new Database widget to your dashboard, follow these steps:

  1. Hover your cursor over the empty dashboard square you would like to position your widget and click the + button.

    Alternatively, click Add widget, located in the top right of your dashboard.
  2. Search for Databases using the Search sources field.

    Alternatively, you can also scroll down the alphabetical list of integrations until you reach the Database integration.
  3. Click on the Databases integration.
  4. On the Connect to Database side panel, select your SQL database type.

    Note

    We currently support MySQL, PostgreSQL, and Amazon Redshift databases. More will be added in time, but you can request support for your SQL database type.

    We test every deployment of this beta on MySQL versions 5.6, 5.7, and 8.0.13, PostgreSQL versions 11.1, 10.6, 9.6, 9.5, and 9.4 and the latest version of Redshift.

  5. Is your first connection? Click New connection and skip to Connecting your database.

    Otherwise, in the panel, click Create a widget.

Connecting your database

Important note

We strongly recommend that you connect with user credentials that have limited permissions.

Once the connection is established, we have access to everything the user can access in your database, but we only pull the data that's in the query. We won't collect anything that you haven't specifically requested, but someone else logging into the account would be able to create additional queries with the connection.

Alternatively, our SQL-Datasets connector is run locally by you so the credentials you use are not shared with us. You could choose to fetch all or any of your data via the sql-query, but Geckoboard only has access to the particular data pushed to the dataset.

However, SQL-Datasets may not suit less experienced users, as it requires you to set up a script that pings your database.

To make a new connection to an SQL database, follow these steps.

  1. On the Connect to Database panel, choose a Database type.Choose your database type
  2. Type in the Connection name. Give it a memorable name that helps you remember this connection.Enter your connection name
  3. Type in the Host (this could be your Public IP) and Port. By default the port is set to 5432. Enter your database host and port details

    Note

    We cannot connect to a database on localhost (127.0.0.1). The Host needs to be a publicly accessible address.

  4. Type in the Database name.Enter your database name
  5. Type in the database Username and PasswordEnter your database username and pasword
  6. Click Connect. If successful, skip to running a query.

Having trouble connecting to your database?

Please run through the checklist for preparing your database for connecting to Geckoboard.

Running a query

Once you've connected to your SQL database, it's time to write your query and paste it into Geckoboard. Follow these steps:

  1. In the pop-up window that appears Paste your SQL query and click Run query.

    We encourage you to write your query elsewhere before copying and pasting into this window. Enter your SQL query in the pop-up window

    Note

    Queries don't need to be terminated with a semicolon (;).

    Note

    Avoid including comments in your SQL query. This includes single line comments using the -- symbol and multi-line comments using the /* and */ symbols.

    Note

    You can load data from views as well as tables.

    A view is named stored query that provides another way to present data in the database tables.

    Important note

    We have a query execution limit of 15 seconds, after which, the widget will timeout. If you hit this limit, please take steps to simplify your query.

    Important note

    We limit the size of data returned at 4MB. If you hit this limit, please take steps to simplify your query.

    Recommended reading

    PostgreSQL documentation

    Includes manuals for PostgreSQL versions 11.1, 10.6, 9.6, 9.5, and 9.4.

    PostgreSQL Tutorial

    Contains practical examples to help you learn PosgreSQL quickly.

    Get help

    If you're experiencing errors, see if you need to use one of our workarounds. Otherwise, drop us a line and our Customer Success team will help you.

  2. If successful, you'll see confirmation. Depending on your query you will see one or more highlighted visualization options. Highlighted visualization types for your SQL queryHovering over each visualization type will tell you why it was, or wasn’t, suitable for your query.Number visualization eligibility

    Note

    When creating a line chart, the first column is the x axis and must be a time stamp.

  3. To add a widget, hover over your chosen visualization type and click the + button that replaces it.
  4. And that's it! Your new widget will now appear on your dashboard.

    Important note

    Be mindful of creating too many database widgets. The database may disconnect if you exceed approx. 200 widgets.

Unsupported statements

Drop table

Data definition queries such as DROP TABLE will return an error if run in the SQL code window. This is because queries run by the Database integration are read only.

Unsupported data types

Jsonb data type

Jsonb is not supported. To display jsonb data, you'll need to use CAST to convert it to another data type.

Example of using CAST to convert a jsonb datatype

To convert a jsonb data type, use:

SELECT surname, CAST(address as varchar) FROM gecko_keepers WHERE title = 'Dr

One value from the jsonb can be displayed as text in a table visualization by using this query:

SELECT surname, address ->> 'road' AS road FROM gecko_keepers WHERE title = 'Dr

Money data type

Money is not a supported datatype, and so will not be displayed in visualizations.

Money can be displayed in a table by using CAST to convert from one data type into another.

Example of using CAST to convert currencies

To convert a currency to numeric, use:

SELECT destination_id, TRUNC(CAST(value_of_food_money AS NUMERIC)) AS Value, shipping_date from shipping_geckos

Workarounds

Minimum and maximum values

Note

You might experience a loss in precision when using real, double precision, numeric or decimal types with large numbers or very small negative numbers.

We support Integer values up to 32 bits.

Bigint types not only consume a lot of storage but also decrease the performance of the database, therefore, you should have a good reason to use it.

The following table illustrates the minimum and maximum values for integer and serial types:

Data type Min value Max value
Integer -2147483648 2147483647
Serial 1 9007199254740991

Decimal points

When choosing how many decimal points you'd like, it's best to use the TRUNC function. TRUNC returns a truncated value of a number or a datetime value. For more information, see PostgreSQL's list of Mathematical Functions and Operators.

Example uses of TRUNC

To round mean_temperature_c to 2 decimal points, use:

SELECT date_first_shipped_to, TRUNC(mean_temperature_c,2) as Temperature FROM destinations;

For no decimal points, use:

SELECT date_first_shipped_to, TRUNC(gravity) FROM destinations;
Was this article helpful?

Awesome! 👍  Thanks so much for your feedback!

Sorry about that! Start a conversation now.