Automate dbdocs action to convert generated SQL to DBML

[jmiranda]

After talking to the dbdocs team about the service I realized we're not automatically updating openboxes.dbml in the source code. In fact, we're not even periodically updating it manually. It would be great to use Hibernate export-schema or Liquibase to export SQL that can be converted to DBML and used to publish the documentation.

We might need to manually update the openboxes.dbml file in order to add grouping, descriptions, etc.

We might also want to restrict updates to PRs merged to develop rather than ALL commits/PRs.

[jmiranda]

Thinking this might be low-hanging fruit, I took a crack at this tonight.
https://community.dbdiagram.io/t/anyone-have-success-setting-up-a-continuous-integration-using-github-actions/1465

Checkout code

git checkout feature/upgrade-to-grails-3.3.10
git checkout -b 4094-automate-dbdocs

Install / configure dependencies

npm install dbdocs
sdk use grails 3.3.18
sdk use java 8.0.302-open

Export schema

grails schema-export

Error

Caused by: org.gradle.execution.TaskSelectionException: Task 'schemaExport' not found in root project 'openboxes'.
	at org.gradle.execution.TaskSelector.getSelection(TaskSelector.java:116)
	at org.gradle.execution.TaskSelector.getSelection(TaskSelector.java:81)
	at org.gradle.execution.commandline.CommandLineTaskParser.parseTasks(CommandLineTaskParser.java:42)
	at org.gradle.execution.TaskNameResolvingBuildConfigurationAction.configure(TaskNameResolvingBuildConfigurationAction.java:44)
	at org.gradle.execution.DefaultBuildConfigurationActionExecuter.configure(DefaultBuildConfigurationActionExecuter.java:48)
	at org.gradle.execution.DefaultBuildConfigurationActionExecuter.access$000(DefaultBuildConfigurationActionExecuter.java:25)
	at org.gradle.execution.DefaultBuildConfigurationActionExecuter$1.proceed(DefaultBuildConfigurationActionExecuter.java:54)
	at org.gradle.execution.DefaultTasksBuildExecutionAction.configure(DefaultTasksBuildExecutionAction.java:44)
	at org.gradle.execution.DefaultBuildConfigurationActionExecuter.configure(DefaultBuildConfigurationActionExecuter.java:48)
	at org.gradle.execution.DefaultBuildConfigurationActionExecuter.access$000(DefaultBuildConfigurationActionExecuter.java:25)
	at org.gradle.execution.DefaultBuildConfigurationActionExecuter$1.proceed(DefaultBuildConfigurationActionExecuter.java:54)
	at org.gradle.execution.ExcludedTaskFilteringBuildConfigurationAction.configure(ExcludedTaskFilteringBuildConfigurationAction.java:47)
	at org.gradle.execution.DefaultBuildConfigurationActionExecuter.configure(DefaultBuildConfigurationActionExecuter.java:48)
	at org.gradle.execution.DefaultBuildConfigurationActionExecuter.select(DefaultBuildConfigurationActionExecuter.java:36)
	at org.gradle.initialization.DefaultGradleLauncher$CalculateTaskGraph.run(DefaultGradleLauncher.java:305)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor$RunnableBuildOperationWorker.execute(DefaultBuildOperationExecutor.java:301)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor$RunnableBuildOperationWorker.execute(DefaultBuildOperationExecutor.java:293)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.execute(DefaultBuildOperationExecutor.java:175)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.run(DefaultBuildOperationExecutor.java:91)
	at org.gradle.internal.operations.DelegatingBuildOperationExecutor.run(DelegatingBuildOperationExecutor.java:31)
	at org.gradle.initialization.DefaultGradleLauncher.constructTaskGraph(DefaultGradleLauncher.java:190)
	at org.gradle.initialization.DefaultGradleLauncher.doBuildStages(DefaultGradleLauncher.java:145)
	... 67 more

Needed to add Hibernate5 plugin to classpath

