SchemaPuker can be accessed here: https://schemapuker.herokuapp.com/

Read on for more information about SchemaPuker!

Often, we need to produce diagrams of our organisation’s data model (aka. ERDs). This will be especially true for those of us who are consultants.

Perhaps you are doing a discovery or analysis and need a a copy of the current data model, or maybe you need a ‘current state’ and a ‘to be’ for comparison, or you are designing new functionality that connects with an existing data model, or documenting functionality after completion.

Now, salesforce does have a tool to visualise the data model, called Schema Builder, however this cannot export the model, nor can it be customised without actually changing the data model itself.

To solve this problem, I came up with… SchemaPuker! (thanks to David Carroll for the name! and to David Everitt for the idea in the first place!) For more about how it came to be, and the name click here

But for now, SchemaPuker is a fairly simple tool, It allows you to authorise to salesforce, get a list of your objects and export them as a PostgreSQL schema file. This file can be imported in to Lucidchart (and other tools) in order to generate an editable ERD.

The tool itself is very simple to use, first, navigate to https://schemapuker.herokuapp.com, choose if you are using a Production/Developer Org or a Sandbox and click ‘Login’. You will then be asked to enter your salesforce credentials and to authorise SchemaPuker to access your org.

Screen Shot 2016-09-01 at 16.36.36

Once authorised, you will be given a list of objects inside your salesforce org. You then select the objects you wish to be in your ERD by holding down command (or crtl on windows/linux) and clicking, or by typing the API names in the ‘Selected Objects’ box

sp2

Once you click submit, you are given the PostgreSQL Schema. You can either copy/paste this into lucid chard, or click the ‘Download’ button below the output.

sp3

Next, log in to Lucidchart and create a new drawing, click ‘More Shapes’ at the bottom and then tick ‘Entity Relationship’ and press ‘Save’

lucid1

Now, you can either import the downloaded file from SchemaPuker by pressing ‘Choose File’, or paste the output in to the box below. You can ignore steps one and two in the import window.

lucid2

You will now see your salesforce objects in the sidebar just under the ‘Entity Relationship’ panel. You can drag the objects on and the relationships between the objects will be automatically created.

lucid3

You can click add new shapes from the ‘Entity Relationship’ panel to extend your ERD as required.

Thats it! Please try it out and let me know how you go!

Please Note: This is still very much beta, and is ‘minimum viable product’. However I am working to improve it on a regular basis, and would love to hear your thoughts.
It is limited to ~30 objects per export and may crash in fun and exciting ways. The app does *not* store any data, nor does it make *any* changes to your salesforce org.

23 thoughts on “SchemaPuker: ERDs made easy

  1. Had chance to take a look at the Schema Puker tool – and the thread you’ve created here. I’ve been looking for a functionality like this for quite some time – and while I’ve seen some good attempts – this is the best one I’ve seen so far.
    Fantastic article!!

  2. Is it possible to adapt the oauth authentication so it isn’t just looking for an access token but to also consider those working with ip restrictions.

    I get the error:

    “There was an unexpected error (type=Internal Server Error, status=500).
    Response body is incorrect. Can’t extract a “access_token”\s*:\s*”

        1. I had a look through the logs and found the crash that happens when you tried to login (I can only see “error”:”invalid_grant”,”error_description”:”ip restricted”, not any user details). Unfortunately, from what I can see, there is nothing I can change at my end to allow people with IP restrictions to use the app. This is something set in your org that would need to be changed. I would suggest doing this in a sandbox, not production if you wish to use SchemaPuker.

  3. Really cool! Gave it a try had a couple of questions:

    – For each object, how does it decide which fields to pull in? For large objects, seems to kind of pull in a certain number of fields and then stops at maybe around ~30 fields, is that a current limitation?

    – Seems like when importing to LucidChart, some of the object relationships get pre-loaded with the relationship lines in LucidChart, but others don’t. Have you noticed the same thing, does the order of the objects matter when creating the output or importing to LucidChart for the relationship lines?

    1. Hi John, thanks for the comments. I have not had any reports of either of these bugs as yet, so I will definitely look into them.

      There is a hard limit of 100 objects, however I don’t believe this extends to fields, but I will check. As for the lines in lucid, this may be a product of the fields not being dumped out.

      1. I am experiencing the same issues: Some relation lines are drawn, some are not – which is confusing and somehow defeats the purpose.

        Great work nonetheless, I agree with all before me that this is the best attempt so far.

        Any plans to integrate Class Schema Data as well? That would be most convenient!

        1. Thanks for the comments. I am currently looking in to the bug where the lines are not drawn, I hope to have a fix ready soon.

          I have thought about building a UML tool, and it’s something I’d like to do, I just lack the time at the moment to do so. But hopefully next year I can get started

  4. Michael, I really like SchemaPuker. Elegant, simple design that solves a big problem — I’m amazed that nobody else has stepped up to provide a good tool for SF ERDs.

    The relationship lines omit master-detail. Is that something you can look into?

    1. I could not believe how simple the tool was to use. I am wondering if you are still working on updates to the tool as it still seems like the best option out there and it would be fantastic if you had a complete version!

      What brought me here though is that I noticed that some lookup relationships are missing also. For example, if I just drag in Account and Asset, Asset has several lookup relationships to Account but only one seems to showing up.

      If you have an updated version that would be perfect!

      1. Hi Stef,

        I have made some updates recently in the handling of relationships. If you wouldn’t mind sending me your output so I can take a look and see what is happening with your specific asset/account relationships?

        Thanks,
        Mick

  5. Is it open-source? It could be very useful to tweak it as software to run on an in-house server (rather than having to start from scratch w/ the Metadata API) so as to build a program that can create a “DROP TABLE … CREATE TABLE”-type script for us (to facilitate reporting across complex table joins, we are looking into caching key data from Salesforce to our local database).

  6. Looks like a great tool. Is this only available for certain Salesforce versions? I just tried and this was the top line of the error:

    [UnexpectedErrorFault [ApiFault exceptionCode=’API_DISABLED_FOR_ORG’
    exceptionMessage=’API is not enabled for this Organization or Partner’
    extendedErrorDetails='{[0]}’

    1. Hi Patrick,

      The API used by SchemaPuker (Metadata API) is only supported on the following editions; Enterprise Edition, Unlimited Edition, Performance Edition, or Developer Edition.

      If you are using one of these and still cannot access the tool, an administrator may have disabled API access.

  7. Hi Mick,
    it looks like the import tool for Lucid Chart ERDs has changed. You have to specifiy a DB and then run a query for the correct ouput. I tried with all DB types but Import does not work. Any ideas ?

      1. Ah. They changed the delimiters. Take the SchemaPuker output in to excel (or anything) and change the ; delimiter to , and remove quotes around text. LucidChart took that from me.

        1. And BTW, THANK YOU for the time invested in this. It has saved me a ton of it. If you are at Dreamforce this year, I’d love to say thank you (I am working on this from the plane on the way to). Look for me. I will be the guy with glasses and a beard. Probably carrying a bag of some sort.

Leave a Reply

Your email address will not be published. Required fields are marked *