SQLite Editor

Open and edit local SQLite files without a server.

Where it is: Workspace › Tools › SQLite Editor

The SQLite Editor is a self-contained tool for local .sqlite/.db files, similar to DB Browser for SQLite. It has four sections: Structure, Data, Execute SQL and Pragmas, and works even without a server connection.

Calíope opens unencrypted SQLite databases (SQLCipher is not supported).

Keywords: sqlite, db, local, database, browser, file

Open an existing SQLite file or create a new one.

Where it is: Workspace › Tools › SQLite Editor

Use Open… to pick an existing .sqlite/.db file, or New database… to create an empty one. Recent files appear on the start screen. A Read-only badge shows when a file can't be modified.

Keywords: open, create, new, recent, file

Browse tables, views, indexes and triggers, and create them.

The Structure section shows the schema tree with each object's columns and DDL. Use New to create tables, indexes or views, Add column or Rename table on a selected table, and Delete to drop an object.

Keywords: structure, table, index, view, trigger, create, ddl, schema

View, filter, sort and edit rows.

The Data section shows a table's rows page by page, with the counter and the arrows at the right of the toolbar. How many rows a page brings is up to you, in Preferences › Text editor › Results › Rows per page —Settings, on iPad—: it is the same preference used by the Execute SQL grid and by the SQL Editor's. Sort by pressing a column header —press it again to reverse the order—, narrow the list column by column with Filters, or type in the search field to look in all of them at once; either way the filter is applied by pressing Return. The Table selector lists the file's tables, and not all of them can be edited: on a WITHOUT ROWID table, or on any of them if the file was opened read-only, the toolbar says the object can't be edited instead of offering the buttons.

Editing a cell

macOS: double-click it. iPad: a single tap.

The value is typed in the grid itself. Return confirms, and leaving the cell —pressing another one, or the field losing focus— confirms too; Esc cancels and leaves the previous value (on iPad, with a keyboard attached).

The advanced editor

Whatever does not fit on one line is written in a separate sheet. It opens with the cell's ⋯ button —macOS: when the pointer is over it; iPad: on the cells of the selected row, which you mark by tapping its number— or with Advanced editor… in the context menu (right-click on macOS, press and hold on iPad). There the same value is written as text, as JSON —with a button that formats it, and refusing to apply an invalid one—, in hexadecimal, or seen as an image when the bytes are one —and that image zooms, from 100 % to 800 %, with a pinch, with the wheel or with the buttons at the bottom—; and those bytes are imported from a file and exported to another. A cell that already holds binary data opens this sheet directly, without going through the field in the grid.

Leaving a cell as NULL

In the cell's context menu, Set to NULL, without opening anything else; inside the advanced editor it is there as a switch too. A cell with no value is drawn as NULL in italics. Careful: clearing the content in the grid and confirming writes the empty string, which is not the same as NULL.

Adding and deleting rows

Add row opens a form with one line per column, its type and whether it is required. The columns the table can fill on its own —those declaring a default value, and the INTEGER primary key— start ticked so that the engine writes them: clear the checkbox if you would rather enter the value yourself. A field left empty is stored as NULL, and Insert stays off while a required column has no value —the notice beside it says which one—. On accepting, a single INSERT statement is issued, naming only the columns that carry a value. To delete, select the row by its number and use Delete row, or the context menu of any of its cells.

None of this touches the file until you press Save changes: what you edit stays in a pending transaction that Revert changes undoes entirely.

Keywords: data, rows, edit, filter, sort, search, cell, blob, null, insert, new row, delete, json, hex, image

Syntax highlighting, autocomplete and a results grid with a row cap.

The Execute SQL section uses the same text field and the same results grid as the server SQL Editor, but over the open file and with no connection involved.

Writing and running

The text is highlighted: keywords, strings, numbers and comments, each in its own colour. Press Run, or ⌘↩ from inside the text, and the whole script runs. What is drawn below is the result of the last statement that returned rows; if none did, it says how many rows were affected. An error stops the script at that statement and shows the engine's message as it came.

Whatever writes to the file —an INSERT, an UPDATE, a CREATE TABLE— stays pending, just like an edit made in Data: it does not touch the file until Save changes, and Revert changes undoes all of it.

Autocomplete

The suggestion panel brings SQL keywords and the open file's tables with their columns —all of them, not only those of the table selected in Structure—, and it keeps up with every schema change.

It opens by hand, and never appears on its own while you type. On macOS, with ⌥Esc and at least the start of the word typed: the list comes from that prefix, with keywords first and the file's names after, and the name is inserted as is. On iPad, with the Autocomplete button in the section header, which opens the panel at any time and needs no keyboard; there, accepting a table or a column writes it between backticks —`my_table`—, which SQLite accepts just like double quotes.

Keywords and function help are Calíope's general SQL, not SQLite's: SQLite-only functions such as julianday() or printf() run fine, but are not suggested.

The results grid

Rows go page by page, and how many fit in one is up to you in Preferences › Text editor › Results › Rows per page —Settings, on iPad—: the same preference used by the Data section and by the SQL Editor.

On macOS you can sort by pressing a column header —the order is the client's and reaches every row fetched, not only the page in front of you—, change a column's width by dragging its edge, hide or reorder columns from the header, pick several rows with ⇧ or ⌘ and copy them with ⌘C, which leaves them as TSV with the column names first. On iPad you turn pages with the buttons at the foot, and columns measure themselves.

On both, dragging a row out of the app takes it to another one as a table, and a cell's context menu —right click on macOS, long press on iPad— copies its value, shows it whole and exports the row to CSV.

What you see here is not edited: to change a value there is the Data section.

The row cap

A large SELECT is not fetched whole: it is cut at Preferences › Text editor › Results › Row cap, the same cap the SQL Editor uses. When it cuts, the grid says so below the rows and offers Fetch all, which runs the query again with no cap. With the cap at zero nothing is ever cut.

Function entries

Hover the pointer over a function on macOS, or put the text cursor on it on iPad, and its entry appears, the SQLite one: syntax, return type, parameters and an example. The topic Contextual help for SQL functions explains it.

Keywords: run, execute, sql, highlighting, syntax, autocomplete, suggestions, grid, results, sort, copy, drag, cap, rows, page

Changes stay pending until you save them.

Edits are grouped into a pending transaction. An amber dot marks unsaved changes. Use Save changes to commit or Revert changes to discard. Closing with pending changes asks for confirmation.

Keywords: save, revert, commit, rollback, transaction, changes

Import CSV and export to CSV, JSON or SQL.

From Import / Export you can import a CSV file —it becomes a table named after the file, with text columns, or its rows are added if that table already exists—, export the table open in Data to CSV or JSON, or export the whole database to a SQL dump (schema and data). An import stays pending, like any edit, until Save changes.

Keywords: import, export, csv, json, sql, dump, backup

Change the file's pragmas, check it and compact it.

The Pragmas section shows the file's settings. Those under Editable —Foreign keys, Auto-vacuum and the rest— change on the spot, and the numeric ones with Apply. They act at once, outside the pending transaction, so they stay off while there are unsaved changes: save or revert first. Some, like auto-vacuum, only take effect after a VACUUM.

Maintenance checks the file —Integrity check, Foreign-key check— or tidies it up —VACUUM, ANALYZE, REINDEX—, and the engine's answer appears below the buttons. The other pragmas are listed under Read-only with their current value.

Keywords: pragma, pragmas, maintenance, integrity, vacuum, analyze, reindex, foreign keys