Database Tools and Such…

Database_Tools

Well here is an interesting post that is not car related… or is it?

I have been jumping back in to doing some PHP and MySQL development and needed some reasonable database tools. I had seen a few that I liked and would love to have DBArtisan, but it’s just crazy expensive and it’s lower cost brother Rapid SQL but if you hit up the Embarcadero site you will not see pricing. That’s because you can’t afford it :) So what you won’t find here is an in depth technical discussion of these products, just quick impressions as I evaluated them. Most are easy to get and you should spend some time with each before sticking with one.  Lot’s of  “2 Cent’s” opinion below take them or leave them.

My main database is MySQL/MariaDB, with some MS-SQL and possibly Firebird (I always liked that database). I’m not a DBA nor would I think of myself as a hard core developer. I can cut code and figure things out and rely on a solid tool base to help out with that task.

My development environment is typically the database on a windows machine where I just do SQL work, or if I’m trying to hack some Yii/PHP I will spin up a VirtualBox instance (yeah I know it’s Oracle) with my Mint development environment  and SSH Tunnel with the windows tools into that. The windows tools are just so much better then anything on Linux from a polished UI standpoint (yes that is my opinion).

So on a quest to find a good windows based database tool I came across a few that looked useable. And lets get this off the plate pretty early in this discussion, most tools like Oracle’s MySQL Workbench blow, and I’ll say the UI for most Java based tools are just off a bit.  About the only one that I would consider would be Aqua Studio, it has vast support for databases (looks like mostly through JDBC). It looks OK under windows,  it’s ugly under Linux which could be just taking a bunch of time working with fonts and settings but I have no time for that. Their are a couple of free version in various stages of development some support only MySQL/MariaDB and they were looked at too. I’ll put a list of the tools I looked at later on in the post.  And PLEASE don’t complain if I didn’t find tool ‘XYZ as it’s the best’. Post a comment if you have one, these are just the ones I came across and liked and could afford.

MySQL Workbench – Oracle Corp

I looked at MySQL workbench. First strike it’s under the umbrella of the Oralce Corp. I don’t like them much. Then it has a smelly odd Java based UI that I just didn’t like. It does do a lot of things for you and a good FREE tool for MySQL. Looks better on Windows then Linux. It was off the list early. Just didn’t like it.

MyPhpAdmin

You will need to use and install this. But I will say that if you are doing a lot of database work you will ditch this tool as fast as you can. It’s nice that it is almost ubiquitous with any LAMP stack install and generally available when you can’t get SSH or other shell access to you development machine. Other then that I find it slow (due to clunky web interface) and sometime just doesn’t work as expected due to the odd way you work with it’s interface. Again you should know how to use this and make it your friend but I would not want to be the one that uses this as a primary tool unless I really needed to.

Heidi SQL

I liked this one, and I think has some good promise. The interface was nice, supported native 32 bit and 64 bit Windows and supports MS-SQL as well as MySQL/MariaDB. I had actually found this as I recall it came with the MariaDB installation package for windows. This looks to be very actively developed and seemed to work well given it’s set of functionality.  I didn’t do a lot with it but looked at the overall functionality and seem like a good start and has a good growth path, but lacking in some of the more hard core database management tools that some of the others have.  If you are looking for a good free tool I would put this on the short list.  Interface was snappy an overall seemed like a good tool with a fair amount of functionality.

 Database Workbench – Upscene

