SQL clients

Materialize is wire-compatible with PostgreSQL, which means it integrates with many SQL clients that support PostgreSQL. In this guide, we’ll cover how to connect to your Materialize region using some common SQL clients.

Connection parameters

You can find the credentials for your Materialize region in the Materialize console, under Connect externally in the navigation bar.

Before connecting, double-check that you've created an app-password for your user. This password is auto-generated, and prefixed with mzp_.

Runtime connection parameters

{{< warning >}} Parameters set in the connection string work for the lifetime of the session, but do not affect other sessions. To permanently change the default value of a configuration parameter for a specific user (i.e. role), use the ALTER ROLE...SET command. {{< /warning >}}

You can pass runtime connection parameters (like cluster, isolation_level, or search_path) to Materialize using the options connection string parameter, or the PGOPTIONS environment variable. As an example, to specify a different cluster than the default defined for the user and set the transactional isolation to serializable on connection using psql:

# Using the options connection string parameter
psql "postgres://<MZ_USER>@<MZ_HOST>:6875/materialize?sslmode=require&options=--cluster%3Dprod%20--transaction_isolation%3Dserializable"

# Using the PGOPTIONS environment variable
PGOPTIONS='--cluster=prod --transaction_isolation=serializable' \
psql \
    --username=<MZ_USER> \
    --host=<MZ_HOST> \
    --port=6875 \
    --dbname=materialize

Tools

DataGrip

{{< tip >}}

Integration with DataGrip/WebStorm is currently limited. Certain features -- such as the schema explorer, database introspection, and various metadata panels -- may not work as expected with Materialize because they rely on PostgreSQL-specific queries that use unsupported system functions (e.g., age()) and system columns (e.g., xmin).

As an alternative, you can use the JDBC metadata introspector. To use the JDBC metadata instrospector, from your data source properties, in the Advanced tab, select Introspect using JDBC Metadata from the Expert options list. For more information, see the DataGrip documentation.

{{< /tip >}}

To connect to Materialize using DataGrip, follow the documentation to create a connection and use the PostgreSQL database driver with the credentials provided in the Materialize console.

[use the JDBC metadata introspector]:

DBeaver

Minimum requirements: DBeaver 23.1.3

To connect to Materialize using DBeaver, follow the documentation to create a connection and use the Materialize database driver with the credentials provided in the Materialize console.

The Materialize database driver depends on the PostgreSQL JDBC driver. If you don't have the driver installed locally, DBeaver will prompt you to automatically download and install the most recent version.

Connect to a specific cluster

By default, Materialize connects to the pre-installed quickstart cluster. To connect to a specific cluster, you must define a bootstrap query in the connection initialization settings.

1. Click on Connection details.

2. Click on Connection initialization settings.

3. Under Bootstrap queries, click Configure and add a new SQL query that sets the active cluster for the connection:

   mzsqlCopy

   SET cluster = other_cluster;
   

Alternatively, you can change the default value of the cluster configuration parameter for a specific user (i.e. role) using the ALTER ROLE...SET command.

Show system objects

By default, DBeaver hides system catalog objects in the database explorer. This includes tables, views, and other objects in the mz_catalog and mz_internal schemas.

To show system objects in the database explorer:

1. Right-click on the database connection in the Database Navigator.
2. Click on Edit Connection.
3. In the Connection settings tab, select General.
4. Next to the Navigator view, click Customize.
5. In the Navigator settings dialog, check the Show system objects checkbox.
6. Click OK.

TablePlus

{{< note >}} As we work on extending the coverage of pg_catalog in Materialize, some TablePlus features might not work as expected. {{< /note >}}

To connect to Materialize using TablePlus, follow the documentation to create a connection and use the PostgreSQL database driver with the credentials provided in the Materialize console.

psql

{{< warning >}} Not all features of psql are supported by Materialize yet, including some backslash meta-commands. {{< /warning >}}

{{< tabs >}} {{< tab "macOS">}}

Start by double-checking whether you already have psql installed:

psql --version

Assuming you’ve installed Homebrew:

brew install libpq

Then symlink the psql binary to your /usr/local/bin directory:

brew link --force libpq

{{< /tab >}}

{{< tab "Linux">}}

Start by double-checking whether you already have psql installed:

psql --version

sudo apt-get update
sudo apt-get install postgresql-client

The postgresql-client package includes only the client binaries, not the PostgreSQL server.

For other Linux distributions, check out the PostgreSQL documentation.

{{< /tab >}}

{{< tab "Windows">}}

Start by double-checking whether you already have psql installed:

psql --version

Download and install the PostgreSQL installer certified by EDB. {{< /tab >}} {{< /tabs >}}

See also

See also the following integration guides for BI tools:

• Deepnote
• Excel
• Hex
• Metabase
• Power BI
• Tableau
• Looker