Nobody seems to document SQL codes
I find that SQL code is often undocumented. A lot of SQL code don't get the sweet documentation love that python and javascript codes so often do.
Some commented out lines of code, which supposedly apply a particular business rule over another, are left hanging without what they were supposed to do. Some comments allude to warnings about the consequences of changing particular join conditions, without an explanation as to why.
Many SQL codes can go over a dozen layers of join paths deep, with multiple subqueries, badly written split-apply-combine methods, and inexplicably named aliases.
Someday that might bite us on our backsides
Executives of the business may one day get an automatically generated report one day and question "how the figures were generated" (Think "why is this number so low?"). With people moving from one job to another, undocumented SQL code jibberish is all that the analyst has to go by (if the analyst is given access to code at least).
A lot of data pipelines rely on SQL, and they really should be documented better - explain to the user the code that they are looking at. Throw in a diagram perhaps.
Let's use markdown in a comment block
But we shouldn't have to write a separate document that does these things. Separating the documentation from the code increases the chances that the documentation will not be updated. Documentation should really live in the code as comments. I like the simplicity of markdown, and think it would be nice if I could use markdown within the comment block of the sql to document the code.
There's a tool for that.
A little while ago, I posted about documenting Python code for data science. I had built a package called mindoc that allowed me to document my pipeline code using markdown within my code.
Now I have expanded the tool to help document SQL code and added support for diagrams (using mermaid js)!
See this github page for how it works: https://minchulkim87.github.io/mindoc/
Top comments (5)
I like the idea to use Markdown to comment SQL. Markdown documents could be very easy integrated in WIKI documents. It would be easy to combine "normal" WIKI content with automated generated content.
And I had a look on the mindoc. Currently I have no idea how to use this tool to document a complete DWH. It looks like the tool is good for documenting ad-hoc SQL.
BTW, this is one reason why I recommend to users and testers to use Azure Data Studio and their notebooks. This is great to combine ad-hoc queries with descriptions and comments in markdown.
I found this article searching "database documentation to markdown". Normally I use SQL Server extended properties to document most objects. Markdown could also be used in these properties, linebreaks are supported.
I also will try github.com/k1LoW, after I understand how to start. They want to document databases in markdown, why not to use markdown in "comments"? then these formatted comments could be integrated into the documentation.
One more issue is missing support in frontends: For example I document all my tables, columns and measures in SSAS Tabular, but Power BI does not even support line breaks in comments.
But the idea to use Markdown in descriptions should be promoted. It's very easy.
For a very long time I use sqlspec: elsasoft.org/
They allow to use XML in extended properties to generate formatted descriptions. But Markdown is much easier to use than XML.
But the development has stopped, there will be no markdown support. I use it as it is.
Thanks. I was so focused on making something my team needed that I hadn't elaborated the use. The first "version" was made for documenting single python files used for data pipelines. Because we would use SQL scripts as well for some parts of the ETL pipeline, I figured it would be convenient to use the same package to document .sql files as well as .py files.
I guess there would be SQL specific documentation tools... This mindoc I've made requires a python environment - and I haven't even properly packaged it up yet. Maybe in the near future...
Whether this tool or not, I think using markdown (within comments) to document SQL is useful, at least it has been for my team. Let us know of the documentation landscape within SQL as you test them out (perhaps a blog post here in dev.to!) - I live mainly in Python land and am not knowledgeable.
Cheers
one product I found. Look at this example with nice markdown support in the "comments":
schemaspy.org/sample/index.html
I'd like to second Germo Görtz in saying that a Markdown / Mermaid / Graphviz solution would be really neat. Draw.io has something quite useful but still just for adhoc uses.
In my opinion, it should be possible to write something like that with PowerShell or Python. Maybe I'll consider to write something like that when I'll have the time for this.
Hi
Yes. So the tool I have made and mentioned is a markdown + mermaid documentation tool.