dependencies {
        classpath "com.bertramlabs.plugins:asset-pipeline-gradle:${assetPipelineVersion}"
        classpath "org.grails:grails-gradle-plugin:${grailsVersion}"
        classpath "org.grails.plugins:database-migration:${databaseMigrationVersion}"
        classpath "org.grails.plugins:views-gradle:${grailsViewsVersion}"
        classpath "org.owasp:dependency-check-gradle:${owaspVersion}"
        classpath "org.grails.plugins:hibernate5:${gormVersion - '.RELEASE'}"

    }

Trying again

grails schema-export

Success

024-04-26 21:14:29,659  INFO [main      ] grails.boot.GrailsApp                   : Application starting in environment: development
Generating script to ./build/ddl.sql in environment 'development' for the default DataSource
2024-04-26 21:14:29,977  INFO [main      ] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000227: Running hbm2ddl schema export
2024-04-26 21:14:30,029  INFO [main      ] o.h.t.schema.internal.SchemaCreatorImpl : HHH000476: Executing import script 'org.hibernate.tool.schema.internal.exec.ScriptSourceInputNonExistentImpl@5d73530f'
2024-04-26 21:14:30,032  INFO [Thread-12 ] g.u.s.DevelopmentWebApplicationContext  : Closing grails.ui.support.DevelopmentWebApplicationContext@7a3e5cd3: startup date [Fri Apr 26 21:13:40 CDT 2024]; root of context hierarchy

[jmiranda]

Ok, now we have a generated file here and we just need to pass this to dbdocs.

./build/ddl.sql

But first, we install dbdocs

npm install -g dbdocs

Success

pm WARN deprecated @oclif/screen@3.0.8: Package no longer supported. Contact Support at https://www.npmjs.com/support for more info.
/home/jmiranda/.nvm/versions/node/v14.19.0/bin/dbdocs -> /home/jmiranda/.nvm/versions/node/v14.19.0/lib/node_modules/dbdocs/bin/run
npm WARN notsup Unsupported engine for antlr4@4.13.1: wanted: {"node":">=16"} (current: {"node":"14.19.0","npm":"6.14.16"})
npm WARN notsup Not compatible with your version of node/npm: antlr4@4.13.1

+ dbdocs@0.9.2
added 245 packages from 157 contributors in 13.369s

and the dbml CLI tool

npm install -g @dbml/cli
/home/jmiranda/.nvm/versions/node/v14.19.0/bin/dbml2sql -> /home/jmiranda/.nvm/versions/node/v14.19.0/lib/node_modules/@dbml/cli/bin/dbml2sql.js
/home/jmiranda/.nvm/versions/node/v14.19.0/bin/sql2dbml -> /home/jmiranda/.nvm/versions/node/v14.19.0/lib/node_modules/@dbml/cli/bin/sql2dbml.js
npm WARN optional SKIPPING OPTIONAL DEPENDENCY: fsevents@~2.3.2 (node_modules/@dbml/cli/node_modules/chokidar/node_modules/fsevents):
npm WARN notsup SKIPPING OPTIONAL DEPENDENCY: Unsupported platform for fsevents@2.3.3: wanted {"os":"darwin","arch":"any"} (current: {"os":"linux","arch":"x64"})
npm WARN notsup Unsupported engine for antlr4@4.13.1: wanted: {"node":">=16"} (current: {"node":"14.19.0","npm":"6.14.16"})
npm WARN notsup Not compatible with your version of node/npm: antlr4@4.13.1
npm WARN @babel/cli@7.24.1 requires a peer of @babel/core@^7.0.0-0 but none is installed. You must install peer dependencies yourself.

+ @dbml/cli@3.4.3
added 86 packages from 56 contributors in 6.923s

Convert the SQL DDL file to DBML

sql2dbml ./build/ddl.sql --mysql -o schema.dbml

Huzzah

  ✔ Generated DBML file from SQL file (MySQL): schema.dbml

And now we build the dbdocs ...

dbdocs build schema.dbml
✖ Failed: Something wrong :( Please try again.

Doh.

[jmiranda]

Oops forgot to login

$ dbdocs login
? Choose a login method: GitHub/Google
? Please input your authentication token:  <token>
✔ Validate token
✔ Save credential

Let's try it again.

$ dbdocs build schema.dbml
✖ Failed: Something wrong :( Please try again.