Chapter 9

Tools

Beyond browsing and editing, Rowan has tools for the rest of the job: see what's running, manage users, find a value anywhere, draw your schema, back up and restore, move data in and out, and watch it all on a Metrics Board.

Most tools open their own connection to the server, so they work even while a long query runs in your workspace, and they all go through Safe Mode before changing anything.

Process list#

The process list shows the server's sessions and what each is running. Press Ctrl+., or choose Tools > Process List.

Each row shows the process ID, database, user, application, client address, when its query started, its state and the query. Show idle includes sessions that aren't running anything. Refresh reads the list again.
Kill cancels the selected queries, leaving their sessions connected.
Terminate Session… ends the selected sessions, rolling back anything they haven't committed.
Right-click for Copy, Copy Selected Cells and Copy Rows As, or open a long query in Quick Look.

When you open the list while your own query is running, by pressing Ctrl+. or the editor's Cancel, your query's process is already selected: press Kill to stop it.

User management#

Choose Tools > User Management… to see the server's roles and change them.

General: Username, Password, Valid until, Connection limit, Can log in, and the roles it's a Member of.
Global Privileges: Whether the role can create databases or roles, is a superuser, can replicate, bypasses row-level security, and inherits its groups' privileges.
Table Privileges: Pick a schema, then a table (or the whole schema), then a column (or the whole table). Move privileges between Available Privileges and Granted Privileges with the arrow buttons or a double-click.

The Table Privileges tab for the orders table with available privileges such as SELECT and INSERT — Table Privileges: what the role holds on a table, and what it could be granted.

Changes wait until you choose Apply: edited roles show in orange, new ones in italics, and roles to delete are struck through. Show SQL lists exactly what Apply will send, all in one transaction. New User adds a role.

Note

Passwords never leave Rowan as themselves. Rowan sends only a SCRAM-SHA-256 verifier, the way psql's \password does, so the password never appears in History, the console or the server's logs.

Search in database#

To find a value wherever it is, choose Tools > Search in Database…, or Search in Database… from the sidebar's ⋯ menu. The search takes the place of the sidebar until you choose Done.

