0xDBE: A First Look

Tweet

0xDBE is a new database administration tool that aims to make things right in an otherwise extremely barren landscape. Sure, there are other tools, but they all regularly underperform – either on the stability, or the features front.

0xDBE is Jetbrains’ attempt to make it right. Don’t let the name confuse you – it’s not final. DBE stands for “Database Environment” and 0x is just a jab at a hexadecimal representation of the same. In this post, I’ll have a look at the EAP version – please note that everything is still subject to change, and Jetbrains is still collecting feedback and feature requests (already over 400 in the issue tracker!) before they publish the first official release.

Note that I’ll be approaching this tool as a Workbench / PhpMyAdmin convert and as such may not be entirely objective in the matter. The point of a first impressions post, however, isn’t to be objective anyway. I’ll be performing some very rudimentary tasks with the tool and in a way comparing them to the main competitor – Workbench. Note also that the EAP I’ll be testing in this case is version 138.551.

Supported Database Engines

0xDBE will support all major SQL engines in version 1 – that alone is a milestone not many DB management tools can boast. Initially, support will be limited to: MySQL, PostgreSQL, Oracle, Sybase, DB2, SQLite, SQL Server, HyperSQL, Apache Derby, ODBC and H2, while NoSQL support is planned too, for future versions.

You can read about their features on their brilliantly laid out features page, complete with animated gifs to guide you – I won’t be regurgitating their content here. Instead, I’ll try the DBE out on a sample database of mine and just see how it generally feels.

First look

Running the application produces the same experience you’re used to across all JetBrains products – consistency is key for them, and all their products being based on the IntelliJ platform makes it rather easy to accomplish. Immediately, we’re asked if we’d like to import some older settings into the new app, after which we get to select a theme. Being used to the Darcula theme on PHPStorm, I choose that one, though everything is changeable afterwards.

The next step offers a selection of editor window styles, upon which I again select Darcula, when it finally presents me with a selection of preferred SQL dialects. The tool supports a wide variety of DB engines and, as such, needs to ask me for my default preference when dealing with databases – Oracle is, after all, somewhat different from MySQL, for example. You can leave this on “Generic” for autodetection and generic SQL use, but I picked MySQL in this case, as that’s the database I’m going to be using for now.

The default data source I connect to is the Laravel Homestead default MySQL connection. As per the Homestead documentation, the username/password combination is homestead/secret, and the port I’m to connect on is 33060. Let’s give that a go.

Drivers

When you enter the “New Data Source” menu and select “MySQL”, you’ll be able to enter the connection parameters, but you won’t be able to initiate the connection. This is because the MySQL drivers aren’t installed into 0xDBE yet – in order to keep things light and fast, the tool is stripped of unnecessary drivers until they’re needed. Installing them is as simple as clicking a download link next to the “Missing Drivers” warning:

This allows 0xDBE to be faster than the competition from the get-go. By preventing – not reducing – bloat, they made sure you’re only using what you absolutely need, while at the same time allowing you to easily grab the necessary add-ons with a single click.

Creating a DB

Having verified the connection parameters, I’m returned to the main window after clicking OK, and am given the list of databases that exist on the VM. Disappointingly, I have no option to create a new schema via the left sidebar and am left with using the editor to do so.

JetBrains says there’s a lot to still improve on this front, and adding this option is definitely in the pipeline. Some other common but currently missing features include user management and permissions, but all are planned.

Already we’re noticing some super fast auto-completion for SQL commands – nothing we aren’t used to from other JetBrains tools. Comparing the auto-completion speed with Workbench, 0xDBE leaves it miles behind. We’ll see how it performs on an actual populated database, though.

Creating a Table

The table creation dialog was disappointing. While it’s quite nice to see the query forming before me, it lacked the common options like “unsigned”, “auto increment” and others that are present in, for example, Workbench:

These features are absent right now due to the genericness of the tool – in an effort to support as many engines as possible, it had to reduce the features to the lowest common denominator. Once 0xDBE can successfully derive the table’s context from the underlying data source, these features will be added.

Sadly, there’s no visual model builder like in Workbench either – the instability and outdatedness of that app aside, the model builder is one of the most useful database design tools I had ever come across and I use it fairly regularly. However, JetBrains did say they intend to ship some visual tools with 1.0, and others with later versions – so they’re definitely on the roadmap.

Testing on a real DB

In order to properly test the functionality of the IDE, I decided to import an old discarded database backup I found lying around from my days at the old job. Unzipped, it comes down to a decent 1.3GB, which should be enough for a decent use case.

Unfortunately, there is no way to import an SQL backup directly through the IDE, so I had to resort to the command line.

Having imported the data, I tried performing some queries across FK linked tables, focusing on the tables with the most rows. What I found was that while the results appeared lightning fast, they lacked some information I would have liked to know. For example, there’s no total row count anywhere to be found – you need to do a separate COUNT to get that.

Initial Impression

