Queries

A query asks your Library a question in SQL: how many notes have each status, which notes nobody links to, how much work each person has open. Terra keeps an index of your notes in a small SQLite database, and a query reads it through a few public views. Queries only read; they can't change a note. For questions you can answer with conditions, a database is simpler; reach for a query when you need counts, sums, joins or a chart over time.

Write and run a query

1. Choose File › New Query, or click + (New query) beside Queries in the sidebar.
2. Type a name over Untitled query.
3. Write a SELECT in the editor. The Schema list shows the views and their columns; click a view to see its columns.
4. Press ⌘Return, or click Run query in the tools above the editor.

SELECT status, count(*) AS notes
FROM v_notes
WHERE instr(path, 'projects/field-guide/') = 1
GROUP BY status
ORDER BY notes DESC

Tools › Run Query runs the query in the focused pane. If a result has an id, note_id or note column, click a row to open that note.

To have the SQL written for you, describe what you want in Describe the query you want… and click Generate, or press ⌘Return in that line. It needs an AI provider (see Ask your Library), sends only the list of views and columns, and checks the statement before it reaches the editor. Read it before you run it. Queries are always SQLite; the dialect menu beside Generate doesn't change that.

What you can read

• created and updated are timestamps such as 2026-10-16T09:00:00Z; wrap them in date() to group by day.
• In v_frontmatter, text and dates are in value_text, numbers in value_number, checkboxes in value_bool (1 or 0), and lists such as tags in value_json.
• word_count is always 0 for now.
• In v_blocks, kind is heading, paragraph, callout, list, listItem, quote, codeBlock, table, embed, image or hr. For a Sprout, callout_kind holds its kind, such as tip.

The rules

A query is one SQLite SELECT, optionally starting with WITH. Everything else is refused before it runs:

• Only the views above. Notes' own files and Terra's other tables are out of reach.
• One statement, with no INSERT, UPDATE, DELETE, CREATE or PRAGMA.
• Functions from this list only:

LIKE and GLOB count as functions and aren't on the list, so they fail with an error such as not authorized to use function: like. Match text with instr() instead:

SELECT id, title
FROM v_notes
WHERE instr(lower(title), 'pricing') > 0

A query in the Query surface stops after 3 seconds and returns at most 10,000 rows.

See the results as a table, sheet or chart

Click View: Results in the tools and choose Query (the SQL), Results, Sheet or Chart.

• Sheet opens the results as a spreadsheet, with Save sheet to Library and Download sheet as XLSX.
• Chart draws a Line, Bar, Area, Scatter or Heatmap chart. It starts with the first text column across and the first number column up; change them with Chart settings (the gear). Cumulative turns each value into a running total, which is how you draw a burndown: see Chart a burndown.

The chart's type and settings aren't saved with the query yet, so set them again after you reopen it.

Save and reuse queries

Click Save query in the tools. Terra checks the statement first, then lists the query under Queries in the sidebar and stores it in your Library as .terra/queries/<id>.json. To rename it, change the name at the top and save again. The sidebar can change a query's icon and copy its id, but can't rename or delete it yet.

The other tools:

Open as Database captures the notes as they are now; the database doesn't follow the query afterwards. A live query in a note isn't possible yet: export the results, or embed the database. Every variable you add must appear in the SQL, or the run stops with Invalid parameter name.

Make a database from SQL

A database can collect its notes with SQL instead of conditions, and stays live. Open the database's View preferences, choose the Database tab, and switch Match by to SQL (the <> button). Write a SELECT that returns an id or note_id column:

SELECT n.id
FROM v_notes n
JOIN v_tags t ON t.note_id = n.id
WHERE t.tag = 'field-guide'

Any other columns the statement returns show up as columns in the database. The same rules apply, with a 5-second limit. Turn on Cache the result to keep the last answer until you click Refresh now or edit the statement, which helps when the line under it says the query is slow.

Examples

Notes edited this month, newest first:

SELECT id, title, updated
FROM v_notes
WHERE updated >= date('now', 'start of month')
ORDER BY updated DESC

The 20 most used tags:

SELECT tag, count(*) AS notes
FROM v_tags
GROUP BY tag
ORDER BY notes DESC
LIMIT 20

Notes nothing links to:

SELECT n.id, n.title
FROM v_notes n
WHERE NOT EXISTS (SELECT 1 FROM v_backlinks b WHERE b.note_id = n.id)

Open effort per owner, from three properties:

SELECT o.value_text AS owner, sum(e.value_number) AS effort
FROM v_frontmatter o
JOIN v_frontmatter e ON e.note_id = o.note_id AND e.key = 'effort'
JOIN v_frontmatter s ON s.note_id = o.note_id AND s.key = 'status'
WHERE o.key = 'owner' AND s.value_text != 'Done'
GROUP BY owner

Sprouts by kind:

SELECT callout_kind, count(*) AS sprouts
FROM v_blocks
WHERE kind = 'callout'
GROUP BY callout_kind

Related

• Databases — live collections built from conditions, or from SQL.
• Run a project — a burndown chart from a query.
• Search syntax — find notes without writing SQL.
• Build a personal dashboard — counts and charts on one page.