There was a time not so long ago that Database Administrators (DBAs) worked with database servers that were running on their company’s infrastructure, perhaps even nearby on the same floor. Since the emergence of cloud database services like Amazon Aurora, Google Cloud SQL, and Oracle Database Cloud Service, this arrangement is all but obsolete. Although just about all cloud DBaaS platforms offer their own web consoles and tools for managing your databases, many DBAs prefer to rely on the familiarity and superior performance of native applications.
While most apps focus on a specific database product, a few have gone the extra mile in supporting numerous database platforms. In today's article, we'll explore a recent addition named TablePlus. It supports a whole set of relational databases including PostgreSQL, MySQL, RedShift, SQL Server, MariaDB, Redis, and SQLite, with support for Oracle and CockroachDB coming soon.
A Quick Overview
TablePlus was created by Huy Pham in July of 2017. Now, he’s got a small team helping him develop it as well. TablePlus is a native application for macOS written in Swift, Objective-C, C/C++, and Perl for OSX. Currently, Pham and his team are hard at work on a C# driven Windows version. It’s expected to be completed shortly. You can sign up on the website to be notified by email when it's released.
There are two licenses to choose from: “Starter” and “Happy”.
The Starter plan is FREE and is geared towards basic users. It imposes a few limits:
- You cannot change the theme.
- Limited to 3 plugins.
- Limit of 2 tabs, 2 workspaces, and 2 advanced filters.
The Happy plan is for the individual DBA and is the recommended plan for a single user. $49 gets you a permanent license and one year free of updates. You can also apply this license to multiple devices by ordering more seats at a cost of $35 each.
Once you’ve paid for the app, it’s yours to keep and use into perpetuity. However, you can only upgrade the app for the first year after purchase.
TablePlus comes with a 7-day money back guarantee, should it not work as advertised for any reason.
The first time that you launch TablePlus, it will present a dialog for creating a database connection. It contains clickable icons of all the supported databases as well as a button to import a database connection URL:
Creating a Database Connection
Being able to connect to many different DBMS flavors residing on different environments (i.e. dev, test, prod) can result in many connection profiles. Managing these is made a lot easier thanks to TablePlus’s ability to create Groups from the Connection screen. Just click a Group label while holding down the Option key to access the context menu. Then choose the “Group…” item and give your Group a name in the dialog:
Creating a New Database
An existing database is not a prerequisite for creating a Connection. You can create a new database in one of two ways:
- From the Welcome screen, click the Restore icon, choose the connection, then you'll see the “New database” button on the right.
- While you're connecting to a database, click the database icon ("show database list" or command K) near the top left of the window, right-click a database and choose "new".
Here is the app after logging into the SQLite Chinook database and selecting the albums table:
Create a New Database
At the bottom of the application window, there are a number of controls for performing different operations.
Directly beneath the database Items, there is a plus sign (+) for adding a new table as well as a list for navigating between databases.
Below the table data there is:
- a plus sign (+) for adding a new table row
- a toggle button for switching between table Data and Structure
- row information
- a button to select which Columns to display
- a button to filter results
- arrow buttons to navigate through the result set
- a cog icon for setting the record paging Limit and Offset properties
Modifying Table Data
Clicking a table row will highlight it and populate the searchable Fields pane to the right of the row details. Pressing the Option button while clicking a cell will put it in Edit mode. Modifying the contents of a cell will show its edited state by changing the background color. The same color will be applied to the database items list. The highlighted elements are those that have been updated but not yet committed. To apply your changes, click the Apply Commands button, shown with a red rectangle below:
Apply Commands Button
In the above screenshot, you can also see how I used the Filters button to narrow down the data to those rows that contains the word “rock”. It’s also accessible using Cmd + F shortcut key. In fact, every function can be executed using shortcut keys; it's one of TablePlus users’ favorite features!
Depending on your preference settings, screens can either open in a tab or new window. The above image contains my two tabs open: SQL Query and albums.
Before committing your changes, you can preview all of the statements in the Code Review window. In the following screenshot, I created a new table named “subgenres” and added three records:
Code Review Window
Querying the Database
TablePlus comes equipped with a full-featured SQL editor that includes all of the bells and whistles that you’d expect in a professional grade app, including: Syntax Highlighting, Beautify SQL, Splitting Results into Tabs, Splitting the Pane Horizontally, to name but a few.
The Autocomplete Feature
Autocomplete is one of the best perks of graphical SQL Editors and TablePlus’s works very well. As soon as you type a few characters of a word in the editor, the autocomplete list will appear below the text. It contains both database keywords as well as objects like tables, views, and columns:
You can run all statements in the editor, the current one, or just the selected text. The results appear underneath the editor. Individual column values may also be displayed if the Field Details pane is showing:
Results Appear Underneath the Editor
Query results may be exported in either CSV or JSON format.
In these times of rampant data theft, you’ll be happy to know that TablePlus never syncs any user data to the cloud. Moreover, the TablePlus website only maintains account information for licensing, managing seats and updates. You can refer to their Privacy and Terms of Service Policy for all the details.
TablePlus is a young project; Pham and his team fix bugs and add new features every day, then put them together in a new update released at the end of the week or month. There is no official how-to guide as of yet, but there is a fair amount of information on their blog, as well as a growing community on Facebook, Twitter, and GitHub. You can also request new features via the issue tracker.
If you’re looking for an inexpensive database management tool that can connect to many DBMS types, then I would suggest that you give TablePlus a try.