Database can be complex and intimidating.
As a developer (especially if you're from the back-end team), how many times have you been bombarded with emails from non-technical team members or clients asking what a field means?
Documenting and organizing metadata of your database is important for that matter, yet it's not our top priority. Due to the database complicated nature, we often avoid maintaining it, or worse, creating it in the first place.
That's where database documentation tools come in handy. Besides helping you create a detailed data catalog for your database with pre-defined formats, tools can be a great asset for the whole team.
This blog post will share with you the true importance of database documentation tools and top 5 tools you should use to improve both your personal and team's productivity.
What is a database documentation tool?
A database documentation tool acts as a data catalog that captures all the information of a database in a simplified tabular format. It is most commonly used as a data dictionary that provides granular details of the table schema, or a single source of truth that provides an overview of all relationships inside the database.
Why do we need a database documentation tool?
A documentation tool has both short-term and long-term benefits.
However, since most people are only aware of its temporary advantage and negligent towards the big picture, they tend to regard those tools as a temporary solution.
Some of the more obvious short-term benefits of database documentation tools are:
- Help developers quickly prototype a database structure for the current project
- Business members (sales, BA) can easily understand the context and meaning of table fields with rich metadata that developers input
However, it is the long-term benefits that make database catelog tools truly valuable:
- Tools can help developers generate a holistic database document that acts as a single source of truth for everyone. Now, the knowledge of the database is not locked in any senior developers and walked out of the door as they leave the company. It stays.
- A shared data wiki can help avoid future conflicts when the team expands. Senior developers can easily onboard newbies with one source of data.
- Some tools allow shareability where external users can access the most up-to-date version of the database document online. No need for manual screenshots or meetings. This will help save your coding time, yes, but also improve the business' productivity and reputation in the long run, especially if your business is in the consulting industry.
The top 5 database documentation tools in 2020
2020 users require 2020 design styles.
No one would like to use those tools with the design style dating back to the 90s.
Also, the data analytics landscape is becoming more complex. Teams are more aware of building a scalable analytics stack that puts a greater focus on their data warehouse.
Consequently, databases are growing in complexity and magnitude to handle a lot of data sources.
Therefore, we need powerful tools to accommodate the growth of databases, yet they must be lightweight and beautifully designed enough to save time for our increasingly busy developers.
Below we have curated a list of 5 database documentation tools that match such requirements:
- ApexSQL Doc
- RedGate SQL Doc
dbdocs.io is a free, simple tool to create web-based database documentation for your database. Unlike most of its competitors, dbdocs.io stands out as the most light-weight and doesn't require a complicated setup.
Also, dbdocs has its own DSL language that helps developers quickly define and document their database schema and structure. You can check out a sample document here.
dbdocs is in its beta stage so it's completely free. You can get started here.
- Light-weight and simple to set up. The only requirement to install dbdocs via your CLI is having NodeJS and NPM (which most of the developers already have). No need to connect it to any database.
- A super fast way to document database using DBML - its own open-source DSL language. You have complete control over which schema, relationship, and metadata you want to add to the document.
- Have a visualized overview of the database structure as well as an individual view of each table and its relationships. You can use dbdocs with dbdiagram.io to quickly prototype your database ERD.
- Easily share the document with team members by a simple link. If you're working with clients, use password protection to keep the file private and accessible only to those with a password.
- Easily integrate into your workflow. Create a DBML file in your git repo and update it every time a commit is made.
- Available for any operating system. dbdocs only requires a simple code edtior and a CLI tool to generate the document (Terminal in Windows and Mac, for example), which means it's useable by any user!
- Instant email support
- dbdocs.io is a fairly new tool so it lacks certain features like version control or deep editing capability. But according to their developers, those features are already on the roadmap.
- dbdocs hasn't allowed collaborative editing. Currently, each developer can edit the document using the local DBML file, so changes one made will not be reflected in the document of another.
- dbdocs hasn't supported
exportfeature like most of its competitors, but it's going to be available soon.
Dataedo is an advanced database documentation tool that includes data dictionary, ERD, and business glossary.
It will connect to an extensive list of data sources (i.e. MySQL, PostgreSQL, Oracle, Amazon Redshift...) and extract metadata, to which you can add more meaningful custom information.
Like dbdocs, Dataedo allows developers to visualize data models with ER diagrams.
Dataedo is available in three pricing plans: Pro, Pro+, and Enterprise.
The Pro version costs $39 (£30.11) per user per month with standard email support and five custom fields. The Pro+ version costs $79 (£60.99) per user per month with 10 custom fields and priority support.
The Enterprise version costs $159 (£122.74) per user per month for 100 custom fields with extra features like Schema Change Tracking and Business Glossary**.** They also provide a custom pricing plan in case you do not find the current plans suitable.
- Support a large base of data sources
- Easy-to-create ER diagrams: Simply drag and drop the data tables and Dataedo can automatically detect and visualize the relationships between them.
- Rich metadata: Users can describe data elements with custom fields and rich texts, or organize tables into different modules
- Shareability: Besides PDF and Excel format, users can generate an interative HTML that does not require a dedicated server
- Startups may find the pricing a bit overhead.
- Complicated setup
- The tool requires a steep learning curve to fully utilize its power
- Only available for Windows users
ApexSQL is a powerful tool for not only SQL but also SSIS, SSAS, SSRS and Tableau documentation. ApexSQL's most impressive feature is customization. You can fully customize the documentation and its layout with different styles, custom text, themes and graphics.
ApexSQL costs $699 alone per user. This sounds like a reasonable investment in the long term, but it means the tool is restricted to only one user.
- Powerful customization and personalization capability
- Support documentation for a wide range of services
- Easily automate and schedule documentation
- Support a wide range of output formats (CHM, HTML, Word, MD and PDF)
- Include graphical relationship and Data model diagrams
- Only available for Windows users
- The high upfront cost for teams with multiple users who want to collaboratively document their database
- Require a steep learning curve to fully utilize its power
- Outdated design style
Redgate SQL Doc
Redgate SQL Doc is a tool to help you generate a document for your database quickly and automatically. You can obtain an overview of a database schema, inter-object and inter-database dependencies, with annotations to help users find what they need. You can also document databases within SSMS by right-clicking on object explorer.
You can check out a sample here.
Redgate SQL Doc caters for a wide range of usage. If your team has 1-4 users, it will cost $385 per user (lifetime), including 1 year of support and upgrade. If your team has 5-9 users, it will cost a bit less at $327 per user. If your team has 10-20 users, it'll be $308. For more than 20 users, you can contact the team for a personalized quote.
- Easily automate documentation from CLI
- Support a wide range of output formats (PDF, HTML, MS Word)
- Redgate SQL Doc is designed for viewing purposes mostly, thus it has very limited editing capability. You cannot change table fields, types, values, which are basic components of any schema. You can only edit the descriptions.
- Outdated design style (both UI and UX)
- Cannot create ER Diagram
SchemaSpy is the second free tool on this list. It is a Java-based, CLI-based tool (requires Java 5 or higher) that analyzes the metadata of your database schema and generates a visual representation of it in a browser-displayable format
You can check a preview of a document here.
SchemaSpy is free to use.
- SchemaSpy is free and lightweight. If compared to RedGate SQL Doc, which has almost the same set of features (except generating ERDs), then SchemaSpy definitely has a pricing advantage
- Easy to set up and configure
- SchemaSpy can scan and generate comprehensive metadata of the database (tables, columns, constraints, relationships, orphan tables, anomalies, routines)
- Support Markdown for table comments!
- The project hasn't been updated for nearly a year (the latest version v6.1.0 was released on 18th September 2019). It looks like the owner of this amazing tool will not be supporting it actively, so if you look forward to having constant updates and new features then this might not be the tool for you.
- SchemaSpy editing capability is limited. It can neither add comments to table components nor directly edit/add more metadata of the current schema.
- Outdated design style (especially the diagrams)
Each documentation tool is designed to serve a particular need, so it's of great importance to know firsthand your business's needs.
If you simply want to generate a read-only version of your current database then RedGate SQL Doc and SchemaSpy are the best options. While SchemaSpy is suitable for teams with a limited budget, RedGate SQL Doc might give you a little bit more assurance as the product is still being actively developed.
You should definitely choose dbdocs.io if you need to enrich your document with more metadata and visually beautiful ER diagrams. Its own language (DBML) does not require much learning and is easier to write, read, and maintain than traditional DDL language (like SQL). Also, this tool is especially preferable by consulting startups/small and medium enterprises as it is free, has shareable documents with password protection and usable on any OS.
A possible alternative to dbdocs.io is dataedo, if your business is large and has a significantly dedicated budget for database documentation. It has slightly more features than dbdocs.io that require a steeper learning curve, which might become an obstacle to employ.
If your priority is documenting a wide range of services (and heavy customization), then ApexSQL is second to none! Similar to Dataedo, this tool is suitable for teams with a large budget for the data team due to a high upfront cost and per-user pricing model.
Sign up for more Inside Holistics
Stories, opinions, and lessons learnt
while building a BI startup in South East Asia.
No spam, ever. We respect your email privacy. Unsubscribe anytime.
From SQL Queries To Beautiful Charts
Connect to your database and build beautiful charts with Holistics BILearn More
"Holistics is the solution to the increasingly many and complex data requests from the operational teams"
Tang Yee Jie
Senior Data Analyst, Grab