Skip to content

Matches page titles and page text. Forty pages, indexed at build time.

Using the data browser and SQL editor

Open a table, follow a foreign key, run a statement, and export the result. What each control does, what read-only means, and what to do when something is refused.

Every service has a table browser and a SQL editor built in. They are on the service's own page, as the Tables and SQL editor sections, and they talk to your database over our private network rather than over the internet.

Both run as your own database role — the role your applications authenticate as, the one your GRANT statements are written against. Your privileges are the boundary. A statement that your role has no privilege for fails in PostgreSQL, with PostgreSQL's own error, exactly as it would from psql.

Every statement either of them runs is recorded in your audit log, with the statement text, its duration, how many rows it touched and whether it succeeded.

Opening them

There are three ways in, and all of them land on the same two sections.

  1. On the organisation home, each card under Your services carries Tables and SQL editor.
  2. On Services, each row's Actions column carries Tables and SQL editor.
  3. On a service's own page, the Your data panel on Overview has Browse tables and Open SQL editor, and the section strip has Tables and SQL editor directly after Overview.

A link straight to a section works too: …/services/<service id>?tab=data opens Tables and ?tab=sql opens the SQL editor.

If neither appears, see When the sections are not there below.

Browsing a table

  1. Open Tables. The left column is headed Schema and lists what your role can see: your own schemas first, then pg_catalog and information_schema, both marked system and collapsed. Anything your role has no privilege on is not in the tree at all.
  2. Select a table. It opens on its rows, because that is what people come for. The View control above the grid switches between Data and Structure.
  3. Read the header line above the grid. It names the key the page is ordered by. A table with a primary key or a unique index pages by that key. A table with neither pages by ctid, and the page says so, because ctid moves when the table is rewritten and the page you are on can shift under you.
  4. NULL and an empty string are drawn differently, always. A cell showing nothing at all is an empty string; NULL is marked.
  5. Select a cell to open it in full. JSON is pretty-printed, arrays are listed, bytea is shown as a size and a short hex preview rather than a screenful of escapes, and timestamps carry their original text.

Filtering and sorting

A filter is a column, an operator from a fixed set, and a value. The value is sent as a bound parameter, never pasted into the statement, so a quote in your search term is a quote in your search term. If you want a predicate the filter set cannot express, write it in the SQL editor, which is one click away.

Sorting is by a column the browser names, and the sort is part of the key the page is ordered by. That is why the set of sortable columns is smaller than the set of columns.

Following a foreign key

This is the part worth knowing about.

  1. On any row, select Follow in the last column.
  2. The popover has two groups. References is what this row points at. Referenced by is what points at this row — the direction most tools leave out, and the one you want before a DELETE.
  3. Select a constraint. References opens the single row being referenced. Referenced by opens the referencing rows, already filtered by that key.

A composite key carries every column, so a follow on (order_id, line_no) lands on the row that matches both. A key whose column is NULL in this row is not offered as a link: the popover says which constraint it is and that the row references nothing through it, rather than showing a control that would open an empty page.

Changing a row

Editing, inserting and deleting are available only in write mode, one row at a time, and the statement is shown to you before it runs. A table with no primary key and no unique index is read-only in the browser: an UPDATE matched on every column is an UPDATE that can hit a duplicate.

Running a statement

  1. Open SQL editor.
  2. The line above the editor is the session. It names the database you are connected to, the mode — Read-only or Write mode — and the three limits in force: the statement timeout, the row cap and the result-size cap. They are never behind a settings panel.
  3. Type your statement. Two or more characters of a name offers completions read from this database's catalogue, so they match what is actually there. The arrow keys move through them and Enter accepts one.
  4. Select Run, or press Ctrl/Cmd + Enter. Ctrl/Cmd + ] indents. Tab moves to the next control rather than inserting a tab, so the editor never traps the keyboard.
  5. The result appears below, with the number of rows and the server-side duration of that one run.

A buffer with several statements produces one result per statement, in order, each with its own timing and its own audit entry. It stops at the first failure and keeps the results before it. Selecting text and running runs exactly the selection.

Read-only, and turning it off

Every session starts read-only, and read-only is enforced by the database rather than by us: each statement runs inside a read-only transaction, so a write fails with PostgreSQL's own read_only_sql_transaction error.

Turn on write mode is per session. It is not remembered: the next session starts read-only again, and you turn it on again. That is deliberate. The cost of asking again is one click, and the cost of a remembered write mode is a DELETE somebody thought was a SELECT.

Write mode needs a role that holds it. Owners and administrators do; developers do on projects they belong to; viewers do not.

Statements we ask you to confirm

A DROP, a TRUNCATE, or an UPDATE or DELETE with no WHERE clause is confirmed before it runs. The confirmation names what it will affect and estimates how many rows, from EXPLAIN. It is labelled as the planner's estimate, because that is what it is.

A statement we cannot classify is treated as destructive and confirmed as well. There is no "do not ask again", and there will not be one.

Explain, cancel and export

  • Explain shows the plan without running the statement.
  • Explain analyse runs the statement. On a data-modifying statement in write mode, it takes the same confirmation as running it would.
  • Cancel sends PostgreSQL's cancel request to the backend running your statement. Closing the browser tab does not: use Cancel.
  • Export streams CSV or JSON from the statement, not from the rows on screen. A query that returned more than the row cap exports all of it. CSV writes NULL as an empty field, and says so in the file, because CSV cannot tell the two apart.

Saved queries and history

Saved queries have a name and a visibility of private or shared with your organisation. Opening one loads it into the editor. It never runs it.

History is your own last hundred statements on this service, with their outcome and duration. It is readable by you and by nobody else, including an owner. Clear empties it completely.

Troubleshooting

The result says fewer rows than you expected

The row cap stopped the read. The note under the result says so and names which cap it was — rows or bytes. Both are shown above the editor and both can be raised within your plan's ceiling. To get everything, use Export, which streams from the statement rather than from the grid.

"This editor session has ended."

Sessions end after fifteen minutes with no statement, after four hours whatever you were doing, and when you sign out. Run again and a new session opens.

"Too many editor sessions are open for this service."

Editor sessions have their own small budget, separate from the connections your applications use, so that a browser tab cannot take a connection slot from your production traffic. Close a tab you have finished with, or wait: an idle session ends after fifteen minutes.

"This session is read-only."

Either write mode is off — turn it on above the editor — or your role does not hold it. A viewer can read every table the managed role can read and cannot write to any of them.

A statement timed out

The timeout is PostgreSQL's, not a deadline we apply after the fact, so the statement really was cancelled and the backend really is gone. Raise the timeout above the editor, up to your plan's ceiling, or make the query cheaper. Explain is free and will usually say which.

When the sections are not there

Two reasons, and they read differently.

The switch is off for this service. Tables and SQL editor are absent from the section strip, and the Your data panel on Overview says so. An owner or an administrator turns it back on from the service's Configuration tab. It is off per service, not per person.

Your role does not hold the capability. The sections are there and say which role holds what you are missing. Schema reading, data reading and running a statement are three separate capabilities, so an organisation can let a role browse tables without letting it open the editor.

The IP allowlist does not apply here

The editor reaches your database from inside Balta, not from your network, so an address that is not on a service's IP allowlist can still be used to open one. Who may is decided by roles and capabilities instead, and every statement is in your audit log. If that is not acceptable for a particular service, turn the editor off for it on its Configuration tab.