Querying & inspecting

This page covers reading what is in your project: the rows in a table, the shape of the schema, full SQL queries, and the plan the database uses to run a query. Examples use the example library.

Viewing rows

show data prints a table’s rows:

show data books

┌─────────┬───────────────────────────┬───────────┬───────────┬────────────────┐
│ book_id │ title                     │ author_id │ published │ isbn           │
├─────────┼───────────────────────────┼───────────┼───────────┼────────────────┤
│       1 │ A Wizard of Earthsea      │         1 │      1968 │ 978-0553383041 │
│       2 │ The Left Hand of Darkness │         1 │      1969 │ 978-0441478125 │
│       3 │ Invisible Cities          │         2 │      1972 │ 978-0156453806 │
│       4 │ Kindred                   │         3 │      1979 │ 978-0807083697 │
└─────────┴───────────────────────────┴───────────┴───────────┴────────────────┘

Add a where clause to filter, and limit to cap the number of rows:

show data books where published > 1968

┌─────────┬───────────────────────────┬───────────┬───────────┬────────────────┐
│ book_id │ title                     │ author_id │ published │ isbn           │
├─────────┼───────────────────────────┼───────────┼───────────┼────────────────┤
│       2 │ The Left Hand of Darkness │         1 │      1969 │ 978-0441478125 │
│       3 │ Invisible Cities          │         2 │      1972 │ 978-0156453806 │
│       4 │ Kindred                   │         3 │      1979 │ 978-0807083697 │
└─────────┴───────────────────────────┴───────────┴───────────┴────────────────┘

The where grammar is the same one used by update and delete — see Inserting & editing data.

Inspecting the schema

show table describes one table’s columns, relationships, and indexes (see Columns and Relationships for the full output). The plural forms give a project-wide overview:

show tables

Tables (4):
  authors
  books
  loans
  members

show relationships

Relationships (3):
  books_author: authors.author_id → books.author_id on delete cascade
  loans_book: books.book_id → loans.book_id on delete cascade
  loans_member: members.member_id → loans.member_id on delete cascade

show indexes

Indexes (1):
  books.idx_books_author (author_id)

The singular show relationship <name> and show index <name> show one item in detail — show relationship draws the two-table diagram covered in Relationships.

Querying in advanced mode

Advanced mode runs full SQL select, including projections, order by, joins, group by, set operations, and with (CTEs). The richer query features — distinct, having, set operations, subqueries, CTEs, and case / cast — have their own page: SQL queries. A projection of two columns, newest first:

select title, published from books where published > 1968 order by published desc

┌───────────────────────────┬───────────┐
│ title                     │ published │
├───────────────────────────┼───────────┤
│ Kindred                   │      1979 │
│ Invisible Cities          │      1972 │
│ The Left Hand of Darkness │      1969 │
└───────────────────────────┴───────────┘

A join follows a relationship to combine rows from two tables — here, each book with its author’s name:

select authors.name, books.title from books
  join authors on books.author_id = authors.author_id
  order by authors.name

┌───────────────────┬───────────────────────────┐
│ name              │ title                     │
├───────────────────┼───────────────────────────┤
│ Italo Calvino     │ Invisible Cities          │
│ Octavia E. Butler │ Kindred                   │
│ Ursula K. Le Guin │ A Wizard of Earthsea      │
│ Ursula K. Le Guin │ The Left Hand of Darkness │
└───────────────────┴───────────────────────────┘

Add group by with an aggregate to summarise — how many books each author has:

select authors.name, count(*) as book_count from books
  join authors on books.author_id = authors.author_id
  group by authors.name
  order by book_count desc

┌───────────────────┬────────────┐
│ name              │ book_count │
├───────────────────┼────────────┤
│ Ursula K. Le Guin │ 2          │
│ Octavia E. Butler │ 1          │
│ Italo Calvino     │ 1          │
└───────────────────┴────────────┘

Query plans

Prefix a query with explain to see how the database would run it, drawn as an annotated tree. explain never runs the statement — it only reports the plan — so it is safe even over a delete:

explain show data books where author_id = 1

SELECT "book_id", "title", "author_id", "published", "isbn" FROM "books" WHERE "author_id" = 1
└─ SEARCH books USING INDEX idx_books_author (author_id=?)

SEARCH … USING INDEX means the query found its rows through an index instead of reading the whole table. A filter on an un-indexed column shows a SCAN instead. In advanced mode, explain also wraps a select, with, insert, update, or delete.

Syntax

show data <Table> [where <expr>] [limit <n>]
show table <Table>
show tables | show relationships | show indexes
show relationship <Name> | show index <Name>
select (* | <expr> [as <alias>][, ...]) from <Table> [join …] [where <expr>] [group by …] [order by <expr> [asc|desc]] [limit <n>]
explain show data <Table> [where <expr>]
explain <select | with | insert | update | delete …>

See also Indexes, Inserting & editing data, and the Tables reference.