The Tools I’m Using

Just some thoughts about the tools and services I am using for my project.

Up until the middle of the batting stats table, I had been running MySQL locally on my late 2012 MacBook Pro 15inch Retina, maxed out with the 2.6 i7 and 16GB of RAM.  It’s still a great machine.

For a while, I had been using MySQL Workbench as my interface.  It’s not bad at all and, for a free tool, it’s great.  It does have some shortcomings in terms of ease of use and importing data.  For example, when using it to connect to a remote database, it insists on treating it like a localhost.  Honestly, after a few hours of live chats with tech support trying to make it work, I gave up on it.

I switched to Querious and I couldn’t be happier.  Not cheap, but it’s a joy to work with.  My favorite feature?  That would have to be the ability to easily import from csv.  Being able to do it at all is pretty fantastic, but being able to clean and manipulate the data easily before import is amazing.  On top of that, the import speeds are blazing fast.

Querious has 4 main views: Content, Structure, Query, and Server.

Content allows you to view the content of tables and views just by clicking on an object in the sidebar.  From within that view, you can filter the results shown by clicking on the “show all’ button.  This pulls down a list of all the columns in the table and allows you to write a quick WHERE clause to filter the data.  A real time saver when you need to do a quick sanity check.

Clicking on the database itself rather than a table gives you a view of every table and view along with data size, index size, auto_increment count, and row count.  My only quibble with this is that the row count does not update automatically.  You have to refresh for each table to see a value.

Structure shows you the structure of the database and the tables.  Clicking on the database will show you a diagram of all the tables, with lines showing foreign keys and relationships between tables.  Clicking on a  table will show you all of the columns, their types, any constraints.  You can switch tabs to see primary keys, indexes, and foreign keys.  You can also add, change, or delete these from this view.  Other tabs show you what triggers exist for tables and allow you to create or change them.  Finally, you can see table info and options – along with an exportable CREATE statement.

The Query view gives you a SQL editor with all the usual bells and whistles: context sensitive highlighting, an intelligent (much more so than Workbench) auto complete feature.  There’s also a context sensitive table reference panel that senses which table you’re referring to and lists all of the columns.  Double clicking on a column will insert the name at the cursor.  You can also switch to a different table if you’re thinking quicker than the AI.

You can also open or create SQL files in a separate window that has all of the same features except for the table reference panel.    The look and feel of both the editors are great, but for reasons mentioned below, I am still doing most of my editing in a different program.

My only complaint at all about Querious is that it seems to be frightened of large sql files.  My 10MB Test Data Load script has never failed to hang the program (beachball) necessitating a force-quit.  Even loading a 1000 line script hangs the program for at least a minute.  That’s loading, not executing.  The way around the test data load is to import the data via csv.  It handles that like a pro.

Still, as mentioned above, I do most of my editing in a separate program.  I use Brackets because the editing environment is almost as good as Querious and the file management is far better.  Perhaps because it was made with web design in mind, it makes it much easier to manage a collection of files, keeping them straight, organized, and easily accessible.  Querious allows you to save sql files and even allows you to put them in folders and favorites, but I can’t seem to get comfortable with the function.  With Brackets, I know where my files are and can flip back and forth with ease.

My remote database is hosted by DreamHost.  I have a number of domains hosted with them and really like them.  Their pricing is fair and have a number of goodies that make setting up portfolios and blogs, as well as provisioning databases, simple.  Their customer service is great.  If you’re interested in setting up hosting with them, ask me for a referral!