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.
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:
Otherwise, let us know.
Add a new Database widget
To add a new Database widget to your dashboard, follow these steps:
- 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.
- 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.
- Click on the Databases integration.
- On the Connect to Database side panel, select your SQL database type.
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.
- Is your first connection? Click Connecting your database.
Otherwise, in the panel, click .
and skip to
Connecting your database
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.
- On the Connect to Database panel, choose a Database type.
- Type in the Connection name. Give it a memorable name that helps you remember this connection.
- Type in the Host (this could be your Public IP) and Port. By default the port is set to 5432.
We cannot connect to a database on localhost (
127.0.0.1). The Host needs to be a publicly accessible address.
- Type in the Database name.
- Type in the database Username and Password
- Click running a query. . If successful, skip to
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:
- In the pop-up window that appears Paste your SQL query and click
We encourage you to write your query elsewhere before copying and pasting into this window.
Queries don't need to be terminated with a semicolon (
Avoid including comments in your SQL query. This includes single line comments using the
--symbol and multi-line comments using the
You can load data from
viewsas well as
viewis named stored query that provides another way to present data in the database tables.
We limit the size of data returned at 4MB. If you hit this limit, please take steps to simplify your query.
Includes manuals for PostgreSQL versions 11.1, 10.6, 9.6, 9.5, and 9.4.
Contains practical examples to help you learn PosgreSQL quickly.
- If successful, you'll see confirmation. Depending on your query you will see one or more highlighted visualization options. Hovering over each visualization type will tell you why it was, or wasn’t, suitable for your query.
When creating a line chart, the first column is the x axis and must be a time stamp.
- To add a widget, hover over your chosen visualization type and click the + button that replaces it.
- And that's it! Your new widget will now appear on your dashboard.
Be mindful of creating too many database widgets. The database may disconnect if you exceed approx. 200 widgets.
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
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
Minimum and maximum values
You might experience a loss in precision when using
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|
When choosing how many decimal points you'd like, it's best to use the
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
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;