If you work with MariaDB (MySQL) a lot, you understand the need for a good database client. I’ve worked with many different RDBMs in my career. Over the years I have learned what to expect from a good RDBMS client and Sequel Pro – a free database client running on OSX – has most of them.
Most RDBMS clients will let you execute an valid SQL. You can write your own Create statements, Alter statements, or any other Data Definition Language (DDL) and execute it in the query window. A good tool – one like Sequel Pro – will make it easier for you.
Sequel Pro shines in this area by giving you the “Structure” area. You can examine and alter the structure of any existing table and create new ones by simply defining the entities in the form provided. All of the options that you can define in a Create/Alter statement can be set from this form.
Hand coded DDL is a thing of beauty but it can be a pain, especially if – like most developers I know – you only do it every now and then. Having a tool that helps you create your database is a wonderful thing.
Of course this isn’t a substitute for a good “Entity-Relationship Diagramming” (ERD) tool. If your database is going to contain more than 5-7 tables, you are going to need to diagram it out. There are some good tools on the market to do this, but don’t expect it from your client.
Easily see data
When I started learning about RDBMS I was working with a language called FoxPro. FoxPro was awesome because it was a language built around an RDBMS. One of the great features of FoxPro is what we called “Danger Browser”. In Browse mode, we could simply open a table and scroll through the data. The upside is that it was very handy, especially in development, to be able to just see the raw data. The downside was that you browsing the data LIVE. One errant keystroke and you could easily wipe out the data in a field.
Sequel Pro gives you the power of the Browse with it’s “Content” area. When you click on “Content, you can select any table and browse through it 100 records at a time. Like Foxpro, the data is live and you can edit it – accidentally or on purpose – right in the grid.
One really cool feature of Sequel Pro’s Browse is that you can apply a simple filter to the grid. At the top of the grid are 3 simple fields.
- A drop down of all the fields in the current table.
- A list of common conditionals like “equals”, “contains”, starts with”, etc.
- A text box to enter a value.
Using these three fields, you can quickly filter the data to only the records for which you are looking. Once you have the data filtered, you are still in Danger browse mode, you can still edit the data live.
This is the main purpose of the database client, to execute Data Manipulation Language. (DML) Select statements, Updates, Deletes, Inserts. The heart of any CRUD application. The whole point of having a client is so that you can easily run these types of statements. Regardless of whether you are doing maintenance on the database, or testing the query, a good database client will make this task easy.
Sequel Pro does a great job of this. Queries can easily be executed using the “Query” area. The resulting rows – if the query returns a result set – can easily be exported to CSV or even SQL statements.
The Query window can hold multiple statements. Not all RDBMS clients will allow you to do this. Do not take this handy feature for granted.
The “Run” button changes based on context.
- If your cursor is on a statement, then you can “Run Current”
- If you have selected text, – regardless of whether it is syntactically valid DML – you are offered the option of “Run Selection”.
- If your cursor is just past a statement, you are offered the option to “Run Previous”
The only thing to watch out for is properly ending your statements with a semi-colon. Without that, Sequel Pro will assume that two statements are together, even if there are blank lines between them.
Managing users in MySQL has always been a pain. The command line client that comes with MySQL does not make this task any easier. Sequel Pro does. It makes it very easy to manage users, but also the hosts from which a user can connect. Defining the permissions for any given user/host combination is easy and the flow is obvious to anyone who has worked with MySQL.
Previous versions of Sequel Pro had an issue where the program would abort after setting user permissions. The permissions were set, but to confirm them, you had to restart the program. The most recent version – Sequel Pro 1.1, Build 4499 – seems to have cleared this problem up.
Sequel Pro is a good solid Mariah DB (MySQL) client. Sadly for my non-OSX friends, it is only available for OSX. The current version, 1.1, is rock solid on El Capitan. I’ve not had a single problem with it.
Sequel Pro is one of the 2-3 programs that I have open at all times and usually connected to multiple databases. It is a tool that I have come to rely on and one that is easy for me to recommend to any developer.
Until next time,
I <3 |<