I had come across Database Workbench while of all things reminiscing about Interbase and the Firebird database. I had come across this gem on one of the pages that listed applications that supported the Firebird database. It’s a nice looking application written in the beloved Delphi. Looked interesting so I took a look not expecting it to support any other databases. What I found was a big surprise, it seems that not only did it support Firebird, but a good amount of databases, including Interbase, MySQL, Oracle, MS-SQL Server,  SQL Anywhere and one called NexusDB.  They also had a full suite of SQL debuggers, Data Generators, etc for the extra hard core DBA.  Much of that stuff in some form is supported in the Database Workbench PRO package. The tool is available in a FREE and paid version. The Free version is limited to supporting a small set of databases (you can only access the first 5 and can’t pick which 5), and doesn’t contain some of the higher end functions of the PRO version. I really liked the windows interface, flexible, fast and felt like it was not an impediment to doing work.  That being said it was just a bit more of a learning curve then some of the simpler tools, but once you get familiar with the interface it’s fast and powerful.  I like the way the SQL Editor works as well as SQL execution. Navigation is nice via the typical tree which shows a lot of information without having to dump DDL.  I had used some of the data pump in trying to move a MS-SQL database to MySQL with good results, not perfect, but very good with minimal rework to get things moved. The PRO version has diagramming and other useful additions to the free version, and about the only thing that is not included in the PRO version is a generic report generator (or I have not found it).  One other nice feature is that the tool is smart, it is not just doing work for you, but helps you when it see something common, i.e., when dumping contents of a database with an auto increment field it’s notifies you as such and options how to deal with it. That is a simple case but I see lots of forethought like this in the tool to help the bewildered developer like myself! So far this was at the top of my list. Pricing model for the PRO was a base price for the application and additional per supported database. Prices were reasonable given I don’t really need access to Oracle or Sybase but could add it at a later date. One nice missing feature was SSH tunnel into a remote box directly from the tool. While it didn’t directly support it, it’s easy to accomplish with Putty (free) so only a minor inconvenience. The tools also had a good amount of configuration and option settings, which is a nice thing. This tool was a final contender!

Navicat Premium

I started looking at Navicat’s website prior to looking at some of these tools and liked what I saw, but trying to figure out the pricing and option model from their site was confusing. I just ended up looking at the Navicat Premium which includes all databases and options. Pricing was not bad inline with Upscene’s Database workbench, but potentially better if you need a few different database support.  I think this tool has a lot of promise, and was pretty easy to use it out of the box. I though I would like the interface a lot, but the more I used it the more I found it odd. That’s right ODD.  It has a nice clean look, but it just doesn’t flow well. The tree navigation only supports a view of some database object, missing in my mind are things like constraints, indexs, keys, that sort of drill down. They are all buried in some other form.  Double clicking on some objects take you to data editors vs. table editors things like that. Data editing is pretty good on a side note. Many things are dockable and adjustable in the interface so you can make it look and feel how you want.  It has a nice modeler and what looks like a really flexible generic report generator. I had spent some time with the tool editing (read hacking) some SQL and found I hated the key strokes to run selected SQL. I do this like a million time since I’m bad at it, and  CTRL+SHIFT+R is cumbersome.  The options setting also doesn’t have much in the way of customizing many aspects of the application. One very nice thing that the Navicat folks put into the tool was SSH tunnel support for logging in to remote servers. This is a nice alternative to PHPMyAdmin’s web interface  if you can get SSH into the box. Overall I still find the tool useful and while evaluating the Premium version they offered a special for the pack at a highly discounted rate so I had to buy it. I think this is a good tool, but with some interesting ways of doing things. This still makes it to the short list as it does work well.

The Conclusion

For the free tools I would definitely look at HeidiSQL, I liked that it was free and seemed to have ties to MariaDB. Looks also like a good community behind it.

For the pay to play tools it would have been a tough choice if I had to buy just one, and if I did it would be Upscene’s Database Workbench. It just has a ton more functionality then anything else in the price range.  Navicat Premium gets the OK as database support is good and again an OK interface with reasonable pricing for the developer, I think this tool is still evolving and is a good alternative. So as it works out I have 2 very good tools and the budget was not blown out. Both have had support releases for enhancements and bug fixes and that also makes me feel good that the tools are supported as well as both have responded quickly to technical questions. Overall very happy with the tools.

If you are looking for a nice tools set for windows here are a few that might make your short list. If you have other send them and I’m always interested in looking.

Happy SQL’ing

Sandy

, ,