Type the value, and choose how to match it: Contains, Not contains, =, Has prefix or Has suffix. Turn on Match Case if case matters.
Choose Search all items, or Select items and name the tables (orders, or billing.invoices for one schema's).
Choose Start Search. Rowan counts matching rows table by table; Pause and Resume as you need.

Click a table in the results to open it filtered on Any column, so you see exactly the rows that were counted. Searching a large database reads every table, so it adds load to the server; Rowan reminds you under the button.

ER diagram#

The diagram draws a schema's tables and the foreign keys between them. Choose Tools > Show Diagram, or right-click a table and choose Show Diagram. It opens in a tab, one schema at a time; pick another from the schema menu at its top left.

Move around#

Drag a table to move it. Rowan remembers where you put each one.
Drag empty space, or hold the middle mouse button and drag anywhere, to pan.
Ctrl and the mouse wheel, or Zoom In and Zoom Out, zoom from 20% to 300%. Reset Zoom goes back to 100%.
Zoom to Fit shows every table at once.
Sort by name or column count, or Rearrange to put joined tables together.
Ctrl+F searches table names; Up and Down move through the matches and Enter centers one.

The whole schema shown at once after Zoom to Fit — Zoom to Fit puts every table in view.

Edit from the diagram#

Changes made in the diagram are staged like any other and committed with Ctrl+S.

Click a table for its settings: Name (rename it), Color, and links to its Structure and Index, or Delete Table.
Add Table, on the diagram's toolbar or its right-click menu, adds a new table with an id key.
To add a foreign key, drag from the middle of a table's left or right edge to another table. Rowan guesses the columns from their names, such as customer_id, and you can choose On Update and On Delete.
Right-click a line and choose Delete to drop the keys between two tables.

The foreign key popover from audit_log to orders, with Columns order_id, Referenced Columns id, and On Update and On Delete set to NO ACTION — Drawing a key from audit_log to orders. Rowan has matched order_id to orders.id.

New things are drawn dashed in blue, changed tables in orange and dropped ones in red until you commit. The diagram's toolbar has its own undo and redo.

Export the diagram#

Choose Export on the diagram's toolbar to save the whole diagram, not just what's in view, as a PNG (at twice the size, for sharp printing) or an SVG.

Backup and restore#

Rowan backs up and restores with PostgreSQL's own pg_dump and pg_restore. Rowan includes them for every supported PostgreSQL version, so there's nothing else to install. Choose Backup Database… or Restore Database… in the welcome window, or File > Backup… and File > Restore… in a workspace.

The Backup window with the connection list on the left and version, options and file name on the right — The Backup window. Pick a connection and database, then choose Start Backup.

Back up a database#

Choose the connection on the left, then the database.
Rowan picks the tool Version matching the server. Change it only if you need to.
Set the Options. Backups start with --format=custom, which pg_restore reads; Add Option… lists the others.
Set the File name. Customize… builds it from {connection}, {database}, {date} and {time}. For plain SQL dumps, Compress file using Gzip makes a .sql.gz.
Choose Start Backup and pick a folder.

The tool's output scrolls by as it works. Stop ends it at once, and a stopped or failed backup leaves no half-written file. When it's done, Rowan shows the file and its size.

Restore a backup#

Choose the connection and the database to restore into, or New Database… to create one.
Check the options. Restores start with --single-transaction, so a failure changes nothing.
Choose Start Restore and pick the backup file.

A restore always asks first, and wants the database password on Safe Mode 1 and 2. Read-only connections can't be restored to. A plain .sql dump isn't a pg_restore archive; import it instead, with From SQL Dump.

Backups and restores work over SSH and SSL just like your connections. Each runs as a background job: choose Run in Background to close the window and carry on.

Import#

Import CSV, JSON or a SQL dump into a table. Choose File > Import > From CSV…, From JSON… or From SQL Dump…, or right-click a table in the sidebar and choose Import to import into it. Pick the file, and the import sheet opens.

CSV and JSON#

Encoding starts at Auto-detect, which shows what it found. Every common encoding is listed, and over a hundred more.
For CSV, set the Delimiter, Quote, and whether the First line is header. An unquoted empty field becomes NULL; a quoted empty one is an empty string.
JSON can be an array of objects, JSON Lines, or an object holding an array ({"data": [...]}). Numbers arrive exactly as written, and nested values go in as JSON.
Choose the target table, or Create new table, named after the file, with Detect data types from the first 100 rows.
For an existing table, the Matching strategy pairs file columns with table columns by name and order, by name, or by order. Above each preview column, choose its target or Do not import.
Stop on error (the default) or Update on duplicate, which updates rows whose primary key is already there.

Importing a CSV into a new table, with a type chosen above each column — Into a new table: Rowan detects the types and you can change them.

SQL dumps#

For a SQL dump, the sheet shows the start of the file, highlighted, and runs it in one transaction unless you turn that off. Large dumps stream through in pieces, and pg_dump's COPY blocks are sent as data.

The SQL dump import showing the start of a seed script with INSERT and UPDATE statements — Importing a SQL dump.

Imports run on a connection of their own, in one transaction: a bad row stops it and nothing is kept. Progress shows rows and a bar; Stop rolls everything back. Choose Import in Background to close the sheet while it runs.

Export#

The export wizard writes tables and views to files. Press Ctrl+Shift+E, choose File > Export Tables…, or click the export button in a table's bottom bar.

Check the tables and views to export. With one checked, choose which of its columns to include.
From a table tab, choose All rows, Filtered rows or Current page. Rows come out in the grid's sort order.
Choose a format and its options (below).
Set the file name pattern with Customize…, from {connection}, {database}, {schema}, {table}, {tag}, {date} and {time}.
Choose Export… and pick a folder. Several files go in a folder of their own inside it.

CSV: Delimiter, quote, line break, decimal point, encoding, Convert NULL to EMPTY, line breaks to spaces, field names in the first row, and a byte-order mark for Excel.
JSON: An array of objects for each table, with numbers, booleans and JSON columns typed.
SQL: One file with each item's Structure, a Drop first if you want, and its Data as INSERT statements. Compress the file using Gzip writes .sql.gz.
Excel: A workbook per table, with numbers, booleans and dates as real Excel values, and the header bold and frozen. Dates as text keeps exact values such as microseconds.

The export wizard set to SQL with Structure, Drop and Data columns for each item and Gzip on — An SQL export of tables from two schemas.

Files are written in place only when the whole export succeeds, so a failure or Stop leaves nothing half-written. Rowan asks before replacing a file. When it's done, Open Folder opens the folder.

In an SQL editor, Ctrl+Shift+E exports the current result straight to a file. Export Result… in a result's menu opens the wizard for one result or every result tab.

Background jobs and notifications#

Every import, export, backup and restore is a job. The bell in the toolbar counts jobs that are running or finished but not yet seen; it turns red if one failed. Click it to see them all.

The Notifications popover listing a running import with a progress bar, a backup, a finished export, a stopped import and a failed restore — Notifications: running jobs with progress and Stop, finished ones with their result.

Running jobs show their progress and a Stop button.
Finished jobs show their result or error. Exports and backups have Open Folder; imports have Open, which opens the table.
Clear removes finished jobs.
When a job ends while Rowan is behind another app, Windows shows a notification and the taskbar button flashes.

Server notices from your queries appear here too. Jobs last until you quit Rowan; closing a window with a job running asks first.

Metrics Board#

A Metrics Board is a page of live charts, numbers and tables, each driven by a query. Click Metrics in the toolbar, or choose Tools > Show Metrics Board. It opens in a tab; Database takes you back.

Boards#

Your boards are listed on the left. Right-click for New…, Rename…, Duplicate, Delete, and Import Metrics Board… and Export Metrics Board… to share a board as a file. Boards are saved as you change them.

Objects#

Choose Add on the board's toolbar, or right-click the canvas, to add:

Data table: The query's rows.
Scoreboard: One big number with a detail line, from the first row.
Bar chart, Line chart, Pie chart: The same charts as the SQL editor's Chart view.
Input field, Dropdown, Button: Controls that feed values to other objects. A dropdown's choices can be a fixed list or come from a query.
Exporter, Importer: Export a query's rows to CSV, JSON, SQL or Excel, or open the import wizard for a table.
Connection: Runs the objects linked to it on another saved connection and database.

Drag an object's title to move it, and its edges or corners to resize it; both snap to the grid. Hold the middle mouse button and drag to pan the board. Lock stops moving and resizing, Grid shows the dot grid and links, and Status pauses or starts all refreshing.

Settings and refresh#

Click an object's title for its settings: its Label, its SQL, which columns go where, and its Refresh rate, from every second to every 30 minutes, Refresh on Event, or Do not auto refresh. Choose Done to apply them.

The settings for a bar chart object with its label, SQL, axes and refresh rate — An object's settings.

Links and inputs#

Drag from a control's round handle to an object to link them. When the control sends its value (on Enter, when editing ends, when the value changes, or on an event), linked objects run again. In their queries, write $name for the value of the linked control called name:

SELECT count(*) AS tables
FROM pg_class
WHERE relname LIKE $pattern

Values are bound as parameters, so typing into an input can never change the query. For the rare case where a value must be SQL, such as a column to sort by, a control can be set to Insert as SQL text.

Safe Mode on the board#

Boards run on connections of their own, never your workspace's, and refreshing pauses while the board's tab isn't showing. An object is never started again while its last run is still going. A query that changes data never runs on a schedule while Safe Mode is on; refreshed by hand, it asks with the level and password of the connection it runs on.

To put a chart from the SQL editor on a board, choose Add to Metrics Board in its Chart view.