Connect LibreDB Studio to ClickHouse

Connect LibreDB Studio, an open-source web-based SQL IDE, to a self-hosted ClickHouse server over the HTTP interface.

LibreDB Studio is an open-source (MIT), self-hosted SQL IDE that runs in the browser. It connects to ClickHouse over the HTTP interface, with no JDBC or native driver, and gives you a SQL editor, a database browser, EXPLAIN plans and a monitoring dashboard built on the system tables.

Before you begin

• Run LibreDB Studio with Docker (docker run -p 3000:3000 ghcr.io/libredb/libredb-studio:latest) or with Node.js 24 or later (npx @libredb/studio), then open http://localhost:3000. On first run, the admin password is printed to the log.
• Make sure the machine that runs LibreDB Studio can reach the ClickHouse HTTP or HTTPS port.
• Create a ClickHouse user with access to the databases you want to query. The database browser also needs SELECT on system.dictionaries and system.data_skipping_indices, and dictGet on each database that holds dictionaries. For the dashboard, grant SELECT on the system tables listed under Monitor ClickHouse.

For example, for a user reader that queries the uk database:

GRANT SELECT ON uk.* TO reader;
GRANT dictGet ON uk.* TO reader;
GRANT SELECT ON system.dictionaries TO reader;
GRANT SELECT ON system.data_skipping_indices TO reader;

Deployment	Tested version	Notes
Self-hosted ClickHouse	26.7	Uses the HTTP(S) interface (8123, or 8443 for HTTPS).

How it works

1. The browser sends the statement to the LibreDB Studio server.
2. The server sends it to ClickHouse as the body of an HTTP POST /, with default_format=JSONCompact and the selected database as URL parameters, and credentials as HTTP Basic authentication.
3. The server returns the rows, the column types ClickHouse declared and the elapsed time that ClickHouse reported. Each statement is one stateless request; no session is pinned.

Connect to self-hosted ClickHouse

1. Gather your connection details

   You need the host, the HTTP port (8123 by default, or the HTTPS port your server uses, often 8443), a username and password, and optionally a database name. A connection that names no database uses default.

2. Create the connection

   1. In the sidebar, select + (New connection).
   2. Choose ClickHouse as the database type. The port is filled in as 8123.
   3. Enter a connection name, the host, the port, the username, the password, and optionally the database name.
   4. For HTTPS, open SSL / TLS, pick an SSL mode other than disable, and change Port to your server’s HTTPS port (often 8443). Every mode other than disable verifies the server certificate against the CAs the LibreDB Studio server trusts, which is what verify-system describes. This works with ClickHouse Cloud and with servers whose certificate comes from a publicly trusted CA. For a self-signed or private-CA certificate, see Troubleshooting.
   5. Select Test Connection, then Establish Connection.

   You can also paste a URL with Paste URL instead of filling the fields, for example clickhouse://localhost:8123/default or https://reader:[e-mail]:8443/analytics. An https:// URL turns TLS on.

3. Run a query

   Open the connection, write a statement in the editor and run it. The database browser lists each database’s tables, views, materialized views, dictionaries and user-defined functions, with the columns, primary key columns, data-skipping indexes and object definitions.

   This example uses the UK property price dataset. Load it into uk.uk_price_paid as that guide describes, set the connection’s database to uk, then run:

   SELECT
       town,
       count() AS sales,
       round(avg(price)) AS average_price
   FROM uk_price_paid
   GROUP BY town
   ORDER BY sales DESC
   LIMIT 10
   

Monitor ClickHouse

The monitoring dashboard reads these sources with the connection’s user:

Area	ClickHouse source
Version, uptime, connections	version(), uptime(), system.metrics, system.server_settings
Cache hit ratio, queries per second, memory	system.events, system.asynchronous_metrics
Slow queries, grouped by normalized query	system.query_log
Running queries	system.processes
Tables, table parts, rows and size	system.tables, system.parts
Data-skipping indexes	system.data_skipping_indices
Disks	system.disks

When the user lacks a grant on one of these tables, or query_log is turned off, that panel stays empty instead of failing the whole dashboard. system.query_log is flushed asynchronously, so a statement that just finished can take a few seconds to appear.

LibreDB Studio can also run OPTIMIZE TABLE ... FINAL on a table, show a system.parts summary of a table or database, and stop a running query with KILL QUERY ... SYNC. These need the matching grants.

Troubleshooting

The connection was refused or timed out

LibreDB Studio’s server makes the requests, not your browser, so the host must be reachable from the machine or container that runs it. When LibreDB Studio runs in Docker and ClickHouse runs on the same machine, localhost points to the container, not to the host. Check that you entered the HTTP port, not the native protocol port 9000.

HTTPS fails with a self-signed or private CA certificate

LibreDB Studio’s server runs on Node.js, which checks the server certificate against its own bundled list of public CAs. It ignores a CA certificate pasted under SSL / TLS, and adding the CA to the operating system’s certificate store isn’t enough on its own. Start the LibreDB Studio server with the NODE_EXTRA_CA_CERTS environment variable set to the path of a PEM file that holds your CA certificate, or the server’s own certificate if it’s self-signed. With Docker, mount the file into the container and pass, for example, -e NODE_EXTRA_CA_CERTS=/certs/ca.pem.

The database browser shows no objects, or a dictionary is listed as a table

The database browser reads system.dictionaries to sort each database’s objects into kinds, and system.data_skipping_indices for a table’s details. Without SELECT on system.dictionaries, no object list loads, and without SELECT on system.data_skipping_indices, a table’s details don’t open. A dictionary is listed under tables until the user has dictGet on its database. Grant these as shown under Before you begin, then refresh the browser.

Some dashboard panels are empty

The ClickHouse user can’t read one or more of the system tables listed under Monitor ClickHouse. Grant SELECT on the tables you need and reconnect.

The slow-query view is empty

The slow-query view reads system.query_log, which records nothing while query logging is off. Enable the log_queries setting for the user, for example in its settings profile, and run a few queries. New entries appear once the log is flushed, which can take a few seconds. If the view stays empty, query logging may be disabled for the whole server rather than for the user. Check that the query_log section is present in the server configuration, as described in system.query_log.

Next steps

• See the LibreDB Studio ClickHouse provider documentation for the full behavior.
• See the ClickHouse HTTP interface documentation for server configuration.