6 Responses to Database Tools and Such…

  1. Sandy Ganz March 3, 2014 at 1:01 pm #

    Add another tool to the must check out list –

    dbForge which you can download a demo at http://www.devart.com/dbforge/mysql/studio/

    It’s a single database tool specific to MySQL. It support MariaDB and has a very nice user interface. It has some nice features, but some are works in progress. The SSH connectivity doesn’t work well but does work. It’s an acknowledged probem that’s being worked on or so they say. Editor is good except you can’t triple click to select a line to execute. It does have a lot of configuration options unlike NaviCat but not even close to DBWorkbench’s options. The more I use Navicat the more I dislike the interface. It’s a good tool as it supports a lot of databases (premium version) but about the only reason I fire it up as it is the only tool so far where the SSH access to MySQL via a remote server works flawless.

    Another area of concern on dbForge is database design. Currently you can do nice schema design but it’s tightly coupled to the database in the sense that as you develop you schema it ‘must’ create the tables, working offline is not an option so if you can’t connect to the database you are out of luck.

    DBWorkbench’s schema tool is way better but not as pretty of a UI in design and you develop a generic schema and then generate it for any specific database. Much better, except the picture is not so pretty :)

    As far as user interface I think it’s my dbForge my favorite as it’s clean and still powerful like DBWorkbench but not quite as complete as DBWorkbench in power. For functionality DBWorkbench is the king so far, again with many smart checks and options to help the bewildered developer. I think the ultimatly DBWorkbench and dbForge for MySQL that will reside on my development machines will ultimatly be DBWorkbench (and hope for the SSH functionality) and dbForge. As I said Navicat seems to have great marketing but the user interface is just flows oddly and mostly bugs me when I try to do thinks in a natrual manner like the other tools (including the others tested)

    One thing that I found that is interesting is that all three tools are based on Delphi after inpsecting the executable. I am a long time Delphi/C++Builder fan from my days as a systems engineer at Borland and like seeing this technology being used as it is solid.

    Again, give these all a try, most have 30 day trials that are unrestricted. This is enough time to find your favorite and most productive tool!

    Back to accidently dropping tables…

    Sandy Ganz

  2. Jarek March 13, 2014 at 1:15 pm #

    I recommend Vertabelo. Vertabelo is an online database designer working under Chrome developed by company I work for.

    The most important feature of this tool is that it allow to share database models across the team and collaborate on them via web browser.

    Others Vertabelo features include:

    Model versioning
    Support for PosgreSql, MySQL, Oracle, MS SQL Server, DB2, SQLite, HSQLDB
    Dynamic/Visual search
    Live database model validation
    Reverse engineering

    Vertabelo is free to use for smaller projects (up to 3 models and 20 tables within each model) and have commercial versions for larger database projects.

  3. Shlomi Noach March 21, 2014 at 5:31 am #

    Nice review. Yes, there are some good tools around. Some have very advanced functionality.
    However I’m always shocked that we still only have one native linux app – MySQL Workbench. The reason being that many (many) DBAs are linux enthusiasts and use a linux desktop.
    I got a request-for-review for some of the aforementoned tools. If it’s a Windows app — it out of the question for me. I haven’t used a Windows desktop in years.
    Whether Windows or Linux are better as desktops is not the point of the above; just that many of the people that count (namely your DBAs) love to use Linux.
    For that, kudos to MySQL/Oracle for providing the only native Linux MySQL development app.

  4. Sandy Ganz March 21, 2014 at 9:48 am #

    Shlomi – I found this to be a problem too. MySQL Workbench is just plain bad on Linux, almost as bad on windows, it’s got suck a clunky interface I _almost_ prefer to use Phpmyadmin! Their are a few good Linux tools more and more as I look around. One that stood out, and it supports most everything database/datastore called Aqua Data Studio.

    http://www.aquafold.com/

    It’s a bit pricy but the java developers that develop are much better then the folks at Oracle ;) Again, not free, but a good tool. I have tried it under both windows and Linux and the Linux version is not too bad. Still a bit costly but hands down a better value then a free tool that’s not so good.

    I see lots of new projects some with good potential, I think we will be seeing more and more tools developed native Linux as time moves on as well as alternatives to Phpmyadmin such as Chive and other web based projects too. Hope some of them get finished :)

    My philosophy about tools is that they are just that. I try to build my tool box with the best tools that I can afford that helps me do a better job, and being limited to a choice of Linux tools would be a… well, a limitation ;).

    Operating systems aren’t a barrier really anymore unless people make it so. Hey, just fire up a VM of your choice and expand the universe of tools you can use. That being said if the DBA’s are productive with MySQL workbench more power to them!

    The best part of all of this is trying out some really good tools and finding some gems or diamonds in the rough.

    I have been using Upscene’s tool a lot and less of Navicat, and more of dbForge to replace it. Next month who knows what gems I’ll add to the box!

    Again thanks for the comment!

    Sandy

  5. Martijn Tonies May 6, 2014 at 6:18 am #

    Thanks for the kind words Sandy.

    For all Linux users out there, Database Workbench runs on Linux with Wine, screenshot available here:
    http://www.upscene.com/support.php?page=dbw_ubuntu_wine

  6. admin May 6, 2014 at 9:08 am #

    Thanks Martijn for the link and info on how to use DBWorkbench on Wine!

    Sandy

Leave a Reply

The home of Smokey Bleach Burnouts