Chapter 7

Write SQL

The SQL editor knows your schema. It suggests tables and columns as you type, runs one statement or a whole script, streams results without freezing, and charts them if you like.

The editor#

To open an SQL editor, press Ctrl+E or click SQL in the toolbar. Ctrl+Enter anywhere outside an editor opens one too. Each editor is a tab; its text is saved as you type and comes back when you reopen Rowan.

An SQL editor with two named statements, and the first one's result below it in a tab named Latest paid orders — An SQL editor. The run bar is at the bottom; each statement's result gets a tab.

The editor highlights SQL, numbers its lines, and indents for you. Typing (, ' or " adds the closing character, and typing it again steps past it. Settings for the font, tab size and more are in Settings > Editor and Fonts & Colors. Ctrl+= and Ctrl+- change the editor's text size.

Comments, case and folding#

Ctrl+/ comments or uncomments the current lines with --. Ctrl+Alt+/ wraps the selection in /* */.
The editor's right-click menu has Transform > Make Upper Case, Make Lower Case and Capitalize.
Click the chevron beside a line number to fold a statement that spans several lines. Fold All and Unfold All are in the right-click menu. A .sql file remembers its folds.

An editor with one statement folded to a single line ending in an ellipsis — A folded statement. Click the ellipsis, or move the cursor into it, to unfold it.

Run statements#

Run Current Query (Ctrl+Enter): Runs the statement the cursor is in, or the selection if you've selected some text.
Run All Queries (Ctrl+Shift+Enter): Runs every statement in the editor, one after another.

The run button does whichever you choose as its default: click its arrow and choose Default. The same menu has both runs, Add to Queries…, Save SQL As and Change Shortcut Keys….

While a query runs, the run button becomes Cancel (Ctrl+.), which opens the process list with your query already selected, so you can stop it. The window never freezes, however long the query takes.

When a statement fails#

Rowan marks the failing line in the editor and shows the server's message, with its detail and hint, in the result.

An editor with line 4 tinted red and an error saying column stauts does not exist, with a hint — The error points at the line. The hint suggests the column you probably meant.

When you run several statements, a failed one is reported and the rest still run. To stop at the first failure instead, turn on Return on error in the run bar's SQL Editor Settings (the gear), or in Settings.

Results, result tabs and pinned results#

Each statement that returns rows gets a result tab, named Query 1, Query 2 and so on. To give one a name, put a comment directly above the statement:

-- name: Latest paid orders
SELECT id, status, total
FROM orders
WHERE paid
ORDER BY id DESC;

-- label: and -- tab: work too. Hover over a result tab to see what its statement did.

Pin a result to keep it while you run other queries: right-click its tab and choose Pin This Result. Pinned results stay until you unpin or close them.
To put every result in one tab instead, choose Append in One Tab under Result View Mode in the run bar's gear. Each statement then appears in turn with its name, what it did, and its first rows. Scripts of over 100 statements always use this.
Ctrl+` hides and shows the results pane.
Ctrl+F on a result searches it; see Search and replace in results.

The Message view#

Each result has Data, Message and Chart views. Message shows what the statement did (such as UPDATE 3), how long it took, and anything the server said while it ran: RAISE NOTICE output, warnings and hints. Export… saves the messages as a text file.

A statement that raises notices also adds one entry to Notifications.

Row limits and Load More#

Results stream in a page at a time, so even a ten-million-row SELECT starts showing at once and never fills memory.

The Limit chip in the run bar caps how many rows a query returns, per connection: from 100 up to 500,000, or No limit. When a result reaches the limit, it says Limited to 1,000 rows with Change Limit.
With a higher limit, or none, Rowan shows the first page and a Load More button for the next.

A result ending with Limited to 1,000 rows and a Change Limit button — A result that reached the row limit.

The Chart view#

Switch a result to Chart to draw it as a Line Chart, Bar Chart or Pie Chart. Choose the x-axis and one or more y-axis columns (for a pie, a label and a value) from the menus above it. Rowan starts with the first text column across and the numbers up.

A bar chart of revenue by month, January to December — A bar chart of monthly revenue. Hover over a bar to see its value.

To open a result as a chart straight away, put a chart comment above the statement:

-- linechart x: month, y: orders
SELECT to_char(month, 'YYYY-MM') AS month, orders
FROM monthly_revenue
ORDER BY 1;

Use barchart or linechart with x: and one or more y:, or piechart with label: and value:.

A line chart of orders per month over two years — A line chart, from a chart comment.

A ring chart of revenue by product category with the total in the middle — A pie chart is drawn as a ring with the total in the middle. Past ten slices, the rest are grouped as Other.

A chart draws the rows that are loaded, and says so. Chart All Rows loads the rest, up to 100,000 rows; beyond that, group or filter in the query.
Export saves the chart as a PNG image.
Add to Metrics Board puts the chart and its query on a Metrics Board.

Autocomplete#

As you type, Rowan suggests what fits where the cursor is, from a copy of your schema it keeps in memory. Suggestions never wait on the server.

Suggestions after typing o. in SELECT o. FROM orders o, listing the columns of orders with their types — After `o.`, Rowan lists the columns of the table `o` stands for, with their types.

After	Rowan suggests
`FROM`, `JOIN`, `UPDATE`, `INTO`	Tables and views, then schemas
`schema.`	The tables in that schema
`alias.` or `table.`	That table's columns
`SELECT`, `WHERE`, `ON`, `GROUP BY`, `ORDER BY`, `SET`	Columns of the tables in the statement, then functions, then keywords
The start of a statement	Keywords and snippets

Suggestions open after two letters, right after a ., or when you press Ctrl+Space. Press Tab or Enter to accept one, and Esc to close the list.
Matches rank by prefix first, then word starts (cid finds customer_id), then letters in order. Columns of tables in your statement, and names you use often, rank higher.
Aliases, CTEs (WITH x AS (…)) and subqueries in FROM are understood, so their columns are suggested too.
Names are folded the way PostgreSQL folds them, and names that need quotes, such as "Order Items", get them.
A SET search_path you run in a tab is taken into account; tables outside the path are inserted with their schema.
Functions are inserted as name() with the cursor inside, and a hint shows their arguments, with the current one in bold.

After JOIN customers c, the first suggestion is ON o.customer_id = c.id — After a JOIN, Rowan offers the join condition from the foreign key.

After JOIN customers c, a space offers the whole condition, such as ON o.customer_id = c.id, worked out from the foreign keys. In a select list, typing * (or o.*) offers the columns it stands for, so you can expand it. Lists that open on their own like these take Tab only, so Enter still starts a new line.

A signature hint showing order_total(p_order bigint) returning numeric — The signature hint while typing a function's arguments.

Keyword case follows Settings > Editor: UPPER, lower, or As Typed, where the first letter you type decides.

Snippets#

At the start of a statement, these triggers expand to whole statements, with the cursor where you'll type next:

Type	To get
`sel`	`SELECT * FROM`
`selc`	A `SELECT count(*)`
`selw`	A `SELECT` with a `WHERE`
`ins`, `upd`, `del`	An `INSERT`, `UPDATE` or `DELETE`
`cte`	A `WITH … AS (…)` query
`case`	A `CASE WHEN … END` expression, anywhere in a statement

Saved queries with keywords appear in suggestions too, with a star.

Multiple carets#

Edit several places at once:

Ctrl-click to add a caret; Ctrl-click it again to remove it.
Alt-drag to select a rectangle, with a caret on each line.
Ctrl+Alt+Up and Ctrl+Alt+Down add a caret above or below.
Ctrl+D selects the word at the cursor, then each next match. Ctrl+Shift+L selects every match.
Esc goes back to one caret.

Every occurrence of status selected in an editor, with a caret at each — Every `status` selected with `Ctrl+Shift+L`. Typing now changes them all.

Typing, deleting, pasting and accepting a suggestion happen at every caret, and undo takes them back in one step.

Find and replace#

Press Ctrl+F to find, or Ctrl+H to find and replace. Every match is tinted and the count reads, for example, 2 of 3.

F3 or Ctrl+G finds the next match, and Shift+F3 the previous one. Enter and Shift+Enter do the same in the field.
Turn on Match Case, Whole Word or .* for regular expressions. With regular expressions, $1 to $9 put groups into the replacement, and \n matches a line break.
Replace changes one match; Replace All changes them all in one undo step. Esc goes back to the editor.

Beautify and uglify#

Ctrl+I, or the wand in the run bar, formats the selection, or the statement at the cursor, with consistent indentation and keyword case. Ctrl+Shift+I does the opposite and puts it on one line, turning -- comments into /* */.

-- Before
select id, total from orders where paid and total > 10 order by total desc

-- After Beautify
SELECT id,
       total
FROM orders
WHERE paid
  AND total > 10
ORDER BY total DESC

Choose the layout in Settings > Editor > Format SQL: Standard, or Tabular Left and Tabular Right, which line clause keywords up in a margin. You can also set keyword case and whether commas lead or trail.

Query parameters#

With query parameters on, placeholders in a query ask for their values when you run it.

Click the run bar's gear and choose Query Params Options > Enable Query Params, or turn on Enable query params in Settings > Editor.
Write the query with placeholders, such as :status.
Run it. The Variables sheet asks for each value; press Enter to move to the next, and Ctrl+Enter or Apply to run.

SELECT id, status, total
FROM orders
WHERE status = :status
  AND total > :min_total
ORDER BY total DESC;

The Variables sheet asking for status and min_total before running the query — The Variables sheet. The values you gave last time are filled in.

The formats are :name, %name%, ?, ${name}, $name and {{name}}. Choose one with Change Query Params Format.
Values are sent as bound parameters, never pasted in, so a value can never change your statement. Type them as you would in SQL: 'shipped' loses its quotes, NULL is a null, and numbers go as typed.
Placeholders in comments, quoted names and dollar-quoted bodies are ignored, as are casts like ::int. To look inside quoted strings too, turn on Look inside quoted strings.
Cleanup forgets saved values this query doesn't use.

Note

Because values are bound, a value can't be SQL, such as a table name or now() - interval '1 day'. Write the expression in the query and bind only the value.

Saved queries, folders and keywords#

Saved queries are .sql files that Rowan lists in the left sidebar's Queries view, in folders. Each connection has its own, and All Connections holds queries for every connection.

The Queries view with Reports and Cleanup folders, a shared Team Queries folder, and a saved query open in a tab — Saved queries in folders. A query's keywords show beside it.

Save a query#

Click the star in the run bar, or choose Add to Queries… from the run button's menu. To save just the selection or the statement at the cursor, choose Add to Queries… from the editor's right-click menu.
Enter a Name, and optionally a Folder and Keywords.
Turn on Available on all connections to share it across connections, then choose Save.

The tab is then linked to the query. With Automatically save queries while editing on (the default), your edits are written to its file as you type; otherwise the tab shows a dot until you save it, and asks when you close it. Renaming the tab renames the query.

Use saved queries#

Double-click a query to open it. Right-click it for Run, Copy, Edit/Rename…, Open in Tab, Insert into SQL Editor, Show in Explorer and Delete.
Type a query's keyword in any editor and it's offered first, with a star. Press Tab to insert it.
Drag a query onto a folder to move it. Deleted queries go to the Recycle Bin.
To share a folder with your team, right-click empty space and choose New > Add a Shared Folder to Queries, then pick a folder in Dropbox, OneDrive or a network share.
The settings button has Sort By (Name, Date Created, Date Modified) and Auto Save Queries.

The Edit Query sheet with name, folder, keywords and the query's SQL — Edit/Rename changes a query's name, folder, keywords and SQL in one place.

Since they're plain files, you can edit saved queries in any editor; Rowan notices the change.

Query history#

Every statement you run is saved in the left sidebar's History view, with when it ran and whether it failed. Commits are logged too, with the old values of changed rows.

The History view listing statements by day, with one failed SELECT marked — History, newest first. Search it with the field at the top.

Double-click an entry to insert it into a new editor tab.
Right-click an entry for Run, Copy, Save As…, Insert into SQL Editor, Add to Queries…, Delete and Clear All History.
Delete removes the selected entry.

.sql files and the SQL Viewer#

Ctrl+O opens a .sql file in a tab linked to it. Ctrl+Shift+S saves the tab to its file, asking where if it has none; Save SQL As… always asks. File > Open Recent lists your last ten files.
A file opened from outside the Queries folder behaves like any document: it shows a dot while edited and asks before closing.
Rowan keeps each file's own line endings, Windows or Unix.

The SQL Viewer opens a .sql file in a window of its own, without a connection: for reading and editing scripts, with highlighting, find and replace, and beautify. Nothing runs there. Open it from the welcome window with Open SQL File…, or choose File > New SQL Viewer for an empty one.

The SQL Viewer window showing a monthly revenue script — The SQL Viewer, for scripts you want to read or edit without running.

Editable results#

You can edit a query's result like a table when every column comes from one table, that table has a primary key, and the key is in the result. Rowan asks PostgreSQL where each column came from, so it knows rather than guesses.

A result from customers with an edited name tinted, and the commit control in the toolbar — Editing a result. The change is staged with the rest of the workspace's changes.

Double-click a cell, press F2, or start typing. Columns the query works out, such as total * 2, stay read-only.
Right-click for Set Value, Duplicate, Delete and Restore Row, and every copy format.
Edits are staged with the workspace's other changes: Preview, Ctrl+S and Ctrl+Z cover them all.

If a result can't be edited, double-clicking it says why in the status pill: more than one table, a view, no primary key, or which column to add to the SELECT.

Search and replace in results#

Press Ctrl+F on a result, or click its search button, to find rows by one column or any column, with Contains, Not contains, Has prefix or Has suffix, and Match Case. Rows that don't match are hidden. In an editable result, Replace and Replace All stage the changes for you to commit.

The result search bar finding ship in the status column, with dispatched as the replacement — Search a result, then replace what it found.

Vim mode#

If you prefer Vim's keys, choose Vim for Key binding in Settings > Editor. Every editor switches at once.

An editor in Vim's Visual Line mode with two lines selected, and VISUAL LINE shown in the run bar — Vim mode shows the current mode in the run bar.

Normal, Insert, Visual and Visual Line modes, with motions, counts, operators and text objects (ciw, da(, yi"), registers, . to repeat, / and ? search, and :s substitutions.
:w saves the tab's file. Ctrl+Enter runs in every mode, and Ctrl+C and Ctrl+V still copy and paste.
Outside Insert, Ctrl+D, Ctrl+U and Ctrl+R are Vim's; Settings lets you give each back to Rowan.
Vim: use the system clipboard makes yank and put use the Windows clipboard.
Open rowan.vimrc in Settings opens a settings file for mappings (such as imap jk <Esc>) and options like set relativenumber, ignorecase, smartcase and hlsearch. Saved changes apply at once.