Free Plugin: Database Schema + DBML

[ben13]

We published a free plugin last month that generates a DBML schema (https://www.dbml.org) for your bubble database but didn’t make an official annoucement. We have been testing the plugin for a month now and it has been functioning quite well so we wanted to make a more general post in case other users might find the plugin helpful.

The plugin has an element that you place on the page. The schema text is automatically generated and then you can copy and paste it into dbdiagram.io - Database Relationship Diagrams Design Tool and generate a visual schema that looks like this:

Today we also published an update that adds a SSA which uses DBML core and allows you to convert your DBML database script to postgres, mysql or mssql schema syntax. This enables you to easily recreate your database in a different database engine with very little effort.

The link for the plugin is here:

SQL DBML Schema Plugin for Bubble, by BuildIt Branding

If you find any bugs or issues or if you have ideas to improve the functionality, please do let us know.

[troy.roberge]

Sounds awesome. Bigger question, can you add to this something that looks at all of your pages, workflows, etc, and makes a chart or something?

[ben13]

@troy.roberge I’m not 100% sure if I understand, but I think you are talking something that I saw posted a while ago here: [New Extension] Bulk import from CSV file for Option Set - #38 by bubble.trouble

There is a link on that post for the bubble app that has a demo of the functionality here: https://double-trouble-bubble.bubbleapps.io/version-test/workflow-documentation?debug_mode=true

Maybe you can clarify if this is what you were describing or something different?

[troy.roberge]

No I’m thinking about something that would look like a chart. So think about any page. At the top you would see the main page, then below you would see groups below it, and attached workflows and such.

It’s likely something that would never be made, or couldn’t be, but it would be handy to have your page able to be viewed in a different fashion than simply through the editor. I can make a mock-up later if this makes no sense lol.

[Nakita]

General Error
WARNING: Error parsing plugin code for update (DBML Schema). Please check
function(instance, properties, context) {

if(window.bubbleToSql && window.bubbleToSql.initialized === 0) {
console.log(“-- Start Building DBML Script --”)

window.bubbleToSql.generateScripts = function() { 

    var tables = this.customTypes;
    var setup = '';

    var tableArr = [];
    for(i=0; i t.name == ct)) {
               	refs += Ref: custom.' + ct + '._id > custom.' + tableName + '.' + cname;
               }                
            
            ct = c_type.replace('custom.','');
            console.log('ct: ' + ct);
            if((c_type.startsWith('custom.') || ct == ('user')) && tables.find(t => t.name == ct)) {
               	refs += 'Ref: ' + c_type + '._id - custom.' + tableName + '.' + cname;
               }

        }

        var str = `

TABLE custom.${tableName} {
COLUMNS`;

        str += `"Created By" nvarchar(max) 
"Created Date" nvarchar(200) 
"Modified Date" nvarchar(200)
"_id" nvarchar(50)
"CreatedOn" datetime2(7) 

}

`;

		str += `

${refs}

`;

        str = str.replace('__COLUMNS__', columns);
        tableArr.push(str);
    }

    var sql = setup + tableArr.join(' ');

    return sql;
};    



var n = window.appquery.custom_types().map(x=> x.json.__name);

bubbleToSql.customTypes = [];


for(i=0; i < n.length; i++) {
    var c = window.appquery.custom_type(n[i]).json._child("%f3").child_names();
    bubbleToSql.customTypes.push({"name" : n[i], "columns" : c});
    
}    

document.getElementById('cs-container-dbml').innerText = bubbleToSql.generateScripts();

window.bubbleToSql.initialized = 1;

console.log("-- Finish Building DBML Script --")

}
}

[ben13]

@Nakita my guess is this has to do with table/column names having odd characters in them…

[Nakita]

Other than alphanumeric i have “#” “_” “:” and ’ (Example DB_Linked_Table’s) removed all ’ and : character, still error appears

[Nakita]

Update : I’ve tested this plugin on a brand new app with only one table (only alphabet on name “DatasetA”)

Still this error is repeated, So its something wrong with the plugin and not the header names, Any way to check this ? @ben13

[ben13]

@Nakita I’ve confirmed there is some issue here. I am looking into it. It works in the test app but does not work when deployed through the plugin marketplace. I also can’t get the plugin to redeploy so I’m guessing these are related issues. I’ll post again when I have an update.

[ben13]

@Nakita ok upgrade the plugin to 1.3.13 - final. I think this version will build the schema for you. Let me know if it doesn’t work.

[Nakita]

Its working, But require lot of manual involvement to make it into diagram. As relationships are shown with a “REF” without a space/linefeed and gives the below error

(603:83) Expected " ", “[”, Table Group, comment, end of input, enum, project, references, table, or whitespace but “:” found.

By giving a space/linefeed before Ref, this error will disappear, It will be great, if this can be handled by the plugin.

Example
custom.new_companyRef: custom.member_id Ref without a space giving the error, So i’m manually adding spaces everywhere, in my DB around 180 spaces to be added (being smarter and using Find replace)

[ben13]

@Nakita Good feedback. Check now. There should be a space / line feed for the ref

[jon4]

@ben13 - Love this plugin, and thank you for fixing the Ref line issue.

In the latest export, the plugin generated all table references with the prefix “custom.”, with the exception of the user table. This created a parser error in db diagram - we were able to manually fix all instances to read custom.user and that seemed to fix the issue.
Cheers,
Jon

[simeontechguru]

How were you able to fix all instances? I have the same problem and I’m trying to fix it

[jon4]

If it’s the same issue we had, you’ll need to look through each parse error and add “custom.” in front of each user table reference.

I’m sure there’s an elegant way to fix this with regex, but it took 2 mins to fix by brute force.

[simeontechguru]

Thanks @jon4, I fixed the issue.

@ben13 Looks like the schema also shows deleted fields. Any fix for that?

[stuart4]

awesome plugin, very handy now I’ve got a ton of data to work through.

And as mentioned above - i had to run a find and replace for “custom.user” and change to “user” to make it work as expect.

[ben13]

Thanks @stuart4 I just fixed the user issue so that hack/work around should no longer be necessary. It should copy/paste as expected with no errors now.

@fede.bubble What do you think about proposing this to the Bubble team for official addition to the bubble editor? It seems like it would be easy enough and a nice out-of-the-box feature for users to be able to visually diagram their database?

[tom29]

Works but I had to replace custom.“DataType” everywhere by “DataType”. Would be great if this was automatic