Have you heard the expression "there's an app for that"? Well, it's true. Every day on the bus you can observe riders immersed in their phones. While some are no doubt playing Pokemon, you may find that many commuters are in fact hard at work, already getting started on the day's agenda. Modern smartphones and other mobile devices have ushered in a new work paradigm where the office is where ever you are. An unwelcomed burden to some, to on call personnel like database administrators, the benefits of having 24/7 remote access to databases via mobile apps cannot be overstated.
Perhaps the main catalyst for mobile database management apps is the growing popularity of cloud database services like Amazon Aurora, Google Cloud SQL, and Oracle Database Cloud Service. Despite having their own web consoles and tools for working with cloud databases, many DBAs still like to work with their favorite tools. That being the case, application developers have responded with some excellent offerings.
One such product is called SQLPro. In today's article, we'll explore SQLPro for MySQL iOS's many features and get a feel for how it can help manage your publicly available and cloud databases from wherever you happen to be.
A Little Background
SQLPro for MySQL iOS is a lightweight but powerful MySQL and MariaDB database client, developed by Kyle Hankinson, who's been developing Mac apps for around ten years with a primary focus on database applications. SQLPro for MySQL allows quick access to MySQL/MariaDB Servers including cloud services such as Amazon RDS, Microsoft Azure and Digital Ocean. Hankinson has also created editions of SQLPro for MSSQL, Postgre, and SQLite. Recently, his company, Hankinsoft Development, released editions of SQLPro for iOS, which has been very well received by the MySQL community.
Some of SQLPro’s many features include:
- Intellisense/SQL autocompletion
- Syntax highlighting
- Tabbed based interface for an optimal user experience on iPad
- Multiple concurrent query execution
- Quick access to tables, columns, etc.
- Dark & Light themes which can be switched manually or automatically adjusted by your devices brightness
- Export options including CSV, XML and JSON
- SSH Tunneling (password authentication only)
- Support for both iPhone and iPad (with specific iPad Smart Keyboard support)
- Images stored in a database are detected and can be previewed and/or exported
The rest of this article will explore some of the above features in more detail.
Establishing a Connection
On startup the app shows all of your stored connections. If it’s the first time you run the app, you’ll see the Northwind sample database, along with a message that points you to the button to create your first connection.
The connection screen has all of the fields that you’d expect to see for a MySQL connection, as well as a couple of advanced settings like the Database to connect to and SSH Tunneling information. At this time, only password authentication is supported, but public key authentication will be added soon:
SQLPro’s Connection Screen
Working with the SQL Editor
The SQL editor takes up most of the screen real estate (as it should!), while database objects like tables & views are listed on the left and query/data output is displayed at the bottom of the screen. The editor provides syntax highlighting autocompletion so that typing a few letters of a database object will pop up a list of suggestions below the text:
SQLPro’s SQL Editor
You’ll notice that most of the screenshots feature a white screen background. Depending on your device brightness settings, you can switch between Dark and Light themes. Here is what the Dark theme looks like:
SQLPro’s Dark Theme
You don’t have to bother commenting out statements when working on more than one at a time because SQLPro has no problem with executing multiple statements simultaneously. Arrow buttons are included on the right of the Results pane to navigate between them:
SQLPro has no problem with executing multiple statements
Queries may be saved to a file, emailed, added to Notes, and even shared using AirDrop. If you aren’t familiar with AirDrop, it’s a mechanism for sharing content with other Apple devices nearby. If they turn on AirDrop in their device’s Control Center, you’ll see their names on the Save screen. Tapping on a name will send a copy of the SQL statement to their device.
Share Using AirDrop
Database Object Actions
Beside each database object in the left-hand pane, you’ll see a down-pointing arrow. Clicking it expands the available actions for that object. For instance, a table will have four associated actions:
- Select top 100 rows
- View create statement
- Drop table
- Truncate table
Database Object Actions
Query Result Actions
Once you’ve gotten some results from a query, these may be edited or even deleted (with confirmation) directly. To edit a record, just click it. That opens the Edit Record dialog:
Query Result Actions
Other Query Result Actions are accessible by clicking on the ellipsis (...) at the top right of the screen. These include:
The Export and Find tools in particular are worth elaborating on.
Query results can be exported in multiple formats such as CSV, JSON or XML. Exported files can then be opened directly into other applications such as Numbers or Excel.
The Find command helps you locate text within an active query; great for really long statements!
More on Pricing
SQLPro for MySQL allows for the creation of a single fully functional database connection as well as a built in read-only sample connection. Professional users who need to create more than one connection will need to purchase the SQLPro Premium subscription of $9.99 USD per year.
Although SQLPro for MySQL iOS is still a relative newcomer to the mobile database administration arena, it’s already generated a lot of buzz. I would personally rate SQLPro’s intuitive UI and highly affordable licensing as its top features. Beyond that, SQLPro is jam packed with a lot of advanced functionality like SSH Tunneling, query result editing, and multiple statement execution. With all that going for it, SQLPro for MySQL iOS is well worth checking out.