Writing queries

Open a query tab with ⌘ T. Whatever you type is saved as a draft for that connection and comes back next time.

Running

⌘ ↵ runs:

• the selection, if you selected something;
• otherwise the statement under the cursor, when the tab holds several (the button says Run Statement);
• otherwise the whole tab.

To run a whole script, select all and press ⌘ ↵, or use Create… › Run SQL file… in the sidebar.

Stop cancels a running query. Queries time out after 30 seconds by default; change it in Settings › Data & queries › Query timeout (0 turns it off).

Each statement that returns rows gets its own result tab, Result 1, Result 2 and so on.

Run in a transaction

⌥ ⌘ ↵ wraps everything in BEGIN and COMMIT. If any statement fails, all of them roll back. Only the last result is shown.

ClickHouse has no transactions, so there the statements run one by one without rollback. On Trino it depends on the connector.

Parameters

Write :name anywhere a value goes:

SELECT * FROM orders WHERE customer_id = :customer AND status = :status

Before running, Lily asks for each value. Numbers go in as they are; anything else is quoted. PostgreSQL casts like ::date are not mistaken for parameters, and neither is text inside strings or comments. For saved queries, Lily remembers the last values.

Autocomplete

Suggestions appear after two characters, or when you press Esc. Columns of the tables in your FROM and JOIN come first, then other tables and columns, then SQL keywords. Pick with ↑ ↓ and ↵.

Snippets

Type a trigger and press Tab:

Trigger	Expands to
sel	SELECT * FROM
selw	SELECT * FROM … WHERE
cnt	SELECT COUNT(*) FROM
ins	INSERT INTO … VALUES
upd	UPDATE … SET … WHERE
del	DELETE FROM … WHERE
jl	LEFT JOIN … ON
ji	INNER JOIN … ON
grp	GROUP BY
ord	ORDER BY
lim	LIMIT
with	WITH cte AS (…)

Add your own in Settings › Query library › Snippets. Use $0 in the template to mark where the cursor lands. A custom trigger with the same name replaces the built-in one.

Formatting and editing

• ⇧ ⌘ F formats the SQL. Settings › Appearance › Auto-format query when run formats the query every time you run it, except when you run a selection.
• ⌘ L comments or uncomments the line.
• ⌥ ⌘ F finds text in the editor.
• ⌘ =, ⌘ - and ⌘ 0 zoom the editor. The default size is in Settings › Appearance.
• Curly quotes are straightened as you type.

SQL files

⌘ O opens a .sql file into a new tab, and ⌥ ⌘ S saves the tab as one.

Create… › Run SQL file… runs a whole script. Choose Roll everything back to run it as one transaction, or Keep going, report failures to run each statement on its own. Failed statements can be opened in the editor to fix them. On ClickHouse every statement always runs on its own.

When a query fails

The error card shows the server's message, with Copy error, Reconnect and, if AI is set up, Fix with AI. When a table or column name looks misspelled, Lily suggests the right one.

Before a write runs

Writes you run here are checked first: Lily counts the rows they will touch, shows some of them, and asks you to confirm. The riskiest ones ask you to type the table name. See Guardrails.

The editor toolbar also has Explain, the visual plan and the AI menu. See EXPLAIN and slow queries and AI in the editor.