Connect your SQL database to Geckoboard

Who can use the database integration?

Plan availability: Trial Starter Growth Company

In this article, we'll show you how to create a widget powered by your PostgreSQL database for your Geckoboard dashboard.

Note

This integration is currently in beta.

So far, PostgreSQL is the only type of SQL database we support. More will be added very soon, but you can request support for your SQL database type.

We've tested this beta on PostgreSQL versions 11.1, 10.6, 9.6, 9.5 and 9.4.

There are some limitations to the beta and you may see additions and improvements to it over time. If you discover any bugs, or would like to share any feedback or feature requests for this integration, please email us at support@geckoboard.com.

Important notes

You may need to ask a system admin for your SQL database information.

Also, be mindful of the permissions given to the database user used to connect to the database. This may result in them having permission to display confidential information, such as a list of users in the database.

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 Database 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 Database integration.
  4. Is your first connection? Click New connection. Otherwise, in the panel, click Create a widget.

    Important note

    Your database must be publicly accessible via the internet, have SSL encryption enabled, and be allowed to accept incoming connections from Geckoboard IP addresses.

  5. On the Connect to Database panel, choose a Database type.Choose your database type
  6. Type in the Connection name. Give it a memorable name that helps you remember this connection.Enter your connection name
  7. Type in the Host and Port. By default the port is set to 5432. Enter your database host and port details
  8. Type in the Database name.Enter your database name
  9. Type in the database Username and PasswordEnter your database username and pasword
  10. Click Connect.
  11. 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

    Before you start

    To avoid errors when using this beta, take a look at how best to format your query and if you need to use one of our workarounds.

    Note

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

    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.

    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.

  12. You will see a confirmation that your query was run successfully. 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.

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

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.

The integer type Bigint currently doesn't support maxJSSafeInteger. Read more about floating point numbers.

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 -9007199254740991 9007199254740991
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!