Data grid with query results
Sorting
Click a header to sort by that column, and click it again to reverse the order.Option-click another header to add it as a further sort key, or Option-click a key already in the sort to reverse that one alone. A plain click always starts over with the column you clicked. Sorting re-runs the query with a new ORDER BY, so it orders the whole table rather than the page in front of you. In a query tab that query is the one statement behind the result, never the rest of the script.
The arrow marks the first sort key. With more than one key, point at a header to see where that column falls in the order. Don’t Sort in the header menu returns to the server’s order and stays there.
A Cassandra, ScyllaDB or Bigtable table has no header sort, and a query result there sorts the rows it holds. See Cassandra and Bigtable.
Sort direction in Settings > Data decides which way that first click goes. It is Ascending out of the box; set it to Descending to open on the newest rows and reach them in one click.
To sort on open, set Default row sort to Primary key or First column; out of the box the order is the server’s. A sort the setting applies carries the same arrow as one you clicked, and clicking that header reverses it.
Filtering
The header menu carries two filters that answer different questions.
The cell menu has a third: Filter turns the clicked cell’s value into a condition such as
status = “paid” and runs it against the whole table. Filter by cell value lists what it offers.
Filter Values… lists every value in the column with its count, NULL and empty among them. Select the values to keep and click Apply. To keep one value out of hundreds, type part of it in the search field and press Return: the search selects every value that contains what you typed and leaves out the rest, so paid keeps unpaid too. Select All Results selects or deselects only the values found, and clearing the search brings back what was selected before it. Filter several columns and a row has to match all of them; Clear Value Filter and Clear All Value Filters are on the same menu.
Filter a column by its values
Cmd+R, sorting, paging, the filter bar and Fetch All keep it and apply it to the rows they load. Hiding a filtered column keeps it filtered. Edit a cell so its row no longer matches and the row stays where it is until the rows load again. The filter also runs again when you change it or the column’s display format.
Highlighting
Right-click a cell and open Highlight. A color under Rows Where status = “paid” tints every row holding that value; a color under Cells Where status = “paid” tints only that cell. The palette marks the color a matching rule already uses, and Remove Highlight takes the rule away. For anything other than an exact match, click the highlighter button in the status bar or choose View > Highlight Rules. A rule is a column, an operator from the filter bar, a value, a color, and Row or Cell. Highlight Values… in the header menu starts a rule on that column. A rule left without a value is dropped when the popover closes. A rule is marked in the popover, and colors nothing, when its column is not in this result or when its regular expression will not compile.
US invoices in green, Canadian in blue, and customer IDs under 10 in orange
true, 1, t and yes alike, and NULL never satisfies a comparison: match it with is NULL or is empty.
A row waiting to be inserted or deleted keeps its change tracking tint over any rule, and a selected row shows the selection. On a table you edit, give row rules a color other than the green and red those tints use, so a highlight never reads as a pending change.
Rules belong to the table, scoped to the connection, database, and schema, and follow the table through a rename. A query result that comes from one table uses that table’s rules. Rules on any other query result are not saved, and go when the tab closes or the app quits. Saved rules stay on this Mac and do not sync.
Columns
Drag a border to resize a column, or double-click it to fit the content; Size to Fit and Size All Columns to Fit on the header menu do the same. A fitted column stops at half the visible grid width. Point at a header for the column’s type and, with Show object comments on in General settings, its comment. Hide columns from the columns button in the status bar or from the header menu. A hidden column is not fetched, so hiding a large one makes the table load faster; the primary key and the sorted column are fetched either way. On a schemaless store the popover lists every field the grid can draw, including ones that turn up only in later documents. Widths, order, hidden and frozen columns are remembered per table, scoped to the connection, database, and schema; Reset Columns in the popover puts them back. Right-click a header and choose Display As to read a column’s stored values in a different form; Cell and Row Viewers lists the formats.Freeze columns
Right-click a column’s header and choose Freeze Up to This Column to hold it and every column before it beside the row numbers. The rest scroll under them, and a line marks where the frozen columns end. Select, copy, edit, find and jump to a frozen column as to any other. Unfreeze Columns on the header menu lets them scroll again. Frozen columns cover at most two thirds of the grid’s width. The menu item is dimmed for a column that would freeze more, and frozen columns that no longer fit after the window narrows scroll until it widens again. Sort, resize and reorder frozen columns from their own headers at any scroll position. A drag keeps a column on its side of the line, so freeze or unfreeze to move one across. On a query result they stay frozen until the tab closes or the app quits.Large binary values
On MySQL and MariaDB, a binary value larger than the connection’s limit stays on the server until you need it, in any table with a primary key. The cell shows the value’s size in italics, such as 5.2 MB. Set the limit under Options > Load binary values up to in the connection form; it is 64 KB until you change it. The value is read by its primary key when you open the cell, copy it, show its row in the inspector with one row selected, duplicate the row, list the column’s values with Filter Values…, or export the rows with Export Results…. File > Export reads every value as before. Set NULL, paste and Fill Column read the value first, so undo puts it back. A table without a primary key, a sorted column and a column under a value filter load every value with the page. Find and the selection summary skip a value until it loads, and the JSON view shows it as Not loaded with its size. Deleting a row whose value never loaded saves normally, but Data Rewind cannot put that row back.Jump to a column
Cmd+Shift+J, or Edit > Find > Jump to Column, lists every column of the result with its type and its place in the grid. Type part of a name; matching is fuzzy, so crat finds created_at. Return scrolls the selected column into view and puts the cell cursor in it, on the selected row. A hidden column is listed too, marked Hidden, and jumping to it shows it first. Jump to Column at the foot of the columns popover opens the same panel with the popover’s search carried over.
Jump to Column ranking a fuzzy match
Foreign keys
A foreign key cell carries an arrow on its right edge. Click it to open the referenced table filtered to the matching row, or right-click for Preview Referenced Row, which shows that row in a popover. The reference arrives in a tab of its own, so the rows you were reading stay open behind it. A tab already showing that same reference comes forward instead of a second one opening, andCmd-click opens a new tab even then.
Clicking an arrow that points back into the table the tab already shows re-filters that tab rather than opening another. Ctrl+Cmd+[ returns to the rows it left.
Double-click a foreign key cell, press Return on it, or open its right-click menu to pick the key rather than type it. The picker lists rows from the referenced table, each key with a label beside it, and narrows as you type. Return in the search field commits the text as typed, which covers a key the search has not turned up. Set NULL appears on a nullable column.
Label at the foot of the picker opens the referenced table’s columns. Tick one or several: a parent whose rows are unique only on descrizione and marchio together reads as six identical rows under either column alone. Ticked columns show beside the key in the table’s own order, and the search matches any of them. None clears them and lists keys on their own. A date or a number shows beside the key but carries no text search, so a picker with no text column to search says so rather than reporting no matches. The choice is remembered for the referenced table, so every column pointing at it shows the same ones. A search fetches the first 50 matches, which keeps the list quick on a large table.
A column of a foreign key that spans several columns keeps the text editor. The picker sets one column, and a key it offered might not pair with the values the row holds in the constraint’s other columns. Type the key, or open the referenced table and read the pair off it.
Two label columns, so every employee reads as a name
Ctrl+Cmd+[) and View > Forward (Ctrl+Cmd+]), or with the same pair under Actions in the toolbar. Add the buttons to the toolbar with Customize Toolbar to keep them in reach. Back restores the table you came from as you left it: same filters, sort, page, and selected row. Each tab keeps its own history and Back never closes a tab. With unsaved edits in the tab, Back asks to discard them first; with a staged structure edit it stands down, because that work cannot be discarded from here.
Foreign key lookup
Inspector
PressCmd+Option+I, or click the button at the trailing end of the toolbar, to open the inspector beside the grid. The table and which row of how many is selected sit under the pane’s header, and Fields and JSON are the two renderings of that row, picked from the header’s ellipsis menu. Cell and Row Viewers covers both. Turn on Auto-show inspector on row select in Settings > Data to open it whenever a row is picked, in a query result as well as a table.
Short values sit on one line, label leading and value trailing. Long text, JSON, PHP, binary and images take the full width. Long text and JSON carry a resize handle; PHP, binary and images draw at a fixed height. Tab and Shift+Tab move between fields; the menu at the trailing edge of each field carries the same values as Set Value: Set NULL, Set DEFAULT, Set EMPTY, Generate UUID and Generate UUIDv7 where the column takes them, and SQL Functions with the date and time functions that fit it. Ctrl+Option+N and Ctrl+Option+D set NULL and DEFAULT. With several rows selected, Generate UUID gives each row its own value. Search filters the list by column name or stored value, and the pencil button narrows it to fields already edited.
With no row selected and a table open, the inspector shows that table’s size, row count, engine, collation and timestamps, as far as the database reports them. They are read only while the inspector is open.
A Track row with its scalar columns on one line each
Find in the loaded rows
PressCmd+F to open the find bar above the grid that has focus, or Cmd+Option+F to open it with a replace field. The first match after the cell cursor becomes the selected cell, so Cmd+C, Return and the arrow keys act on it. Return and Cmd+G step forward, Shift+Enter and Cmd+Shift+G step back, and Escape closes the bar and hands the keyboard back to the grid. Cmd+E searches for the value of the selected cell. Query results and the Structure lists have the same bar. In a query tab, click into the result first: with the editor focused, Cmd+F searches the SQL.
Matching runs over the text as displayed and ignores case and accents. The options menu beside the field turns on Match Case, Whole Words and Regular Expression, each of which matches accents exactly, and Search In limits the search to one column. Spatial columns are skipped, and so is a binary column still showing hex. The counter says which rows were searched:
When none of the loaded rows match and more rows exist, Search All Rows in the bar turns the term into a filter that runs at the database, covering the whole table. It replaces the tab’s filters, so it appears only when none are applied; with a filter set, add the term to the filter bar instead. Cassandra and ScyllaDB cannot match any one of several columns, so it never appears there.
In an editable table, select Replace in the bar to show the replacement field. Replace changes the current match and moves to the next; Replace All changes every match in the loaded rows as one step, so one
Cmd+Z puts all of them back, and the bar says how many cells changed. With a regular expression, $1 in the replacement inserts the first capture group. Replace works on the stored value, so a match that exists only in a Display As form is left alone, and so is a cell you could not type into. Replacements wait in the queue like any edit until you save.
Pages and row counts
A table tab pages. The status bar offers 5, 10, 20, 100, 500 or 1,000 rows per page, Custom… up to 1,000,000, and All rows…, plus First, Previous, Next and Last;Cmd+[ and Cmd+] step pages, and clicking the page indicator (3 / 12) jumps to a page number. In a narrow window that indicator is a menu carrying Go to page… and the row-count choices together. The size a table opens with is a data setting.
A large table shows an estimated total prefixed with ~ instead of running a slow COUNT(*). Count Exactly beside it runs the real count in the background, and Cmd+. cancels that. While a total is an estimate, Last and All rows… stay unavailable and Next works as long as the last page came back full.
Cassandra, ScyllaDB, Bigtable and the Firebase Realtime Database have no estimate, and their count reads every partition or key, so a table there shows no total until you click Count Exactly.
A query tab does not page. It stops at the row cap instead and offers Fetch All to load the rest, and a query carrying its own LIMIT, FETCH FIRST, or TOP is never capped. Cmd+. cancels a running query or a Fetch All.
Some engines cannot skip rows and return a fixed maximum from one query. On those, a table tab shows its leading rows only: First, Previous, Next, Last and All rows… are gone, the rows-per-page menu stops at the engine’s maximum, and the total is counted only when you click Count Exactly. Filter or sort to decide which rows load. Cloudflare R2 SQL works this way.
Reloading
Cmd+R reads the rows again and leaves the grid where it was. Scrolled down, the row at the top stays at the top, matched by primary key, so a row another session added takes its sorted place without moving what you are reading. At the top of the grid, the grid stays at the top and rows added above come into view. On a table without a primary key, or with a key that repeats, the scroll position holds instead. Selected rows stay selected, matched by primary key; on a table without one, the selection clears.
On a table tab, sorting, filtering, and changing page or page size start at the first row. The horizontal scroll position stays.
Saving edits, importing rows, Insert Document…, a structure change and Refresh Materialized View… reload every tab showing the table they changed, in every window of the connection. The tab in front reloads at once and keeps its place. A tab behind it reloads when you switch to it. A tab with a cell open or unsaved edits is not interrupted. It reloads as soon as the cell is closed and its edits are saved, undone or discarded. Tabs on other tables stay as they are. A SQL file import reaches every table tab of the connection the same way. A statement run from the SQL editor reloads nothing, so press Cmd+R on the tabs it touched.
Copying
Click a cell to select its row and put the cell cursor on that cell. Drag orShift-click for a range of cells, and Cmd-click to add or remove a cell. Click a row number to select a whole row; drag down the row numbers, or Shift-click or Cmd-click them, for several. Cmd+A selects every row and leaves no cell cursor. Escape shrinks a range of cells to the cursor’s row, and a second Escape clears the selection.
For a whole column, Cmd-click its header or right-click the header and choose Select Column; Cmd-Shift-click another header to take every column between the two. A plain click on a header sorts. The row numbers stay at the left edge on a table wider than the window, so whole rows are still selectable when the columns have scrolled past them. Shift+Space widens whatever is selected to every row it touches.
The selection draws in your Accent color, or in the theme’s selection color when it sets one, and the cell the cursor is on carries a focus border. A right-click never changes the selection: inside it, the menu acts on the whole selection; outside it, on the cell or row you clicked.
After a plain click on a cell, Cmd+C copies the value of the cell under the cursor. Rows selected by their row numbers or with Cmd+A copy in the Copy rows as format from Settings > Data. A range of cells copies as tab-separated cells.
Copy rows as offers TSV, TSV with Headers, JSON, CSV, CSV with Headers and Markdown. Whatever the format,
Cmd+V in a table pastes the copied rows, not the text. To copy one value out of a selection, choose Edit > Copy Special > Copy Cell Value for the cell the cursor is on, or Copy Cell Value in the right-click menu for the cell under the pointer.
Right-click and open Copy as for CSV, CSV with Headers, Markdown, and IN Clause, which writes ('a', 'b', 'c') for a WHERE col IN (…). INSERT Statement(s) and UPDATE Statement(s) appear when the result comes from a single table whose structure has loaded, and never on MongoDB or Redis. UPDATE is also missing on a table without a primary key that has a column no match can compare, such as a SQL Server sql_variant. Copy Column Values on the header menu copies one column, one value per line. Every format is also in Edit > Copy Special, where IN Clause takes the one column a cell selection covers, or the cursor’s column. Those items have no shortcut until you record one in Settings > Keyboard.
Every copy follows the grid as shown: hidden columns are left out, columns keep their current order, and over a cell selection only those columns go out. INSERT and UPDATE write what a save of the same row would. They leave out the columns the server fills in, such as identity and computed columns. INSERT leaves a cell set to DEFAULT to the server, and UPDATE assigns it DEFAULT. The table name carries its schema. UPDATE finds the row as it is stored, not as you have edited it: by every column of the primary key, whether or not those cells are selected, or on a table without one by every column, hidden ones included, which also changes any identical copy of the row. A row you added and have not saved gets no UPDATE.
Selection summary
Select two or more cells and the status bar shows their Sum, Average and Count. A narrow window shows the sum alone, then only the Σ button. Click it for the full list: Count, Numbers, Empty, Sum, Average, Minimum and Maximum, each with a copy button. Right-click inside the selection for Copy Sum and Copy Average.- Count is the selected cells that are not NULL. Average divides the sum by Numbers, the cells that hold a number.
- Integer and decimal columns are summed. A text column is not, so a ZIP code such as
00501never joins a total. A column with no declared type, such as a SQLite expression, is summed when its value is a plain number. NaN,Infinityand PostgreSQLmoneyvalues are left out and counted under Not a number.- A column shown with Display As, such as a Unix timestamp shown as a date, is counted but not summed.
- Rows marked for deletion are left out, so the figures match the table after you save.
- Only loaded rows count. On a paged table or a query that stopped at its row limit, a whole selected column covers the rows on screen, and the popover says so.
- The arithmetic is exact:
0.1added ten times is1, and aBIGINT UNSIGNEDsum keeps every digit. Copy writes the full value without group separators, ready to paste into SQL.
View modes
Switch between Data, Structure, JSON, Chart, Map, and Output with the switcher at the leading edge of the status bar, or from View > Result View. Query tabs have no Structure mode, Map appears only while the result holds a column of drawable geometry, Output appears only when the statement printed something on the server (see Server output), the mode is remembered per tab, and Cell and Row Viewers covers JSON mode.Chart mode
Needs a Pro license.
ORDER BY sets it, and NULL gets a panel titled “No value”. A series keeps its color, symbol, and dash in every panel, and a re-sort or a page turn does not change them.
A chart draws the loaded rows, up to 2,000 points, 20 series, 12 facets, and 50,000 inspected rows, and the toolbar says when it hit one: “Showing the first 2,000 points of 8,431 loaded rows”. Null, binary, and unrepresentable axis values are skipped and counted. Grid selection, Find, hidden columns, and value filters do not narrow a chart, and the page controls and Fetch All stay in the status bar. Chart type and axes belong to the tab and follow the column names, so they survive a page turn, a sort, and a re-run.
Map mode
Map appears when a column of the result holds values the map can read. Ageometry, geography or geo_shape column usually does, and so does a MongoDB field holding GeoJSON, which arrives typed as JSON. A result of plain columns never carries the segment, and neither does a spatial column whose values are in a format the reader does not cover.
Points, lines and polygons draw over Apple’s own tiles. Nothing about the result reaches a third-party tile server. Click a shape to select its row, then switch to Data to see that row selected in the grid; select rows in the grid and their shapes come back highlighted. Fit to Result frames every drawn shape. A result with more than one geometry column gets a picker to choose between them.
For one row’s geometry on its own, select the row and open the inspector: a geometry field there draws that value alone, in any view mode.
Values are read as WKT, EWKT, WKB and EWKB hex, GeoJSON, ClickHouse point and ring tuples, and all six spellings of an Elasticsearch geo_point, so PostGIS, MySQL, MariaDB, MongoDB, Snowflake, Elasticsearch and ClickHouse draw without any setup. The map draws the rows the grid is showing, so a value filter narrows it and the page controls and Fetch All stay in the status bar.
The toolbar says what was drawn and what was not: Drawing 4,102 shapes in SRID 4326. 88 rows in other coordinate systems are not drawn. One coordinate system is drawn at a time, whichever most of the column uses. A member of a multi-part geometry that the map cannot place is left out and counted in the same line, and the rest of that value still draws.
Maps place longitude and latitude. Geographic SRIDs 4326, 4979, 4269, 6318, 4258, 4283, 7844, 4490, 4674 and 6668 draw as stored, with no datum shift. Web Mercator (3857, 900913, 3785, 102100, 102113) is converted, and a column carrying no SRID draws when every coordinate falls inside the range of longitude and latitude.
ST_Transform(geom, 4326), which produces a result the map draws. Curved and polyhedral geometry (CIRCULARSTRING, CURVEPOLYGON, COMPOUNDCURVE, MULTICURVE, MULTISURFACE, POLYHEDRALSURFACE, TIN, TRIANGLE) is counted and named rather than drawn; ST_CurveToLine converts it. Latitude past 85.05 degrees flattens to that value, so Arctic and Antarctic shapes land in the wrong place.
The map is one element to VoiceOver and does not publish its shapes, so the grid stays the readable representation of spatial rows.
SQL Server, Oracle, SpatiaLite, Teradata, Trino, Presto, Spanner, Cassandra and BigQuery hand geometry over in formats the reader does not cover yet. Their spatial columns show text and carry no Map segment, because the segment follows what the values are rather than what the column is called.
Editing and display
Double-click a cell, or pressReturn on it, to edit it in place. A value that holds a line break, or runs past 256 characters, opens in a larger editor anchored to the cell instead: Return saves, Option+Enter starts a new line and Escape cancels. Option+Enter while editing in the cell moves the text into that editor. In a grid you cannot edit, the same gestures open the value in a viewer. Nothing reaches the database until you save, and Change Tracking covers the type-specific editors, the row operations, and what a Save runs.
With the grid focused, Cmd+= and Cmd+- make its text bigger or smaller. They change the Data Grid Font size in data settings, so every grid, the inspector and the cell popovers follow, and the size is kept between launches.
NULL renders as styled NULL text. That text, the date format, row height, row numbers, and alternate row backgrounds are data settings. Every shortcut the grid answers to, and how to rebind it, is in Keyboard Shortcuts.
Invisible characters
A value that will not match aWHERE clause it looks right for often holds a character that draws as nothing. Cells mark those characters the way the SQL editor does: a zero-width space, a byte order mark, a soft hyphen or a bidirectional control shows as a box with its short name, such as ZWSP, BOM, SHY or RLO, and a non-breaking or other special space is outlined. A carriage return with no line feed after it shows as CR. Other line breaks still read as a space.
The inspector’s text fields, the viewer and editor that open on a cell, and the text window mark the same characters. The text window’s status bar also counts the value’s characters and bytes.
To hide the marks, turn off Show invisible characters in Settings > Data.