The workspace
Once you connect, the workspace opens. The connection bar along the top holds every open connection, so you can keep several databases open and switch between them. The + next to the last one opens another.
SQL engines (PostgreSQL, MySQL, SQLite, SQL Server, Trino, ClickHouse) share the layout below. Redis, MongoDB and DynamoDB have screens of their own, described on their engine pages.
The sidebar
From top to bottom:
- Reload schema (⌘ R) and the Tools menu: Understand this schema, Investigate, ER Diagram, Search a value across the database, Health check, Activity, and Close connection.
- Three quick buttons: Understand (the AI map), Map (the ER diagram) and Create… (new table or schema, export the schema as SQL, run a SQL file, and the schema and data diffs).
- Dashboards, with a count.
- The database and schema pickers.
- Filter tables…, the table list, your favorites and, on PostgreSQL and MySQL, the database objects.
Database and schema
The database picker is a breadcrumb. It is called Catalog on Trino. The schema picker filters the table list; it does not change your SQL or close your tabs. The ? next to them explains what each level means for the engine you are on.
| Engine | First level | Second level |
|---|---|---|
| PostgreSQL, SQL Server | Database | Schema |
| MySQL, ClickHouse | Database (same thing as a schema) | none |
| Trino | Catalog | Schema |
| SQLite | Database file button | Attached database |
Switching database closes your tabs. If a tab has unsaved edits, Lily asks first.
Tables
Each table shows a row count. On PostgreSQL and MySQL the counts come from the catalog in one query, so they are estimates; other engines count each table as it scrolls into view. Click the chevron to see the columns and their types. Views have an eye icon.
Hover a table's icon and click the star to add it to Favorites, which stay at the top of the list.
Right-click a table for templates (Copy as SELECT, Copy as INSERT skeleton, Copy as UPDATE skeleton), Move to another engine…, Change with AI…, and the schema actions.
Database objects
On PostgreSQL the sidebar also lists Functions, Procedures, Triggers, Sequences and Enums; on MySQL, functions, procedures and triggers. Click one to read its definition.
Tabs
A tab is a query, a table, a notebook or a dashboard. The + button opens a new query tab (⌘ T); its menu also creates and opens notebooks and dashboards.
- Drag tabs to reorder them.
- Right-click a tab to Pin tab, Close other tabs or Close all tabs. Pinned tabs stay first and survive both.
- A spinner means a query is running, a dot means unsaved edits, and a check or an exclamation mark means a result you have not looked at yet.
Query text and open tables are saved per connection and come back the next time you connect. With Settings › Workspace › Reopen last connections on launch on (the default), Lily reconnects to what you had open.
If a connection drops after sleep or a long idle, Lily reconnects by itself and keeps your drafts and staged edits. A write whose outcome is unknown is never sent again on its own.
Finding things
- Global search (⇧ ⌘ O) finds tables and columns by name across every loaded schema.
- Search a value across the database (in Tools) looks for a value in every column of every table, then opens the matching table already filtered to the rows it found. It searches up to 500 tables.
- Semantic search, in Understand this database, finds tables by what they mean: where do we store refunds?
- Filter tables… in the sidebar matches table and column names.
Command palette
⌘ K opens the palette. From it you can switch to an open connection, connect to a saved one, open a table by name, start a new query tab or connection, and reopen saved queries and your last 25 history entries.