I’m Matt, the creator of modelDBA. I love working with databases (this is a great icebreaker at parties). I’ve been a database administrator, done database development, designed huge data models, and more. But I’ve always felt that the database tools I had available to me were holding me back.
Database tool I do, database tool I don’t
I tried everything
Integrated development environments (IDEs) are indispensable for software developers. I’ve tried every database IDE out there. Despite the existence of plenty of options, they all feel the same. Rather than being a great database IDE, the focus seems to be only on making you better at writing SQL, instead of making you better at managing a database. SQL is amazingly powerful, and we wouldn’t have modern relational databases without it. But when you’re designing a database, mocking up a table structure, adding tables and columns, or modeling a redesign, SQL and DDL feel verbose and clunky. Querying data is one part of the job, but it’s not the whole job.
SQL and DDL are key building blocks of relational databases. You can say the same thing about assembly languages and programming. They’re essential, but I don’t want to spend my days writing code in assembly. I have better things to do.
The Database IDE I Dreamed Of
Show me the database
When you start working on a database for the first time, what’s the first thing you want to see? A list of tables? No! Relational databases are so powerful because of their relationships. A list of tables and their columns doesn’t tell you anything about those. There’s a reason why DBAs print out database diagrams and post them on the walls. It’s because they’re the single best way to understand the database you’re working on. With modelDBA, the first thing you see when you open a database is an interactive table diagram.
Here I’ve loaded the “sakila” example database provided by MySQL. Immediately, you can tell which tables are important to this database and which are less important and likely used for reporting. You don’t need to dig through column lists to figure out that staff members are associated with payments as well as rentals. Intuitively, you can see that the customer, staff and rental tables are central to this database, based on all of the relationships that depend on them. You immediately have some understanding of how the database, and the applications that use it, work.
Click. Change. Done.
Seeing the database is one thing, but to be a real database IDE, it needs to be able to actually change your database. That diagram is more than just something to print out and pin up on the wall, it’s interactive. You can double-click on any table to open it up for editing.
Instead of looking up the syntax for altering a column yet again, you can easily make changes using checkboxes and dropdowns. modelDBA lets you add, drop, or alter any column or table. Once you’re happy with your changes you can save them locally to see an updated database diagram.
Changes you save aren’t applied directly to the database. When coming up with a new table structure, you might want to try out a few designs and get feedback before actually making changes.
Once you decide on which changes you’d like to implement, you can apply the changes you want, and undo the changes you don’t. modelDBA handles generating and running the SQL commands needed to make your database look the way you want it to.
Table changes, without the annoying parts
Automatic column renaming
Here’s the scenario. You need to make a change to your table. The primary key column name you used to use doesn’t make sense anymore. So, you want to change the column name. But there’s a problem. That column is referenced by five other tables. Now, to rename the column you need to drop all those foreign key references first, then rename the column and add them back. That’s 11 queries just to rename one column!
ALTER TABLE payments DROP FOREIGN KEY annoying_key1; ALTER TABLE stores DROP FOREIGN KEY annoying_key2; ... ALTER TABLE staff ALTER COLUMN staff_id staff_payroll_id TINYINT(3); ALTER TABLE payments ADD FOREIGN KEY annoying_key1 (staff_id) REFERENCES staff (staff_payroll_id); ... (You get the idea)
If you also want to rename the column in those five other tables, that’s another five queries. Sixteen queries for a simple column rename is insane. With modelDBA, this simple column rename is actually simple. You just edit the column name and save your change. You’ll be prompted that the change will impact the five other tables, and with your confirmation, it will take care of everything for you.
One-click to drop a column with foreign keys
Another change. This time, you’re dropping a column that is referenced by foreign keys from other tables. You’ve got the same problem. You need to first find and drop all of those foreign keys before you can drop that one column. modelDBA will notify you and make that change for you too (with your approval).
Take it one step further. Now you want to remove all of those columns that referenced the column you’re deleting. modelDBA gives you the option to do that too. You’re in control of your tables, and nothing is applied to your database without your explicit approval. If you make a mistake, it’s easy to cancel and undo.
Who broke production?
With modelDBA, it won’t be you. Accidentally making a change to the wrong environment can be devastating. GitLab famously lost a significant amount of data in 2017 due (in part) to applying changes to a production database instead of a development environment.
It should be obvious whether you’re in a development environment or a production environment. In fact, it should be blatant. In modelDBA not only can you classify database connections as being ‘Development’ or ‘Production’, but you’ll know by looking at the interface which environment you’re in.
Production environments turn the interface red, so that you know you should be proceeding with caution. Additionally, any database changes that could result in data loss will prompt you for an additional confirmation before they are applied to the database.
Just getting started
modelDBA is just getting started. Initially, it only supports MySQL, but it has been designed to work with any relational database management system (If you want yours implemented next, send me an email here. I’d love to hear from you). Databases are complex beasts. I want to make them less so. Maybe you love databases as much as I do, maybe you don’t. Either way, I want to make your experience working with them the best it can be. This started off as the database IDE I was dreaming of. I want it to be the database IDE you have been dreaming of too.
Get the latest version of modelDBA here.
If you want to keep up to date with the latest features and capabilities, sign up below.