Working with data

SQL editor

Write and run queries with autocompletion and formatting, handle transactions, save your queries and understand their execution plan.

#Open an editor

Pick a connection, database or schema in the tree and click “New query” or Ctrl+T. The query runs on the database and schema chosen above the editor; you can change them there.

#Run

Each statement that returns rows opens its own result in the bottom panel; messages and affected rows go to “Messages”. “Stop” halts the run: the remaining statements don't run.

A result shows the first rows. To go through a whole table, open it from the tree with Enter or a double-click: the grid loads more rows as you scroll.

#Statements that delete

Before running DROP, TRUNCATE, DELETE or UPDATE without WHERE, or something that replaces an existing object, Qrulla shows what it will do and on which connection, and asks you to confirm. In MongoDB and Redis it warns about the equivalents (dropping a collection, FLUSHDB…).

With the Max plan, on production connections it also shows how many rows will change before running, and any UPDATE or DELETE from the editor can be undone afterwards.

#Transactions

If you run BEGIN (or your engine's equivalent), the editor shows “Open transaction” until you end it with COMMIT or ROLLBACK. If you close the tab, switch databases or disconnect with a transaction open, Qrulla warns you: its changes are rolled back.

Under “Isolation level” you pick this tab's level (Read Committed, Repeatable Read, Serializable…); it applies from the next run.

#Write faster

• Autocompletion of tables, columns and keywords as you type. If you created new objects, click “Refresh names for autocompletion”.
• Format: Ctrl+Shift+F tidies the SQL, or just the selection. If the SQL looks incomplete (unclosed quotes or parentheses), it leaves it alone so its meaning doesn't change.
• Design query: build a SELECT by dragging tables and ticking columns. See Design.

#Save queries

Ctrl+S saves the query with a name in its database's “Queries” folder in the tree. From there you open, rename or delete it. It's saved on your computer; nothing changes on the server.

#History

The “History” button opens, in the right panel, the queries you ran on that connection, with a search box. “Use in the editor” brings one back. The history is kept only on your computer. The Developer plan shows the last 10; Advanced and Max, everything.

#Execution plan

“Plan” (Ctrl+Shift+E) shows how the server would run the current statement or the selection, without running it: each step with its cost and estimated rows, the costliest step highlighted and full table reads marked.

With the Max plan, “In plain words” explains it in sentences, for example: “Reads the whole orders table to keep the rows that match the filter. An index on customer_id would avoid reading all of it”.

#See a result as a chart

In a result, “Chart” draws it as bars, lines, area, ring or scatter. You pick the categories, one or more series and their summary (sum, count, average…). “Add to a report…” saves it in a report.

#MongoDB and Redis

• MongoDB: write commands as in its shell, one per line or separated by ;, for example db.customers.find({age: {$gt: 30}}).sort({name: 1}).
• Redis: one command per line, for example HGETALL customer:1.

#Query across connections

Max plan. Join tables from different connections in one query, even from different engines. Copy tables in the tree (Ctrl+C) from one or more connections and open “File → New query across connections”. The tables are copied to a temporary database on your computer, deleted when you close the tab, and queried as connection.table in SQLite's SQL:

SELECT c.name, sum(o.total)
FROM sales.customers c
JOIN shop.orders o ON o.customer = c.id
GROUP BY c.name

Something missing or unclear? Write to support@qrulla.com.