The best MySQL tool for macOS
The best MySQL tool for macOS
Since Querious 2 was initially released, there have been dozens and dozens of updates, adding hundreds of new features and improvements. Here are some of the changes made along the way.
New User Interface
The entire UI has been refined. At the top level there are now four main tabs available in the Toolbar: Content, Structure, Query, and Server. The toolbar can now be changed to show small icons, icons only, or text only. A new Editor has been added to the menu bar.
Although there are no MariaDB-specific features, connections to versions 5 and later of MariaDB (a drop-in replacement for MySQL) are supported in Querious 2.
Each connection tab now has a navigation history (see Go Forward/Backward in the View menu), making traversing between different views and selected schema objects easier.
Each saved connection can have an associated color, which tints the window's titlebar when that connection is active. Now you can easily see visual confirmation of development vs production connections.
When you have many tables within many databases, quickly navigating among them can be tiring. With the Go Quickly panel you can simply type (pieces of) the name of the database, table, trigger, event, etc you want to view, and Querious will instantly narrow down a suggestion list to the one you want. Simply press Return and you're there.
Group your connection settings into folders for easier navigation. No longer do you need to have all connections in one flat list, you can now even nest folders inside other folders.
When you have dozens of connection settings saved, use the new smaller connection sidebar layout to view more connections at once.
Easier SSH Setup
Using a custom key for SSH key-based authentication is easier. Now you can set the key file explicitly in the SSH settings instead of solely relying on ssh-agent.
Need a bit more room to read the nickname and hosts of your saved settings? The connections sidebar is now resizable.
When adding or editing a saved connection, use the new "Test" button to ensure that the newly entered settings can successfully connect before saving the changes.
Easier Image Setting
To set a custom image for a saved connection, double-click on the image well to bring up an Open dialog to select an image file.
The sidebar in the Content and Structure views displaying the databases, tables, views, etc, can now be focused to show schema objects in just a particular database, making navigation easier.
See it All
Now shows functions, procedures, triggers, and events in addition to databases, tables, and views. You can also add, remove, and rename them.
The contextual menu in the list now contains the Table Operations menu. Analyze, Check, Flush, Optimize, Repair, and—new in Querious 2—Truncate.
Expanded Contextual Menu
Importing and exporting a specific database or table is now easier. Simply right-click on it and use the new menu items. Copying the
CREATE statement for any object is also a cinch.
The sidebar's width is now saved and restored for each individual server you connect to. (Sometimes the small changes are among the most welcomed.)
Import via Drag & Drop
Quickly import/execute a SQL file into/on a database, or import CSV/Tab into a table by dragging and dropping the file directly onto it.
The search controls have been revamped. Not only are they slimmer, leaving more room for your content, but you can also create searches with multiple conditions using the convenient and buttons.
The Content view now maintains the search and results per-table. Navigating back and forth between tables is now quicker and more consistent. Simply refresh to see the latest results if needed.
"Show Referencing Rows"
This contextual menu item brings up a sheet to select a foreign key (or artificial foreign key) to find a row or rows in another table which reference or are referenced by the selected row. Ex: find the line items belonging to a selected order, or even the order the selected line item belongs to.
Set Value Across Multiple Rows
Select any collection of rows in the Table Content view, right-click within the desired column, and use the new contextual menu item to simultaneously set the value for that column in all selected rows. No need to rely on repeated Copy & Paste any more.
Just like setting an entered value for a column across rows, you can now easily set one or more rows' column value to
NULL by simply right-click and selecting the Set to NULL menu item. No longer do you need actually type in "NULL".
Single Row Refreshing
Sometimes you know you need to refresh the values of just a few rows or even a single row. Instead of having to refresh all rows shown in the Table Content view, you can select the row(s), right-click, and select "Refresh Row".
The Action menu at the bottom of the view keeps Import, Export, Refresh, and Table Operations actions nearby. The Action menu is also where you can open the Hide Columns popover.
Binary Hex Editing
The "Hex" tab in the the Field Value Editor sheet now contains a true hex editor, displaying the editable hex and ASCII side by side.
Binary Display Preference
Binary column values can be either displayed either with the "Binary" indicator, or, at the flip of the setting in Preferences, displayed with their hex value.
Enum & Set Type Columns
Table cells for columns using the
set types now have popups to select from the appropriate list of values. Autocomplete is available when editing the value as text.
Row Status Highlighting
The Table Content view's row list now has a green row selection highlight behind the newly added row as a reminder it is unsaved. If a displayed row can no longer specifically be found in the table, it's drawn with a yellow cautionary background.
Table columns using geometry types can now all be edited using the WKT (Well Known Text) format. Table columns using geometry types can now all be edited using the WKT (Well Known Text) format.
With MySQL 5.7, Querious supports adding and editing JSON columns to your tables. JSON field values can be shown and edited with syntax coloring and validation too.
Sometimes editing a field value in the row list is what you want, and other times you need a bit more working room. The new preferences in the Results preference panel allow finer control over which types to edit in a separate window and which to edit directly in the list.
Improved Data Entry
Among the features making data entry easier are: the Duplicate Row contextual menu item, and easier editing row-to-row editing of the same column. Now, after editing column #3, selecting a different row, and pressing return to begin editing will start with the focus on column #3 instead of the first column.
Although a table may have 20 columns in it, you may only want to see 5 of them and ignore the rest. Selecting "Hide Columns…" in the Action menu will display a list letting you choose which columns you wish to see. The narrow gray "gutter" next to the left of the first column indicates that other columns are hidden. Clicking on this gutter will display the same list, as will right-clicking on any column header.
Editable MySQL Views
When viewing the content results of a MySQL View, you can now edit the values in the rows if the original row and column can be uniquely located. Views offer another handy way of presenting your data in a different format for specialized purposes.
Millions of rows can be smoothly scrolled through with ease. The speed of drawing the Content (and Query) view results is dramatically improved, especially when the preference expanding row heights for multi-line field values is enabled.
Small touches sometimes make the biggest difference. Entering "NOW" or "CURRENT_TIMESTAMP" into a timestamp column's field will now assign the equivalent time for you automatically.
Cmd-Delete now deletes the selected rows without asking for confirmation.
All saved and recent queries can be accessed through the quick pull-down menus in the Favorites Bar. Customizable pull-down menus for grouping saved queries. Easily preview and execute the query from the menu itself.
Multiple Query Results
When running multiple queries, now each query's result status and rows can be seen individually. As each query is finished executing, its results are immediately available for viewing while the following queries are still executing. Cancelling running queries is now immediate as well.
Multiple Query Editors
In the Query view you can have any number of separate editors, accessible in the sidebar, letting you switch back and forth between various databases and sets of queries and results.
Open, Edit, Run SQL files
In addition to keeping saved queries in Querious itself, you can also open, edit, and run sql files from elsewhere on your system.
The new autocomplete system displays the partial completion in the text view, and has a new window displaying the list of possible results. The autocomplete window not only shows SQL keywords, database/table/column/view/trigger/etc names, but also displays what kind of object each suggestion is, and where (which database and table) it is contained in.
Right-click on a query and select "Explain Query" to see MySQL's detailed information on how the query will be executed.
Saved Queries Editor
The Saved Queries editor now allows organization of queries by drag and drop in any number of nested folders. Easily duplicate a query by holding option while dropping, and even import queries from text clippings and files by drag and drop.
Editable Query Results
It can take considerable time and effort to craft the right
SELECT statement to grab the desired set of rows. But what if after seeing those rows, you want to change a field value in the results? Now you can. As long as the original field and row can be uniquely found, Querious will let you edit the value directly in the Query results view.
When viewing the row results of a query, the Action menu at the bottom of the view contains an "Export…" menu item which will begin the process to export those specific results.
Often when writing a query you need a reminder of what the column names are in various tables. Autocomplete is certainly helpful but Querious goes further and offers a helpful sidebar that automatically displays the column names (and types) in the relevant table. Now there's no need to go hunting in the Structure view.
Right-click on a query and select "Format" to have keyword case (upper or lower) unified and the query reformatted with indentation. A handy way to clean up messy SQL.
Query Editor Sidebar
Next to the query editor text view is a sidebar line number view. In the sidebar on the starting line of each query is a marker. Clicking on a marker will select only that query. Option-clicking will add that query to the selection, allowing you to execute multiple but specific queries.
Vastly Improved Editing
The new text editor supports line wrap, highlights the selected line, customizable tab behavior (spaces vs tab, and width), balanced parenthesis selection, auto-indent on Return, menu items and keyboard shortcuts for shifting selected lines left and right as well as commenting/uncommenting them, and even copies text with syntax coloring applied.
Configurable 'Run' Button
The "Run" button can now be configured to run the current or all queries. Cmd-Return still "clicks" the button, performing the desired action, but the new Cmd-Shift-Return shortcut will perform the opposite function providing keyboard access to both actions.
Copy Rows as SQL
When result rows belong to single table, they can be copied as SQL INSERT statements for convenience.
The Query view layout has been refined to make the Selected Database a clear and prominent feature. There is also more horizontal room for query results, and there is also more room for the saved queries editor as well.
When changing the table's character encoding, Querious now asks if you want to simply change the default encoding for new columns, or convert all existing columns and data to use the newly selected encoding too.
The autoincrementing column selector is now a popup for a more compact layout. It also shows the current autoincrement value, and a button allowing it to be reset to a specific value.
Multiple Row Editing
Columns view: When multiple rows in the list are selected, the character set, collation, and comments can be changed and will affect all items in the selection.
Table File Size
The Table Info view now displays the size of the table's file on the server in megabytes or gigabytes. Knowing how big your data really is can be important.
The Foreign Keys editor now has the ability to specify the On Update and On Delete constraints, and can also refer to columns within the same table.
Columns view: Added popups with NULL and CURRENT_TIMESTAMP to the Default value column. Also added "ON UPDATE CURRENT_TIMESTAMP" as an option to the Extra column. These make it easy to not have to remember what the SQL is specifically.
Table Row Count
For MyISAM tables, the time it takes to get the row count is effectively instant. For InnoDB tables, however, a longer one-by-one count of the rows must be done. The Table Info view now has a Refresh control to ask MySQL for the accurate current row count of the selected table instead of the very inaccurate estimate it keeps.
Duplicate a Column
Columns view: Duplicating a column can now be done by using the Edit→Duplicate menu item, or by holding the Option key and clicking on the button below the list.
Columns view: When editing the column type by text instead of using the popup menu, Querious will now offer autocomplete suggestions to speed it up.
A new top-level "Server" view has been added to the toolbar containing many subviews for server-wide features, status, and settings. The Users view has been moved from the toolbar into the sidebar of the Servers view.
This special view shows all queries that Querious has run on your behalf. Each query is given a "type" to describe the reason it was run: User (the queries you've explicitly executed in the Query view), Fetches (simple lookups that won't modify any data or structure), Actions (such as flushing privileges or optimizing a table), and Changes (queries that do alter data or structure explicitly).
See a list of all MySQL events in each database on the server, along with their execution status and schedule. The server's Event Scheduler can also be turned on or off.
At a glance, see the server uptime, bandwidth used, number of current connections, attempts, threads running on the server, and all of the zillions of MySQL "status" variables, capturing the state of various subsystems in the server.
Need to see how active the server is? In the Processes view, you can see a list of all of the work being performed by all connections to the server, including the exact query being performed. This number of active versus sleeping processes is charted over time to present a larger scale of what's happening.
By acessing the error log on the server through SSH (or the local file system), Querious can show you all of the notes, warnings, and errors occurring on the server that otherwise would go unnoticed.
Slow Queries Log
To make sure your applications are running at peak efficiency, MySQL can log "slow" queries which take more than a specified amount of time to execute. Querious displays these logged queries (and can group similar queries to provide a more succinct view) so that you can identify and optimize potential bottlenecks.
See a list of all variables declared on the server, with their global and session-specific values. Global variables include things like how the server has been configured, such as performance and connection parameters.
The Users view is now a subview of the Servers view. A number of fixes and improvements have been made, including adding privileges for triggers and events.
With a database selected, the Content view shows an overview of all tables in the database, with their row counts, file size, table type, and encoding info.
With a procedure selected, the Content view shows an interface for running the procedure with specified parameters. The results of the procedure are shown, along with any output variable values.
With a database selected, the Structure view's Relationships subview visually diagrams all tables in the database and their relationships created by MySQL foreign keys and Querious's artificial foreign keys. This view gives a quick understanding of the database and allows you to add artificial keys for more easily navigating within the Content view.
Database: SQL Structure
With a database selected, the Structure view's SQL Structure subview adds a handy way to get the SQL for recreating the structure (and the data) of the entire database, or just the select objects you want to include.
Create and edit MySQL views (pseudo-tables) by specifying a SELECT statement from which to create the view. Once the changes (if any) are applied, see the column names and types that form the artificial table the view represents.
Create and edit functions and procedures with a visual editor that takes care of the peripheral SQL so that you can focus on the SQL that actually comprises the function or procedure itself.
Easily create and edit triggers — a set of SQL statements that are executed before or after rows in a specified table are inserted, updated, or deleted.
Easily create, edit, and schedule MySQL events. The SQL syntax for scheduling events is a bit confusing, but in Querious it's as easy as clicking a few buttons while still offering the full range of capability offered in SQL.
Export and import progress is now shown within the new progress panel instead of a sheet that blocks you from continuing to work. Import/Export progress is now shown on the icon in the Dock, and completion is shown in Notification Center for long-running operations.
Importing and exporting is fast, but duplicating is even faster. Duplicate tables (even including the content data), views, functions, procedures, triggers, and events on the same server or even between servers!
Import and exporting has never been faster. Anywhere. Many many times faster than other apps and even 3x faster than the venerable 'mysqldump' command line tool, Querious is lightning fast.
Duplicate an Entire Database
Not only can you duplicate an individual table/view/etc, but you can duplicate an entire database at once. And not only can you duplicate the database on the same server, but also between servers. It's a cinch.
When exporting to SQL, you now have the option to very specifically select which tables, triggers, views, events, functions, etc to export, along with ancillary options such as inserting statements for locking the tables and disabling keys around
You can also control whether
INSERT statements should include column names, and whether to batch multiple rows into a single statement. These options give you the flexibility to balance file size versus completeness.
New multi-condition filtering. Filter rows by values in a single column or all columns, using one of 14 comparators. The rows, columns, and values, are also still fully editable when a filter is active.
In addition to displaying only filtered rows, the new find panel lets you jump between rows matching the specified search conditions. The text-based search and replace can be limited to a single column or all columns, and also can use regular expressions.
Expanded Copy & Paste
Not only can you select and copy any number of rows from a document as CSV/Tab text, you can also paste those rows into a new document, or into an existing document to merge records from multiple sources. You can also drag and drop rows between documents.
Find distinct, unique, or duplicate rows and either remove them from the current document, or copy them into a new document. Easily find the unique rows, or slim down a document to exclude the extra copies by removing unwanted duplicates.
Any combination of columns can be joined into a new single column, such as joining FirstName and LastName columns into a single FullName column. A column can also be split into multiple columns. A column with date values such as '2029-03-17' can be split into Year, Month, and Day columns.
SQL Text Editor
Now you can use Querious to open and edit SQL documents with the same familiar feel and features as the Query view's editor; Syntax coloring, line numbering, indentation, line-wrapping, and SQL autocomplete.