There are tons of tools out there to help make your life easier when working with databases. The most difficult part is figuring out which types of database tools you actually need. We’ll cover the largest tool categories below, starting with tools for the least sophisticated organizations and moving up to those for the most sophisticated.
Database Tool Summary
The most popular categories of database tools are:
- SQL IDEs
- Database IDEs
- Object-Relational Mappers
- Migration Managers
- Data Dictionaries
- Data Modelling Tools
- Database Testing Tools
SQL IDEs
SQL is the bedrock of relational databases. After all, what good is all the data you’re storing if you can’t access or add to it? Even if you aren’t using a database that uses SQL (for example, a NoSQL database like MongoDB), there are IDEs available for your database’s query language too.
Why should you use a SQL IDE?
SQL IDEs provide the features you need to write SQL queries more efficiently. Auto-complete features will save you from typing out repetitive names and SQL keywords. In-editor SQL analysis capabilities will automatically detect, and suggest corrections for, common syntax errors or invalid references. If you spend a lot of time writing SQL, you owe it to yourself to use something better than Notepad. An SQL IDE will save you countless hours spent debugging silly mistakes.
Which SQL IDE should you use?
There are a ton of options out there and you should investigate a few before settling on one. Some of the most popular options include JetBrains DataGrip, Altova DatabaseSpy, and Navicat.
Database IDEs
You’re already using a SQL IDE, but being a database administrator is a lot more than just writing SQL. A database IDE is aimed at helping you complete all the other tasks you need to do. Editing tables, mocking up database designs, and more.
Why should you use a database IDE?
A database IDE can save you a ton of time when making changes to a database. Instead of looking up all the intricacies of ALTER TABLE statements, you can instead make the change directly through a simple GUI. Instead of manually dropping and re-adding foreign keys when you need to change a column, they can handle it for you. A database IDE will save you a ton of time and headaches when you’re changing the structure of your database.
Which Database IDE should you use?
Unlike SQL IDEs, there aren’t a lot of database IDEs out there. In fact, that’s why I created modelDBA. It makes changing database designs incredibly easy. Another option to consider is phpMyAdmin, which offers some similar features for MySQL databases.
You can try out modelDBA here.
Object-Relational Mappers (ORMs)
Object-Relational Mappers, known more commonly by their acronym, ORMs, may sound complicated, but what they do is quite straight forward. ORMs act as an interface between application code and a database. In fact, they can completely manage creating and updating tables for you.
Why should you use an ORM?
If you’re an application developer, you may be a lot more comfortable writing code in JavaScript, Java, Python or whatever programming language you’re coding in, rather than writing SQL. ORMs can save you a lot of time you would have spent writing the boilerplate code to connect your application code (i.e. the entities/classes in your application), to the database that persists the data for them. There are some performance concerns related to ORMs (although they have gotten a lot better at this), and they may not be the best option for all situations (such as an existing database), but an ORM is definitely worth looking into if you’re starting a new app.
Which ORM should you use?
This is a bit trickier to answer, since which ORM you use is totally dependent on which programming language your application is being written in. If you’re using JavaScript/TypeScript, consider Sequelize or Waterline. For Python, you could take a look at SQLAlchemy. If your application is written in Java, Hibernate is the best known. Whichever programming language you use, there’s guaranteed to be a few options available to you.
Migration Managers
Migration managers are another key tool for application developers. There comes a time in every application’s lifecycle that a breaking database change will need to be implemented. A breaking change is one that isn’t backwards compatible with earlier versions of your schema, such as dropping or renaming a column. You’ll need to control when that database change will be put into production and synchronize that with the launch of the new version of your application that supports that change.
Why should you use a Migration Manager?
When you write code, it’s a no brainer to use some sort of version control system. What if you accidentally overwrite a file? What if you change something and it has some unintended consequences? Version control means that you can revert back to the most recent working version of your code. Migration managers do the same thing for your database structure. They keep track of all previous versions of your database and help you move between them. This is especially useful if you have multiple environments (development, QA, production, etc). A migration manager will keep you organized and make sure you have one documented place to store the historical changes for your database, and help you move between them as needed.
Which Migration Manager should you use?
Like ORMs, each programming language typically has open source packages that can integrate with your code and continuous integration systems. However, if you’re looking for something that can live independently from your application code, or you use multiple different programming languages, the industry leader by far is Flyway by Redgate.
Data Dictionaries
Eventually, your database or your organization will grow large enough that it will be difficult to keep track of what everything in your database is used for, and what all those acronyms and abbreviations actually mean. As team members join or depart, you’ll need some way of communicating what it all means.
Why should you use a Data Dictionary?
Data dictionaries are the most important pieces of documentation for understanding the functional use of your database. They serve as your most important guide when training team members, or when trying to go back and understand why you chose the column names you used. Like documenting your code, not keeping an up to date data dictionary is something you’ll regret later! A data dictionary can be as simple as a text document or a spreadsheet, or it can be a sophisticated tool that attaches documentation to diagrams. Either way, a data dictionary is key to the maintenance and upkeep of your database.
Which Data Dictionary should you use?
Data dictionaries, like all documentation, are easy to get started with. Just make a list of all of your tables and columns in a spreadsheet and add a note describing what each column is used for. When you get to the point that the spreadsheet feels inadequate, you can explore tools like Dataedo for a more sophisticated way to document and share your data dictionary.
Data Modelling Tools
Data modelling is the act of taking business requirements and data domain information and mapping and transforming them into a database schema that will work best for all the application. Data modelling itself is a huge topic, but at its simplest, you can think of data modelling as a database diagram that describes the entities and relationships stored in your database.
Why should you use Data Modelling tools?
Data modelling tools are most important when trying to modify your database. If you have many stakeholders, it’s much easier to mock up a model of the proposed changes (in a few different configurations) and share and discuss the model rather than in the abstract. They help ensure everyone is on the same page. In this way, they also save you time by making sure that you completely understand all the necessary modifications required for a database change, so you can implement once instead of needing to go back and re-work the change.
Which Database Modelling tool should you use?
Needing to easily model and implement changes is part of the reason why I built modelDBA. You can use it to mock up changes, and when you’re happy, apply your chosen design to the database in a single click! However, if you’re looking for heavy duty tool completely dedicated to the needs of data modellers, you should take a look at a tool like erwin Data Modeler.
Database Testing
You’re probably wondering why database testing is the last tool category on the list. Isn’t testing key to everything related to software development? Yes! Unfortunately, database testing tools are not very well developed. In general, you can integrate basic database tests into your application tests. If you make heavy use of stored procedures, it may be good to have a SQL script prepared to sanity test them. However, compared to the rest of the tools listed here, database-specific testing tools are the least developed.
Conclusion
As we’ve covered above, there are a ton of tools out there to make you a better database administrator or user. Don’t be overwhelmed! Not all projects, teams, or companies need to use all of these tools. Start at the top of the list and introduce the ones that work for you. As your project or organization grows in complexity, consider if adding another tool would make your life easier, or just add to the overhead. The most important part is to get started and give some tools a try. You’ll be more productive in no time.
Did we miss a tool you love? Let us know by reaching out!