Manage your data using AlloyDB Studio

This page explains how to explore and manage your AlloyDB for PostgreSQL data using AlloyDB Studio in the Google Cloud console.

AlloyDB Studio includes an Explorer pane that integrates with a query editor and a SQL query results table. You can run DDL, DML, and DQL statements from this single interface. For example, instead of configuring a third-party database query tool, you can create a table or query your data using this query editor.

If you're new to AlloyDB for PostgreSQL, see AlloyDB overview.

You can use the Explorer pane in AlloyDB Studio to view and interact with your database objects. You can create, alter, and delete the following database objects:

  • Schemas
  • Tables
  • Columns
  • Indexes and keys
  • Triggers
  • Views
  • Functions
  • Procedures
  • Sequences
  • Types
  • Extensions

Required roles and permissions

To use AlloyDB Studio, you need the following permissions:

  • alloydb.clusters.get
  • alloydb.databases.list
  • alloydb.instances.executeSql
  • alloydb.instances.list
  • alloydb.users.list

You can get these permissions through the roles/alloydb.admin role, or using a combination of roles/alloydb.viewer and roles/alloydb.databaseUser roles. If you don't have this role, contact your Organization Administrator to request access. You might also be able to get the required permissions through custom roles or other predefined roles.

Additionally, ensure that you have database-level permissions for the database that you're using for authentication.

Explore your data

To access the Explorer pane, follow these steps:

  1. In the Google Cloud console, open the AlloyDB page.

    Go to AlloyDB

  2. Select a cluster from the list.

  3. In the navigation menu, click AlloyDB Studio.

  4. Select a database and user, and enter the user's password.

  5. Click Authenticate. The Explorer pane displays a list of the objects in your database.

Create, modify, and query your data

You can use the query editor to run SQL statements. Statements must be separated by a semicolon. You can compose a query yourself, or you can populate the query editor with a template.

Statements are executed based on the order in which you enter them in the query editor.

To create, query, or modify your data, follow these steps:

  1. In the Google Cloud console, open the AlloyDB page.

    Go to AlloyDB

  2. Select a cluster from the list.

  3. In the navigation menu, click AlloyDB Studio.

  4. Sign in to AlloyDB Studio using the name of your database, username, and password.

  5. Compose a query using one of the following methods:

    • To compose your own query, follow these steps:

      1. Open a new tab by clicking New SQL editor tab or New tab.
      2. When the query editor appears, write your query.
    • To compose a query starting with a template, follow these steps:

      1. In the Explorer pane, next to a database object, click more_vert View actions. One or more available actions appear.
      2. Click an action. The query editor populates with a template.
      3. Replace any placeholders in the template.
  6. Click Run. This executes all the queries in the query editor. The results of your query appear in the Results table.

    You can also select and execute one or multiple queries at the same time. To do this, select a query or multiple queries and click Run selected.

Limitations

  • AlloyDB Studio supports lightweight queries. Responses over 8MB might be truncated. AlloyDB Studio indicates when partial results are returned.
  • Requests taking longer than one minute on the instance are cancelled.
  • AlloyDB Studio doesn't keep a database connection open after execution of a SQL script is completed. Similarly, the Editor tabs don't share a common session and every execution starts a new connection using a separate session.
  • AlloyDB Studio only supports PostgreSQL-native authentication (username and password). You can't authenticate using Identity and Access Management or use credentials saved in Secrets Manager.
  • If you're running multiple query statements at the same time, only the first result set is available in the Results tab in AlloyDB Studio. If one of the queries results in an error, then the whole query is cancelled.

What's next