Editing data
You can edit a table in the grid when it has a primary key and the connection is not read-only. Otherwise the bottom bar shows a lock and says why. Grid editing works on PostgreSQL, MySQL and MariaDB, SQLite, SQL Server and ClickHouse; Trino tables are always read-only.
Redis, MongoDB and DynamoDB have their own editors, described on their engine pages.
Staging changes
Nothing is written until you save. Until then, every change is staged and marked in the grid.
- Edit a cell: double-click it, or right-click and choose Edit. Enum columns offer their values in a menu.
- Set NULL and Revert cell are in the cell's right-click menu.
- Add a row: click Add Row in the bottom bar. If the primary key is a UUID column (or its values look like UUIDs), Lily fills in a new one; integer and serial keys are left for the database.
- Duplicate a row: right-click it and choose Duplicate row. The key is left empty, or gets a new UUID.
- Delete rows: right-click and choose Delete row. With several rows selected, the menu acts on all of them. Undo delete brings them back before you save.
The bottom bar counts what is pending, for example 3 edited · 1 new · 2 deleted.
Saving
Press ⌘ S or click Save. Discard throws the staged changes away.
- If any rows are deleted, Lily lists them and asks you to confirm. Deleting every row loaded in the grid asks you to type the table name.
- Otherwise, with Settings › Data & queries › Preview SQL before committing edits on (the default), you see the exact statements and click Apply.
- Everything runs in one transaction: deletes, then updates, then inserts, each matched by primary key. If one statement fails, nothing is changed, your staged edits stay, and the error shows above the grid.
On ClickHouse, updates and deletes are sent as ALTER TABLE … UPDATE and ALTER TABLE … DELETE mutations, and since ClickHouse has no transactions they run one by one.
Binary values
Values that cannot round-trip through a text field, like most binary columns, cannot be edited in the grid.