Connect a PostgreSQL or MySQL database and import its tables as datasets. Database connectors are on the Pro plan and above.
Before you start
- A workspace on Pro or above.
- A database Chartlet can reach over the internet. If your database only accepts connections from known addresses, contact support before you connect.
- A read-only database user. Chartlet only reads, and a read-only user makes sure of it.
For PostgreSQL, a read-only user can be set up like this (adjust the names):
CREATE ROLE chartlet_reader LOGIN PASSWORD 'choose-a-strong-password';
GRANT CONNECT ON DATABASE your_database TO chartlet_reader;
GRANT USAGE ON SCHEMA public TO chartlet_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO chartlet_reader;
For MySQL:
CREATE USER 'chartlet_reader'@'%' IDENTIFIED BY 'choose-a-strong-password';
GRANT SELECT ON your_database.* TO 'chartlet_reader'@'%';
Connect
- Open Connectors and choose Connect on PostgreSQL or MySQL.
- Enter a connection name, the host, the port (PostgreSQL usually
5432, MySQL3306), the database name, and for PostgreSQL the schema (publicif you leave it empty). - Enter the read-only user's name and password, then connect.
- Chartlet tests the connection as soon as you save it, and tells you if it cannot sign in or reach the host.
The password is stored encrypted for your workspace. To change it, choose Fix on the connection when it needs attention, or disconnect and connect again.
Import tables
- In a chat, choose +, Import from a connection, then the database.
- Pick one or more tables. Optionally limit rows to a time window.
- Import. Each table becomes a dataset you can ask about, join with others, and build dashboards from.
Use Sync now on a dataset, or a sync automation, to re-import a table as it changes. See Automations.
Troubleshooting
Could not connect. Check the host and port, and that the database accepts connections from outside your network.
Authentication failed. Check the username and password, and that the user may connect to that database.
A table is missing. The user needs SELECT on it, and for PostgreSQL USAGE on its schema. Check the schema name in the connection.