Editing table data

When data is shown in a grid you can edit it in place — click a cell and type a new value. For anything that does not fit comfortably in a cell there are editors, reached by right-clicking the cell.

An editor that does not apply to the column you clicked is greyed out: Numeric Editor appears only for a numeric column, Image Editor only for a blob, and so on. Text, JSON and blob columns also grow a small … button in the focused cell, which opens the editor the column's type calls for without going through the menu.

Text Editor

For text columns — a CHAR or VARCHAR with no size, or any value long enough to be awkward in a cell. Load and Save read and write the value as a file; Word wrap turns wrapping on and off; the status bar carries the caret position and the length of the value.

JSON Editor

For JSON values, whether they are stored as text or as a JSONB blob. The value is shown with line numbers and syntax colouring. A JSONB value opens already laid out; a text value opens exactly as it is stored, and Format indents it for reading. The status bar says which of the two storage forms the value uses and whether what you have typed is valid JSON.

The menu opens the JSON Editor on any text cell, whatever it holds. The status bar says whether what is there is valid JSON and OK will not save it until it is, so a text value that is not JSON yet can be turned into one here — and an empty cell opens for the same reason, which is how a JSON value gets entered where there was none. A blob is the one exception: one that is not JSON cannot be shown at all, so the editor declines it and says to set the cell to null first, which leaves an empty cell to open on.

Numeric Editor

For numeric columns. There is no larger form of a number the way there is of a paragraph or a picture, so what this editor offers instead is somewhere to work the value out: a calculator, dropped down and ready as the dialog opens.

The calculator works in floating point, which cannot tell consecutive whole numbers apart above about 9,000,000,000,000,000. Rather than quietly round a key or an identifier that large, the editor refuses to open on one and says so — edit such a cell directly instead.

Image Editor

For a blob holding a picture. Load and Save exchange it with a file, Copy and Paste with the clipboard, and Open and Open with… open it in a program that can show it. The status bar gives the format, the pixel size and the zoom.

Hex Editor

For a blob of any kind, a picture included — the same value the Image Editor shows, in bytes.

Value Inspector

The others open a value to change it; this one explains it. It reports what the column is declared as, what SQLite makes of that declaration, which storage classes the column can end up holding, and the class, length and exact contents of the value in the cell — a number is also read back as each of the dates it might be. It writes too, and it is the only place the storage class a value is stored as can be chosen.

The Value Inspector has the whole account.

Record editor

Shows all of a row's fields together, one under another, with the grid's navigator along the bottom. Double-clicking a row opens it, if that option is enabled. A blob field offers a row of buttons instead of a value, so you can choose which editor to open it in.

A text field opens as tall as the value it shows, up to six lines, so a short value takes a single line. To give a field more room, or less, drag the strip of dots under its box; the fields below move with it.

Editing JSON

The JSON Editor keeps a value in the form it was stored in. A text value is shown exactly as it is in the database and written back exactly as you leave it, so any indenting you have applied to it survives. A JSONB value has to be decoded to be shown at all, and is encoded again when you save, so the column keeps its storage form.

The colouring follows JSON's own rules rather than a near language's. Member names are coloured apart from string values, escape sequences apart from the text they sit in, and anything JSON does not allow — a number like 007, an unquoted name, a backslash that starts no escape — is coloured as an error, so you can see what is wrong with a value as you type it rather than only when you press OK.

Whether a value arrives laid out depends on how it is stored. A JSONB blob holds no whitespace of its own — the text you see was made by decoding it — so it opens indented, and closing it with OK without editing anything still writes nothing. A text value carries its layout as part of the value, so it opens exactly as stored and indenting it is the Format button's job; that way, opening a value and closing it with OK never rewrites layout you chose yourself. OK checks the value first: if it is not valid JSON the editor stays open with your edit still in it, rather than closing and discarding it.

Emptying the editor sets the cell to null, since an empty value is not JSON in either storage form.

Note

Bear in mind that SQLite's own json_set, json_insert and similar functions rebuild the value they return, so a value updated through them comes back without whatever line breaks and indenting it had. The JSON Editor does not use them on stored text for exactly this reason.

Tip

To make a set of related edits safely, start a transaction first: your changes are held until you commit them, and you can roll them back if something isn't right.

See also: The Value Inspector · Working with JSON · Viewing table data · Editing live queries