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
EXPLAINquery 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
INSERTscript 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:
-
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.
-
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.
-
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.
-

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 |
|
Autocomplete, Text Explain |
CockroachDB |
|
Autocomplete, Explain Plan tree, Read-only isolation |
Databricks |
|
Autocomplete, DDL viewer, Text Explain, Read-only check (each statement runs on its own, with no transaction) |
Google BigQuery |
|
Autocomplete, Read-only session (Skipped transaction locks) |
IBM Db2 LUW |
|
Autocomplete, Text Explain, Read-only isolation |
Informix |
|
Autocomplete, Read-only isolation |
MariaDB |
|
Autocomplete, DDL viewer, Text Explain, Read-only session |
MS SQL Server |
|
Autocomplete, XML Explain Plan tree, DDL viewer, Read-only isolation |
MySQL |
|
Autocomplete, DDL viewer, Text Explain, |
Oracle Database |
|
Autocomplete, Text Explain, DDL generation, Read-only session |
PostgreSQL |
|
Autocomplete, DDL viewer, JSON Explain Plan tree, |
SAP SQL Anywhere |
|
DDL viewer, Text Explain ( |
SAP HANA |
|
Autocomplete, Read-only session |
SingleStore |
|
Autocomplete, Read-only isolation |
Snowflake |
|
Autocomplete, Read-only session, Cost prevention banner |
Sybase ASE |
|
Autocomplete, Read-only isolation |
Vertica |
|
Autocomplete, Read-only session |
YugabyteDB |
|
Autocomplete, Explain Plan tree |
Operational procedures
Executing a query
-
Navigate to Query Studio from the Control Plane left sidebar.
-
In the top bar, select your Pipeline and the associated Agent connection.
-
Write your SQL statement in the active editor tab. Autocomplete will recommend keywords, tables, and column names as you type.
-
(Optional) Highlight a specific statement if you have multiple queries written in the same tab. Query Studio will only execute the highlighted block.
-
Click Run or press
Ctrl/Cmd + Enter. -
If your query contains named (e.g.,
:id) or positional (e.g.,?) parameters, a modal will prompt you to enter values. -
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:
-
Write your query in the editor.
-
Switch to the Explain tab in the result pane.
-
Click Run or use the shortcut. Query Studio runs a dialect-specific
EXPLAINrequest behind the scenes. -
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
.csvfile. -
Copy as CSV / JSON: Copies the rows directly to your system clipboard.
-
Copy as INSERT: Generates standard SQL
INSERTstatements 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 ( |
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
-
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 SQLNULL. -
Fill in one field per column of the current result. Each field has a NULL checkbox; when checked, Query Studio writes SQL
NULLfor that column instead of the field value. -
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
-
In the Actions column of the result grid, click Delete row on the row to remove.
-
The danger modal Delete row? opens. It deletes the selected row, matched by its primary key. A primary-key value that is
NULLis matched withIS NULL. -
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 |
Saving queries
To store queries for future use:
-
Click Save query (floppy disk icon) on the toolbar or press
Ctrl/Cmd + S. -
Provide a Name and Description.
-
Select the visibility scope:
-
Personal: Only visible to you.
-
Team/Workspace: Shared with all users authorized on the current pipeline.
-
-
Add relevant tags to categorize the query.
-
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 betweenOpen Query Studio AI helperandClose Query Studio AI helper. -
Panel — opening the launcher expands a wide, low chat panel horizontally over the editor, with Core Hub Carbon
2pxradius. 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
proposedSqlthrough 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.
Tabaccepts it andEscdismisses 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_ADMINmust configure a provider). Other HTTP400responses stay ordinary errors and do not trigger it. The empty state is role-aware:-
Roles that can open the LLM providers tab (
SUPER_ADMINandMANAGER) getConnect 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) getAn 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. AMANAGERcan 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
providerIdselects 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 whenproviderIdis 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 |
|---|---|
|
Required. User prompt (1..32000 characters). |
|
Optional. Continue an existing chat for this Query Studio agent; omit to start a new conversation. |
|
Optional. Current editor SQL for grounding (max 100000 characters). |
|
Optional. Selected editor text (max 16000 characters). |
|
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 |
|---|---|
|
Conversation id for follow-up turns. |
|
Assistant explanation (never claims the query was executed). |
|
Optional read-only SQL draft extracted from the assistant reply. Never executed by this endpoint. |
|
Optional list of |
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 |
|---|---|
|
Required. Full editor buffer (max 100000 characters). |
|
Required. Zero-based character offset in |
|
Optional. Default |
|
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. |
|
Optional. Vault provider id. |
Example response:
{
"completion": "o.status = 'OPEN'",
"replaceFrom": 32,
"replaceTo": 32
}
| Field | Notes |
|---|---|
|
Suffix-only ghost text to insert. Not a chat reply. |
|
Inclusive zero-based replacement offset. Currently equals |
|
Exclusive zero-based replacement offset. Currently equals |
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, orVALUES) and not DDL/DML or mutating operations. This is a soft prompt restriction: Core Hub does not server-validate thatproposedSqlorcompletionis 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,
unmaskPiiis forced off. Prefer the suggested Scan this schema for hidden PII prompt (classify_table/classify_schema) instead ofSELECT *when looking for sensitive columns. See PII discovery and masking.
Related
-
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_AUDITbehaviour 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 |
|---|---|---|---|---|---|---|
|
✅ Allowed |
✅ Allowed |
✅ Allowed |
✅ Allowed |
✅ Allowed |
✅ Allowed |
|
✅ Allowed |
✅ Allowed |
✅ Allowed |
✅ Allowed |
❌ Restricted |
✅ Allowed |
|
✅ Allowed |
❌ Restricted |
✅ Allowed |
❌ Restricted |
❌ Restricted |
❌ Restricted |
|
❌ 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
-
Select the user in User.
-
Pick the first database in Connection.
-
Set Connection mode to Restricted and add the schema and table rules.
-
Click Save policy.
-
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
-
For each connection, configure the matrix and click Save as template. Use the same template name every time.
-
Select the user, choose that template in Template, and click Apply template.
-
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. |
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
canRunQueryWritablecapabilities (SUPER_ADMINorMANAGER). 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, orgluesync.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, orEXPLAINof 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’sNEXTVAL 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 namedload, and MySQL’sINSERT()andTRUNCATE()functions are reads.
Denied functions, however their name is quoted or cased:
| Database | Denied |
|---|---|
PostgreSQL and compatible |
|
SQL Server and Sybase |
|
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 |
|
Oracle |
Yes (an Explain runs in a rolled-back transaction instead, because |
|
MySQL, MariaDB |
Yes |
|
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:
SELECTon 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, orDBAaccount. -
Grant no server-level roles or privileges:
-
SQL Server: no
sysadmin,serveradmin,securityadmin, orCONTROL SERVER; keepxp_cmdshelland Ad Hoc Distributed Queries disabled; -
PostgreSQL: no superuser, no
pg_read_server_files,pg_write_server_files,pg_execute_server_program, orpg_signal_backend, and noEXECUTEondblink; -
MySQL and MariaDB: no
FILE,SUPER,PROCESS, orSHUTDOWN; -
Oracle: no
DBAand noANYprivileges.
-
-
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
SELECTonly.
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_ADMINto 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
WHEREclauses.
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
MANAGERorSUPER_ADMIN, toggle the toolbar mode to Writable, and try again. A token with a read-only scope cannot run writable queries; use a token withgluesync.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
SELECTon 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 narrowWHEREfilters and strictLIMITclauses. -
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.
scheduler-module JWT, not this ordinary external-module Query Studio role.