While the autocompletion really is lightning fast, and being able to auto-format the queries to look good is priceless, those are all mostly cosmetic features that don’t make up for the lack of functionality. Some areas in which it does stand out are ease of export – you can instantly copy any result set to an SQL, CSV, TSV, XML or JSON file, or even to an HTML table, right from the context menu. Having inline errors as you write the query is something that’s missing in all SQL editors out there, and being warned of an error before you try and execute it is a huge timesaver. There’s also the ability to rename a table column across all of its instances – from comments to foreign keys, all at once, in all usages – also an enormous time saver. The table editor is a blast and super easy to use, very intuitive when doing changes directly on the data.

However, if you’re coming from tools like Workbench, this tool may not be the right choice for you – yet. It currently seems like a jack of all trades, but that implies mastery of none, and while I do see potential in this tool, I’m skeptical of the direction it’s heading in. By trying to cover too much ground, it seems to be spreading itself too thin across all SQL engines (and in the future all NoSQL engines). This looks like it might slow down progress – though JetBrains say they love a good challenge and won’t back down from the notion of fixing the SQL management landscape regardless of how intimidating it seems at first glance.

I won’t be using this tool just yet for my projects, but I’ll definitely be keeping an eye on it and testing all future EAPs thoroughly, at least from the MySQL and PostreSQL end. How about you? Did you give the EAP a go? What did you think?

Get your free chapter of Level Up Your Web Apps with Go

Get a free chapter of Level Up Your Web Apps with Go, plus updates and exclusive offers from SitePoint.

  • Lewis Cowles

    Sorry but this adds nothing to MySQL users who have had workbench for years… I Cannot recall ever using SQL server and not having access to the microsoft IDE, hopefully it helps people with PostgreSQL and Oracle etc…

    • gggeek

      Sorry, people on Oracle have TOAD, which is by a long stretch the best DB-IDE I’ve ever seen. It was in fact better ten years ago than most mysql tools are now.: source code versioning, integrated tuning workbench, object compare, session inspection, report and the kitchen sync (btw, a freeware version can be gotten with a 1-year time limit, if you want to compare it)

      It should not be too hard for the JetBrains to beat the free/official sqldeveloper IDE from Oracle, which has a limited feature set and clumsy interface.

      Personally I have not a lot of hope for cross-db tools: most of the value-added bits are really db-specific, and if you want to support everything either you end up with a gigantic codebase, or you simply replicate the cli connection tool + allow backups

  • Tatsh

    Sorry but why would you be working on things that require you touch the database directly? Tools like this are just like SSH logging into a machine and ‘fixing’ a problem (which is *never* really a fix).

    I see not much of an issue with reading non-secure data (and especially if you can set a read-only mode, or read from a slave that is running in read-only mode), but there should only be a few ways to get/modify data in any database/storage system (i.e. an API, the website, etc). A client tool like this should *never* be one of them. Any decently designed storage system will only have well defined, logged points (yes, SOX compliance) at which to input/modify data (and often time read). Also if you need a tool like this for reading, you should probably improve your API or website or application that uses such a database.

    Tools like this just encourage bad behaviour, they tend to leave nobody held accountable when things break, and typically by default never set the correct SQL modes compared to the app (namely ERROR_ON_DIVISION_BY_ZERO for MySQL, which 99% of all frameworks do on connect, but Workbench does not).

    • http://www.sitepoint.com/ Dave Slutzkin

      Fair point, Tatsh, but it depends on your environment. If you’re at a massive business that cares about SOX compliance, needs heavyweight audit trails because it can’t trust all of its employees, and has the resources to make all this happen – then sure, your points are valid.

      On the other hand are feisty startups like us here at SitePoint. We have a small number of developers who we place a lot of trust in and they repay that, so audit trails aren’t always required. For reporting purposes it’s often quicker for me to jump onto a slave and do some bespoke SQL queries. And even for writes, sometimes it’s quicker the first time to fix something directly on the server. Takes two minutes. If you find yourself doing it often then sure, write an API endpoint or UI to make it easier. But you’ve saved yourself a lot of time if the fix never needs to happen again.

      Neither is necessarily right or wrong; it’s important to note the differences in business requirements.

  • ElDerecho

    It looks good, but I’m going to stick with HeidiSQL for managing MySQL servers. Its open source, mature, stable, and can connect to remote servers via SSH. Perhaps if one was already invested in the JetBrains ecosystem this might be more interesting.

    • Jody

      I wouldn’t call Heidi stable. About once a week it crashes hard on me. If not for the query history it would be unusable. It is robust though.

  • Bruno Seixas

    Just like you said, I wont used it in my projects for now but I will try to follow it´s development.

  • Lewis Cowles

    Thanks Joeri and gggeek for coming back ;)

  • thecity2

    I’ve been using it for several weeks, and the only feature I really miss is the ability to import data. For that I just use SequelPro when I need to. The auto-fill and everything else seems miles ahead of other IDE’s I’ve used.

  • scragar

    I love it over SQL workbench because of code completion and working hints. The database diagrams it produces look beautiful as long as you’re using foreign keys. In my opinion though, if you’re using a GUI to create your database you’re not the target of a tool that claims to be for DBAs, everyone who wants to understand their database engine at a low level write the code by hand to ensure they’re considering every option, developers of good pages don’t use WYSIWYG editors to create the pages, and developers of good database solutions don’t use wizards.