Search docs.molo17.com

    Query Studio module

    Overview

    How-to guide

    For a step-by-step walkthrough tested on a running Gluesync, see the how-to guide Query pipeline data with Query Studio.

    Query Studio is an embedded, lightweight SQL workbench integrated directly into the Gluesync Control Plane. It acts as an in-product alternative to external database management tools (such as DBeaver, DataGrip, or pgAdmin), allowing developers, database administrators, and sync engineers to inspect, query, and validate synchronized datasets without leaving the Gluesync portal.

    By leveraging Gluesync’s pre-existing knowledge of source and target database credentials, Query Studio provides a unified, secure workspace where you can run ad-hoc queries, explore schema hierarchies, inspect execution plans, and run statistics directly against your managed connections.

    Key capabilities

    • Multi-tab SQL editor: Keep up to 10 active SQL editors open in a single session, with query scripts and tab configurations persisted locally.

    • Dialect-aware syntax highlighting: Highlighting and auto-complete are tailored dynamically to the specific database engine of the selected agent (e.g., PostgreSQL, Oracle, MySQL, SQL Server).

    • Interactive schema browser: A dynamic tree representation of the database’s schema hierarchy that lets you lazy-load schemas, tables, and columns on demand.

    • Explain plan visualization: Dialect-native EXPLAIN query analyzer that outputs the physical or logical query execution path in text or hierarchical tree formats.

    • Query progress streaming: Real-time progress notifications (started, executing, first row, rows processed) delivered via standard Gluesync WebSocket channels.

    • Secure transaction boundaries: Safeguard production databases using default "Read-only" transaction isolation levels, with toggleable write permissions for authorized administrators.

    • Automatic parameter prompting: Detects and parses positional (?) or named (:name) query parameters in SQL statements, requesting user input values before execution.

    • Query statistics calculation: Computes analytical data statistics (min, max, mean, length, unique values, null count/percentage) on the client side for returned results.

    • Flexible result exporting: Stream or download result sets as standard CSV or JSON, or generate a formatted SQL INSERT script targeting custom tables.

    • Row insert and delete: In Writable mode, on a single-table result with a discovered primary key, add a row or delete a row straight from the result grid, next to in-place cell editing. Not available on MongoDB; see Inserting and deleting result rows.

    • Saved queries & execution history: Store recurring query templates within specific pipeline contexts and track up to 500 query execution records.

    • AI helper & Tab autocomplete: Schema-grounded chat drafts and suffix-only SQL ghost text via Core Hub endpoints; see AI helper and Tab autocomplete.

    • PII labels and masking: Classified columns show a PII tag in the schema tree and result grid. Cell values are masked in results, copies, and exports unless a privileged user unmasks for that session. AI helper and MCP SQL results stay masked. See PII discovery and masking.

    • Query Data Access: Per-user, per-connection schema and table rules on top of the user’s role; see Query Data Access.

    UI tour

    Query Studio is organized into a cohesive three-pane workspace:

    1. Connection picker (top bar): Selects the pipeline and agent connection. A dedicated color chip—synced with the pipeline’s metadata color or inferred from environment patterns (Dev, Test, Prod)—provides clear visual context of the active database instance.

    2. Schema browser (left panel): Provides hierarchical exploration of the active database connection. You can expand schemas to view tables, and tables to inspect columns along with their data types. Classified columns show a PII tag. The column menu can scan a table or schema for PII, mark a column as PII, or dismiss a false positive.

    3. Interactive editor & results (center panel): Contains the multi-tab SQL editor, action toolbar, and result sections. The results pane includes specialized tabs:

      • Results: Interactive data grid displaying query outputs with sortable headers.

      • Errors: Highlights detailed database error messages, warning logs, and line/column error markers.

      • Explain: Visualizes the database-specific execution plan.

      • Stats: Displays client-computed statistics for each returned column.

    Query Studio interface

    Supported database agents

    Query Studio is restricted to SQL-capable database agents. To protect platform performance, NoSQL connectors (such as MongoDB, Cassandra, Redis, Couchbase, or Aerospike) are automatically filtered out from the connection picker.

    Query Studio supports dialect-specific optimizations, query formatting, and explain plan parsing for the following 18 database engines:

    Database dialect Query Studio identifier Features supported

    Amazon Redshift

    redshift

    Autocomplete, Text Explain

    CockroachDB

    cockroachdb

    Autocomplete, Explain Plan tree, Read-only isolation

    Databricks

    databricks

    Autocomplete, DDL viewer, Text Explain, Read-only check (each statement runs on its own, with no transaction)

    Google BigQuery

    google-big-query

    Autocomplete, Read-only session (Skipped transaction locks)

    IBM Db2 LUW

    db2-luw

    Autocomplete, Text Explain, Read-only isolation

    Informix

    informix

    Autocomplete, Read-only isolation

    MariaDB

    mariadb

    Autocomplete, DDL viewer, Text Explain, Read-only session

    MS SQL Server

    mssql

    Autocomplete, XML Explain Plan tree, DDL viewer, Read-only isolation

    MySQL

    mysql

    Autocomplete, DDL viewer, Text Explain, SET TRANSACTION READ ONLY

    Oracle Database

    oracle

    Autocomplete, Text Explain, DDL generation, Read-only session

    PostgreSQL

    postgresql

    Autocomplete, DDL viewer, JSON Explain Plan tree, SET TRANSACTION READ ONLY

    SAP SQL Anywhere

    sqlanywhere

    DDL viewer, Text Explain (SELECT PLAN('…​'), the optimizer plan as text), row limit with TOP n, Read-only check (the validator refuses writes before connecting; SQL Anywhere has no read-only transaction statement, so reads run in a transaction that is rolled back: connect with a least-privileged database user)

    SAP HANA

    saphana

    Autocomplete, Read-only session

    SingleStore

    singlestore

    Autocomplete, Read-only isolation

    Snowflake

    snowflake

    Autocomplete, Read-only session, Cost prevention banner

    Sybase ASE

    sybase

    Autocomplete, Read-only isolation

    Vertica

    vertica

    Autocomplete, Read-only session

    YugabyteDB

    yugabytedb

    Autocomplete, Explain Plan tree

    Operational procedures

    Executing a query

    1. Navigate to Query Studio from the Control Plane left sidebar.

    2. In the top bar, select your Pipeline and the associated Agent connection.

    3. Write your SQL statement in the active editor tab. Autocomplete will recommend keywords, tables, and column names as you type.

    4. (Optional) Highlight a specific statement if you have multiple queries written in the same tab. Query Studio will only execute the highlighted block.

    5. Click Run or press Ctrl/Cmd + Enter.

    6. If your query contains named (e.g., :id) or positional (e.g., ?) parameters, a modal will prompt you to enter values.

    7. Once executed, the Results tab will populate. The status bar will show the row count and execution duration in milliseconds.

    Reviewing execution plans

    To analyze query performance and ensure indexes are leveraged correctly:

    1. Write your query in the editor.

    2. Switch to the Explain tab in the result pane.

    3. Click Run or use the shortcut. Query Studio runs a dialect-specific EXPLAIN request behind the scenes.

    4. The plan will render as a visual, nested tree block showing node costs, estimated rows, and scan types (or as the database’s native raw output format, such as MS SQL XML or BigQuery JSON).

    Explain takes exactly one query that starts with SELECT, WITH, or VALUES and passes the read-only check, for every user and token. Chained statements (SELECT 1; CREATE TABLE …) and EXPLAIN ANALYZE, which runs the query, are refused with the message "EXPLAIN takes a single SELECT query". The plan request runs in a transaction that is always rolled back, made read-only first where the database allows it. On Oracle, Db2, and SAP HANA, whose EXPLAIN writes rows to a plan table, those rows are rolled back too.

    Analyzing result statistics

    The Stats tab calculates statistics on the client side for the active result set:

    • Numeric columns: Displays Min, Max, Mean, Null Count, Null Percentage, and Total Row Count.

    • String columns: Displays Min Length, Max Length, Unique Value Count (calculated on result sets up to 1,000 rows), Null Count, Null Percentage, and Total Row Count.

    • Temporal/Other columns: Displays Null Count, Null Percentage, and Total Row Count.

    Exporting and copying data

    • Export CSV: Downloads the returned result set directly as a .csv file.

    • Copy as CSV / JSON: Copies the rows directly to your system clipboard.

    • Copy as INSERT: Generates standard SQL INSERT statements from the result set. When clicked, a modal prompts you to define the target table name, then places the complete insert script onto your clipboard.

    Masked PII columns are omitted or replaced in these copies the same way they appear in the grid. Unmask first (if your role allows it) only when you truly need the original values in a file. See PII discovery and masking.

    Date and time values

    Query Studio returns DATE and TIMESTAMP (without time zone) values as the wall-clock value the database holds, in ISO form without a zone: 2026-03-29 for a date, 2026-03-29T02:30:00 for a timestamp. Columns with a time zone (for example PostgreSQL timestamptz or SQL Server datetimeoffset) keep the instant form, ending in Z.

    So the grid shows the value as it is stored, whatever the time zone of your browser or of Core Hub, including a time in the hour that daylight saving time skips.

    Before 2.3.0.1, these values were sent as instants (…Z) computed in Core Hub’s time zone. The grid shifted them when the browser’s time zone differed from Core Hub’s, and a time in the daylight-saving spring-forward hour moved by an hour. If a script calls the Query Studio REST execute endpoint and parses DATE or TIMESTAMP values as instants, update it to read them as local dates and date-times.

    The same values reach Query Forge clients.

    Inserting and deleting result rows

    In-place cell editing stays as it is. The same result grid also offers two row actions: Add row and Delete row.

    When the actions are shown

    Both actions appear only when the same gate that enables cell editing passes, and the dialect allows row changes:

    • The Read-only toggle is off (Writable mode).

    • The result comes from a single table.

    • Schema discovery found a primary key for that table.

    • The connection dialect is not MongoDB. The check is case-insensitive and ignores surrounding spaces, so MongoDB, mongodb, and ` MONGODB ` are all hidden. A blank dialect also hides the actions. Any other non-blank dialect shows them.

    MongoDB does not support row insert or delete from Query Studio. This matches the separate MongoDB query mode, which is read-only (see What’s New in Gluesync 2.3).

    Both actions are disabled while a cell save, a row mutation, or a pending cell edit is in progress. Add row is also disabled while the grid is loading.

    Add row

    1. Click Add row in the result toolbar. The Add row modal opens. Its body explains that it inserts one row into schema.table, that an empty field is stored as an empty string, and to use NULL when the column should be SQL NULL.

    2. Fill in one field per column of the current result. Each field has a NULL checkbox; when checked, Query Studio writes SQL NULL for that column instead of the field value.

    3. Click Insert row, or Cancel to close without writing.

    Insert row is disabled while the insert is in progress or if the result has no columns.

    Delete row

    1. In the Actions column of the result grid, click Delete row on the row to remove.

    2. The danger modal Delete row? opens. It deletes the selected row, matched by its primary key. A primary-key value that is NULL is matched with IS NULL.

    3. Click Delete row to confirm, or Cancel.

    What gets executed

    Both actions send a parameterized INSERT or DELETE through the existing query execute route. There is no separate insert or delete API. The route’s writable permission check, QUERY_STUDIO_AUDIT record, and execution history apply to these statements exactly as they do to a statement typed in the editor (see Security, permissions, and auditing).

    On desktop, if a manual transaction is open, the success message is Row inserted. Commit to finalize the transaction. or Row deleted. Commit to finalize the transaction. The change is not final until you commit. Without a manual transaction the message is Row inserted. or Row deleted.

    Gluesync Mobile offers the same Add row and Delete row actions, with the same MongoDB hide. Mobile always auto-commits, so the success message is only Row inserted. or Row deleted.

    Messages

    Message Meaning

    Row changes are not supported for this database.

    The dialect does not allow row insert or delete from Query Studio (for example MongoDB).

    Failed to insert the row. / Failed to delete the row.

    The write did not happen. The database error follows the prefix.

    The row change was saved but refreshing the result failed.

    The INSERT or DELETE succeeded, but re-running the query to refresh the grid failed. Run the query again to see the current rows.

    Saving queries

    To store queries for future use:

    1. Click Save query (floppy disk icon) on the toolbar or press Ctrl/Cmd + S.

    2. Provide a Name and Description.

    3. Select the visibility scope:

      • Personal: Only visible to you.

      • Team/Workspace: Shared with all users authorized on the current pipeline.

    4. Add relevant tags to categorize the query.

    5. Click Save. Saved queries can be accessed anytime from the Saved Queries sub-page (/query-studio/saved).

    Spark and other MCP clients can read that same library. list_saved_queries lists queries the caller owns and queries shared with them; get_saved_query loads one, including its SQL. A personal query owned by someone else is not returned. Run it with execute_sql using the returned SQL, pipeline, and agent. Both tools have to be on the Spark agent’s allow-list. See Saved queries on the Core Hub MCP server.

    Scheduling a query with Chronos

    From Scheduler, choose the Query Studio action on a schedule, a chained event, a platform event, or a webhook-triggered event. Pick a pipeline, then a saved query (search by name) or Use a custom query. Chronos stores a SQL snapshot; when the job fires it prefers the live saved query from Core Hub and executes through the same Query Studio endpoint as the workbench (120 second timeout). Groups and entities do not apply. Write statements need I acknowledge this query can modify data.

    See Query Studio actions for the Control Plane fields, fire-time SQL resolution, and write safeguards.

    AI helper and Tab autocomplete

    Query Studio can call two dedicated Core Hub AI endpoints for the selected pipeline agent:

    • Chat helper — schema-grounded guidance and an optional SQL draft. It does not require an AI Studio Agent.

    • Tab completion — low-latency ghost text at the editor cursor.

    Both reuse the bring-your-own (BYO) LLM provider vault administered under Settings → LLM providers (same vault as AI Studio). When the vault is empty, the API returns HTTP 400 with a message that Query Studio AI requires at least one LLM provider configured by a SUPER_ADMIN.

    Control Plane interface

    • Launcher — a 52 px circular floating action button in Carbon blue (#0f62fe) carrying the white Knowledge Agent chat-bubble icon, anchored to the bottom-right corner of the Query Studio SQL editor. While the panel is open the FAB swaps to the Knowledge Agent close icon. Its accessible label toggles between Open Query Studio AI helper and Close Query Studio AI helper.

    • Panel — opening the launcher expands a wide, low chat panel horizontally over the editor, with Core Hub Carbon 2px radius. The panel sits to the left of the launcher, which stays the bottom-right anchor. Panel title Query Studio AI helper, subtitle Describe the data you need and review the proposed SQL.

    • Composer — an Ask Query Studio AI field with the placeholder Ask where data lives or describe a query…, sent with Send to AI helper.

    • Accepting a draft — the tertiary Insert into editor button applies the returned proposedSql through a Monaco edit: it replaces the current selection if there is one, otherwise it inserts at the cursor. The change is a normal editor mutation (undo/redo still apply). It never overwrites the whole buffer and never executes the statement; running it afterwards goes through the normal read-only / SafetyGate path.

    • Tab completion — the suffix-only ghost hint is rendered at the cursor. Tab accepts it and Esc dismisses it. It is additive and does not replace Query Studio’s existing discovery-backed autocomplete.

    • Empty provider vault — the panel shows its empty state only when Core Hub signals a missing or empty vault (an explicit provider-configuration code, or Hub’s wording that a SUPER_ADMIN must configure a provider). Other HTTP 400 responses stay ordinary errors and do not trigger it. The empty state is role-aware:

      • Roles that can open the LLM providers tab (SUPER_ADMIN and MANAGER) get Connect an LLM provider to use Query Studio AI. with an Open LLM provider settings action that deep-links to Settings → LLM providers (settingsTab=llmProviders).

      • Roles that cannot open that tab (for example VIEWER) get An LLM provider is not configured. Ask a SUPER_ADMIN to configure LLM providers. with no settings button, so nobody is offered a control they cannot use.

      Opening the tab is not the same as administering the vault. Vault CRUD (add, edit, remove providers) remains SUPER_ADMIN-only. A MANAGER can open Settings → LLM providers but cannot change the vault.

    Prerequisites and auth

    • Caller must send a Bearer token.

    • Caller needs Query Studio read permission (canRunQueryReadOnly).

    • Optional providerId selects a configured vault provider. If omitted, chat keeps the conversation’s previous provider when present; otherwise Core Hub uses the first configured provider (ordered by name). Completion follows the same provider resolution when providerId is omitted (no conversation binding).

    • Usage and lightweight audit events reuse AI Studio storage. These endpoints do not execute generated SQL.

    Chat helper

    POST /query-studio/pipelines/{pipelineId}/agents/{agentId}/ai/chat

    Request body:

    {
      "message": "Help me retrieve the latest orders with out-of-stock items",
      "conversationId": null,
      "editorSql": "SELECT * FROM orders",
      "selection": null,
      "providerId": null
    }
    Field Notes

    message

    Required. User prompt (1..32000 characters).

    conversationId

    Optional. Continue an existing chat for this Query Studio agent; omit to start a new conversation.

    editorSql

    Optional. Current editor SQL for grounding (max 100000 characters).

    selection

    Optional. Selected editor text (max 16000 characters).

    providerId

    Optional. Vault provider id. See provider resolution above.

    Example response:

    {
      "conversationId": "uuid",
      "message": "Use orders and order_items...",
      "proposedSql": "SELECT ...",
      "citations": [{ "schema": "public", "table": "orders" }]
    }
    Field Notes

    conversationId

    Conversation id for follow-up turns.

    message

    Assistant explanation (never claims the query was executed).

    proposedSql

    Optional read-only SQL draft extracted from the assistant reply. Never executed by this endpoint.

    citations

    Optional list of { schema, table } references grounded in the bounded schema context.

    Typical HTTP outcomes: 200 success; 400 invalid request or no LLM provider configured; 403 missing/invalid Bearer or missing Query Studio read permission; 404 agent or conversation not found; 422 LLM provider failure.

    Tab completion

    POST /query-studio/pipelines/{pipelineId}/agents/{agentId}/ai/complete

    Request body:

    {
      "sql": "SELECT o.id FROM orders o WHERE ",
      "cursorOffset": 32,
      "maxTokens": 64,
      "schemaSummary": "public.orders (id bigint PK, status varchar)",
      "providerId": null
    }
    Field Notes

    sql

    Required. Full editor buffer (max 100000 characters).

    cursorOffset

    Required. Zero-based character offset in sql.

    maxTokens

    Optional. Default 64; Hub allows 1..256.

    schemaSummary

    Optional. Bounded summary from the UI’s cached discovery metadata (max 24000 characters). Sending it avoids schema discovery on every Tab request. If omitted, Core Hub builds bounded context from existing schema/table/column discovery APIs.

    providerId

    Optional. Vault provider id.

    Example response:

    {
      "completion": "o.status = 'OPEN'",
      "replaceFrom": 32,
      "replaceTo": 32
    }
    Field Notes

    completion

    Suffix-only ghost text to insert. Not a chat reply.

    replaceFrom

    Inclusive zero-based replacement offset. Currently equals cursorOffset (insert-at-cursor).

    replaceTo

    Exclusive zero-based replacement offset. Currently equals cursorOffset (insert-at-cursor).

    Typical HTTP outcomes: 200 success; 400 invalid cursor, token limit, schema summary, or missing LLM provider; 403 missing/invalid Bearer or missing Query Studio read permission; 404 agent not found; 422 LLM provider failure.

    Safety model

    • Endpoints return drafts and ghost text only. They never execute SQL.

    • Prompts instruct the model to suggest read-only SQL (SELECT, WITH, SHOW, DESCRIBE, EXPLAIN, or VALUES) and not DDL/DML or mutating operations. This is a soft prompt restriction: Core Hub does not server-validate that proposedSql or completion is SELECT-only.

    • No Core Hub MCP catalogs or secret stores are duplicated for these calls.

    • Classified PII cells are never sent to the model. Schema context includes PII tags, not raw values. If the helper runs SQL through Core Hub, unmaskPii is forced off. Prefer the suggested Scan this schema for hidden PII prompt (classify_table / classify_schema) instead of SELECT * when looking for sensitive columns. See PII discovery and masking.

    • Settings → LLM providers — BYO LLM provider vault shared with AI Studio. Setup (including Ollama) lives in AI models and LLM providers. Agent builder and Connect hosted-chat policy live in AI Studio; do not duplicate them here.

    • Executed-statement RBAC and QUERY_STUDIO_AUDIT behaviour remain under Security, permissions, and auditing; AI draft endpoints never write to the database.

    Security, permissions, and auditing

    Because direct SQL access is a sensitive capability, Gluesync applies strict protection mechanisms on database sessions, user operations, and audits.

    Role-based access control (RBAC)

    Access to Query Studio is controlled by a robust permission matrix. Permissions are verified both at the UI layer and at the API route layer:

    Role read-only queries Writable queries Manage personal saved queries Share saved queries View query audit logs Unmask PII in results

    SUPER_ADMIN

    ✅ Allowed

    ✅ Allowed

    ✅ Allowed

    ✅ Allowed

    ✅ Allowed

    ✅ Allowed

    MANAGER

    ✅ Allowed

    ✅ Allowed

    ✅ Allowed

    ✅ Allowed

    ❌ Restricted

    ✅ Allowed

    VIEWER

    ✅ Allowed

    ❌ Restricted

    ✅ Allowed

    ❌ Restricted

    ❌ Restricted

    ❌ Restricted

    EXTERNAL_MODULE [1]

    ❌ Restricted

    ❌ Restricted

    ❌ Restricted

    ❌ Restricted

    ❌ Restricted

    ❌ Restricted

    Roles decide what a user can do in Query Studio. To limit which schemas and tables a user can reach on each connection, use Query Data Access.

    Query Data Access

    A SUPER_ADMIN sets Query Data Access per user and per connection. The policy applies on top of the user’s role and is enforced on Query Studio, the AI helper, and MCP/Spark SQL.

    • Connection mode: OPEN (default) leaves the connection unrestricted for that user. Restricted limits the user to the schema and table rules you add. Rules allow or deny; deny always wins.

    • The User, Connection, and Template fields pick which matrix you are editing. They do not tie a user to a single connection: the same user can have a separate policy on every connection.

    • The same matrix can carry PII unmask grants. See PII discovery and masking.

    Restrict a user on several connections

    Option A: one connection at a time

    1. Select the user in User.

    2. Pick the first database in Connection.

    3. Set Connection mode to Restricted and add the schema and table rules.

    4. Click Save policy.

    5. Change Connection to the next database and repeat steps 3 and 4. Each connection needs its own Save policy.

    Option B: one template for all connections

    1. For each connection, configure the matrix and click Save as template. Use the same template name every time.

    2. Select the user, choose that template in Template, and click Apply template.

    3. Click Save policy.

    Apply template copies every connection matrix stored in the template onto the user in one step.

    Apply template also removes any connection policies the user already has that the template does not include. Make sure the template covers every connection the user needs.

    Built-in templates

    VIEWER and MANAGER are built-in default templates and are read-only. To share one custom matrix across connections, save it under your own template name.

    Connection isolation & pool management

    • Dedicated pool: Query Studio creates a separate, isolated read-only HikariCP connection pool (keyed as qs-[agentId]) distinct from Gluesync’s CDC replication pool. This prevents ad-hoc user queries from starving thread pools or delaying active synchronizations.

    • Auto-eviction: Idle connection pools are automatically evicted and closed after 5 minutes of inactivity to release database resources.

    • Connection caps: Pools default to a maximum size of 3 active connections and a minimum of 1 connection.

    Destructive query safeguards

    • Read-only toggle: The toolbar contains a Read-only toggle. When active (default), queries run in Read-only mode.

    • Writable mode requirements: Disabling the Read-only toggle (enabling writable access) is restricted to users with canRunQueryWritable capabilities (SUPER_ADMIN or MANAGER). Destructive operations executed in this mode will be run directly against the database, triggering a prominent warning notification in the UI.

    Read-only mode

    Query Studio runs a query read-only whenever the caller may only read:

    • the Read-only toggle is on;

    • the user’s role cannot run writable queries, such as VIEWER;

    • the request uses a Personal API Token with a read-only scope (gluesync.api_read, gluesync.mcp_read, or gluesync.jdbc_gateway): see Read-only scopes;

    • the AI helper, AI SQL, and Visualize run it;

    • it is an Explain.

    Three layers stop a read-only query from writing.

    1. The read-only check

    Before the query reaches the database, Query Studio checks it. A refused query fails with a message that starts with "Read-only mode forbids", followed by what it found (for example "Read-only mode forbids 'DELETE' statements").

    • A statement must start with SELECT, WITH, VALUES, SHOW, DESCRIBE, or EXPLAIN of a read. The query a CTE runs, and the statement after the CTEs, must be reads too, so a data-modifying CTE is refused.

    • Refused anywhere in a statement: SELECT … INTO, row locks (FOR UPDATE, FOR SHARE, LOCK IN SHARE MODE), advancing a sequence (NEXT VALUE FOR, Db2’s NEXTVAL FOR), and the denied functions below.

    • Query Studio reads the query the way its database does: comments, quoted names, and string literals in each database’s syntax, including dollar quotes and E'…' strings on PostgreSQL, backslash escapes and # comments on MySQL and MariaDB, and [names], GO, and statements chained without ; on SQL Server and Sybase. MySQL’s executable comments (/*! … */) are refused.

    • A keyword counts only where a statement can start, never as a column or alias name: SELECT merge FROM t, SELECT 1 AS set, a column named load, and MySQL’s INSERT() and TRUNCATE() functions are reads.

    Denied functions, however their name is quoted or cased:

    Database Denied

    PostgreSQL and compatible

    dblink*, pg_terminate_backend, pg_cancel_backend, pg_read_file, pg_read_binary_file, pg_ls_dir, pg_stat_file, lo_import, lo_export, lo_unlink, pg_reload_conf, pg_rotate_logfile, and the effects a read-only transaction does not stop: set_config, pg_stat_reset*, pg_stat_statements_reset, pg_advisory_* and pg_try_advisory_*, pg_sleep*

    SQL Server and Sybase

    OPENQUERY, OPENROWSET, OPENDATASOURCE, OPENXML, xp_cmdshell, sp_executesql, sp_OACreate, sp_OAMethod, sp_configure; EXEC and WAITFOR in any position; the UPDLOCK, XLOCK, TABLOCK, TABLOCKX, and HOLDLOCK hints

    2. A read-only transaction

    Where the database has one, the query runs in a read-only transaction on the connection that runs it, and the transaction is rolled back afterwards. The database then refuses any write, including one a function makes.

    Database Read-only transaction Statement

    PostgreSQL, YugabyteDB, CockroachDB, Amazon Redshift

    Yes

    SET TRANSACTION READ ONLY

    Oracle

    Yes (an Explain runs in a rolled-back transaction instead, because EXPLAIN PLAN writes PLAN_TABLE)

    SET TRANSACTION READ ONLY

    MySQL, MariaDB

    Yes

    START TRANSACTION READ ONLY

    SQL Server, Sybase ASE, Db2, Snowflake

    No: the read-only check and the database user’s privileges are the defence

    —

    SAP HANA, Vertica, SingleStore, ClickHouse, and others

    Not enabled: the read-only check and the database user’s privileges are the defence

    —

    3. Least privilege for the agent’s database user

    The read-only check refuses the writes it can recognise. A database user that cannot write also stops the ones it cannot, such as a function with side effects, a procedure, or a feature of a newer server. For the database user each source or target agent connects with:

    • Grant only what the pipeline needs: SELECT on the replicated tables (plus the CDC objects for a source), and the write privileges a target needs on its own tables. Do not use an owner, sa, root, postgres, SYSTEM, or DBA account.

    • Grant no server-level roles or privileges:

      • SQL Server: no sysadmin, serveradmin, securityadmin, or CONTROL SERVER; keep xp_cmdshell and Ad Hoc Distributed Queries disabled;

      • PostgreSQL: no superuser, no pg_read_server_files, pg_write_server_files, pg_execute_server_program, or pg_signal_backend, and no EXECUTE on dblink;

      • MySQL and MariaDB: no FILE, SUPER, PROCESS, or SHUTDOWN;

      • Oracle: no DBA and no ANY privileges.

    • Where Query Studio users may read but the pipeline does not need to write, prefer a source agent, or a separate read-only connection, whose user has SELECT only.

    Query auditing

    Every statement executed in Query Studio is logged to the QUERY_STUDIO_AUDIT schema. The database audit records:

    • The authenticated user’s identity (OIDC subject or local email).

    • The targeted agent and pipeline identifiers.

    • A SHA-256 hash of the complete SQL text.

    • A truncated 200-character SQL text preview.

    • Read-only status, execution status (completed, error, cancelled, timeout).

    • Execution timestamps, duration (in milliseconds), and row count.

    Audit records are retained for 90 days by default, and can only be queried or viewed by users possessing SUPER_ADMIN privileges.

    Working with large datasets

    To avoid memory exhaustion (OOM) on both the Gluesync Core Hub JVM and the user’s browser, Query Studio implements several data mitigation policies:

    • Hard row limit: The backend enforces a server-side cap (defaulting to 10,000 rows) for all executed queries. If the database returns more rows than the limit, the server truncates the result set, flags the response as truncated, and shows a warning in the UI.

    • Client-side page size: The result grid defaults to 200 rows, allowing smooth DOM rendering. Users can configure the page size up to 5,000 rows.

    • Virtualized grid: The result data grid is virtualized using react-window, rendering only the visible viewport. This enables scrolling performance at 60 fps even when displaying large datasets.

    Troubleshooting

    Tables or schemas are missing from the tree

    • Cause: The database connection parameters might restrict schema access, schema filters are applied in the agent configuration, or your Query Data Access policy on that connection is Restricted.

    • Resolution: Verify database user privileges and ask a SUPER_ADMIN to check your policy for that connection (see Query Data Access). You can also trigger a manual refresh on the Schema Browser to force a metadata synchronization.

    Query fails with a timeout error

    • Cause: The statement is scanning a large unindexed table, or the database is under heavy load. The default Query Studio client timeout is 30 seconds.

    • Resolution: Use the Explain panel to check the query path. Avoid scanning massive tables without selective WHERE clauses.

    Transactions are blocked or read-only errors occur

    • Cause: You tried to perform a mutation (INSERT, UPDATE, DELETE, CREATE) while the query ran in Read-only mode: the Read-only toggle was active, your role cannot run writable queries, or your Personal API Token has a read-only scope. The message starts with "Read-only mode forbids".

    • Resolution: Ensure you are authorized as a MANAGER or SUPER_ADMIN, toggle the toolbar mode to Writable, and try again. A token with a read-only scope cannot run writable queries; use a token with gluesync.api.

    • Cause: A read is refused because it uses a denied function, a row lock, or a sequence (see Read-only mode).

    • Resolution: Remove that part of the query. These are refused for every read-only query.

    Add row or Delete row is missing from the result grid

    • Cause: The gate described in Inserting and deleting result rows did not pass: the Read-only toggle is on, the result spans more than one table (joins, aggregations), schema discovery found no primary key, or the connection is MongoDB (or reports a blank dialect).

    • Resolution: Switch to Writable, run a single-table SELECT on a table with a primary key, and check the schema browser shows the key columns. MongoDB does not support row insert or delete from Query Studio.

    Best practices

    • Default to Read-only: Keep writable mode disabled unless performing planned database maintenance or debug corrections.

    • Use parameter binding: Leverage named parameters (:value) rather than hardcoding constants. This makes query templates reusable and allows saving them for your team.

    • Optimize Snowflake / BigQuery / Databricks scans: Be cautious when querying metered analytical data warehouses. Running broad SELECT * scans can incur significant infrastructure query costs. Use narrow WHERE filters and strict LIMIT clauses.

    • Validate mappings locally first: When verifying pipeline sync values, start with a small, specific key lookup before querying general table counts.

    • Treat PII as masked by default: Keep PII visible off unless you need original values. Scan or mark columns before sharing a result set or asking the AI helper about a table. See PII discovery and masking.


    1. Chronos automation uses the scheduler-module JWT, not this ordinary external-module Query Studio role.