Home  /  Tools and integrations

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.

Field Value
Host Materialize host name.
Port 6875
Database name Database to connect to (default: materialize).
Database username Materialize user.
Database password App password for the Materialize user.
SSL mode Require

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.

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.

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.

DataGrip Materialize Connection Details

[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.

Connect using 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 default 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:

    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.

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.

Screenshot 2023-12-28 at 18 45 11

psql

WARNING! Not all features of psql are supported by Materialize yet, including some backslash meta-commands.

    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

    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.

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

    psql --version

    Download and install the PostgreSQL installer certified by EDB.

    Back to top ↑